How To Link Quarterly Financial Data Into Excel

Executive Summary

What Are Recommended Strategies for Building Fiscal Models?

Every bit with all endeavors cumbersome and complex, begin with a sound, thoughtful blueprint for the model. Understand, every bit part of this process, the timeline for edifice the model and its expected useful life, likewise as the desired tradeoff between "reusability" and "model-granularity".
Next, structure and fabricate your model carefully. At a minimum, pause it downwardly into three sections: (a) inputs/drivers, (b) calculations, (the actual model, which will illustrate the projected financial statements), and (c) outputs.
Finally, build the model and take the time to format it for a clean, consistent, professional finish.

The Summit Tricks and Tips for User-friendly, Smart, Fault-Free Modeling

I'll testify/share with you some fourth dimension-tested hacks for creating an error-free model. This will include some of my personal credos such equally, "One Row, One Formula", and rules like "No Difficult-coded Numbers Embedded Within Formulas". Additionally, I'll show you how to create checks throughout your model via Aggregated Error Checks.
The importance of pre-structuring your model. I'll walk you through the most intuitive fashion to setup your model such that it makes intuitive sense in terms of formula flow as well equally ease-of-audit and handover.
Color formatting is a must. It sounds simple only is an extremely effective tool that instruct novice model users what inputs mean what. Read on to meet how to quickly setup a macro to automate this procedure.
Good old-fashioned Excel formula shortcuts. This section covers a couple of very effective Excel best practices for our more advanced users. These might crave a bit of adjusting, but should save several hours of work later and be relatively elementary to implement.

How Tin can a Finance Expert Help You/Your Company?

By working alongside you as a thought partner to blueprint, construction, build, and deliver a range of polished models or budgets for pre-mandated specific projects, purposes, or decisions.
By creating a prefabricated, multi-tab become-to model template that can exist adapted uniquely past almost anyone, for any purpose, beyond your organization.
By designing specific outputs and running complex sensitivity analyses using Excel en route to a management-level, board-level, or operator-level strategic decision.
By building or creating templates for every sort of fiscal model type, including "how-to" instructions, ranging from discounted cash flow (DCF) model and leveraged-buyout, to mergers and acquisitions or cash-period models.
By training individuals or groups of individuals inside your organization on everything from the nuts of modeling to advanced quantitative methods.

Introduction: A Financial Model

Financial models are an indispensable part of every company'southward finance toolkit. They are spreadsheets that item the historical fiscal data of a given business, forecast its future fiscal performance, and assess its risks and returns profile. Financial models are typically structured around the three financial statements of bookkeeping—namely: income statement, balance sheet, and cash flow statement. The direction of nearly corporations rely, at least in part, on the details, assumptions, and outputs of fiscal models, all of which are disquisitional to said companies' strategic and capital controlling processes.

This commodity serves as a step-past-step guide for the novice and intermediate finance professional looking to follow expert all-time-practices when building financial models. For the advanced financial modeler, this article volition 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 offset stride to building a financial model ("model") is to carefully layout a pattern. Unplanned, unanticipated structural changes midway through a modeling practice can be time-consuming, confusing, and error-decumbent, particularly if the model's adapter is not the same equally its author. Such challenges are easily subverted with a scrap of devoted planning time at the onset of the exercise. I recommend that your planning stage 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 role of this process, take the fourth dimension to ensure that your model'southward key stakeholders sign off on your design and process design before starting to build. This gives them the opportunity to voice whatsoever final preferences or intentions, thus avoiding any "scope creep" (industry parlance) or painful redirection downwardly the road.

ii. Empathize the timelines for both building the model and for its useful life.

Though secondary to the model's end goal, understanding the timelines for edifice the model and how long the model will be used for are also important inputs to determining the approach to the modeling practice. Long-duration and long-tenured (useful-life) models are typically custom congenital from the ground up and include tremendous amounts of operating item, flexibility, and sensitivity capabilities. For more firsthand, shorter duration operating or capital-projection models, modelers volition oftentimes employ prefabricated templates to maximize speed of structure while minimizing errors. Farther, model templates besides tend to exist more familiar and thus easier to utilise/dispense by dissimilar stakeholders within organizations.

3. Determine optimal merchandise-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 only this ane-off do), a useful framework for deciding on one's model choice/approach, which I accept followed through most of my career, is as follows:

Graphic representation of the trade-off between level of detail vs. reusability

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 information technology volition be to audit and ameliorate the model while minimizing errors and optimizing on time.

