spreadsheet

In May 2014, I launched Sapling’s blog with a post entitled “10 Model Building No-Nos”. Almost two years later, I’ve seen a raft of additional models developed by internal accountants/analysts and external consultants. Many of them left me scratching my head, while a few have shown me innovative ideas and new ways of doing things that we’ve begun to incorporate into our own practices.

As a quick refresher, here were the 10 no-nos I originally offered:

  1. Pasted values
  2. Failing to differentiate between inputs, formulas and outputs
  3. Different components on different worksheets
  4. Not showing months/years
  5. Showing years, not months/quarters
  6. Inputs in many places
  7. Unformatted numbers
  8. No summary
  9. No p’s and q’s
  10. Overly complex formulas

These all remain true, and if you use modelling at least some of the time in your professional practice and haven’t read my original post, I’d highly recommend that you do so. But as I’ve gone through additional client models since my original post, I’ve seen more things that reduce the effectiveness and credibility of models.

1. Spacer columns between periods:

Often, probably for formatting purposes, modellers will insert narrow blank columns between the periods (months, quarters or years) that they are modelling. While I can appreciate that there may be a level of aesthetic merit to this, it shatters the ideal of a model that can be re-constructed instantaneously by “filling right” from about three or four columns in. Furthermore, it is possible to forget to insert a blank column, or to insert one too many, meaning that the probability of errors when linking to these columns increases notably.

2. Other columns between periods:

Other times, modellers will insert summary columns between periods. These could be the total values for a year, or an “actual” column with a “delta” column beside it. While these types of columns add a form of convenience to the model, they also break from the ideal. We much prefer to have all of the modelling done on a single, homogeneous tab that contains only the same type of column going from left to right. These can then be linked to (directly, or with SUMIFs, INDEX-MATCHes, etc.) on another tab, where summaries and deltas can be shown more effectively.

3. Individually entered links to inputs by period, instead of INDEX-MATCH or H/VLOOKUPs:

When linking your formulas to input cells, especially if you’re modelling on a monthly basis but entering inputs on, say, a quarterly or annual basis, there are two broad categories of methods of doing this. The first involves directly linking to the inputs, possibly with an absolute reference (e.g., =$A$4). The second, which I greatly prefer, employs INDEX-MATCH, VLOOKUP or another lookup function. Using the latter assists with holding up the ideal of the “fill right” model while reducing the possibility of linking to the wrong cell. It also makes it easier to add to the model later.

4. No shading, or worse, ugly shading:

Some models I’ve seen have almost no shading (background colouring) whatsoever; one, in fact, had absolutely no shading or font colour coding (other than that, it was quite impressive!). Others, though, have hideous shading, which is probably worse than no shading at all. I’m no designer, but it seems evident that having an arsenal of, say, three or four well-picked shades to help distinguish the function of various parts of the model enhances appearance. But don’t go crazy: shading the title and leaving the functional cells with a white background is better than shading everything. And hiding grid lines (Alt –> V –> G) can improve the look of a model without much effort.

5. No error checks:

With modelling being the finicky art that it is, we all know that errors can creep in. That’s why a lot of models have test checks in various places. The most obvious is one that ensures that the balance sheet balances by subtracting liabilities + equity from assets. Others will ensure that input allocations (e.g., total revenue allocation for the company by product line) sum to 100%. If you’re doing these already, keep doing them; if not, then start. But the next level of this is to create a table of error checks that links to each of these. From a single place, the user can then debug the entire model. Even better, add some conditional formatting that instantly colours some very visible cells red the moment an error crops up in the model.

6. No data validation:

Continuing with the topic of errors, another way of mitigating them is by employing Excel’s data validation functionality. You may have seen this before in the context of cells that have a dropdown menu. Data validation can be time consuming to implement, but especially if you’re going to be handing off the model to non-modellers, taking the time to ensure that all of the key inputs have constrained values (e.g., “Y” or “N” or values only above 0) will reduce the headache for everyone and build credibility in the model’s functionality.

7. No sales tax in receivables/payables:

While this won’t always have a significant impact, for models that have detailed working capital forecasting, it’s important that receivables and payables contain sales tax (HST in Ontario). After all, sales tax can be a real drain on cash flow: it’s payable to government after you invoice, not after you collect, and it’s payable to vendors when you send a cheque. That said, make sure you don’t include sales tax on the income statement.

8. Sales and costs not connected:

I’ve often seen models where sales and costs are not connected. I’m not speaking about situations where a company sells a physical product which has a corresponding COGS value—that’s fairly simple, and most models integrate this. Instead, I’m referring to situations where sales grow drastically without costs that are fairly “fixed” increasing to support the growth. If in reality your plant is at capacity, then sales growth will mean a large injection of expansion capital. If in reality your 5 staff are doing sales of $2M per year, then when you hit $10M, you shouldn’t still be paying for just 5 staff. Likewise, if your office is your garage on day 1, it won’t be once you have 25 employees working for you. This functionality can and should be included in your model if it is to be a true reflection of reality, and an integral tool in your strategic planning process.

We could go on, but these provide some additional tips for more advanced modellers to continue improving their craft. If you’ve digested these points and are hungry for more, contact us. And if you have other “no-nos”, please comment below and we’ll add them in future editions of this post.