Home > Excel for Business > 

Using Excel to Find Errors
After a System Conversion

Christopher,

That's an excellent idea!

As you know, your Excel-friendly OLAP can contain data from both systems. It offers the only tool I know that lets you compare that data easily.

One approach you might consider is to create a spreadsheet with formulas that return the monthly changes for any given account for any series of months for any given division, department, and so on. A simple macro could loop through all those items, testing every combination.

During each loop, you could use Six Sigma techniques within Excel to look for statistically significant changes at the time of your ERP conversion. Because your company operations haven't changed significantly during this time, any changes you find probably will be caused by differences in the way your two systems classify their transactions.

Graphically, you would be looking for either of the conditions shown here. The old ERP generated the data before April, 2006, and the new ERP generated the data from that point on.

Of course, when this analysis finds problems, you won't necessarily know which system is at fault. The new system might have corrected a long-standing problem; or it might have introduced new problems. 

That is, you're going to have to research the exceptions you find. But your general approach offers an excellent way to discover those exceptions.

Hope this helps,

Charley Kyd
June, 2006

(Email Comments)