I accept followed the aforementioned structural approach for most every model that I accept congenital; an approach which both my respective stakeholders and I have e'er establish practical, digestible, and ultimately useful. Its sections are as follows:

Cover Page (Tab): Projection lawmaking name, a description of the model's intent, the author's contact data, and any applicative disclaimers.
Drivers Tab: Inputs and assumptions.
Model Tab: Calculations (i.eastward., the three financial statement projections and calculations).
Outputs Tab: A make clean, bang-up summary of the nearly important highlights of the model.
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 interruption each of these sections down for you, one at a time. As follows:

Cover Page

The cover folio is the first point of contact with your work. While information technology is the simplest to build, when done well, it leaves a neat first impression and conspicuously explicate what is to come up. A simple, instructional cover page is generally the best approach and typically includes the post-obit sections:

Name of the Model: Self-explanatory.
Purpose of the Model: A paragraph describing its intended use(s)
Model Index: A brief tabular array detailing each tab's description and purpose. This section can exist especially helpful to non-finance operators, helping them "digest" the model'due south structure and flow by highlighting which tabs they need to employ for inputs, which outputs to focus on during controlling, and which complex adding tabs they should leave untouched.
Model Version History: Investing a few seconds in typing, past appointment, the central changes fabricated to the model equally you keep always saves fourth dimension down the road, especially if yous need to retrace and opposite/modify changes. This is especially true for complex models and models that you may employ every bit templates in hereafter periods.
Author's Contact Information: Self-explanatory
Applicative Legal Disclaimers (if whatever, as provided by your Legal Counsel): Self-explanatory

Please annotation: I recommend that the cover folio always exist locked to anyone and everyone without limited authority to make changes, outside the writer.

Driver'south Tab: Inputs and Assumptions

Immediately following the model'due south comprehend page, must come the drivers (inputs) tab. Y'all must ensure that this tab is clear, concise, and easy to understand, as this is the tab that non-finance operators volition probable manipulate near 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 alter over time, such as the hypothetical "size of a power plant" or "a company'due south starting debt balance"; and past dynamic inputs, I mean inputs that are variable over time (east.g., month-to-month, or year-to-year) such equally "inflation" assumptions, "cost of debt," or "revenue growth" assumptions.

Example of a Sample Drivers and Assumptions Tab — Source: Toptal

Inside both of the higher up static vs. dynamic input sections, I recommend that you besides 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 supposition scenarios and ultimately your sensitivity tables. Note, still, that you never fully know which parameters are going to constitute sensitivity parameters and which you volition not until the terminal stages of the project. For more than 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 piece of work together to projection a visitor's fiscal performance into its outer-years. It is also out of this tab that various supposition-driven scenarios will be run every bit well equally the valuation piece of the exercise that volition be conducted ahead of the final strategic decision.

Example of a Sample Model Tab — Source: Toptal

Scenarios and Sensitivities Tab

Authorized, 3rd-party model operators volition use the Scenarios and Sensitivities tab fairly ofttimes, even if just to select their choice of pre-programmed scenarios. For this reason, you should build scenarios intuitively, protect the bodily scenarios from outside editing, and build sufficiently varied sensitivities such that the scattering of pre-programmed scenarios will be sufficient to yield a wide view of possible outcomes once sensitivity tables (sample beneath) are also built.

For your consideration, the scenarios format structure I have relied on throughout my career is as follows, every bit only ane blazon of case:

Example of a sample scenarios and sensitivities tab

A Few Notes on the Above Epitome:

The model user should exist 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 correct side of the spreadsheet. The user will so present the selected scenario (in this instance, No. 6) in the first cavalcade. This is the only cavalcade of the Scenario and Sensitivities spreadsheet that is referenced in the model.
Add together a couple of description fields here that effectively summarize what the selected scenario represents.
I always observe it very helpful, specially if someone else volition use the model, to add a column that specifies each unit of input.
This column pulls in the leverage statistic/field of the selected scenario (in this case, No. 6), which are all displayed on the correct (in blue). The formula required to bulldoze it is an offset function, i.e., "=Get-go (insert empty prison cell immediately left of the starting time scenario highlighted in red higher up ,, Cell where the scenario is selected/highlighted)." Please notation that there is an empty infinite between the two cells and then the ii commas (,,) aren't a typo.
Group your assumptions in macro-categories and sub-categories. This will assistance both you (the modeler) and your users gain a articulate agreement of which scenario the model has selected.

Example of a Sample Sensitivity Table — Source: Toptal

