Add sensitivity analysis to your worksheets

BVWireIssue #105-3
June 15, 2011

Samuel Weaver (Finance Professor of Practice, Lehigh Univ.) advises business appraisers to take advantage of more of the available risk analysis tools—even the seldom-used functions in Excel. For instance, while speaking to over 200 NACVA/IBA members last week in San Diego, Weaver asked how many routinely use sensitivity analysis in their DCF’s.  Only about 15% of the room raised their hands, even though this simple tool (located under data and then “What If Analysis” and then “Tables” in Excel 2007) can “do this in five minutes.”

“Data tables can quickly be created on any cell—COGS, NPV, growth rate,” said Weaver. The range of solutions is similar to a Monte Carlo analyses and looks like a roulette wheel when displayed graphically (“They didn’t want to call this method the ‘Atlantic City’ analysis, even though they also have roulette wheels,” Weaver joked.)

Add probability percents to the ranges of assumptions to enhance sensitivity analysis on such elements as cost of capital.  A range of outcomes may not succeed in court, Weaver noted, but may help business owners to assess risks and outcomes. Software such as Crystal Ball and @ RISK “can really help add value for your clients,” he said.

Please let us know if you have any comments about this article or enhancements you would like to see.