Publication number: ELQ-37929-1
View all versions & Certificate
How to Build a Financial Model | The Complete Guide
This is a fantastic guide offering tips and tricks to help you improve your financial modeling ability.
Introduction
What will this financial modeling guide cover?
This financial modeling guide will cover several important topics designed to sharpen your financial analysis. Topics in this guide include:
Why build a financial model
Excel tips and tricks
Modeling best practices
Building the forecast
Linking the statements
Further analysis (DCF, sensitivity, M&A and more)
Presenting results
Why build a financial model?
For anyone pursuing or advancing a career in corporate development, investment banking, financial planning, and analysis FP&A, equity research, commercial banking, or other areas of corporate finance, building financial models is part of the daily routine.
Financial models are essential just tools to help make decisions. These decisions often include: whether or not to invest in a company, asset, or security; whether or not to invest in a project (project finance); whether or not to do a merger or acquisitions, and whether or not to raise money; and other corporate finance transactions.
The financial model allows decisions makers to test scenarios, observe potential outcomes, and hopefully make an informed decision. There is a lot of talk about software programs that can be used, but the truth is the vast majority of financial modeling takes place in Excel.
- Step n°1 |
Excel tips and tricks
Excel is the main tool used by banks, corporations, and institutions to perform financial modeling. The main reason for this is Excel’s phenomenal versatility. Every company or investment opportunity is unique, and Excel is a blank canvas that can be totally customized and tailor to the situation. The flip side of this is that there are no controls or rules in place to ensure the model is accurate and error-free.
Here are some of the most important Excel tips for this financial modeling guide:
Use as many keyboard shortcuts as possible
Keep formulas and calculations simple – break them down into smaller steps
Use the grouping function to organize sections of the financial model
Use F5 (go to special) to quickly locate all hardcoded numbers or formulas
Use Trace Precedents and Trace Dependents to audit the model
Use XNPV and XIRR to apply specific dates to cash flows
Use INDEX MATCH over VLOOKUP for looking up information
Use a combination of date functions (EOMONTH) and IF statements to make dates dynamic
Remove gridlines when presenting or sharing the financial model
Memorize all the most important Excel formulas for financial modeling
- Step n°2 |
Financial modeling best practices
Over and above good Excel skills, analysts who really stand out at financial modeling are great at structuring and organizing their spreadsheets.
Here are our top 10 best practices for structuring a model:
- Use color coding to distinguish between inputs and formulas (i.e. blue and black)
- Build a standalone 3 statement model on one worksheet (don’t separate the statements onto different sheets)
- Clearly separate the assumptions or drivers from the rest of the model (one section at the top)
- Use clear headers and subheads (with bold shading) to clearly distinguish sections
- Use the cell comments function (shift + F2) to describe calculations or assumptions that need explaining
- Build in error checks such as ensuring the balance sheet balances (without a plug)
- Pull forward (or repeat) information where it helps users follow the logic of the model (i.e. pull forward EBITDA from the income statement to the cash flow valuation section)
- Avoid linking to other Excel workbooks unless absolutely necessary (and if so, clearly indicate those links exist)
- Avoid circular references unless necessary (and use iterative calculation to solve them)
- Use tables, charts and graphs to summarize important information
