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
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.
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
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
High School Grad Salary
The same logic used to compute the College Grad Salary is applied to
calculate the High School Grad Salary.
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.
Net Present Value Calculations Section
This section starts with the row labeled
as shown in Figure 10-5. The values shown are for illustrative
Net Present Value Calculations section
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