Financial Modelling
Modelling provides valuable insights into the behaviour and performance of the organisation being modelled. A financial model is a representation of the present and future states of an organisation. The 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.
Brightstar’s popular and comprehensive Financial Modelling course shows how modelling is done and how models are used. It also covers the finance, accounting, spreadsheet and design skills that underlie good modelling practice.
Financial Modelling contains a high level of practical work, backed up by lecture and discussion, to provide the most valuable and intensive learning experience possible. All participants will be provided with their own laptop and software for use in practical exercises. Participants should have at least an intermediate level of experience with Excel.
Gain up to 14 CPD hours
Key Learning Outcomes
- Learn the objectives, principles and methods of financial modelling
- Gain expertise in spreadsheet functions and tools used to build efficient and well-presented financial models
- Discover techniques to make models easily extended, maintained and adapted
- Develop sophisticated “what-if” and scenario analyses
- Build clearly understandable, “bullet-proof” and user-friendly models
Past Participant Testimonials:
“The course was more useful than I predicted. Alex was very patient and went through the content at a pace I was able to follow’ I liked that we had a chance to attempt exercises ourselves.”
Jaya Ramanjam, Assistant Accountant, The University of Auckland
“Very practical, example/ scenario based. Lots of practice. Helped me join some dots together. I personally enjoyed Alex’s style of teaching in a technical course/ training like this. Clear, logical and very detailed. Content coverage is what I’m after as well. Enough practice and lots to take away for own study and future reference.”
Rong Wilks, Management Accountant, City Care Ltd
“This is an excellent training session for developing excel skills and displaying your data for external/ internal stakeholders in more meaningful manner.”
Angel Karan, Financial Administrator, The University of Auckland
Introduction to financial modelling objectives and outcomes
- The true breadth and scope of financial modelling
- The process of financial modelling
- How the financial system we are modelling responds to its drivers
- Key metrics and how they feed into the decision-making process
Generating key metrics and performance indicators
- Quantifying key metrics such as value and risk
- Understanding the business being modelled
- Understanding drivers, constraints, inputs and outputs
- How are key metrics derived?
Financial modelling as a valuable management tool
- Reporting on how realised key metrics compared with modelled metrics
- What caused variances?
- What “trajectories” are key measures taking?
- Using financial models for scenario, sensitivity, attribution, and what-if analysis
- Risk assessment: gaining a sense of risk exposure
Finance concepts and financial functions
- Applying finance principles and functions
- Key concepts: Present and future value, discount & hurdel rates, cost of capital
- Spreadsheet financial functions: NPV, XNPV, IRR, XIRR, NOMINAL
Spreadsheet functions, tools and features used in financial modelling
This section of the course presents a broad scope of spreadsheet applications, some are simple and others are more complex. Their purpose is to illustrate many of the capabilities – some of them not well known – of spreadsheets. Almost all of the examples are interactive.
- Logical, referencing and aggregation functions.
- Arithmetic and ranking functions
- Error handling functions
- Date functions
- Auditing tools
- Custom formats
- Charts and templates
- 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
- Income statement
- Cash flow statement
- Finance concepts
Design and construction of models
- 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
Advanced Spreadsheet functions and features
- Arrays
- Form elements and controls
- Checkbox, drop down, listbox
- Conditional formatting
- Custom formats
Goal-Seeking and the Solver
- Iteration
- Goal seek
- Solver
- Optimisation
Financial modelling objectives and outcomes
- Financial models give key insights and support for analysis, reporting, decision-making and management. Those are some of the objectives and outcomes of modelling.
- Generating metrics and performance indicators
- What-if analysis
- Decision-making
- Management and reporting
Financial modelling design and implementation
- The stages in designing and building financial models are covered and practised.
- Determining requirements
- Plan
- Design
- Build
- Validate
- Document and release
Best-practice principles
- Best-practice principles lead to models that are easy to adapt, user-friendly, efficient and feature-rich.
- Structure – Logical flow of information, modularity, separation of data and calculations, consistency and error detection
- Conventions and style
- Presentation – User interface and dashboards
- Formulae – consistency and choosing best from the range available
- Finance and accounting concepts and spreadsheet functions
- Financial modelling blends finance, accounting and spreadsheet disciplines. The following are covered and applied.
- Cost of capital, discount rates, present and future value
- Internal rate of return and hurdle rates
- Financial statements: Balance sheet, profit and loss and cash flow
- Spreadsheet functions supporting the above
Alex Palfi
Facilitator, Finance
Alex is an experienced Australian-based trainer specialising in technical finance and business topics.
Previously, Alex worked as 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.
