Charting Data for Excel Sales Forecasting - dummies

Charting Data for Excel Sales Forecasting

By Conrad Carlberg

It is critical to chart your baseline and your forecast in Excel before you start forecasting. Being able to visualize what’s going on is important for several reasons.

Using Excel’s charts, you can see how your actuals are doing. And by charting your actuals, you can see how well your sales forecasts do against the actual sales results. The second figure shows a forecast that’s based on moving averages, against the monthly actuals.

2201_Chart
An Excel chart makes it much easier to see how your sales are doing.
2202_Moving-average
Notice how the moving average lags behind the actual results.

By charting your baseline and your forecasts, you can:

  • See how your actual results are doing. A chart is almost always more revealing than a table of numbers.
  • See how well your forecasts predict actual results. Your eye is a good gauge of the quality of your forecasts.
  • See how well a different variable — advertising dollars or the Consumer Price Index — predicts the sales of your product.

Yes, an R squared or some other summary statistic can give you a concise estimate of how well your forecasts are working. But there’s nothing, nothing, like a chart to tell you if you’re forecasting results or if you’re forecasting junk.