Excel Module 3 Working with Large Worksheets, Charting ...

1 Excel Module 3 Working with Large Worksheets, Charting , and What-If Analysis Lab 1: Eight-Year Financial Projection Problem: Your supervisor in the finance department at August Online Technology has asked you to create a worksheet for the flagship product that will project the annual gross margin, total expenses, operating income, income taxes, and net income for the next eight years based on the assumptions in Table 3- 9. The desired result is shown in Figure 3-85. You will place these assumptions in the spreadsheet (see step 8), as shown in Fig. 3-85 and use them with absolute references when building your formulas. Some steps have * s in front of them. The * s indicate that this is where you will print something, one print per *.

2 Definitions: workbook: the entire spreadsheet file, worksheet: one of possibly many tabs (spreadsheets or charts) in the file. Be sure to include the lab Part number with the step number in the footer. Excel Module 3 Working with Large Worksheets, Charting , and What-If Analysis Figure 3-85 Instructions Part 1: Run Excel , open a blank workbook, and create the worksheet following these instructions. Do NOT use fit to one page EVER! Do NOT print double-sided! It is OK if it takes multiple pages. Just be sure to label them with the same step number in the footer. When entering numbers, do NOT use the Comma icon and do NOT type the commas. When typing currency, do NOT type the $-signs or commas. You will be told how to format them!

3 1. Apply the Parallax theme to the worksheet. 2. Enter the worksheet title August Online Technology in cell Al and the subtitle Eight-Year Financial Projection for Product X in cell Al. Format the worksheet title in cell Al to 26-point and the worksheet subtitle in cell A2 to 16-point. Enter the system date in cell I2 using the NOW function. Format the date to the mm/dd/yy style. 3. Change the following column widths: A = characters; B through I = characters. Change the heights of rows 7, 15, 17, 19, and 22 to points. See chapter 2 of the book for how to change the row height in points. 4. Enter the eight column titles Year 1 through Year 8 in the range B3:I3 by entering Year 1 in cell B3. Format cell B3 as follows: a.

4 Increase the font size to 12. b. Center and italicize it. c. Angle its contents 45 degrees. Then drag cell B3's fill handle through the range C3:I3. 5. Use the Format Painter button to copy the format assigned to cell B3 to the range C3:I3. 6. Enter the row titles, as shown in Figure 3-85, in the range A4:A19. Change the font size in cells A7, A15, A17, and A19 to 14-point. Add thick bottom borders to the ranges A3:I3 and A5:I5. Use the Increase Indent button (Home tab | Alignment group) to increase the indent of the row titles in cell A5, the range A8:A14, and cell A18. Do NOT use "spaces" to do the indenting. 7. Change the entry in row 14 by inserting your surname (last name) prior to the text, Web Services. Increase the column width as needed 8.

5 Enter the table title Assumptions in cell A22. Enter the assumptions in Table 3-9 in the range A23:B27. Use format symbols when entering the numbers. Change the font size of the table title in cell A22 to 14-point and underline it. 9. Select the range B4:I19 and then click the Number Format Dialog Box Launcher (Home tab | Number group | Format Cells), then use the Number category to assign the appropriate style that displays numbers with two decimal places (with negative numbers in black font, enclosed in parentheses) to the selected range. 10. Complete the following sub-steps (a-s). Remember to use absolute references for the percentages (these are called constants ) in the formulas. You are NOT permitted to use named references for them.

6 Put these constants below the assumptions. You do NOT have to create labels for them. The instructions below give you: the name of a cell = formula. For example: Year 2 Sales (cell C4) = formula. You only need to type the = and the formula that follows it, into the cell. a. Year 1 Sales (cell B4) = Units Sold in Prior Year* (Unit Cost/ (1 - Margin)) example: for this step in B4 put =$B$23*($B$24/(1-$B$27)) b. Year 2 Sales (cell C4) =Year 1 Sales* (1 +Annual Sales Growth) * (1 +Annual Price Increase). Copy cell C4 to the range D4:I4. c. Year 1 Cost of Goods (cell B5) = Year 1 Sales* (1 - Margin). Copy cell B5 to the range C5:I5. d. Gross Margin (cell B6) =Year 1 Sales - Year 1 Cost of Goods. Copy cell B6 to the range C6:I6.

