Excel for Financial Modelling

Microsoft Excel is one of the most important business tools any of us will use. It’s the tool of choice for presenting and analysing data. However, even though Excel is so widely used, much of the workforce don’t know how to apply all of the fantastic tools which are available.

We provide a comprehensive range of Excel and financial modelling programmes.

Applications of financial modelling include: valuing companies, making mergers and acquisition decisions, budgeting and forecasting, performing financial feasibility, and credit analysis.

expand_more Who's it for?

Any organisation that wants to get ahead of the curve in Microsoft Excel. It’s ideal for anyone who wants to learn more about Excel for financial management and modelling, and how to develop best practices that will improve efficiency and productivity.

Eligibility

There are no formal entry requirements. It’s open to anyone who wants to progress their Excel skills.

Looking to upskill your staff?

Get in touch

Confidence in the workplace

Microsoft Excel is an important tool. Your employees will work towards being more proficient, faster, accurate, and able to make better decisions.

Enhanced analytical skills

Individuals will work towards developing strong analytical skills and be able to add value to multiple projects.

Data visualisation techniques

Understand how to tell a story by bringing insights to life. Display all company data in a visually attractive format for different audiences.

Practical case studies and exercises

We provide lots of modelling case studies to learn from. We also provide Excel files and exercises to explore Excel tools and functionality.

About the programmes

Whatever your employees understanding is of Excel, we can take them to the next level. Our Excel programmes are split out by beginner, intermediate, and advanced. This ensures that every learner receives the most appropriate training to meet their needs. Most of our programmes can be done in a day, whether in-person in your offices, or online.

Beginner

Introduction to Excel Functionality

This introductory course expands your knowledge on the day-to-day uses of Excel. Major topics include data entry tips and tricks, formatting, formulae and functions, named cells and blocks of cells, data validation, data tables, and protection of cells and worksheets.

Learning outcomes

Use keyboard shortcuts to improve speed and efficiency
Use of excel quick access toolbar and cell styling tools
Learn and apply various intermediate and advanced Excel functions
Learn and apply what if analysis and data validation tools in Excel
Produce high quality and dynamic charts for data presentations
Learn to implement a pivot table to analyse a large dataset
Learn to record and edit macros to automate daily tasks.

Key topics

Data handling – entering and editing differing data types in Excel
Useful Excel shortcuts for speed and efficiency
Formatting and efficiency skills – conditional formatting, cell styles, quick access toolbar
Absolute and relative cell referencing (use of $ signs)
Creating, editing, and deleting cell names
Working with functions – basic math, logical, date, lookup, text, and finance functions
Advanced Functions, INDEX, MATCH, OFFSET, CHOOSE, AVERAGEIF, MEDIANIF, MAXIF, MINIF, and more
Data validation and its various applications
What If analysis tools: Data table (sensitivity analysis), Goal Seek and Scenario Manager
Data analysis and mining: Working with large data sets (sorting, subtotals, filtering)
Charting in Excel: creating dynamic and flexible charts
Pivot tables and pivot charts
Developer tool: VBA drop-down lists, checkboxes, and more
Auditing and excel analysis file/model: Auditing tools: F5 (special)
Recording macros in excel and editing using basic VBA tools

Financial Statement Analysis using Excel

Learning outcomes

Learn and apply Excel tools useful in financial statement analysis
Analyse income statements and its key components
Analyse balance sheets and its key elements
Analyse cash flow statements and its key elements
Understand the link between the three financial statements
Perform ratio analysis and assess a company's KPI’s (key performance indicators)

Key topics

Excel tools used in financial statement analysis
Introduction to the three statements: Income Statement, Balance Sheet and Cash Flow Statement
Understanding the accounting equation (A = L + E) and how transactions in a business affects it
Analysing different components of income statements
Understand implications of Capitalising vs. Expensing
Analysing different parts of a balance sheet
Analysing a cash flow statement
Preparing cash flow statements using the direct and in direct method
Financial statement analysis - common size analysis
Financial statement analysis - ratio analysis

