Close the Skill Gap -> Enroll to be a Certified Financial Modeling & Valuation Analyst (FMVA)® Today!

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

 

advanced financial modeling afm example

 

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 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.

 

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.

 

afm 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
  • 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:

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

 

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

 

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

  • DCF modeler guide
  • Advanced Excel formulas guide
  • Financial modeling skills
  • Types of models

Financial Modeling Certification

Become a certified Financial Modeling and Valuation Analyst (FMVA)® by completing CFI’s online financial modeling classes!