August Online Technology Eight Financial recorded Figure 3-85 > Perform the following tasks: Instructions Port 1: Run Excel, open a blank workbook, and then create the worksheet. 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 A2. Format the worksheet title in cell A1 to 26-point and the worksheet subtitle in cell A2 to 16-point. Enter the system date in cell 12 using the NOW function. Format the date to the 14-Mar-12 style. 3. Change the following column widths: A - 24.00 characters, B through I - 14.00 characters. Change the heights of rows 7.15, 17, 19, and 22 to 18.00 points. 4. Enter the eight column titles Year I through Year 8 in the range B3-B by entering Year! in cell B3 and then dragging cell B3% fill handle through the range C3:13. Format cell B3 as follows: a. Increase the font size to 12. b. Center and italicize it. c. Angle its contents 45 degrees. 5. Use the Format Painter button to copy the format assigned to cell B3 to the range C3:13. 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 A3B and A5:15. Use the increase Indent button (Home tab 1 Alignment group) to increase the indent of the row titles in cell A5, the range A8:A14, and cell A18. 7. If requested by your instructor, change the entry in row 14 by inserting your surname prior to the text, Web Services 8. Enter the table title Assumptions in cell A22. Enter the assumptions in Table 3-9 in the range A23:127. Use format symbols when entering the numbers. Change the font size of the table title in cell A22 to 14-point and underline it. Copyright 2018 Cunglingager WOOD a. Year 1 Sales (cell B4) - Units Sold in Prior Year (Unit Cost / (1 - Margin)) b. Year 2 Sales (cell C4) - Year 1 Sales * (1 + Annual Sales Growth)*(1 + Annual Price Increase). Copy cell C4 to the range D4:14. c. Year 1 Cost of Goods (cell B5) - Year 1 Sales (1 - Margin). Copy cell B5 to the range C5:15. d. Gross Margin (cell B6) - Year 1 Sales - Year 1 Cost of Goods. Copy cell B6 to the range C6:16. e. Year 1 Advertising (cell B8) = 1250 + 8% Year 1 Sales. Copy cell B8 to the range C8:18. 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. g. Year 1 Rent (cell B10) = 1,000,000 h. Year 2 Rent (cell C10) - Year 1 Rent + (6.5% Year 1 Rent). Copy cell C10 to the range D10:110. i. Year 1 Salaries (cell B11) - 12% *Year 1 Sales. Copy cell Bll to the range C11:111. j. Year 1 Shipping (cell B12) = 3.6% Year 1 Sales. Copy cell B12 to the range C12:112. k. Year 1 Supplies (cell B13) = 1.2% Year 1 Sales. Copy cell B13 to the range C13:113. 1. Year 1 Web Services (cell B14) = 85,000 m. Year 2 Web Services (cell C14) - Year 1 Web Services + (6% Year 1 Web Services). Copy cell C14 to the range D14.114. n. Year 1 Total Expenses (cell B15) - SUM(B8:B14). Copy cell B15 to the range C15:115. o. Year 1 Operating Income (cell B17) = Year 1 Gross Margin-Year I Total Expenses. Copy cell B17 to the range C17:117. p. Year 1 Income Tax (cell B18): If Year 1 Operating Income is less than 0, then Year 1 Income Tax equals 0; otherwise Year 1 Income Tax equals 33% *Year 1 Operating Income. Copy cell B18 to the range C18:118. q. Year 1 Net Income (cell B19) = Year 1 Operating Income - Year 1 Income Tax Copy cell B19 to the range C19:119. r. In cell J4, insert a column sparkline chart (Insert tab 1 Sparklines group) for cell range B4:14. s. Insert column sparkline charts in cells 35,36,38:J15, and J17:J19 using ranges B5:15, B6:16, B8:18 - B15:115, and B17:117 - B19:119 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:14, 16:16, B8:18, B15:115, B17:117, and B19:119. Apply the comma style format to the following ranges: B5:15 and B9:114. Apply the Number format with two decimal places and the 1000 separator to the range B18:118. 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 August Online Technology Eight-Year Financial Projection. 14. Preview the worksheet. Use the Orientation button (Page Layout tab Page Setup group) to fit the printout on one page in landscape orientation. Preview the formulas version (CTRL+") of the worksheet in landscape orientation using the Fit to option. Press CTRL+ to instruct Excel to display the values version of the worksheet. Save the workbook again