Introduction to Financial Modelling with Excel

You'll cover the principles and practice of financial modelling, focusing on a case study involving the construction of an integrated set of financial statements, and subsequent investment appraisal.

Learning outcomes

Learn and apply Excel tools useful in financial forecasting
Understand and design the layout of a flexible model
Forecast financial statements of a public or private company
Apply scenario analysis to the forecasted financial statements
Prepare charts for data presentation

Key topics

Overview of financial modelling
Excel tools used in financial modelling
Model design and structure for the case study company
Know the key model outputs and their drivers and understand the requisite inputs
Developing cell styles for model transparency and review
Incorporating historical financial statements of the subject company
Building balance sheet error checks in the model
Forecasting financial statements: forecasting revenue and key operating expenses
Projecting key schedules: debt and interest, capex and depreciation, and working capital
Stepwise projection of income state

Intermediate

Corporate Valuation using Excel

Whether the objective is to value a company for an investor or for a merger and acquisition transaction, the programme covers all aspects of valuation using real-life case studies and valuation models.

Learning outcomes

Understand the drivers of corporate value: cash, growth, and risk
Understand components of a company's enterprise value
Calculate equity value from enterprise value (EV) and vice versa
Calculate a company's weighted average cost of capital (WACC) and its components
Learn and apply discounted cash flow valuation tools
Learn and utilise multiples-based valuation tools
Perform scenario and sensitivity analysis for valuation outputs

Key topics

Introduction to corporate valuation - difference between enterprise value and equity value
Understanding and calculating enterprise value and its components

DCF Valuation

Understanding FCFF and FCFE
Choice of forecast horizon and forecasting key drivers of free cash flows
Understanding the key inputs and calculating WACC
Calculating and performing sanity checks for terminal value – using growth rate and multiple methods
Calculating equity value from enterprise value: EV to equity bridge

Multiples based valuation

Different types of valuation multiples: P/E, P/B, P/S, EV/EBITDA, EV/Sales
Understanding what drives the valuation multiples – cash, growth and risk analysis
Selecting the right set of multiples to value the subject company – price based or EV based
Selecting the right set of peer groups to value the subject company
Cleaning and normalising the operating metrics (EPS, EBITDA) for the comparable universe
Using prior transaction multiples

Three Statement Modelling and Valuation

The objective of this programme is to equip learners with the knowledge and tools to develop a fully integrated three statement financial model from scratch and perform equity valuation.

Learning outcomes

Understand how to plan a model's design and structure
Forecast financial statements
Develop consistently and review friendly models
Understand how to perform a flexible DCF valuation
Perform scenario and sensitivity analysis

Key topics

Introduction to corporate valuation - difference between enterprise value and equity value
Understanding and calculating enterprise value and its components

DCF Valuation

Overview of financial modelling
Key Excel tools used in financial modelling
Model design and structure for the case study company
Know the key model outputs and their drivers and understand the requisite inputs
Developing and using cell styles for model transparency and review
Extracting, cleaning and normalising historical financial statements from annual reports
Building error checks in the model
Forecasting financial statements: revenue modelling and forecasting margins
Projecting key schedules: debt and interest, capex and depreciation, and working capital
Stepwise projection of an income statement, balance sheet, and cash flows
Understanding and forecasting Free Cash Flows to the firm (FCFF)
Understanding the key inputs and calculating WACC
Calculating and performing sanity checks for the terminal value
Developing DCF Valuation
Performing sensitivity analysis using data tables
Scenario analysis

Advanced

Mergers and Acquisitions, and Leveraged Buyout Modelling

We'll equip learners with knowledge and tools to develop financial models. We aim for learners to efficiently analyse a merger and acquisition, private equity and leveraged buyout transaction.

Learning outcomes

Understand and develop a merger model
Prepare proforma group financial statements
Perform financing mix and accretion dilution analysis
Understand and develop an LBO deal analysis model
Prepare flexible debt schedules for different sources of debt
Understand and prepare a source of value creation in private equity transactions

