fairchance
New member
- Joined
- Jan 4, 2015
- Messages
- 48
- Reaction score
- 0
- Points
- 0
Dear All
Hi
I need a spreadsheet that allows me to daily record Unqualified (U) & Qualified (Q) leads of 60 sales staff, give me daily and weekly totals for the team and individual salesperson, and calculate the Q to U percentage, then have excel calculate the daily accumulative amount to be paid (on a monthly pay cycle). I also need charts that show the daily Q amounts over time for each salesperson.
The salespersons pay rate is thus:
Leads 1-5 are paid at $10 each
Leads 6-10 are paid at $15 each
Leads 11+ are paid at $20 each
For example, 7 qualified leads in a day are paid at (5x$10)+(2x$15)=$80, 13 qualified leads in a day are paid at (5x$10)+(5x$15)+(3x$20)=$185.
Regards
Shehbaz H.
Hi
I need a spreadsheet that allows me to daily record Unqualified (U) & Qualified (Q) leads of 60 sales staff, give me daily and weekly totals for the team and individual salesperson, and calculate the Q to U percentage, then have excel calculate the daily accumulative amount to be paid (on a monthly pay cycle). I also need charts that show the daily Q amounts over time for each salesperson.
The salespersons pay rate is thus:
Leads 1-5 are paid at $10 each
Leads 6-10 are paid at $15 each
Leads 11+ are paid at $20 each
For example, 7 qualified leads in a day are paid at (5x$10)+(2x$15)=$80, 13 qualified leads in a day are paid at (5x$10)+(5x$15)+(3x$20)=$185.
EXAMPLE | |||||||||||||||
Mar | |||||||||||||||
Name | 1 | 2 | 3 | 4 | 5 | ||||||||||
John | Unqualified | 12 | 11 | 13 | 11 | 10 | |||||||||
Qualified | 4 | 5 | 5 | 6 | 6 | ||||||||||
Sally | Unqualified | 13 | 11 | 15 | 13 | 13 | |||||||||
Qualified | 6 | 3 | 5 | 5 | 4 | ||||||||||
Ben | Unqualified | 15 | 14 | 12 | 11 | 12 | |||||||||
Qualified | 5 | 6 | 4 | 5 | 5 | ||||||||||
Total | Unqualified | 40 | 36 | 40 | 35 | 35 | |||||||||
Qualified | 15 | 14 | 14 | 16 | 15 | ||||||||||
Ratios | Q to U (%) | 37.5 | 39 | 35 | 46 | 43 | |||||||||
Commissions | |||||||||||||||
Paid on Qualified leads only | |||||||||||||||
Leads 1-5 | $10 | ||||||||||||||
Leads 6-10 | $15 | ||||||||||||||
Leads 11+ | $20 | ||||||||||||||
Mar | | Week Total | Accumulative Monthly Total | ||||||||||||
1 | 2 | 3 | 4 | 5 | |||||||||||
John | 40 | 50 | 50 | 65 | 65 | 270 | 270 | ||||||||
Sally | 65 | 30 | 50 | 50 | 40 | 235 | 235 | ||||||||
Ben | 50 | 65 | 40 | 50 | 50 | 255 | 255 | ||||||||
I Want these cells in above to auto-populate when cells in bold have data added. |
Regards
Shehbaz H.