Pivot table for reporting sales performance

[Ram]

Hi Experts,

I am hoping that you can help me with the following :

I am preparing a report for 3 sales teams team leaders (TL1, TL2, TL3) who
manage 3 sales reps each (SR1, SR2, SR3; SR4, SR5, SR6; SR7,SR8, SR9). EAch
sales rep is responsible for sales of 3 Product groups (PG1, PG2, PG3;
PG4.......). The sales targets work bottom up.

The Team Leader's target is the sum of targets of the 3 Sales reps and
inturn the Sales Rep's target is the sum of product group targets.

I want to integrate the targets in to the pivot report. the sales data is
from a database, which contains, all information except the targets.

How do you recommend that i add the target figures to the data sheet.

 

[Roger Govier]

Hi

Add an extra column to your source data table called Target.
Create a single row entry for each PG, for each SR for each TL with the
value of the target level.
If you are reporting the data monthly, then you will need to have 12
rows for each, rather than a single row.

Allocate the Target field to the data area.
Drag the Data button to the Total column to have actual and target
appear side by side.

 

[flummi]

If you set up your table like below, click in A1 and select
data-->group & outline-->Auto outline. Excel will give you on the left
hand side of your screen a graphical grouping indication.
You can click on the numbered symbols and it will display totals on
that level.

Look at the samples below.

Sorry for the formatting.

Hans

Teaml S-Rep PG Cust sales target % target
TL1 SR1 PG1 1111 120 180 66,67%
TL1 SR1 PG1 1112 80 100 80,00%
TL1 SR1 PG1 1113 145 200 72,50%
TL1 SR1 PG1 1114 60 100 60,00%
TL1 SR1 PG1 1115 110 180 61,11%
Total PG1 515 760 67,76%
TL1 SR1 PG2 2111 85 100 85,00%
TL1 SR1 PG2 2112 221 300 73,67%
TL1 SR1 PG2 2113 152 100 152,00%
TL1 SR1 PG2 2114 50 80 62,50%
Total PG2 508 580 87,59%
TL1 SR1 PG3 3111 300 350 85,71%
TL1 SR1 PG3 3112 250 300 83,33%
Total PG3 550 650 84,62%
Total SR1 1573 1990 79,05%
TL1 SR2 PG4 4111 30 50 60,00%
TL1 SR2 PG4 4112 80 100 80,00%
TL1 SR2 PG4 4113 60 80 75,00%
TL1 SR2 PG4 4114 110 100 110,00%
Total PG4 280 330 84,85%
TL1 SR2 PG5 5111 420 1000 42,00%
TL1 SR2 PG5 5112 150 150 100,00%
Total PG5 570 1150 49,57%
TL1 SR2 PG6 6111 100 100 100,00%
TL1 SR2 PG6 6112 120 150 80,00%
Total PG6 220 250 88,00%
Total SR2 1070 1730 61,85%
Total TL1 2643 3720 71,05%
TL2 SR11 PG221 1111 120 180 66,67%
TL2 SR11 PG221 1112 80 100 80,00%
TL2 SR11 PG221 1113 145 200 72,50%
TL2 SR11 PG221 1114 60 100 60,00%
TL2 SR11 PG221 1115 110 180 61,11%
Total PG221 515 760 67,76%
TL2 SR11 PG22 2111 85 100 85,00%
TL2 SR11 PG22 2112 221 300 73,67%
TL2 SR11 PG22 2113 152 100 152,00%
TL2 SR11 PG22 2114 50 80 62,50%
Total PG22 508 580 87,59%
TL2 SR11 PG23 3111 300 350 85,71%
TL2 SR11 PG23 3112 250 300 83,33%
Total PG23 550 650 84,62%
Total SR11 1573 1990 79,05%
TL2 SR12 PG30 4111 30 50 60,00%
TL2 SR12 PG30 4112 80 100 80,00%
TL2 SR12 PG30 4113 60 80 75,00%
TL2 SR12 PG30 4114 110 100 110,00%
Total PG30 280 330 84,85%
TL2 SR12 PG31 5111 420 1000 42,00%
TL2 SR12 PG31 5112 150 150 100,00%
Total PG31 570 1150 49,57%
TL2 SR12 PG70 6111 100 100 100,00%
TL2 SR12 PG70 6112 120 150 80,00%
Total PG70 220 250 88,00%
Total SR12 1070 1730 61,85%
Total TL2 2643 3720 71,05%


Teaml S-Rep PG Cust sales target % target
Total PG1 515 760 67,76%
Total PG2 508 580 87,59%
Total PG3 550 650 84,62%
Total SR1 1573 1990 79,05%
Total PG4 280 330 84,85%
Total PG5 570 1150 49,57%
Total PG6 220 250 88,00%
Total SR2 1070 1730 61,85%
Total TL1 2643 3720 71,05%
Total PG221 515 760 67,76%
Total PG22 508 580 87,59%
Total PG23 550 650 84,62%
Total SR11 1573 1990 79,05%
Total PG30 280 330 84,85%
Total PG31 570 1150 49,57%
Total PG70 220 250 88,00%
Total SR12 1070 1730 61,85%
Total TL2 2643 3720 71,05%


Teamleader SalesRep PG Customer sales target % target
Total SR1 1573 1990 79,05%
Total SR2 1070 1730 61,85%
Total TL1 2643 3720 71,05%
Total SR11 1573 1990 79,05%
Total SR12 1070 1730 61,85%
Total TL2 2643 3720 71,05%


Teamleader SalesRep PG Customer sales target % target
Total TL1 2643 3720 71,05%
Total TL2 2643 3720 71,05%