Advanced Financial Modeling (AFM)
Top 5 techniques and skills
Top 5 techniques and skills
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.
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:
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.
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:
Additionally, the above two tools can be used to perform two different types of sensitivity analysis:
For a step-by-step tutorial on how to integrate this into an advanced model, see our online course on sensitivity analysis.
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:
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.
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.
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:
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!
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:
Learn how to build a financial model in Excel with CFI's Financial Modeling Courses! Build a model from scratch the easy way with step-by-step instruction.
Become a certified Financial Modeling and Valuation Analyst (FMVA)™ by completing CFI’s online financial modeling classes!