Improving Your Modeling Techniques as a Finance Professional
Updated: Nov 16, 2022
The quality of your modeling is a critical component of any finance professional’s work, and each step of model building has a small margin for error. For the reason of this task’s importance, finance teams should know how to maximize their approach to modeling. The best practices constitute a solid planning phase for financial modeling, a thorough understanding of how to best structure your models, and what resources and strategies you can use to be more efficient in the modeling process. The following outline describes these practices you can use to your advantage.
Planning Your Models
The first step in creating a financial model, before even opening Excel, or any application, is to carefully lay out a blueprint. Unplanned, unanticipated structural changes midway through a modeling exercise can be time-consuming, confusing, and error-prone, especially if the one improvising model adapter isn’t the original author. You can evade such obstacles with a small amount of time committed to planning at the onset of the exercise. The following is an effective strategy in planning your models:
1. Define the model’s end goal:
Any quality model must start with a clearly defined purpose, in order to determine 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 the 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.
Structuring Your Models
Following the planning phase, you will be 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 you are at organizing your models based upon such sections, the easier it will be to audit and amend it, while minimizing errors and optimizing on time. The sections are five as follows:
Cover Page (Tab): This includes the project code name, a description of the model’s intent, the author’s contact information, and any applicable disclaimers.
Drivers Tab: Inputs and assumptions
Model Tab: Calculations (i.e., the three financial statement projections and calculations)
Scenarios & Sensitivities Tab: The range of scenarios, sensitivities, and data outcomes that management will rely on as they transition into their decision-making process.
Outputs Tab: A clean, neat summary of the most important highlights of the model.
1. Cover Page:
The cover page is the first point of contact with your work, or its first impression. This can leave a critical impact on how a viewer perceives the rest of your model. A simple, instructional cover page clearly explaining what to expect is generally the best approach and typically includes the following sections:
Name of the Model
Purpose of the Model
Model Index
Model Version History
Author’s Contact Information
Applicable Legal Disclaimers (if any, as provided by your Legal Counsel).
2. Driver’s Tab:
Following the model’s cover page is the drivers (inputs) tab. This tab should be clear and concise, because it is often the tab that non-finance operators manipulate the most. A good set up is to implement two input sections within the inputs tab, one for “Static Inputs”, and the other for “Dynamic Inputs”. “Static Inputs” don’t change over time. This could be a company’s starting debt balance, or the hypothetical size of a power plant. Dynamic inputs are mean inputs that vary over time, be it on a year to year or month to month basis. Such inputs could be “inflation” assumptions, “cost of debt,” or “revenue growth” assumptions. An example of the driver’s tab is as follows:
An effective technique in the driver’s tab is to clearly separate your data into two kinds:
Hard-coded figures that don’t change irrespective of assumptions scenario
Sensitizing parameters that will drive different assumption scenarios and ultimately your sensitivity tables.
Note, however, that it isn’t possible to determine precisely which parameters are going to constitute sensitivity parameters and which will not until the final stages of the project.
3. Model Tab:
The model tab is essentially the meat and potatoes section, where all the assumptions, inputs, and scenarios work together to project an organization’s financial performance into its outer-years. This tab also shows various assumption-driven scenarios that will be run, as well as the valuation piece of the exercise that will be conducted ahead of the final strategic decision.
4. Scenarios and Sensitivities Tab:
Authorized, third-party model operators will use the Scenarios and Sensitivities tab fairly often, even if just to select pre-programmed scenarios. Because of this, you should create scenarios intuitively, protect the actual scenarios from outside editing, and build sufficiently varied sensitivities such that the handful of pre-programmed scenarios will be enough to yield a wide view of possible outcomes once sensitivity tables are also created. Below is an example of this sort of tab for the sake of reference:
5. Output Tabs:
The output tabs are the tabs that operators of the model will use most frequently. A popular way to segregate such tabs is by creating 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. Typically this is presented on an annual basis, even on quarterly models. This output should be between 50 and 150 rows and present all the key line-items from calculation tabs. This tab should present enough detail in order to ensure users are not struggling between this tab and various calculations tabs. Additionally, no output tabs should re-perform any calculation and this information should only include direct links in.
2. Executive Summary Table:
This tab is very standard and usually presents a mix of charts, tables, and graphs illustrating as simply 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.
Shortcuts & Improving Efficiency When Modeling
Modeling, like any Excel task, can be very time consuming. There are two ways to be more efficient in your modeling: integration and proper use of FP&A software, and Excel shortcuts. A couple of the most effective shortcuts in Excel are as follows:
1. Select Multiple Sheets:
If you have several worksheets that have the exact same format, you can select and make changes to several of them at a time. To do this, hold down the control and shift buttons, and press the page up or down keys in the direction of the adjacent worksheets.
For example, if you have three worksheets that are identically formatted right after one another, you can select the last sheet in the group, hold control and shift, and press the page up key twice. Alternatively, you can hold the shift button and click the last member of the group.
In the scenario, you can select the first sheet in the group, hold shift, and click the third and last worksheet to select all. From here, you can make changes to formulas and cell contents directly across all three sheets.
Note: How you line up the format and positioning of cell rows and columns across all three sheets is critical. All the changes you make in Cell D39, for example, will happen to cell D39 across all the sheets. This means that if Cell D39 contains a different piece of data or formula in one of the sheets, your model may run into errors.
2. Go To Special:
Go To Special: Pressing F5 brings up the “Go To” menu. At the bottom left-hand corner of the pop-up box, there is a “Special…” button. Pressing this shows the options about certain types of data – for example, comments, constants, formulas, and blanks. Selecting any of these will command Excel to highlight all the cells that contain any other instances of the same.
For example, selecting “Constants” and pressing OK will highlight all the non-blank cells in your model that contain hard-coded, constant data. This is useful if you want to format or color all the cells in your model that are not formulas.
