Businessman using iPad

Spreadsheets for Revenue Analytics: A Love & Hate Relationship

Spreadsheets are the duct tape of the data world. Love them or hate them, they have been a dependable tool for more than 40 years, built on a fundamental concept that hasn’t really changed. Spreadsheets are a blank canvas and a mathematical workhorse. For how simple they are, they’re versatile and surprisingly robust, seemingly capable of patching together any number of datasets and creating a fully functioning analytics system.

That also happens to be a weakness.

For us in revenue analytics, we use spreadsheets to do everything: record historical revenue numbers, follow critical business metrics, and even predict the outcomes of various strategic choices. Spreadsheets are so versatile that every business, whether startup or enterprise, will use them on a regular basis. The problem is that some businesses, most businesses in fact, rely on spreadsheets almost exclusively in their data reporting toolkit, and they do this to their detriment.

The fast, but brittle solution

Let’s pose some questions:

  1. Does your revenue organization take weekly or even daily snapshots of your CRM so you can compare current numbers to historical ones? Is it somebody’s dedicated job to maintain these snapshots?
  2. Do you have an explosion of reports, such as bookings numbers by region, by sales rep, by industry—even if the underlying bookings data is the same?
  3. Do you ignore numbers that seem like anomalies, outliers, or might be actual errors because it would take too long to triage and investigate?

If this sounds like your business, you’re not alone. Spreadsheets are powerful tools, but they are not always the perfect tool for the job.

Take the need to capture historical data, for example. A big flaw of many CRM tools is they typically provide very little ability to analyze current numbers against historical performance. How bookings, MQLs, or opportunity pipeline are trending this month or quarter isn’t very useful if you can’t compare them against last month, last quarter, or even last year.

Spreadsheets can be a fast band-aid solution to this flaw. Take a snapshot of the CRM, combine it with the previous week’s snapshot, and now you have a historical record to use for further analysis. Essentially, you’ve created a de facto database without any of the upfront investment. 

The problem is that although spreadsheets can act as databases, they are very flimsy ones. Manually aligning 52 spreadsheet tabs for 52 weeks of data for one year is troublesome, though not impossible. But try to maintain that process for two or three years, and suddenly you end up with a critical but brittle operational system whose update and servicing is a major concern of your analyst staff. 

I’ve been surprised by how many tech-savvy SaaS companies rely on their spreadsheets as the only source of historical CRM data for analysis, and continue to live with labor-intense updates and the inherent brittleness of such a patchwork solution even as they scale. The problem with building your data foundation with duct tape is that once your solution bursts at the seams, repairing it is a herculean effort.

Customer Case Study:

I recently engaged with a successful SaaS unicorn that utilized a spreadsheet for all their inside sales reporting. Much love and care was invested into this spreadsheet, and the organization developed a set of smart processes and workarounds to minimize the effort required to update and maintain it. Unfortunately, as the file has grown to be 500+ MB in size, they are now seeing timeouts and errors from their cloud-based spreadsheet, leaving the organization scrambling to replace this critical operational reporting system.

More spreadsheets, more problems

Spreadsheets have similar pros and cons when we use them as reporting and analysis tools. They’re fast to spin up but cumbersome to scale. They’re rigid and static, and over time, spreadsheet reports need to be updated and increasingly customized as different people ask different questions—or even permutations of the same question—creating an enormous, unscalable, and ineffective analytics system.

If the data is incorrect, it can take weeks to triage the source of the error. If a surprising phenomenon (e.g. conversion rate is lower than historical norms) is reported, net new spreadsheets are needed to diagnose the issue. If metrics definitions need to be altered (because of a change in forecasting process, or new definitions of opportunity stages, etc.) all impacted reports become dysfunctional. If a report is updated, it needs to be redistributed, creating multiple sources of “truth.”

In larger organizations, these problems become particularly acute when revenue operators have to combine data from multiple sources, while also creating hundreds of reports for different stakeholders. With different revenue leaders asking different questions, it doesn’t take long to have 500 reports—often 500 cuts of the same data floating around week after week. This simply doesn’t work for very long.

Are spreadsheets ever the right tool?

So far we’ve mostly discussed using spreadsheets as tools for recording historical data and for reports and analytics. In the early stages of a company, these are great use cases for spreadsheets. They’re not perfect, but for startups where velocity is paramount and change is rapid, they’ll get the job done until the company scales to a point where GTM motions start to stabilize. The key is being able to recognize the need to transition away from spreadsheets before they become a significant operational challenge.

What we have not discussed is that data is useful for many other things besides historical records and analytics. We also use data to reason through different scenarios, and build and test numeric models based on human expertise.

This is exactly where spreadsheets are still the perfect tool for the job.

Spreadsheets—with their blank cells, formulas, and even the ability to add text—make it easy for revenue leaders to pair historical data with their own human judgement. Take budget planning,  which executives often do in spreadsheets to show how different levels of spending could impact metrics across the revenue funnel. Importantly, this makes all the what-if scenarios appear concrete, allowing leaders to argue for, or against real numbers instead of theoretical predictions and “expert” knowledge. 

Spreadsheets may not allow for full analytical simulations, showing the entire distribution of outcomes given certain assumptions, but the reality is that such simulations are often excessive and unnecessary. For most use cases, the spreadsheet is more than good enough.

This is why even the largest enterprises, with an entire analytics stack in hand, will still use spreadsheets for many non-operational situations. They simply use them for what they do best: the quick one-off problem, and also in strategic planning.

Planning Process Example:

Spreadsheets’ combination of a blank canvas and human judgement is perfectly reasonable for annual planning. Take your revenue targets and inject your assumptions about the outcome of various efforts (e.g. a promising new account-based marketing approach, a streamlined rep onboarding system) and work backwards to determine desired pipeline coverage, number of AEs needed, quotas for those AEs, number of SQLs and MQLs Marketing needs to generate, etc.

You can definitely systematize this type of planning, but in most organizations, this will be done at most once a quarter, and human judgement on new initiatives will likely affect the results as much as historical data would. In this case, the flexibility of a spreadsheet likely outweighs its shortcomings, particularly for most mid-sized organizations.

So if not spreadsheets, then what?

In our next post, we’ll talk about the fork in the road. How do you know exactly when to begin moving away from spreadsheets for revenue analytics? What are your choices? Investing in technology and tools to move beyond spreadsheets is itself a problem full of questions.

We will discuss each of the alternatives, their strengths and weaknesses, and how to select which path is right for you.