1/26/2021Financial Modeling Best Practices & Excel Guide - Wall Street Prep https://www.wallstreetprep.com/knowledge/financial-modeling-best-practices-and-conventions/1/25 Privacy - Terms Wall Street Prep | www.wallstreetprep.com The Ultimate Guide to Financial Modeling Best Practices How to structure, format, audit and error-proof your nancial model 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 justied 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 PurposeGranularityFlexibility One page DCF Used in a buy side pitch book to provide a valuation range for one of several potential acquisition targets.Low. Ball-park valuation range is sucient) / Small. Entire analysis can t on one worksheet < 300 rows) Low. Not reusable without structural modications. Will be used in a specic pitch and circulated between just 1-3 deal team members.Fully integrated DCF Used to value target company in a fairness opinion presented to the acquiring company board of directors MediumLow. Not reusable without structural modications. Will be tailored for use in the fairness opinion and circulated between deal time members.Comps model template Used as the standard model by the entire industrials team at a bulge bracket bank MediumHigh. Reusable without structural modications. A template to be used for a variety of pitches and deals by many analysts and associates, possibly other stakeholders.Will be used by people with varying levels of Excel skill.Restructuring model Built specically for a multinational corporation to stress test the impact of selling 1 or more businesses as part of a restructuring advisory engagement HighMedium. Some re-usability but not quite a template. Will be used by both the deal team and counterparts at the client rm.Introduction Wall Street Financial Modeling Best Practices & Excel Guide 1 / 3
1/26/2021Financial Modeling Best Practices & Excel Guide - Wall Street Prep https://www.wallstreetprep.com/knowledge/financial-modeling-best-practices-and-conventions/2/25 Privacy - Terms Wall Street Prep | www.wallstreetprep.com Model PurposeGranularityFlexibility Leveraged nance model Used in the loan approval process to analyze loan performance under various operating scenarios and credit events HighHigh. Reusable without structural modications. A template to be used group wide.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.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 dierences 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 dierent 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
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 Continue Reading Below 2 / 3
1/26/2021Financial Modeling Best Practices & Excel Guide - Wall Street Prep https://www.wallstreetprep.com/knowledge/financial-modeling-best-practices-and-conventions/3/25 Privacy - Terms Wall Street Prep | www.wallstreetprep.com Practically speaking, the more granular a model, the longer and more dicult 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 dierent uses. A model designed for a specic 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 specic or “transaction specic 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. Specically, a merger model template might require the following items that are not required in the deal-
specic model:
- Adjustments to acquirer currency
- Dynamic calendarization (to set target’s nancials to acquirer’s scal year)
- Placeholders for a variety of income statement, balance sheet and cash ow statement line items that don’t appear on
- Net operating loss analysis (neither Disney or Apple have NOLs)
- / 3
Disney or Apple nancials
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 proong becomes critical.