MAP Supplier Invoice Reconciliation

Home MAP Supplier Invoice Reconciliation

Proactively validate supplier invoices to reduce overcharging.

 

Case Studies
David Carson, Senior Consultant
27 December 2022

CLIENT SUCCESS STORY

Company Highlights

Region: UK

Sector: Utilities/Energy

Division: Revenue Assurance

Approach: Data360 Analyze, Revenue Assurance, Data Quality, Data Accuracy, ETL, Reconciliation, Power BI Dashboardx

 

 

I N T R O D U C T I O N

How do you know that your suppliers are charging you accurately? This case study looks at how you can utilize your own data sources to create a view of the
expected invoice, then perform a reconciliation to identify and categorise outliers in the supplier invoice.

This case study will focus on how a combination of internal and external data is used to allow proactive calculation of expected monthly charges, using the agreed contracted rates. It then discusses the steps taken by Pomerol, as well as some of the logic used, in order to enable validation of MAP invoices at an invoice row level.

 

C H A L L E N G E

Energy suppliers provide gas and electricity to individual properties through meters. If they do not own the meter, then they will need to pay a rental fee as dictated by their contract with the Meter Asset Provider (MAP). There are many types of meters, with differing age profiles, which use different daily charge rates. The main difficulty lies with knowing whether you are being charged the correct daily rate for the correct period (i.e. the right price for that particular meter, for that particular date, in that particular state). This was an existing revenue assurance gap that we would address.

 

S O L U T I O N

The key to validating the accuracy of MAP invoices is to obtain and maintain an ongoing view of the meter assets. It is important to know what the state of the meter is (i.e. installed/removed), along with the meter type (e.g. Classic, SMETS1, SMETS2, etc) and whether the meter is on supply – throughout time – as all of these factors affect pricing.

With the meter and supply data identified (from both internal and external sources), we then used the Data360 Analyze visual ETL tool to pull data from the various database sources and to create a view of the meter/supply data, which we then stored within a new database structure. This new database structure provided the input for the calculation of how many days of rental would need to be paid for each meter (in a specified period – usually monthly), and at what rate. We referred to these expected charges as “accruals”.

Once the MAP invoice was received, an automated process compared the backing data (i.e. the invoice “per meter” charges) against the accruals in order to establish one of three possible outcomes per invoice row:

1. An accrual exists but there is no matching charge on the invoice – take no immediate action as the
charge may appear on a future invoice instead
2. An accrual exists and there is a charge on the invoice
3. A charge appears on the invoice but there is no accrual (i.e. this is an unexpected charge)

For the rows of invoice data (#2 and #3), further automated checks are then performed to check the validity of the charge and to establish any reasons why differences may occur. Commentary is added against each row to highlight this information to the Analyst that will review the reconciliation output – the Analyst will then liaise with the MAP to dispute certain charges.

Some examples of further checks performed are:
• Was the meter installed during that period?
• Was the supply point on supply during that period?
• Does the invoicing MAP match the meter owner MAP?
• Does the meter type match?
• Does the daily rate match?

Once the reconciliation has been performed for every row of invoice data, then an output file will be produced. The data is also displayed on a Power BI dashboard, so that a historical view of the invoice accuracy can be viewed (and assessed) by any interested parties.

 

R E S U L T

The main objective for this engagement was to perform a revenue assurance engagement – i.e. to answer the question of “Are we being charged correctly?”

Prior to our arrival, the client did not have a view of whether the MAP invoices that were being received were correct/undercharging/overcharging, but there was an underlying assumption that overcharging was prevalent within the suppler invoices. The previous six years of invoices were checked and an estimated overcharging of between £1-3m annually was identified.

Their objective was to progress to a point where every row of every invoice could be validated by an automated process – and if there was a difference then to perform additional automated checks to identify why the difference existed. This allows their Analysts to spend their time reviewing and disputing the identified differences, rather than manually reviewing (or spot checking) the MAP invoice data.

The client now has access to row-level validation of invoice data, along with a dashboard to track MAP invoice charging activity over time. In addition, due to the use of multiple internal and external data sources (used to improve data quality), the Invoice Assurance team now receives various ad-hoc data queries from around the business – as the team now have views of data that are not available to other areas of the business.

 

A B O U T  P O M E R O L

Pomerol Partners creates and delivers meaningful data-driven business solutions and services. We’ve been operating in Data Analytics and Intelligence Reporting for over 9 years and our Partners have a cumulative 100 years in global experience. In 2013, Pomerol spun out of Deutsche Bank after a range of successful BI projects were delivered saving the bank over $3m. Ever since then we’ve been partnering with our clients (executives, managers, analysts, etc.) across all business verticals to optimize the intersect between changing business demands, technological progress and sustainable business growth.

Our consultants have helped hundreds of organizations, of all sizes and in all industries worldwide, make money. How do we do this? Leveraging your data to increase revenue, automate processes, decrease costs and understand your insights. Pomerol delivers value that helps you grow and change with better data-driven decisions and solutions that empower you as an Elite Qlik Partner as well as a Gold Data Platform, Silver Data Analytics and Silver Cloud Platform Microsoft Partner.  Our professional consultants, that reside in three countries (United States, United Kingdom and Portugal), are experts that will partner with you for the technical as well as business-oriented solutions. Working with the same team in all capacities ensures you always know where we stand with our work and commitment to you.

Pomerol offers a comprehensive range of Power BI training options. For those new to Power BI, Dashboard-in-a-Day will provide give you the knowledge and confidence to build your own dashboards. For those looking to take their Power BI skills to the next level our advanced DAX and M courses will teach you the fundamentals of these two languages and to how to use them to build complex solutions. We also offer bespoke training workshops that can be tailored to your specific training needs.

#Dashboards #Data Accuracy #Data Quality #Energy #ETL #Microsoft #Power BI #Revenue Assurance #UK #Utilities

More Insights