Master Class: Financial Modelling For Strategic Decision-Making
Course Summary
4 Days | High Impact
This intensive 4-day course equips participants to build sophisticated financial models from scratch in Excel, using best practices adopted by leading investment banks.
The program begins with a foundation in Excel efficiency—shortcuts, key functions, and finance-specific formulae—before progressing to the construction of a fully integrated 3-statement model using live data from Walmart Inc. Participants will learn model planning, data extraction, and normalization from public sources. Forecasts incorporate detailed debt amortization, cash sweeps, revolvers, and corkscrew logic to ensure balance sheet integrity. Auditing techniques and structural checks are embedded to ensure robustness.
The completed model forms the base for valuation modules—Discounted Cash Flow and Comparable Company Analysis—where participants derive WACC, Enterprise Value, and Free Cash Flows. Flexibility is built in via optionality for beta types, tax rates, capital structures, and terminal value methods. Scenario and sensitivity analysis round out the training to enhance decision-making under uncertainty.
Learning Objectives
- Excel Tools for Effective Modeling: Master formula-based conditional formatting, use of cell and range names, advanced date functions, and robust data validation. Learn to perform dynamic lookups using INDEX-MATCH and XLOOKUP, apply key Excel functions—including logical formulas (the IF family), MIN/MAX, and others—to model scenarios such as debt amortization.
- Model Set-up: A fully integrated financial model by establishing a structured framework that links the Profit & Loss, Balance Sheet, and Cash Flow Statement. Develop key operating assumptions and cash flow drivers, supported by schedules for working capital, capex, debt, and equity. Emphasis is placed on model flexibility, auditability, and integrity to support valuation, sensitivity, and scenario analysis.
- Valuation: Develop a valuation framework focusing on DCF and comparables. Extract FCF, derive WACC via adjusted beta and CAPM, and apply terminal value and stub adjustments. Use sensitivity analysis to test assumptions. For comparables, evaluate trading and deal multiples. Summarize results with a football field chart.
Who Should Attend
This course is designed for:
- Finance analysts
- Graduates preparing for IB interviews
- Investment bankers
- Private equity professionals
- Corporate finance professionals
Learning Topics
Excel Tools & Modeling Foundations
Excel for Financial Modeling
- Best practices in model layout and structure
- Excel shortcuts and navigation efficiency
- Formula-based conditional formatting for alerts and insights
- Use of cell and range names to enhance model clarity
– Advanced date functions: EOMONTH, YEARFRAC, DATEDIF
– Building flexible formulas for forecast drivers INDEX-MATCH, and XLOOKUP
– Data validation techniques to control user inputs - Dropdowns and list-based selectors for scenario toggles
Integrated 3-Statement Model Development
- Structuring revenue and cost forecasts
- Modeling operating expenses, margins, and EBITDA
– Integrating assumptions into the income statement
– Constructing supporting schedules: working capital, capex, depreciation - Building the cash flow statement: direct vs indirect approach
– Linking statements through corkscrew logic and integrity checks
– Complete the full historical and forecasted 3-statement model using real data (Walmart Inc. or Ferrari S.p.A.)
Debt and Equity Schedule Construction
- Modeling amortizing loans, bullet repayments, and revolving credit lines
- Interest calculations with beginning/ending balances
– Cash sweep mechanics and repayment prioritization
– Balance sheet checks, sign conventions, and validation flags - Building traceability using Excel auditing tools
Scenario and Sensitivity Analysis
- Embedding scenario toggles: base, upside, downside cases
- Using data tables for one-way and two-way sensitivity
- Dynamic visualizations to present output variability
Valuation & Presentation Output
DCF Valuation Framework
- Calculating Unlevered Free Cash Flow (FCF)
- Deriving WACC using CAPM and adjusted beta
- Estimating terminal value: Perpetuity Growth Method vs Exit Multiple
- Applying stub period adjustments for partial-year projections
Comparable Company Analysis
- Selecting appropriate comps and peer metrics
- Calculating and interpreting trading and deal multiples
- Adjusting for non-recurring items and outliers
Synthesizing Results & Presenting Valuation
- Summarizing valuation outputs in a football field chart
- Integrating valuation into the financial model
- Best practices for communicating valuation findings
Final Exercise & Wrap-up:
- Complete valuation modules on the case model
- Present and review group models and conclusions