Everybody loves Microsoft Excel right?

I mean it’s a great tool for reporting, modeling and analytics.

Back in the day, it made up for System shortcomings in that one could quickly sort data, slice and dice it anyway you wanted to. One could customize reports rather easily as well. I became really good friends with Microsoft Excel over the years. Granted, Systems have come a long way over the years. However, Excel is still a tool to take reporting and analysis further, quicker.

Now on the flip side – since it is such a powerful tool – it is not without risk. What I’m speaking to is errors that can be inherent with spreadsheets. Formula errors can lead to bad reporting which in turn could potentially lead to ill-informed decisions or even embarrassing surprises. (Or worse).

I often find that one of the biggest reasons for errors in spreadsheets is due to plain ‘ole carelessness. Another is being in too big of a hurry in the design phase. There are other reasons as well.

What can you do about it?

 

Focus on Prevention

1 Learn Excel:

I mean, become a student of the program. Increase your skill level. Learn from others.

2 Tips for the Design Phase:

Slow down. Take time to think it through. The variables, the sources and the desired outcome. Then design the layout in a logical and simple manner.

Use data input tabs. Use separate tabs for results. Allow for the import of data when possible vs manual data entry.

Do not hard-code formulas. Be consistent in the application of formulas across columns and rows. Create checks and balances within the sheet.

Highlight input cells, protect formula cells. Review and test formulas.

3 Tips for the Live Phase:

Don’t rush your work. Correcting your work later is duplicate work.

Don’t assume just because the data was imported from “The System” and the report or analysis is from Excel, it has to be right. Consider the controls around “The System” and consider that imperfect people are inputting data into “The System”.

Be careful when sorting data.

Take adequate time to review your work. Understand your results. Do they pass the litmus test? Were the results you got reasonable, were they what you expected? Sometimes you’ll discover an input or formula error or some design flaw that didn’t consider a new variable that has presented itself.

Keep live production models separate from other “what if” or forecasting models.

Case in Point

Back in my corporate days, a physical inventory turned up a loss of like $300,000. Turned out it was a valuation issue primarily. The Plant Controller and his boss spent several days trying to figure out what went wrong. At this point in my career, I was in a different area. I was asked to take a look at it. After a couple hours, I found a formula error in a spreadsheet that was used to value production.

They had opened the live production model, changed the hard-coded formulas to do some budget modeling and then saved the file with-out renaming it. The new hard-coded formulas were used in the normal live production file for a few months.

So, as you can see things can go horribly wrong using spreadsheets incorrectly, which leads me to my final thought…

If you inherit a spreadsheet from someone else, make sure you review it just as you would had you built it yourself. Be able to defend that spreadsheet and its results.

 

Happy Excelling…