Key topics

Mergers and Acquisitions (MA) Modelling

Setting up an MA Model – calculating sources and uses of funds
Preparing pro forma financial statements – MA accounting
Key MA model output: EPS accretion dilution analysis, contribution analysis
Understanding of post transaction - P/E requirement for breakeven
Sensitivity analysis using data tables

Leveraged Buyout Modelling (LBO) Modelling

Understanding characteristics of a good LBO candidate
Forecasting the operating cash flows of the LBO target
Target’s enterprise value and uses of funds
Various sources of funds: senior debt (term loan A, B), High yield, mezzanine, PIK, shareholder loan and revolving line of credit
Preparing flexible debt and interest repayment schedules for different sources of funding
Incorporating cash sweep in debt schedules and avoiding circularity
Calculating exit IRR and MOIC for the GP and analysing the sources of value creation
Scenario and sensitivity analysis for analysing LBO

Advanced Financial Modelling

The objective of the programme is to help learners develop best practices to build flexible and auditable financial models and improve productivity.

Learning outcomes

Apply advanced Excel tools and functions in financial models
Develop fully flexible forecasts using switches
Consolidate monthly or quarterly forecasts to annual forecasts
Model timing flags for use in project finance and debt models
Decode and audit existing financial models
Calculate advanced metrics to analyse past investment performance
Create and edit macros using VBA tools
Create dynamic dashboards for model presentation

Key topics

Advanced Excel tools and functions useful in financial modelling
Creating flexible forecasting models incorporating detailed scenario analysis using switches
Consolidating monthly forecasts to quarterly, and quarterly forecasts to annual
Using timing flags to create flexible debt repayment and project analysis schedules
Advanced debt modelling incorporating revolver, cash sweep and different tranches of debt
Financial model auditing tools – decode and understand any existing financial model
Analysing large volume stock price or fund performance data for investment decision making
Recording and editing macros using basic VBA tools
Dynamic dashboards and charting to present financial models

Bank Modelling and Valuation

Learners will gain the knowledge and tools to forecast a bank's financial statements and perform equity valuation from scratch.

Learning outcomes

Learn and apply advanced excel tools used in bank modelling
Understand how to plan the bank model design and structure
Understand a bank's financial statements and interaction between its key assets and liabilities
Forecast a bank's financial statements
Develop audit and review friendly financial models
Value a bank using cash flows and comps based valuation tools
Perform scenario and sensitivity analysis to the valuation

Key topics

Overview of bank financial modelling
Key Excel tools used in bank financial modelling
Model design and structure for the case study bank
Know the key model outputs and their drivers and the requisite inputs
Developing cell styles for model transparency and review
Extracting, cleaning and normalising historical financial statements of a bank
Building error checks in the model
Forecasting a bank's financial statements
Forecasting regulatory tier 1 and tier 2 capital requirement for a bank
The model balancer of the bank's balance sheet
Analysing a bank's forecasted financial statements and performing ratio analysis
Bank valuation – discounted cash flows and trading comps
Calculating a bank's cost of equity and using the right CAPM inputs
Estimating terminal value – growth rate and multiple methods
Performing scenario and sensitivity analysis to DCF valuation outputs
Presenting the model outputs using high quality and flexible charts and creating a management dashboard

Why partner with Kaplan?

check

Expert teaching

Our tutors are all subject matter experts, with real world experience, and teach small cohort groups of like minded learners.

check

Practical learning

We provide practical training on Excel’s features and functions. Our learners go back into the workplace confident and ready to apply what they’ve learned.

25+ years

Over 25 years teaching Excel.

70+ of FTSE 100

72 of the top FTSE 100 companies have used Kaplan for training.

Don’t just take our word for it

Great theory and descriptive hand-outs to support the different exercises. The instructor was amazing, so interactive and kept the audience engaged - only wished we had more time and more content to cover!