a. Prepare a plot of the CPI data. Based on this plot, which of the time series

Question:

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.