What-if Analysis in Sales Planning [How-to + Spreadsheet Template Download]
Updated: Nov 29, 2022
1. 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?
2. Why use what-if analyses?
You would probably check out a flight simulation game before piloting your first ACTUAL airplane. To test out how it would look without the actual dangers of crashing. But 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…
3. What-if analysis and financial modeling
Before we go any further, let us try and distinguish the two similar, most commonly used concepts in this context: sensitivity analysis and scenarios. Researching this article, we found different sources sometimes use the terms “what-if scenario”; “what-if analysis”; “scenario analysis” and “sensitivity analysis” interchangeably. They are close but not synonyms.
3.1. 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.
Example: Sales director wants to tweak the price of a product in their portfolio and see how this would affect the overall revenue numbers.
3.2 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.
Example: Sales director wants to find out how switching to new raw materials in production affects the revenues coming in from multiple products in the company’s portfolio.
4. What-if analysis 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.
4.1 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.
4.2. 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.
Example: You plan to put $2000 in a savings account with a 5% compound monthly interest rate, and you want to make a calculator that will help you determine what happens with your savings after some time. The variables you can change are the initial amount you put in the account and the number of years you will save. With Data tables, you can generate a table that tells you your total savings balance if you change the initial amount you will put in your account.
It is possible to create a similar table using the time variable, but also using both time and the initial amount:
4.3. 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.
The issue with Goal Seek is that it only supports one input and one output cell, so it is somewhat limiting in solving more complex problems.
5. What-if analysis in Excel - How to?
The best way to wrap your head around these tools is to see how planners use them. To do this, we decided to create a somewhat simplified financial model of a hypothetical pharmaceutical production company. Planning problems of some of our clients that produce and distribute their products inspired us.
We will not go into too much detail on how we built the model, and we will try to focus only on the what-if analysis part. You can download the model on the link below, and find more information about it with detailed instructions on using it in the file.