Managing the B2B SaaS Sales Machine — Part 3: Excel Model

Welcome to the third and final part of our little series on sales controlling and monitoring! In this part I will share the underlying Excel model and explain how it works.

But first as a recap:

Part 1

In the first part I covered the first 3 reports on ARR, Deals Won and Quota Performance. You can find it here: https://medium.com/senovovc/managing-the-b2b-saas-sales-machine-part-1-arr-and-pipeline-a7dfb885ce04?source=friends_link&sk=ecf28cb6672805054ce2033303e8caa6

Part 2

In the second part we had a detailed look at funnel. You can find it here: (https://medium.com/senovovc/managing-the-b2b-saas-sales-machine-part-2-diving-into-the-funnel-1e295f4c6f18?source=friends_link&sk=bed64d1e5883d8210f6e726d362fda5c)

If you haven’t had a look at these posts first, I recommend checking them out first.

Let’s get to it!

The Excel Model

You can find the excel here: https://docs.google.com/uc?id=1PFw8ZRRM4UyqPOkUGV-s8CQN8uUfTNMd&export=download. This excel is quite large and complex (more on that at the very end). Please make sure that you have activated macros. We need to map deals from one (earlier) CRM export date to another, more current state of the CRM. This is very slow within excel, so we wrote a short macro for it which sorts and maps the data.

I would also recommend to de-activate automatic calculations of formulas, so you don’t get any delays while mapping and editing the data. You can do this by selecting Formulas tab -> Calculations -> Manual. But take care: as long as the setting is activated you need to manually press “Calculate Now” on the same tab.

How it works

The model is organized in 6 areas which are individual tabs:

1) The dashboard: this contains the Dashboard I covered in the previous posts

2) AE Model: a model detailing your current AE headcount, hires, ramp ups and quotas

3) Support: supporting data such as dates, deal types, lost deal categorization, AE names

4) Target Data: the data structure required for the dashboard to work

5) CRM Export: your CRM data goes here

6) Manual: explanations of the excel

I’ll start bottom up with the CRM Export to make it easy to understand.

CRM Export & Target Data Tabs

As a first step you need to export snapshot data from your CRM to the CRM export tab. We will need at least 2 snapshots from different dates so we can compare how the deals moved.

The information does not need to be in the same format / order as shown on the Target Data tab. The purpose of the export tab is that you can export your data into this tab and then map the fields to the Target Data tab.

You will need to map the following information:

· Date Stamp: Date of the export so we can compare how a deal moved.
· Deal ID: Unique ID for the CRM entry (needs to be constant over the exports).
· Close Date: is assumed to be closed. This is a forecast.
· Deal Type: Category of the deal so we can differentiate between certain types such as new business, upsells or renewals.
· Date: Closed Lost: Date when the deal was actually closed and marked as lost (not a forecast).
· Create Date: Date when the deal was created.
· Closed Lost Reason: Category why the deal was lost. Needs to map to the categories configured in the Support Tab.
· Deal owner (AE): Name of the deal owner. Needs to map to the names in the Support tab and on the AEs tab.
· Last activity date: Last time there was an interaction on this deal. This is used to identify “stale” leads
· Deal Stage: Pipeline stage of the deal. Needs to map to what is configured on the Support tab.
· License Start: Start date of the license once you have closed the deal.
· Date: Closed Won: Date when the deal was actually won (not a forecast).
· Deal Name: Name of the customer / deal.
· Amount (ARR): ARR of deal, often an estimate before you are in the final stage of the funnel

The best way of doing the mapping is to create references from the “Target Data” tab (market yellow) the respective columns in the “Export” tab.

The green market columns O — U are automatically calculated and contain unique identifiers and changes from one data export to the next data export — ie the “movement” of the deals.

Support Tab

Here you can configure the following information:
· Column A: stages of your sales funnel
· Column B: mapping of probabilities / weights to the funnel stages
· Column C: dates of export snapshots on the export tab
· Column D: the categories of the deals from which data will be pulled for analysis. If a deal type (eg a “renewal”) is not included then it won’t count as pipeline, attained quota etc
· Column E: months in a year, unlikely that this will change 😉
· Column F: configurations of years, quarters and annual totals
· Column G: configuration years, usually the current year and current year +1
· Column H: categories of deals lost
· Column I: Names of account executives

AEs Tab

On this tab you can find in the first 5 Rows a mapping of months to “names” of a quarter (eg Q1 2023) and to the date ranges of a quarter. As always, hard coded values are in yellow and can be changed to fit the proper time frame.

In row 6 you find the seasonality of the sales process. We often see a slow down in summer for example and a very busy Q4. If this is the case in your business you could plan for “productivity factor” of 0.8 or 0.7 in July / August and 1.2 or 1.3 in November, December and possibly January.

Row 8 has the target quota for an AE and below this you will find the quota for the individual account executives including adjustments for saisonality and ramping times as defined in row 21. If an employee relationship ends, you can simply hard code that to 0 as you can see that in row 10.

As a ramp time we often see it taking up to 6 months in an enterprise sales team. So in row 21 you can set the attained quota for each month of the first 6 months. In the screenshot you can see a linear 10% ramp in the first 3 months and then an acceleration in the later 3 months.

If your experience would be a linear 3 months process, then the settings would be 25%, 50%, 75%, 100%. Of it were 0 productivity for the first 3 months and then the same gradual ramp, then the setting would be 0%, 0%, 0%, 25%, 50%, 75%

Sales Dashboard Tab

The various charts and tables now pull the data from the Target Data Tab and filter it according to the Date configured in Cell F4 when you hit the “Perform Calculations” button:

You can also find a few helper tables to the bottom and the right of the main dashboard areas as well as in the dashboard tables when needed. To separate this information from the intended output we have formatted it in light grey to show you what is calculated. Feel free to set this text to white if you want to make the dashboard a bit more visually appealing.

I hope you enjoyed this post and that you found it helpful. We made a point of implementing everything within Excel so that it can serve as a template and case study. Since most of you who find this content to be relevant will already have somewhat of a mid-sized team at least, I think it would make sense to move some the logic which slows down the Excel into a proper programming language / environment. This relates mainly to the automatically calculated fields in the Target Data Tab and I think Python or so and a small helper database could make it work like a breeze.
If you want to do this, you can also have a look at the macro we coded to match the deals from the CRM exports. Visual Basic is quite rudimentary and so we had to write a quick sort style algorithm, which is most of the macro logic.

Last but not least: comments always welcome! You can reach me at best markus@senovo.vc or on Twitter at @markusgrundmann