Spreadsheet errors have cost companies millions, but these simple management steps could protect your business from the same costly mistakes.
In the 20 years I have spent as a freelance spreadsheet developer, I have discovered businesses using shockingly unsafe spreadsheets. They were full of errors, constantly fell over, and were very difficult to fix or update. The effects of these problems constantly compounded on the business and presented enormous risks to them.
There was one thing in common with all of these cases. Not one of the companies using these toxic spreadsheets had any form of spreadsheet management in place.
It is not just small businesses that fall foul of these problems, Bigger companies also fall foul of simple spreadsheet errors. Famous cases include;
- Fidelity Magellan Fund — A missing minus sign resulted in a $2.6 billion error.
- Fannie Mae — Rushed process changes cause a $1.1 billion error.
- TransAlta — A cut-and-paste error led to a $24 million overspend.
Again, some simple spreadsheet management processes would have made these errors easier to stop.
Fortunately, spreadsheet management need not be a big complex process that is difficult and time-consuming to implement. The concepts in this article are simple, straightforward and above all, they are tried and tested in the real world.
The 6 Ps – Prior Proper Planning Prevents Painfully Problems.
Yes, I cleaned up the 6 Ps.
One of the biggest spreadsheet crimes I witness regularly is people opening Excel and building a spreadsheet with no pre-planning. The chances of building a safe and stable spreadsheet this way are almost zero.
Before you open Excel, you need to plan with a pen and paper! Not only will this help you avoid problems, but it will also greatly reduce your spreadsheet development times.
I prepare my spreadsheets in 3 key stages.
In this stage, you will need to define some key elements of what your spreadsheet will do and how it will do it. This should include;
- The overall purpose of the spreadsheet.
- What the role of each sheet in the spreadsheet will be.
- What methodologies it will use in calculations.
- Where the source data will come from and how it will get into the spreadsheet.
- Who the stakeholders in the spreadsheet will be.
Now you can open Excel and start laying things out. The purpose of this stage is not to build a working spreadsheet, it is simply to lay things out. Not only will this help you work out where everything goes, but it will also give you a better idea of how your spreadsheet will work.
Once you have laid things out, you can show it to the spreadsheet’s stakeholders. Check it matches their expectations and it includes all the features that they need.
While you do this, there are some core concepts you need to try to keep in mind.
Try to keep data entry, data, calculations, and charts apart.
This is not always possible, but the more you can do it, the easier your spreadsheets will be to maintain.
Work out what areas you want to be editable by the user.
Do you really want your users to over type and/or alter formulas they don’t agree with? As you lay things out highlight the cells you want to protect, and apply the protection later.
Work out where you can limit the user’s data entry choices.
Some data fields can be commonized to a small set of values. For example, users can type salutations in endless ways – to enter the “mister” salutation they could type in “Mr”, “Mr.”, “mr”, “mr.”, “Mister” or “mister”? Instead of being a free typed entry, convert these into a drop-down list of values (e.g. “Mr”, “Mrs2, “Miss” and “Ms”). That way all “misters” can only be entered as “Mr”. This will make filtering and processing data easier.
Work out your fixed values so you can break them out.
Will your spreadsheet contain lots of formulas that use the same value over and over again? For example, formulas that calculate a 20% sales tax on a series of formulas. To calculate this, you would normally write the formula as;
Variations of this could appear thousands of times in your spreadsheet.
This is great until the government changes the sales tax rate, and you have to manually edit all these formulas. To get around this store the sales tax percentage in a named range in the “Settings” area or sheet. This will let you rewrite all those formulas as;
Now, if the government changes the sales tax rate, you only need to edit one cell, not thousands. You should do this with all semi-fixed values in your spreadsheet.
Adding blank spaces ready for future developments and data fields will make your life a lot easier in the future. This has saved me a lot of work on so many occasions.
In all my spreadsheets I use cell K11, rather than A1, as my starting point and hide all the rows above to the left. That way I have 10 rows and columns I can use later on should I need extra fields in the future. I will be able to use them instead of inserting rows and columns and risking breaking loads of formulas.
I would also suggest adding 10 hidden blank columns between subsets of data. For example, if your first 10 columns are your customer’s details and the second 10 are their order details, hide 10 blank columns between those two subsets. If you then need an extra customer details field or an extra order details field, they are there ready to use.
Document Everything To Make Your Life Easier.
The easiest way to help protect your business is to document everything as much as possible. I build a lot of documentation into my spreadsheets and future me is always grateful that I took the time to put them in.
For every column or row heading, I try to include a comment that details what is in the column or row. This could include what type of data is to be entered there, or how the formula in that row or column works. This level of documentation will help users use your spreadsheet, and you when you are editing or fixing the spreadsheet in the future.
An important element I include in all my spreadsheets is a specification sheet or cover sheet. Its only purpose is to provide documentation of what the spreadsheet does and how it does it. This sheet includes several tables and bits of information including;
- Details of what the spreadsheet is designed to do.
- List of all the sheets and what their individual purposes are.
- A list of all the stakeholders in the spreadsheet and their interest in it.
- A list of all the sources of data and how it is entered into the spreadsheet.
- Details of any specific mathematical methods used.
- A list of revisions to the spreadsheet.
- Who owns the spreadsheet (can be a person or department).
- Any audits carried out on the spreadsheet and when.
The more information you have in this sheet the better, as it offers two major benefits;
- Users will have a clearer idea of what to expect from the spreadsheet. As a result, there will be fewer attempts to adjust or “fix” the spreadsheet.
- Editing and updating the spreadsheet will be easier and less error-prone if enough information is provided. (You will spend more time updating, and less time reverse engineering your own work).
It is hard to overemphasize how important simple documentation is in a spreadsheet. Over the last 20 years it has saved my skin countless times.
Have A Change Request Process In Place.
Allowing people to edit spreadsheets with no controls in place is a recipe for disaster. Who can edit a spreadsheet and under what circumstances should be tightly controlled.
Ideally only the spreadsheet owner should be able to edit the spreadsheet and everyone else should refer to them if they need anything changing. The owner should then consult the stakeholders in the spreadsheet to ensure there are no potential issues or conflicts.
You should list any revisions in the specification or cover sheet mentioned in the previous section. If the functionality of the spreadsheet has changed in any way, you will need to edit any specifications on that sheet to mirror the changes.
If you have lots of users of a spreadsheet, it is best to give your spreadsheets a new revision number each time you make changes. This will help ensure everyone is using the same version.
Don’t Leave Your Spreadsheets To Fend For Themselves.
Overtime spreadsheets get edited (authorized or not) or may break because they contain too much data and it exceeds the ranges in formulas. There are countless reasons why a spreadsheet can develop issues over time.
Simply leaving your spreadsheets to fend for themselves can be fatal for your business.
For each spreadsheet, you should set up an auditing schedule and stick to it. How often you audit a spreadsheet will depend on what role that spreadsheet plays in your business, and what an error in it could cost your business.
If an error might break your business, audit it every 1-3 months. If it could cost your business a lot of inconvenience or shut your business for a day or two, it should be audited every 3-6 months. Annually should be good enough for everything else.
An audit doesn’t have to be a long, difficult process either, and it won’t be if you included good documentation with your spreadsheets. When you audit a spreadsheet, you need to check the following basic elements;
- The spreadsheet still performs the role you designed it for.
- The documentation for each sheet still applies.
- Formulas haven’t had any unauthorized edits.
- Formula ranges cover all the necessary data.
As I said, this is a lot easier and quicker if you build in great documentation.
Protecting your business from potentially fatal errors is mostly down to spreadsheet management, and this takes little or no technical know-how. It just requires a bit of effort and pre-planning.
Most people quickly dismiss spreadsheets as being just a spreadsheet, however, in most cases, they are pivotal in running a business. And we should treat them as such. We should manage, update, and monitor them as much (if not more) as any other key element or employee of our businesses.
I’ve evolved and used these methods over the past 20 years. In that time, it has prevented far more errors than any methods I build into my spreadsheets.
In this article, we have only covered spreadsheet management as a way of stopping errors and issues. You can also look at building checks in your spreadsheets themselves. Simple checksums and check formulas can check data as it is entered and verify results generated by formulas.
You could start to roll these methods out today and help ensure your spreadsheets won’t cause you any future business nightmares.