Is it possible to do an excel sensitivity analysis with three variables?
Answers
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
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
Feel free to contact me if you would like further information on scenario analysis.
All The Best,
Dave
1
Great answer, David.
Here's a treasure trove of free excel spreadsheets, free
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
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
Alan,
Simple suggestion is to create a two-way data table on a single sheet in
Not elegant, but it will achieve your goal of changing 3 variables in a sensativity analysis.
Good luck.
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.
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
Check this post out
https://marketxls.com/dupont-analysis-in-excel/
Filed Under:
FP&A