Pivot Table Sales Projections column

R

rivers99

I would like to add a column for projected sales into a pivot table.
Formula would be Grand Total/(month sales are through)*12.

I am running into problems because when I try to add the column on top
because I have the MONTH column on top already the pivot table wants
to expand beyond 255 columns. Here is what I want it to look like:

MONTH
SALES REP NAME Projected Sales Jan Feb
March..... Grand Total
John Smith $5,600 $200
$500 $700 $1,400
Bruce Jones
Steve Johnson

Any Suggestions? Also, the pivot table format I am using is the
default one.
 
H

Herbert Seidenberg

Assume your PT looks like this:
SalesRep 1 2 3 4 5 6 7
AmTh 12 14 13 17 22 19
BrKe 44 37 35 39 30 29
CaTo 17 22 19 11

Some month sales are blank, not zero, because the
sales rep might have been absent.
These months should not be included in the average.
Also, some reps might have an up or down trend.
Thus I suggest a linear trendline, not an average.
Instead of month text labels, use numbers.
If the PT is located at G3, then use this formula
outside the PT for projected yearly total sales:
=SUM(FORECAST({1,2,3,4,5,6,7,8,9,10,11,12},G5:N5,$G$4:$N$4))
=238
=333
=182
 
R

rivers99

Assume your PT looks like this:
SalesRep        1       2       3       4       5       6       7
AmTh    12      14      13              17      22      19
BrKe    44      37      35      39      30     29
CaTo                            17      22      19      11

Some month sales are blank, not zero, because the
sales rep might have been absent.
These months should not be included in the average.
Also, some reps might have an up or down trend.
Thus I suggest a linear trendline, not an average.
Instead of month text labels, use numbers.
If the PT is located at G3, then use this formula
outside the PT for projected yearly total sales:
=SUM(FORECAST({1,2,3,4,5,6,7,8,9,10,11,12},G5:N5,$G$4:$N$4))
=238
=333
=182

works great, thanks Herbert!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top