The
Sales Capacity Plan
Clevenue's ultimate how to guide on sales capacity planning, from key rules, assumptions and formulas to free templates.
Sales capacity, quota setting and sales planning in general is sorely misunderstood and miscalculated, with internet guides and leadership programmes promoting all kinds of formulas that encourage planning behaviours that only lead to missed targets and overspend.
Instead, we've gone back to basics to work out the real maths behind revenue planning, creating a new set of revenue formulas that you can use and trust, that we'll break down the why and how.
The below guide is a comprehensive guide to the sales capacity plan, covering off some key rules to follow, the maths, some of the modeling, and some of the logic behind a realistic sales capacity plan, as well as how you use it to drive targets, and not the other way around.
Grab yourself a coffee and a biscuit before starting as it's around 30 minutes of reading time, however it goes deep on the formulas and concepts so you might need longer!
Use the jumps below to skip to any of the key sections:
Let's dive in.
What is Sales Capacity?
Don't worry, this guide ramps up very quickly, but it makes sense to re-align your potential knowledge out the gates. For the purpose of this guide, Sales Capacity is a measure of how much capacity a sales rep has to do the work required to generate revenue from opportunity. Based off their working week, it should account for time spent doing any of the following activities, even if they don't lead to revenue:
-
Generating Opportunity
-
People Managing (i.e. leading a team)
-
Qualifying Opportunity
-
Managing & Closing Opportunities
-
Account Managing Clients
Any of the above activities may form part of someone's role, however the more time that is needed on activities outside of managing and converting opportunities into Closed Won revenue, the less actual capacity is available in a form that maps to quota.
What is a Sales Capacity Plan?
A Sales Capacity Plan is a model of what capacity your business has do to all of the activities that are required in order to generate revenue, and how it maps against your people, their roles and your future plans covering marketing and people hiring.
This includes:
-
Opportunity generated through planned marketing
-
Qualifying marketing opportunities via SDRs or AEs
-
Understanding what additional pipeline is needed to reach target
-
What pipeline is generated by the current team
-
What additional marketing or sales hiring is needed close the gap
Notice how quota isn't mentioned anywhere above? For good reason, but we'll come back to that in a mo.
The sales capacity plan should be one of the most intricate models that is built, and given that revenue generation is pretty much the lifeblood of businesses (especially post ZIRP era), not dedicating appropriate time to this part of planning is a sure fire way to miss targets.
So we've got the basics down and we'll come back to them all shortly, however there's a few key rules you should follow if you want to build a plan that actually works (and not just ticks a box for finance/exec):
Sales Capacity Golden Rules
There's no rules that exist when it comes to planning sales capacity, but we feel that there should be. These rules act as guardrails for your process, and everyone involved from building through to interrogating and signing off on the plan should know, understand and accept these rules.
Doing so helps you to build a plan that actually works, instead of something that simply ticks a box:
Rule 1: Quota Coverage ≠ Capacity
The single most misunderstood part of sales planning, and one of the leading reasons behind businesses over committing, over hiring and over spending on sales teams. When you hear of sales teams at 50-60% attainment your first thought shouldn't be to question the skills of the sales teams, it should be to ask if there was ever justification for a team of it's size.
Quota coverage is like a fuel tank in a car. It's a representation of both how much fuel the car can hold, and based on an average MPG, you can estimate how far you can travel.
Let's assume that you have a car that can hold enough fuel to travel 1000 miles.
If you half fill that car, you will travel 500 miles. Adding more cars to the fleet will not increase how far you can travel - EVERYONE will break down at 500 miles if everyone gets a full tank.
Even better, lets say you have 5 full tanks of fuel and 10 cars.
Now every additional car that you add is going to reduce the amount in each car even further: If I add 10 more cars (so 20 cars in total) I now have 1/4 of a tank for each car, and each car will travel 250 miles.
You would have to be stupid to blame the cars here right? This is where we'll stop...
The solution?
Reduce the fleet of cars down to 5, so that your 5 tanks of fuel fully fill the 5 cars. Now you have 5 cars that each can reach 1000 miles. (And aren't paying for the cost of the additional 15 cars...)
It all makes so much sense when put this way, right?
Rule 2: Assumptions in = Bad data out
We all know that bad data in = bad data out, however bad assumptions can quickly kill a plan also. Much like the above, the problem is that you don't realise that you've killed your plan until your progress starts to diverge from what you expect, and by then it's too late. Bad assumptions are in effect the worst kind of bad data as the assumptions are very deliberately chosen.
This means that when it comes to your assumptions, you should be very careful about which ones you make, and why, and ensure that you always keep sight of how they compare to actual performance.
Even better is to not use assumptions - Create your baseline plan based solely on actuals, and if you're aiming to improve certain KPI's, create additional scenarios that unlock new routes.
Only once the business is achieving the elevated KPIs should you actually move from plan A to plan X.
And what happens if you ignore this rule? Let's play out a scenario:
The SDR team generate 50% of the current pipeline, however we're hiring enablement so we believe that they are going to be twice as effective (lol) as before, and so generate all of the pipeline. This means that we can cut marketing budget, and there's no need for additional hires.
Fast forward 6 months and the increase is a 20% performance increase (awesome)
But we'd accounted for 100% increase (guh)
Instead of generating 100% of the required pipeline, you're generating 60% and the remaining is simply missing as you didn't hire, and you cut marketing spend.
You're already 6 months through the year and depending on how fast your sales cycle is, you might not even be able to recover in time to overachieve Q4 enough to save the year (not only would you need to hit 100% over Q3 & Q4, but you need to make up for 50% in Q1 & Q2)
You can now make a far more sensible assumption in that you're not going to hit any more than 50% of the annual target. Great. (Not).
Rule 3: Scenario Test the Bounds of Bad Data
Bad data can't be avoided, but all data comes with a degree of confidence, and this is at least a starting point. Whilst the above rule about assumptions is based on outperforming the current state, there might be certain parts where you have to assume a data point that is based off a current trend, and not just wishful thinking.
In this case, it's not unreasonable to create an assumption, however you need to understand both sides of the coin.
Scenario testing your plans helps you to understand every eventuality, helping you to build contingency into your plan and to move faster when things don't go as expected.
To do this, for each unsure KPI, you should create some reasonable bounds and scenario test each of them, with an appropriate plan in place for every scenario, especially in the worst case direction.
For example:
Conversion rate between Stage 2 and Closed Won is all over the place, but we think it could be 17% on average.
We feel confident that it's at least 10%
It could be as high as 22%
We should create a plan based off 17% conversion
A new plan off a scenario that is using 10% conversion
An additional plan that is based off 22% conversion
Then you should closely monitor and track the KPI, updating the plan or moving between scenarios as the picture of the accurate figure becomes clearer.
Side note: This is naturally more difficult to do in spreadsheets (due to the permutations), it can take seconds in our planning sandboxes however.
Rule 4: Finish Planning Before the Sales Cycle Starts
Are you planning for a full year, or just the final 3 quarters?
For many tech & SaaS businesses, planning season doesn't kick off until Q4, and doesn't often conclude until right at the end of it, which is problematic.
Let's say that you have an (optimistic) sales cycle of 3 months - Everything that you do today will count towards your targets in 3 months time, and no sooner.
But what if as part of the planned efforts you need to hire? You can add an additional 2 months onto that, plus time to ramp.
Here's the problem with planning in annual cycles: You're starting too late.
Even with a 3 month sales cycle, any plan that requires hiring sales to reach a sales target requires a 5-6 month run up.
If you work to a calendar year, it means that to hit a January target you need to have plans finalised in June/July. Your February relies on certainty in July/August, and your entire Q1 is missed if your planning doesn't finish by August/September!
This means that if you're wrapping up planning in November/December you may as well write off the entirety of H1, and this means that any revenue growth is going to have to be found in H2. If by then things like sales cycles or conversion rates have shifted, you might find yourself needing a new plan or even worse, up s*** creek without much of a paddle.
Rule 5: Hire for Capacity in the Correct Areas
Right at the start of the guide we covered what sales capacity is, and what it means in terms of planning, but this is one of the most critical areas (along with understanding that quota ≠ revenue)
Hiring for capacity in the right areas means understanding all of the tasks of the sales and retention process, and designing a effective and efficient team around it.
A key thing to define is what you mean by efficient - Do you care about the overall time, quality or cost to do a task?
Sure, a Senior Account Executive (AE) might be better than a Senior Sales Development Rep (SDR), however if you're paying an AE twice the amount of salary as an SDR, where does it make sense getting an AE to do work that could be done by an SDR?
Let's give an example:
-
You pay an AE $2,000 a week
-
You pay an SDR $1,000 a week
To generate 2 opportunities
-
It takes the AE 2 days
-
It takes the SDR 2.5 days
That means that per opportunity it costs
-
$333 for the AE
-
$200 for the SDR
Are the leads from the AE really worth the 65% premium? Especially when it reduces how many concurrent opportunities they are able to handle?
Even worse...
An AE carries a $700K quota, and they had an SDR supporting them (generating 4 opportunities a week). The SDR team were laid off as they weren't quota carrying, on the basis that the AE teams could prospect.
To achieve the same volume of leads (4 a week) the AE will need to spend 4/5 days of their time prospecting, leaving one day to managing pipeline.
Based on the old quota of $700k, each working day was equivalent to being able to handle $700K/5 of quota, so with one day left for handling and closing revenue, their new capacity is worth $140K of quota...
OK it's way more complex and nuanced than that, however the overall premise is hard to argue.
Going back to hiring for sales capacity in the right areas, it means understanding the following:
-
How many opportunities do we need to generate to hit company target?
-
How many of these opportunities should come from marketing?
-
How many of these opportunities should come from AEs?
-
How many SDRs do we need for the remaining gap?
-
How many opportunities can an AE handle concurrently?
-
How many AE's do we need throughout the year to hit goal?
From here you then design your roles to cover each of the capacities, and then calculate how many of each role you need throughout the year.
Calculating Sales Capacity
Breaking down the maths of sales capacity and quota isn't difficult, however it involves looking at the maths of your business completely differently to how you've done so previously.
This is not about calculating targets - This is about understanding how much revenue can be generated, and the definition is very simple:
Sales Capacity is the volume of sales activities that a sales person is capable of carrying out as part of their usual duties
Whilst for quota bearing reps it's typically represented as their quota - It is actually a calculation of the end output of doing all of the activities that can be achieved at capacity, at average performance.
What this means:
Sales Capacity - Activities
-
Lead generation
-
Lead qualification
-
Opportunity managing
-
Opportunity closing
All of these are handled by the business, and will carry an average performance across the team generating the following data points:
Sales Capacity - Activity Data Points
-
Average Conversion Rates
-
Average Deal Lengths
-
Average Deal Sizes
These then stack up to give the equivalent revenue, from doing all of these activities.
Sales Capacity Formulas
It's time to get technical so don't worry if it doesn't click first time round (and if you struggle reach out to me), here are some new formulas that don't seem to have been put to paper before, so we'll take the time to explain how it's all derived.
Because sales capacity and quota setting are inherently linked, and quotas are set to cover a whole year of revenue, the maths is designed to cover a year period, or the annual sales capacity of reps.
Calculating Annual Number of Sales Cycles
This is the number of sales cycles that can occur within a year, based on the time it takes to open and close a deal. If you had a 12 month sales cycle, this would mean that you could only work through one sales cycle per year. If you had a 6 month sales cycle you could run through 2 cycles back to back.
The maths for this is simple:
Number of Sales Cycles = Working Weeks (Annually) / Average Deal Length (Weeks)
Example:
Number of Sales Cycles = 48 Working Weeks / 12 Week Average Deal Length = 4 Sales Cycles
There's a few things that you need to take into account however:
Working Weeks are the total number of weeks that a sales rep is available for work, and so is likely to be:
Working Weeks (Annually) = 52 Weeks (Annually) - [ Holidays (Weeks) + Other Absences (Weeks) ]
Example:
Working Weeks (Annually) = 52 Weeks Per Year - [ 4 Holiday Weeks - 0 Other ) = 48 Working Weeks
Calculating Sales Rep Capacity per Cycle
We've calculated how many cycles a sales rep can go through per year, now you need to calculate how many opportunities they can handle concurrently in each cycle.
Again, the maths for this is simple:
Sales Capacity = Sales Working Hours (Weekly) / Weekly time spent per opportunity (Hours)
It's important to note that the unit of measurement for sales capacity in this formula is opportunities.
Let's look at some example maths:
Sales Capacity (Opps per Week) = 33 sales hours / 1.5 hours per opportunity = 22 Opportunities
This maths is looking at how many hours a rep has that they can dedicate to activity that turns opportunities into revenue, covering everything like:
-
Discovery calls
-
Demo calls
-
Proposal writing
-
Follow up
-
All other deal linked activity
Where we define sales working hours, we're meaning the weekly working hours less any hours lost to non opportunity handling work, this means you should exclude pipeline generation from this.
Sales Working Hours = Working Hours - [ Time in non-sales meetings + Prospecting + Other Admin ]
Example maths:
Sales working hours = 40 hours - ( 2 hours of meetings + 4 hours prospecting + 1 other ) = 33 sales hours
Calculating capacity (and subsequently quota) means that you can put a dollar cost on internal meetings (like 1:1's, standups etc.) as well as on prospecting.
When using our calculator you can see how increasing the requirement for a rep to prospect decreases their capacity to handle pipeline opportunities. This means that there's a real business case to not having your best reps handle prospecting, and instead using SDRs.
Calculating Total Annual Sales Rep Capacity
We've calculated how many cycles a sales rep can go through per year, now you need to calculate how many opportunities they can handle concurrently in each cycle.
Again, the maths for this is simple:
Sales Capacity (Annual) = Sales Capacity (Weekly Opps) X Number of annual Sales Cycles
The unit of measurement for sales capacity in this formula is annual opportunities.
Example maths:
Sales Capacity (Annual) = 22 Opps Weekly X 4 Annual Sales Cycles = 88 Opportunities Annually
So now you have a capacity figure for how many sales opportunities can be handled throughout the year. It's worth recognising that this is maximum capacity - Having the rep handle more than their ongoing capacity of 22 opportunities will require them to find more hours in the week, or reduce the time spent on each opportunity.
This means that running reps overcapacity will result in either burnout, or a decrease in quality of work, likely leading to decreases in the sales performance of the rep. In turn, it can lead to a decrease in sales conversion rates and deal sizes.
Like we say - Capacity really does matter!
Calculating Sales Quota
Now that you have the sales capacity as a function of opportunities, you can calculate what that equates to in terms of revenue. To do this, we simply bring in a couple of deal averages:
Sales Quota (Annual) = Sales Capacity (Annual Opps) X Average Conversion Rate X Average Deal Size
Example maths:
Sales Quota (Annual) = 88 Annual Opps. X 23% Conversion Rate X $35,000 Deal Size = $708,400
Remember - This is based on the rep being at 100% capacity throughout the year, and so would carry a 100% utilisation also.
Put into non maths terms:
Sales Quota is the sum of producible revenue, based on handling the average number of opportunities at average capacity, for the average length of time, won at the average closed won rate
Sales capacity dictates the volume of opportunities that can be managed at any one point in time, however it's all based upon the averages.
What this means is that if the volume of opportunities generated, is not equal to the capacity used to calculate quota, revenue will be less than quota.
In real world terms, if any proportion of a sales quota comes from lead flow that is out of a sales reps control, that is lower than the amount used to calculate quota, that sales rep will not achieve target without additional work on their part to close the shortfall - This will in return work to reduce their capacity to effectively handle opportunities.
It also means that when increasing overall targets, it should only be done off the back of increasing opportunity flow. If not, all it serves to achieve is widening the attainment gap from goal, which is to the detriment of team morale & culture.
Overall Sales Quota and Capacity Formula
Stitched back together, the formula for capacity isn't particularly difficult to use, however it does require an understanding of the work involved with the sales role, and current sales performance - This is a good thing as given that the earnings of the reps stems from the quota, whoever is calculating it should have an appreciation for the amount of work that is involved to achieve it.
The full revenue formula is as follows:
The above approach is naturally a very different way of looking at capacity and quota, compared to the typical target derived quotas that work top-down from a company or market target.
Using this approach provides a fairer route to creating goals and commission plans, with targets set in a more realistic realm.
As we'll cover in the Revenue Modeling Section, it does not however represent projected revenue attainment, and is only a measure of what is achievable at capacity, provided that there is a sufficient flow of leads.
Revenue Modeling
Modeling out revenue as part of your sales planning & progress monitoring and you're (almost) guaranteed to need a tonne of different spreadsheets (we have a bunch of templates to get you started).
Now that you understand how to calculate the sales capacity of your reps, there's some additional concepts that you need to understand before building your models.
These then stack up to give the equivalent revenue, from doing all of these activities.
Sales Capacity ≠ Sales Attainment
Ok, this should probably have been one of the Golden Rules, however we already had "Quota Coverage ≠ Sales Capacity", this rule takes things one step further however:
When you're calculating sales capacity, it's on the basis that opportunity is provided to the level of available capacity.
In simple terms - That capacity of 88 opportunities will turn to the $700K of revenue, on the basis that not only is the average deal size and conversion rate is achieved, but all 88 of the required opportunities are provided.
Sales people are not magicians, and despite the illusion of finding money at the end of a year or quarter, revenue does not just appear.
The volume of revenue generated can be assumed to be directly proportional to the volume of leads provided to a rep, versus the volume of leads that they need to achieve full capacity.
What this means is that your sales capacity models really need to be a model of how you generate leads, who manages them, and their capacity to handle them - This is where Bottom-Up Planning comes into play.
Bottom-up modeling of revenue is actually counter-intuitive name-wise as it refers to modeling from the top of the revenue funnel, all the way through to sale. It's the namesake however of Top-Down modeling, which is the approach of working down from target.
Top-Down modeling is a natural starting point for most businesses, however there's a fundamental issue with the using approach, even when it's a tops-down bottoms-up:
-
Overall Target is handed to you
-
Overall Budget is handed to you
Remember the car fuel tank analogy from the Golden Rules? Tops down bottoms up is equivalent to:
Please drive this car from New York to San Francisco, we're only giving you half a tank of fuel. Please use the map to show us exactly how you're going to get there.
Sounds stupid, right?
This is how businesses accept targets that are unachievable, only to kick the problem of missing them further down the road.
This is why the bottom up model is critical.
Building a Bottom-Up Revenue Model
Building a bottom up model requires several different components, and you can either build it in a spreadsheet, use a template or connect your data with a dedicated planning platform.
You're going to need data covering:
-
Marketing lead volumes
-
Inbound Capacity
-
Outbound volumes by role
-
Sales Capacity by role
-
Average Deal Sizes
-
Average Sales Cycles
-
Average Conversion Rates
Where you have multiple markets or segments, you'll need the data for this separately for each market and segment.
We run down the maths of building a bottom up model in our Revenue Academy Basics lesson, you can watch the video explainer for it below:
Modeling sales attainment & opportunities
When building up a bottoms up model, there needs to be something at the top that you're working up towards, and this typically is the gross target, or company target.
This target is made up of the sum of all individual targets, with the purpose of the model being to work out what is needed to achieve 100% of that gross target. You'll probably want to build in a cushion, however however this is almost always incorrectly applied, however we'll come onto that in a moment.
As we've already mentioned:
The volume of revenue generated can be assumed to be directly proportional to the volume of leads provided to a rep, versus the volume of leads that they need to achieve full capacity.
And given that the maths for calculating sales attainment is:
Sales Attainment = Revenue Target / Achieved Revenue
We can easily build a formula for calculating it, based on the volume of leads and the sales capacity. The assumption that we're going to make here is that the sales target is equal to the calculated sales capacity (and not any more!)
Remember, the formula for calculating revenue is:
Achieved Revenue = Opportunities X Opportunity Conversion Rate X Average Deal Size
This gives us the following formula for calculating attainment:
You'll note how because the opportunity conversion rate and average deal size feature at both sides of the division, that they cancel each other out. This means that you can simply calculate attainment by dividing the opportunities at capacity, by the volume of opportunities that are actually received & generated by the sales rep.
This means that simply by creating a model of the flow of leads through the business, you can model out the attainment of reps, on the basis that their revenue target is no larger than their capacity.
Don't forget:
Running reps at overcapacity will result in either burnout, or a decrease in quality of work, likely leading to decreases in the sales performance of the rep. In turn, it can lead to a decrease in overall sales conversion rates and deal sizes.
This is why it's so important to get capacity modeling right.
Top down modeling from target
Even when building a bottom up model, there's an aspect of tops down that has to happen, this is why it's frequently referred to as a top down bottoms up model. This aspect of it however is limited and typically stems from the target, and when done correctly it's fine as a practice.
Naturally, there's going to be a company revenue or growth target, and so the end revenue output of the plan is going to be a known figure that you're aiming for. Modeling this isn't simply about taking a revenue target and dividing by quotas, however it's reasonably related as an approach.
The purpose of this top down part of the model is not to calculate how many reps you need, but to calculate how many opportunities you need.
So what does this look like:
Total Number of Opportunities = Company Target / ( Opportunity Conversion Rate X Average Deal Size )
Example maths:
Total Number of Opportunities = $2,880,000 / ( 23% X $35,000 ) = 358 Opportunities
Now that you have the total number of opportunities that are required to reach overall target, you can use this figure to calculate how many sales reps you need, based on the sales capacity per rep that you've already calculated. Remember, this is on the basis that sales quotas are equal to 100% sales capacity:
Required Number of Sales Reps = Total Opportunities / Sales Capacity Per Rep
Example maths:
Required Number of Sales Reps = 358 Opportunities / 88 Opportunities = 4.1 Sales Reps
Sales Overassigment / Target Cushions
Let's be realistic: You're not going to set your company targets at exactly what you're reps are capable of achieving and are likely to want to add a safety cushion to them - this is often termed as overassignment.
The problem with it historically is that it's built in by assuming that reps hit 80% of quota, which is actually a self fulfilling prophecy. As you'll have seen from the above, the volume of leads that you deliver to a team is proportional to attainment, and so when your plans are built to deliver 80% of what is required, you can pretty much guarantee what the outcome is.
So how do you build in a target cushion to your plans, without ending up with an unhappy sales team who have no path to goal, without having to resort to working 7 days a week to get there?
It's simple, you add your cushion over the main target, and work backwards the same as before. Let's use the previous example, but build in a 20% cushion:
Cushioned Target = Company Target / ( 100% - Cushion (%) )
Example maths:
Cushioned Target = $2,880,000 / ( 100% - 20% ) ≈ $3,600,000
Now going back and calculating it as a volume of opportunities, and the reps required to handle them:
Total Number of Sales Reps = ( $3,600,000 / ( 23% X $35,000 ) ) / 88 = 5.1 Sales Reps
But isn't that the same mathematically as simply adding my cushion onto the number of required sales reps? Yes it is! However you're still going to need the total number of leads as that is going to be used to calculate the rest of your sales and resource needed to hit target.
N.B - When most people talk about a X% cushion, they aren't mathematically referring to adding it on. Instead, it's usually about being able to fall that short, hence why the math is this way round.
I.e. Hitting 80% of a 100 target gets you to 80, and but if 80 is the minimum number, adding 20% on as a buffer only gets you to 96 (and so setting that as a target would leave you at 76.8 at 80% which is enough of a difference to be a problem...)
Modeling sales lead generation, marketing & SDRs
This one is a biggie, so repeat after me:
The impact of an SDR should not be modeled as a ratio of the number of quota carrying reps
This is where almost every sales capacity plan that we've ever seen falls down, and when businesses make cuts their inability to model this vital part of the revenue model leads them to cut in the complete wrong way, often cutting off their outbound lead gen efforts in an effort to retain quota carrying capacity.
Spoiler alert: There's no point in retaining quota-bearing sales reps if they have no opportunity
Let's go back to our model of opportunities that are required to reach target. We have a total figure for the volume of leads that are required for the company, including a cushion. Our running example works out as having 4 sales periods, so we're going to treat them as quarters, and our annual target is going to ramp through the year.
Using formula from above, we translate the revenue target into an equivalent number of opportunities that are needed to reach target:
Now we have the number of opportunities, it's time to revert back to a bottom up approach to calculating. We now need to find out if we have enough resource to fulfil our pipeline / opportunity needs. This example uses a very generalised marketing plan, however you can (and should) get granular here.
We're using overall MQL volumes by quarter, and an MQL to Opportunity conversion rate:
You'll note that the volume of opportunities generated by marketing are less than what are required to reach target, this leaves a shortfall which you can use to calculate how many SDR's you need to be hiring into the business (or how much extra marketing spend is needed).
We calculate using the following:
Lead Shortfall = Total Required Opportunities - Marketing Derived Opportunities
As a full formula:
Lead Shortfall = Total Required Opportunities - ( Number of MQLs X MQL to Opp Conversion Rate )
Using the first quarter, it looks like this:
Lead Shortfall = 75 Opportunities - ( 150 MQLs X 33% Opp Conversion Rate ) = 50 Opportunities
Now that we know the shortfall from target, we can create a plan to close the gap, through either additional marketing spend (adding additional MQLs) or through sales hiring of outbound sales reps (typically called Sales Development Reps or Business Development Reps)
To calculate this, you need to set an outbound lead target for these reps, which should be based off current team performance (if you have existing SDRs) or benchmarks from your industry.
In this example, our SDRs are targeted to generate 6 opportunities per month, equivalent to 18 opportunities per quarter. We then use this along with the shortfall to calculate what that equates to in terms of headcount:
SDR Headcount Required = Lead Shortfall / SDR Target
Running the maths for the first Quarter:
SDR Headcount Required = 25 Lead Shortfall / 18 Leads per Quarter = 1.4 SDRs Needed
So you should hopefully have spotted a huge flaw in the above model: Time
The model doesn't account for a couple of critical factors that have a significant impact on the efficacy and accuracy of your revenue plan: Sales Cycles & Sales Rep Ramping / Onboarding.
Let's go even deeper into the model.
Accounting for time lag in Sales Modeling
The time lag throughout sales is has a huge impact not only on end revenue, but on how you time your decisions, and how you build your models. You might think that it's easy to handle, however as I'll explain, the more accurately that you want to model, the mode difficult it becomes (one of the many reasons that by now you should be considering a platform to do all of this)
As alluded to above, there's two main areas where lag is introduced, however even the way that people approach some of these concepts is fundamentally wrong, and understanding it requires understanding sales, people and looking at it with a fresh view of how sales works.
Let me explain:
Sales Cycle Lengths & Deal Lag
After:
Notice how in all of the models so far, opportunities seem to convert to revenue in the exact same period that they are required?
When you start to break your model down by month, it would be assuming that you can generate & close revenue in the same month - Unless your sales cycles are under 30 days it's not just wholly inaccurate, it's dangerously wrong.
This is where your models need to introduce a lag, or the time it takes for an opportunity to be worked from opportunity to closed won revenue, and this time is called a Sales Cycle Length.
The Sales Cycle Length is the time it takes for an opportunity to turn into closed won revenue
This is typically measured through tracking the timestamps of an opportunity in a sales funnel, and comparing the duration between entering the sales stage that signifies being an opportunity and the sales stage at which it closes.
Let's go back to our ongoing example, you'll see that we've split out our revenue targets, as well as our MQL volumes by month. Because of this, we're now calculating the SDR headcount using their monthly target of 6 (previously it was 18 a quarter)
As you can see, in the example revenue ramps in a reasonably linear fashion, as do the MQLS, however all of the lead generation maths happens in the same month as the target.
What we need to do is to defer the opportunity generation by the duration of the sales cycle, so that all of our opportunity generation efforts are calculated correctly.
In this example, we're going to run with the following:
Sales Cycle Length = 60 Days = 2 Months
What this means is:
All opportunity generation should happen 2 months before the revenue target that they convert to
Ok but in simple model terms:
All opportunities requirements should be deferred backwards 2 months
So let's look at what this means in terms of the model - We'll add 2 months to the start of it for "the past" and we'll trim it down to make it easier to understand.
Before:
You'll see that by moving the opportunities back in time, all the other calculations adjust to account for it. The net effect is a complete change to headcount needs. Whilst the need to generate opportunities to account for the sale cycle can be combatted by bringing forward hiring, the marketing plan is set (for now) and so whilst the opportunities that stem from them initially felt like they kept pace with revenue growth targets, the reality is that it arrives too late.
From a headcount perspective, it results in a shortfall of leads in the two months prior to the start of the year that can only be addressed through hiring, or adding in the marketing plan for the current year (which should go some way to remove the deficit)
Overall, you can see comparing January across both plans that through accounting for the sales cycle, it increases the headcount need by over a person, with the need for one and a half more people than originally planned by December.
Modeling sales ramp
You're making progress, I bet you're glad we suggested reading this with a coffee!
It's time for yet another honest conversation about how sales capacity models are built, and it's one that we desperately need to have - No-one is modeling sales ramping correctly.
What do we mean by this?
Let me tell you why - Imagine a 6 month sales cycle.
That is a sales cycle where from it becoming an opportunity, to turning into closed won revenue, it takes 6 months.
Whilst granted you can hand a new rep some in-flight deals (i.e a rep has left and you transfer pipeline), chances are that even when someone leaves, you transfer it to more tenured team members.
In the above example then, it becomes an almost statistical impossibility to close revenue before the the 6 month period, or 7 month if you account for the first month being complete onboarding.
So in this scenario, to assume 15% revenue attainment in month 2 would assume a 15% chance that they ability to close revenue 500% faster than the statistical average for deals cycles in your business.
But wait, there's more, remember this:
The volume of revenue generated can be assumed to be directly proportional to the volume of leads provided to a rep, versus the volume of leads that they need to achieve full capacity.
Is it ingrained yet? What this means in the context of ramping is that ramping is less about a sales reps ability to generate revenue, and more about their ability to effectively handle leads.
Quota bearing rep ramping is a function of their growing effectiveness to carry out their role in the business, and their subsequent growing capacity to handle opportunities effectively.
What this means is that you need to take a completely different approach to how you model the output of a rep that that is joining your business, and how their effectiveness grows. At a bare minimum, this should incorporate the fact that any leads in month 1 will not until they have seen out the average sales cycle duration, but their efficiency is likely to also have an impact on their overall deal conversion rates and deal sizes compared to company average, until they are fully ramped.
This applies to both AE and SDR (both inbound and outbound) style roles, however it has different impact on the end output that you'll see from each, and what it's supposed to model.
We'll wrap this bit up by pointing out that we use our own proprietary people models to more accurately model the effects of not only ramping as a new starter, but the equivalent effects as people move into new roles throughout the business, as well of course as the impact of capacity and how that is affected by the flow of leads and revenue.
NB: Hiring Lag
There's another time lag assumption that we're not building into this - Hiring lag.
This is yet anther lag that you need to account for, that you then apply over the headcount model to account for when you need to start hiring for, so that you can ensure that you have people in seat. This deferral should happen on a separate plan for execution, and your models should remain modelling once someone lands in seat. However, it's important to ensure that there's business visibility in to who needs hiring and when, so that the risks of delayed hiring are minimised.
Creating a Sales Headcount Model
So we're almost at the end of the model, you have suggestions on what your headcount should look like, now you need to build a headcount plan that acts on the suggestions and then models from the bottom upwards to give you an overall revenue output.
We'll go back to our ongoing example, at the point where we have the deferred hires. The suggestions are contain partial hires, and so there needs to be a decision around at what threshold you decide to hire another person. It's almost gut, but acting on data. Once you have the whole year modelled out you can then move people around to suit.
SDR Headcount Planning
To continue with the example, we're now adding SDR hires based on the required SDR headcount. To make things simple, we're not going to ramp their performance, however we are going to assume that they don't become productive until month 2 - This means that for each SDR hire, they are producing 0 opportunities in the month they join, followed by 6 the next month.
Because of the sales cycle being 60 days long (or two months), opportunities are offset by 2 months. This means that there's a need to generate opportunities in November, and because of a lack of marketing leads, there's a need for the equivalent output of 3.5 SDRs.
Once we hit March however, and Marketing starts to kick in, this need decreases to 2 SDRs.
Because of this, we're going to add in 2 for November, and we can see the remaining cumulative opportunity shortfall still increases month on month. We also don't produce any revenue in January due to the opportunities not being generated until the following month, with the revenue arriving from SDRs startin in February onwards.
The total revenue generated in this scenario is $2,907,660 which is 80.8% of target
This is enough to get over the core target, but doesn't leave any room for manoeuvre (hence the cushioned target)
Adding an additional SDR in November increases the volume of opportunities created out of the gates, providing enough to overachieve between March and August, which is a great opportunity to get a head start on the year.
Despite this, from September onwards
The total (12 month) revenue generated in this scenario is $3,438,960 which is 95.5% of target
This is closer to target, but still not enough.
Adding an additional SDR in November increases the volume of opportunities created out of the gates, providing enough to overachieve between March and August, which is a great opportunity to get a head start on the year.
Despite this, from September onwards
The total (12 month) revenue generated in this scenario is $3,438,960 which is 95.5% of target
This is closer to target, but still not enough.
Finally, by placing an additional SDR into the business in June, it's just enough to creep over the 100% of projected revenue.
The total (12 month) revenue generated in this scenario is $3,632,160 which is 100.1% of target
N.B - You may note that in here we're not accounting for attrition for rep departures, and there's many ways that you could do that, however for now we've going to park it.
AE Headcount Planning
We now know that there's enough opportunity generation occurring within the plan to get to target, on the basis that there are enough people within the business to be able to handle the opportunities and convert them into revenue. This is where AE based roles come into play, and with the volume of opportunities ramping to keep pace with targets, it's not as simple as dividing a company target by a quota and hiring that many (as we've already covered)
Let's go back to some of the assumptions that we already have in place:
Company Target (inc. cushion) = $3,600,000
Annual Rep Quota = 100% Rep Capacity = $708,400
With old top down logic, you might simply divide your target by the quota to figure out how many reps:
Sales Reps Required = $3,600,000 / $708,400 = 5.1 AEs
The problem with this is that the capacity requirement isn't consistent throughout the year, and so it leads to periods of under-utilisation of your reps capacity followed by overutilisation (i.e. running them at overcapacity)
Continuing again with our example, we've added a few more rows into the model, this time for total AEs. Their 22 a month opportunity capacity is divided by 3 to give the equivalent of 7 a month (rounded), and the purpose of this part of the headcount planning is to ensure there's enough capacity coverage for the volume of opportunities that are required to get to revenue target.
It starts of with an assumption of 5 already in the team, but you can see that from June onwards the teams are way overutilised, working over capacity. This means that there's additional hiring need. Neither November or December are wholly representative of utilisation as the SDR opportunities don't extend back to before November, and the marketing leads don't kick in until January, however you can see that with 5 the utilisation leaves a little room for capacity, but not enough to downsize the team.
There is a clear hiring need however, and we now add hires into June, July and October (below). You'll note that there are 3 extra lines, for the effectiveness of each of these hire - We have not built in a ramp for them, however we can see that removing the ramp effects we're able to level out the utilisation, and make effective use of opportunity.
There is already a delay to the revenue output of each of these reps, thanks to the sales cycle - Any opportunities that they are capable of picking up convert to revenue 2 months later.
We do however need to build in a ramp, and to keep things simple, we're making a 3 month, 0%, 50%, 100% ramp.
Adding in the ramp reduces the efficacy and their subsequent capacity, which for the Total AE's you can see that there are "half" AE equivalents showing in certain months. With this effect in place, it means that the AEs need to be moved earlier into the plan, to ensure that they are ramped in time to provide the capacity when it's required.
There is one final consideration however - Timing.
Whilst bringing people in for effective coverage is idealistic, it's not realistic. Onboarding a single rep on their own, for 3 months of the year triples the amount of onboarding, account allocation & team integration compared to larger joining cohorts.
As a result of this (and the risk of early attrition), it's typically recommended to hire in groups of 3+
The final change we made to the model to group the AE hires into the month of May, to avoid the problem of staggered onboarding.
So there we have it - The end output of the Sales Capacity & Headcount model, with hiring for both AE's and SDR's, modelled from the bottom up. But we're not done, there's many many issues with this approach still (all of this for one single market segment...), and we haven't even gone into how to test of different scenarios using it.
Time for another coffee?
Let's dive straight in!
Scenario Testing a Revenue Model
We've built a revenue model and headcount plan, and we're pretty happy with the output, but the reality is that there's very little likelihood that the performance & assumptions in the plan are able to stick throughout the year, so what next?
This is where scenario planning comes in, an approach which involves testing and understanding the outcomes of many different permutations, and this is where the above goes from being pretty difficult to build and maintain to an absolute nightmare of a spreadsheet (and you could be expected to share it around the exec team so people can "play around" with the inputs)
Why do it?
You already know that your plan isn't going to stick exactly, which is fine however, you're going to want to know at what point you need to make a change.
Like the Titanic heading for the iceberg, a change in direction early enough would have made missing it easy, but the closer you get to impending disaster, the bigger the moves are that you have to make to correct course and get back on track.
Let's go back to our plan and look at some simple scenarios that could hit, remember we have some assumptions:
-
Average deal size is $35,000
-
Sales Conversion rate is 23%
-
Sales Cycle is 60 days
In 3 different scenarios, they all lead to drops in revenue of at least 10%, with the table showing:
Average deal size falling to $29,000
Sales Conversion dropping to 20%
Sales Cycle increasing by a month
The effect of each are considerable, however when stacked as a scenario that is a combination of all 3, the effects are pretty disastrous, with overall attainment dropping by over 37%
The biggest challenge with scenario testing is the combination of scenarios, involving changes to KPIs, different hiring & marketing delivery pacing, as well as other strategic decision making that can hit progress. It's not just about revenue attainment however, it should also be about understanding efficiency:
Scenario testing is about comparing different strategies to find the most efficient and cost effective way of reaching revenue target.
In our State of Revenue Planning Report, we found that businesses that scenario tested their revenue plans were 4 times more likely to hit target, than those that created a plan and ran with it, and so we believe that it's a critical part of planning that shouldn't be ignored, and should be revisited continually with current progress.
How to Scenario Test a Plan
The first place to start is with your finished revenue plan - This is your starting point and your baseline plan for comparison. From there there are a couple of different ways that you can scenario test, which all depends on how much trade off you want between planning fidelity and comparison convenience:
Option 1. Create a simplified model with side-by-side comparisons
This option gives you a rough and ready comparison of most KPIs, however it's more for getting a gauge of the difference between two KPIs, rather than trying to produce a more accurate understanding of revenue through a waterfall method. What it won't allow you to do is to understand what it means for your hiring plans, especially as your revenue targets move throughout the year.
We have a handy template in this format that you can use for this purpose - Grab it here
Option 2. Make changes in your model and record the outputs
This is the dumb option, but is what we used for the above table - It's a rough and ready method that involves changing the core model with a new scenario, and then noting down the results before moving onto the next.
The problem with this approach is that should you want to tweak the scenario (like changing the hiring approach) it involves going back and redoing it, again. We don't recommend this however it's the most typical approach and eats lots of time.
Option 3. Create interlinked versions of your waterfall revenue model
This is where things get pretty complicated, risk 3rd party error (these are errors that are accidentally added by team members mis-keying into the sheets) and can become difficult to update. This totally relies on not only being a spreadsheet jedi, but knowing exactly how the formula and model works, as well as the core assumptions that you'd make.
This option provides the most insight but is incredibly time consuming to produce and carry all of the same limitations across the model that persist in the main waterfall.
We have a revenue waterfall capacity template that you can use for this purpose - Grab it here
We're not going to walk you through a step by step of an entire model, however we'll show you the basics of letting you link them together which should be more than enough for anyone capable of doing the rest to figure out (and if not it's probably time to move away from the sheets).
Let's walk through how to link them together:
Step 1: Create a top level input / reporting sheet
This sheet essentially "controls" your duplicate models, meaning that you don't have to jump into each spreadsheet to update them individually. You effectively create link between each spreadsheet, so that data can flow and be updated from one place.
A good place to start is by taking a copy of the data input sheet from your main model, and altering it so that you can input multiple models at once, like the below (but obviously way more detailed)
Step 2: Build your core model and link it to the input sheet
This step is all about building a capacity model like the one we covered, however you'll want to build in lots come to cater with different marketing channels, different market sectors, segments and territories, as well as costs and the ability to change the sales cycle duration (our walkthrough didn't cover this, however we've built it into our template)
The data inputs for this sheet should come from a tab that contains a direct data import.
To do this, use the IMPORTRANGE formula in Google Sheets or Excel Online, which can draw a table of data from another sheet. This table of data should be the Plan Assumptions Table from that sheet.
You'll need to authorise the connection, and ensure that everyone using the main sheet has access to each of the individual model sheets also.
If successfully connected, you should get the table of data through (sans styling & formatting), with your inputs. You now need to link your model to this table, for the relevant scenario (the first will be baseline).
Step 3: Duplicate lots of times
When you're happy that the data is pulling through and linked, you now need to duplicate this model lots of times. For each one, you need to link the data to the correct scenario assumptions in the table/
Warning!
Any errors that you make in the model will be duplicated across every sheet. Should you ever need to make changes you will have to do so across every single scenario.
Step 4: Link the results of each model back into the main sheet
Now you do the same IMPORTRANGE function to draw through the results of each of your scenarios, into your main sheet.
If working correctly, you should be able to change a value in the plan assumptions, and after a short wait the returned data should be updated.
From here you can use this approach to pipe as much data as you want between the sheets, with the results in a central place.
Spreadsheet Model Limitations
So you've made it through all of the above, did it take 1 coffee or 2?
We've covered an awful lot, challenged a tonne of the "traditional" calculations that are used to calculate sales capacity, quota and attainment, and even given you some golden spreadsheet tips on how to interlink spreadsheets so that you can scenario test a waterfall model. So you you're all set, right?
Wrong.
There's some severe limitations to not only the above, but even some of the more advanced models that are available further down in our templates (we link a bunch at the bottom). These limitations affect your ability to model, and also your ability to treat planning as an ongoing exercise that can keep your business on track towards target.
Let's break it down:
Reporting Granularity
Here's the glaring omission that's been deliberately in place throughout the examples, to keep things simple:
Markets, sectors & territories
We've kept all the examples to a single market, with a single sales conversion rate, a single average deal size a single team etc.
So what if you have multiple markets, like SMB, Mid Market and Enterprise? Your waterfall model is now tripled.
And further segmented across US, EMEA and APAC?
That's going to be a total of 9 waterfall models to hook up, and that's just for your baseline plan.
Time
Not the time that it takes you to build it, but how time is dealt with in all of these models. In our Capacity Planning Template, we actually deal with the deferrals & offsets that come with sales cycles so that sales cycle length can be changed as a variable, however it's imperfect:
In the spreadsheet:
If the sales cycle is 60 days, it moves revenue back 2 months
If the sales cycle is 90 days, it moves revenue back 3 months
If the sales cycle is 120 days, it moves revenue back 4 months
etc.
It does this by moving data around between one column, or the next, depending on the size of the offset. Functionally, it woks by rounding up the sales cycle length in days to the nearest 30 so that it knows it in terms of months, and then uses a HLOOKUP to decide which month leads and revenue should live in, and it might be found in other online templates.
The reality is that it is wrong.
With models working like this, moving a sales cycle 29 days can have 0 effect, but moving it 1 day can also shift revenue an entire month. Without moving to modelling on a daily basis (RIP spreadsheet) or changing the way that you model, it's an inherent limitation to the spreadsheet.
An exponential amount of scenarios
The scenario testing covered changes to core KPIs, but what about different hiring approaches, or different hiring plans?
The linked scenarios that we've taught you grows exponentially:
It starts with a baseline, and 3 other scenarios - 4 scenarios total
You want to have two different hiring plans for each scenario - 8 scenarios total
You want to have 3 different marketing plans for each scenario - 24 scenarios total
But wait, there's more:
You have an SMB, Mid Market & Enterprise, across US, EMEA & APAC?
That's gonna be 72 markets scenarioed out in total.
Then 3 months into the plan, the economic outlook changes, you have to update everything
Forever Stuck with Revops or Finance
What happens if we do X?
This is a question that then gets bounced to RevOps (or finance) who have to then go and create a new scenario, link it up (if using option 3), and then eventually report back.
Exec can't change scenarios as they either don't know the baseline, have access to the correct sheets or know how to interpret the formatting of the sheet. This means that everything has to come back to the author.
This slows down planning, eats time and misses the opportunity to make decisions when the irons are hot.
Lots and lots of error
It's not even deliberate like with making false assumptions, this can be completely by accident, deliberate but well intentioned or everything in-between.
People accidently overwriting formulas with hard numbers
People putting the wrong numbers in the wrong places
People getting scenarios mixed up
People misunderstanding how the sheet is supposed to work
People not knowing the process of updating
People making their own copies & uncontrolled versions
etc.
People don't work like spreadsheets
This is the biggest one, and we've tried to address it with some of the modelling that we've introduced you to in the guide, however the dynamics of a sales team simply cannot be represented in a spreadsheet.
Whether it be how someone is promoted from a junior role into a senior role, moved from one market into another or is impacted by a growing sales team around them, there are all kinds of dynamics and effects that cannot come into play when you're trying to model out a spreadsheet, otherwise you'll quite literally be drowning in tabs (and be waiting for G-Sheets to calculate with every change of a cell - Ask me how I know!)
Some businesses have dedicated relationships between SDR's and AE's, others will have rulesets around round-robin distributions, and none of the effects of this can ever be modelled in a spreadsheet without getting super manual, which then limits the ability to scenario test (cutting your chances of success by 75%).
Dedicated Sales Capacity Software
So after all of that, you might no longer fancy spending the next few weeks (or for some months) building out a sales capacity model, just for another department to question how you came up with the numbers or built it. It's time to rely on a tried and tested software platform to do all of the above (and much more!)
This is where Clevenue's Revenue Planning Platform comes in, removing the need to build models, maintain spreadsheets or go through the hell of trying to create scenarios - It does it all for you.
Dynamic bottom up modelling with no need to build models, or spreadsheets
Connect you data into Clevenue and see a view of future revenue like you've never seen before, with revenue modelled down to the individual level, using both active models and rep performance data to forecast well into the future.
Get Started with Clevenue Today
Contact us to get started, see the platform or find out more about how Clevenue can change the way you plan. Get in touch and we'll get back to you today.
Helpful Spreadsheet Templates
We've already mentioned these, however it would be rude not to hand them over - You can find all of our tools over at our resources, or you can learn more about the basics of revenue in our Revenue Academy.
