Microsoft Office Tutorials and References
In Depth Information
ASSIGNMENT 2: USING THE SPREADSHEET FOR DECISION SUPPORT
numbers) and returned income (positive values). The syntax is
,
Value N). The rate is an annual rate, and can be a cell reference; in the model, it is an input cell.
Value1
NPV(rate, Value1, Value2,
¼
Value N need not refer to contiguous cell references. Here, the investment values and
return values are in the Net row. This row will contain values of zero if the student completes
college in less than six years, as illustrated in Figure 10-5. You cannot refer to cells that contain
values of zero or to empty cells when calculating the NPV. Thus, you need to create an IF
statement and key it to the number of years in college. For example, if the student graduates in four
years, the list of cells would refer to C42:F42, I42:AL42. Note that if the NPV is positive, the
investment was a good one at the investment rate specified; the discounted benefits received
exceeded the discounted costs incurred. But, if the NPV is negative, the investment was not a
good one at the investment rate specified; the discounted benefits received were less than the
discounted costs incurred.
￿
Total Cost Of Attending College
This value is the undiscounted sum of the costs of attending
college. Note that the SUM function ignores empty or zero-valued cells.
￿
Total Benefit Of Working Career
This value is the undiscounted sum of the Difference cells in
the 30 working years.
￿
Was College Worth It?
An IF statement is needed. You will see an output of
YES
if the NPV is
positive, and
NO
if the NPV is negative. In the example in Figure 10-5, the investment was not
worthwhile.
ASSIGNMENT 2: USING THE SPREADSHEET FOR DECISION SUPPORT
You will now complete the case by using the spreadsheet model to gather data needed to answer four
questions that a student might have about the value of a college degree. Next, you will document your
findings in a memorandum. If your instructor requires it, you will also give an oral presentation.
Assignment 2A: Using the Spreadsheet to Gather Data
You have built the spreadsheet to develop
s input values. The inputs
represent the logic of a question, and the outputs provide information needed to answer the question.
Question 1: What is the financial impact of taking more than four years to complete the college degree?
In other words, is there really a serious financial penalty for staying in school for an extra year or two? The
inputs for answering this question are shown in Figure 10-6.
what if
scenarios using the model
Tuition and Fees
20,000
Room and Board
12,000
Books and Supplies
5,000
Cost Increase: Tuition and Fees
10%
Cost Increase: Room and Board
8%
Cost Increase: Books and Supplies
7%
50,000
NumberofYearsinCollege(4,5,or6)
4,5,6
Work Region (NE, SE, NW, SW, MID)
NE
High School Grad Salary & Benefits
35,000
Low Risk Investment Rate (XX)
1%, 4%, 7%
FIGURE 10-6
Question 1 input data

Search JabSto ::

Custom Search