O'Reilly logo

Excel Scientific and Engineering Cookbook by David M Bourg

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

6.9. Forecasting

Problem

You need to make a forecast of future values given historical values for a time series.

Solution

Excel does not provide built-in functionality for sophisticated forecasting . It does have a FORECAST spreadsheet function that sounds enticing; however, that merely uses a linear trendline and is unsuitable for time series that exhibit significant seasonal variability.

As an alternative, you could use the various moving average techniques to make near-term forecasts. Or you could use some higher-order regression equation (see Chapter 8) to make near-term predictions. Keep in mind that extrapolating too far beyond the extents of the original time series could result in significant errors.

If you have the resources, you could purchase one of the many Excel add-ins that offer sophisticated autoregressive moving average (ARMA) or neural network techniques. If you're so inclined, you can even program such techniques yourself in VBA.

In lieu of all these approaches, you can use standard spreadsheet techniques to make forecasts. I'll show you how by extending the techniques discussed in the previous several recipes.

Discussion

I'll again use the time series shown in Figure 6-23. Recall that this series consists of average monthly temperatures in Louisiana from January 1996 to December 1999. The task now is to make a forecast for the average monthly temperatures in the year 2000.

The brunt of this forecasting effort has already been covered in the previous recipes, in which ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required