Comprehensive course analysis
Who should attend
Professionals in corporates and financial institutions who need to create useful and robust financial models. Attendees should be able to use the basic facilities of Microsoft Excel and have basic accounting knowledge.
About the course
This five-day advanced certificate program consists of three courses; Fundamentals of Financial Modeling, Best Practice Financial Modeling and Advanced Financial Modeling. This program is designed for professionals who wish to raise their financial modeling skills to a superior level and to structure and present fully flexible and auditable models, whilst cultivating the most efficient data analysis tools and solve other common modeling problems.
Key Learning Outcomes
- Design and construct useful and robust financial modeling applications
- Learn how to develop flexible financial forecasts
- Use several of the support tools and techniques in spreadsheet programs
- Build robust sensitivity and scenario analysis
- Construct professional models such as DCF valuation and LBO models
- Understand how to build integrated models with additional valuation or analysis
- Understanding the sensitivity of key drivers
- Significantly improve efficiency of using shortcuts
- Optimize the use of financial functions
- Harness Excel’s tools within a best practice framework
- Add flexibility to their models using switches and flexible lookups
- Work efficiently with large data volumes
- Model debt effectively
- Approach modeling for tax and debt with confidence
- Build flexible charts and sensitivity analysis to aid the presentation of results
Excel Skills for Modeling
Excel can be used like a calculator. This session converts participants to using Excel in best practice ways for modeling so that repeatable, consistent, flexible and robust formulae and structures can be created.
- Effective use of keyboard shortcuts
- Dynamic referencing
- Formatting data professionally
- Style basics
Introduction to Modeling
What are the key attributes of a model? Starting the design of a model focused on the required outputs will allow us to work backwards to understand what modules are required to get to this point. However, the structure must allow flex – things change.
- Effective construction of a model
- Objective and structure
- Initial preparation
The Fundamentals of Model Building
Applying best practice approaches we create a model required for analysis, and then add create some forecasts
Model set up
- Consistent sheet and print set-up
- Input vs formulas
- Inputting base data
Key Excel functions and tools
- IF (and other logicals) with flags
- Setting up date flexibility throughout the model using EDATE or EOMONTH
- Text strings
- Conditional formatting
Adding the financial data
- Data transfer and links
- SUMIF and data validation to extract the relevant data
- Breakdown and grouping of accounting elements
- Determine drivers of the forecasts
- Using Excel tools to identify trends
- Effectively creating assumptions for projections
Model diagnostics and checks
- Identifying what could go wrong and flagging up any errors
Flexible and user-friendly models
- Quick build scenario manager with switch – base case vs downside vs upside
Auditing and Debugging
Picking up someone else’s model can be challenging. This session guides the participants through an effective approach to reviewing models.
- Using existing models
- How to become comfortable with ‘new’ models
- Auditing models
Formatting and Showcasing
A quick overview of the best approaches to creating an output sheet are explored.
Creating an output sheet / dashboard
- The camera
- Professional showcase
- Use of graphs
Basics of Financial Modeling
What are the ground rules to financial modeling? Understanding both the dos and don’ts of good financial models and what are the most efficient techniques to model construction and review are necessary to ensure best practice is incorporated into models.
Effective keyboard shortcuts
Customizing Excel for efficient modeling
Formatting (for the last time in your life)
- Conditional formatting
Overview of useful functions (IF, MAX, MIN, NPV, IRR, etc.)
Consistency, consistency, consistency
The importance of checks
Setting up a Model
Starting from a blank Excel spreadsheet we define and create a structure which enables flexible forecast modeling.
- Understanding the structure of a model – inputs, workings, outputs
- Scope limitations – output requirements vs. input source
- Using group edit tools to quickly set up a robust, consistent and printable financial model
- Input sheets – creating underlying assumptions / forecasts
- Setting up date flexibility throughout the model – using EDATE and similar functions
Build the Financials of a Listed Company
Provided with the annual report, we add the financials by constructing the integrated historic financial statements suitable for flexible forecasting. If we forecast financials there are knock-on effects to other elements of the model. Are these inter-linkages understood and can they be consistently applied so that the integrity of the model is maintained? This session ensures these connections are understood.
Extracting the relevant historics by defining the key numbers, e.g.
- Revenues, EBITDA and net income
- Operating cash flows and CFADS
- Debt, net debt and capital structure
Other line items needed to calculate the key numbers, e.g. operating costs, interest, tax, etc.
Integrating/linking the financials
- Income statement to cash flows
- Income statement to balance sheet
- Cash flows to balance sheet
Building consistent checks
We have created the perfect structure for projecting the income statement, balance sheet and cash flow numbers. Combining Excel analytical tools with commercial knowledge we create and apply forecast drivers to calculate the integrated forecasts.
Step by step forecasting / integration – a modular approach to forecasting
Identifying and forecasting the value drivers, e.g.
- Sales growth, margins, capex / depreciation, effective tax rates, working capital days
- Applying the concept of “fade”
- Harnessing Excel’s forecasting tools
- Forecasting for consistency
- Charts as guides
Integrating the forecasts in the financial statements
Approaches to ensure the avoidance of circular references
Building a Fully Flexible Scenario Manager
We are rarely content with a unitary outcome. What happens if a product fails, the $ strengthens, the business opens a German business? This session focuses on adding that flexibility to the model.
Base, Bear, Bull
- Data validation
- Visual basic tools to enable the efficient switching between different scenarios
- IF, Choose, Vlookup, Index or Offset?
Valuation Concept Reviewed
Is the share undervalued or overvalued? As most of the numbers have now been created, we review the key equity/company valuation techniques and apply the DCF approach, to answer this question.
Refresher of company valuation techniques
Practical application of valuation constituents
Practical DCF construction
- Forecast horizon
- Free cash flow
- Terminal value
- Discounting the cash flows
- From enterprise value to equity value
Additional Applications of Integrated Financial Models
How else can this data be used? The program will conclude with a quick review of how the model can be easily flexed to allow for further analysis or outputs to be created.
Additional applications reviewed
How to expand your model for additional analysis including:
- Discounted cash flow
- Multiples valuation approaches – “comps”, “precedents”
- Leveraged finance
- Overview of level of ratios in line with covenants
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 math 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 vs. 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
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 do that most efficiently?
- Eomonth, Edate, etc. for date functionality
- Changing the start/transaction date; changing the length of forecast periods
- Consolidating monthlies into quarterlies and annuals
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
- F2 v Ctrl-[ v Auditing toolbar
- AL M P
- 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
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
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
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 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.
Read more about Business Analytics
Because of COVID-19, many providers are cancelling or postponing in-person programs or providing online participation options.
We are happy to help you find a suitable online alternative.