No, it’s not the hottest new nightclub on the strip – it’s a method for forecasting how much pipeline is needed to hit your quarterly plan and how much pipeline you are likely to create (assuming a certain set of marketing activities).
In this example, we’ll walk through a relatively simple model that a hypothetical company might use to calculate pipeline from both a top down and bottom up view. For the sake of simplicity, we are assuming the sales cycle for this company is about 90 days and we are planning marketing activities now for deals that will close next quarter. Stick around till the end and I’ll show you how you can kick this up a notch. Ready? Make a copy of this spreadsheet and let’s go!
Top Down
Let’s start with our top-down approach. The first number you will need is the Sales Target for a future quarter. You should be able to get this from your head of finance – this is the board approved plan number for closed-won sales.
The second number is Pipeline Needed – this is the amount of sales pipeline you need to generate in order to hit your sales target. In this spreadsheet the number is calculated based on Coverage. You can calculate this number by taking the dollar amount of opportunities created and dividing it by the dollar amount of opportunities won. While every sales organization is different, you typically see a requirement of 3x to 4x coverage; meaning, for every dollar of closed-won business, you need to generate $3 to $4 of pipeline for that quarter. In this example, the sales organization requires 4x coverage for inbound opportunities and 4.5x coverage for outbound opportunities.
I’m splitting inbound and outbound into two distinct categories because you might see large differences in funnel performance based on where the opportunity was created. For example, if someone was actively searching for a solution to an existing problem, they may be more likely to purchase a solution in the near term than someone who responded to a cold outbound email or phone call. Your organization may differ based on the degree to which your go-to-market motion relies on inbound vs. outbound. Adjust the % Sourced by Inbound number based on the degree to which you believe inbound or outbound will be responsible for creating pipeline.
Next, subtract from Pipeline Needed the current amount of pipeline that is set to close within that quarter. This essentially gives you credit for the pipeline you’ve already created. This leaves you with Remaining Pipeline Needed – the amount of pipeline you need to create this quarter.
The next couple sections require you to update the Inbound and Outbound areas at the bottom of the sheet. In these examples, I’m using a currency of “meetings”, but this could as easily be sales qualified leads (SQLs) or any other metric that precedes the creation of an opportunity. I’m using % Meeting to Opportunity to represent what percentage of these turn into opportunities. In my example, 40% of inbound meetings turn into opportunities, whereas 70% of outbound converts to an opportunity (in my hypothetical company, they find it hard to set that first meeting with outbound, but the ones that do tend to be better qualified). You will also need to know the Average ACV (annual contract value) of inbound and outbound opportunities.
Once you have all of this, the spreadsheet calculates how many opportunities and meetings are needed to hit the goal. This company needs 40 opportunities to be generated from 100 inbound sourced meetings and 9 opportunities to be generated from 13 outbound sourced meetings. They should be tracking 9 combined meetings per week from inbound and outbound sources.
Bottom Up
Let’s now take a look at this from another direction. On the next tab, I’ve listed out the number of meetings that this hypothetical company is getting on a per-channel basis for several quarters. I’m continuing to use meetings as the primary currency of what marketing produces, but this could easily be sales qualified leads or opportunities (just be consistent with the first tab)
For each channel, we see historical performance broken down by quarter. This is a very rough way of getting in the ballpark of what we might expect in future periods based on past performance. I’ve also added a Quarterly Change column to provide an at-a-glance look at how performance has shifted over time to help you calculate this.
Depending on your marketing activities and seasonality, you should see some channels behave somewhat predictably while others tend to be very lumpy. Projected Improvement is a way of taking the most recent performance and applying your judgment to how those channels might change over time. For example, in this hypothetical company, they might have had several big events in Q1 that won’t be repeated in Q3 or Q4, so Projected Improvement here is lower than the historical number. If you have more specific numbers, it’s OK to overwrite your projections directly in the cell instead of relying on a formula. It’s very helpful to create notes around why you expect performance to change (for the better or worse) if you are sharing this spreadsheet with finance and other members of the leadership team – especially if you expect performance to change dramatically over past performance.
The blue columns in the sheet use Projected Improvement to estimate what the aggregate pipeline might look like at the end of the quarter. Compare these numbers with the Top Down tab. Do you have immediate problems with pipeline that need to be called out? Is marketing being aggressive enough to hit plan? In this particular case, we seem to have an adequate inbound pipeline, but we need to make up $85,000 worth of outbound pipeline somewhere.
Higher Resolution
This spreadsheet provides a quick way of determining whether or not the status quo will leave you with large gaps in pipeline. This should be paired with a more detailed planning process that includes all of the additional activities that comprise each channel’s performance. This is where you can also include assumptions about budget and the relative performance of each marketing activity. This analysis is not a replacement for such planning, but is meant to provide an overview of the metrics needed to hit targets and whether or not, from a bottoms up perspective, you are likely to hit those targets if you were to continue doing the things you are already doing.
Make It Fancy
If you are the type that automates things, you can easily connect a copy of this spreadsheet with Salesforce via the Salesforce Connector extension. This extension will allow you to pull meetings, opportunities, and closed won into additional tabs so that you can reference actual performance numbers without leaving this spreadsheet. You can also add a date field at the top and use that in your calculations so that adjusting this spreadsheet for future quarters is as easy as updating the dates and refreshing your reports.
