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

Play with the 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

Invoice and Payments Template

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

Invoice and Payments Template

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

Invoice and Payments Template

  • 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

Invoice and Payments Template

  • 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.

  1. Did you like the Analysis + Visuals presented in the Dashboard?
  2. 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

  1. You need to have the latest Power BI Desktop. It’s free – you can download it here
  2. And of course Excel 2007 or above version to maintain the back end data (excel files)

 

Purchase the Template

Buy for $59

Invoice and Payments Template

Once you purchase the template you’ll be

  1. Directed to a secure login.
  2. Where you can download the template and back-end files.
  3. 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

 



Topics that I write about...






Download Smart Ebooks on
Excel and Power BI