CREATING A SPREADSHEET FOR DECISION SUPPORT In this assignment, you produce a spreadsheet that models the problem

Subject:Computer SciencePrice: Bought3

Share With

CREATING A SPREADSHEET FOR DECISION SUPPORT

In this assignment, you produce a spreadsheet that models the problem. Then, in Assignment 2, you will use the spreadsheet to gather data and 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 and is available for you to use; it will save you time. If you want to use the spreadsheet skeleton, locate Case 10 in your data files and then select TeachersPensionFund.xlsx. Your worksheet should contain the following sections:

• Constants
• Inputs
• Summary of Key Results
• Calculations
• Fund Balance Statement
• Fund Liability

A discussion of each section follows.

 

Constants Section

Your spreadsheet should include the constants shown in Figure 10-1. An explanation of the line items follows the figure.

 

• Retiree Years of Service—On average, teachers work for 25 years before retiring.
• Average Increase in Teacher Salary—Teacher salaries are expected to increase by an average of 1 percent each year for the next 30 years.
• Retiree Rate—On average, 4 percent of teachers are expected to retire each year in the next 30 years.
• Mortality Rate—On average, a pensioner receives payouts for 20 years. On average, 5 percent of pensioners are expected to die each year.
• Expected Average Final Salary—The average final salary for teachers retiring in 2016 was $82,000. The average final salary is expected to increase somewhat each year, as shown.
• Expected Administrative Expense—The pension fund has employees, rents office space, consults with experts in securities markets about investments, and has other expenses. The plan’s administrative cost is expected to be $25 million in 2017 and to increase each year, as shown.

Inputs Section

Your spreadsheet should include the inputs shown in Figure 10-2. Possible values are shown in the figure.

Each of the inputs applies to each of the 30 years modeled. An explanation of the line items follows the figure.

 

• Cost of Living Adjustment—By union contract, this adjustment is 3 percent. Ideally, plan administrators would like to negotiate this percentage lower.
• Long Term Rate of Return—A 7.5 percent return on investments is assumed. Plan administrators want to see the effects of changing this variable.
• Productivity Factor—The total number of teachers has been declining by 0.5 percent each year in recent years. State officials hope for greater productivity in the future.
• Employee Contribution Rate—Working teachers contribute 9.5 percent of their salary to the pension fund. Some state officials think this rate must increase in the future.
• Final Salary Give Back—State officials want a reduction in the final salary for pension purposes. The reduction would be called the “give-back.”
• State Contribution Factor—By contract, the state contributes 2.5 times what the teachers contribute. This factor may need to be increased to ensure there is enough money to pay pensions.

Summary of Key Results Section

Your worksheet should include the key results shown in Figure 10-3. An explanation of the line items follows the figure.

 

• NPV of Unfunded Liability—The NPV of the pension fund’s unfunded obligation is computed elsewhere in the spreadsheet and can be echoed here.
• Ratio of Assets to Liability NPV—The ratio of the value of fund assets to fund liabilities is computed elsewhere in the spreadsheet and can be echoed here.

Calculations Section

The Calculations section is shown in Figure 10-4. Some 2016 values are provided. Values for 2017 through 2046 are calculated by formula. Use cell addresses when referring to constants in formulas unless otherwise directed. Use absolute addressing properly. An explanation of the line items follows the figure.

 

• Average Teacher Salary—The average in a year is a function of the prior year’s value and the expected rate of increase in the year. The latter value is from the Constants section.
• Number of Active Teachers—This amount is a function of the prior year’s value and the expected “productivity factor.” The latter value is from the Inputs section.
• Number of New Retirees—This amount is a function of the number of active teachers in the prior year (from the previous row) and the retiree rate for the year (from the Constants section).
• Number of Retirees—The number of retirees in a year is the number of retirees in the prior year plus the number of new retirees in the year, minus the number of retirees who die in the year. The number of retirees who die is a function of the number of retirees in the prior year and the year’s mortality rate. The latter value is from the Constants section.
• Total Teacher Compensation—This amount is a function of the average teacher salary in the year and the number of active teachers. Both values are from the Calculations section.
• Employee Contribution to Fund—This value is a function of total teacher compensation (from the previous row) and the contribution rate (from the Inputs section).
• State Contribution to Fund—This value is a function of the employee contribution (from the previous row) and the state contribution factor (from the Inputs section).
• Average Retiree Benefit—The average retiree payout in a year is a function of the expected final salary in the year (from the Constants section), the .022 payout rate (a factor you can hardcode), and the expected years of service (from the Constants section). This amount should be increased by the expected cost of living factor and then reduced by any give-back amount; both values are from the Inputs section.
• Expected Benefits Payout—The total benefits to be paid in a year is a function of the average retiree benefit and the number of retirees in a year. Both values are from the Calculations section.

Fund Balance Statement Section

This section shows a calculation of the pension fund balance at the end of each year, as illustrated in Figure 10-5. The pension fund’s balance is increased by employee contributions, state contributions, and earnings on fund assets. The pension fund’s balance is decreased by benefits paid and administrative expenses. An explanation of the line items follows the figure.

 

