Building an Excel customer receivables dashboard came to me when I realized that most companies seem to really make use of their invoice information to look at their customer credit performance. Reviewing how your customers are doing in terms of making their payments plays a significant role in having a successful company because this is your lifeline for actually getting the bills payed. This cash flow plays an integral part in the business and also allows your company to grow.
So, What Does This Dashboard Do Exactly?
The customer receivables dashboard allows you to load up your customer contact information (from your customer table) as well as load up your invoice details and due dates (from your invoice detail or invoice table) and provides you with an easy to use interface that allows you to select any customer (using a drop down validation list based on the customer table information that you loaded into the table) and will immediately display your customer’s main contact information as well as the breakdown of their past due invoices. This will provide you with information like number of past due invoices, total past due invoice amount, oldest invoice and matching customer PO number to that invoice and finally the breakdown and distribution of late invoices based on age group (1-30 days, 31-60 days, 61-90 days and greater than 90 days). I’ve also included a neat graph that will show you the distribution of past due invoices based on aging category. Finally within the centre portion of the dashboard you’ll notice that the invoice aging category with the highest value of past due invoices will display the percentage of the total past due invoices.
I’ve included a quick video of the dashboard being used to give you an idea of how the dashboard works and whether or not this is something that may be useful to you.
Excel Customer Aged Receivables Dashboard – Video Demonstration
As always, thanks for all of your support! If you have any ideas or questions please leave a comment.
