A limitation of using ExcelModules for some of the forecasting models described in this chapter is that

Question:

A limitation of using ExcelModules for some of the forecasting models described in this chapter is that the program creates a custom spreadsheet that covers a specific length of time. Thus, as actual values for new months into the future are realized, a new spreadsheet would need to be created, and all the old data would need to be reentered.

Consider Figure 11.12. Modify the program to account for 24 more future periods. Use an IF function to have the cells in the error columns appear only if the cell containing the actual value in column B for that particular month is not empty. For example, you could check to see if cell B19 has a value by using the ISBLANK(B19) function. Then if the cell is blank, place “” into the error cell; otherwise place the answer to the applicable error formula. Note that by doing this, the AVERAGE formulas at the bottom of your sheet will still be valid because they will ignore all cells containing “”. Finally, eliminate the “Next period” cells (currently in row 22) because the reference would need to change with each new month. Instead, have the forecast (column D) value appear for the month following the latest month that has an actual value in column B by using a similar IF function as just described.

Figure 11.12