Advanced Financial Modelling

Session 1: From Clean Data to Messy Reality

Linking and Reconciling Historical Financials

• Importing and structuring historical data for model integration
• Adjusting for inconsistencies in accounting periods, reporting formats, and restatements
• Reconciling historical data with external reports (audited financials, management accounts, broker research)
• Handling differences between IFRS and US GAAP presentations in source data
• Structuring the historical tab: layout best practices, colour coding inputs vs calculations, and ensuring auditability

Normalising Earnings

• Identifying and adjusting for non-recurring items (one-time expenses, extraordinary income, discontinued operations)
• Reclassifying items to produce a clean underlying earnings profile
• Effective vs marginal tax rate considerations and normalising the tax charge
• Adjusting for changes in accounting policy and prior period restatements
• Practical judgement: which adjustments do lenders, acquirers, and investors typically accept, and which are contested?
• Working through a normalisation case study: identifying adjustments from real financial statements and management commentary

Session 2: Driver-Based Forecasting

Revenue Forecasting

• Top-down vs bottom-up forecasting approaches and when each is appropriate
• Modelling revenue drivers: price, volume, and mix decomposition
• Building revenue bridges that explain year-on-year movement
• Industry-specific forecasting nuances (e.g., subscription-based revenue vs project-based vs product sales)
• Incorporating management guidance, broker consensus, and independent assumptions into the forecast

Cost Forecasting

• Fixed vs variable cost structures and modelling operating leverage
• Forecasting margins and cost efficiencies as the business scales
• Handling step-function costs and semi-variable cost behaviour
• Modelling economies of scale and diseconomies at different revenue levels
• Separating COGS from SG&A and understanding the margin waterfall (gross margin to EBITDA to EBIT)

Working Capital Forecasting

• Days-based working capital modelling: receivable days, payable days, inventory days
• Understanding the cash conversion cycle and its funding implications
• Seasonality adjustments and sensitivity to operational changes
• Modelling the difference between operating working capital and total working capital
• How working capital movements flow through the cash flow statement and impact free cash flow

Capital Expenditure and Depreciation

• Maintenance vs growth capex: aligning capital expenditure with revenue assumptions
• Linking capex to the PP&E schedule and depreciation waterfall
• Forecasting capital expenditures using capex-to-sales ratios, management guidance, and historical patterns
• Structuring depreciation schedules for historical and incremental capex with different useful economic lives

Session 3: Advanced Debt Architecture

Revolver Facilities

• Modelling drawdowns, repayments, and commitment fees
• Interest calculation methods: average vs opening balance approach
• Setting up the revolver as the balancing item in the cash flow waterfall
• Modelling facility limits and availability constraints

Term Loans and Mandatory Payments

• Straight-line amortisation, sculpted repayment profiles, and balloon payments
• Discretionary vs mandatory repayments and their modelling treatment
• Cash sweep mechanics: modelling excess cash flow sweeps and integrating into the debt waterfall
• Modelling multiple tranches of debt with different seniority, pricing, and repayment terms
• PIK (payment-in-kind) interest and its impact on the debt balance and cash flow

Debt Capacity and Serviceability

• Assessing debt capacity using unlevered free cash flow
• Debt Service Coverage Ratio (DSCR) and leverage ratio calculations (Net Debt / EBITDA, Net Debt / Equity)
• Interest coverage ratios: EBIT-based vs EBITDA-based, and when each is appropriate
• Covenant modelling: compliance triggers, cure mechanisms, and headroom analysis
• Building a covenant compliance dashboard within the model
• Cash flow waterfall for leveraged businesses: EBITDA to Cash Flow Available for Debt Service to Cash Flow Available for Debt Repayments

Session 4: Model Integration and Scenario Analysis

Debt Schedule Integration

• Linking interest expenses, principal repayments, and cash sweeps into the income statement and cash flow statement
• Handling the circularity that arises: interest depends on debt, debt depends on cash, cash depends on interest
• Managing circular switches in Excel: practical approaches to iterative calculation
• Balancing the balance sheet through the cash flow statement and confirming the model ties
• Building automated balance sheet checks and error flags

Scenario and Sensitivity Frameworks

• Building dynamic scenario models using CHOOSE, INDEX, OFFSET, MATCH, and XLOOKUP
• Sensitivity analysis with one- and two-variable data tables
• Framing scenarios around decision-useful questions: what breaks the covenants? What is the downside return? At what revenue decline does the business breach DSCR thresholds?
• Designing sensitivity matrices for IC memos, credit papers, and board presentations
• Management case, bank case, and downside case: structuring the three scenarios a lender or investor expects to see
• Linking scenario outputs to a summary dashboard with key metrics and visual indicators

Session 5: Model Integrity, AI-Assisted Review, and Handover

