3 principles for great spreadsheets

There is a fair amount of good prac­tices to fol­low when con­struct­ing a spread­sheet. Stick­ing to them would require a tiny bit of effort but will go a long way in help­ing you and all stake­hold­ers in the future. Of course, fol­low­ing such guide­lines is annoy­ing at first but will quick­ly become nat­ur­al to you so it won’t come in the way of your work. Also, I have nev­er seen an XLS, even rel­a­tive­ly small in size, that man­ages to per­fect­ly imple­ment all such advice. How­ev­er, striv­ing for such high work stan­dards is what mat­ters and even­tu­al­ly makes you great at practice.

Unless you have to do some­thing “quick and dirty”, when tim­ing is cru­cial, here are 3 prin­ci­ples to fol­low when craft­ing your beau­ti­ful files.

1.Consider who the user would be.

There are very few instances in which you will hap­pen to be the sole user of your tables. Exchang­ing files with clients, both exter­nal and inter­nal (your team is includ­ed here), is the norm. There­fore, you have to con­sid­er what kind of peo­ple would inter­act with your work and try to tai­lor it to their needs and expertise.

To make mat­ters a lit­tle fun­nier, a sin­gle report­ing cycle (of a month lets say) might be enough for you to for­get how your spread­sheet was built and then hav­ing to spend sig­nif­i­cant time reverse-engi­neer­ing or build­ing from scratch. I have been there — the shame is even worse than the extra work 🙂

What you need to do:

  • In case you don’t already know it, try to find out who the (end) user would be and how detailed you need to craft your work in order to be use­ful and com­pre­hen­si­ble for them.
    If the doc­u­ment is inter­nal for your team chances are there is much small­er need of notes, abbre­vi­a­tion expla­na­tions or lay­ing out steps in cal­cu­la­tions — pre­sum­ably you are all well aware what is going on. On the oth­er hand, I have seen how oth­er teams that I work close­ly with, get con­fused by some­thing I con­sid­er straight­for­ward and elementary.
  • In case you don’t know who will be on the receiv­ing end, sim­ply assume they have very lit­tle (or absolute­ly no) knowl­edge of the sub­ject and lack the time to trace fig­ures in your work.
  • Have you already inter­act­ed with the recip­i­ent? Can you recall what they asked you about last time? As enlight­en­ing as it was, you are not going to rely on them remem­ber­ing the lit­tle exchange you had back then, right?

What you can expect in return:

  • Less ques­tions asked — Peo­ple val­ue your time but val­ue much more their own, so if they can under­stand some­thing there is lit­tle prob­a­bil­i­ty of get­ting back to you with questions.
  • Slow­ly “teach­ing” peo­ple you reg­u­lar­ly inter­act with what to expect and how to digest it. From estab­lish­ing terms to adop­tion of cal­cu­la­tion method­olo­gies, I have wit­nessed how well defined and explained approach­es can sur­face on top.
  • Less inter­rup­tions — most impor­tant­ly. Regard­less of it being a call or a meet­ing, hav­ing less events that break your con­cen­tra­tion is always good.
2. Build-in checks as you go.

With every error made we learn a bit more about the impor­tance of checks. And the more of them you have, the bet­ter. What I am try­ing to do when work­ing is to put tests, numer­i­cal or log­i­cal, for every piece of input AND for the results of the cal­cu­la­tions. Then I will con­sol­i­date the checks some­where I can have a birds view and try to make the spread­sheet sig­nal to me as vis­i­bly as pos­si­ble when some­thing is not right.

Numer­i­cal checks are easy and when it comes to inputs — usu­al­ly finite in number.

  • For exam­ple, you might have input that is a per­cent­age split of cat­e­gories — do they make 100% when put togeth­er? Arguably, if the inputs are sep­a­rat­ed (dif­fer­ent file with appro­pri­ate checks or DB that is pop­u­lat­ed after val­i­da­tion), you can skip this in the core mod­el. How­ev­er, I have seen peo­ple going far in their efforts to change the work­ing of a source tem­plate so I would try imple­ment at least a hand­ful of tests when trans­fer­ring the data from the orig­i­nal source.
  • Once you start crunch­ing the fig­ures, end­less num­ber of rec­on­cil­i­a­tion checks can be made. Focus on the most impor­tant as usu­al­ly they are suf­fi­cient to indi­cate if there is a prob­lem. How­ev­er, if your mod­el is going to be exten­sive­ly used, hav­ing checks that pin-point issues makes sense.

It is always the case of count­less log­i­cal checks that can be per­formed. Once again the more you do, the bet­ter. This may vary from sim­ple neg­a­tive num­ber check (eg. you may want to watch if the cash posi­tion in the BS turns neg­a­tive) to much more sophis­ti­cat­ed tests. Either way, the pres­ence of san­i­ty checks has proven to me as a way to mit­i­gate embar­rass­ing errors.

Once a check does not pass I will try to make it as obvi­ous as pos­si­ble. Even though I am not a fan of con­di­tion­al for­mat­ting (this I will cov­er anoth­er time), I don’t hes­i­tate using it in this case. Have you ever seen some­one mask­ing error in a cell with a white back­ground by mak­ing the text colour white as well? You don’t want to be this per­son. Not only because of the epic facepalm this atti­tude rep­re­sents but also because it will all come back to you once search­ing the root of the issue that will cer­tain­ly pop-up later.

Pro­vid­ed the time, I will con­sol­i­date the checks or types of checks from the entire file on a sin­gle sheet where I will eas­i­ly have overview if every­thing seems to be run­ning fine. I call the sheet the not-very-descrip­tive Hus­ton because… a cliché.

3.No hard inputs in the formulas

This might seem as a mis­step that only a rook­ie would do but it is not. Typ­ing an exchange rate, ratio or oth­er val­ue right into the for­mu­la text is done by even the most expe­ri­enced pro­fes­sion­als. Some­times I find myself doing it as well.

Con­sid­er the fol­low­ing sit­u­a­tion — For more than 20 years in the coun­try, I live in (Bul­gar­ia) the local cur­ren­cy (BGN) has been pegged to the Euro at a fixed exchange rate (1 EUR = 1.95583 BGN). As you can expect after such a long peri­od of time every­one has become so used to the fig­ure that mul­ti­ply­ing by it now feels nor­mal. This is so because unless in the event of a seri­ous eco­nom­ic event, the fix­ing can be expect­ed to remain unchanged. How­ev­er, as would tell you the head of the ALM depart­ment of a bank I worked for — this is a risk often for­got­ten but far from com­plete­ly absent. Now imag­ine such rate event mate­ri­al­izes — not only you will have to deal with the emer­gency of much dif­fer­ent eco­nom­ic envi­ron­ment but also spend the time search­ing, replac­ing and check­ing all your hard inputs with the new val­ue. Had you fol­lowed good advice, you were like­ly to change a sin­gle value.

In busi­ness con­tract terms, tar­gets and oth­er key inputs change and mod­els change with them. Mask­ing vari­ables in the for­mu­la bar instead of hav­ing them input sep­a­rate­ly is a recipe for dis­as­ter. Unless you want to make your spread­sheet hard­er in terms of trace­abil­i­ty and main­te­nance, take the very few moments to build appro­pri­ate references.

Fol­low­ing those rules has helped me be pro­duc­tive by mak­ing the Excel files more friend­ly to both me and every­one I share them with. It is some­times a has­sle to stick with such guide­lines but it is well worth at the end when you con­fi­dent­ly take a look at your creation.