Excel Sales Forecasting For Dummies, 2nd Edition

Chapter 9

Charting Your Baseline: It’s a Good Idea

IN THIS CHAPTER

Using dates and times in Excel

Figuring out what’s going on in your baseline

Using pivot charts to make your data look good

Using more than one scale

To make good forecasts — forecasts that your colleagues think of as useful guides to the future — you need to know as much as you can about the baselines that determine those forecasts. You can run all sorts of highfalutin statistical tests on a baseline, but one of your best sources of information is the lowly chart. For example, if your baseline describes an upside-down U, simple regression statistics or correlations aren’t going to tell you that. But charting the baseline often results in the well-known interocular impact effect: It hits you right between the eyes.

If you use a fairly recent version of Excel, you can use a special kind of chart called a pivot chart. Pivot charts are similar to pivot tables in two primary ways:

• You can easily change the role of a variable — for example, moving it from a chart’s horizontal axis to its vertical axis.
• You can display a data field as any one of a set of summary options: Count, Sum, Average, and so on.

Sometimes you want to chart more than one data series at once, so you can see how two baseline variables behave over time. If those data series have very different scales, such as monthly sales and monthly sales commissions, the differences in the scales can make it hard to see what’s happened during the course of ...