Best Practice Financial Modeling (Intermediate)

The two days are trainer-led and necessarily hands-on at all points with the participants building a model from the starting point of a blank Excel file. The course will develop the best practice approaches to structuring and presenting fully flexible and auditable forecast financial models suitable for analysis, financial structuring and/or valuation.

Key Learning Outcomes:

• Construct professional models
• Understand how to build integrated models with additional valuation or analysis
• Understanding the sensitivity of key drivers
• Significantly improve efficiency of using shortcuts
• Optimise the use of financial functions

Content

Day One

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)

• Customizing
• Styles
• 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

Day Two

Forecasting

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”

Forecasting techniques

• 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

Building switches

• 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
• WACC
• 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

Free Course Reference Guides

The Complete Financial Modeller is included as part of the programme

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.

You will also receive the 3-Statement Model Construction Kit

The 3-Statement Model Construction Kit, part of Fitch Learning's Complete Analyst system, is a compilation of step-by-step snapshots of how to construct, from scratch, a 3-statement financial model, which would be suitable for forecasting financials, performing credit analysis, establishing discounted cash flow valuation and a range of other uses. The design and build of an integrated 3-statement model requires a structured approach and this kit breaks down the approach into a series of simple steps that will reinforce classroom learning, post programme. Written by the industry's leading IBD trainers, this kit is a useful aid for quick memory recall and contains the clearest explanation and application of technical content in a manner that makes it relevant daily to bankers of all levels.

The course also includes a handy pocket size Excel Short Cut takeaway guide containing 40 of the most commonly used shortcuts.