How to create Accounts Payable Aging Report using Microsoft Power BI

Power BI is gaining popularity among financial analysts as an ad hoc reporting tool. That is why we have added this functionality to Ocerra AP automation solution.

In this article, I’ll show how to create dynamic Accounts Payable Aging Report using Power BI and Ocerra. The Accounts Payable aging report allows seeing outstanding balance per supplier and its terms. It is essential to forecast outgoing cash and prioritize supplier invoice processing. As well as to ensure there are no unauthorized bills.

Prerequisites:

1. Download Power BI for Desktop. A free business intelligence tool from Microsoft

2. Process supplier invoices through Ocerra AP invoice automation software. You can request a pilot account setup.

One of the most used ways to visualize data in Power BI is by using Open Data Protocol (OData) connection. In Ocerra, we support OData out of the box and provide secured access to your data.

Connecting to your company's data:

Power BI allows you to connect to Ocerra and consume metadata from the application site:

Connect to OData feed using the public URL:

Enable Basic authentication using your login and password:

Select all required tables Voucher Header and Workflow State if you want to filter by Approved status. In Ocerra we keep all supplier invoices in tables called Voucher. This term might be unfamiliar for a larger accounting audience. The Voucher is an internal document that combines all related data to the supplier invoice, including Vendor (Supplier), linked Purchase Orders, Workflow Status, Payments, and so on.

On this next step, we are Transforming Data and removing all non-required columns. Then, we expand some additional tables to make our reports friendly. Using the table expand feature from Power BI we can expand up to 3 tables up in the hierarchy, i.e. Voucher -> Supplier -> Name.

As I’m planning to build my report in the local currency, I’m going to exclude Currency Columns and other non-related fields. In Ocerra, we automatically calculate local currency value for all invoices using the pre-defined or automatic currency rates.

To make our report relative to the current date, we’re going to add new columns. The first column will calculate a relative number of days from today to the Due date using a simple formula:

= Duration.Days(Date.From([DueDate]) - Date.From(DateTime.LocalNow()))

In the next step, we’re planning to group our days in bins. You can vary the size of your bin as per your payment schedule. In the demo, I’m using 7, 14, 20, 30+ day bins.

The smart invoice processing brings transparency in your payment process and enhances your cashflow.

Here is the final data transform step, I promise this is the last one advanced BI feature in this demo. Using the Pivot function, we’re able to make columns out of Periods and aggregate the values for our invoices by period.

Finally, let’s build our dashboard. You can publish this dashboard over your Office 365 account or just use it from Power BI Desktop.

Ocerra is a modern, AP invoice automation software that offers custom reporting functionality such as Power BI.

Follow us on linkedin to get timely updates about new features.