Best Practice Financial Modeling (Intermediate)
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.
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
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
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.
Who should attend
Professionals in corporates and financial institutions who wish to raise their financial valuation skills to a superior level. Attendees should have a good basic excel knowledge and basic accounting knowledge. This course is the ideal follow up to the Introduction to Financial Modeling.