Overview
This lecture covers best practices for building sensitivity analysis in Excel-based financial models, including direct and indirect methods and professional presentation using gravity sort tables and tornado charts.
Introduction to Sensitivity Analysis
- Sensitivity analysis assesses how changes in key assumptions impact financial model outputs.
- Common applications: valuation, business planning, and model testing.
- Key users include investment banking, equity research, FP&A, and corporate development professionals.
Reasons for Sensitivity Analysis
- Valuation: Shows business value range under different scenarios.
- Business Planning: Assesses cash flow needs, funding, and scenario planning.
- Model Testing: Stress tests the model to ensure logical results.
Model Integration Approaches
- Sensitivity analysis is performed after the core financial model is built, typically on a separate worksheet area.
- Direct method: Adjusts model input variables (hardcoded assumptions).
- Indirect method: Alters formulas by adding a sensitivity driver for more flexible analysis.
Direct Method Steps
- Select a model input (e.g., revenue growth) and define a sensitivity range.
- Choose the output to analyze (e.g., share price).
- Set up a data table in Excel linking inputs to outputs.
- Fill the table to view results under varying assumptions.
Indirect Method Steps
- Create hardcoded cells for each variable to sensitise (input zero initially).
- Insert these variables into relevant formulas (e.g., revenue, COGS, discount rate, exit multiple).
- Build data tables by referencing these new input cells and desired output.
Analyzing and Presenting Results
- Identify which model inputs have the greatest impact using a gravity sort table (ranks inputs by effect).
- Use Excel’s small/large, index, and match functions to automate sorting.
- Build a tornado chart from gravity sort data for clear visual communication.
- Combine detailed tables and summary charts for professional presentation.
- Discuss and display the variability of each input, showing a range of potential outcomes.
Key Terms & Definitions
- Sensitivity Analysis — Examining how model outputs change as assumptions vary.
- Direct Method — Sensitizing outputs by adjusting model input variables directly.
- Indirect Method — Sensitizing outputs by inserting sensitivity variables into formulas.
- Gravity Sort Table — A table ranking sensitivities by their impact on outputs.
- Tornado Chart — A bar chart visualizing model output sensitivity to various drivers.
Action Items / Next Steps
- Practice building both direct and indirect sensitivity analyses on your financial model.
- Construct a gravity sort table and tornado chart for key assumptions.
- Review Fundamentals of Financial Modeling and Building a Model in Excel courses if concepts are unclear.