Microsoft Office Tutorials and References

In Depth Information

**ASSIGNMENT 1: CREATING A SPREADSHEET FOR DECISION SUPPORT**

NPV

—

This value is the net present value of the investment

’

s cash flows, discounted using the

investment rate from the Inputs section.

Was It Worth It?

The investment is worthwhile if the NPV is positive; otherwise, the

investment is not worth it.

—

Total Cost Of Attending College

—

This value is the undiscounted sum of all college costs.

Total Benefit Of Working Career

—

The difference between the college graduate

’

s salary and the

high school graduate

s salary is calculated for each year of a working career. This value is the

undiscounted sum of those differences.

’

Calculations Section

This section calculates the values shown in Figure 10-3. Values are calculated by formula, not hard-coded.

The values are then used in other calculations that follow. An explanation of the line items follows the figure.

FIGURE 10-3

Calculations section

High School Grad Salary Increase

The expected work region is an input. Expected annual

salary and benefit increases for high school graduates are: NE, 1%; SE, 3%; SW, 4%; NW, 1%;

MID, 2%.

—

College Grad Salary Increase

Expected annual salary and benefit increases for college graduates

are: NE, 2%; SE, 4%; SW, 5%; NW, 3%; MID, 4%.

—

Cost of Attending College and Working Career Inflows Sections

This section is the spreadsheet body, as shown in Figure 10-4. Values shown are for illustrative purposes only.

Investment outflows are computed in the Cost of Attending College section, and investment inflows are

computed in the Working Career Inflows section. An explanation of the line items follows the figure.

FIGURE 10-4

Cost of Attending College and Working Career Inflows sections

Tuition And Fees

Assume that the student is a high school senior and will attend college in

the following year. Thus, the Year 1 tuition and fees is the current year amount multiplied by

(1

—

the expected percentage

increase) multiplied by the Year 1 amount, and so on. In the example shown, the student

expects to be in college for four years, so the dashes represent zeroes in years 5 and 6. Positive

values are shown for years 5 and 6 if the student enters 5 or 6 for the number of years in

college. Thus, an IF statement is needed in the Year 5 and Year 6 cells.

the expected percentage increase). The Year 2 amount is (1

þ

þ

Room And Board

The logic used for Tuition and Fees is applied here for room and board.

Again, IF statements are needed for the Year 5 and Year 6 cells.

—