Output Tab

The output tabs are the tabs that operators of the model volition use nigh frequently. Over the years, I've found myself leaning towards at least three output tabs for mid-to-complex models:

Financial Output Tab: This is an abridged summary of the financials detailed in the model tab. They are commonly presented on an almanac basis (even though the model may be quarterly). This output should exist between 50 and 150 rows and should present all the primal line-items from the adding tabs. Delight be sure to present enough detail and so as to ensure users are non toggling between this tab and various Calculations tabs. Please besides notation that, as a best practice, no output tabs should re-perform whatever calculation and this information should only include straight links in.
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 diverse trends, analyses and primal summary statistics that executives and board members require to navigate their key decisions.
Specific Output Tab: This tab contains specific outputs, usually dictated by the template of the investment memo, the investment commission presentation, or requests by executives and board members as required to reach their conclusion points.

Example of a Model Output Tab, Including Tables, Charts, and Graphs — Source: Alberto Mihelcic Bazzana

At this juncture, the construction stage of the model is officially complete. We may turn our attention to some of the expert-level modeling best practices I referred to at the onset of the article. Let's begin with formatting.

Formatting Your Model

First, it is of import to note that each business firm/group might have its ain preferences or internal practices. As such, while building, it is important to beginning check in with—and adhere to—whatever format your respective firm prescribes. In the absenteeism of house-specific practices, still, the content below details Wall Street's universal language for formatting a model.

The offset 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:

Blueish = Inputs, or any hard-coded information, such as historical values, assumptions, and drivers.

Black = Formulas, calculations, or references deriving from the same sail.

Green = Formulas, calculations and references to other sheets (note though that some models skip this step altogether and employ blackness for these cells).

Regal = Links, inputs, formulas, references, or calculations to other Excel files (again, note that some models skip this pace altogether and utilize blackness for these cells also).

Red = Error to be fixed.

Example of Well-formatted (Color-coded) Financials Summary — Source: Toptal

Please note that in that location is no in-built automation functionality to color lawmaking your Excel spreadsheets according to the universal color coding standards above. Instead, y'all may design your own macro(s) to achieve these outcomes, and subsequently create shortcut combinations to automatically color-code your work.

Onetime in my recent past, I received from a colleague (who I thank to this day), the following macros (including detailed instructions), which accept since saved me several hours of transmission labor. I'd similar to share them, if I may.

Macro creation instructions (for both Mac and PC versions of Excel):

Hit Alt + W + M + R, concurrently, to proper name and begin recording your macro.
Striking F5 ("Spring to Cell") and so Alt + S, concurrently, to arrive at the "Go to Special" card.
Hitting "O" to select Constants and "Ten" to uncheck text.
Now hit Alt + H + FC (or Ctrl + 1), meantime, and select your blue font color for these constants.
Hitting Esc.
Now do the same affair, starting with F5, only select Formulas (F) instead of constants and press "X" to uncheck text.
Now striking Alt + H + FC (or Ctrl + one) and select a Black font colour for these constants.
Finish recording the macro with Alt + W + M + R or Alt + T + K + R.

Links to Other Workbooks

Finding links to other workbooks and worksheets is catchy, and y'all will most probable have to use VBA to become this working correctly. Here's the bones thought: search for the presence of the symbol "!" in each cell that contains a formula across your workbook, and and so alter the font colour to green. You volition need to change this in the VBA Editor and make it a for each loop through all instances of "!" you find, and and then change the font color for each of these.

Please be aware that this shortcut still won't piece of work 100% of the fourth dimension because some formulas will reference cells in other worksheets without straight linking to them. Fortunately, greenish cells are rarer than blackness or blueish cells, so the method above works fairly well in about models (and you can organically format the rest of your links to other worksheets manually as they come upward or equally y'all come across them).

All-time Practices for Auditing a Model

When modeling, I encourage you to always carry this single question at the back of your heed: "Am I making this model easily auditable?" because for every task executed, formula created, and link congenital, there will always be a faster, "dirtier" (in industry parlance) style to exercise the task. Such hacks and tricks, even so clever they may seem at the fourth dimension, and especially after time intervals, volition invariably be forgotten and will lead to hard-to-track-down errors. Keeping a 3rd-person reviewer in mind will guide you through your procedure and help you come up to the correct decision at key junctures.

Beneath are a series of best practices on how to build with an auditor mindset. Every bit follows:

ane. I Row, One Formula

