Spreadsheets: Best Practices (Part 3)

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?”

Related Articles:

Spreadsheets: Risks (Part 1)

Spreadsheets: Auditing & Validating (Part 2)

Advertisements

2 thoughts on “Spreadsheets: Best Practices (Part 3)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s