Is it possible to do an excel sensitivity analysis with three variables?

Alan Jones's Profile

excel sensitivity analysis

Answers

Topic Expert
Member's Profile
Verax Point

Alan,

The two most common approaches for analyzing multiple variables are:

1) Scenario Analysis: Creating a half dozen or more reasonable combinations of the variables. This is particularly necessary when the variables tend to move together. For example, in an economic downturn, a restaurant may experience both a volume decrease as people eat out less AND a net margin decrease as you compete for customers with discount specials or complete price reductions. Your business team can determine which scenario is most likely and assign probabilities to the more optimistic and pessimistic scenarios to develop a complete risk profile.

2) Monte Carlo Simulation: Applying a scenario simulator such as @Risk or Crystal Ball. I generally discourage use of this tool. While it is powerful and elegant, it is far more complex to produce a good analytical run -- but it is almost impossible to tell the difference between a good run and a flawed run. As a result, most companies make worse decisions because of Monte Carlo analysis.

Take a look at this prior Proformative discussion: Monte Carlo Simulation Forecasting Models as well as an earlier rant of mine against Monte Carlo Simulations: http://capexcompass.com/2011/07/18/monte-carlo-traps/

Yes, I have strong feelings against Monte Carlo Simulations because of the how badly I've seen it employed. Of the dozen companies I researched, only one (DuPont) invested in the thorough training of experts, analysts and executives to generate quality results. The rest were acting on highly flawed information.

Feel free to contact me if you would like further information on scenario analysis.

All The Best,
Dave

Member's Profile

Great answer, David.

Here's a treasure trove of free excel spreadsheets, free accounting spreadsheets, including free excel sensitivity analysis spreadsheets:

https://www.proformative.com/resources/free-accounting-spreadsheet

Plus, this free "Excel Short Cuts Cheat Sheet"

https://www.proformative.com/whitepapers/excel-shortcuts-cheat-sheet

Enjoy!

Best... Sarah

Member's Profile

Short yes, its possible; the potential 'problem' is are the variables the 'right' variables. In most financial models there are many variables; assuming your model calculates net present value, vary the variables one at a time, and see which three variables have the largest impact on NPV. Use these variables to drive your sensitivity analysis, and as others have suggested you can display the results tabularly, in a graph, or (gasp) in a monte carlo simulation.

(As an aside, if you're not calculating NPV, use total net income, total revenues, total cash, whatever seems to be the next best measure).

Doug Neeper

Proformative Advisor
Member's Profile

Alan,
Simple suggestion is to create a two-way data table on a single sheet in Excel, then create multiple copies of the sheet. On the other sheets you can change the 3rd variable as needed and rerun the two-way data table with the changed 3rd variable.

Not elegant, but it will achieve your goal of changing 3 variables in a sensativity analysis.

Good luck.

Member's Profile

A cleaner, more elegant way to do this is to use a one variable column oriented data table. Have that one variable be a counter. To the left of the counter column, use lookups to control the specific assumptions that you want to change. On the right, have the outputs that you want to consider.

Member's Profile

Hello Alan,

I have programmed a little Excel Add-In macro, which allows you to put sensitivity analysis on one, two, three, and up to twenty input cells in your spreadsheet and at the same time observe one or multiple output cells for their reaction on the input variations.
You can either chose to have those input cells varied one at a time ("single Sensitivity") or in all combinations of the varied inputs ("multiple Sensitivity").
The add-in is free for commercial or private use and can be found at: http://www.life-cycle-costing.de/sensitivity_analysis/

I hope this little tool does just what you had been asking for.

Would be nice to hear, whether this solved your problem.

BR
Thomas