Certificate in Financial Modelling
Introduction
The course will incorporate different approaches to modelling structured financing, where the volatility of the cash flow (as opposed to its value) is the key to determine the serviceability of a stressed financing structure. The programs aims to teach delegates the complexities of handling multiple tranches of finance with flexible input variables and the associated interest calculations.
Key business benefits
- Building a cash flow and financial statement forecast model for companies, ensuring that all the drivers are appropriate, that the modeling logic and the accounting treatments are sound, and that modeling best practice is applied;
- Understand how the key input drivers are modeled to both revenues and costs including multiple currencies, inflation and oil price movements;
- Compare different approaches to modeling structured financings, where the volatility of the cash flow (as opposed to its value) is the key to determine serviceability of a stressed financing structure;
- Gain insights about the design and implementation of scenarios, as well as the sensitivity and break-even analysis;
- Practice handling multiple tranches of finance with flexible input variables and the associated interest calculations.
Skills Acquired
- Develop practical solutions to your business problems through superior spreadsheet design;
- Consolidate worksheets from different sources into one workbook;
- Develop efficient techniques for using charts and maps;
- Use spreadsheet skills for advanced financial applications;
- Analyze data accurately using pivot tables.
Learning objectives
- Know the different types of financial models and when each should be applied;
- Be able to design financial models;
- Be able to develop models for their own use, incorporating elements such as risk, sensitivity, optimisation and forecasting;
- Discover hidden short cuts to dramatically reduce the time you spend on spreadsheets;
- Learn how to format quickly and accurately;
- Develop practical solutions to your business problems through superior spreadsheet design;
- Building a cash flow and financial statement forecast model for companies, ensuring that all the drivers are appropriate, that the modeling logic and the accounting treatments are sound, and that modeling best practice is applied;
- Understand how the key input drivers are modeled to both revenues and costs including multiple currencies, inflation and oil price movements;
- Compare different approaches to modeling structured financings, where the volatility of the cash flow (as opposed to its value) is the key to determine serviceability of a stressed financing structure;
- Gain insights about the design and implementation of scenarios, as well as the sensitivity and break-even analysis;
- Practice handling multiple tranches of finance with flexible input variables and the associated interest calculations.
Day 1
Session 1: Model Design
- Identifying the purpose and mode of use of the model;
- Designing the analysis worksheet;
- Determining the flexibilities required and the variable inputs;
- Best practice issues;
- Circular references.
Session 2: Excel Conventions
- Naming Cells;
- OFFSET and MATCH;
- IF, AND, OR Functions;
- MOD Function;
- SUMPRODUCT/SUM combination;
- Dynamic Ranges;
- Dates and timing - the framework for a model.
Session 3: Revenue Analysis and Projection
- Historical analysis of revenue using various methods;
- Differences in revenue expectation based on the arithmetic & geometric mean;
- Integrating revenue into the model;
- Managing growth rate scenarios;
- Compound annual growth rate;
- Implementing a scenario selection system.
Session 4: Asset Assumptions
- Current Assets: cash, accounts receivable, and inventory;
- Projecting receivables and inventory;
- Capital expenditures: setting up a schedule for CAPEX;
- Property, plant, and equipment and depreciation: methods and implementation;
- Depreciation functions and methodology;
- Intangible amortization.
Session 5: Liability Assumptions
- Current Liabilities: accounts payable, other current liabilities;
- Projecting payables;
- Debt: setting up a system for amortization;
- Calculate cash available for interest and debt amortisation;
- Build out the debt schedule for the various types of debt, including a revolver;
- Link the closing debt and cash balances back into the BS;
- Calculate the final interest payment;
- Revolvers and liquidity facilities;
- Calculating tax and integrating taxes payable and deferred taxatio.
Session 6: Working Capital and Cash Flow
- The balance sheet and income statement interaction to produce the cash flow statement;
- Understanding working capital in the model;
- Changing assumptions and the effect on working capital;
- Free cash flow.
Session 7: Capital Structuring Applications
- Setting up a model for capital structure analysis;
- Optimal debt and equity;
- The effects of optimal capital structure in the valuation model.
Day 2:
Session 8: Checks and Reporting
- Internal tests to ensure model integrity;
- Appropriate outputs for a finance model;
- Controlling the output system;
- Implementing automated output reporting.
Session 9: Major Case Study
- Participants will be provided with input data and some assumptions and will be required to develop a robust, fully integrated model.
Session 10: Advanced Techniques
- Data base techniques;
- Goal seek, Optimization, Scenario analysis;
- Audit requirements in financial modeling;
- Identifying and controlling key sensitivities;
- Building risk factors in models to enhance decision making.
Session 11: Sensitivity Analysis
- Purpose of sensitivity analysis;
- Establishing a suite of sensitivities;
- Combined downside 'worst' case;
- Minimizing run-time errors;
- Identifying sensitivity printout;
- Automating sensitivity runs using an input table;
- Cross checking against base case.
Session 12: Step-By-Step Process is applied for Financial Model Audit
- Low level review – model characteristics, ranges and range names, formulae, documentation trail;
- High level review – consistency with supporting documentation, commercial matters, balancing;
- Sensitivities – running the model with key variables which will be changed singly or in combination.
Session 13: Excel Add-In for Auditing
- Review of add-in tools.
Session 14: Case Study
- A financial model will be provided with numerous errors as well as non-compliance aspects in terms of FAST.
Session 15: Summarizing Data Using Pivot Tables
- What are pivot tables?
- Creating pivot tables;
- Grouping data in pivot tables;
- Refresh pivot tables;
- Selecting elements of a pivot table;
- Making use of multiple data fields;
- Using grand totals and subtotals;
- Changing a pivot table’s calculations.
Andre Lanser
Credit, Corporate Finance and Financial Modelling
Andre Lanser is a consulting trainer as well as a corporate finance practitioner and he has delivered courses around the world in the areas of Credit, Corporate Finance, Valuation and Financial Modelling both for Corporates and Banks.
Andre originally qualified as a Chartered Accountant, completing articles with PricewaterhouseCoopers. He started his career with a subsidiary of Commercial Union, involved in the steel manufacturing industry.
His experience ranged from systems implementation, credit assessment of the highly risky construction industry customers, heading up the finance functions to successfully turning the business around and then selling the company through a trade sale.
Later he joined Commercial Union in the investment back-office function, reengineering the investment back office and later headed-up a team who implemented a new investment management system for both the back office and the front office.
His commercial experience includes restructuring and turning around a Steel Manufacturing operation, consulting to a Pharmaceutical company involved in medical devices for both Government and Private Hospitals and an in depth knowledge of the citrus industry, consulting to South African Airways and extensive work at Hanover Acceptances property company, Dorrington in London.
His career then spanned over 15 years in the Venture Capital and Private Equity arena, where he has gained a vast amount of experience in the corporate analysis and valuation field.
He is a specialist in developing and training finance related courses, including corporate credit related topics, credit analysis and cash flow analysis with a special focus on the banking sector, derivatives, and financial modeling courses. His training experience spans a period of more than 15 years
Online Purchasing Instructions
Choose a product and add it to the cart
Add more products or proceed to checkout
Fill in all the information and finalize the order
Check your email for confirmation and details