Excel Case

BACKGROUND

Many years ago, your state’s lawmakers established a pension fund for the state’s public school teachers. Under the plan, teachers contribute a portion of their salary each year to the fund. The state is obligated to contribute each year as well. The fund’s assets are invested in stocks and bonds; earnings on the investments are put back into the fund. A retired teacher’s pension is paid out of fund assets for the rest of the teacher’s life. This kind of retirement plan is very common in the United States for public employees, including public school teachers.

Key aspects of the teacher retirement plan are negotiated between state officials and the teachers’ union:

· The amount that teachers will contribute to the fund each year

· The amount that the state will contribute to the fund each year

· The formula used to compute the yearly pension payout to retirees

Teachers currently contribute 9.5 percent of their salary to the fund each year. The state agrees to donate 2.5 times as much as the teachers contribute.

A retired teacher’s pension is equal to 2.2 percent multiplied by the teacher’s salary in his or her final year of work multiplied by the teacher’s number of years of service. Thus, a teacher who worked many years and had a high final salary will have a greater pension than a teacher who did not work as many years and had a lower salary in the final year. For example, a teacher who worked 25 years and had a final salary of $100,000 would get a yearly payment of .022 × $100,000 × 25, or $55,000.

In addition, the teachers’ union negotiated some inflation protection for retirees years ago. A 3 percent cost of living adjustment is added to the base payout. Continuing the previous example, the teacher’s yearly pension payout would be 1.03 × $55,000, or $56,650. The next year, the teacher’s pension payout would be 1.03 × $56,650, and so on, as long as the retired teacher receives payouts.

The financial health of a pension fund is assessed by comparing (1) the value of the fund’s assets with (2) the amount of benefits the fund is obligated to pay:

1. The value of the assets is relatively easy to compute because stocks, bonds, and other instruments usually have market values for quick reference.

2. The amount of benefits the fund is obligated to pay is harder to estimate. Typically, a 30-year horizon is assumed. The number of covered pensioners is estimated for each year along with the estimated payout for pensioners each year. The present value of each year’s estimated obligation is computed. The total of the 30 present values is the estimated total obligation, stated in today’s dollars.

3. If the value of the assets is equal to or greater than the present value of the fund’s obligations, the fund is said to be fully funded. If the value of the assets is less than the present value of the fund’s obligations, the fund is said to be underfunded. In that case, the difference between the present value of the obligations and the assets’ value is called the net present value (NPV) of the unfunded liability. The NPV is a measure of how far a pension plan is “in the hole.”

Your state’s teacher pension fund is thought to be underfunded. In the short term, the problem is not critical—there is enough money in the fund to pay benefits this year and the next few years. However, as time goes on, the fund will not have enough money. The pension payments are a contractual obligation for the state, so the problem must be addressed.

Various factors have contributed to this problem:

· The state assumes that the fund will earn, on average, 7.5 percent of the value of the assets invested each year. However, the financial markets have been volatile in recent years. The average annual rate of return for the last decade has been less than 7.5 percent.

· The state has not always had enough money to make its yearly contribution. For example, the total of the teachers’ payroll deductions might be $1 billion, in which case the state would be obligated to pay $2.5 billion into the fund. However, what if the state has more immediate priorities? In several years during the past decade, the state has not written a check for the amount it should have written to the fund.

· Recall that the retirement benefit is a function of the teacher’s final annual salary. State officials say that some teachers take on additional tasks in their last year of work to elevate their final annual salary. For example, some teachers might decide to teach summer school. Also, some school districts have “extra pay for extra work” rules. For example, a teacher might decide to be the chairperson of math instruction at his or her school and earn extra money in the final year before retirement. State budget officials estimate that the average teacher earns about $4,000 more in the final year of work than in previous years. State officials want the final salary amount to be reduced, a process that might require union contract negotiation. This reduction would be called the “give-back.” For example, a teacher’s final salary for pension purposes might be the actual final salary minus $2,000.

· The 3 percent cost of living add-on is an irritant to state budget officials, but union representatives point out that the adjustment remains at 3 percent even in years in which inflation is actually higher. State officials counter by saying that the pension plan was not set up to adjust for inflation, and that 3 percent compounded yearly becomes a lot of money. State budget officials think the plan would be much healthier if the cost of living adjustment did not exist.

· People are living longer these days. On average, a retired teacher in the state draws benefits for 20 years. The plan may not be able to support longer lifespans.

· A decade ago, state education officials launched “productivity” programs with the goal of educating the same number of students with fewer teachers. For example, schools were asked to make better use of technology to deliver educational content. Also, after painful negotiations with the teachers’ union, work rules were changed so that administrators could more easily remove incompetent teachers. “Early out” bonuses were put in place to encourage veteran teachers to retire. These productivity programs have been somewhat successful, and the number of teachers has been declining by about 0.5 percent per year.

The teachers’ union is quite large, and its members vote. Union officials are questioning the pension plan’s viability in meetings with state legislators, who are now convinced the plan is underfunded. A recently

passed resolution requires the state to act as quickly as possible to restore the financial health of the teachers’ pension fund. The resolution specifies that the ratio of the fund’s assets to the present value of the fund’s obligations must be raised to at least 80 percent.

The state’s budget director has called you in to help analyze the situation. She says she knows the plan is underfunded but does not know how bad the situation is. An Excel model is needed to help her understand the dimensions of the problem and decide how to try to change the plan for the better.

“I do not know where they came up with the 80 percent rule, but I suppose it’s not a bad benchmark,” she tells you. “We need to figure out a way to get there. I know you are good with Excel models. Run the numbers, and then let’s see where we are.”

Assignment 1: 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.

FIGURE 10-1 Constants section

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 hard- code), 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

ASSIGNMENT 2: USING THE SPREADSHEET FOR DECISION SUPPORT

You will now complete the case by (1) using the spreadsheet model to gather data needed to answer the budget director’s questions about the plan, (2) documenting your findings in a memo, and (3) giving an oral presentation if your instructor requires it.

Assignment 2A: 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.