This sample project examines the trend of payables over the past 5 years to identify any patterns that need attention.

The dataset can be found and downloaded for your reference on Kaggle

Tools Used for this Project: Power Pivot, Data Modeling, Power Query, DAX, Slicers, and the Built-in Charts of Microsoft Excel

Data Preparation

For this sample project, I gathered three datasets: Category, Payments, and Utility.

I have already cleaned the data before loading it into Power Query. I dropped unnecessary columns and formatted the data to serve its purpose.

Data modeling structure

For data modeling, since the dataset is not large, I used a relational model for this.

Analysis

In the first part of the analysis, I focus on the total payables from 2019-2023 and their breakdown, as shown below:

To easily identify who has the highest percentage, I summarized it by the percentage of the grand total, as shown below:

Based on this data, we can see that COM (Commercial) and RES (Residential) have the highest percentages.

But if we look closely at the data, we can see that there are no significant gaps within the total amounts in the three categories. This leads us to conclude that there are no irregularities in this data.

Visualization

Based on the analysis, I created a dashboard highlighting the key metrics that had the most impact on our findings. The dashboard is shown below.

Advertisement

If you have a similar project that you need help with, feel free to contact me.