For business users of Microsoft Excel Free guides and templates
Home >  Excel Reports > 

Data Strategies

How to Use Excel to Find Errors After a System Conversion

If you're going through a system conversion, you know how many hidden errors there can be. Here's how to find them easily.


by Charley Kyd, MBA
Microsoft Excel MVP, 2005-2014
The Father of Spreadsheet Dashboard Reports

Back when I worked as an OLAP consultant, I received the following note from one of my clients...

"We recently converted to a new accounting system and we're concerned that we've lost some controls that the old system gave us. Because we've stopped running in parallel, we can't check that values are identical in both systems. Even so, we're planning to write data from both systems into our Excel-friendly OLAP and use Excel to look for problems. What's your reaction?"

I told him that was an excellent idea. It's one that you should keep in mind if you ever find yourself in a conversion where significant amounts of data are involved.

My client was using an Excel-friendly OLAP database, which could contain data from both systems. OLAP databases are the only technology I know that lets you compare two sets of data easily.

If you're still running in parallel in both systems, you could compare their values easily. But if you're like my client who no longer was running in parallel, you still can uncover likely errors. 

To do so, create a spreadsheet with formulas that return data from the old system until the time of the conversion, and from the new system after that point.

The formulas could return the monthly balances and the changes for any given account for any series of months for any given division, department, and so on. Then, you could use a simple VBA macro to loop through all those items, testing every possible combination of division, department, account, and so on.

During each loop, you could use statistical techniques in Excel to look for statistically significant changes in the trends of each data series at the time of your ERP conversion. Any significant differences you find could well be caused by differences in the way your two systems classify their transactions.

Graphically, you would be looking for unexplained jumps in the trends in the data series before and after the conversion.

Each time you find an exception, your macro could add the relevant data to a simple list in Excel. This would allow you to split up the list among co-workers and research the exceptions quickly.

Of course, when this analysis finds exceptions, you won't necessarily know which system is at fault. The new system might be correcting long-standing problems that the old system had; or it might be introducing new problems that you need to fix.

But by taking this general approach, you would have an excellent way to discover the exceptions that you should research.

 

Free Excel Dashboards

Click to see customer testimonials

Charley's SwipeFile charts