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.
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)
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.
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)
Additionally, the above two tools can be used to perform two different types of sensitivity analysis:
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) 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:
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.
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: