5 Day Corporate Financial Modelling Course Click to print this pagePrint
You are here: / Home / Courses / Course Categories / Corporate Financial Modelling / 5 Day Corporate Financial Modelling Course

Introduction
This hands-on, application driven 5-day programme leads delegates through numerous 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.

In essence, it is a combination of the 2-day Advanced Excel Spreadsheet Techniques course and the 3-day Corporate Financial Modelling course.

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

To The Top

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.

To The Top

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:
    1. Moving around the spreadsheet & workbook
    2. Inserting & deleting rows and columns
    3. Inserting text & formulas
    4. Copying and constructing formulas
    5. Formatting cells
    6. Basic Excel functions - calling and using Excels functions
    7. Making cell addresses absolute
To The Top

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

To The Top

Course Content

Day 1

Some Useful Tips to speed up your spreadsheet skills
Identifying and Protecting data in spreadsheets
Naming Cells, Ranges & Formulas
Creating your own Formats (and the uses thereof)
Conditional Formats
  • Hands-on exercise - Creating conditional formats
Validating data and producing drop-down lists
Multiple sheet models - Working across sheets
Drilling Down
  • Using the Ctrl key
  • Using hyperlinks
Logical Tests - IF, AND, OR, Nested Ifs
  • Hands-on exercises - IF and Nested Ifs
SUMIF and COUNTIF
  • Examples of the two forms
Excels Database
  • Auto filter & advanced filter
  • Extracting one of each (unique)
  • Sorting
  • Creating subtotals
  • The effect of the SUBTOTAL function
  • Using Dfunctions - DSUM, DCOUNT, DAVERAGE
Pivot Table Reports
  • Hands-on exercise - Pivot table reports

Day 2

Lookups
  • Vlookup
  • Hlookup
  • Lookup
  • Match
  • Index
Practical exercises incorporating all of the above
Row(S) and Column(S) functions
Using "Masks"
The SUMPRODUCT function
String functions
Data Tables
  • Hands-on exercise - Creating a data table
Outlining - Collapsing rows & columns

Creating Views
Indirect Addressing
  • Hands-on exercise - Indirect addressing
Working with Arrays, Array Formulas, Array Functions, and Array Constants
  • Hands-on exercises - Array formulas
Date Functions
Consolidating data from different files
Recording macros
Assigning macros to buttons & icons
Setting up "Controls" (Drop-Down Boxes, Option buttons, Spinners)
Appendix
  • List of uses of the Ctrl key
Course summary and conclusion

Day 3

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
  • Exercise - Completing a monthly budget model
Logical tests
Multi-file models
A Sales model with links and overridden data
=SUMIF
  • Exercise - Sumif
Pivot Table Reports
Summing through the sheets
  • Exercise - Summing through sheets
Consolidating the branch files
  • Exercise - Consolidating Branch files
Converting the consolidated results to foreign currency
  • Exercise - conversion to foreign currency


Building a 20-year project model
  • Toll road project exercise 1
Using =SUM to accumulate data
Using "masks"
  • Toll road project exercise 2
Calculating Loan capital and Interest payments
  • Toll road project exercise 3 - Comparing results and calculating the loans
Contribution
  • Toll road project exercise 4 – Preparing an Income Statement
  • Toll road project exercise 5 – Preparing a Cash flow Statement and Balance Sheet

Day 4

Discounted cash flow
Discounted cash flow over unequal periods
  • 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
  • Toll road project exercise 6 – Calculating the returns and break-even points
  • Toll road project exercise 7 – Inserting hyperlinks
Creating Scenarios

Comparing Scenarios
Scenario summary
  • Toll road project exercise 8 - Creating 3 scenarios
Sensitivity analysis
Recording a Macro
Creating a sensitivity graph
  • Toll road project exercise 9 – Creating a Sensitivity table
Data tables
  • Toll road project exercise 10 – Setting up a 2-input data table


Choosing between alternatives
  • 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

Day 5

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

Optimisation Models
  • Exercise - A simple optimisation model
  • Exercise - A routing problem


Weighted average
Mean & Standard deviation
Portfolio Analysis
  • Exercise – Balancing the portfolio

Basic Forecasting
Probabilities
Monte Carlo Simulation – Basics

To The Top