Amanda has another set of orders for you to enter on the Customer Orders worksheet. In
addition to calculating the charge for creating the DVDs, Amanda also wants to include
the cost of shipping in the total charged to each customer.
1. Open a blank workbook, and then save the workbook as Order Report.
2. Rename the Sheet1 worksheet as Documentation, and then enter the following data into the specified cells:
Cell A1: RipCity Digital
Cell A3: Author Cell B3: your name
Cell A4: Date Cell B4: =today()
Cell A5: Purpose Cell B5: To track customer orders for RipCity Digital
3. Insert a new worksheet (plus sign to right of previous worksheet), and rename the new Sheet2 worksheet as Customer Orders.
4. On the Customer Orders worksheet, enter the following data into the specified cells:
Cell A1: RipCity Digital
Cell A3: Customer Orders Report
Cell A4: March 27 to April 17, 2015
5. In cells A5 through H10, enter the column titles and data from Figure_One. Wrap text accordingly.
Figure_One Date LastName FirstName Address Phone DVDs Price per DVDFigure_One Date LastName FirstName Address Phone DVDs Price per DVD Shipping Charge
25 Lee St. Bedford, VA 24523
28 Ridge Ln. Newfane, VT 05345
150 Main St. Greenbelt, MD 20770
250 East Maple Ln. Cranston, RI 02910
207 Jackston Ave. Westport, IN 47283
6 Set the width of column A to 10 characters, columns B and C to 12 characters, column D to
20 characters, and columns E, G, and H to 16 characters. (Select column, Home tab, Cells
group, Format, Column Width)
7. Autofit all rows in the worksheet to the cell contents. (Select row, Home, Cells group,
Format, AutoFit Row Height)
8. In cell I5, enter Total Charge. In cell I6, insert a formula that calculates the total charge
for the first customer (the number of DVDs multiplied by the price per DVD and then added
to the shipping charge). Increase the width of column I to 11 characters.
9. Copy the formula in cell I6 and paste it into the range I7:I10.
10. In cell E11, enter Total DVDs. In cell F11, use the SUM function to calculate the total
number of DVDs created for all customers. In cell I11, use the AutoSum feature to insert
the SUM function to calculate the total charges for all of the customer orders.
11. Use Edit mode to make the following corrections:
a. In cell D6, change the street address from 25 Lee St. to 2500 Lee St.
b. In cell F9, change the number of DVDs from 15 to 17.
c. In cell H8, change the shipping charge from $7.23 to $8.23.
12. Use the Find and Replace commands to replace all occurrences of St. with Street, Ln.
with Lane, and Ave. with Avenue.
13. Fill color cell range A5:I5 to a color of your choosing. Surround each cell (all cells) with a
14. Change the page layout of the Customer Orders worksheet to print in landscape orientation
on a single page.
15. Return the view of the Customer Orders worksheet to Normal view, save your changes to
the Order Report workbook, and then save the current workbook as Revised Report.
16. Remove Kay Dexter s order from the Customer Orders worksheet.
17. Add the following order directly after the order placed by June Romano: date 4/22/2015;
name Patrick Crawford; address 200 Valley View Road, Rome, GA 30161; phone (706)
555-0998; DVDs 14; price per DVD $16.55; shipping charge $12.45.
18. Use Edit mode to change the ending date of the report in cell A4 from April 17 to April
19. Save the workbook and preview it for correctness.
20. Submit all saved workbooks to the instructor s gradebook via Blackboard. No emails of
Altac Bicycles Deborah York is a financial consultant for Altac Bicycles, an online seller of bicycles and bicycle equipment based in Silver City, New Mexico. She has entered some financial information in an Excel workbook for an income statement she is preparing for the company. You will enter the remaining data and formulas.
1. Open the Altac workbook, and then save the workbook as Altac Bicycles.
2. Insert three new rows at the top of the Sheet1 worksheet, and then enter the following text on two lines within cell A1: (In cell A1, enter the first line of text, and then hold down the Alt key and press the enter key, and then enter next line of text.)
3. In cell A2, enter For the Years Ended December 31, 2012 through December 31, 2014.
4. In the range C6:E7, enter the following net sales and cost of sales figures: 2014 2013 2012
Net Sales 13,520 10,981 9,034
Cost of Sales 4,140 3,960 3,011
5. In the range C11 :E14, enter the following expense figures (expand the column widths as necessary to show the text and data values):
2014 2013 2012
Salaries and Wages 1,632 1,481 1,392
Sales and Marketing 2,631 2,112 1,840
Administrative 521 410 375
Research and Development 501 404 281
6. In the nonadjacent range C18:E18;C20:E20;C24:E24, enter the following values for Other Income, Income Taxes, and Shares, pressing the Enter or Tab key to navigate from cell to cell in the selected range:
2014 2013 2012
Other Income 341 302 239
Income Taxes 1,225 1,008 821
Shares 3,621 3,001 2,844
7. In the range C8:E8, enter a formula to calculate the gross margin for each year, where the gross margin is equal to the net sales minus the cost of sales.
8. In the range C15:E15, enter the SUM function to calculate the total operating expenses for each year, where the total operating expenses equal the sum of the four expense categories.
9. In the range C17:E17, enter a formula to calculate the operating income for each year, where operating income is equal to the gross margin minus the total operating expenses.
10. In the range C19:E19, enter a formula to calculate the pretax income for each year, where pretax income is equal to the operating income plus other income.
11. In the range C22:E22, enter a formula to calculate the company’s net income for each year, where net income is equal to the pretax income minus income taxes.
12. In the range C25:E25, enter a formula to calculate the earnings per share for each year, where
earnings per share is equal to the net income divided by the number of shares.
13. Use the spelling checker to correct and replace any spelling errors in the worksheet. Ignore the
spelling of “ALtac.”
14. In cell A18, use Edit mode to capitalize the word “income.”
15. Increase the width of column A to 18 characters and increase the width of column B to 25
characters. Autofit the height of row 1.
16. Rename the Sheet1 worksheet as Income Statement.
17. Add a new worksheet, Sheet2, and rename the Sheet2 worksheet as Documentation and move it to
the beginning of the workbook.
18. In the Documentation worksheet, enter the following text and values:
Cell A1: Altac Bicycles
Cell A3: Author
Cell A4: Date
Cell A5: Purpose
Cell B3: your name
Cell B4: the current date
Cell B5: Income statement for Altac Bicycles for 2012 through 2014
19. Save the workbook, preview the workbook and make sure each worksheet in portrait orientation fits
on one page.
20. Close the workbook, and then submit the finished workbook via Blackboard to the instructor s
Year 2014 2013 2012
Cost of Sales
Salaries and Wages
Sales and Marketing
Research and Development
Total Operating Expenses
Eanings per share
- (in millions except per-share amounts)