Summing Revenues in Excel Sales Forecasting

By Conrad Carlberg

In sales forecasting, you’re in the business of summing revenues and counting units sold. So generally you’re going to want to have your value field sum up revenues if it’s dollars, or count units if it’s the number of items you’ve sold.

The figure shows what your worksheet looks like when you’re ready to put a field into the Σ Values area of the Field List.

4601_Summing-Revenues
You’ve completed the pivot table as soon as you put a field in the Σ Values area.

Excel stalks you. It’s always watching what you’re doing. Suppose your value field is revenues. That’s a numeric field: It’s numbers. When you put a numeric field into the Σ Values area, Excel notices that and it automatically gives you a sum. For each value of the row or column field, you’ll get a sum of, for example, your sales revenues.

You’ll almost always have a row, column, or filter field in your pivot table. In sales forecasting, you’ll usually find yourself using dates as a row field, so January might be in one row, February in the next, and so on. The pivot table’s Sales Date field shows the date of each individual sale, so it will have to be grouped to summarize on month.

For each item in the row field, the pivot table’s value field will give you the total of all the revenue that goes with that item. Maybe your list shows 50 sales records for February. The pivot table can total the revenue for those 50 records and show the sum of that revenue in one row — which would then be labeled “February.”