Creating Aging Report to Measure Company’s Financial Health with Power BI
Introduction:
In today’s world, the immense power that business data holds is unmatched. More and more organizations want an efficient method of deciphering complex data and discovering insights that can effectively drive their business operations. Power BI is indeed the right tool to accomplish that goal as it offers exceptional financial data management services with no limitations on the reporting of the data. Organizations often use Power BI to build different financial reports that can help them to track their performance, identify areas for improvement, and make data-driven decisions quickly. One of those reports is a Customer Aging, or sometimes referred to as Accounts Receivable Aging.
I want to use this opportunity to demonstrate how we can utilize Power BI to create an impactful Customer Aging report. In specific, it must meet the three key standards of displaying key metrics, including effective visual representations, and allowing users to quickly search for the information they need. I will walk you through the entire process by using my most recent project and everything will be summarized in 3 main sections, including “Background Information & Objectives”, “In-Depth Process & Insights” and “Result & Conclusions”.
Moreover, even though the purpose of each financial statement is slightly different from one another and maybe you will never have to deal with Customer Aging throughout your professional career, I hope you can use this article as a guideline the next time you need to develop a more common report such as Balance Sheet or Income Statement.
Background Information & Objectives:
Aabstract: So what’s exactly is Customer Aging? Customer Aging is a record of overdue invoices from a specific time period. An organization can use it to determine which customers have an overdue balance and how long that balance has been overdue. From there, they can determine whether or not the customers in question are a credit risk to the business and decide what to do accordingly. Along with the three main financial statements of Cash Flow, Income and Balance Sheet; Customer Aging is arguably one of the most important reports that an organization can rely on to measure their financial health.
Customer Aging often runs in 30-days segment (current, 30, 60, 90 and 120+) and shows the customers or accounts that have overdue balance, date ranges of overdue balance, and the total. An example of the report would look something like this:
Objectives & Deliverables: I was tasked by the client to develop a Customer Aging report for their company. But, they want more than just a simple table displaying the information. The client presents their objectives as follows and wants them to be accomplished in a specific time frame:
1. A visual that displays overdue balance across multiple dimensions such as subsidiary, customer, and invoice. It also has to be formatted to allow overdue values to show up more apparent.
2. Charts or Graphs show the trend of overdue balance through years (2018–2023). Additionally, they need to measure how well a given metric is performing against the target goal. In this case, compare between “Aging Total” vs “Total Balance Due” and between “Current Total” vs “Total Balance Due”.
3. An option to quickly choose a criteria and the data will be filtered accordingly. For example, something that allows user to choose a specific invoice number and see the information associated with it.
4. A visual that shows when the report is being refreshed and updated with the most recent data.
In-Depth Process and Insights:
Data Extraction —
There are 14 subsidiaries within the company and each one has its own aging dataset. The very first step is to properly export the data and load it into Power BI for data preparation. However, to avoid working with 14 different datasets, it is also imperative for me to find a way to combine them into one query. Since they have the same extension of .csv and share identical structure, using the Folder approach is most effective in this case.
To do this, I create one empty folder on my local machine and move each dataset into it. Afterward, I pull up the Query Editor and grab a connection to the folder. Doing this initiates Power BI to automatically run through appending process and produce ONE appended table containing all the data in one place. That table would start with records from the first subsidiary, go all the way down, and ends with records from the 14th one. Overall, this particular approach helps to eliminate extra tables and dependencies, while giving me the ability to pull in new data with ease by just dropping new files or replacing current files in the same folder.
Data Preparation & Modeling —
After importing relevant datasets into Query Editor, the very next step is to transform the data to make sure it is both accurate and usable. This process would involve different features that Query Editor has to offer such as removing duplicates, changing data type and pivoting data. For this particular project, I have to perform a lot of text transformation as the datasets contain a lot of text-string content about customers, the invoices and accounts. For instance, I employ the split-by-delimiter feature to separate the subsidiary name from the invoice number and have all the subsidiaries in one column.
Once desirable data quality is achieved, the upcoming task is to model relationships between different datasets and generate right measures to answer complex question. For example, a common measure that decision makers would be really interested in is the Aging Total amount, which can be calculated by summing up the overdue balance of each 30-day bucket. Later, I can use this measure to create a gauge chart helping the organization to quickly see how much money they still need to collect from their credit customers (More about this later).
Data Visualization —
This is where the data is brought to life. The nature Customer Aging report is emphasis on comparative analysis where decision makers want to access the customers and their balance due from a logical viewpoint and there are multiple variables to weight. As a result, I choose a combination of Matrix, Slicers, Pie Chart, Gauge Chart and Card to deliver the report as coherently as possible.
I. Decision Matrix
The matrix is a perfect way to summarize the balance due for each customer broken down into respective aging categories. When combining with the right format, it can help make the pattern and trend of overdue balance more apparent. For the first matrix, I highlight values of the Current column in green to indicate anything that is not yet due, and values of other 30+ day segment columns in red to signify the amount that has passed after the due date. Yet, I construct the second matrix to give a holistic view of the balance due summarized by years and months. This allows the company to quickly see how the outstanding invoices have fluctuated over a 6-years period.
II. Gauge Chart
By using a colored data range, a gauge chart can provide a clear view of a progressive KPI compared against a pre-defined target. The best case scenario for the company is to be able to collect all the money from the customers; therefore, the pre-defined target variable would be the sum of all receivables. Whereas, the outstanding balance that company has not yet acquired would be classified as the driving KPI. With these metrics, I create 2 different gauge charts to let the company see how much money they still need to collect from all doubtful accounts and estimate the total amount to be written off.
III. Pie Chart
Expressing the overdue balance by aging periods through pie chart is a effective way to show what is the most common length of time the invoices have been outstanding. Based on this visual, the company can identify which 30-days segment carries the highest balance and if their receivables are being collected much more slowly from normal. As a result, they can come up with informed decisions that can benefit the business such as removing unreliable customers or to change their sales practice or policy.
IV. Slicer
Slicer gives a convenient way to filter the data and control what being displayed in the the report. It can make the process of data analysis easier and more convenient for users because they can swiftly narrow down specific information that they need, while ensure the overall cohesiveness of the report’s structure . There are four slicers in total for this report and they are: Company, Customer, Account and Voucher/Invoice. Depending on the criteria being chosen on the slicers, the report will display data pieces tailored specifically to that selection. For example, if I select “AAM” in the Company slicer, I will only see the information on invoices and outstanding balances tie to that subsidiary.
V. Card
I employ Card to display the date/time when the data being refreshed. Even though it has no real impact on analysis aspect like other visuals do, it alerts the company how fresh the data is in the report and if they need to import new data. To create this type of visual, I have to go back to Query Editor, create a new blank query and employ a little bit of M-code to generate the date/time value. The code and result are as follows
Results and Conclusions:
The last step is to put all the visuals together to create the most visually appealing report. It is recommended to make some necessary arrangements to fit everything in one page and use different visual elements, such as title or wallpaper, to enhance the overall appearance of the report. Below is the final result of my Customer Aging report that meets all the requirements from my client.
My client can use this report as a management tool to understand better the financial health of their subsidiaries and evaluate the reliability of their customers. For example, the information could help them decide whether they should keep doing business with customers who are always late on paying their invoices. Or, they can track delinquent accounts to estimate the number of “lost” accounts and how it would contribute to cashflow issues. Overall, I hope this article would deliver some values to you. It can be a new knowledge on the finance side or on Power BI. Please let me know what you think below. I would also be extremely grateful if you share this article with others so we can help each other success !!!
