Why quota allocation is the first job of every sales manager
Every sales year starts the same way. The VP of Sales hands you a single number, say 6 million dollars for your team, and from that moment the number is yours. Nobody will remember next November how it was split. They will only remember whether your team hit it. Yet the split itself decides a lot: which reps feel motivated, which ones give up in March, how much commission you pay, and how believable your forecast is all year. This free first lesson is for front-line sales managers, team leads who just got promoted, sales operations analysts and founders who now manage their first few reps. It shows you how to turn one team quota into fair individual quotas in Excel, using a method that you can explain to every rep in two minutes.
We use the same fictional company as the lab above. Dana Whitlock manages eight reps at Corvane Industrial Supply, a distributor of industrial parts. Each rep owns one territory: North, East, South, West, Central, Metro, Coast and Valley. The VP gave Dana a team quota of $6,000,000 for the year. Dana wants to split it by territory potential and add a 10% over-assignment so that her own number is protected if one or two reps fall short. By the end of this article you will know exactly which formulas the lab expects, why each one works and where managers usually go wrong.
The problem with an even split
The fastest way to split $6,000,000 across eight reps is to divide by eight: $750,000 each, or $825,000 each after a 10% over-assignment. It looks fair because everybody gets the same number. In practice it is the least fair option you have. Fatima in the Metro territory sits on $2.9 million of customer potential, while Hana in the Valley territory has only $1.2 million. With an even split Fatima needs to win about 28% of her potential, Hana almost 69%. Fatima will probably beat her quota by July and coast, and Hana will be behind from the first week, however hard she works. Both outcomes cost you money: overpaid commission in one territory, a demotivated or departing rep in another.
An even split also makes your forecast worse. If quotas do not reflect what the market can give, attainment stops telling you anything about performance. A rep at 120% may simply have an easy territory, and a rep at 80% may be your best seller. When you later rank reps or decide whom to coach, which you will do in lesson 2 of this course, you want the quota to be a fair yardstick. That starts here.
The core idea: share of territory potential
Territory potential is an estimate of how much the customers in a territory could buy from a company like yours in a year. It is not a sales target and not last year’s revenue. It is the size of the opportunity. You can estimate it from industry spend data, from the number and size of target accounts, or, as a simple proxy, from the total spend of the accounts in the territory. Whatever source you use, use the same source for every territory, because the method only compares territories with each other.
Once you have a potential per territory, the allocation is two simple steps. First, each territory’s share is its potential divided by the total potential of all territories. Second, each rep’s quota is that share multiplied by the team quota and by the over-assignment factor. In formula form: share = potential / total potential and rep quota = share × team quota × over-assignment. Because the shares always add up to 100%, the rep quotas always add up to exactly team quota × over-assignment, here $6,600,000.
The over-assignment deserves a word of its own. Very few teams have every rep at or above 100% at the end of the year. Someone leaves, someone is on long leave, a big customer delays a project. If the rep quotas add up to exactly the team quota, every one of those misses becomes a miss on your number. Many sales organisations therefore assign more in total than the manager’s own quota, often somewhere around 5% to 15%, depending on how predictable the business is. In our example Dana uses 110%. The difference between the assigned total and the team quota is the buffer: $600,000 for Dana.
Worked example: Corvane Industrial Supply
Here is Dana’s full allocation with the numbers from the lab. Total potential across the eight territories is $15,600,000. The share column is potential divided by that total, the quota column multiplies the share by $6,000,000 and by 1.1. The last two columns compare the result with an even split of $825,000 per rep, so you can see who gains and who loses from the fairer method.
| Rep | Territory | Potential (C) | Share (D) | Rep quota (E) | Even split | Difference |
|---|---|---|---|---|---|---|
| Alex | North | 2,400,000 | 15.38% | 1,015,385 | 825,000 | 190,385 |
| Bea | East | 1,850,000 | 11.86% | 782,692 | 825,000 | -42,308 |
| Carlos | South | 2,100,000 | 13.46% | 888,462 | 825,000 | 63,462 |
| Dina | West | 1,600,000 | 10.26% | 676,923 | 825,000 | -148,077 |
| Ethan | Central | 2,250,000 | 14.42% | 951,923 | 825,000 | 126,923 |
| Fatima | Metro | 2,900,000 | 18.59% | 1,226,923 | 825,000 | 401,923 |
| Greg | Coast | 1,300,000 | 8.33% | 550,000 | 825,000 | -275,000 |
| Hana | Valley | 1,200,000 | 7.69% | 507,692 | 825,000 | -317,308 |
| Total | 15,600,000 | 100.00% | 6,600,000 | 6,600,000 | 0 |
The formulas behind the table are short. In D2 the share is =C2/SUM($C$2:$C$9), copied down to D9. In E2 the quota is =D2*$B$12*$B$13, copied down to E9. The total in E10 is =SUM(E2:E9) and returns 6,600,000. The buffer in B15 is =E10-B12, which returns 600,000. Notice the dollar signs: the total potential range, the team quota cell and the over-assignment cell must stay fixed while you copy the formulas down, so they are absolute references. The potential in C2 has no dollar signs because it should move one row down with each copy.
Read the result the way a rep would. Fatima’s Metro territory holds 18.59% of all potential, so she carries 18.59% of the assigned quota: $1,226,923. That is about $402,000 more than under an even split, but it still asks her for about 42% of her territory’s potential, exactly the same proportion as everybody else. Hana carries $507,692, about $317,000 less than under an even split. Every rep is asked for the same percentage of their market, 6,600,000 / 15,600,000 = 42.3%. That single sentence is the strongest argument you have when a rep asks why their quota is higher than a colleague’s.
The buffer works like a shock absorber. Dana’s reps together are assigned $6.6 million, but Dana herself only has to deliver $6.0 million. That means the team as a whole can reach 6.0 / 6.6 = 90.9% of the assigned quotas and Dana still lands at 100% of her number. You can calculate that safety level directly in Excel with =B12/E10.
How to do it in Excel, step by step
- Put one row per rep with the rep name, the territory and the potential (A2:C9 in the lab). Keep potential as a plain number without currency text so that Excel can calculate with it. Format it with Home › Number › Comma Style or press
Ctrl+Shift+1. - Put the inputs that apply to the whole team in their own cells: team quota in B12 and over-assignment in B13. Format B13 as a percentage (
Ctrl+Shift+5) so 1.1 shows as 110%. - Click D2 and type
=C2/SUM($C$2:$C$9). After typing the range C2:C9 inside SUM, pressF4once to turn it into $C$2:$C$9. Press Enter. - Copy D2 down to D9: select D2:D9 and press
Ctrl+D, or double-click the small square (fill handle) at the bottom-right of D2. Format D2:D9 as a percentage with two decimals. - Click E2 and type
=D2*$B$12*$B$13, again usingF4on B12 and B13. Copy it down to E9 withCtrl+D. - In E10 enter
=SUM(E2:E9)or pressAlt+=(AutoSum) with E10 selected. It must show 6,600,000. If it does not, one of your references moved when you copied. - In B15 calculate the buffer with
=E10-B12. As a check, the buffer must equal=B12*(B13-1), here 600,000. - Optional but useful: round quotas to a clean number for communication with
=ROUND(D2*$B$12*$B$13,-3), which rounds to the nearest thousand. Check afterwards that the rounded total is still close to the target. - Select the table and press
Ctrl+Tif you want an Excel Table, or add a column chart with Insert › Charts › Clustered Column to show the team how the number was split.
Choosing a good measure of potential
The maths is simple. The hard part is the potential column, because every number in it ends up in someone’s pay cheque. A few practical rules help. Use a measure that reps cannot easily influence. Last year’s sales by territory, for example, reward reps who sandbagged and punish reps who already did very well, because a strong year raises their next quota. External or account-based measures, such as the number of target companies, their employee count or their estimated spend on your product category, are harder to game. Many teams combine two or three measures with weights, for example 60% account spend and 40% number of target accounts. You can do that in Excel by first turning each measure into a share and then taking the weighted average of the shares with =0.6*F2+0.4*G2.
Also look at what potential leaves out. A new territory with no relationships yet may have high potential but a long ramp, and a rep who just joined needs a lower number for the first months anyway. That is why the course has a separate lesson on ramp quotas for new hires. Likewise, if one territory holds a single enormous account, consider handling that account separately, because one deal can swing the whole territory. Potential is a starting point for a conversation, not a law, and a sensible manager adjusts a few quotas by hand and writes down why.
Common mistakes and how to spot them
- Relative total in the share formula. Typing
=C2/SUM(C2:C9)without dollar signs works in row 2 but shifts to SUM(C3:C10), SUM(C4:C11) and so on as you copy down. Spot it: the shares no longer add up to 100%, and the bottom rows show shares that are too high. - Forgetting the over-assignment. Using
=D2*$B$12gives quotas that add up to exactly 6,000,000. Spot it: E10 equals B12 and the buffer is zero. - Hard-coding the inputs. Writing
=D2*6000000*1.1gives the right answer today but breaks the moment the VP changes the number. Spot it: click a quota cell and look for typed numbers in the formula bar. - Averaging the wrong way. Calculating each share from a different source of potential, such as account counts for some territories and spend for others, makes the comparison meaningless. Spot it: ask where every number in column C comes from.
- Rounding before summing. If you round every quota and then also round the total separately, the parts may not add up to the whole. Spot it: compare
=SUM(E2:E9)with=B12*B13. - Including the total row in the range. A range like
$C$2:$C$10also counts the total in C10, so every share is half as big as it should be. Spot it: the shares add up to 50%.
=SUM(D2:D9). If it ever shows anything other than 100%, something in your allocation is broken.
Quick reference: functions and shortcuts used
The table below lists every function, reference type and shortcut used in this lesson, with the cell where you meet it in the lab.
| Function or shortcut | What it does | Example from this lesson |
|---|---|---|
SUM(range) | Adds all numbers in a range | =SUM(C2:C9) → 15,600,000 |
$A$1 absolute reference | Keeps the reference fixed when you copy a formula | $C$2:$C$9, $B$12, $B$13 |
F4 | Cycles a selected reference through $A$1, A$1, $A1 and A1 | Press once after typing C2:C9 |
ROUND(x, -3) | Rounds to the nearest thousand | =ROUND(E2,-3) → 1,015,000 |
Ctrl+D | Fills the top cell of a selection down | Select D2:D9, press Ctrl+D |
Alt+= | Inserts an AutoSum | In E10 |
Ctrl+Shift+5 | Applies percentage format | D2:D9 and B13 |
Ctrl+Shift+1 | Applies number format with thousands separator | C2:C10 and E2:E10 |
Ctrl+T | Turns a range into an Excel Table | Optional for the rep list |
Real-world uses
- Annual planning. Every autumn, when the next year’s number arrives, a manager splits it across reps, then across quarters. The same share logic works for quarters: use seasonality shares instead of territory shares.
- Mid-year re-allocation. When a rep leaves in June, the manager has to move the open quota to the remaining reps or to a new hire. Re-running the allocation on the remaining potential gives a defensible answer.
- Product or category quotas. A distributor that wants growth in one product line can split a product-specific number by the potential of each territory in that category, exactly like this lesson.
- Channel and partner targets. Partner managers split a channel number across resellers by their market potential, often with an over-assignment because not every partner will deliver.
- Explaining quotas to the team. A one-page table with potential, share and quota answers most “why is my number so high?” questions before they turn into arguments.
What you will build in the rest of this course
This lesson is free. The remaining nineteen lessons are part of the paid course (a one-time $9.90) and use the same team, the same eight reps and the same kind of hands-on lab with an instant check of every answer. Here is what each one adds.
Attainment by Rep: Ranking the Team and Flagging Who Needs Coaching. You turn Dana’s year-to-date results into attainment percentages, rank the eight reps with RANK and add a status column that tells you whom to coach first. This is where the fair quotas from today start to pay off.
Pipeline Coverage by Rep: Who Has Enough Deals to Make the Quarter?. Six weeks before the quarter starts, you compare each rep’s open pipeline with the quota still left and calculate the coverage ratio and the extra pipeline each rep must find.
Forecast Roll-up: Commit, Best Case and the Weighted Number. You roll the reps’ deals up into the three numbers a VP asks for every Monday: commit, best case and a probability-weighted pipeline, and you measure the gap to target.
Forecast Accuracy: Who Calls the Number Right?. You compare each rep’s commit from the start of the quarter with what they actually closed, and you find out who over-calls and who sandbags, with error measures that make the pattern visible.
Activity Metrics: Calls, Meetings and Proposals against Targets. You check last week’s calls, meetings and proposals against the team targets, see who converts activity into meetings and flag reps who missed several targets, with conditional formatting.
Win Rate and Sales Cycle by Rep with COUNTIFS and AVERAGEIFS. You use COUNTIFS and AVERAGEIFS on closed deals to calculate win rates and sales-cycle length by rep, two numbers that explain a lot of attainment gaps.
Discount Discipline: Flagging Deals Above the Approval Limit. You flag every signed deal above the 15% discount approval limit and measure how much margin the team gave away, so you can have a data-based conversation about discounting.
Commission Budget vs Payout: Tiers, Accelerators and the Variance. You pay each rep through a two-tier commission plan with an accelerator above quota and compare the payout with the $104,000 budget from Finance.
Ramp Quotas for a New Hire: Tenure, Ramp Table and Lookup. You build a ramped monthly quota for a new hire from a ramp table with a lookup formula, so her first months are realistic and her number still adds up.
Territory Balance: Which Territories Are Too Big or Too Small?. You compare accounts and potential per territory against the team average and find out which territories are too big or too small before next year’s plan, which takes the ideas from today one step further.
Sales Team Dashboard: Attainment, Chart and Colour Scale. You put it all together in a one-screen dashboard for the quarterly business review: attainment per rep, a quota-versus-closed chart and a colour scale.
Summary
A fair quota split asks every rep for the same share of their market. In Excel you need only three formulas: the share =C2/SUM($C$2:$C$9), the quota =D2*$B$12*$B$13 and the buffer =E10-B12. The absolute references keep the totals and the inputs fixed, the over-assignment protects your own number, and a quick check that the shares add up to 100% catches most errors. For Dana that means $6.6 million assigned against a $6.0 million team quota, and a buffer of $600,000.
Now scroll back up to the lab and do it yourself. It takes about fifteen minutes, the Coach checks every answer, and afterwards you will be able to split any team number in your own spreadsheet with confidence.
Frequently asked questions
How do you calculate sales quota allocation in Excel?
Divide each territory’s potential by the total potential to get its share, for example =C2/SUM($C$2:$C$9). Then multiply the share by the team quota and the over-assignment factor, for example =D2*$B$12*$B$13.
What is quota over-assignment?
Over-assignment means the individual rep quotas add up to more than the manager’s own team quota, for example 110%. It creates a buffer so the manager can still reach the team number if some reps fall short.
How much over-assignment should a sales manager use?
It depends on how predictable your business is and how many reps usually miss. Many teams use somewhere between 5% and 15%; test a few values in a separate input cell and see what buffer each one gives.
Why is an even quota split unfair?
An even split ignores how large each territory is. Reps with big territories get an easy number and reps with small territories an almost impossible one, which distorts attainment, commission and coaching decisions.
What can I use as territory potential?
Common choices are the estimated spend of the accounts in the territory, the number of target accounts, or industry market-size data. Use the same measure for all territories and avoid measures that reps can easily influence, such as last year’s sales alone.
Why do I need dollar signs in the quota formula?
The dollar signs make references absolute so they do not move when you copy the formula down. Without them the total potential range and the input cells would shift, and the shares would no longer add up to 100%.
Keep going: Excel for Sales Managers: Team Quota, Forecast Accuracy, Coaching & Commission Budget
This free lab is lesson 1 of 20. Unlock the other 19 hands-on labs, the final exam and your certificate for a one-time $9.90.
- Attainment by Rep: Ranking the Team and Flagging Who Needs Coaching
- Pipeline Coverage by Rep: Who Has Enough Deals to Make the Quarter?
- Forecast Roll-up: Commit, Best Case and the Weighted Number
- Forecast Accuracy: Who Calls the Number Right?
- Activity Metrics: Calls, Meetings and Proposals against Targets
- Win Rate and Sales Cycle by Rep with COUNTIFS and AVERAGEIFS
- Discount Discipline: Flagging Deals Above the Approval Limit
- Commission Budget vs Payout: Tiers, Accelerators and the Variance
- Ramp Quotas for a New Hire: Tenure, Ramp Table and Lookup
- Territory Balance: Which Territories Are Too Big or Too Small?
- Sales Team Dashboard: Attainment, Chart and Colour Scale
- Sales Capacity Planning: Ramped Reps, Attrition and Hires Needed
- Prorated Quotas: Start Dates, Leave and Working Days
- Pipeline Generation: Weekly Creation by Source vs What Next Quarter Needs
- Deal Slippage: How Much of the Forecast Moves to Next Quarter?
- Quota Phasing: Spreading the Annual Number by Seasonality
- Commission Accruals and Clawbacks at Month-End
- Territory Rebalancing: Moving Accounts and Checking the New Balance
- Final Project: The Next-Year Team Plan