a. 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?

b. 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 November 2005 and November 2006 using this technique?

c. 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 November 2005 and November 2006 using this technique?

d. 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.

e. A partner of the firm has looked at your graph and asked you to repeat your analysis excluding the data prior to 1997. What MSE do you obtain using Holt’s method? What MSE do you obtain using linear regression? What is the forecasted CPI value for November 2005 and November 2006 using each technique?

f. 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.

g. The same partner is pleased with your new results but 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

Where 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 November 2005 and November 2006 using this technique?

h. What CPI forecast for November 2005 and November 2006 would you recommend that TPF&Z actually use?

i. Use CB Predictor to determine the best forecasting function for this data based on minimum RMSE. What technique does CB Predictor suggest?

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 earning to fund the employee’s retirement. Individual employees covered by this type of plan determine how their money is to be invested (e.g., 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 usually are 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 like TPF&Z assist companies in making this determination.

Several of TPF&Z’s clients offer employees defined benefit retirement plans that allow for cost of living adjustments (COLAs). Here, employees’ retirement benefits still are based on some measure of their final earnings, but these benefits are increased over time as the cost of living rises. These COLAs often are 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 October 2005 is given in the following table (and in the file CPIData.xls on your data disk).

To assist their 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 aspossible.

Consumer Price Index Data 1991-2005 Month 1991 1992 1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 1134.6 138.1 142.6 146.2 150.3 1544 1591 161.6 164.3 166.6 175.1 177.1 181.7 185.2 190.7 2 134.8 138.6 143.1 1467 150.9 1549 159.6 1619 164.5 168.8 1758 1778 183.1 186.2 1918 3 135.0 139.3 1436 1472 151.4 155.7 160.0 162.2 165.0 1698 176.2 1788 184.2 1874 193.3 4 135.2 139.5 144.0 147.4 151.9 1563 160.2 162.5 166.2 1712 176.9 179.8 183.8 188.0 194.6 5 135.6 139.7 144.2 147.5 152.2 1566 160.1 162.8 166.2 171.3 177.7 179.8 183.5 189.1 194.4 6 136.0 140.2 144.4 148.0 152.5 156.7 160.3 163.0 166.2 171.5 178.0 179.9 183.7 189.7 194.5 7 136.2 140.5 144.4 148.4 152.5 157.0 1605 163.2 166.7 1724 177.5 180.1 183.9 1894 195.4 8 136.6 140.9 1448 149.0 1529 157.3 160.8 1634 167.1 1728 1775 180.7 184.6 189.5 1964 9 1372 141.3 145.1 149.4 153.2 157.8 161.2 163.6 1679 1737 178.3 181.0 185.2 189.9 1988 10 1374 141.8 145.7 149.5 153.7 158.3 161.6 164.0 168.2 174.0 177.7 181.3 185.0 190.9 193.2 11 1378 142.0 145.8 149.7 153.6 158.6 161.5 164.0 168.3 174.1 177.4 181.3 184.5 191.0 12 1379 141.9 145.8 149.7 153.5 158.6 161.3 163.9 168.3 174.0 1767 1809 184.3 190.3