Sensitivity analysis in Excel

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 option is to perform in Excel through a two-variable data table where we can see the effect...