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.

This section provides guidelines for the four questions that your spreadsheet can answer for you.

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

’

Data About the College:

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%

College Grad Salary & Benefits

50,000

Data About You:

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