Available dates

Nov 21—22, 2019
2 days
New York, New York, United States
USD 1495
USD 747 per day
Nov 21—22, 2019
2 days
London, United Kingdom
GBP 1295 ≈USD 1668
GBP 647 per day
Dec 18—19, 2019
2 days
Dubai, United Arab Emirates
GBP 1295 ≈USD 1668
GBP 647 per day

Disclaimer

Coursalytics is an independent platform to find, compare, and book executive courses. Coursalytics is not endorsed by, sponsored by, or otherwise affiliated with Fitch Learning.

Full disclaimer.

About the course

This course will develop the best practice approaches to structuring fully flexible and auditable models whilst cultivating the most efficient data analysis tools and solve other common modeling problems.

Key Learning Outcomes:

  • Harness Excel’s tools within a best practice framework
  • Add flexibility to their models through the use of switches and flexible lookups
  • Work efficiently with large data volumes
  • Model debt effectively
  • Approach modeling for tax, debt, pensions and disposals with confidence
  • Build flexible charts and sensitivity analysis to aid the presentation of results

Content

Day One

Flexible Forecasting

Forecasting is uncertain; however, we can use Excel’s functionality to assist and make the forecasts as flexible and easily updated as possible.

  • Using Excel’s forecasting tools
  • Identifying trends
  • Using maths and modeling best practice to create flexible fade formulae

Advanced Scenario Management and Data Retrieval

We often need to give more than one set of assumptions to drive a model. There are many ways to add these different scenarios, but which is the most flexible in different situations. A number of approaches will be introduced, together with many of Excel’s powerful data retrieval functions, to identify the best possibilities.

  • The Lookup School compared – Choose vs. Vlookup vs. Hlookup vs. Index vs. Offset. The advantages and disadvantages of each
  • Uses of Match
  • Types of switch. Data validation lists versus VBA forms
  • Building a fully flexible output sheet – select the scenarios and required output(s) and the model does the rest

Debt Modeling and Structuring

Where does the cash flow and in who gets it first when a business has a number of different tranches of debt? The topic is an excellent application of using Excel for problem solving. We focus on the logic of the flows of debt modeling and how to apply a consistent approach, whilst keeping the formulas short and avoiding circularity.

Finding the debt capacity and optimising the debt structure

Debt amortisation schedules

  • Switching between payback profiles
  • Cash vs non-cash interest
  • Cash waterfall

Planning to avoid circularity

Revolver, minimum cash balances and cash sweeps

Modeling Taxes, NOLs and Deferred Taxes

The logic flows of tax modeling are interesting. Being able to structure a model around these is an excellent application of best practice financial modeling.

Calculation of taxable profit

Carry forward of losses, use of loss relief and derivation of deferred tax

Accounting for taxes

  • Cash vs accruals
  • Balance sheet impacts
  • Deferred tax movements

Day Two

Quarterly Modeling and Period Consolidation

Excel has a large number of date functions which we harness when data is required monthly and/or quarterly and/or annual. If the forecasts are monthly and we need to consolidate to annual numbers or vice-versa, how do we most efficiently do that?

  • Eomonth, Edate, etc. for date functionality
  • Changing the start/transaction date; changing the length of forecast periods
  • Consolidating monthlies into quarterlies and annuals

Auditing

A common request: how do I efficiently review someone else’s model? This session looks at the various auditing techniques whilst also using a logical step by step approach to model review

What to look out for on opening

  • Finding and killing circularity
  • Finding and killing links

Troubleshooting

Auditing tools

  • F2 v Ctrl-[ v Auditing toolbar
  • AL M P
  • F5-Special
  • Use of the camera

Large Volume Data Analysis

Data can come from many sources but is rarely in the form required for easy analysis. This session looks at quickly extracting data from pdf documents and how to best clean up and manipulate large data sets.

  • Extracting data from a pdf
  • Quick and flexible fixes for dirty data
  • Extracting key data points – use of wildcards
  • Text functions – LEN, RIGHT, FIND, etc
  • SUMIF and its variants
  • Array formulae

Sensitivity Analysis

One- and two-dimensional data tables are used to analyse the sensitivity of key inputs to the model outputs. This can be tricky and so we explore the best practice approaches to creating and updating these useful analytical tools.

Basic data tables – one- and two-dimensional Self-centring data tables

  • Ensure tables automatically coincide with model drivers and outputs

Dynamic Charting

A picture paints a thousand words, so let’s do that in Excel. Having the skills to build and manipulate the appropriate charts can add significant clarity to a model’s outputs.

Quick fixes using charts

Making charts dynamic for

  • Changes in input source
  • Changes in start and end dates
  • Flexible titles and labels
  • Dynamic value bridges

Sensitivity Analysis

Use of the camera

Basic data tables – one and two dimensional

Self-centring data tables

  • Ensure tables automatically coincide with model drivers and outputs

Free Course Reference Guide

The Complete Financial Modeler is included as part of the programme (Normal RRP £80).

This 150+ page reference guide, taken from Fitch Learning’s “Complete Investment Banker” manual, is designed to act as a comprehensive and practical Excel and modeling reference guide. It helps users become more proficient at taking an idea or objective and turning it into a robust, flexible model. It is relied upon globally by our clients to support further training after their course. The guide is applicable for professionals at every level, from Intern through to Managing Director. It contains the clearest explanation and application of technical Excel content in a way that makes it accessible to all.

The programme also includes our Excel Short Cut that includes quick access to 40 of the most commonly used shortcuts.

Who should attend

This course is aimed at financial modelers, M&A analysts and associates with significant financial modeling experience who have identified problems adapting their own models and also wish to take their skills to the next level.

Course reviews

Downloadable files