Recently I worked on an interesting accounting problem where the business owner was generating invoices but the customer in turn was settling the invoices in lump-sum payments. I even posted this as a little DAX / Power BI Challenge.
Consider something like this..
| Date | Invoice | Customer | Amount |
| 01-Jan | 1 | Alpha | $100 |
| 01-Jan | 2 | Alpha | $120 |
| 02-Jan | 3 | Alpha | $50 |
But the customer rather than paying in-full against the invoice, paid 2 lump-sum payments of $70 and $150.
| Date | Customer | Amount Received |
| 02-Jan | Alpha | 70 |
| 05-Jan | Alpha | 150 |
Now the problem arises when the business owner needs to set-off invoices manually using FIFO (first in first out method) against the payment received till date to find out which invoices have been paid off. This problem gets further complicated when the customer sometimes pays against the invoices (in-full or partially) and sometimes pays lump-sum.
My new Invoice and payment management template solves it, pretty elegantly!
Video Tour – Invoice and Payment Management Template
The Dashboard is split into 2 screens
- Screen 1 – Company and Customer Overview
- Screen 2 – Invoice Paid and Unpaid by Customer
SCREEN 1: COMPANY AND CUSTOMER OVERVIEW
The objective is to show you an over all Company and Customer overview. Some of the key metrics that are displayed are
- Total Billings and Amount Pending till date
- Top 7 Customer who haven’t paid
- Total Billing and Balance Pending till date for selected Customer
- Customer Level detailing
SCREEN 2 : INVOICES PAID AND UNPAID BY CUSTOMER
The objective is to show you in-depth customer and invoice level details. Some of the key metrics that are displayed are
- Total Billings, Amount Received and Balance Pending till date for the Customer selected
- 2 Tables showing
- How many invoices have been set-off under lump-sum payments
- How many invoices have been set-off by payments made against the invoice (in-full or partial)
The Back-end Data
The back-end data is pretty simple to maintain there is just one Excel file with 2 sheets – Invoice Data and Receipts Data
Sheet 1 – INVOICES DATA
- One row means one unique invoice. Invoice Number is the unique column
- Rest columns are pretty self explanatory.
- Every customer has a unique Customer ID
Sheet 2 – PAYMENT RECEIVED DATA
- One row means one instance of payment received.
- The Invoice number will be mentioned if the customer pays against a specific invoice else blank.
- The payment received against a specific invoice should never be more than the invoice value. Although the payments can come in multiple installments.
- Customer receipts without the invoice number can exceed the total amount of all invoices issued to a particular Customer.
Important Note about Data – For the Dashboard to function properly with your data, it’s very important to maintain the data in the desired format, rest is automated! 😎
Implementing Invoice and Payment Management Template
Once you have the data ready, this is pretty much a plug and play template that you start using instantly for your own company’s analysis. Now that you have seen the Dashboard, you can ask yourself 2 quick questions.
- Did you like the Analysis + Visuals presented in the Dashboard?
- Can you collect and maintain the data in the desired format?
If the answer to the above question is YES, go for it you won’t regret a dime you spend on this template!
Requirements
- You need to have the latest Power BI Desktop. It’s free – you can download it here
- And of course Excel 2007 or above version to maintain the back end data (excel files)
Purchase the Template
Buy for $99
Once you purchase the template you’ll be
- Directed to a secure login.
- Where you can download the template and back-end files.
- You’ll also have the lifetime access to detailed video guides on configuring & customizing the dashboard
Have Questions?
I am sure you have.. probably many! The comments are open and I don’t want your questions to go un-answered.
Cheers
