Five tips for better spreadsheets

I’m old enough—just—to remember accounting work before the rise of spreadsheets. We had a accounting ledger system on a mainframe compute to record all the transactions and it could produce printed reports but if you wanted to do any analysis you had to do it by hand. We had stationery called analysis paper which was A3 size, pale blue with a grid pattern in darker blue ink. There were wider cells on the left side and heading and footer rows, too. This stationery made it easier to lay out data in rows and columns but all the sums had to be done with a calculator, at least twice to check they were right. There were no graphs and no sorting on the fly.

Excel is ubiquitous now but in the mid-80s we moved from paper to SuperCalc (I knew some organisations used Lotus-123) and these were spreadsheets that pre-dated the graphical user interface and the mouse. They did not have colours: they were white or green on black depending on your monitor. And we used them like analysis paper, basically summing the numbers in our columns and rows.

Modern spreadsheet packages are massively more powerful with all types of arithmetic, financial, scientific, statistical and database functions built in. Indeed, there are more functions than most users are aware of. My point with this blog post, though, is not to explain some esoteric function but to address the fact that accountants (and others) are not trained in financial modelling. Instead of adhering to some basic standards or principles for all their work people lay out each spreadsheet to solve the problem they have right now. This is OK as long as no-one else is collaborating on the spreadsheet or, at some later date, asked to update it. At times like that the lack of standardisation means it is very difficult to understand how the spreadsheet works and it might be easier for the person to build a new spreadsheet from scratch—using their idiosyncratic style, of course—than update the old one.

One way to envisage what I mean is to think about a paper notebook. Your notebook is yours and it does not matter how you choose to keep notes at meetings. Your notes could jump around from topic to topic, mix words, numbers and pictures, have asides, scribbles and crossings-out but that does not stop you from being able to remember what you need to remember. In my experience most people create spreadsheets in much the same way. They start at the top left of the sheet and add things as they think of them. They are not thinking of future users as they build the spreadsheet.

But there is a better way. Some organisations do have standards for financial modelling so that there is consistency in the work produced by their staff. This makes good business sense since it saves time, enables quality control to be implemented consistently and, I reckon, reduces errors. (Errors in spreadsheets are legion and can be costly, either in money or reputation.)

In the absence of financial modelling standards in your organisation, here are five tips, or principles, that you can adopt in your spreadsheets. Although I mention Excel throughout they would equally work on Google Sheets, Apple's Numbers, or Open Office spreadsheets.

1 Inputs, calculations & outputs go on different worksheets

Excel opens to a blank spreadsheet and many people will start creating their masterpiece on ‘Sheet 1’ and build it up and up until it’s done. For years it’s been possible to have multiple worksheets within a spreadsheet file and you move between them by selecting the tabs at the bottom of the screen. I suggest as a minimum you use three worksheets, one for your inputs and assumptions, one for your calculations and one for your outputs. If you want a variety of output formats you might want multiple output worksheets, and if your calculations are complicated you might need several worksheets for them so they are easier to follow (and audit).

It’s separating the inputs that I think is most important. By inputs I mean the key assumptions, such as start and end dates, interest rates, inflation, etc and also data specific to your model. It’s here that you would have all the relevant data for the model such as population information, unit costs, prices. If your organisation has specific financial policies such as the discount rate to use or the profit margin to apply you would also include them on this page. This simple tip, combined with tip 2, makes the job of someone asked to update your spreadsheet much easier. Basically they ought to be able to look at the input sheet and make amendments there which ripple through the calculation sheet(s) into the output.

2 Use an input in only one place (so it is easy to change)

This is an important tip for avoiding errors and making updates easier. If the interest rate is in cell C10 on the ‘Inputs’ worksheet then wherever it is needed in the calculations it should be referenced as Inputs!C10.

3 Do not hard code numbers into calculations

The formulas in the cells in the calculations worksheet(s) should not include actual numbers. The reason for this is that the calculations should be drawing on data on the inputs sheet(s). If you are including some data directly into your calculations you are making it very difficult for someone to understand your model and be able to update it. Even if they find the number within your calculations they may not know why it is there or where it is from. The possible exception to this rule is that sometimes it might be useful to include the numbers 0 and 1 in formulas. For example, you might use them as results from a conditional formula because 0 and 1 are the logical values for false and true.

4 Break up long formulas into component calculations in separate rows/columns

It’s possible to have very long formulas in a spreadsheet calculation especially if you are nesting If statements or the like. Developing these formulas can be difficult because you need to get all the arguments right and include the correct number of commas and parentheses; and once created, they can be difficult for someone to understand. It can help, therefore, to break the whole formula into component parts and set them out on the rows (or columns) of your worksheet. This makes it easier for you to create the formulas and makes it easier for someone to follow your logic.

5 Use the exact same formula across all the cells in a row

When using Excel have you ever noticed a small green triangle appear in the top corner of some of the cells. This is an audit warning marker. It appears when the formula in a cell does not match the neighbouring cells. This is a useful feature but only if you create your worksheets so that the same formula is used across a whole row. If that is how you build your spreadsheets then the green triangle actually works to draw our attention to a possible (likely) error. If you create spreadsheets where the formula used in each column is different then the triangle is no help. Creating formulas that are the same across the rows does require a little bit of extra thinking. This could be, for example, because the calculation you want to create in the first and/or last period of the date range are different from the calculations for all the other periods. You might need a calculation that includes an ‘If’ statement to identify whether it is the first or last period. Another example might be to base the calculations on the number of days in the month. For that you might need a row with a formula that calculates the number of days in the period (i.e. the end date of this period minus end date of prior period) and then use the answers in subsequent formulas.

I’m confident these five tips will improve your spreadsheets immensely but there’s more you can do so I’ll post five more tips soon. Meantime, you can always share these tips with colleagues to improve their work and, if you want to be kept informed about my work, please join my mailing list here.