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.