more-arw search

Q&A Forum

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

Alan Jones's Profile

excel sensitivity analysis

Answers

Topic Expert
David Wittenberg
Title: Director of Financial Strategy
Company: World Vision
(Director of Financial Strategy , World Vision) |

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

Sarah Jackson
Title: Associate Editor
Company: Proformative
(Associate Editor, Proformative) |

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

Doug Neeper
Title: FP&A - making better decisions
Company: DFN Consulting
(FP&A - making better decisions, DFN Consulting) |

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

Topic Expert
Henry Schumann
Title: Manager FP&A
Company: Allscripts
(Manager FP&A, Allscripts) |

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.

Jason Chroman
Title: Vice President Finance & Controller
Company: Tubular Labs
LinkedIn Profile
(Vice President Finance & Controller, Tubular Labs) |

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.

Thomas Knoll
Title: scientific staff
Company: Chemnitz University
(scientific staff, Chemnitz University) |

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

Harrison Delfino
Title: Freelancer
Company: oDesk Freelancer
(Freelancer, oDesk Freelancer) |

Check this post out
https://marketxls.com/dupont-analysis-in-excel/

38259 views
Topics

Get Free Membership

By signing up, you will receive emails from Proformative regarding Proformative programs, events, community news and activity. You can withdraw your consent at any time. Contact Us.

Business Exchange

Browse the Business Exchange to find information, resources and peer reviews to help you select the right solution for your business.

Learn more

Contribute to Community

If you’re interested in learning more about contributing to your Proformative community, we have many ways for you to get involved. Please email content@proformative.com to learn more about becoming a speaker or contributing to the blogs/Q&A Forum.

Excel @ Excel - 30 Courses Available