ACC3201: ACCOUNTING INFORMATION SYSTEMS

PROBLEM SOLVING CASE STUDY - MICROSOFT EXCEL

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:

• 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.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;
  • 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.

1. The value of the assets is easy to compute because shares, bonds, and other financial instruments usually have market values for quick reference.
2. The amount of benefits the fund is required to pay takes into consideration the following.
3. Typically, a 30-year horizon is assumed;
4. The number of covered superannuants is estimated for each year along with the estimated payout for the superannuant each year;
5. The present value of each year’s estimated obligation is computed;
6. The total of each of the 30 years present values is the estimated total obligation, stated in today’s dollars;
7. 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:

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:

• An outline of analysis undertaken, its objectives and why it was undertaken;
• A summary of the inputs, constants and calculations for all four cases [table form] and an explanation of these variables in respect to each case;
• If you have chosen to vary the inputs provided by the Director-General you should show these variations in the summary table, provide appropriate economic justification for each variation and reference your source data;
• If you have not chosen to vary the inputs provided by the Director-General, you should provide an explanation as to your decision not to, i.e., why do you believe the inputs as given are appropriate for this analysis;
• An analysis of each case and its implications for both employees and the government over the long-term. Your analysis should be supported with professionally presented tables and graphs as appropriate;
• Your report should conclude with your recommendation as to which of the four cases should be recommended to the Premier and the Minister of Education as the course of action that the government should take going forward. Keep in mind this is a political decision; your support for the recommendation needs to reflect both the hard-economic realities as well as the potential political realities.
• Finally, appropriate referencing as per the ECU Referencing Guide should be applied if applicable.

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:

1. Your fully completed ECU Assignment Coversheet. Please name this file: assignment coversheet;
2. Your Microsoft Excel workbook. The file name must be in the following format [all lower case]: your_family-name_your_student-number e.g., priest_10234678.xlsx
3. Your internal memo and report. This must a single Microsoft Word document. The memo should be the first page, the remaining pages should be your report. The file name must be in the following format [all lower case]: your_family-name_your_student-number e.g., priest_10234678.docx

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:

• “Computer issues” can only be considered as grounds for a late submission without penalty if supporting evidence of the computer problem is provided. Normally this is in the form of screen grabs which show the relevant error messages (photos are not acceptable) and/or written confirmation from IT Services of your problem which was beyond your control. No supporting evidence means no late submission without penalty;
• Use of your own personal computer and/or internet service provider whilst allowed has to be at your own responsibility. Neither the teaching staff nor ECU IT Services can take responsibility for any problems you may encounter using your personal computer and/or internet service hence you are encouraged to complete the submission on-campus using ECU computers;
• If you are not familiar with using the assignment manager, you must take appropriate steps to inform yourself. Support materials are provided in Blackboard via the Student Support link at the top right-hand corner of the screen. Failure to use the assignment manager correctly is not grounds for a late submission without penalty.