The manager of a small health clinic needs to forecast demand for laboratory services in the facility. Data from the last six weeks are available and are, respectively, 330, 350, 320, 370, 368, and 343 tests. The manager decides to use a forecasting technique known as the three-period moving average.
For this technique, the most recent three weeks' demands are averaged together to produce the forecast for the upcoming week. For example, weeks 1-3 are used to produce the forecast for week 4.
(a) Construct a spreadsheet model to help the manager forecast demand. Assume that this technique has been in place in the previous weeks as well, and generate the forecasted demand for week 7. You will not be able to generate forecasted demands for weeks 1-3.
(b) Expand the model to calculate the error in the forecast each week. The error should be calculated as the actual demand for the week minus the forecasted demand. Which week had the largest error? Which had the smallest error? What does it mean that some errors are positive and some are negative?