Spreadsheets can be great. They are flexible and powerful and can be used to automate and simplify many calculations and applications.
But they can also dangerous to the unwary. They can easily give you the wrong answer if the calculations are wrong, or if someone inadvertently alters something.
🔹️ Validate your inputs – check that you have received in all the data you are expecting.
🔹️ Validate your outputs – check that your spreadsheet calcs have not lost or added anything unexpected in compiling your reports.
🔹️ Build in error checks – check your calcs are ok, for example do the totals still add up.
🔹️ Document the structure – add a single sheet to explain how the spreadsheet works.
🔹️ Separate out inputs/workings/outputs – don’t mix them up on the same sheet as it makes things confusing. And confusion leads to errors.
🔹️ Keep it simple – keep everything simple, visible and logical. Don’t use fancy formulae if you don’t need them.
🔹️ Use a logical direction to your workings – go from top to bottom, and left to right
🔹️ No gaps – don’t leave 300 rows then a few calcs. People will not know the calcs are there.
🔹️ No hard coding – if you have a tax rate, say, have it as a variable cell the user can update, and then reference that. Don’t under any circumstances, hard code the tax rate in the formula itself.
🔹️ Split out formulae – keep it simple by having two separate simpler formulae, rather than one complicated one.
Now, if you can follow these principles, you should be on your way to creating some excellent spreadsheets.
If you need any help with your spreadsheets or with any of your finance processes, then please do give me a shout.