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: the users. There is no point in building a financial model that cannot be understood by all the users of the model.

A good financial model needs to have the following characteristics which will make the model 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, includes relevant inputs, because we don’t want to confuse the users with a whole pile of unnecessary information. It needs to be simple to use and it needs to be transparent, which will assist the review process and most importantly, you make mistakes easier to be identified. It will be robust and flexible. This is allowing changes to be made within the model and within the outputs to be updated effortlessly.

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

One of the rules is to separate inputs, calculations and outputs. The inputs are manually entered into a model and they’re separated from the calculations in the outputs by using different tabs. This ensures that the inputs are only entered once and all the assumptions and data is 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 held necessary to obtain the outputs.

The last tab would be the outputs tab which maintains the results of the financial model such as the financial statements, ratios, graphs and sensitivity tables. This use of separate tabs is assisting the user to navigate through the model and to find out where to make changes to the inputs, amend the calculations and to analyze the results in different ways.

Another rule of financial model is it needs consistent use of cell and number formats, so what does this mean? All the inputs, calculations and outputs of the model are clearly identifiable. We can use specific backgrounds and font colors to for each type. In addition to that, any headers of tables also need to have their own distinct cell format.

The use of this consistent number formatting greatly enhances the model and provides further consistency. This includes using units on all numerical terms and the same formats throughout the model. It’s not necessarily important which format you use as long as you’re consistent. For example, a zero could be formatted in a cell as a dash, a zero or even a blank. The next rule that I want to talk about is keep formula simple and constant across the rows.

To ensure the model can be understood by every level of user, It’s important not to over complicate 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. On the other hand, use a formula such as offset, index, match, and V look up, may add an unnecessary complexity.

A financial model, again, is going to be used for decision making by many different levels of users. By keeping things simple and following these basic rules, it’ll be much easier for other people to understand your work.