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

• 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? 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

• This advanced financial modelling course assesses 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

• Session 3 of this advanced financial modelling course explores 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 judgment: 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

• The following essential output metrics are covered in this advanced financial modelling course: ROCE

• DuPont decomposition

• Leverage and coverage ratios

• 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

• 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 advanced financial modelling 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.

Training Objectives

• 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 speed up model auditing and error detection

• Create dynamic divisional and consolidated summaries for multi-segment businesses.

Training Course Summary

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

Training is delivered through a hands-on workshop format. The course combines instructor-led walkthroughs, practical Excel exercises, and real-world case studies.

It is 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.

Participants of this advanced financial modelling course 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