5 Simple Reasons Spreadsheets Fail At What-if Analysis

In the world of business, “what if…?” is not an unusual statement.

Maybe you have a goal, but aren’t sure of the best path to get there. Or, as we’ve seen more recently, your environment is changing and you need to consider what the future will look like.

You’re not a stranger to what-if analysis, also known as scenario planning, but you’re wondering if there’s a better way. Spreadsheets are wonderful for ad-hoc, scrappy analysis and even for reporting but it’s impractical to manage complex data and scenarios with them.

In this article, you’ll learn exactly why spreadsheets fail fast when it comes to scalable what-if analysis, and what you can do instead. 

‍

What-if analysis is non-intuitive with spreadsheets

You rely on intuition for much of your day-to-day routine. Why should your scenario planning be any different?

Using the scenario manager, goal seek feature, or other what-if analysis tools within your spreadsheet software can work if your data set is very limited and your problem statement is straightforward.

But what happens when you need to make decisions that have a huge impact on multiple stakeholders? Say your VP of Sales asks you to calculate the impact of a hiring freeze on your revenue projections.

You need dedicated software to answer serious questions

A business planning platform with native scenario planning enables what-if scenario problem solving in three ways:

1. Consolidate data from multiple data sources, including your CRM, ATS, HRIS, data warehouse, and *ahem* spreadsheets
2. You don’t have to plan your entire data structure and model around scenarios - use Scenarios without disrupting or modifying your original data/models
3. An intuitive, modern, and friendly user interface makes it easier and faster to get answers

‍

Cell references are a massive headache

This is a very specific limitation of spreadsheets that can make complex analyses beyond basic formulas and the occasional pivot table a headache. Cell references have many disadvantages:

• Easy to lose data by overwriting
• Annoying syntax that leads to mistakes even with advanced users
• Difficult to replicate formulas
• Not aligned with the way humans think

With what-if analysis, the disadvantages of cell reference based models are painfully obvious. You have to structure your data so it’s easy to select the correct range of cells. Then, you have to create a scenario and enter new values for each cell. Oh, and then, you have to do it again if you have more than one scenario to compare.

‍

Count the number of hoops you have to jump through before getting the answers you need.

When you create scenarios with metrics instead of cells, there’s no requirement to structure and maintain data in a specific location. As a result, you get much more flexibility. And your scenarios change as you evolve your model and update your data - not just because you ctrl+c and ctrl+v in the wrong cell.

‍

Visualization is an extra step

A picture is worth a thousand words and time is money. All the cliches in the world can’t help the fact that data visualization is severely lacking in spreadsheets.

Spreadsheets will let you do a quick and dirty what-if on perhaps two rows of data, and show you the output in a table. But how effective is a table in communicating the urgency of each alternative scenario?

For the most effective what-if analysis that answers questions at a glance, you need two levels of visualization:

• Visualize your baseline, best case, and worst case scenarios side-by-side and
• View how your scenarios are structured in relation to one another

‍

‍

Scenario planning is often an add-on feature or an afterthought

More often than not, scenario planning is an afterthought or completely missing from your spreadsheet solution of choice. And that’s with good reason: the cell-based architecture isn’t adept at handling dynamic data and changing variables.

‍

‍

The ratings are… not great. It also costs you time to discover the third-party app, read reviews, download, and hope for the best. Whereas, native scenario planning means it’s built right within the platform to analyze any model you create.

In fact, we cover a step-by-step process to build your own scenarios with an example. Spoiler: it’s only four steps and most of them involve aligning your priorities rather than manipulating your tool to do its job.

‍

You need a powerful solution that scales at your level

It’s hard enough maintaining huge datasets across multiple spreadsheets. Given how models tend to break at the slightest modification, you end up spending more time on maintenance over execution.

That may have worked on a much smaller scale and during relatively calmer times, but big data and bigger inflation has created a complex environment for businesses everywhere. You need a solution that empowers you to ask tough questions and get immediate answers to drive life-changing decisions.

That’s the crux of what-if analysis in a world where it’s increasingly difficult to stand out from competition. Your ability to ask what-if on every aspect of the business will reveal the best path to move forward.

To achieve a high level of confidence in your decisions, you need a tool you can trust to scale with the size of your problems. After all, the most successful leaders turn problems into solutions.

Find a better way to solve problems and craft your own future with Scenarios on Pigment.