• Beginning Balance—The balance at the beginning of a year equals the balance at the end of the prior year.
• Add: Employee Contribution—This amount has been calculated elsewhere in the spreadsheet and can be echoed here.
• Add: State Contribution—This amount has been calculated elsewhere and can be echoed here.
• Add: Income on Investments—This amount equals the fund balance at the beginning of the year multiplied by the expected earnings rate. The latter value is from the Inputs section.
• Less: Benefits Payout—This amount has been calculated elsewhere and can be echoed here.
• Less: Administrative Expenses—This amount is taken from the Constants section and can be echoed here.
• Ending Balance—This amount equals the beginning balance plus the employee contribution, the state contribution, and income on investments, minus the benefits paid and administrative expenses.

Fund Liability Section

This section shows a calculation of the NPV of the pension fund’s unfunded liability and the ratio of fund assets to this NPV, as illustrated in Figure 10-6. An explanation of the line items follows the figure.

 

• Expected Benefits Payout—The fund’s payout in each year has been calculated elsewhere in the spreadsheet and can be echoed here. The series of values will be used in the NPV calculation.
• Net Present Value of Payouts—The NPV of a series of values is calculated using a discount rate applied to those values. Apply the NPV function to the series of expected benefit payouts using .075 as the discount rate. You can hard-code the discount rate.
• NPV of Unfunded Liability—This value is the NPV of payouts minus the fund balance at the end of 2017.
• Ratio of Assets to Liability NPV—This value is the ratio of the fund balance at the end of 2017 to the NPV of payouts.

 

Using the Spreadsheet to Gather Data

You have built the spreadsheet to create “what-if” scenarios for the model’s input values. The inputs represent the logic of a question and the outputs provide information needed to answer the question. The budget director’s questions are discussed next.

Question 1 (Base Case)

The budget director asks, “What are the net present value of the unfunded liability and the ratio of assets to the net present value of the unfunded liability, given the current situation? This is the ‘base case.’ How bad are things right now?” The inputs for the base case are shown in Figure 10-7.

 

Enter the inputs and then observe the outputs in the Summary of Key Results section. Next, manually record the results in a summary area. You could use a second worksheet for this purpose, as shown in Figure 10-8 (values shown are for illustration only).

 

Question 2 (Worst Case)

The budget director says, “In the worst case, we cannot do anything about the cost of living adjustment, the stock market tanks, and we earn very little—say 3 percent. Productivity goes to zero and other factors remain the same. That is the ‘worst case.’ How bad would that be?” The inputs for the worst case are shown in Figure 10-9.

 

Enter the inputs and then observe the outputs in the Summary of Key Results section. Next, manually record the results in the summary area.

Question 3 (Aggressive Case)

The budget director says, “In my dreams, I take an aggressive line with the union and I win the battles. The cost of living adjustment is reduced to 1 percent. The productivity factor doubles to 1 percent. The employee contribution rate is increased to 10 percent. The salary give-back is $4,000, and the stock market comes back, so we earn 10 percent on our money. That is the ‘aggressive case.’ How good would things be? Surely the ratio gets to 80 percent then!” The inputs for the aggressive case are shown in Figure 10-10.

 

Enter the inputs and then observe the outputs in the Summary of Key Results section. Next, manually record the results in the summary area.

Question 4 (Rescue Case)

The budget director says, “I know the governor is going to ask what the state would have to do to bail out the current system. So, assume the conditions of the base case, except for the state contribution factor.” Run a “what-if” scenario with that factor until you reach a ratio of 80 percent. How big a factor is needed? Call this question the “rescue case.” How much extra money would the state have to contribute versus the base case contribution by the state? The inputs for the rescue case are shown in Figure 10-11.

 

Enter the inputs and then observe the outputs in the Summary of Key Results section. The extra dollar amount that the state would contribute can be calculated by comparing state contribution amounts in the Calculations section in the two scenarios. Next, manually record the results in the summary area.

When you finish gathering data for the four questions, print the model’s worksheet with any set of inputs. Print the summary sheet data as well, and then save the spreadsheet for the final time.

 

Documenting your Findings and Recommendation in a Memo

Document your findings in a memo that answers the budget director’s four questions. The memo should also state your more general assessment of the fund’s financial position: How bad or good is the situation? Use the following guidelines to prepare your memo in Microsoft Word:

• Your memo should have proper and standard headings, such as Date, To, From, and Subject. You can address the memo to the administrators of the state pension fund. Set up your memo as described in Tutorial E.
• Briefly outline the situation. However, you need not provide much background—you can assume that readers are familiar with the situation.
• Answer the four questions in the body of the memo.
• Include tables and charts to support your claims, as your instructor specifies. Tutorial E explains how to create a table in Microsoft Word. Tutorial F explains how to create charts in Excel.

 

GIVING AN ORAL PRESENTATION

Assume that the budget director asks you to be ready to present your analysis and results in an oral presentation to some key legislators. “These guys are always looking for the silver bullet—you know, trying to fix the problem by changing only one thing. So, they want to see sensitivity data and they want it in chart format,” she tells you. “For example, someone will want to know how much the asset-to-liability ratio would change if there was a change in the market rate of return. So get those kinds of charts ready.”

Prepare to talk to the group for 10 minutes or less. Tutorial F explains how to prepare and give an oral presentation.

Your instructor will tell you what sensitivity analyses to prepare or may tell you to choose these analyses yourself. The example chart to which the budget director referred should look like the one shown in Figure 10-12.