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.
To meet the minimum expectations of the Director-General you need to develop an Excel decision support system reflective of the Director-General’s core specifications and which is supported by your report. A best practice decision support system is one that extends significantly the Director-General’s minimum specification.
Your case study submission will be in two parts: the Microsoft Excel workbook and an internal memo/internal report to Ms Anne Nolan, Director of General of the Department of Education where you now work as a graduate accountant.
You are is on a six-month trial as a graduate account before a decision is made about your acceptance into the Graduate program. Your report to Ms Nolan will be taken into consideration when your performance is evaluated at the end of the trial. Keep in mind Ms Nolan has asked all the graduates to prepare an analysis. She will be presenting the best analysis to the Minister of Education, The Honourable Sue Ellery and Premier of Western Australian, the Honourable Mark McGowan on November 16, 2018.
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:
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 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 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 – 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.
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.
Summary of Key Results Section
This is as described here. No changes required or allowed
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.
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:
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.75 |
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 foward.
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.
The “attached” internal report should contain your detailed analysis of the four cases. Such an analysis would include at a minimum the following:
The memo shouldn’t be more than one page long and should be followed by your internal report. The report shouldn’t be more than 10 pages long. All of this should be in a single professionally presented Word document not as part of your assignment coversheet.
Submission Instructions
Your completed Excel workbook, internal memo & report and assignment coversheet must be submitted for marking by 23:59 on Sunday October 28, 2018.
Your submission should consist of the following three separate files:
Your submission can only be made via the Assignment Manager in Blackboard. The submission link [Assignment Manager] can be found in Blackboard -> Assessment -> Assignment 1: Problem Solving Case Studies -> Microsoft Access problem solving case study. The assignment manager link will be available until the assignment deadline which is:
Sunday October 28, 2018 at 23:59
Please understand that the link will close at the stated time, it does not allow for late submissions. In respect to “computer issues” the following applies:
Urgenthomework helped me with finance homework problems and taught math portion of my course as well. Initially, I used a tutor that taught me math course I felt that as if I was not getting the help I needed. With the help of Urgenthomework, I got precisely where I was weak:
Read More
Follow Us