Balanced Scorecards and Operational Dashboards with Microsoft Excel, 2nd Edition

Chapter 23

Smoothing Data and Forecasting Trends

I always avoid prophesying beforehand because it is much better to prophesy after the event has already taken place.

—Winston Churchill

Statesman

1874–1965

Humans have been forecasting ever since the first shaman squatted by a fire and cast bones to divine the future. We forecast how long it will take us to get to work based on the current traffic jams. When we get to work, we forecast product sales, marketing response rates, or budget variance.

Now we cast bones with the aid of complex algorithms built into statistical analysis programs such as SPSS. Even though few people have consistent access to SPSS and the skills to use it, you can meet your everyday forecasting needs with Microsoft Excel. Most of what you need for simple forecasting and analysis is built into Microsoft Excel and is straightforward to use.

In this chapter you learn how to smooth erratic data so that you can see and use the patterns hidden within it. You learn how to write your own formulas that work in any Excel worksheet and learn how to use the wizard in the Analysis ToolPak. If you need to forecast data that may have a linear or curving trend, you also learn how to use Excel’s TREND and GROWTH functions.

There is as much art to analyzing data and developing forecasts as there is mathematical science. You must understand which mathematical method is appropriate and which parameters to use to smooth or forecast data. The qualitative art comes in knowing how far ...