3 Day Corporate Financial Modelling Course

This hands-on, application driven 3-day programme leads delegates through selected advanced Excel functions and procedures pertinent to the construction of financial models, explaining the function or procedure and their uses.

It then moves on to cover good model structure, both for single and multi-file models, and ways of maintaining the integrity of the model using the features provided by Excel.

Delegates are introduced to a number of functions and techniques essential to the preparation of sophisticated financial models, such as logical tests, lookup tables, discounted cash flow over equal and unequal periods, internal rates of return, free cash flow, loan repayments, and "switches".

The program covers what should be done once the model is complete, e.g. sensitivity analysis, data tables and the creation and comparison of scenarios.

Finally it touches on regression analysis, random numbers, probabilities and standard deviation as a means to aid forecasting.

Teaching method
Delegates are guided through the course by a highly-experienced course director.

Once a certain principle, technique, or function has been worked through in class, the delegates tackle a practical exercise pertinent thereto. The course is non-threatening and relaxed, the course director always being on hand to help and guide where necessary.

Delegates are provided with a comprehensive set of notes designed to be used as a reference manual after the course, as well as a CD containing all the worked and unworked examples referred to in the notes, and all the exercises and solutions for the work done in class

Who Should Attend

This course has been designed for:

Budget & planning officers
Financial controllers and managers
Financial & business analysts
Accountants
Corporate treasury managers
General managers

NOTE: Delegates need to have a working knowledge of Excel, and are encouraged to bring their own laptops for the computer based examples and exercises. It is essential that the add-ins "Analysis Toolpak" and "Solver Add-in" have been loaded.

Pre Requisite(s)

Delegates should be familiar with Microsoft Excel (though not necessarily expert in the use thereof, as any relevant Excel function pertinent to an exercise will be discussed prior to tackling the exercise).
Excel knowledge required:
Basic & Intermediate Excel skills, including:

Moving around the spreadsheet & workbook
Inserting & deleting rows and columns
Inserting text & formulas
Copying and constructing formulas
Formatting cells
Basic Excel functions - calling and using Excels functions
Making cell addresses absolute

Outcomes

On completion of this practical, hands-on training course, delegates will:

Be able to build well-structured, flexible financial models
Be able to apply many of the advanced functions and techniques available in Excel
Understand, create, and use scenarios, data tables, sensitivity tables, goal-seeking, etc.
Be able to apply what they've learnt in the calculation of Wacc, NPV, IRR, loans, free cash flow and break-even points
Be able to apply Excel's Solver in the preparation of optimisation models
Have a basic understanding of regression analysis and Monte-Carlo simulation
Have at their fingertips a comprehensive reference manual and worked examples relating to the building of financial models

Course Content

Types of financial models
Example of a deterministic model
Model Structure
The integrity of the model
Data validation techniques
Hyperlinks
Extracting positive and negative balances
Using the "Offset" function
    o Exercise - Completing a monthly budget model
Logical tests
Multi-file models
A Sales model with links and overridden data
=SUMIF
    o Exercise - Sumif
Pivot Table Reports
Summing through the sheets
    o Exercise - Summing through sheets
Consolidating the branch files
    o Exercise - Consolidating Branch files
Converting the consolidated results to foreign currency
    o Exercise - conversion to foreign currency

Building a 20-year project model
    o Toll road project exercise 1
Using =SUM to accumulate data
Using "masks"
    o Toll road project exercise 2
Calculating Loan capital and Interest payments
    o Toll road project exercise 3 - Comparing results and calculating the loans
Contribution
    o Toll road project exercise 4 - Preparing an Income Statement
    o Toll road project exercise 5 - Preparing a Cash flow Statement and Balance Sheet
Discounted cash flow
Discounted cash flow over unequal periods
    o Exercise - Which project produces the best yield?
Free Cash Flow
Measuring the return on investment
Break-even point of a project over a number of years
Match
Index & Lookup
    o Toll road project exercise 6 - Calculating the returns and break-even points
    o Toll road project exercise 7 - Inserting hyperlinks
Creating Scenarios
Comparing Scenarios
Scenario summary
    o Toll road project exercise 8 - Creating 3 scenarios
Sensitivity analysis
Recording a Macro
Creating a sensitivity graph
    o Toll road project exercise 9 - Creating a Sensitivity table
Data tables
    o Toll road project exercise 10 - Setting up a 2-input data table
Choosing between alternatives
    o Optional exercise - Lease or Buy - Anticipating change
Balance sheet terminology
Financial Ratios
The Weighted Average Cost of Capital (WACC)
The Weighted Average Cost of Capital (WACC)
Force-balancing the Balance Sheet

Valuing Companies
Free cash flow & the valuation of companies
Valuing a company using free cash flow
o Exercise - Using free cash flow to value a company

Optimisation Models
    o Exercise - A simple optimisation model
    o Exercise - A routing problem
Weighted average
Mean & Standard deviation
Portfolio Analysis
    o Exercise - Balancing the portfolio

Basic Forecasting
Probabilities
Monte Carlo Simulation - Basics