160 likes | 244 Views
7. Sensitivity analysis. Impact analysis, worst case scenarios, risk analysis, Monte Carlo simulation techniques. We have built a great model, but is it of any use?. Overview. 7.1 Impact Analysis 7.2 Scenario Analysis 7.3 Monte Carlo Simulation. Impact Analysis on IRR.
E N D
7. Sensitivity analysis Impact analysis, worst case scenarios, risk analysis, Monte Carlo simulation techniques Sensitivity Analysis
We have built a great model, but is it of any use? Sensitivity Analysis
Overview • 7.1 Impact Analysis • 7.2 Scenario Analysis • 7.3 Monte Carlo Simulation Sensitivity Analysis
Impact Analysis on IRR • One uncertain item at the time, say Price, Sales and Equipment • Insert impact factor 100% and multiply uncertain item with that • Use Excel Data Table for a range of factor values, say from 50% to 150% • Do same for other uncertain items • Use Excel Chart Sensitivity Analysis
Data Tables for Impact Analysis on IRR of Equity Sensitivity Analysis
Try to see the big picture! Sensitivity Analysis
Scenario Analysis • Many uncertain items changed at the time • Use Excel Scenario Manager • Select Changing Cells • Determine values for each scenario • Determine name of scenarios, for example Pessimistic, Optimistic, .., • Select Result Cells Sensitivity Analysis
Scenario Analysis Sensitivity Analysis
Pessimistic scenarios are more common than optimistic! Sensitivity Analysis
Monte Carlo Simulation • Random number generators • Probability distributions • Simulation tools (@Risk or Crystal Ball) Sensitivity Analysis
Three Point Estimate: a, m, b • For uncertain items, say cost • Assume a Beta probability distribution (or Triangular) • Three points estimated: • optimistic a • most likely m • pessimistic b Sensitivity Analysis
Triangular Distribution Sensitivity Analysis
Simulation with Random Number Generation • Simulation Tools, add-ins to Excel • @Risk • Crystal Ball • Generate histogram for a result item, say IRR of Equity Sensitivity Analysis
Histogram of IRR of Equity Sensitivity Analysis
Beginning to see the light? Sensitivity Analysis