Programme overview
Introduction:
Many finance teams rely on forecasting spreadsheets that nobody fully trusts: balance sheets that do not balance, hard-coded numbers inside formulas, cash that does not reconcile and valuations that cannot be traced back to their assumptions. This Core Concept course develops Advanced Financial Modelling in Excel for analysts who build these models themselves, from driver-based forecasts and linked financial statements to debt schedules, scenarios and a DCF valuation module. Participants leave with an Integrated Financial Model and Valuation Pack, built to recognised modelling standards and checked for errors, ready to support real planning and investment decisions.
Course Objectives:
- Design a model specification, timeline and sheet architecture that separates inputs, calculations and outputs to recognised modelling standards
- Build an integrated three-statement model in Excel in which the income statement, balance sheet and cash flow statement are driven from operating assumptions
- Construct working capital, fixed asset, equity and debt schedules, including interest circularity, that feed the financial statements correctly
- Run scenario and sensitivity analysis with switches and data tables to show how key drivers move cash, profit and value
- Value the business with a DCF module linked to the forecast, using free cash flow, a discount rate and a terminal value
- Test, audit and present a model with integrity checks, stress tests and an output dashboard for decision makers
Target Audience:
- Analysts who build and maintain corporate forecasting and planning models
- Financial planning and analysis staff responsible for multi-year projections
- Corporate finance and treasury staff who prepare funding and debt capacity analysis
- Investment and credit analysts who assess company forecasts and valuations
- Finance business partners who translate operating plans into financial statements
- Model reviewers responsible for checking spreadsheets before they are used for decisions
Course Outline:
Day 1: Model Scoping and Driver-Based Foundations
- Model Purpose, Specification and Scope Document
- Driver-Based Forecasting Logic: Volume, Price and Cost Drivers
- Historical Financial Statement Set-Up and Normalisation
- Timeline, Period Flags and Counters for Monthly and Annual Models
- Excel Functions for Modellers: INDEX, XLOOKUP, SUMIFS and EOMONTH
Day 2: Model Design Standards and Auditability
- FAST Standard Principles: Flexible, Appropriate, Structured, Transparent
- ICAEW Financial Modelling Code: Planning, Structure and Presentation
- Inputs, Calculations and Outputs Sheet Architecture
- Formula Consistency, Row Logic and Naming Conventions
- Colour Coding, Units Labelling and the Model Documentation Sheet
Day 3: Building the Integrated Three-Statement Model
- Revenue and Operating Cost Build from Drivers
- Working Capital Schedule: Receivable, Inventory and Payable Days
- Fixed Asset Register and Depreciation Schedule with Capex Timing
- Linking Income Statement, Balance Sheet and Cash Flow Statement
- Equity, Dividends and Retained Earnings Roll-Forward
Day 4: Debt, Scenarios, DCF Valuation and Model Integrity
- Debt Schedule: Term Loans, Revolving Facility Draws and Repayment Profiles
- Interest Circularity Handling: Circuit Breakers, Average Balances and Iteration Switches
- Scenario Switches with CHOOSE and Sensitivity Data Tables
- DCF Valuation Module: Free Cash Flow, WACC and Terminal Value
- Error Checks, Balance Sheet Integrity Tests and Stress Testing
Day 5: Modelling Build and the Integrated Financial Model and Valuation Pack
- Manufacturing Company Case: Full Integrated Model Build
- Service Business Case: Downside Scenario and Covenant Headroom Test
- Peer Model Review with a Structured Audit Checklist
- Output Dashboard: Tornado Chart, Football Field and Key Metrics Summary
- Model Handover Pack and Presentation to Decision Makers
Skills You Will Gain:
- Model Specification
- Driver-Based Forecasting
- Three-Statement Integration
- Debt Scheduling
- Circularity Management
- Scenario and Sensitivity Design
- DCF Valuation
- Model Auditing
Why Attend This Course:
- Return with an Integrated Financial Model and Valuation Pack built on realistic company cases and reviewed by peers
- Replace fragile, hard-coded spreadsheets with a model structure colleagues can follow, check and update
- Answer questions on cash, debt headroom and value under different scenarios with numbers that tie out
- Compare modelling practice with analysts from other sectors and organisations who face similar forecasting demands
Conclusion:
A financial model earns trust when its statements balance, its assumptions are visible and its outputs survive challenge. This course moves from model scoping and driver logic, through recognised design standards, to building the linked financial statements, and then to debt, scenarios, DCF valuation and integrity testing. The final day applies the full method to company cases in a modelling build, producing an Integrated Financial Model and Valuation Pack that participants can adapt for their next planning or investment decision.