# Best Practice Formulas

By Haydn Palliser | March 13, 2024

### OVERVIEW

Gain some tips and tricks  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

In this video, we’ll discuss Pivotal180’s best practice guidelines for writing formulas into financial models. One key best practice for modeling is keeping the models’ inputs, outputs, and calculations on separate sheets. The best practices for formulas that we’ll cover in this lesson are most relevant to the calculation sheet, which is kind of the engine room of the model. It’s crucial that the formulas are not just accurate, but also easy to understand. And the two main goals of these best practices are, one, to ensure users and reviewers know what the formula is calculating, and, two, to easily identify errors. Let’s start by taking a look at an example of a bad formula. Ironically, it’s easier to identify best practices when they aren’t used at all. This formula calculates EBITDA, or earnings before interest, tax, depreciation, and amortization. For our example, EBITDA is essentially just revenue minus costs. Now, some of you might see this formula and think, “Wow, I have no idea what this formula is doing. “The modeler must be really advanced.” However, I’ll tell you upfront that this is really a pretty poorly constructed formula, so let’s see what Bastian thinks. Bastian, what can you identify in this formula that’s not ideal?

-Well, for starters, I also don’t know what this formula is doing. It’s very unclear. It basically stretches all the way across the screen and has more components that I can count. We want to keep formulas short and simple. This formula is also linking to three different tabs. First, it links to a cell in the Price tab, then to a cell in the Volumes tab, and then some cells in the Cost tabs. What does Price!T341 even mean? Linking formulas across sheets make them hard to trace. Ideally, your formulas should only reference cells in the current tab. Another issue is that the formula references cells in multiple columns, both Column T and Column I. This is a potential red flag because we want to ensure formulas are referring to the correct time periods. The model should be structured so that every sheet has the same timing resolution. This means that if Column F refers to January through March, 2023, Column F should correspond to that period in every other sheet in the model, with the exception of some annual summary sheets. It’s then much easier to confirm your formula is referring to the correct timing period. Also, notice that the formula is multiplied by 1/10th at the end. This is called a hard code, and it’s definitely not best practice. We don’t know what 1/10th means or why it’s in the formula. Do not hard code numbers into your formulas, with the possible exception of some ones and zeroes. Instead, create another cell labeling the number, in this case, 1/10th, and reference that cell in the formula. Lastly, this row does not have a total column. We almost always want to build in a column to the left that sums the cells in that row. This also helps us identify errors. Now, remember, this formula might 100% accurate. The problem is that users will have a difficult time understanding it and identifying potential errors.