# Financial Modeling: Formula Best Practices

## No more “Battleship” formulas

Introduction

Writing and managing spreadsheet formulas can be a tall task. That task becomes even more challenging when your model expands in size. Adding new products, years or regions to your spreadsheet can dramatically increase the cell count and the formula count. This article will provide you with some best practices to keep your models and formulas in check.

Why are formulas so important?

Your formulas determine the accuracy and scalability of your models. Over the years we’ve talked to customers and prospects about their modeling concerns. Many of these conversations revolved around formulas. For that reason, we developed a Spreadsheet Best Practices Checklist.

 Checklist Item Completed? 1 The number of formulas in my workbook is reasonable* ? 2 I can maintain and update my model at a moment’s notice ? 3 I can audit each of my formulas to ensure they are working correctly ? 4 If I share my model, I am confident that the data will remain unaltered ? 5 There is only one version of the truth ? 6 The size of my data is not impacting my performance ? 7 The structure of my model is easy for others to comprehend ? 8 The person who created the spreadsheet still works at my company ? Total

What are “Battleship” Formulas?

In the Battleship boardgame, players place toy battleship pieces into a grid. They then take turns calling out grid coordinates hoping to hit their opponent’s ship. Similar to the Battleship boardgame, your spreadsheet uses grid coordinates to write formulas. Beneath the surface, each of these formulas leave you guessing: what’s F4 or B7? One cell change or error can sink your entire model. Worst of all, it may be very difficult for you to figure out what went wrong.

GENERAL TIPS:

1. Keep actuals separate from calculations and input cells*

*These options, however, are just workarounds and do not fully resolve the core problem with spreadsheets: Formulas are stored within cells.

RESOLUTION: Scrap Spreadsheets, Use a Financial Modeling Tool

While traditional spreadsheets have workarounds, they lack a true permanent solution. Luckily, there are spreadsheet alternatives, like Quantrix, designed to keep formulas manageable.

Let’s take a look at how Quantrix formulas work.

Quantrix formulas are completely separated from the cells. In the image below, you can see the calculated cells at the top, but the formulas can be read and edited in a separate pane beneath. Even as your model grows, your formulas won’t break.

Introduction to Real Language Formulas:

If you’ve used traditional spreadsheets, you’re most likely familiar with the coordinate-style of formula writing. For example, you click in a cell and type “= B7 – C7.” Then, depending on your model, you will copy or drag your formulas across an array of cells. With Quantrix, you can forget about these “Battleship” coordinates and start writing with natural language. In addition to being much more easily understood, these formulas do not need to be copied or replicated. In the image below, you can see some Real Language Formula examples. You can immediately understand what these formulas are calculating.

Example: Gross Profit = Revenue – COGS

Best practices are easier to follow when they’re built-in to the product. Quantrix was designed to do just that.

If your spreadsheets are breaking, it might be time to jump ship and try Quantrix!