We've noticed this is not your region.
Redirect me to my region
What do you want to learn today?

Details

Certificate in Financial Modelling
(In-house Course)

Improved Skills
Improved Performance

We support professionals and organisations with business skills development through hands-on public and in-house training courses.

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 shortcuts 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.
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.

Outline

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:
    • Maintaining a log
    • Status worksheet
  • Hard coding, SE flow, consistent formulae
  • Naming conventions, formatting conventions
  • Consistent timelines, flexibility to change timelines
  • Circular references
    • Which are permissible and which not
    • Workaround if the model needs to be audited
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:
  • Key drivers
    • Inflation: nominal versus real
    • 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 amortization
  • 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 taxation
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
  • Database 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
Tools and Features within Excel which can be applied to Auditing a Financial Model

Session 12

  • Step-By-Step Process is applied for Financial Model Audit
    • Low level review – model characteristics, ranges and range names, formulae, documentation trail
  • Step-By-Step Process is applied for Financial Model Audit
    • 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, which can provide the following:
    • View big picture with a comprehensive workbook report, data flow analysis, and error detection
    • Show how formulas have been copied throughout a workbook
    • Follow complex Workbook ‘Precedent’ and ‘Dependent’ relationships
    • Compare different versions of a spreadsheet
    • Perform advanced inter-worksheet data flow analysis for complex models
    • Highlight bad sensitivity relationships
    • Assist with the understanding and manipulation of “Named Ranges”
Session 14
Case Study
  • A financial model will be provided with numerous errors as well as non-compliance aspects in terms of FAST. The participants will be required to:
    • Identify errors,
    • Find areas which are not in compliance with FAST
    • Make recommendations for improvement
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

Speaker/s

Andre Lanser
CREDIT, CORPORATE FINANCE AND FINANCIAL MODELLING

Andre Lanser has delivered courses around the world in the areas of Credit, Corporate Finance, Valuation and Financial Modelling both for Corporate and Banks. Andre Originally qualified as a Chartered Accountant, completing articles with Price Waterhouse Coopers. 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. 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.

Special Offer

Organize this training course in-house
If you have a group of five or more to train you can save time and money by running this
training course in-house. Use the contact details provided to request a customized offer from one of our training solution specialists.
Reviews
Be the first to write a review about this course.
Write a Review

TKI Skills Academy offers progressive training programs focused on enhancing personal and professional growth. Our courses, developed at global standards, are adapted to the market’s needs and professionals’ experiential learning expectations.

We created the Academy to help organizations reach peak performance through better-skilled professionals operating more mature organisational capabilities. Skills and overall business acumen such as effective communication, data analysis, emotional intelligence, risk management, and innovationare a proven ingredients of professional and organisational success.

Sending Message
Please wait...
× × Speedycourse.com uses cookies to deliver our services. By continuing to use the site, you are agreeing to our use of cookies, Privacy Policy, and our Terms & Conditions.