Sensitivity analysis: how do outcome variables change in relation to changes in input variables.
In this Excel file, we will perform sensitivity analysis using the input variables right here
at the top: the expected volume for a new product, its selling price per unit, and its
variable cost per unit.
The outcome variable of the sensitivity analysis is down here in cell B11 of the profit and
loss forecast: the level of Operating Margin.
This is the number we want to validate and optimize.
We want to get a feeling for its sensitivity to the inputs.
As you can see when scrolling through the cells of the profit and loss forecast, nearly
all of these are driven by formulas.
Revenue is volume times price.
Variable cost is volume times variable cost per unit.
Contribution margin is revenue minus variable cost.
Fixed cost is a hardcoded input.
Operating margin is contribution margin minus fixed cost.
So cell B11 operating margin is linked through formulas to the input variables in cells B2,
B3, and B4.
This is very important for the sensitivity analysis to work.
Let’s start off with a one-variable data table.
We need to let Excel know what the input variable and the outcome variable are.
Starting in cell D4, we are going to input 5 possible levels of volume: 80 thousand,
90 thousand, and then autofill to get 100 thousand, 110 thousand, and 120 thousand.
In cell E3, we link to our outcome variable of Operating Margin.
We select the area where we want the data to appear, go to the Data tab, What-if analysis,
Data Table: “See the results of multiple inputs at the same time”.
The dialog box by default asks you for row input and column input.
As we first build a one-variable data table, we leave the row input cell blank, and link
the column input to cell B2.
When you click OK, the data table is populated with $ values of Operating Margin for different
volume levels.
At 80 thousand units, Operating Margin is $120 thousand.
Every incremental 10 thousand units in volume adds $40 thousand in Operating Margin.
All the way to an Operating Margin of $280 thousand at a volume of 120 thousand units.
To remember later what this data table represents, let’s add “Volume” and “Operating
Margin” as headers.
The more powerful sensitivity analysis option is to perform what-if analysis in Excel through
a two-variable data table where we can see the effect on Operating Margin of changing
two inputs simultaneously.
Start off with letting Excel know what the input variables and the outcome variable are.
In H3, we link to B11, as we want to perform a sensitivity analysis on Operating Margin.
In I3 through M3, we input various levels of price: from $8 to $12.
In H4 through H9, we want to have various levels of volume: from 80 thousand units to
120 thousand units.
From these inputs, we are going to build a 5-by-5 data table.
If you want to, you can make this 10-by-10, there’s no limitation to the size as far
as Excel is concerned.
The data table doesn’t even have to be symmetrical, you can have it 10-by-15, 20-by-48, or any
other combination.
Select the area where you want the data to appear, go to the Data tab, What-if analysis,
Data Table.
Be careful here with row input cell, and column input cell.
The row input cell refers to the row of prices ($8 through $12) that is used to generate
Operating Margin levels in the rows below it.
Therefore, we link the row input cell to B3 (price).
The column input cell refers to the column of volumes (80 thousand through 120 thousand)
that is used to generate Operating Margin levels in the columns to its right.
Therefore, we link to the column input cell to B2 (volume).
This might be counterintuitive to some people, so be careful not to switch things around!
Click OK, and 25 cells of Operating Margin levels appear in our 5-by-5 data table.
Let’s format the cells in the data table as $ amounts.
Let’s make the price and volume input variables bold.
Add the label “Price” at the top, which we merge and center across, and apply a border to.
Add the label “Volume” on the left, which we also merge and center, align center, and
apply a border to.
The $200,000 in cell H3 is needed for the data table to function, but if we think this
looks out of place for presentation purposes, then we can hide it by making the font color white.
It’s still there, don’t delete it, but now the font color is the same as the background.
On the Home tab, we can apply conditional formatting to the values in the data table.
We want every cell with a value greater than our base case of $200 thousand of Operating
Margin to be green.
We want every cell with a value less than our base case of $200 thousand Operating Margin
to be red.
We can even set an additional formatting rule for cells with an Operating Margin less than
zero (loss making situations), through custom formatting, to show in bold black font, with
a dark red background.
This is where sensitivity analysis in Excel can provide very important insights.
At our base case of 100 thousand units sold at $10 per unit, the expected Operating Margin
is $200 thousand.
At constant volume of 100 thousand units, every dollar of price (up or down) makes a
difference of $100 thousand in Operating Margin.
At constant price of $10, every 10 thousand units of volume (up or down) makes a difference
of $40 thousand in Operating Margin.
Why is that the case?
The difference between price and variable cost per unit is $4 ($10 minus $6), multiplied
by 10 thousand units, is $40 thousand.
The Operating Margin projections become much better if we move South-East, towards higher
price and higher volume.
They get much worse if we move North-West, towards lower price and lower volume.
At $8 price level, and lower volume levels, we even risk having negative Operating Margin!
Let’s do a second sensitivity analysis in a two-variable data table, this time with
price per unit and variable cost per unit.
The lazy way to set this up is to copy over the existing data table, keep the price input,
and replace the volume input by variable cost input.
Variable cost of $4, $5, $6, $7 and $8.
We have to set up the data table for the new variables.
Select the area where you want the data to appear, go to the Data tab, What-if analysis,
Data Table.
The row input cell is B3 price.
The column input cell is B4 variable cost per unit.
When you click OK, you see no values in the data table.
Let’s troubleshoot that problem.
For a data table to work properly, we need to let Excel know what the input variables
and the outcome variable are, and we need input and outcome to be linked through a formula.
We also need to stay on the same worksheet, we can’t put the data table in a different tab.
We just specified price and variable cost per unit as input variables, but did not check
the outcome variable.
On the top left of the table, in our earlier formatting stage, we were hiding the $200
thousand of Operating Margin by changing the font to white.
Let’s change that back to black.
Cell H11 refers to the empty cell B19, while H3 refers to the Operating Margin formula in B11.
We need to link H11 to B11, and voila: data appears in the table.
By the way, another common troubleshooting item that could cause a similar issue is your
workbook calculation settings.
Go to File – Options – Formulas and make sure that workbook calculation is set to automatic.
What we see here is that as long as the difference between price and variable cost per unit is
$4, the Operating Margin is $200 thousand.
For every $1 of increase in price, or decrease in variable cost per unit, the Operating Margin
goes up by $100 thousand.
If price goes down, and variable cost goes up, you get into the danger zone of negative
Operating Margin.
We could insert a third data table to perform a sensitivity analysis of input variables
volume and variable cost per unit on Operating Margin.
This might provide additional insights.
However, we cannot accommodate more than two variables into a data table in Excel.
If we want to change all three input variables at the same time, we can use What-If Analysis
– Scenario Manager, but that’s a different story!