Denice R. Preston
Financial models are now an integral part of the business world. They have evolved from abstract spreadsheets into real-world applications. But despite its widespread use as a decision-making and productivity tool, it still has a love-hate relationship with many.
Alberto Bazzana, a finance expert, has written a “how-to” guide for novices as well as experts, detailing Wall Street’s best practices to create a financial model that is intelligent, accurate, and free of errors.
Introduction: A Financial Model
Financial Models are a vital part of any company’s financial toolkit. These spreadsheets detail historical financial data, predict future financial performance, and assess the risks and return profile of a business. Economic models are usually built around the three statements of accounting: income report, balance statement, and Cash flow statement. EconomMost companies use financial models to make strategic and capital decisions.
This article is a step-by-step guide for novices and intermediate finance professionals who want to build financial models using best practices. This article is aimed at the more advanced financial modeler. It will provide a variety of tips and tricks to maximize time, output, and modeling effectiveness. Let’s begin.
Plan Your Model
As with anything complex, the first thing to do when building a financial (“model”) would be to create a detailed blueprint. It can be difficult to adapt a model if it undergoes unanticipated changes. With a little planning at the beginning of the exercise, such challenges can be easily overcome. Your planning phase should go like this:
Define the end goal of your model
Clarifying the purpose of your model will help you determine its best layout, structure, and outputs. Before you begin building your model, ensure that the key stakeholders have approved your blueprint. It gives them a chance to express any preferences or intentions and avoids any scope creep (industry jargon) or painful re-direction down the line.
Understanding the timelines of both the building and the use of the model is important
Understanding the timeline for the model and the length of time it will be in use are important inputs that can help determine the best approach for the modeling exercise. Models that are built to last a long time and have a high level of sensitivity and flexibility include countless operating details. Modelers often use prefabricated templates to speed up construction and minimize errors for more immediate, shorter-duration operating or Capital-Project models. Model templates are also more familiar to different stakeholders and, therefore, easier to manipulate.
Decide the optimal trade-off for “detail” and “reusability.”
When deciding the optimal trade-off between the 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:
After the blueprint/planning stage is complete and all key decisions have been made, we can now move on to the next phase of modeling.
Model Structure
We are now ready to start structuring our model. Every model should be broken down into three sections at the highest level: (a) inputs/drivers and (b) calculations. (Projected financial statements); and (c) outputs. It is easier to audit the model and make changes if it has been divided into these three sections.
It is a structural approach that I have used for nearly every financial model I have created. Both my stakeholders and I have found it practical, digestible, and useful. The sections of the book are:
Cover page (Tab). Project code, description of model intent, author contact information, and any disclaimers applicable.
Outputs Tab: A neat, clean summary of the main highlights of the model.
Sensitivities tab: The ranges of scenarios, sensitivity, and data outcomes on which management will rely as they transition to their decision-making processes.
Let me break down each section for you one by one. The following:
Cover Page
Cover Page is your first contact with the work. It is easy to create, but when it’s done right, it makes a great impression. The best way to approach this is with a simple instructional page that includes sections such as:
Model Index: A table that explains the purpose and description of each tab. This section is especially useful for non-finance operators. It helps them “digest the model’s flow and structure” by highlighting which checks are needed as inputs, what outputs should be used to make decisions, and which tabs with complex calculations they should leave untouched.
History of Model Versions: Saving a few moments to record the changes you make to the model and the date they were made will save you time in the future, particularly if you have to reverse or modify changes. This is particularly true for models that are complex or models you might use in the future as templates.
Please note that I recommend locking the cover page to all those without the express authority to change it, including the author.
Driver’s tab: Assumptions and inputs
The tab (inputs), which must follow the cover page of the model, should be the next thing to appear. This tab must be clear, concise, and easy to read since it is likely that non-finance personnel will use it most. I recommend having two sections in the inputs tab: one for stat and another for dynamic. Statics are inputs that do not change over time. For example, the “size of a hypothetical power plant” or a “company’s initial debt balance.” Dynamics are inputs that vary over time.
In both the static and dynamic input sections, I recommend you clearly separate your data into two types: (1) fixed figures, which do not change regardless of the assumption scenario, and (b) sensitive parameters, which will drive different assumption scenarios and eventually, your sensitivity table. You will not know for sure which parameters will be sensitivity parameters until you are at the end of the project. Please refer to the article for more information on sensitivity modeling.
Model Tab: Calculations in Detail and Operating Build-up
This tab is the core of the model. It’s where inputs, scenarios, and assumptions come together to predict a company’s financial performance in the future. This tab is where the various assumptions-driven methods and the valuation part of the exercise will be performed before the final strategic decision.
