Microsoft Office Tutorials and References
In Depth Information
In the Lab
8. Complete the following entries:
a. Year 1 Sales (cell B11) = Units Sold in Prior Year * (Unit Cost / (1
Margin)) or =B2 * (B3 / (1
b. Year 2 Sales (cell C11) = Year 1 Sales * (1 + Annual Sales Growth) * (1
Annual Price Decrease)
or =B11 * (1 + $B$4) * (1
Copy cell C11 to the range D11:I11.
Year 1 Cost of Goods (cell B12) = Year 1 Sales
(Year 1 Sales * Margin) or =B11 * (1
Copy cell B12 to the range C12:I12.
Gross Margin (cell B13) = Year 1 Sales
Year 1 Cost of Goods or =B11
g. Copy cell B13 to the range C13:I13.
h. Year 1 Advertising (cell B15) = 500 + 13% * Year 1 Sales or =500 + 13% * B11
i. Copy cell B15 to the range C15:I15.
j. Maintenance (row 16): Year 1 = 1,905,000; Year 2 = 5,550,000; Year 3 = 4,250,000; Year 4 =
5,050,000; Year 5 = 2,500,000; Year 6 = 3,150,000; Year 7 = 2,945,000; and Year 8 = 3,560.000.
k. Year 1 Rent (cell B17) = 1,700,000
l. Year 2 Rent (cell C17) = Year 1 Rent + 10% * Year 1 Rent or =B17 * (1 + 10%)
m. Copy cell C17 to the range D17:I17.
n. Year 1 Salaries (cell B18) = 22.25% * Year 1 Sales or =22.25% * B11
o. Copy cell B18 to the range C18:I18.
p. Year 1 Supplies (cell B19) = 1.5% * Year 1 Sales or =1.5% * B11
q. Copy cell B19 to the range C19:I19.
r. Year 1 Total Expenses (cell B20) or =SUM(B15:B19)
s. Copy cell B20 to the range C20:I20.
t. Year 1 Operating Income (cell B22) = Year 1 Gross Margin
Year 1 Total Expenses or =B13
u. Copy cell B22 to the range C22:I22.
v. Year 1 Income Taxes (cell B23): If Year 1 Operating Income is less than 0, then Year 1 Income
Taxes equal 0; otherwise Year 1 Income Taxes equal 40% * Year 1 Operating Income or
=IF(B22 < 0, 0, 40% * B22)
w. Copy cell B23 to the range C23:I23.
Year 1 Net Income (cell B24) = Year 1 Operating Income
Year 1 Income Taxes or =B22
y. Copy cell B24 to the range C24:I24.
9. Change the background colors as shown in Figure 3-85. Use Orange (column 3 under Standard
Colors) for the background colors.
10. Zoom to: (a) 200%; (b) 75%; (c) 25%; and (d) 100%.
11. Change the document properties, as speciﬁ ed by your instructor. Change the worksheet header
with your name, course number, and other information requested by your instructor. Save the
workbook using the ﬁ le name, Lab 3-1 Salioto Auto Parts Eight-Year Financial Projection.
12. Preview the worksheet. Use the Page Setup button to ﬁ t 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 and close the workbook.
13. Submit the workbook as requested by your instructor.
Instructions Part 2:
1. Start Excel. Open the workbook Lab 3-1 Salioto Auto Parts Eight-Year Financial Projection.
2. Use the nonadjacent ranges B10:I10 and B24:I24 to create a 3-D Cylinder chart. Draw the chart
by clicking the Column button on the Insert tab on the Ribbon. When the Column gallery is
displayed, click the Clustered Cylinder chart type (column 1, row 2). When the chart is displayed,
click the Move Chart button on the Ribbon to move the chart to a new sheet.