7 E. Year 1 Advertising (cell B8) = 1250 + 8% *Year 1 Sales. Copy cell B8 to the range C8:I8. f. Maintenance (row 9): Year 1 = 500,000; Year 2 = 600,000; Year 3 = 440,000; Year 4 = 520,000; Year 5 = 555,000; Year 6 = 420,000; Year 7 = 390,000; Year 8 = 400,000. DO NOT create references for these numbers. They are DATA, not constants. Just type them. g. Year 1 Rent (cell B10) = 1,000,000 (This is just data) h. Year 2 Rent (cell C10) =Year 1 Rent+ ( *Year 1 Rent). Copy cell C10 to the range D10:l10. i. Year 1 Salaries (cell B11) = 12% *Year 1 Sales. Copy cell B11 to the range C11:I11. Excel Module 3 Working with Large Worksheets, Charting , and What-If Analysis j. Year 1 Shipping (cell B12) = *Year 1 Sales. Copy cell B12 to the range C12:I12.

8 K. Year 1 Supplies (cell B13) = *Year 1 Sales. Copy cell B13 to the range C13:I13. l. Year 1 Web Services (cell B14) = 85,000 (Just data) m. Year 2 Web Services (cell C14) =Year 1 Web Services+ (6% *Year 1 Web Services). Copy cell C14 to the range D14:I14. n. Year 1 Total Expenses (cell B15) = SUM(B8:B14). Copy cell B15 to the range C15:I15. o. Year 1 Operating Income (cell B17) =Year 1 Gross Margin - Year 1 Total Expenses. Copy cell B17 to the range C17:I17. p. Year 1 Income Tax (cell B18): This cell must contain an IF formula. Use the IF function to compare the value of the Year 1 Operating Income against 0. If Year 1 Operating Income is less than 0, then Year 1 Income Tax must be 0; otherwise Year 1 Income Tax must be 33 % *Year 1 Operating Income.

9 Copy cell B18 to the range C18:I18. In case we have not yet covered the IF function, it has this basic format: =IF(cellref<value, truepart, falsepart) For this step the truepart would be a zero. The falsepart would be the underlined computation. q. Year 1 Net Income (cell B19) = Year 1 Operating Income - Year 1 Income Tax. Copy cell B19 to the range C19:I19. r. In cell J4, insert a column sparkline chart (Insert tab I Sparklines group) for cell range B4:I4. s. Insert column sparkline charts in cells J5, J6, J8:J15, and J17:J19 using ranges B5:I5, B6:I6, B8:I8, B15:I15, and B17:I17 - B19:I19 respectively. 11. Apply the Currency number format with a dollar sign, two decimal places, and negative numbers in black with parentheses to the following ranges: B4:I4, B6:I6, B8:I8, B15:I15, B17:I17, and B19:I19.

10 Apply the comma style format to the following ranges: B5:I5 and B9:I14. Apply the Number format with two decimal places and the 1000 separator to the range B18:I18. 12. Change the background colors, as shown in Figure 3-85. Use Blue, Accent 1, Lighter 40% for the background colors. 13. Save the workbook using the file name, Lab 3-1. Add a footer as in the previous labs. 14. ** Preview the formula version ( CTRL+ `) of the worksheet in landscape orientation and print this formula view. Do NOT use fit to one page! Do NOT print double-sided! It is OK if it takes multiple pages. Just be sure to label the pages with the same step #. Press CTRL+` again to return to the normal (values view) of the worksheet. Print the normal view.