Agenda

• Review and practice using the spreadsheet functions and tools that are the building blocks of efficient and well-presented financial models

• Cover the underlying finance and accounting concepts that underlie financial modelling

• Learn the objectives, principles and methods of financial modelling
• Design and construct a set of models that illustrate and apply the techniques of financial modelling



Strictly limited to 20 participants per venue – get in quick to secure your spot!



A financial model is a representation of the present and future states of a financial entity. A model is a dynamic creation and its outcomes are all action-related: Deciding, reporting, analysing, valuing - these are just some of the outcomes of models.

Modelling provides valuable insights into the behaviour and performance of the entity being modelled. A model can be vital repository of the data, assumptions (and justifications for those assumptions) inter-relationships and dynamics of a financial entity. 

Financial modelling requires accounting, finance and spreadsheet skills. Design, presentation and communication skills are also important.

This course shows how modelling is done and how models are used. It also shows the finance, accounting, spreadsheet and design skills that underlie good modelling practice.



Your expert international trainer is Alex Palfi is Principal of Tykoh Group Pty Limited. 
Prior to this Alex was a Division Director at Macquarie Group in Sydney.  In that role he developed a range of finance workshops both for Macquarie and non-Macquarie people and presented those workshops internationally in London, New York, Tokyo, Los Angeles, Korea, Hong Kong, Singapore, Taipei, Johannesburg, Bangkok, Sao Paulo, Sydney, Melbourne, Auckland and Wellington.

Those workshops focused on financial modelling, financial spreadsheets, valuation, derivatives, credit risk and Visual Basic programming.

As numbers for these courses are strictly limited to 20 participants, places will be allocated on a first come first served basis.

Outline

Excel functions and tools that are useful in modelling
A broad range of Excel functions and tools are useful in modelling. These are reviewed in this section of the course. Later sections then apply those functions and tools in practical settings.

Logical, referencing and aggregation functions.
•    IF, AND and OR
•    LOOKUP, VLOOKUP, HLOOKUP, MATCH
•    OFFSET, CHOOSE, INDIRECT
•    SUMPRODUCT, SUMIF, COUNTIF

Arithmetic functions
•    MAX
•    MIN
•    ABS
•    ROUND

Ranking functions
•    SMALL and LARGE

Error handling functions
•    ISNA, ISERR and ISERROR

Date functions
•    DATE
•    MONTH
•    YEAR
•    EOMONTH

Financial functions
•    NPV & XNPV
•    IRR & XIRR
•    PMT, PV, FV, RATE and NPER
•    PPMT and IPMT

Iteration
•    Goal-seek
•    Using the solver
•    Iteration

Presentation
•    Freezing panes
•    Hiding columns
•    Protecting cells, worksheets and workbooks

Other tools and features
•    Named ranges
•    Arrays
•    Data tables
•    Joining text and strings
•    Conditional formatting
•  Auditing tools
•    Custom formats
• Charts and templates
•  Adding form elements: Checkbox, option button, combo box
• Keyboard shortcuts

Accounting and finance concepts that underlie modelling
Financial modelling builds on accounting and finance concepts. This section reviews the accounting and finance theory that is needed for effective financial modelling.

Balance Sheet
•  Assets
•  Liabilities
• Distinction between current and non-current
• Shareholders equity
• Share capital and retained earnings   

Income statement
• Revenue, expenses and profit
• Interest, tax and depreciation
• EDITDA
• EBIT
• EBT and NPAT

Cash flow statement
• Operations
• Financing
• Investment

Inter-relationship between the statements
• Income statement
• Balance sheet
• Cash flow statement

Finance concepts
• Discount rate
• Cost of debt
• Cost of equity
• Weighted average cost of capital
• Free cash flow to equity
• Free cash flow to the firm
• Market vs book value
• FX rate
• Inflation rate, real and nominal values
• IRR, rolling IRR, hurdle rate

An overview of the modelling process
This section reviews the objectives, principles and methods of financial modelling
• Objectives of models
• Design principles
• Layout, structure, formatting and presentation of a good model
• Key design stages

Financial modelling: Exercises and applications
This section covers practical exercises in constructing and applying financial models. Topics covered include the following
• Structuring the model.
• Logical organisation of tabs and worksheets.
• Determining appropriate periodicity: e.g. Aligning with calculation dates of debt covenants, dealing with seasonal effects
• Dealing with one-off / irregular or short-term events
• Prioritising and apportioning claims on cash flows: e.g. Debt service, CAPEX, dividend payments
•  Planning for sensitivity / scenarios / stress-test analysis.
• Generating summary sheets : High and low level model results
• Model consistency checking and reporting
• Valuation
• Calculation of ratios
• Model versioning, documenting and testing

Facilitator

Alex Palfi, Principal, Tykoh Group Pty Limited

Alex Palfi is Principal of Tykoh Group Pty Limited - an Australian-based training provider specialising in tailored one and two day workshops on technical finance and business topics.

Prior to setting up Tykoh in January 2009 Alex was a Division Director at Macquarie Group in Sydney.  In that role he developed a range of finance workshops both for Macquarie and non-Macquarie people and presented those workshops internationally in London, New York, Tokyo, Los Angeles, Korea, Hong Kong, Singapore, Taipei, Johannesburg, Bangkok, Sao Paulo, Sydney, Melbourne, Auckland and Wellington.  Those workshops focused on Financial modelling, financial spreadsheets, valuation, derivatives, credit risk and Visual Basic programming.

Prior to joining Macquarie in 1994 Alex worked for a number of financial services organisations as a financial software developer and before that lectured at Technical Institutes in New Zealand in programming and electrical engineering.

Alex's university qualifications are in engineering.  He obtained Masters and Bachelors degrees in Electrical Engineering from the University of Canterbury in New Zealand.

Alex Palfi is also facilitating:

• Advanced Spreadsheet Skills for Finance
• Visual Basic for Financial Applications

In-house Training