Spreadsheets are known to be error prone. As per one study by Raymond Panko, 86% of spreadsheets contains errors. Errors in spreadsheets can’t be eliminated completely, but steps can be taken to reduce them.
In 2003, “a cut-and-paste error in a spreadsheet cost TransAlta, a Canadian power generation company, $24 million in overpayments for hedging contracts.”
Below are some best spreadsheet practices suggested by Paula Guilfoyle of the Excel Club;
- Workbooks should contain a how to or explanatory page.
- Inputs and assumptions should be kept on a separate sheet to calculations, outputs should also be on a separate sheet.
- Keep a log sheet – detailing changes to the workbook. This will help other users track and understand any changes.
- Name and number your worksheets for example 1_P&L, 2_Balancesheet, 3_Cashflow.
- Keep timelines consistent. If a number of your worksheets have timelines, keep these timelines in the same place on each worksheet.
- Navigation: Keep a contents page that links to the worksheets and the worksheets should link back to the contents page.
- Keep formulas simple and at the lowest level. Don’t be afraid to use the results from one formula as a criteria or value for another formula. Lower level formula will allow other users understand the workbooks better.
- Security covers a number of elements. Not everyone need access to every spreadsheet. If they do have access to spreadsheets, lock cells so that formulas cannot be changed.
There are also many research papers available on spreadsheet best practices. One of them is “How do you know your spreadsheet is right?” by Philip Bewig.
It’s good to ask “Is your spreadsheet right?” but confidence comes from answering the question “How do you know your spreadsheet is right?”