Advanced Financial Modeling (AFM)

Top 5 techniques and skills

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 modelers should considering using to perform industry-leading financial analysis.

Advanced Financial Model - AMZN Case Study

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:

1. Scenarios

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 by using the CHOOSE function in Excel.  With this function, you can easily toggle between Scenario A, Scenario B, Scenario C, etc., in a model. For a detailed explanation of the choose function, see our advanced Excel formulas guide.

advanced financial modeler function

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.

best financial modeling courses (sensitivity analysis)

3. Dynamic headings and features

If you want to become a financial modeler who’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
  • Dates
  • Currency
  • 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.

advanced concatenate function excel

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:

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.

advanced consolidation model

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) are 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:

afm complex deal structure

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 you can 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. CFI is the official provider of the Financial Modeling & Valuation Analyst (FMVA)® designation and is on a mission to help you advance your career. To keep learning and developing your skills, we highly recommend these additional free CFI resources:

0 search results for ‘