Tarrows, Pearson, Foster, and Zuligar (TPF&Z) is one of the largest actuarial consulting firms in the United States. In addition to providing its clients with expert advice on executive compensation programs and employee benefits programs, TPF&Z also helps its clients determine the amounts of money they must contribute annually to defined benefit retirement programs.

Most companies offer two different types of retirement programs to their employees: defined contribution plans and defined benefit plans. Under a defined contribution plan, the company contributes a fixed percentage of an employee’s earnings to fund the employee’s retirement. Individual employees covered by this type of plan determine how their money is to be invested (for example, stocks, bonds, or fixed-income securities), and whatever the employees are able to accumulate over the years constitutes their retirement fund. In a defined benefit plan, the company provides covered employees with retirement benefits that are usually calculated as a percentage of the employee’s final salary (or sometimes an average of the employee’s highest five years of earnings). Thus, under a defined benefit plan, the company is obligated to make payments to retired employees, but the company must determine how much of its earnings to set aside each year to cover these future obligations. Actuarial firms such as TPF&Z assist companies in making this determination.

Several of TPF&Z’s clients offer employees defined benefit retirement plans that allow for the cost of living adjustments (COLAs). Here, an employee’s retirement benefit is still based on some measure of his or her final earnings, but these benefits are increased over time as the cost of living rises. These COLAs are often tied to the national consumer price index (CPI), which tracks the cost of a fixed-market basket of items over time. Each month, the Federal government calculates and publishes the CPI. Monthly CPI data from January 1991 through March 2013 is given in the file CPIData.xlsx that accompanies this book.

To assist its clients in determining the amount of money to accrue during a year for their annual contribution to their defined-benefit programs, TPF&Z must forecast the value of the CPI one year into the future. Pension assets represent the largest single source of investment funds in the world. As a result, small changes or differences in TPF&Z’s CPI forecast translate into hundreds of millions of dollars in corporate earnings being diverted from the bottom line into pension reserves. Needless to say, the partners of TPF&Z want their CPI forecasts to be as accurate as possible.

1. Prepare a plot of the CPI data. Based on this plot, which of the time series forecasting techniques covered in this chapter would not be appropriate for forecasting this time series?

2. Apply Holt’s method to this data set and use Solver to find the values of α (alpha) and β (beta) that minimize the MSE between the actual and predicted CPI values. What is the MSE using this technique? What is the forecasted CPI value for April 2013 and April 2014 using this technique?

3. Apply linear regression to model the CPI as a function of time. What is the MSE using this technique? What is the forecasted CPI value for April 2013 and April 2014 using this technique?

4. Create a graph showing the actual CPI values plotted along with the predicted values obtained using Holt’s method and the linear regression model. Which forecasting technique seems to fit the actual CPI data the best?

Based on this graph, do you think it is appropriate to use linear regression on this data set? Explain your answer.

5. A partner of the firm has looked at your graph and asked you to repeat your analysis excluding the data prior to 2009. What MSE do you obtain using Holt’s method? What MSE do you obtain using linear regression? What is the forecasted CPI value for April 2013 and April 2014 using each technique?

6. Graph your results again. Which forecasting technique seems to fit the actual CPI data the best? Based on this graph, do you think it is appropriate to use linear regression on this data set? Explain your answer.

7. The same partner has one final request. She wants to consider if it is possible to combine the predictions obtained using Holt’s method and linear regression to obtain a composite forecast that is more accurate than either technique used in isolation. The partner wants you to combine the predictions in the following manner:

Combined Prediction = w × H + (1 − w) × R

In this equation, H represents the predictions from Holt’s method, R represents the predictions obtained using the linear regression model, and w is a weighting parameter between 0 and 1. Use Solver to determine the value of w that minimizes the MSE between the actual CPI values and the combined predictions. What is the optimal value of w, and what is the associated MSE? What is the forecasted CPI value for April 2013 and April 2014 using this technique?

8. What CPI forecast for April 2013 and April 2014 would you recommend TPF&Z actually use?