Case 10: The State Pension Fund Analysis

Homework 2

Case 10: The State Pension Fund Analysis

You must complete Case 10 in your textbook (pp. 167-175). Homework 2 is due on Friday, Feb 9 at 11:59 PM.

Note that you don’t need to write a Memo (Assignment 2B, pp. 174), and you don’t need to give a presentation (Assignment 3, PP. 175). However we do require you to reproduce the Figure 10-12 in PP. 175, see Task 3.

Tutorial C (pp. 93-100) is a data analysis task very similar to this homework, with very detailed instructions. It is highly recommended to go over Tutorial C (until ‘USING SCENARIO MANAGER’ in PP. 101) before you start this homework. You can find the raw and finished Excel data for Tutorial C under ICON/Modules/Labs/Tutorials.

Task 0 Read problem description (pp. 167-169)

Please read the problem description carefully (spend at least 10 minutes here). It contains the information for how to calculate certain values in the Excel spreadsheet. You are going to refer back to this part later when you work with the data in Excel.

Task 1 Create a spreadsheet (pp. 169-172)

1. A spreadsheet is ready for you to get started (‘case10_TeachersPensionFund_raw_data.xlsx’).

2. Read through carefully ‘Constants Section’, ‘Inputs Section’, and ‘Summary of Key Results Section’. See Tutorial C (pp.94-95) for more discussion of the general organization of a DSS model.

3. Finish ‘Calculations Section’ as instructed. In particular, for calculating ‘Average Retiree Benefit’, refer to the last two paragraphs of pp.167.

4. Finish ‘Fund Balance Statement Section’ as instructed.

5. Finish ‘Fund Liability Section’ as instructed. See Tutorial E (pp. 213-214) for how to use NPV function in Excel.

Task 2 Using the spreadsheet for decision support (pp. 172-174)

In this task, you need to change the values in the INPUTS section of your spreadsheet and see how the pension fund would perform under different scenarios. Create a new worksheet called ‘summary’ and record the results for different INPUTS values there. The ‘summary’ worksheet should have 5 columns: the leftmost column are variable names, and values from each question are recorded in one column. Note that you don’t need to write a memo or give a presentation.

1. Question 1 (Base Case). This is the scenario you are currently working with. The INPUTS values are shown in Figure 10-7.

2. Question 2 (Worst Case). Change the INPUTS values as shown in Figure 10-9. Record SUMMARY OF KEY RESULTS in the ‘summary’ worksheet.

3. Question 3 (Aggressive Case). Change INPUTS values as shown in Figure 10-10 and record results.

4. Question 4 (Rescue Case). Change INPUTS values as shown in Figure 10-11. In this case, you need to manually change ‘State Contribution Factor’ until the ‘Ratio of Assets to Liability NPV’ reaches 80%.

Please change ‘State Contribution Factor’ start from 3 and experiment with only integer values. Use the smallest factor that reaches the assets to liability NPV ratio of 80% and record the results.

Under this scenario, you also need to calculate the difference in ‘State Contribution to Fund’ with the state contribution factor equals 2.5 and the factor you find.

Task 3 Relationship between ‘Ratio of Assets to Liability NPV’ and ‘Long Term Rate of Return’

You need to reproduce the line chart that is very similar to Figure 10-12 on pp. 175 in this task.

1. Change the INPUTS values to the Base Case.

2. Create a new worksheet named ‘chart’ and record all results for this task there.

3. Change the value of ‘Long Term Rate of Return’ (cell B13) from 0 to 0.5 increasing by 0.05. In other words, your column of Rate of Return should have values: (0, 0.05, 0.1, 0.15, 0.2, 0.25, 0.3, 0.35, 0.4, 0.45, 0.5).

For each rate of return value, record the corresponding ‘Ratio of Assets to Liability NPV’ (from cell B21 or C47 in the Model worksheet).

4. Finally, plot a line chart for ‘Rate of Return’ and ‘Ratio’.

Make sure to save your file with a descriptive name

(e.g. “studentname_hw2.xlsx”). Good Luck!