With this case study, which is an adapted version of Case 10: The State Pension Fund Analysis in Monk et al (2017) you will use Microsoft Excel to develop a best practice decision support system for the Director-General of the West Australian Department of Education, Ms Anne Nolan.
Background
In Western Australia, the state government has a single superannuation fund, known as the Western Australian Teachers Superannuation Fund (an industry super fund) for the state’s public-school teachers. Many observers both inside and outside of the government think the superannuation fund is financially weak.
You have therefore been asked to use Microsoft Excel to analyse the superannuation fund’s financial condition and to report back to the Director General. Ms Nolan knows the plan is underfunded but does not know how bad the situation is.
A Microsoft 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 at.”
The superannuation fund was established quite a few years ago. Key points of the superannuation fund are as follows:
- Under the plan, teachers contribute a portion of their salary each year to the fund.
- The state government in return is obligated to contribute each year as well.
- The superannuation fund’s assets are invested in the share market and in the bond market (government bonds);
- Earnings on the investments are reinvested back into the superannuation fund.
- A retired teacher’s defined pension (this is not the Commonwealth funded aged pension) is paid out of superannuation fund assets for the rest of the teacher’s life.
- Key aspects of the teacher retirement plan are negotiated between state government officials and the state teachers’ union. These key aspects include:
- The amount that teachers will contribute to the fund each year;
- The amount that the state government will contribute to the fund each year;
- The formula used to compute the yearly defined pension payout to retirees;
- Teachers currently contribute 9.5 percent of their salary to the fund each year;
- The Western Australian state agrees to contribute 2.5 times the amount the teachers contribute;
- A retired teacher’s pension is equal to 2.4 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;
- In addition, the teachers’ union negotiated some inflation protection for retirees as per the enterprise bargaining agreement. A three percent cost of living adjustment is added to the base payout each year increasing the following years defined pension. So, for example if the defined pension in 2017-2018 was $100,000 then the defined pension in the 2018-2019 financial year would be $103,000 and so on.
The financial health of the superannuation fund is determined by comparing (1) the value of the fund’s assets with (2) the amount of benefits the superannuation fund is obligated to pay. The following should be noted in respect of the financial health of the fund.
- The value of the assets is easy to compute because shares, bonds, and other financial instruments usually have market values for quick reference.
- The amount of benefits the fund is required to pay takes into consideration the following.
- Typically, a 30-year horizon is assumed;
- The number of covered superannuants is estimated for each year along with the estimated payout for the superannuant each year;
- The present value of each year’s estimated obligation is computed;
- The total of each of the 30 years present values is the estimated total obligation, stated in today’s dollars;
- If the value of the assets is equal to or greater than the present value of the super fund’s obligations, the super fund is said to be fully funded. If the value of the assets is less than the present value of the super 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 the pension plan is “in the red.”
The superannuation fund is thought to be underfunded. In the short term, the problem is not critical, there is enough money in the super 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 government, so the problem must be addressed.
Various factors have contributed to this problem:
- The state government 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 government has not always had enough money to make its yearly contribution, e.g., during the time the mining boom fell away and the state’s revenues where in significant decline. For example, the total of the teachers’ payroll deductions might be $1 billion, in which case the state government would be obligated to pay $2.5 billion into the fund. However, what if the state government has more immediate priorities? In several years during the past decade, the state government has not paid into the fund at the level which it is contracted to do so.
- Recall that the retirement benefit is a function of the teacher’s final annual salary. Department of Education officers suggest that some teachers take on additional tasks in their last year of work to increase their final annual salary.
- Department of Education officers estimate that the average teacher earns about $4,000 more in the final year of work than in previous years.
- The Department of Education want the final salary amount to be reduced, a process that might require a re-negotiated enterprise bargaining agreement with the teachers’ union. 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 three percent cost of living add-on is an irritant to Department of Education officers, but union representatives point out that the adjustment remains three percent even in years in which inflation is actually higher. Department of Education officers counter this argument by saying that the pension plan was not set up to adjust for inflation, and that a three percent compounded yearly becomes a lot of money.
Department of Finance officers in support of their colleagues in Education 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 superannuation plan may not be able to support longer lifespans. A decade ago, department 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 State School Teachers' Union of W.A work rules were changed so that principals and school boards could more easily remove incompetent teachers.
Redundancy options were also put in place to encourage older 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. That said, the teachers’ union is quite large, and its members vote. Union officials are questioning the pension plan’s viability in meetings with the Minister of Education and Opposition spokesperson for Education, who are now convinced the plan is underfunded.
A recently passed amendment to the Education Act requires the state to act as quickly as possible to restore the financial health of the teachers’ superannuation fund. The amendment 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.
Requirements
The assignment requirements are as specified in this document, not as given in Monk et al (2017). You must complete the assignment as specified here.
Part 1: Creating A Spreadsheet For Decision Support
In this part, you are required to develop an Excel Workbook Decision Support System to facilitate the required modelling as detailed below and requested by the Director-General.
Your workbook should be professionally presented [remember you are providing this workbook and associated report to the Director-General of a major state government department]. The first spreadsheet in the workbook should be your scenario manager analysis and the spreadsheet should be named “SCENARIO”. The second spreadsheet should be your model and it should be named “MODEL”. Any remaining spreadsheets may be used for best practice analysis, developing graphs or tables as required. These additional spreadsheets should have gray tabs.
The initial financial year (your starting year) for this analysis is 2018 and the final year in your modelling should be 2048. The financial year ends on June.
Your modelling worksheet(s) should contain the following sections. Review the textbook and lab activities for the appropriate format. The economic modelling details for each section are provided below.
- Constants
- Inputs
- Summary of Key Results
- Calculations
- Superannuation Fund Balance Statement
- Superannuation Fund Liability
Constants – Core Economic Modelling
The constants are as given by the Director-General or as per your own analysis for the decision support analysis process as discussed in Part 3 of this assignment.
- Years of Service: Teachers on average work 25 years before retiring;
- Salary Increase: Teacher salaries are expected to increase by an average of one percent each year for the next 30 years;
- Retiree Rate: On average four percent of teachers are expected to retire each year in the next 30 years;
- Mortality Rate: On average, retired teachers receive defined pension payouts for 20 years. Five percent of retired teachers are anticipated to pass away each year.
- Final Year Salary: The estimated average final year salary for teachers retiring in 2018 was $96,975. The average final salary is expected to increase each year as outlined in the background section.
- Administration Expense: The superannuation fund administration expense was $4,669,000,000 in 2018 and is expected to increase by one percent per year.
- Payout Rate Factor: A factor set by the superannuation funds actuaries is .024.
- Discount Rate:The discount rate used in NPV calculations is 0.0364
Inputs – Core Economic Modelling
The inputs are as given by the Director-General or as per your own analysis for the decision support analysis process as discussed in Part 3 of this assignment.
- Applicable case: A, B, R or W
- Cost of Living Adjustment: The enterprise bargaining agreement currently has this set at 3 percent.
- Long Term Rate of Return: A 7.5 percent return on investments is assumed.
- Productivity Factor: The current rate of attrition of school teachers is 0.5 percent
- Employee Contribution Rate: Teachers currently contribute 9.5% of their salary but some Department officers believe this may increase in the near future.
- Final Salary Give-Back: Department of Education officers want to introduce reduction in final year salaries which they have called the “give-back”.
- Government Contribution Factor: The state government contributes 2.5 times the value of what the teachers contribute to the superannuation fund each year. This is known as the employer contribution.
Summary of Key Results Section
This is as described here. No changes required or allowed
- NPV of Unfunded Liability: Net Present Value (NPV) of the superannuation fund’s unfunded liability is calculated elsewhere in the spreadsheet and echoed here;
- Ratio of Assets to Liability NPV: The ratio of the value of the superannuation fund assets to the superannuation fund’s liabilities is calculated elsewhere in the spreadsheet and echoed here.
Calculations Section
The calculation values are as given by the Director-General or as per your own analysis for the decision support analysis process as discussed in Part 3 of this assignment. The Director-General does have doubts about these values and is keen to have them reviewed and updated as appropriate.
Some 2018 values are provided below. The remaining values for 2019 through to 2048 are calculated by formula.
- 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 2018 value is $83,200.
- Number of Active Teachers: This amount is a function of the prior’ years value and expected “productivity factor.” The number of active teachers in 2018 is 47,433.
- Number of New Retirees: To be calculated by a formula in the worksheet. The number of retirees in 2018 was 3,786.
- Total Teach Compensation: To be calculated by a formula in the worksheet.
- Employee Contributions to the Fund: To be calculated by a formula in the worksheet.
- Government Contribution to the Fund: To be calculated by a formula in the worksheet.
- Average Retiree Defined Pension: To be calculated by a formula in the worksheet. It is an annual amount. The average retiree defined pension in a year is a function of the expected final salary in the year, the payout rate factor and the expected years of service. This amount should be increased by the expected cost of living factor and then reduced by any give-back amount.
- Expected Defined Benefits: To be calculated by a formula in the worksheet.
Superannuation Fund Balance Statement Section
This section shows a calculation of the superannuation fund balance at the end of the year. We are told that the ending balance as at financial year end 2018 is $7 billion. You need to determine the relevant elements and formulas and to complete this section satisfactorily.
Superannuation Fund Liability Section
This section shows a calculation of the Net Present Value (NPV) of the superannuation unfunded liability and the ratio of super fund assets to this NPV.
The elements of this section are:
- Expected Defined Benefits: As previously calculated.
- NPV of Defined Benefits Payout: To calculated using the appropriate formula in Microsoft Excel. The discount rate is as given.
- NPV of Unfunded Liability: This value is the NPV of the defined benefits less the superannuation fund balance at the financial year end of 2018.
- Ratio of Assets to Liability NPV: This value is the ratio of the fund balance at the end of 2018 to the NPV of defined benefits payout.
Part 2: Using The Spreadsheet For Decision Support
Using the appropriate tools in Microsoft Access, you need to run four “what-if” scenarios to address the Director-General’s questions. The information given in this document is what the Director-General provided and reflects the minimum level of analysis acceptable to the Director-General.
However, if you are ambitious and wish to progress rapidly within the Department of Education, the Director-General would be very impressed by your additional analysis and modifications of the input values to reflect real economic data in the state of Western Australia as at 2017/2018 and beyond. In such a situation the following three cases could be modified: Case 2: Worst Case, Case 3: Aggressive Case and Case 4: Rescue Case. Case 1: Base Case should remain as given and should form the base scenario for any revisions proposed.
You may also which to review the veracity of the constants and calculations and to recommend amendments. Any effects of changes can be modelled and discussed in your internal report. In such a situation your workbook should contain a second modelling spreadsheet called MODEL 2. Please do not change the constants or calculations in your first model spreadsheet as your base case results would be incorrect.
The Director-General provided the four scenarios: Base case (B), worst case (W), aggressive case (A) and rescue case (R):
Case 1: Base Case (B)
The Director-General asks, “What are the net present value of the unfunded liability and the ration of assets to the net present value of the unfunded liability given the current situation? The inputs for the base case are shown below:
|
Cost of Living Adjustment:
|
0.03
|
|
Long Term Rate of Return
|
0.075
|
|
Productivity Factor:
|
.005
|
|
Employee Contribution Rate
|
.095
|
|
Final Salary Give-Back:
|
-
|
|
Government Contribution Factor:
|
2.5
|
The analysis should be done on the base case as given. No amendments are allowed.
Case 2: Worst Case (W)
The Director-General asks, “In the worst case, we cannot do anything about the cost of living adjustment, the share market crashes 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?
A minimum level of analysis would reflect the worst case as outlined by the Director-General. However, as an ambitious graduate, you may want to reflect on the worst case as given and consider appropriate adjustments. For example, you may wish to ask such questions as is the cost of living adjustment valid? Is the long-term rate of return valid reflective of a mining state such as Western Australia? Is the employee contribution rate likely to stay as given? Should the state contribution factor remain as given? Would for example a change in the Federal government have any impact here and so on.
Your scenario manager analysis should reflect your decisions in terms of this case. Any changes to the inputs should be documented in the summary table in the appendix to your internal report to the Director-General and supported in your discussion.
Case 3: Aggressive Case (A)
The Director-General asks, “In my dreams, I have taken an aggressive line with the union and I win the battles. The cost of living adjustment is reduced to one percent, the productivity doubles to one percent, the employee contribution rate is increased to 10 percent. The salary give-back is $4,000 and share market and mining industry comes back from the brink and the states revenues increase by 10 percent. How good would things be? Surly the ratio gets to 80 percent then?”
A minimum level of analysis would reflect the aggressive case as outlined by the Director-General. However, as an ambitious graduate, you may want to reflect on the case as given and consider appropriate adjustments. For example, you may wish to ask such questions as is the cost of living adjustment valid? Is the long-term rate of return valid reflective of a mining state such as Western Australia? Is the employee contribution rate likely to stay as given? Should the state contribution factor remain as given? Would for example a change in the Federal government have any impact here and so on.
Your scenario manager analysis should reflect your decisions in terms of this case. Any changes to the inputs should be documented in a summary table in the appendix to your internal report to the Director-General and supported in your discussion.
Case 4: Rescue Case (R)
The Director-General says, “I know the Premier and the Minister for Education is going to ask what the government would have to do to bail out the current superannuation fund. So, assuming the conditions of the base case, except for the government contribution factor” run the analysis to determine the minimum acceptable government contribution factor to meet the requirement of an 80% Ratio of Assets to Liability NPV.
A minimum level of analysis would reflect the rescue case as outlined by the Director-General. However, as an ambitious graduate, you may want to reflect on the case as given and consider appropriate adjustments. Can the government do more than adjust its contribution factor to achieve the 80%? Are the other inputs reflective of the economic situation in the short to medium future?
Your scenario manager analysis should reflect your decisions in terms of this case. Any changes to the inputs should be documented in a summary table in the appendix to your internal report to the Director-General and supported in your discussion.
Part 3 Analysing And Reporting The Data
You need to report your analysis and findings to the Director-General so that she can brief the Premier and Minister of Education on the situation with the superannuation fund and the options available going forward.
As you are an employee in the Department of Education, the appropriate means of communicating to the Director-General is via a departmental internal memo with your analysis “attached” in the form of an internal report.
Your memo should be appropriately designed, reflecting that it is a Department of Education internal memo form, it should be appropriately addressed and provide a brief summary of why you are reporting back to the Director-General, a summary of the key findings in the report and which case of the four you believe the Director-General should present to the Premier and Minister for consideration. Support for this recommendation should be provided in your report.