Y'all should have but one formula per row, meaning that whatever formula is used in the first prison cell of any given row should exist the aforementioned formula uniformly applied across the entire row. Users should understand the structure of your model by looking at the commencement prison cell of each row as they go along 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 divide between a "historical financials" group of columns and "outer-year forecasts" (see epitome above entitled, "Example of Well-formatted (Colour-coded) Financials Summary," as a reference).

1 easy fashion to accost these instances, is the use of flags (e.g., one/0, True/Imitation) positioned at the top of the spreadsheet, then referenced using IF statements through the body of 1'due south model. A simple analogy of this at piece of work is as follows:

Example of 'The Use of Flags' in Excel Modeling — Source: Alberto Mihelcic Bazzana

2. No Difficult-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/difficult-codes from the formulas; better nevertheless, gather all the inputs/hard-codes (as appropriate) and aggregate them in the same tab. Subsequently have your formulas pull/reference them as advisable from the required prison cell and from the advisable tab.

three. Simple Is Always Better

It is ever 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; simply one that will be much easier to follow and audit by a third political party.

4. Adhere Consistently to Your Sign Convention

You lot should decide at time-zero what your sign convention/key will be. Past way of illustration, enquire yourself in the design stage of your model, "Will costs, expenses, deductions, depreciation, CapEx, etc. exist presented every bit negative or positive numbers?" My personal preference is to always present costs as negative numbers for 2 reasons: (a) The totals volition always be straight sums and y'all will minimize user error, and (b) it will be easier to spot mistakes using but 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 prison cell (e.g., "Aggrandizement") down the road. Instead, I recommend that yous rely on the grid convention of Excel within your formulas (e.thou., simply linking to jail cell C4 or location, [Tab Name]50'!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 lot consolidate all inputs in a few commuter tabs and reference them from their singular points of origin throughout the spreadsheet.

vii. Avoid Linking Files

Avoid linking to other files. It is better to input the relevant information y'all require from a different file equally hard-coded inputs, which you and then manually update every bit required. Cantankerous-linking has been known to crash larger Excel models or update inconsistently, thereby creating hard-to-rails errors.

8. Don't Hide Sheets or Rows

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

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

This practise is based 100% on experience. It is easier to follow and audit a continuous assortment for data across one large, face-to-face spreadsheet, than across multiple tabs or, worse, multiple spreadsheets that are cross-linked.

ten. Create Checks Throughout Your Model via "Aggregated Error Checks" Located in One Tab

Checks are the easiest style to quickly review the integrity of a model. "Checks" encompass everything from ensuring that totals that should necktie really exercise 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 detect an fault in the model then trace where that fault originated.

Sample of a Balance Sheet Check — Source: Toptal

Please note that relying solely on checks to verify the integrity of a model is never a skilful idea as checks are usually quite high-level. But it'south a good starting point.

Carve Out – For Advanced Users: Excel Tips

This department covers a couple of very effective Excel best practices for our more advanced users. These might require a chip of adjusting, but should save several hours of work later and be relatively elementary to implement. They are as follows, in short, succinct, to-the-point bullets:

Use every bit many keyboard shortcuts as possible. There are several files in the internet on Excel Keyboard shortcuts that fit dissimilar needs. I'll reference a couple here:
- Modeling Central Cheat Sheet
- Comprehensive Excel Tips List
Use F5 ("go to special") to apace locate all hard-coded numbers or formulas.
Use Trace Precedents and Trace Dependents to audit the model.
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 adding.
Employ the Alphabetize Match part over the VLOOKUP function for looking up information across large spreadsheets.
VLOOKUP is near always superior to IF statements; get comfy with it.
Go in the habit of including IFERROR in the syntax of your formulas.
Use a combination of the appointment part, EOMONTH, and IF statements to make dates dynamic.
Remove gridlines when presenting or sharing the financial model; information technology makes for a cleaner, more polished output document.

Love It or Hate It…

Love it or hate it, Excel is omniscient, omnipresent, and almighty when it comes to corporate finance, assay, and information-driven decision-making. And believe it or not, it doesn't accept to be intimidating or painful, fifty-fifty for the novice or uninitiated. Like most things in life, practice, consistency, and attention to item (an in Excel's case, shortcuts) will get you about of the style there.

In one case you lot become familiar with the application, you will notice information technology a powerful productivity and numerical storytelling tool, that y'all will sparsely be able to function without, even in your personal life. As y'all progress through the various stages of Excel fluency, I wish you the all-time and encourage you to keep this article as a applied go-to guide that you lot reference often.