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.
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 |
If you couldn’t check all the boxes or are unsure about any item(s) on the checklist, your spreadsheet may be sinking. Don’t worry! There’s a life raft. Keep reading to learn more about formula writing and how to keep your model afloat.
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:
If you’re using a traditional spreadsheet…
- Keep actuals separate from calculations and input cells*
- Use named ranges to help simplify your spreadsheet formulas*
*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!
Want to learn more?
We would love to hear from you! We offer personalized live demos where our experts can show you how Quantrix can solve your modeling and planning problems. You can also take advantage of our free 30-day trial.
