What-If Analysis, Charting, And Working With Large Worksheets Page 75
ADVERTISEMENT
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87What-If Analysis, Charting, and Working with Large Worksheets
Excel Chapter 3
EX 211
5. Enter the row titles in the range A4:A19. Change the font in cells A7, A15, A17, and A19 to
14-point Verdana (or a similar font). Add thick bottom borders to the ranges B3:G3 and B5:G5.
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.
6. Enter the table title Assumptions in cell A22. Enter the assumptions in Table 3–9 on page
EX 209 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 Verdana and underline it.
7. Select the range B4:G19 and then click the Format Cells: Number 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 Comma style with no decimal places and negative numbers enclosed
in parentheses to the range B4:G19.
8. Complete the following entries:
a. Year 1 Sales (cell B4) = Units Sold in Prior Year * (Unit Cost / (1 − Margin)) or = B23*(B24/
(1-B27))
b. Year 2 Sales (cell C4) = Year 1 Sales * (1 + Annual Sales Growth) * (1 − Annual Price Decrease)
or =B4*(1+$B$25)*(1-$B$26)
c. Copy cell C4 to the range D4:G4.
d. Year 1 Cost of Goods (cell B5) = Year 1 Sales * (1 – Margin) or =B4 * (1 - $B$27)
e. Copy cell B5 to the range C5:G5.
f. Gross Margin (cell B6) = Year 1 Sales - Year 1 Cost of Goods or =B4 – B5
g. Copy cell B6 to the range C6:G6.
h. Year 1 Advertising (cell B8) = 500 + 8% * Year 1 Sales or =500+8%*B4
i. Copy cell B8 to the range C8:G8.
j. Maintenance (row 9): Year 1 = 1,605,000; Year 2 = 4,378,000; Year 3 = 5,920,000; Year 4 =
5,050,000; Year 5 = 3,200,000; Year 6 = 4,250,000
k. Year 1 Rent (cell B10) = 2,700,000
l. Year 2 Rent (cell C10) = Year 1 Rent + (12% * Year 1 Rent) or =B10*(1+12%)
m. Copy cell C10 to the range D10:G10.
n. Year 1 Salaries (cell B11) = 17% * Year 1 Sales or =17%*B4
o. Copy cell B11 to the range C11:G11.
p. Year 1 Shipping (cell B12) = 3.9% * Year 1 Sales or =3.9%*B4
q. Copy cell B12 to the range C12:G12.
r. Year 1 Supplies (cell B13) = 1.3% * Year 1 Sales or =1.3%*B4
s. Copy cell B13 to the range C13:G13.
t. Year 1 Web Services (cell B14) = 250,000
u. Year 2 Web Services (cell C14) = Year 1 Web Services + (15% * Year 1 Web Services) or
=B14*(1+15%)
v. Copy cell C14 to the range D14:G14.
w. Year 1 Total Expenses (cell B15) = SUM(B8:B14)
x. Copy cell B15 to the range C15:G15.
y. Year 1 Operating Income (cell B17) = Year 1 Gross Margin - Year 1 Total Expenses or =B6-B15
z. Copy cell B17 to the range C17:G17.
Continued >
ADVERTISEMENT
0 votes
Related Articles
Related forms
Related Categories
Parent category: Education