Advanced Financial Modeling (AFM) Techniques
When it comes to advanced financial modeling (AFM) there are several important techniques that world-class financial analysts practice. This guide outlines the best, most advanced techniques financial modeler should use or considering using to perform industry-leading financial analysis.
Screenshot from CFI’s Advanced Financial Modeling & Valuation Course Amazon Case Study
Below we outline some of the most important advanced financial modeling (AFM) techniques.
Advanced financial modelers use scenarios to make a model more dynamic and cover a wide range of outcomes. These scenarios are designed in Excel, and there are various formulas and functions that can be used to build them.
The most common assumptions to build scenarios for in a financial model are:
- Volume and production (units, customers, services, etc.)
- Sales prices (goods, services)
- Variable costs (consumables, labor rates, components of COGS, marketing, etc.)
- Fixed costs (rent, overhead, administration)
- Capital costs (sustaining capex, growth capex, cost overruns, project delays)
- Discount rate (cost of equity, cost of debt, WACC)
- FX rates (for businesses operating in multiple currencies)
- Macroeconomics assumptions (GDP growth, interest rates, inflation, etc)
- Other industry-specific metrics
The most common way to build these scenarios is using the CHOOSE function in Excel. With this function, you can easily toggle between Scenario A, Scenario B, Scenario C, etc in the model. For a detailed explanation of the choose function, see our advanced Excel formulas guide.
#2 Sensitivity analysis
Anyone who considers themselves to be an advanced financial modeler knows it’s important to understand how sensitive the results of a model are to changes in inputs and assumptions.
The two main Excel tools for performing sensitivity analysis are:
- Goal seek (what-if analysis)
- Data tables
Additionally, the above two tools can be used to perform two different types of sensitivity analysis:
- Direct method
- Indirect method
For a step-by-step tutorial on how to integrate this into an advanced model, see our online course on sensitivity analysis.
#3 Dynamic headings and features
If you want to become a financial modeler that’s truly advanced, you’ll need to incorporate dynamic headings and labels into your models.
The top examples of these dynamic features include:
- Company/project name
- Scenario name
- Any other type of label/heading
The CONCATENATE function in Excel is a way of joining text and inserting dynamic variables into labels. To see a detailed example of this, take our advanced Excel course.
#4 Ability to consolidate multiple businesses or units
Beyond a basic model, it’s important to be able to roll up or consolidate multiple businesses into one parent company level.
The main use of this feature is for:
- Companies with multiple business units
- Mergers and acquisitions (M&A)
The best way to design for this functionality is to create a single-tab or single-sheet model that can easily be duplicated. Once duplicated (as many times as necessary), a new sheet can be inserted that sums up the values from all the other sheets. Since the tabs/sheets are identical, they are straightforward to add up.
#5 Handles complex transaction structures
The last (and definitely not least) feature of advanced financial modeling on our list is the ability to handle complex transaction structures. Once a simple DCF model and internal rate of return (IRR) is in place, a complex transaction structure can have a big impact on the IRR for different classes of investors in the deal.
Examples of complex transaction structures include:
- Leveraged buyout (LBO)
- M&A models with accretion/dilution
- Joint Ventures with GPs and LPs
- Earnouts, Vender takeback notes, PIK loans, etc
How to learn advanced financial modeling
The best way to learn is by doing. CFI has developed several courses and certifications for any financial modeler to become advanced. We recommend starting with our foundational classes (which also come with certificates) and working your way up through the levels.
After our global team of instructors guides you through the model building process step-by-step we recommend practicing on your own by downloading a public company’s financial statements, copying them into Excel, and building a model.
The next step is to take the company’s current share price and reverse engineer your model to arrive at the current price. By doing this, you’ll see what assumptions are baked into the current stock price, and build all sorts of different scenarios from there.
Launch our financial modeling courses and certificates now!
Additional AFM resources
This has been a guide to CFI’s top 5 AFM techniques. To keep learning and developing your skills we highly recommend these additional free resources: