Excel Sales Forecasting For Dummies, 2nd Edition

Chapter 8

Summarizing Sales Data with Pivot Tables

IN THIS CHAPTER

Getting used to the idea of pivot tables

Putting a pivot table on your worksheet

Grouping similar records together

Staying out of pivot table trouble

If you’re going to create good forecasts, you frequently need to match up some amount of revenue with the period that the revenue came in. Or, if you need to deal with the number of units sold rather than revenue, you need to pair a count of units with the period when they were sold. Excel’s pivot tables give you an excellent method of doing that, if you set your data up right.

When you have your pivot table, it often makes sense to look at your revenues from different viewpoints — usually in terms of months or quarters or years, but often by means of other variables such as ranges of commission percentages. Pivot tables refer to this as grouping. In this chapter, I cover building pivot tables and grouping dates.

Understanding Pivot Tables

A pivot table summarizes one variable — typically, one that can be counted or summed, such as units sold or revenue — in terms of another variable, typically one that comes in categories. Pivot tables are the most powerful method in Excel of summarizing any kind of data, including sales information. That makes pivot tables a useful way to prepare a baseline for your forecast.

A pivot table is often based on an Excel table. (If you’re not yet a table maven, check out Chapter 6.) Excel tables put different variables, or fields, ...