Sensitivity & Scenario Analysis Excel Template

How to use an excel data table for sensitivity and scenario analysis for you financial model.

by Michael Shane
Cloud Computing @ Deloitte IndonesiaFollow 72

106 reviews6,397 views|Start the discussion!|Bookmark

Description
The following Excel template is a model used for sensitivity and scenario analysis (or What-If analysis). The Excel template provides 2 types of data tables; 1-way data table and a 2-way data table.

The 1-way data table is used for when we want to see how sensitive an output is (or many outputs are) when set against the changes of 1 of its inputs (variables). On the other hand, we use the two-way data table when we want to see how an output changes when two of inputs changes.

So when we want to see how significantly the output changes when “more than 2” of the inputs change, what do we do?

As an example, if we want to perform a simulation for ROE, GPM, Net Profit, Current Ratio and EBITDA, by changing the price, volume, COGS and SGA expenses. In this case, we would need a 4 way data table as we use 4 variables.

However, Excel is not equipped with more-than-2 way data tables. So in order to solve this issue, we have listed a series of steps explained in the methodology, that you may use along the template.

Also, we can use as many inputs and outputs as we desire in this model, which makes it a very versatile platform for sensitivity and scenario analysis.

If you have any questions or feedback on the model, do comment on the discussion section below.

-Michael Shane

This Best Practice includes
1 Excel sheet, 1 methodology