Four years ago I was reading a business plan and I saw something called a sensitivity analysis table that baffled me.
It was a beautiful table that showed the profitability of a music festival at different numbers of attendees and price points.
The data table showed dozens of profit possibilities in one table, with the different price and attendee numbers laid out across the horizontal and vertical axes.
I thought to myself, “that looks complicated, maybe one day after working in Excel for years I’ll be able to do that.”
However, the reality is that these tables are quite easy to set up. By the end of this blog post you’ll be able to easily perform what-if-analysis.
What is what-if-analysis in Microsoft Excel?
What-if-analyses are used to evaluate complex “what if?” scenarios in Microsoft Excel.
When we perform a what-if-analysis, we can better understand the variability (risk) of results for a business or data model.
These types of analyses help us examine questions like:
These questions are very complex, but with what-if-analysis we can create reports and answer these types of questions in seconds.
Basic structure & setup for what-if-analysis
We’re either changing inputs to solve for outputs, or changing outputs to solve for necessary inputs.
And to perform this type of analysis, we need to have a data model of some kind, say, a budget, with at least one “hard coded variable” (meaning non-formula, just a typed in number).
Two major types: goal seek & sensitivity analysis data tables
There are two main types of what-if-analysis methods:
Let’s start with goal seek.
1. Goal seek
Goal seek allows us to solve inputs to achieve whatever outputs we decide we want. To start, we’ll need a simple data model with an inputs and an outputs section. Let’s review the following financial model for our table company in order to perform our analysis.
In the Inputs Section, we have our hard coded assumptions. Unit costs: sale price, units sold, manufacturing costs, customer acquisition costs (CAC). Fixed costs: rent.
In the Outputs Section we have a financial model that is driven by the inputs. In the model:
Our gross profit is $90,000 and our left over profit (net) is $15,000 at a 10% profit margin.
Now let’s use goal seek to answer some questions
Question 1: How do we get to a 20% profit margin?
We can approach this question in a variety of ways. In order to achieve 20% profit, we could change price, units, manufacturing costs, customer acquisition costs, or rent.
Let’s solve for different variables using goal seek.
Solving for price: What price gets us to 20% profit?
First navigate to the data tab, and on the far right you’ll see what if analysis. Click what-if-analysis and then goal seek.
We have three boxes:
So you’ll see that we get 20% profit if we can just raise the price to $562. At this point, we can easily solve for this same 20% profit output by solving for other inputs, like: units sold, manufacturing costs, customer acquisition costs (CAC), or rent. Just follow the same steps.
Let’s try one more example with goal seek
Question 2: How can we get to $250,000 in gross profit?
In this scenario, the only variables that will change gross profit are price, units, or manufacturing costs (because gross profit = revenue - cost of goods sold).
Let’s solve for price using goal seek.
Solving for price: What price gets us to 250,000 in gross profit?
Navigate to goal seek again and fill in your three boxes.
We have three boxes:
So we would need to change the price to $1283 in order to earn $250,000 in gross profit.
Now we can easily solve for this same $250K in gross profit by solving for units sold. Just follow the same steps.
Limitations & Summary of Goal Seek
If we solve these inputs, and we actually knew this company, we’d immediately recognize what is realistic and what is not realistic. Maybe we just can’t drive marketing costs down, or it’s ridiculous for us to charge $1283 for our product.
But this analysis gives us an idea of what if would take and what the available options are for running the business.
However, the limitation of this type of analysis is that everything is held constant except your one input you are solving for.
It’s probably not reasonable to just raise the price to achieve the profit margin of 20%. But if you can raise prices a little, and lower manufacturing and marketing costs, maybe you can hit your target.
But Goal Seek won’t help you with this type of analysis.
For more complex questions with multiple moving pieces, we need to use the data table feature known as sensitivity analysis.
2. What-If-Analysis Data Table (Sensitivity Analysis)
Sensitivity analysis data tables show us what outputs we’ll achieve at combinations of multiple changing inputs.
So as we change the environment dynamically, how does the profitability of our company change?
Once we jump into our example, you’ll see that this concept is pretty easy.
Let’s use a data table to evaluate whether or not we should raise the price of our product
Let’s use the same table company financial model from our goal seek example.
Question 1: We’re thinking about charging more per table, but we know this may result in selling fewer units. We want a higher profit margin %. Our current profit percent is 10%. Should we do this?
So we need to build a table showing the profit margin % at different unit and price combinations.
And the key here is to make sure your assumptions are reasonable.
In our current financial model, we’re selling 300 units, at $500 per table. So we want to look at higher prices which will result in lower units sold.
So the maximum units should be 300, and we should look at lower numbers from there.
How to set up the sensitivity analysis data table
In order to populate the data table, you’ll need to set the table up like this, although you can format (colors, borders, fonts…etc.) however you want. I use borders and font colors to make it more clear.
The important elements are that you have:
You’ll need to fill in the assumptions with numbers that are reasonable, meaning they reflect a realistic possibility. You fill them in by just “hard coding” (typed in, no formulas) the assumptions you want to evaluate.
Now in the top left corner, link in the metrics that you want to sensitize, so in this case, it is profit %.
=Profit % Cell
Now inside the table, you have different assumption combinations.
The data that will populate the table will be the different profit percent for each combination.
Now let’s perform the calculation to analyze (sensitize) profit % in our data table
First highlight the whole table including the assumptions.
Now navigate to the Data tab, click What-If-Analysis and select Data Table.
We have two input boxes:
Now click OK.
Now quickly, I’m going to apply a percent format to the data table for newly populated profit percent data.
Here’s what it looks like.
Interpreting the results: can we raise the price of our tables to achieve a higher profit %?
Our original goal was that we wanted to raise our profit margin % by raising the price. Does this seem reasonable?
The results are interesting.
If we could raise the price from $500 to $600 per table, we could run a 17% profit margin at 200 units (a reduction of 100 units).
So with just a 20% price increase, we could withstand decreasing our unit sales 33% while still increasing our profit margins from 10% to 17%.
The conclusion? It looks like we could potentially find a solution in our data table that is reasonable in the real world.
I would recommend raising the price slightly as it seems the profit margin is not extremely sensitive to changes in unit sales.
Limitations & summary of sensitivity analysis data tables
The power of data tables comes in the ability to see dozens of possible outcomes at the same time. It helps to drive you strategically in a direction.
Very quickly, you will notice whether you should be pushing to raise the price knowing that unit sales will drop, or lower the price to increase unit sales. It can help you optimize your decisions.
And if we actually knew this company, we’d immediately recognize whether or not the results are realistic.
Maybe we are in a very competitive industry and cannot raise the price because our largest competitor sells the same table for $500.
But these data tables give us an idea of how the outcome we are analyzing will be impacted in a dynamic environment.
The limitation of sensitivity analysis data tables is the human component – the assumptions.
If your assumptions are not reasonable, then your results will not be informative.
What-if-analysis in Microsoft Excel is one of the fastest and most powerful ways to evaluate dozens of hypothetical situations at the same time.
These techniques are also easy to learn - you should be a pro now.
Goal seek is used to analyze one assumption when targeting an specific outcome.
When you need profit margin to be a specific number, and you are only planning to change price, use goal seek.
Sensitivity analysis data tables are used to analyze assumption combinations when targeting a specific outcome.
When you need profit margin to be a specific number, have the ability to change price and manufacturing costs simultaneously, then you should set up a data table to see what happens to profit in many “what if” situations at the same time.
To go deeper with what-if-analysis and the rest of Microsoft Excel, join the army of students on Udemy.com taking my video-based course: Become An Excel Power User in 2.5 Hours. Take a look below!