Microsoft Office Tutorials and References

In Depth Information

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

Books And Supplies

The logic used for Tuition and Fees is applied here for books and supplies.

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

—

High School Grad Salary Foregone

Students in college are not earning a salary as working high

school graduates. Thus, the salary foregone is a cost of attending college. Again, assume that the

student is a high school senior and will attend college in the following year. Thus, the salary

foregone in Year 1 is the current high school graduate

—

’

s salary multiplied by (1

the high school

þ

graduate

’

s expected salary percentage increase for the work region). The Year 2 amount is (1

þ

the expected percentage increase) multiplied by the Year 1 amount, and so on. 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.

Total

—

The total for a year is the sum of all the costs in the year.

College Grad Salary

The dashes in the initial six cells indicate that they will not contain

formulas or values. Assume that the working career will last 30 years after graduation; only two

years of that period are shown in Figure 10-4. Year 1 refers to the first year of work, Year 2 to

the second year, and so on. Values in years 2 through 30 are equal to the prior year

—

’

s value

multiplied by (1

the percentage increase in the College Grad Salary & Benefits from the Inputs

section). Assume that the student is a high school senior and will attend school in the following

year. Thus, the working Year 1 value is the College Grad Salary & Benefits for the current year

multiplied by (1

þ

the expected College Grad Salary Increase from the Calculations section),

raised to the proper exponent. For example, if the student will be in school for four years, the

current salary is $50,000, and the expected rate of increase in salary is .03, the working Year 1

value would equal $50,000 * ((1

þ

.03)^5).

þ

High School Grad Salary

The same logic used to compute the College Grad Salary is applied to

calculate the High School Grad Salary.

—

Difference

This value is the difference between College Grad Salary and

High School Grad Salary. Presumably, the college graduate value exceeds the high school

graduate value, so the difference is a positive value that indicates a return on the investment.

College Advantage

—

¼

Net Present Value Calculations Section

This section starts with the row labeled

“

NET,

”

as shown in Figure 10-5. The values shown are for illustrative

purposes only.

FIGURE 10-5

Net Present Value Calculations section

Net

This row captures the series of investment and return cash flows. Later, the NPV

calculation needs a series that starts with negative investment values, so the college year investment

values are multiplied by

—

–

1 in this row. The work year values are simply echoed from the

preceding Difference row.

NPV At Low Risk Investment Rate

s Help, the NPV function calculates the

present value of an investment using a discount rate and a series of future investments (negative

—

According to Excel

’