Analysing Excel

A service company maintaining over 5,000 separate spreadsheets were able to see the whole picture of their agent commission sharing system with a system developed by SME Data Solutions. The system allowed commission payments to be properly audited and headcount managing the process was halved.

Background

Over the years the company had collected over 5000 spreadsheets around commission claims from their agents. The claims were complex and based on a number of factors. Although the spreadsheets were based on a template, that template had also evolved over the years making it impossible to collate them in a meaningful way. However it was necessary to do so because of an audit requirement to confirm that agents were being paid correctly.

Solution

An Access database was created to parse through all the directories, open each Excel file, search on a cell by cell level to identify relevant data and import into a temporary table. Claims were then matched to both contracts with the agent and with the customer, plus sales ledger to bring all the data into one place. Once this task was done, a further enhancement was made to regularly check a specific email folder for new files and import and process as required. At the same time a new process was put into place to automate the verification of claims.

Outcome

A process that was full time for three people could now be managed by 1 ½ people, the time to process claims was significantly reduced and overclaims were quickly identified.