Helpful Financial Modeling Tips
Building a financial model in Excel can be a daunting task and many analysts fear the request of being asked to build one from scratch. The goal of this article is to give you our top 10 financial modeling tips so you can approach the challenge with confidence, knowing that you’re off to a good start.
List of Top 10 Financial Modeling Tips:
Always plan your layout and structure before you starting building your model. There are generally two approaches to the layout and structure: (1) a single worksheet with grouping and (2) a multi-worksheet approach. With the former, all assumptions, the income statement, balance sheet, and cash flow statement are laid out on a single tab (stacked vertically above each other). This layout makes it easier for linking and formula building and reduces the chances or errors. The FMVA® program always recommends single worksheet models.
#2 Color coding
In order to make sure users (and designers!) of models know which cells contain hardcoded inputs and which contain formulas, it’s important to use color coding. Assumptions (a.k.a. drivers, inputs, hardcodes) should have a blue font color and all formulas and functions should be black. If formulas link to another worksheet they should be green, and if they link to another file they should be red. Strictly following these guidelines will elevate your financial modeling abilities significantly.
One of the best ways to keep information organized is by using grouping in Excel. Grouping rows and/or columns enables the model to easily expand and contract depending on what information you’d like to display. This featured can be used by selecting multiple rows, then on the ribbon Data > Group > Group (Alt, D, G, G on the keyboard). Once you start using this feature you’ll find yourself addicted to keeping files extremely organized and easy to navigate… you’ll also see your financial modeling skills rising to new levels.
#4 Special formulas
One thing that really makes your Excel modeling skills stand out is the ability to use effective formulas and functions. There are certain formulas and functions you must use such as Index, Match, Offset, Choose, Xnpv, Xirr, and If. By knowing how to use those 7 functions you’ll be off to a great start and you’ll see the usability and efficiency of your models really start to take off. If you want to make sure you have the fundamentals down, take a Free Excel Crash Course to learn everything you need to know.
#5 Freeze panes
As your models start to increase in size and complexity it can be really helpful to freeze the top few rows of the spreadsheet so users can always see what time period they’re in, and can easily view other critical information you may want them to see. In the example below, you can see that rows 1 and 2 are displayed at the top despite the user being editing rows in the 200s. As files get long and complicated this can be a big help and is highly recommended.
#6 Auditing tools
This is one of the most overlooked financial modeling tips. When all the formulas and functions are built, the work is not over yet, and while it can be tempting to start celebrating you should really just be beginning to start auditing the model to find any errors or issues. The top auditing tools include Go To Special (F5), Trace Precedents, Trace Dependents, and the Show Formulas view. By using each of these tools you’ll greatly increase your chances of finding an error.
#7 Stress testing
Once you’ve audited the model for explicit errors you still need to check it for any logical errors. For example, this means testing extreme cases with your assumptions to see what happens to the outputs. You may try a steeply declining revenue profile and see what happens to the company’s balance sheet. Does the cash balance deplete as expected? Try running a set of completely flat assumptions (zero change) and see if the income statement balance sheet stay flat as well. This will help you determine if the results from the model make sense.
You can add scenarios to your model either at the beginning when you set it up, or you can easily add them later. Since nobody has a crystal ball to tell them exactly what’s going to happen in the future, it’s important to include different scenarios about what the world could look like in the future. Common examples include a Base Case (the most likely scenario), a Downside Case (if things don’t go well) and an Upside Case (if things go better than planned).
#9 Sensitivity analysis
Sensitivity analysis is different than scenario analysis as it tests one variable at a time. The purpose of it is to see how sensitive a certain output is to changes in underlying assumptions. For example, if the price of a product increases by 10%, how much does the Net Present Value (NPV) of the business increase by? If the discount rate used to value the business is 7% instead of 8%, what impact does that have on the share price? Having a robust section of sensitivity analysis is one top financial modeling tips we can provide.
#10 Charts, graphs and outputs
The most effective way to get the results of your analysis across is to create beautiful charts, graphs, and tables that can be easily inserted into a presentation. Most people can’t read the inner workings of an Excel file and require a visual representation of the analysis. If you build well-designed charts (bar charts, line charts, column charts, combinations, etc.) you’ll find your analysis has a bigger impact and can impact a wider audience.
FMVA – Financial Modeling Careers Certification
To take the above top 10 financial modeling tips and turn them into practice, you can consider the Financial Modeling and Valuation Analyst (FMVA)™ designation. This credential shows employers you’ve got the skills they’re looking for and can perform advanced analysis in Excel.
Thank you for reading this guide to the top 10 financial modeling tips. To learn more about the Financial Modeling and Valuation Analyst (FMVA)™ program and corporatefinance.org, please explore these additional resources: