The ASK MIKE column provides access to questions & answers from finance and small business expert Michael Gonnerman. Mike has served as a trusted advisor to hundreds of technology CEO’s and investors as well as served on more than two dozen corporate boards. The ASK MIKE column contains a mix of current as well as classic Ask Mike Q&A.
How can I prevent spreadsheet errors?
“When I was assembling this year’s budget, I discovered major errors in the spreadsheets that our managers submitted–wrong calculations, incorrect references, corrupted templates, etc. Any advice on how to clean up this mess?”
Mike: I can suggest two simple tips that will do a lot to catch (or even prevent) spreadsheet corruption:
#1: When you create the template that you distribute, use Excel’s “protect cells” function to prevent users from changing anything except the cells you want them to populate.
#2: Build in some error-checking formulas that will quickly identify problems with the user’s data or calculations. For instance, create cells on the balance sheet that show assets = liabilities + equity, net income on the income statement = the change in retained earnings, and cash per the cash flow statement = cash on the balance sheet.
Incidentally, users sometimes tweak a budget template because their actual business activities don’t fit the line items in the template. If you improve the template itself, you’ll probably reduce this kind of “creativity.”
If you like the ASK MIKE Column than you will love the Ask Mike book from Michael Gonnerman. The book contains Mike’s response to actual problems encountered in 30 years as an advisor to high-tech startups and small companies. To learn more or to purchase the book click here.
To access Michael Gonnerman’s bio or to contact Mike directly click here.
Mike encourages you to post your comments below or ask a question that you would like Mike to include in a future post of the ASK MIKE Column.
There’s a book on this, “Spreadsheet Check and Control”, and training with a certification exam at the end, “Spreadsheet Safe”. For those in corporate computing with bigger spreadsheet problems, the annual conference of the European Spreadsheet Risk Interest Group (EuSpRIG) discusses these topics.
How to manage spreadsheet risk is becoming a more popular topic as individuals and companies realise the risk of using Excel spreadsheets within critical processes such as budgeting.
A couple of key factors to consider:
1. Test your spreadsheets thoroughly
2. Create a catalogue of all of the spreadsheets within your organisation and prioritise them by their risk profile
3. As Mike covers above protect the formula cells within your spreadsheets. Also protect the workbook itself if the information is sensitive or confidential.
4. Apply a best practice standard to your spreadsheet development (eg http://www.bestpracticemodelling.com/standards/ssrb)
5. Train your staff so that they have the required level of Excel competence for their role
The EuSpRIG site has lots of information on managing spreadsheet risk: http://www.eusprig.org/
Try using a computer-aided spreadsheet audit tool. There are others, but the best one I’ve come across is The Audinator (http://www.audinator.com). In less than 10 seconds every single formula throughout your spreadsheet workbook is analyzed and tested for a wide variety of common (and uncommon) spreadsheet errors and risks. Hope that helps.