ETL: Supplier Invoice Analysis

Written by Callum Wright, Junior Infor Consultant

Here’s an ETL (extract, transform, load) sequence I’ve written for the credit-control sector of a major M3 customer.

Business Requirement: To create a dashboard to identify why supplier invoices are not automatically matching to their purchase orders. This is to be categorised by department, so we can see what departments are not completing their purchase orders (within the ERP system) to the required standard.

Stage 1:

Extract data from the ERP system. This will collect all the supplier invoice data and link it to any purchase orders where possible.

Stage 2:

Create an excel template with auto-populating dashboards and pivot tables, so the end-user can visualise the data we’ve extracted.

Stage 3:

Automatically populate the excel template. This will add the current and previous months figures, so we can identify any trends in the data.

End Result:

The user is emailed the excel file. It contains an auto-populating dashboard. They can drill into the data on the other sheets if they want.

Little tricks like dynamic conditional formatting allows the end-user to change the tolerances in columns H-J and N-P, without going into any complex rules.

I’ve also coded the formula so that a dynamic totaling row is possible. E.g. if department = “Total” then sum it up, otherwise do my normal calculation.

Summary

So in summary using the above, we now know the following.

This month the customer received 701 invoices from their suppliers, out of which 222 did not automatically match to their purchase orders, and subsequently were put on a clearing account.

Out of those 222, we’ve got a breakdown across 5 reasons as to why they didn’t match to their orders.

It’s clear that across all departments, people are not receipting their goods in time. It’s a problem that is getting worse compared to last months figures. But we know for this month, we had a 20% decrease in the overall number of invoices that did not automatically match to purchase orders.

Author Callum Wright is a Junior Infor Consultant at Anthesis working with major M3 customers to help them use their system effectively to achieve success.