Microsoft Excel 2010 Chapter 3 – Lab Test A

Creating a Financial Projection

Purpose:  To demonstrate the ability to copy a range to a nonadjacent range, apply formulas that use absolute referencing, create a chart, use goal seeking, and perform what-if analysis.

Problem: Your manager in the Accounting department at Woogy High-Speed Internet has asked you to create a worksheet that will project the margin, expenses, and operating income for the six-month period July through December based on the sales projections in Table E3A-1 and the assumptions in Table E3A-2. The desired worksheet is shown in Figure E3A-1. In Part 1 you will create the worksheet. In Part 2 you will create a 3-D Pie chart from the data as shown in Figure E3A-2. In Part 3 you will analyze results by changing data.

Part 1 Instructions: Perform the following tasks to create the worksheet in Figure E3A-1.

Table E3A-1

Woogy High-Speed Internet Projected Monthly Sales













Table E3A-2

Woogy High-Speed Internet Assumptions

What-If Assumptions











Sales Plateau for Bonus


Support, General,  and Administrative



Instructions Part 1: To create the worksheet in Figure E3A-1, do the following:

  1. Start Excel and create a new blank workbook. Change the font of the entire worksheet to 10-point Arial bold
  2. Enter the worksheet title Woogy High-Speed Internet in cell A1 and the subtitle Semiannual Projected Margin, Expenses, and Operating Income in cell A2. Format the worksheet title in cell A1 to 28-point Ravie (or a similar font). Format the worksheet subtitle in cell A2 to 16-point Lucida Calligraphy (or a similar font).
  3. Change the following column widths: A = 35.86 characters; B through G = 14.86 characters; and H = 16.00.
  4. Enter the system date in cell I2 using the NOW function. Format the date to the 3/14/2001 style.
  5. Enter the month name July in cell B3. Format cell B3 as follows: rotate its contents 45o, change the font size to 11, and add a bottom border. Enter the month names August through December in the range C3:G3. Type Total in cell H3 and Chart in cell I3 and use the Format Painter button to format the cells the same as cell G3.
  6. Enter the row titles shown in Figure E3A-1 in the range A4:A25. Use the Increase Indent button to indent row titles as shown in Figure E3A-1. Change the font in cells A4, A6, A8, A14, A16, and A18 to 12-point Franklin Gothic (or a similar font). Underline and italicize cell A18.
  7. Enter the monthly projected sales shown in Table 3-EOC 1 in the range B4:G4.  Enter the assumptions in Table E3A-2 in the range B19:B25. Use format symbols when entering the numbers in the range B19:B25.
  8. Save the workbook using the file name Excel Chapter 3 – Lab Test A in compapps/excel/chapter3 folder on your Z:/ drive
  9. Enter the following formulas in column B:
    1. Cost of Goods Sold (cell B5) = Sales x (1 – Margin Assumption)
    2. Margin (cell B6) = Sales – Cost of Goods Sold
    3. Bonus (cell B9): If Sales is greater than Sales Plateau for Bonus Assumption (cell B24), then Bonus = B19, otherwise Bonus = 0
    4. Commission (cell B10) = Sales x Commission Assumption
    5. Marketing (cell B11) = Sales x Marketing Assumption
    6. Research (cell B12) = Sales x Research Assumption
    7. Support, General, and Administrative (cell B13) = Sales x Support, General, and Administrative Assumption
    8. Total Expenses (cell B14) = Sum of expenses
    9. Operating Income (cell B16) = Margin – Total Expenses
  10. Copy the range B5:B16 to the range C5:G16.
  11. Determine the row totals in the range H4:H16.
  12. Add Sparkline Line charts to cells I4, I6, I14, and I16 to chart Sales, Margin, Total Expenses, and Operating income in those cells. Select cell H16. Select the Line 33 Style on the Design tab on the Ribbon to change the color of the Sparkline in cell H16. Save the workbook.   
  13. One at a time, select the ranges B4:H4, B6:H6, B9:H9, and B14:H16 and then click the Format Cells: Number Dialog Box Launcher on the Home tab on the Ribbon to display the Format Cells dialog box. Assign the Currency style with two decimal places and negative numbers enclosed in parentheses.
  14. One at a time, select the ranges B5:H5 and B10:H13 and then click the Format Cells: Number Dialog Box Launcher on the Home tab on the Ribbon to display the Format Cells dialog box. Assign the Comma style with two decimal places and negative numbers enclosed in parentheses.
  15. Add bottom borders to the ranges B5:I5 and B13:I13. Change the background colors of A1:I2, A4, A6, A8, A14, and A16:I16 as shown in Figure 3-EOC 4. Use Dark Blue, Accent 3, Lighter 40% (column 7, row 4 on the Fill Color palette).
  16. Double-click the Sheet1 tab and change the name Semiannual Financial Projection. Right-click the same tab and change its color to Blue.
  17. Use the Zoom button on the View tab on the Ribbon to zoom to: (a) 200%; (b) 75%; (c) 25%; and (d) 100%.
  18. Change the document properties.  Change the worksheet header with your name, course number, and other information requested by your instructor. Save the workbook.
  19. Preview the worksheet by clicking the Office button, pointing to Print, and clicking Print Preview. Use the Page Setup button in Print Preview to fit the printout on one page in landscape orientation. Close Print Preview.
  20. Preview the formulas version of the worksheet in landscape orientation using the Fit to option.
  21. Save the workbook and then close the workbook.

