Advanced Financial Modeling
The course will develop the best practice approaches to structuring and presenting fully flexible and auditable models whilst cultivating the most efficient data analysis tools and solve other common modeling problems. The course is trainer-led, but necessarily hands-on at all points with the participants solving modeling problems throughout.
This course gives 16 CPD hours.
Who should attend?
This course is aimed at those 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.
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
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
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
- Auditing tools
- 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 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
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.
About Fitch Learning
Part of the Fitch Group, Fitch Learning partners with clients to enhance knowledge, skills and conduct. With centers in London, New York, Singapore, Dubai and Hong Kong, we are committed to questioning and understanding client needs across the globe and...
Have a question about this course? Fill out this form and the provider will get in touch with you shortly
Need help with your search?
findcourses.com offers a free consultancy service to help compare training for you and your team