Error Proofing and Debugging

• Cross-check techniques that catch real problems: balance sheet balance checks, cash flow reconciliation, and ratio trend analysis
• Checking formulae for column consistency and identifying hard-coded overrides
• Stress testing against covenant thresholds and identifying model breaking points
• Sense checking techniques: reviewing outputs against industry benchmarks and historical trends
• Case studies: reviewing models containing errors and applying techniques on how to identify and correct them

AI-Assisted Model Review

• Practical demonstration of AI tools for auditing formula consistency across large models
• Using AI to flag analytical outliers, unusual trends, and potential errors in model outputs
• Generating assumption commentary and model documentation with AI assistance
• Automating repetitive review tasks: formatting checks, naming convention audits, and circular reference detection
• Limitations and judgement: what AI can and cannot reliably check in a financial model
• How AI-assisted review fits into a professional model review workflow alongside manual techniques

Output Design and Model Handover

• Designing the model output dashboard: key ratios for investment committees, lenders, and boards
• Essential output metrics: ROCE, DuPont decomposition, leverage and coverage ratios, and cash conversion analysis
• Structuring the model for handover to a third party (buyer, lender, board member)
• Formatting and navigation best practices for models that will be reviewed by non-builders
• Presentation formats and conventions: positive vs negative sign conventions, colour coding, and tab naming

Bridge to Valuation

• High-level introduction to an initial valuation exercise linking model outputs to a foundational DCF and LBO model
• How the forecast cash flows, WACC assumptions, and exit multiples connect to the 3-statement model built during the course
• Understanding how different model assumptions (revenue growth, margins, capex, debt structure) flow through to equity value
• These valuation methodologies can be further explored in depth through our advanced sessions on LBO Modelling and Valuation

Course Conclusion and Wrap-Up

Please note: a longer two-day version of this course is also available as an in-house option, incorporating divisional summaries, D&A waterfall builds, monthly vs annual model structures, and extended valuation exercises.

The course trainer is an investment professional with direct experience across hedge funds, private equity, investment banking, and M&A advisory. He currently runs an independent investment and advisory firm and works on live healthcare and technology transactions.

His training programmes are built from practitioner experience rather than textbook theory. Participants learn the modelling techniques that are used in real transactions, taught by someone who uses them in live deal work. He has delivered advanced financial modelling, valuation, and corporate finance training for clients, including ICAEW and financial institutions across Europe.

This breadth of practical experience and his passion for sharing and communicating knowledge have now led him to deliver training courses on accounting, financial modelling, corporate finance, valuation, and capital markets at investment banks, private equity and corporate finance firms throughout the world.

• Link, reconcile, and normalise historical financial data from multiple sources into a model-ready format
• Build driver-based revenue and cost forecasts that reflect real business economics rather than simple growth rate assumptions
• Construct advanced debt schedules incorporating revolvers, term loans, amortisation profiles, cash sweeps, and covenant compliance triggers
• Integrate complex debt structures into a fully linked 3-statement model, managing the resulting circularities
• Design scenario and sensitivity frameworks that produce decision-useful outputs for investment committees, credit papers, or board presentations
• Apply model integrity techniques including stress testing, cross-checks, and structured model review
• Use AI-assisted tools to accelerate model auditing and error detection
• Create dynamic divisional and consolidated summaries for multi-segment businesses

This Advanced Financial Modelling course is designed for:

• Financial analysts and associates who can build a basic 3-statement model and need to handle more complex modelling situations
• Corporate finance and advisory professionals working on transactions where debt structuring, covenant analysis, or earnings normalisation is required
• Private equity, credit, and lending professionals who need to assess or build models incorporating advanced debt mechanics
• Finance professionals preparing for roles that require model review, stress testing, or producing IC-ready model outputs
• Financial analysts who are seeking to develop their skills in checking the accuracy and integrity of financial models and performing sensitivity analysis

Pre-requisites - This course assumes participants are comfortable with the mechanics of a basic integrated 3-statement model (income statement, balance sheet, cash flow statement) and have working proficiency in Excel. Participants who have completed a foundational financial modelling course or who build models as part of their current role will get the most from this programme.

This advanced financial modelling course focuses on bridging the gap between foundational modelling and the complex realities encountered in live transactions, including messy data, advanced debt structures, and decision-oriented outputs.

Delivered through a hands-on workshop format, the course combines instructor-led walkthroughs, practical Excel exercises, and real-world case studies to reinforce application.

It is designed for analysts, associates, and finance professionals who already understand core modelling principles and want to build, review, and interpret models at a professional, transaction-ready standard.

Partcicpants will walk away with:

• A completed advanced financial model (Excel file) built during the course
• A model review checklist for use in your own work
• A formula and shortcut reference card
• Course workbook summarising key concepts, exercises, and reference materials