Microsoft Office Tutorials and References

In Depth Information

In the Lab

EX 235

8. Complete the following entries:

a. Year 1 Sales (cell B11) = Units Sold in Prior Year * (Unit Cost / (1

Margin)) or =B2 * (B3 / (1

B6))

b. Year 2 Sales (cell C11) = Year 1 Sales * (1 + Annual Sales Growth) * (1

Annual Price Decrease)

or =B11 * (1 + $B$4) * (1

$B$5)

c.

Copy cell C11 to the range D11:I11.

d.

Year 1 Cost of Goods (cell B12) = Year 1 Sales

(Year 1 Sales * Margin) or =B11 * (1

$B$6)

e.

Copy cell B12 to the range C12:I12.

f.

Gross Margin (cell B13) = Year 1 Sales

Year 1 Cost of Goods or =B11

B12

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

B20

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.

x.

Year 1 Net Income (cell B24) = Year 1 Operating Income

Year 1 Income Taxes or =B22

B23

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, Lab3-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 3). When the chart is displayed,

click the Move Chart button on the Ribbon to move the chart to a new sheet.

Continued >