Instructions Part 2: Do the following to draw a Pie chart with a 3-D visual effect (Figure E3A-2) that shows the monthly contribution to the six-month operating income.

  1. Start Excel. Open the workbook Excel Chapter 3 – Lab Test A created in Part 1.
  2. Select the non-adjacent ranges B3:G3 and B16:G16.
  3. Create a Pie Chart with 3-D visual effect. When the chart is displayed, click the Move Chart button to move the chart to a new sheet. Change the tab name to 3-D Pie Chart. Change the tab color to Red. Drag the 3-D Pie Chart tab to the right of the Semiannual Financial Projection tab.
  4. Delete it the legend. Use the techniques developed in this chapter to do the following: a) add a chart title (Semiannual Financial Projection) above the Pie Chart; b) Add the data labels percentage and category name outside each slice, and c) enhance the 3-D Pie Chart with a bevel along the top edge.
  5. Explode the month slice with the greatest contribution to the six-month operating income by 40%.
  6. Format the chart title to 28-point bold blue font with an underline.
  7. Save the workbook as Excel Chapter 3 – Lab Test A Part 2 and then close the workbook.

Instructions Part 3: In this part of the exercise, you will analyze three different cases involving changes to the assumptions in the worksheet and goal seek on the total operating income by varying the margin assumption. Do the following:

  1. Start Excel. Open the workbook Lab 3-1 Woogy High-Speed Internet Six-Month Financial Projection.
  2. Using the numbers in the Table 3-EOC 3, analyze the effect of changing the assumptions in rows 21 through 25 on the total operating income in cell H16.

Table 3-EOC 3

Woogy High-Speed Internet What-If Analyses Data


Case 1

Case 2

Case 3

















Support, General, and Administrative




The total operating incomes in cell H16 are: Case 1 = $28,363,648.61; Case 2 = $46,183,131.49; Case 3 = $34,019,397.53.

3. Close the workbook saving it as Excel Chapter 3 – Lab Test A created in Part 3. Re-open the workbook that you just closed. Use the Goal Seek command to determine the margin percentage (cell B21) that would result in a semiannual operating income of $45,000,000 in cell H16. Click the What-If Analysis button on the Data tab on the Ribbon to access the Goal Seek command. You should end up with a margin percentage of 59.04%. take screen shots, paste it into word and save as Excel Chapter 3 – Lab Test A created in Part 3.

Figure E3A - 1


Figure E3A - 2