Advanced Financial Modeling

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 optimizing the debt structure
• Debt amortization 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 analyze 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-centering 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

Free Course Reference Guides

The Complete Financial Modeller 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 for professionals to help them become more proficient at taking an idea or objective and turning it into a robust, flexible models and excel spreadsheets. This manual is relied globally by our clients to support our training after our programmes have completed. The manual is applicable for professionals at every level from intern through to managing director and contains the clearest explanation and application of technical Excel content in a way that makes it accessible to professionals while at their desks.

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