What-If Analysis, Charting, And Working With Large Worksheets Page 79
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 215
4. Enter the column titles, row titles, and the fi rst three rows of numbers in Table 3 –11 in rows 3
through 6. Add the column heading Total to cell I3. Center and italicize the column headings in the
range B3:I3. Add a thick bottom border to the range B3:I3. Sum the individual rows 4, 5, and 6 in
the range I4:I6.
5. Enter the Square Footage row as shown in Table 3 – 11 with the comma format symbol in row 16.
Sum row 16 in cell I16. Use the Format Painter button (Home tab | Clipboard group) to format
cell I16. Change the height of row 16 to 42.00. Vertically center the range A16:I16 through the use
of the Format Cells dialog box.
6. Enter the remaining row titles in the range A7:A17 as shown in Figure 3–89. Increase the font size
in cells A7, A14, and A15 to 14 point.
7. Copy the row titles in range A8:A13 to the range A18:A23. Enter the numbers shown in the range
B18:B23 of Figure 3–89 with format symbols.
8. The planned indirect expenses in the range B18:B23 are to be prorated across the branch offi ce
as follows: Administrative (row 8), Energy (row 10), and Marketing (row 13) on the basis of Total
Donations (row 4); Depreciation (row 9), Insurance (row 11), and Maintenance (row 12) on the
basis of Square Footage (row 16). Use the following formulas to accomplish the prorating:
a. Chicago Branch Offi ce Administrative (cell B8) = Administrative Expenses * Chicago Branch
Offi ce Total Donations / ReachOut Neighbors Total Donations or =$B$18*B4/$I$4
b. Chicago Branch Offi ce Depreciation (cell B9) = Depreciation Expenses * Chicago Branch Offi ce
Square Footage / Total Square Footage or =$B$19*B16/$I$16
c. Chicago Branch Offi ce Energy (cell B10) = Energy Expenses * Chicago Branch Offi ce Total
Donations / ReachOut Neighbor Total Donations or =$B$20*B4/$I$4
d. Chicago Branch Offi ce Insurance (cell B11) = Insurance Expenses * Chicago Branch Offi ce
Square Footage / Total Square Footage or =$B$21*B16 /$I$16
e. Chicago Branch Offi ce Maintenance (cell B12) = Maintenance Expenses * Chicago Branch
Offi ce Square Footage / Total Square Footage or =$B$22*B16/$I$16
f. Chicago Branch Offi ce Marketing (cell B13) = Marketing Expenses * Chicago Branch Offi ce
Total Donations / ReachOut Neighbor Total Donations or =$B$23*B4/$I$4
g. Chicago Branch Offi ce Total Indirect Expenses (cell B14) = SUM(B8:B13)
h. Chicago Branch Offi ce Net Income (cell B15) = Total Donations - (Distributed Goods and
Services + Direct Expenses + Total Indirect Expenses) or =B4-(B5+B6+B14)
i. Copy the range B8:B15 to the range C8:H15.
j. Sum the individual rows 8 through 15 in the range I8:I15.
9. Add a thick bottom border to the range B13:I13. Assign the Currency style with two decimal places
and show negative numbers in parentheses to the following ranges: B4:I4; B8:I8; and B14:I15.
Assign the Comma style with two decimal places and show negative numbers in parentheses to the
following ranges: B5:I6 and B9:I13.
10. Change the font in cell A1 to 48-point Britannic Bold (or a similar font). Change the font in cell
A2 to 22-point Britannic Bold (or a similar font). Change the font in cell A17 to 18-point italic
Britannic Bold.
11. Use the background color Green, Accent 1, Lighter 40% and the font color Tan, Background 2,
Darker 75% for cell A7 and the ranges A1:I2; A15:I15; and A17:B23 as shown in Figure 3–89.
12. Insert a Sparkline Win/Loss chart for the range B8:H8 in cell J8. Copy the cell J8 to the cell
range J9:J13.
Continued >
ADVERTISEMENT
0 votes
Related Articles
Related forms
Related Categories
Parent category: Education