Advanced Financial Modeling Best Practices: Hacks for Intelligent, Error-free Modeling

Executive Summary

Introduction: A Financial Model

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.

Planning Your Model

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:

1. Define the model’s end goal.

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.

2. Understand the timelines for both building the model and for its useful life.

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.

3. Determine optimal trade-off between “detail” vs. “reusability.”

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.

Structuring Your Model

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:

1. Cover Page (Tab): Project code name, a description of the model’s intent, the author’s contact information, and any applicable disclaimers.
2. Drivers Tab: Inputs and assumptions.
3. Model Tab: Calculations (i.e., the three financial statement projections and calculations).
4. Outputs Tab: A clean, neat summary of the most important highlights of the model.
5. Sensitivities Tab: The range of scenarios, sensitivities, and data outcomes that management will rely on as they transition into their decision-making process.

I will break each of these sections down for you, one at a time. As follows:

Cover Page

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:

1. Name of the Model: Self-explanatory.
2. Purpose of the Model: A paragraph describing its intended use(s)
3. Model Index: A brief table detailing each tab’s description and purpose. This section can be especially helpful to non-finance operators, helping them “digest” the model’s structure and flow by highlighting which tabs they need to use for inputs, which outputs to focus on during decision-making, and which complex calculation tabs they should leave untouched.
4. Model Version History: Investing a few seconds in typing, by date, the key changes made to the model as you go along always saves time down the road, especially if you need to retrace and reverse/modify changes. This is especially true for complex models and models that you may use as templates in future periods.
5. Author’s Contact Information: Self-explanatory
6. Applicable Legal Disclaimers (if any, as provided by your Legal Counsel): Self-explanatory

Please note: I recommend that the cover page always be locked to anyone and everyone without express authority to make changes, outside the author.

Driver’s Tab: Inputs and Assumptions

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.

Model Tab: Detailed Calculations and Operating Build-up

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.

Scenarios and Sensitivities Tab

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:

A Few Notes on the Above Image:

1. The model user should be able to edit only this, as it is where they will select the scenario number. The number refers to one of the scenarios presented on the right side of the spreadsheet. The user will then present the selected scenario (in this case, No. 6) in the first column. This is the only column of the Scenario and Sensitivities spreadsheet that is referenced in the model.
2. Add a couple of description fields here that effectively summarize what the selected scenario represents.
3. I always find it very helpful, especially if someone else will use the model, to add a column that specifies each unit of input.
4. This column pulls in the leverage statistic/field of the selected scenario (in this case, No. 6), which are all displayed on the right (in blue). The formula required to drive it is an offset function, i.e., “=OFFSET (insert empty cell immediately left of the first scenario highlighted in red above ,, Cell where the scenario is selected/highlighted).” Please note that there is an empty space between the two cells so the two commas (,,) aren’t a typo.
5. Group your assumptions in macro-categories and sub-categories. This will help both you (the modeler) and your users gain a clear understanding of which scenario the model has selected.

Output Tab

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:

1. Financial Output Tab: This is an abridged summary of the financials detailed in the model tab. They are usually presented on an annual basis (even though the model may be quarterly). This output should be between 50 and 150 rows and should present all the key line-items from the calculation tabs. Please be sure to present enough detail so as to ensure users are not toggling between this tab and various Calculations tabs. Please also note that, as a financial model best practice, no output tabs should re-perform any calculation and this information should only include direct links in.
2. Executive Summary Tab: This tab is pretty standard and usually presents a mix of graphs, charts and tables, illustrating, as simply and as easily digestible as possible, the various trends, analyses and key summary statistics that executives and board members require to navigate their key decisions.
3. Specific Output Tab: This tab contains specific outputs, usually dictated by the template of the investment memo, the investment committee presentation, or requests by executives and board members as required to reach their decision points.

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.

Formatting Your Model

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):

1. Hit Alt + W + M + R, concurrently, to name and begin recording your macro.
2. Hit F5 (“Jump to Cell”) and then Alt + S, concurrently, to arrive at the “Go to Special” menu.
3. Hit “O” to select Constants and “X” to uncheck text.
   

   

4. Now hit Alt + H + FC (or Ctrl + 1), concurrently, and select your blue font color for these constants.
5. Hit Esc.
6. Now do the same thing, starting with F5, but select Formulas (F) instead of constants and press “X” to uncheck text.
7. Now hit Alt + H + FC (or Ctrl + 1) and select a Black font color for these constants.
8. Stop recording the macro with Alt + W + M + R or Alt + T + M + R.

Links to Other Workbooks

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).

Best Practices for Auditing a Model

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:

1. One Row, One Formula

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:

2. No Hard-coded Numbers Embedded Within Formulas

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.

3. Simple Is Always Better

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.

4. Adhere Consistently to Your Sign Convention

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.

5. Avoid Naming Your Cells, Instead Rely on Excel’s Grid Logic

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).

6. Never Have the Same Input in Multiple Locations

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.

7. Avoid Linking Files

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.

8. Don’t Hide Sheets or Rows

Within longer spreadsheet, “group” rows/columns rather than “hide” them.

9. Fewer, Bigger Tabs Are Better Than Multiple Smaller Tabs

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.

10. Create Checks Throughout Your Model via “Aggregated Error Checks” Located in One Tab

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.

Carve Out – For Advanced Users: Excel Tips

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:

1. Use as many keyboard shortcuts as possible. There are several files in the internet on Excel Keyboard shortcuts that fit different needs. I’ll reference a couple here:
   • Modeling Key Cheat Sheet
   • Comprehensive Excel Tips List
2. Use F5 (“go to special”) to quickly locate all hard-coded numbers or formulas.
3. Use Trace Precedents and Trace Dependents to audit the model.
4. Use 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.
5. Use the INDEX MATCH function over the VLOOKUP function for looking up information across large spreadsheets.
6. VLOOKUP is almost always superior to IF statements; get comfortable with it.
7. Get in the habit of including IFERROR in the syntax of your formulas.
8. Use a combination of the date function, EOMONTH, and IF statements to make dates dynamic.
9. Remove gridlines when presenting or sharing the financial model; it makes for a cleaner, more polished output document.

Love It or Hate It…

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.