What-if analysis in sales planning helps businesses explore scenarios and their potential outcomes. By assessing market changes or pricing adjustments, it enables data-driven decisions to improve your sales strategy. Below, you will learn how to implement what-if analysis effectively.
What Is a What-if Analysis?
What-if analysis is one of the most common practices in FP&A – financial planning and analytics. Used by decision-makers across all industries, it is often mystified and overcomplicated. But in fact, there is nothing too complicated about the concept.
What-if analysis is a risk assessment method in which you change inputs and investigate how the change affects the output.
Pretty simple, right?
Why Use What-if Analyses in Sales Planning?
You would probably check out a flight simulation game before piloting your first ACTUAL airplane. Because you want to test out how it would look without the actual dangers of crashing. However, just like there are differences between a flight simulator and flying a plane in real life, there are differences between making hypotheses and testing them in real life.
That is what what-if analysis in a business context roughly is: you do a thought experiment and try to analyze the results of the business initiative before actually flying up with unsuspecting passengers (other stakeholders in this case). You try to find out WHAT would happen IF…
What-if Analysis and Financial Modeling
Let us try and distinguish the two similar, most commonly used concepts: sensitivity analysis and scenarios. Different sources sometimes use the terms “what-if scenario”; “what-if analysis”; “scenario analysis” and “sensitivity analysis” interchangeably. They are close but not synonyms.
Sensitivity Analysis
This one is pretty simple: sensitivity analysis is changing the one (independent) variable and investigating how this affects the other (dependent) variable in the model for the same period. It is often also called a what-if analysis or simulation analysis.
Scenarios
Scenarios are a bit more complex to execute but not too hard to explain. In scenario analysis, you change one or more variables and investigate how the changes affect multiple dependent variables in the model.
What-if Analysis in Sales Planning in Excel - Definitions
As far as what-if analyses go, Excel has several built-in tools that can be extremely useful if used correctly. These tools give users a way to change the input values without changing the data itself. They are Scenarios, Data tables, Goal seek.
Scenarios
The scenario tool in Excel is used to store and show several versions of data in the same cells. Up to 32 to be more specific. Planners use base, best, and worst-case scenarios most commonly, though. According to Microsoft, a Scenario is a set of values that Excel saves and can substitute automatically in cells on a worksheet.
Data Tables
The data tables tool is used when you have a formula with one or two variables and want to see all possible outcomes for different combinations of each variable. You can use one or two variables to create a data table what-if analysis.
It is possible to create a similar table using the time variable, but also using both time and the initial amount:
Goal Seek
Goal Seek is used if you know the result you would like to get from a formula, but you want to calculate its inputs. In other words, you backward engineer the formula’s inputs.
Example: A demand planner wants to generate $150 000 by selling four products in the portfolio. The Goal Seek tool can help them determine how much Product 4 they need to sell to get to the wanted revenue numbers if they already know the amounts for Products 1, 2, and 3.
What-if Analysis in Excel - How to?
To see how planners use what-if analysis in sales planning, we created a simplified financial model of a hypothetical pharmaceutical company. Download it below for details and instructions.
In our hypothetical company, demand planners are making a 2022 sales strategy based on 2021 sales data. Management has directed them to increase the gross margin by 2%. Given the company’s operations, the planners can only adjust product prices and promo discounts.
Analyzing the Impact of Price and Discount Changes
They aim to explore:
- How a 4% price increase for some products impacts the gross margin.
- How a 3% reduction in discounts for some clients affects the gross margin.
- How to combine these adjustments for maximum gross margin.
The sales report is central to the model, with all what-if analyses built around it:
You can see the sales numbers for 2021 (the previous year). This report is built using slicers and pivot tables, so the planners can drill down and investigate the numbers for each client, brand, and product category in detail.
The magic happens in the What-if analysis tab. Demand planners can use Excel’s scenario manager tool to play with different combinations of price increases and discount decreases to get to the desired gross margin increase.
This is the final result of our what-if analysis process. Here is how we got to it:
Combining Data for Key Model Calculations
First, we created a tab where we combined the client, product data, and transactions data and made some calculations to get a detailed 2021 sales report – Model Calculations tab. All crucial calculations for the entire model are done here.
For practical reasons, we used pivot tables and slicers to make a more user-friendly sales report with all the calculations needed to make a what-if analysis. It is easier to navigate the data and drill down into specific clients, products, and product segments.
To designate the products and clients whose prices we are going to tweak in the what-if analysis, we created two additional tabs for SKUs and clients, respectively. Download the spreadsheet to learn more about these two tabs.
Setting Up Scenarios with Excel’s Scenario Manager
We know that our scenarios are 4% product price increase, 3% client discount decrease, and the combination of the two. So we go to Data>What-if analysis>Scenario manager to define the scenarios:
Next, we define the price increase scenario. The changing cell is the cell we need to use to define the price increase percentage:
After this, we need to set the value of 4% for our first scenario, like this:
And we do the same for the second scenario:
In the third scenario we change 2 variables simultaneously:
Here is the final result with all 3 scenarios defined:
Now we can select each scenario and show them in the cells.
Comparing Scenario Results with Actuals
We can compare the 2021 actuals and 2022 scenarios in the sales report table.
Our hypothetical demand planners can use the what-if dashboard to tweak the variables as they please, depending on the business goals and conditions. The downside of this approach is that the scenario summary reports aren’t interactive and need to be generated manually for every new scenario.
Problems With Excel What-if Analysis in Sales Planning
Although Excel is a powerful tool, there are many disadvantages in using it as only financial planning and what-if analysis tool, compared to dedicated financial planning tools. Excel can be a lot of work – users need to edit too many variables manually to get results. The model we built is a simplified example of what companies use. Even simplified, it is not easy to compare different versions of data using it – we would “drown” in pivot tables trying to make best and worst-case versions of plan values in the model. The work would double if we tried to make changes in one of the versions on the go, as planners often need to do. Every change in the model requires multiple re-calculations and pivot tables and data slicer refreshes.
Our modeler spent three days building the model and scenario analyses. In his own words, it was sometimes “nerve-racking” trying to connect everything and make it presentable and intuitive to use. We did our best to make the reports and tables as interactive as possible. However, the technical capabilities of the Excel surroundings made it difficult.
What-if Analysis in Sales Planning - in Farseer
There’s a more efficient way to perform what-if analysis in sales planning. We designed Farseer for this exact purpose. Unlike Excel, where setting up a full financial model with interactive dashboards, slicers, and what-if analyses can take days, Farseer completes the process in under three hours.
Faster work with automation
Here is the first scenario, 4% product price increase:
The second scenario, lowering the client promo discount:
The third scenario, combining the 2 variables:
As you can see, as soon as we change the what-if variables, the sales report table and charts automatically refresh. Also, by using slicers, it is easy to drill down to any client and SKU, and see how the scenario affected them:
Every element on the Dashboard is interactive and easily customizable. Users can add new columns to the Sales report table without having to connect them to the model. Unlike in Excel, Farseer charts refresh because they sync with the Farseer database automatically, and the engine does the work quickly in the background.
Conclusion
What-if analysis in sales planning lets you play around with different scenarios—like tweaking prices or adjusting targets—to see how they might impact your sales. It’s a simple way to spot potential risks and opportunities, so you can make smarter decisions, without the guesswork.
Once you’ve mastered sales planning, then discover how to take control of your stock levels and improve efficiency with data-driven inventory replenishment!
