What-If Analysis, Charting, And Working With Large Worksheets Page 85
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 221
Cases and Places
Apply your creative thinking and problem solving skills to design and implement a solution.
1: Bachelor Degree Expense and Resource Projection
Academic
Attending college with limited resources can be a trying experience. One way to alleviate some of
the fi nancial stress is to plan ahead. Develop a worksheet following the general layout in Table 3 –16
that shows the projected expenses and resources for four years of college. Use the formulas listed in
Table 3–17 and the concepts and techniques presented in this chapter to create the worksheet.
Table 3 – 16 Bachelor Degree Expense and Resource Projection
Expenses
Freshman
Sophomore
Junior
Senior
Total
Room & Board
$12,550.00
Formula A
—
Tuition & Books
16,450.00
Formula A
—
Clothes
785.00
Formula A
—
Entertainment
1,520.00
Formula A
—
Miscellaneous
936.00
Formula A
—
Total Expenses
—
—
—
—
—
Resources
Freshman
Sophomore
Junior
Senior
Total
Savings
Formula B
Parents
Formula B
—
Job
Formula B
—
Loans
Formula B
—
Scholarships
Formula B
—
Total Resources
—
—
—
—
—
Assumptions
Savings
10.00%
Parents
12.00%
Job
11.00%
Loans
35.00%
Scholarships
32.00%
Annual Rate
8.25%
Increase
Table 3 – 17 Bachelor Degree Expense and Resource Projection Formulas
Formula A = Prior Year’s Expense * (1 + Annual Rate Increase)
Formula B = Total Expenses for Year * Corresponding Assumption
After creating the worksheet: (a) perform what-if analysis by changing the percents of the resource
assumptions; (b) perform a what-if analysis to determine the effect on the resources by increasing the
Annual Rate Increase to 9.95% (answer = $149,520.41); and (c) with the original assumptions, goal seek
to determine what the Annual Rate Increase would be for the total expenses to be $175,000 (answer =
20.77%). Submit the workbook and results of the what-if analysis as specifi ed by your instructor.
Continued >
ADVERTISEMENT
0 votes
Related Articles
Related forms
Related Categories
Parent category: Education