Microsoft Office Tutorials and References

In Depth Information

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

the student might think she needs five years to complete a degree, and that she might choose to

work in the U.S. Southwest, where pay rate increases have recently been better than in the

Northeast.

Low-risk investment rate

Investment cash flows are negative at first because of the college

investment costs, but they are followed by positive cash flows due to yearly returns. To compute

the current value, you must discount yearly cash flows back to the present using an appropriate

rate of interest. For example, say that you are promised $106 one year from now, and that you

can reliably earn 6% on your investments. The current value of the $106 to be received one year

from now is $100. In the college investment model, money is invested for four or more years,

followed by returns for a 30-year working career or more. For most people, an appropriate

lowrisk interest rate would be the rate on long-term U.S. bonds. This rate typically has been about

4%, although in recent years it has been lower.

—

Data about the region

Given the region the student wants to work in, the model must use the

proper rate of salary increase both for college and high school graduates.

—

Cost of attending college

Given the costs of the target school, the related rates of increase, and

the expected number of years needed to complete the degree, the model would compute the

yearly total cost of attendance. These values are the investment cash flows.

—

Cash flows in working career

Compute the yearly salaries of a college graduate and a high

school graduate under the same circumstances, and compute the yearly differences. The

differences are the investment cash inflows.

—

Computed net present value

Given the investment costs, investment inflows, and interest rate,

compute the present value. A positive value indicates that the investment in college was

worthwhile. A negative value indicates that the investment was not worthwhile.

—

ASSIGNMENT 1: CREATING A SPREADSHEET FOR DECISION SUPPORT

In this assignment, you will produce a spreadsheet that models the problem. Then, in Assignment 2, you will

write a memorandum that explains your findings. In Assignment 3, you may be asked to prepare an oral

presentation of your analysis.

A spreadsheet has been started for you, and is available for you to use. If you want to use the spreadsheet

skeleton, go to your data files, select Case 10, and then select CollegeROI.xlsx. Your worksheet should have the

following sections:

Inputs

Summary of Key Results

Calculations

Cost of Attending College and Working Career Inflows

Net Present Value Calculations

A discussion of each section follows.

Inputs Section

Your spreadsheet should have the inputs shown in Figure 10-1. Note that the values shown are illustrative

only. An explanation of the line items follows the figure.