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 worksheet is shown in Figure 3–85.
Figure 3-85
If this # is
wrong, you have
incorrect data/
formulas
somewhere.
Part 1: Perform the following tasks:
1. Run Excel, open a blank workbook
2. Apply the Parallax theme to the worksheet.
3. Enter the worksheet title August Online Technology in cell A1 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 I2
using the one of the Date Stamp functions we learned about in class. Format the date
to the 14-Mar-12 style.
4. Change the following column widths: A = 24.00 characters; B through I = 14.00
characters.
5. Change the heights of rows 7, 15, 17, 19, and 22 to 18.00 points.
6. Enter the eight column titles Year 1 through Year 8 in the range B3:I3 by entering Year 1
in cell B3 and then using the fill handle.
7. Format cell B3 as follows:
• Increase the font size to 12.
• Center and italicize it.
• Angle its contents 45 degrees.
8. Use the Format Painter button to copy the format assigned to cell B3 to the range C3:I3.
9. Enter the row titles, as shown in Figure 3-85, in the range A4:A19.
10. Change the font size in cells A7, A15, A17, and A19 to 14-point.
11. Add thick bottom borders to the ranges A3:I3 and A5:I5.
12. 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.
13. Change the entry in row 14 by inserting your surname prior to the text, Web Services.
14. 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.
15. Select the range B4:I19 and then click the Number Format Dialog Box Launcher (Home
tab | Number group) to display the Format Cells dialog box. Use the Number category
(Format Cells dialog box) to assign the appropriate style that displays numbers with two
decimal places and negative numbers in black font and enclosed in parentheses to
the range B4:I19.
16. Complete the following entries:
• Year 1 Sales = Units Sold in Prior Year * (Unit Cost/(1 – Margin))
• Year 2 Sales = Year 1 Sales * (1 + Annual Sales Growth) * (1 + Annual Price
Increase).
Copy cell C4 to the range D4:I4.
• Year 1 Cost of Goods = Year 1 Sales * (1 – Margin).
All formulas/
Copy cell B5 to the range C5:I5. functions that
• Gross Margin = Year 1 Sales – Year 1 Cost of Goods reference cells
Copy cell B6 to the range C6:I6.
B23:B27 need
to be absolute
• Year 1 Advertising = 1250 + 8% * Year 1 Sales
cell references!
Copy cell B8 to the range C8:I8.
• 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
• Year 1 Rent = 1,000,000
• Year 2 Rent = Year 1 Rent + (6.5% * Year 1 Rent)
Copy cell C10 to the range D10:I10.
• Year 1 Salaries = 12% * Year 1 Sales
Copy cell B11 to the range C11:I11.
• Year 1 Shipping = 3.6% * Year 1 Sales
Copy cell B12 to the range C12:I12.
• Year 1 Supplies = 1.2% * Year 1 Sales
Copy cell B13 to the range C13:I13.
• Year 1 Web Services = 85,000
• Year 2 Web Services = Year 1 Web Services + (6% * Year 1 Web Services)
Copy cell C14 to the range D14:I14.
• Year 1 Total Expenses =SUM(B8:B14)
Copy cell B15 to the range C15:I15.
• Year 1 Operating Income = Year 1 Gross Margin – Year 1 Total Expenses
Copy cell B17 to the range C17:I17.
• Year 1 Income Tax: 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:I18.
• Year 1 Net Income = Year 1 Operating Income – Year 1 Income Tax
Copy cell B19 to the range C19:I19.
• In cell J4, insert a column Sparkline chart for cell range B4:I4.
• 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.
17. Apply the Accounting number format with a dollar sign and two decimal places to the
following ranges: B4:I4, B6:I6, B8:I8, B15:I15, B17:I17, and B19:I19.
18. Apply the comma style format to the following ranges: B5:I5 and B9:I14.
19. Apply the Number format with two decimal places and the 1000 separator to the range
B18:I18.
20. Change the background colors, as shown in Figure 3–85. Use Blue, Accent 1, Lighter 40%
for the background colors. Shade the entire assumptions area using Blue, Accent 1,
Lighter 40%.
21. Create a header as listed below:
Center Section: Enter your First Name and Last Name along with your Period #.
Left Section: Current date element
Right Section: Sheet Name element
22. Create a footer as listed below:
Center Section: File Name element
23. Save the workbook using the file name, LastName August Online Technology
24. Preview the worksheet. Print on 1 page in landscape orientation.
25. Preview and Print the formulas version of the worksheet on 1 page and in landscape
orientation.
26. Display the values version of the worksheet. Save the workbook again and move on to
Part 2.
Part 2 Perform the following tasks:
Create a chart to present the data, shown in Figure 3–86.
Figure 3–86
1. Use the nonadjacent ranges B3:I3 and B19:I19 to create a Stacked Area chart. When
the chart appears, click the Move Chart button to move the chart to a new sheet
titled, Net Income Chart.
2. Change the chart title to Projected Net Income
3. Use the Chart Elements button to add a vertical axis title. Edit the axis title text to read
Net Income. Bold the axis title.
4. Change the Chart Style to ‘Style 4’ in the Chart Styles Gallery. Use the ‘Chart Quick
Colors’ button (Chart Tools Design tab | Chart Styles group) to change the color
scheme to Monochromatic, Color 5.
5. Rename the sheet 1 tab to Financial Projection
6. Rearrange the sheets so that the worksheet is leftmost and change the tab colors to
those of your choosing.
7. Insert the same header/footer format as defined in Part 1 to your chart sheet.
8. Print your chart.
9. Click the Financial Projection tab to return to the worksheet. Save the workbook using
the same file name as defined in Part 1.
