What-If Analysis, Charting, And Working With Large Worksheets Page 83
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 219
3. Delete rows 11 through 13 to remove the statistics below the Totals row. Change all the row
heights back to the default height (12.75).
4. Insert four rows above row 1 by selecting rows 1 through 4, right-clicking the selection, and
clicking Insert on the shortcut menu.
5. Change the row heights as follows: row 5 = 48.00; row 6 = 25.50; and 7 = 38.25. For the range
B7:I7, change the format so that the text wraps. Center the range B7:I7.
6. Delete column B by right-clicking the column heading and clicking Delete on the shortcut menu.
7. Insert a new column between columns D and E. Change the column widths as follows: A = 25.00;
E = 13.00; and F through I = 9.71. Enter the new column E title Suggested Reorder in cell E7.
8. Insert two new columns between columns F and G. Enter the new column G title Last Month
Sales in cell G7. Enter the new column H title Current Month Sales in cell H7.
9. Enhance the worksheet title in cell A5 by using a 36-point purple Cooper Black (or a similar font)
font style as shown in Figure 3–91b.
10. Assign the NOW function to cell B6 and format it to the 3/14/2001 style.
11. Delete item Car Air Freshener (row 13). Change Mini Jar Candle’s (row 12) cases on hand to 6.
Change Large Candle’s (row 8) items per case to 8 and cost per case to $131.00. Change Incense
Stick’s (row 10) sale price per item to $2.00 and Incense Holder’s (row 9) sale price per item to
$4.75.
12. Freeze column A and rows 1 through 7 by selecting cell B8, clicking the Freeze Panes button
(View tab | Window group), and then clicking Freeze Panes on the Freeze Panes gallery.
13. In columns G and H, enter the current month and last month sales values listed in Table 3 –13.
14. Insert three new rows immediately above the T otals row. Add the new items data as listed in Table 3–14.
Table 3 – 13 Dinah’s Candle Depot Monthly Sales Values
Inventory Item
Last Month Sales
Current Month Sales
Large Candle
851.6
1703.2
Incense Holder
37.5
56.25
Incense Stick
90
100
Candle Topper
390
260
Mini Jar Candle
1208
1208
Table 3 – 14 Dinah’s Candle Depot New Items
Inventory
Cases On
Items Per
Reorder
Order
Last
Current
Cost Per
Sale Price
Item
Hand
Case
Level
Time
Month
Month
Case
Per Item
(Weeks)
Sales
Sales
Votive Candle
3
18
4
1
300
450
20
1.5
Taper Candle
2
16
3
2
350
612.5
35
3.5
Candle
2
5
1
3
638
797.5
100
15.95
Warmer
15. Center the range B8:F15. Use the Currency category in the Format Cells dialog box to assign a
Comma style (no dollar signs) with two decimal places and negative numbers within parentheses
to the range G8:K16. Draw a thick bottom border in the ranges A7:K7 and A15:K15.
16. As shown in Figure 3–91b, enter and format the Increase Reorder Amt (2), the Decrease Reorder
Amt (1), and the Sales Threshold % (25%) information in the range A1:B3. Use format symbols
where applicable.
Continued >
ADVERTISEMENT
0 votes
Related Articles
Related forms
Related Categories
Parent category: Education