1/26/2021 Financial Modeling Best Practices & Excel Guide - Wall Street Prep
tabbs
The Ultimate Guide to Financial
Modeling Best Practices
How to structure, format, audit and error-proof your nancial model
Introduction
Like many computer programmers, people who build nancial models can get quite opinionated about the “right way” to do it.
In fact, there is surprisingly little consistency across Wall Street around the structure of nancial models. One reason is that
models can vary widely in purpose. For example, if your task was to build a discounted cash ow (DCF) model to be used in a
preliminary pitch book as a valuation for one of 5 potential acquisition targets, it would likely be a waste of time to build a
highly complex and feature-rich model. The time required to build a super complex DCF model isn’t justi ed given the model’s
purpose.
On the other hand, a leveraged nance model used to make thousands of loan approval decisions for a variety of loan types
under a variety of scenarios necessitates a great deal of complexity.
Understanding the purpose of the model is key to determining its optimal structure. There are two primary determinants of a
model’s ideal structure: granularity and exibility. Let’s consider the following 5 common nancial models:
Model Purpose Granularity Flexibility
One page DCF Used in a buy side pitch book to Low. Ball-park valuation Low. Not reusable without structural modi cations. Will be
provide a valuation range for one of range is su cient) / used in a speci c pitch and circulated between just 1-3 deal
several potential acquisition targets. Small. Entire analysis can team members.
t on one worksheet <
300 rows)
Fully Used to value target company in a Medium Low. Not reusable without structural modi cations. Will be
integrated fairness opinion presented to the tailored for use in the fairness opinion and circulated
DCF acquiring company board of directors between deal time members.
Comps model Used as the standard model by the Medium High. Reusable without structural modi cations. A
template entire industrials team at a bulge template to be used for a variety of pitches and deals by
bracket bank many analysts and associates, possibly other stakeholders.
Will be used by people with varying levels of Excel skill.
Restructuring Built speci cally for a multinational High Medium. Some re-usability but not quite a template. Will
model corporation to stress test the impact of be used by both the deal team and counterparts at the
selling 1 or more businesses as part of client rm.
a restructuring advisory engagement
Privacy - Terms
Wall Street Prep | www.wallstreetprep.com
https://www.wallstreetprep.com/knowledge/financial-modeling-best-practices-and-conventions/ 1/25
, 1/26/2021 Financial Modeling Best Practices & Excel Guide - Wall Street Prep
tabbs
Model Purpose Granularity Flexibility
Leveraged Used in the loan approval process to High High. Reusable without structural modi cations. A
nance analyze loan performance under template to be used group wide.
model various operating scenarios and credit
events
Financial model granularity
A critical determinant of the model’s structure is granularity. Granularity refers to how detailed a model needs to be. For
example, imagine you are tasked with performing an LBO analysis for Disney. If the purpose is to provide a back-of-the-
envelope oor valuation range to be used in a preliminary pitch book, it might be perfectly appropriate to perform a “high
level” LBO analysis, using consolidated data and making very simple assumptions for nancing.
Continue Reading Below
STEP-BY-STEP ONLINE COURSE
Everything You Need To Master
Financial Modeling
Enroll in The Premium Package: Learn Financial
Statement Modeling, DCF, M&A, LBO and Comps.
The same training program used at top investment
banks.
Enroll Today
If, however, your model is a key decision making tool for nancing requirements in a potential recapitalization of Disney, a far
higher degree of accuracy is incredibly important. The di erences in these two examples might involve things like:
Forecasting revenue and cost of goods segment by segment and using price-per-unit and #-units-sold drivers instead of
aggregate forecasts
Forecasting nancials across di erent business units as opposed to looking only at consolidated nancials
Analyzing assets and liabilities in more detail (i.e. leases, pensions, PP&E, etc.)
Breaking out nancing into various tranches with more realistic pricing
Looking at quarterly or monthly results instead of annual results
Privacy - Terms
Wall Street Prep | www.wallstreetprep.com
https://www.wallstreetprep.com/knowledge/financial-modeling-best-practices-and-conventions/ 2/25
, 1/26/2021 Financial Modeling Best Practices & Excel Guide - Wall Street Prep
tabbs
Practically speaking, the more granular a model, the longer and more di cult it will be to understand. In addition, the
likelihood of errors grows exponentially by virtue of having more data. Therefore, thinking about the model’s structure —
from the layout of the worksheets to the layout of individual sections, formulas, rows and columns — is critical for granular
models. In addition, integrating formal error and “integrity” checks can mitigate errors.
Financial model exibility
The other main determinant for how to structure a model is its required exibility. A model’s exibility stems from how often
it will be used, by how many users, and for how many di erent uses. A model designed for a speci c transaction or for a
particular company requires far less exibility than one designed for heavy reuse (often called a template).
As you can imagine, a template must be far more exible than a company speci c or “transaction speci c model. For example,
say that you are tasked with building a merger model. If the purpose of the model is to analyze the potential acquisition of
Disney by Apple, you would build in far less functionality than if its purpose was to build a merger model that can handle any
two companies. Speci cally, a merger model template might require the following items that are not required in the deal-
speci c model:
1. Adjustments to acquirer currency
2. Dynamic calendarization (to set target’s nancials to acquirer’s scal year)
3. Placeholders for a variety of income statement, balance sheet and cash ow statement line items that don’t appear on
Disney or Apple nancials
4. Net operating loss analysis (neither Disney or Apple have NOLs)
Together, granularity and exibility largely determine the structural requirements of a model. Structural requirements for
models with low granularity and a limited user base are quite low. Remember, there is a trade-o to building a highly
structured model: time. If you don’t need to build in bells and whistles, don’t. As you add granularity and exibility, structure
and error proo ng becomes critical.
Privacy - Terms
Wall Street Prep | www.wallstreetprep.com
https://www.wallstreetprep.com/knowledge/financial-modeling-best-practices-and-conventions/ 3/25
tabbs
The Ultimate Guide to Financial
Modeling Best Practices
How to structure, format, audit and error-proof your nancial model
Introduction
Like many computer programmers, people who build nancial models can get quite opinionated about the “right way” to do it.
In fact, there is surprisingly little consistency across Wall Street around the structure of nancial models. One reason is that
models can vary widely in purpose. For example, if your task was to build a discounted cash ow (DCF) model to be used in a
preliminary pitch book as a valuation for one of 5 potential acquisition targets, it would likely be a waste of time to build a
highly complex and feature-rich model. The time required to build a super complex DCF model isn’t justi ed given the model’s
purpose.
On the other hand, a leveraged nance model used to make thousands of loan approval decisions for a variety of loan types
under a variety of scenarios necessitates a great deal of complexity.
Understanding the purpose of the model is key to determining its optimal structure. There are two primary determinants of a
model’s ideal structure: granularity and exibility. Let’s consider the following 5 common nancial models:
Model Purpose Granularity Flexibility
One page DCF Used in a buy side pitch book to Low. Ball-park valuation Low. Not reusable without structural modi cations. Will be
provide a valuation range for one of range is su cient) / used in a speci c pitch and circulated between just 1-3 deal
several potential acquisition targets. Small. Entire analysis can team members.
t on one worksheet <
300 rows)
Fully Used to value target company in a Medium Low. Not reusable without structural modi cations. Will be
integrated fairness opinion presented to the tailored for use in the fairness opinion and circulated
DCF acquiring company board of directors between deal time members.
Comps model Used as the standard model by the Medium High. Reusable without structural modi cations. A
template entire industrials team at a bulge template to be used for a variety of pitches and deals by
bracket bank many analysts and associates, possibly other stakeholders.
Will be used by people with varying levels of Excel skill.
Restructuring Built speci cally for a multinational High Medium. Some re-usability but not quite a template. Will
model corporation to stress test the impact of be used by both the deal team and counterparts at the
selling 1 or more businesses as part of client rm.
a restructuring advisory engagement
Privacy - Terms
Wall Street Prep | www.wallstreetprep.com
https://www.wallstreetprep.com/knowledge/financial-modeling-best-practices-and-conventions/ 1/25
, 1/26/2021 Financial Modeling Best Practices & Excel Guide - Wall Street Prep
tabbs
Model Purpose Granularity Flexibility
Leveraged Used in the loan approval process to High High. Reusable without structural modi cations. A
nance analyze loan performance under template to be used group wide.
model various operating scenarios and credit
events
Financial model granularity
A critical determinant of the model’s structure is granularity. Granularity refers to how detailed a model needs to be. For
example, imagine you are tasked with performing an LBO analysis for Disney. If the purpose is to provide a back-of-the-
envelope oor valuation range to be used in a preliminary pitch book, it might be perfectly appropriate to perform a “high
level” LBO analysis, using consolidated data and making very simple assumptions for nancing.
Continue Reading Below
STEP-BY-STEP ONLINE COURSE
Everything You Need To Master
Financial Modeling
Enroll in The Premium Package: Learn Financial
Statement Modeling, DCF, M&A, LBO and Comps.
The same training program used at top investment
banks.
Enroll Today
If, however, your model is a key decision making tool for nancing requirements in a potential recapitalization of Disney, a far
higher degree of accuracy is incredibly important. The di erences in these two examples might involve things like:
Forecasting revenue and cost of goods segment by segment and using price-per-unit and #-units-sold drivers instead of
aggregate forecasts
Forecasting nancials across di erent business units as opposed to looking only at consolidated nancials
Analyzing assets and liabilities in more detail (i.e. leases, pensions, PP&E, etc.)
Breaking out nancing into various tranches with more realistic pricing
Looking at quarterly or monthly results instead of annual results
Privacy - Terms
Wall Street Prep | www.wallstreetprep.com
https://www.wallstreetprep.com/knowledge/financial-modeling-best-practices-and-conventions/ 2/25
, 1/26/2021 Financial Modeling Best Practices & Excel Guide - Wall Street Prep
tabbs
Practically speaking, the more granular a model, the longer and more di cult it will be to understand. In addition, the
likelihood of errors grows exponentially by virtue of having more data. Therefore, thinking about the model’s structure —
from the layout of the worksheets to the layout of individual sections, formulas, rows and columns — is critical for granular
models. In addition, integrating formal error and “integrity” checks can mitigate errors.
Financial model exibility
The other main determinant for how to structure a model is its required exibility. A model’s exibility stems from how often
it will be used, by how many users, and for how many di erent uses. A model designed for a speci c transaction or for a
particular company requires far less exibility than one designed for heavy reuse (often called a template).
As you can imagine, a template must be far more exible than a company speci c or “transaction speci c model. For example,
say that you are tasked with building a merger model. If the purpose of the model is to analyze the potential acquisition of
Disney by Apple, you would build in far less functionality than if its purpose was to build a merger model that can handle any
two companies. Speci cally, a merger model template might require the following items that are not required in the deal-
speci c model:
1. Adjustments to acquirer currency
2. Dynamic calendarization (to set target’s nancials to acquirer’s scal year)
3. Placeholders for a variety of income statement, balance sheet and cash ow statement line items that don’t appear on
Disney or Apple nancials
4. Net operating loss analysis (neither Disney or Apple have NOLs)
Together, granularity and exibility largely determine the structural requirements of a model. Structural requirements for
models with low granularity and a limited user base are quite low. Remember, there is a trade-o to building a highly
structured model: time. If you don’t need to build in bells and whistles, don’t. As you add granularity and exibility, structure
and error proo ng becomes critical.
Privacy - Terms
Wall Street Prep | www.wallstreetprep.com
https://www.wallstreetprep.com/knowledge/financial-modeling-best-practices-and-conventions/ 3/25
