Using Excel Pivot Table Row Fields in Sales Forecasting - dummies

Using Excel Pivot Table Row Fields in Sales Forecasting

By Conrad Carlberg

Row fields are important in Excel pivot tables, because you can use them to organize your data as a summarized table. In forecasting situations, this means that each row in the pivot table represents each time period in your baseline.

For example, suppose you want to forecast sales for the next quarter. You set up your pivot table so that each of the quarterly sales totals for the previous, say, ten years shows up in a different row — even though each record in your source data represents an individual sale on a particular day.

To total according to quarters, months, or some other time span, you need to group the date field.

With your pivot table set up that way, getting the Data Analysis add-in, or an XY (Scatter) chart, to create a forecast is easy. And the time period will be right. Summed up into quarterly totals, you can get the next quarter’s estimate. Summed up into monthly totals, you can get the best estimate for the next month. You can’t do that if the data is still organized by individual sale or by specific day.

The way to start is to put the dates that the sales were made into a pivot table’s row field. If the company’s accounting system, or sales database, already summarizes sales into the time period you want to use, so much the better. Then you don’t need to do any grouping of individual dates into months, quarters, or years. If the company’s accounting system, or sales database, doesn’t already summarize sales into the time period you want to use, you can do that in a snap.