General American Investors Co., investment company, invests primarily in medium- and high-quality stocks. Jim Campbell is studying the asset value per share for this company and would like to forecast this variable for remaining quarters of 1996. The data are presented in Table 1.1. Evaluate the ability to forecast the asset value variable using the following forecasting methods: naïve rate of change model, naive average change rate model, moving average, moving weighted average and exponential smoothing. When you compare techniques, take into consideration that the actual asset values per share for the remaining quarters of 1996 were: 26,47; 25,85; 25,64. Write a report for Jim indicating which method he should use and why.
1. Using data placed in the table below and Excel program create the spreadsheet and carry out a dynamic analysis of asset value per share (AVS) for General American Investors Co. Assuming that the average change rate of asset value is constant and will be constant in the near future, forecast this variable for the remaining quarters of 1996 and estimate forecast errors.
Table 1.1 Based on Hanke J., Wichern D., Business Forecasting, p.135.
Solution:
First, copy that table to the worksheet (be sure that your table has two columns instead of six). Then create chain indexes of asset value (“AVSI” entered in column C). Remember: in order to enter a formula the “=” sign must precede it. To estimate average change rate use geometric mean (“GM” entered in column D, row 46). See picture 1.
Forecasting values can be entered into column B, row 47 (below the data set). All you have to do is use the formula below:
=B46*$D$46
Picture 1. The example worksheet. For better view the screen has been divided and the forecast values painted in green.
This mean that the last known asset value has been changed according to the average change rate (ACR) presenting in column E, row 46. The rate shows quarterly increase of 0,94%. Notice that every further value of the forecast in column A was made by increasing previous one. So you could copy the formula (F1) up to the row 49. To copy the formula highlight cell B47 then click the fill handle in the lower-right corner and drag it down to cell 49. Remember: in order to copy a formula using the same cell the “$” sign must precede both number of column and row.
Now you can estimate forecast errors using the following equation and Result Table 1.1.:
where:
- forecast error in time period t
- actual value in time period t
- forecast value
Result Table 1.1.
You should copy the table to the spread sheet and copy forecast values to the table. Then you should compute forecast errors in cell D54 (the error is a difference between the real value and the forecast value). You can use the following formula and drag it down to cell D56:
=C54-B54
To evaluate if the error is high or low we usually determine a relative error. Ex-post relative errors are ex-post errors divided by actual values of AVS. The last two rows in the result table are used to evaluate a forecasting method. The mean squared error (MSE) measures forecast accuracy by averaging squared errors. (RMSE) computes the forecasting errors in terms of percentages, that is why we should divide MSE by mean of the actual value of AVS. The complete result table is shown below (see Picture 2):
Conclusions:
Picture 2. The complete Result table 1.1. for sheet1
2. Assume that the rate of change (CR) vary in time and you are able to predict using the last change rate in accordance with incoming data. Forecast this variable for the remaining quarters of 1996 and estimate forecast errors. Use the above Result Table.
Solution:
Prepare the worksheet by copying data set to the sheet 2. You should also copy the actual values of AVS to the first column. Then compute chain indexes (AVSI) and change rate (CR) for the forecasting period (starting from 1996Q1). You can now estimate forecast by changing (increasing or decreasing) actual value of AVS according to CR. The example worksheet and complete Result table are shown below.
Conclusions:
Picture 3. The complete Result Table 1.1. for sheet2
3. Assume now that you can bring ACR up to date according to incoming data. Using the unstable average change rate predict AVS values for the remaining quarters of 1996 and estimate forecast errors. Use the previous Result Table.
Solution:
Prepare the next sheet the same way as before, by copying first two column with data set. Then compute all chain indexes (AVSI) up to 1996Q3. In the next column you should estimate the average chain index (GM) including data set from beginning to the end of analyzing period (up to Q1 1996). Then block the beginning in the formula of GM:
=GEOMETRIC.MEAN($C$3:C46)
and copy it up to the third quarter of 1996. You can now compute the average change rate (ACR) the same way as before and bring it up to date by copying the formula up to Q3 1996. Finally estimate forecast value using previously computed ACR and fill in the result table. The example worksheet and complete Result table are shown below.
Conclusions:
Picture 4. The complete Result Table 1.1. for sheet3
For practice:
Use the updated average change rate model (the length of the GM is constant) for forecasting AVS variable. Forecasting period is the same as before.
Answer: RMSE=2,42%
4. Revision. The table below presents rate of 100 Euro set by National Bank of Poland (February 2001 - April 2003). Evaluate the ability to forecast the rate of 100 Euro variable for the last three months of 2003 using the following forecasting methods: naive rate of change model, naive average change rate model, naive updated average change rate model. Compare techniques taking into consideration the actual rate of the currency for the remaining months of 2003 (shaded in the table). Write a report indicating which method we could use and why.
Table 1.2 Rate of 100 Euro set by NBP. Based on Central Statistical Office.
