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.

⏱️TIMESTAMPS⏱️
00:00 Introduction to sensitivity analysis
00:40 Setting up formulas
01:19 One variable data table
02:49 Two variable data table
04:48 Data table formatting
06:06 Sensitivity analysis in Excel
07:14 Copying over a data table
07:57 Troubleshooting Excel data tables
09:12 Sensitivity analysis summary

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.

The more powerful #sensitivityanalysis option is to perform #whatifanalysis 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. 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.

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!

Philip de Vroe (The Finance Storyteller) aims to make accounting, finance and investing enjoyable and easier to understand. Learn the business and accounting vocabulary to join the conversation with your CEO at your company. Understand how financial statements work in order to make better investing decisions. Philip delivers #financetraining in various formats: YouTube videos, classroom sessions, webinars, and business simulations. Connect with me through Linked In!

Want to get access to bonus content, and/or express your gratitude by buying me a cup of tea? Join my channel as a member through https://www.youtube.com/channel/UCQQJnyU8fALcOqqpyyIN4sg/join