Marginal Books

Business Technology Article 01 - Sensitivity Analysis

(With a good example of its usage in Monte Carlo Simulation)

In various models of the 2 Monte Carlo Simulation eBooks, I use Sensitivity Analysis. Although in one or two cases, the steps are detailed, it would be out of scope for the two eBooks to go into detailed procedures for conducting Sensitivity Analysis.

This article gives a general background of this powerful facility. More importantly, it provides several workouts showing the various applications of Sensitivity Analysis, specifically in Monte Carlo Simulation. A 9 step  tutorial is included on the use of What If tables. Three other examples are also provided to show the power of What If data tables. One of these covers the use of What If to replicate rows in Monte Carlo Simulation with Excel.

Modeling Input and Output

As a term, “Sensitivity Analysis” conveys so much meaning it can easily be misunderstood. To be fair to Microsoft Excel, it does not use the term. It uses the term “What If” analysis (under the Data menu).

A model consists of X inputs, a formulation and Y outputs. I use the algebraic notation where X is the independent variable and Y is the dependent variable. In a model, as the values of X change, the outputs Y will change.

The question is: how many of each of X and Y are allowed in different models? When using Excel, there are 4 available  facilities:

  1. What If Analysis with 1 variable: 1 input variable and many output variables
  2. What If Analysis with 2 variables: 2 input variables with 1 output variable.
  3. Scenario Manager: many input variables and many output variables
  4. Monte Carlo Simulation: many input variables and many output variables

The difference between the last two facilities is that the scenarios in the Scenario Manager have to be entered manually, each scenario defining the values of its own input variables. Monte Carlo Simulation allows you to duplicate the formulations and use randomly sampled input values.

Sensitivity analysis is the answer to the question: what happens to my output as my input changes? It is often not enough to view the resulting output as the input changes. Sensitivity analysis expects you to find out how that change behaves. Is it linear? Is it exponential? Is it a complex curve that needs to be fitted mathematically, with an equation?

What can you do with the resulting What If Tables?

On completing a What If analysis, you are left with a two column table (at least). One column would be defined by you, and it shows the range and shape of the input you wish to apply in the formulation. The other column(s) will be generated by Excel. These are the result of applying the formulation in the model using each one of the input values.

  1. The table can be analyze statistically

  2. Given a two column table, chart it so the independent (input) variable is on the horizontal axis and the dependent (output) variable is on the vertical axis, you will get a visual assessment of how Y depends on X.

  3. You can use conditional formatting to highlight specific results such as all net profits that are below zero or all sales that are between two values.

  4. Other data analytic processes can be applied using Excel’s Analysis Toolpack or its statistical functions: contingency testing (chi-squared), forecasting, descriptive statistics.

You can also use the What If table to replicate rows in a Monte Carlo Simulation model (see the Excel workout in the download file).

Download a Step by Step Tutorial in Excel

Click Here to download a zipped file containing:

  1. A 9 step tutorial on What If tables in Excel
  2. An example of conflicting IRR’s exposed by What If tables
  3. An example of multiple IRR’s exposed by What If tables
  4. A workout (in PDF Format) and its solution taken from the Monte Carlo Simulation book on this website. (Part 1). It is used to show how to replicate rows in the simulation model.

The example in the 9 step Tutorial is based on a very brief Income Statement. The requirement is to project the budgetary figures for the next year (NY) using actual values from the current year (CY) multiplied by growth factors. Our objective is to see how the Net Profit Margin depends on the Sales Growth used. The formulation shows it for 4%. The What If analysis allows you to vary the Sales Growth from 2% to 12% in 0.5% increments and study the resulting Net Profit Margin.

The last worksheet shows how sensitivity analysis is applied to discounted cash flow. Since IRR is defined as the value of the discounting rate when NPV = 0, the chart of the What If table will show you exactly where IRR is.

But Excel has an IRR function, so why bother? There are two reasons:

a) Multiple IRR’s: there might be cash flows in a single project that can result in more than one value of the discounting rate for NPV = 0. (An example is included in the download file). This will result in more than one IRR which the Excel function cannot show.

b) Conflicting IRR’s: when comparing two projects, it is possible that in some ranges of discounting rates, one project would have a higher NPV than another while at a later value, the reverse would be the case. (An example is included in the download file). IRR on its own can only show one project to be better than the other. Only a visual approach can clarify the global comparison.

To comment, no need to register or log in with Disqus. Enter any name and a check box will enable you to comment as a guest. Your email will be required but Disqus will not show it in the thread. I am committed to your privacy and confidentiality and will not use it or distribute it. Click Here to review guest privacy and commenting policies in Disqus.

Top of Page