Alberto Mihelcic Bazzana
Albert is a seasoned PE executive with over $1Bn in trasactions. Prior to this he led consulting engagements across nine countries.
Expertise
Previously At

From abstract spreadsheets to real-world application, financial models have become an inextricable part of business life and an indispensable part of every company’s toolkit. But irrespective of its ubiquitousness as a productivity and decision-making tool, many out there still have a love-hate relationship with it.
Finance expert Alberto Bazzana authors a comprehensive “how-to guide,” for both the novices and experts among us, detailing Wall Street’s best practices for intelligent, effective, and error-free financial modeling.
From abstract spreadsheets to real-world application, financial models have become an inextricable part of business life and an indispensable part of every company’s toolkit. But irrespective of its ubiquitousness as a productivity and decision-making tool, many out there still have a love-hate relationship with it.
Finance expert Alberto Bazzana authors a comprehensive “how-to guide,” for both the novices and experts among us, detailing Wall Street’s best practices for intelligent, effective, and error-free financial modeling.
Albert is a seasoned PE executive with over $1Bn in trasactions. Prior to this he led consulting engagements across nine countries.
Financial models are an indispensable part of every company’s finance toolkit. They are spreadsheets that detail the historical financial data of a given business, forecast its future financial performance, and assess its risks and returns profile. Financial models are typically structured around the three financial statements of accounting—namely: income statement, balance sheet, and cash flow statement. The management of most corporations rely, at least in part, on the details, assumptions, and outputs of financial models, all of which are critical to said companies’ strategic and capital decision-making processes.
This article serves as a step-by-step guide for the novice and intermediate finance professional looking to follow expert best-practices when building financial models. For the advanced financial modeler, this article will also showcase a selection of expert-level tips and hacks to optimize time, output, and modeling effectiveness. Let’s begin.
As with all things complex, the first step to building a financial model (“model”) is to carefully layout a blueprint. Unplanned, unanticipated structural changes midway through a modeling exercise can be time-consuming, confusing, and error-prone, especially if the model’s adapter is not the same as its author. Such challenges are easily subverted with a bit of devoted planning time at the onset of the exercise. I recommend that your planning phase go as follows:
Clearly defining the purpose of a model is key to determining its optimal layout, structure, and end-outputs. As part of this process, take the time to ensure that your model’s key stakeholders sign off on your blueprint and process design before starting to build. This gives them the opportunity to voice any final preferences or intentions, thus avoiding any “scope creep” (industry parlance) or painful redirection down the road.
Though secondary to the model’s end goal, understanding the timelines for building the model and how long the model will be used for are also important inputs to determining the approach to the modeling exercise. Long-duration and long-tenured (useful-life) models are typically custom built from the ground up and include tremendous amounts of operating detail, flexibility, and sensitivity capabilities. For more immediate, shorter duration operating or capital-project models, modelers will oftentimes use prefabricated templates to maximize speed of construction while minimizing errors. Further, model templates also tend to be more familiar and thus easier to use/manipulate by different stakeholders within organizations.
When deciding the optimal trade-off between desired level of detail and model reusability (i.e., whether the model is intended to be re-worked for multiple transaction-types/purposes or has instead been designed for just this one-off exercise), a useful framework for deciding on one’s model choice/approach, which I have followed through most of my career, is as follows:
With the blueprint/planning phase now complete and key decisions settled up, we may now move onto the next phase of modeling.
At this juncture, we are ready to open Excel and to begin thinking about structuring. At the highest possible level, every model can/should be divided into three sections: (a) inputs/drivers, (b) calculations (projected financial statements), and (c) outputs. The better one is at segregating these sections, the easier it will be to audit and amend the model while minimizing errors and optimizing on time.
I have followed the same structural approach for almost every financial model that I have built; an approach which both my respective stakeholders and I have always found practical, digestible, and ultimately useful. Its sections are as follows:
I will break each of these sections down for you, one at a time. As follows:
The cover page is the first point of contact with your work. While it is the simplest to build, when done well, it leaves a great first impression and clearly explain what is to come. A simple, instructional cover page is generally the best approach and typically includes the following sections:
Please note: I recommend that the cover page always be locked to anyone and everyone without express authority to make changes, outside the author.
Immediately following the model’s cover page, must come the drivers (inputs) tab. You must ensure that this tab is clear, concise, and easy to understand, as this is the tab that non-finance operators will likely manipulate most often. I usually recommend implementing two input sections within the inputs tab, one for static inputs and the other for dynamic. By static inputs I mean inputs that don’t change over time, such as the hypothetical “size of a power plant” or “a company’s starting debt balance”; and by dynamic inputs, I mean inputs that are variable over time (e.g., month-to-month, or year-to-year) such as “inflation” assumptions, “cost of debt,” or “revenue growth” assumptions.
Within both of the above static vs. dynamic input sections, I recommend that you also clearly separate your data into two kinds: (1) hard-coded figures that don’t change irrespective of assumptions scenario, and (b) sensitizing parameters that will drive different assumption scenarios and ultimately your sensitivity tables. Note, however, that you never fully know which parameters are going to constitute sensitivity parameters and which you will not until the final stages of the project. For more on sensitivity modeling, please refer to the following article.
This tab represents the heart of the model, where all the inputs, assumptions, and scenarios work together to project a company’s financial performance into its outer-years. It is also out of this tab that various assumption-driven scenarios will be run as well as the valuation piece of the exercise that will be conducted ahead of the final strategic decision.
Authorized, third-party model operators will use the Scenarios and Sensitivities tab fairly often, even if just to select their choice of pre-programmed scenarios. For this reason, you should build scenarios intuitively, protect the actual scenarios from outside editing, and build sufficiently varied sensitivities such that the handful of pre-programmed scenarios will be sufficient to yield a wide view of possible outcomes once sensitivity tables (sample below) are also built.
For your consideration, the scenarios format structure I have relied on throughout my career is as follows, as just one type of example:
The output tabs are the tabs that operators of the model will use most frequently. Over the years, I’ve found myself leaning towards at least three output tabs for mid-to-complex models:
At this juncture, the construction phase of the model is officially complete. We may turn our attention to some of the expert-level Excel modeling best practices I referred to at the onset of the article. Let’s begin with formatting.
First, it is important to note that each firm/group might have its own preferences or internal practices. As such, while building, it is important to first check in with—and adhere to—whatever format your respective firm prescribes. In the absence of firm-specific practices, however, the content below details Wall Street’s universal language for formatting a model.
The first and lowest-hanging formatting method for financial modeling is to use consistent and identifiable color schemes to denote different types of cells and data. As follows:
Blue = Inputs, or any hard-coded data, such as historical values, assumptions, and drivers.
Black = Formulas, calculations, or references deriving from the same sheet.
Green = Formulas, calculations and references to other sheets (note though that some models skip this step altogether and use black for these cells).
Purple = Links, inputs, formulas, references, or calculations to other Excel files (again, note that some models skip this step altogether and use black for these cells also).
Red = Error to be fixed.
Please note that there is no in-built automation functionality to color code your Excel spreadsheets according to the universal color coding standards above. Instead, you may design your own macro(s) to achieve these outcomes, and subsequently create shortcut combinations to automatically color-code your work.
Sometime in my recent past, I received from a colleague (who I thank to this day), the following macros (including detailed instructions), which have since saved me several hours of manual labor. I’d like to share them, if I may.
Macro creation instructions (for both Mac and PC versions of Excel):
Finding links to other workbooks and worksheets is tricky, and you will most likely have to use VBA to get this working correctly. Here’s the basic idea: search for the presence of the symbol “!” in each cell that contains a formula across your workbook, and then change the font color to green. You will need to modify this in the VBA Editor and make it a for each
loop through all instances of “!” you find, and then change the font color for each of these.
Please be aware that this shortcut still won’t work 100% of the time because some formulas will reference cells in other worksheets without directly linking to them. Fortunately, green cells are rarer than black or blue cells, so the method above works fairly well in most models (and you can organically format the rest of your links to other worksheets manually as they come up or as you come across them).
When modeling, I encourage you to always bear this single question at the back of your mind: “Am I making this model easily auditable?” because for every task executed, formula created, and link built, there will always be a faster, “dirtier” (in industry parlance) way to do the job. Such hacks and tricks, however clever they may seem at the time, and especially after time intervals, will invariably be forgotten and will lead to hard-to-track-down errors. Keeping a third-person reviewer in mind will guide you through your process and help you come to the right decision at key junctures.
Below are a series of best practices on how to build with an auditor mindset. As follows:
You should have only one formula per row, meaning that whatever formula is used in the first cell of any given row should be the same formula uniformly applied across the entire row. Users should understand the structure of your model by looking at the first cell of each row as they proceed vertically down your model.
While this is simple in principle, it is violated often enough to highlight further. A common instance often takes place when spreadsheets are split between a “historical financials” group of columns and “outer-year forecasts” (see image above entitled, “Example of Well-formatted (Color-coded) Financials Summary,” as a reference).
One easy way to address these instances, is the use of flags (e.g., 1/0, TRUE
/FALSE
) positioned at the top of the spreadsheet, then referenced using IF
statements through the body of one’s model. A simple illustration of this at work is as follows:
Never use hard-coded numbers embedded in formulas because they are very difficult to spot if the user is less familiar with the model. Instead, clearly highlight and separate the inputs/hard-codes from the formulas; better yet, gather all the inputs/hard-codes (as appropriate) and aggregate them in the same tab. Subsequently have your formulas pull/reference them as appropriate from the required cell and from the appropriate tab.
It is always better to avoid complicated formulas. Instead, break up your formula into easily digestible steps. Instead of one seemingly neat row, this approach will often create many more rows, resulting in a larger spreadsheet; but one that will be much easier to follow and audit by a third party.
You should decide at time-zero what your sign convention/key will be. By way of illustration, ask yourself in the design stage of your model, “Will costs, expenses, deductions, depreciation, CapEx, etc. be presented as negative or positive numbers?” My personal preference is to always present costs as negative numbers for two reasons: (a) The totals will always be straight sums and you will minimize user error, and (b) it will be easier to spot mistakes using just the signs.
Where possible I strongly recommend avoiding naming your cells as it becomes difficult to locate the source input for said named cell (e.g., “Inflation”) down the road. Instead, I recommend that you rely on the grid convention of Excel within your formulas (e.g., simply linking to cell C4 or location, [Tab Name]l'!G21
, if the reference is in a different tab or workbook).
Organize your inputs simply and transparently. It is my recommendation that you consolidate all inputs in a few driver tabs and reference them from their singular points of origin throughout the spreadsheet.
Avoid linking to other files. It is better to input the relevant data you require from a different file as hard-coded inputs, which you then manually update as required. Cross-linking has been known to crash larger Excel models or update inconsistently, thereby creating hard-to-track errors.
Within longer spreadsheet, “group” rows/columns rather than “hide” them.
This practice is based 100% on experience. It is easier to follow and audit a continuous array for data across one large, contiguous spreadsheet, than across multiple tabs or, worse, multiple spreadsheets that are cross-linked.
Checks are the easiest way to quickly review the integrity of a model. “Checks” encompass everything from ensuring that totals that should tie actually do to ensuring that one’s balance sheet actually balances. I usually build a few checks at the top or bottom of each spreadsheet then consolidate them in a separate “Check Tab.” This ensures that it is easy to find an error in the model and then trace where that error originated.
Please note that relying solely on checks to verify the integrity of a model is never a good idea as checks are usually quite high-level. But it’s a good starting point.
This section covers a couple of very effective Excel best practices for our more advanced users. These might require a bit of adjusting, but should save several hours of work later and be relatively simple to implement. They are as follows, in short, succinct, to-the-point bullets:
XNPV
and XIRR
to allow for the application of custom__ dates to cash flows, en route to a returns analysis; this, as opposed to Excel’s NPV
and IRR
functions, which implicitly assume equidistant time intervals for the calculation.INDEX MATCH
function over the VLOOKUP
function for looking up information across large spreadsheets.VLOOKUP
is almost always superior to IF
statements; get comfortable with it.IFERROR
in the syntax of your formulas.EOMONTH
, and IF
statements to make dates dynamic.Love it or hate it, Excel is omniscient, omnipresent, and omnipotent when it comes to corporate finance, analysis, and data-driven decision-making. And believe it or not, it doesn’t have to be intimidating or painful, even for the novice or uninitiated. Like most things in life, practice, consistency, and attention to detail (an in Excel’s case, shortcuts) will get you most of the way there.
Once you become familiar with the application, you will find it a powerful productivity and numerical storytelling tool, that you will sparsely be able to function without, even in your personal life. As you progress through the various stages of Excel fluency, I wish you the best and encourage you to keep this article as a practical go-to guide that you reference often.
There are nine main types of financial models: (1) three-statement operating models; (2) discounted cash flows (DCFs); (3) merger models (M&A); (4) initial public offering (IPO) models; (5) leveraged buyout (LBO) models; (6) sum of the parts; (7) budgets; (8) forecasting models; and (9) option pricing models.
The exercise of building spreadsheets that detail the historical financial data of businesses, forecast their future performance, and assess their risks-returns profile. Put another way, financial data modeling is the task of building an abstract simulation of real-world financial situations ahead of key decisions. Financial model examples include three-statement operating models, discounted cash flows (DCFs), merger models (M&A), initial public offering (IPO) models, and leveraged buyout models, among others.
To analyze the historical financial performance of a given corporation; assess, project, and forecast its future financial performance, and value companies or specific capital projects, including their risk/reward profile. They are used by the operators of companies to come to data-oriented decisions.
Located in United States
Member since April 4, 2017
Albert is a seasoned PE executive with over $1Bn in trasactions. Prior to this he led consulting engagements across nine countries.
World-class articles, delivered weekly.
World-class articles, delivered weekly.
Join the Toptal® community.