Modeling Principles

OVERVIEW

Gain a quick overview of best practice financial modeling in this extract from a Pivotal180 course.

Although in many ways obvious, these concepts are a useful reminder for experienced analysts and a priority for new analysts to learn.

Video

Video Transcript

A financial model is a decision making communication tool which will be used and reviewed by many people.

There is no point in building a financial model that can’t be understood by all the users of the model.

A good best practice financial model needs to have the following characteristics, which will make it easier to read and to be reviewed. It needs to be clear and concise, simple to use, and robust and flexible. A clear and concise model is one that is well presented, only includes relevant inputs. We do not want to confuse the users with unnecessary information.

The model also needs to be simple to use and transparent in order to assist the review process. And most importantly, making it easy to identify mistakes. The model will be robust and flexible, allowing changes to be made within the model and the outputs to be updated effortlessly.

There are also rules that need to be applied as best practice in a financial model. Let’s look at three of Pivotal’s 10 rules of financial modeling.

Rule one. The inputs, calculations, and outputs are all separated by using different tabs within the Excel spreadsheet. This ensures that the inputs are only entered once and all the assumptions and data are entered in one place within the model.

The calculations, which are in a separate tab, are the engine room of a model, and this is where all the formulas are stored.

The last tab would be the outputs tab. This is where the results of the financial model are found, such as financial statements, ratios, graphs, and sensitivity tables. This use of separate tabs assist the user to navigate throughout the model and to understand where to make changes to the inputs, amend the calculations, or to analyze the outputs.

Another rule of financial modeling is it needs to have a consistent use of cell and number formats. What does this mean? All the inputs, calculations, and outputs of the model are clearly identifiable through cell styles and cell formats. We use specific backgrounds and font colors for each type. In addition to this, any headers of tables also need to have their own distinct cell format. This use of consistent number formatting greatly enhances the model and provides further consistency. This includes using units on all numerical terms with exactly the same format throughout the model. It’s not important which format you use as long as you are consistent. For example, a zero could be formatted in a cell as a dash, a zero, or a blank.

Another best practice rule is to keep formulas simple and constant across the rows.

To ensure a model can be understood by every level of user, it is very important not to overcomplicate the calculations within the model. The simpler the calculations, the more clearly it can be read and reviewed. For example, the use of simple formulas, such as if, sum, multiplication may enhance your model, but on the other hand, formulas such as offset, index match, and V lookup may add unnecessary complexity. A financial model is used for decision making by many different levels of user. By keeping things simple and following a few basic rules, it becomes so much easier for other people to understand your work.