A Timely Reminder on Spreadsheet Error

I read the other day a self-published article from LinkedIn/Pulse about spreadsheet error. The author talks about the different sources of error and how they can greatly impact your financials, mostly in an adverse manner. One, there is the common manual input or copy/paste error. He faults many organizations for not automating their spreadsheets, thus opening themselves up to avoidable errors. Two, he talks about bad use of formulas or incorrect formulas which can be rectified with an audit of the spreadsheet. And three, he talked about hidden rows, which is not the point of this post but was an interesting insight on how people can deliberately hide information by hiding rows or columns and then printing them out on PDF. He gave an example of bank deals where one party deliberately hid rows so that the other party would not know what it was really getting.

A lot of what he talked about resonated with me because I have been doing a lot of automation and testing/auditing lately. But I will say that most finance/accounting people don’t do automation. My boss is one of the rare birds who pushes for automation. I’m not sure why most don’t try to automate; it could be you just have to have an inclination to do so. Automation could be something simple such as links or vlookups to something more intricate such as pivot tables and decision formulas and onto the most complex such as macro programming.

Automation does take time and effort to solve problems that will come up. But I will say, take the time up front and you will save a whole lot of time later. An example I used to use back at my old job is the weekly report generated for approximately 100 projects (well, really 150 but for simplification of the math…). If you spent 5 minutes on each of the 100 projects, that would lead to 500 minutes or a little over 8 hours. Spending 8 hours doing nothing (no lunch, no bathroom, no phone calls, no meetings, nothing) but creating all 100 reports. Instead, I had an automated system that didn’t even include a macro. All you did was download the data from SAP, plop it into the excel file, refresh the pivot table and BAM, you have your report. In a perfect world, it would be 5 to 10 minutes but the report ran so long in SAP that it probably took at most 30 minutes. That beats 8 hours (or a week if you had to do other things). I did this every week whereas my peers did it every 2 weeks or even every 4 weeks, if they did it at all.

Once you use the file a couple of times, you will run into errors and fix them, thus making the file even more robust. The only caveat would be if somebody enters data into SAP in a different fashion, which can throw you off. And that is why I always have a cross check embedded in my spreadsheet to check the totals in my file to the totals in my download.

I don’t know why more financial types don’t do this.