Report of the Week - Customer Balance Report

How much do your customers owe you? How about customers with payments long overdue? How much advance payments do you have in hand? For all these questions, the Customer Balance Report provides you with the answer. This week we'll see how to create a customer balance report based on your transactions over a specific period.

You can create this report over Zoho Books or Zoho Finance data.

To create a customer balance report, you need to follow the below steps.

Define the period for analysis (by creating a Query Table)
Get the Opening balance, Debit and Credit (by creating a Query Table)
Join the above two Query Tables
Create the Customer Balance Report on top of the above joined table
Get Closing Balance (by defining a Report Formula)

Define the period for analysis (Query Table)

Define the period for which you want to analyze the customer balance. The following query will set the period as from the beginning of the year till the current date.

SELECT

start_day(Year, current_date()) as 'From Date', current_date() as 'To Date'

You can also create a Table and manually enter the From Date and To Date for analysis.

Get Opening balance, Debit and Credit (Query Table)

Now you need to get the opening balance for each customer at the beginning of the period, and debits and credits during the period. The following query gets these details.

/*Opening Balance from Invoices*/

SELECT

"Customer ID",

"Total (BCY)" as 'Opening Balance',

0 as 'Debit',

0 as 'Credit'

FROM "Invoices"

JOIN "Period for Analysis" ON "Invoices"."Invoice Date" < "Period for Analysis"."From Date"

UNION ALL

SELECT

/*Opening Balance from Credit Notes*/

"Customer ID",

"Total (BCY)" * -1,

FROM "Credit Notes"

JOIN "Period for Analysis" ON "Credit Notes"."Credit Note Date" < "Period for Analysis"."From Date"

WHERE "Credit Notes"."Credit Note Date" < "Period for Analysis"."From Date"

UNION ALL

SELECT

/* Credit notes in Current period*/

"Customer ID",

"Total (BCY)" * -1,

FROM "Credit Notes"

JOIN "Period for Analysis" ON "Credit Notes"."Credit Note Date" > "Period for Analysis"."From Date"

AND "Credit Notes"."Credit Note Date" < "Period for Analysis"."To Date"

UNION ALL

SELECT

/*Opening Balance from Customer Payments*/

"Customer ID",

"Amount (BCY)" * -1 as "Opening",

FROM "Customer Payments"

JOIN "Period for Analysis" ON "Customer Payments"."Payment Date" < "Period for Analysis"."From Date"

UNION ALL

SELECT

/*Invoices in the current period*/

"Customer ID",

"Total (BCY)",

FROM "Invoices"

JOIN "Period for Analysis" ON "Invoices"."Invoice Date" > "Period for Analysis"."From Date"

AND "Invoices"."Invoice Date" < "Period for Analysis"."To Date"

UNION ALL

SELECT

/*Customer Payments in the current period*/

"Customer ID",

"Amount (BCY)"

FROM "Customer Payments"

JOIN "Period for Analysis" ON "Customer Payments"."Payment Date" > "Period for Analysis"."From Date"

AND "Customer Payments"."Payment Date" < "Period for Analysis"."To Date"

UNION ALL

SELECT

/*Opening Balance from Debit Notes*/

"Customer ID",

"Transaction Amount (BCY)",

FROM "Accrual Transactions"

JOIN "Period for Analysis" ON "Accrual Transactions"."Transaction Date" < "Period for Analysis"."From Date"

WHERE "Entity Type" in ( 'debit_note' )

UNION ALL

SELECT

/*debit Note in current period*/

"Customer ID",

"Transaction Amount (BCY)",

FROM "Accrual Transactions"

JOIN "Period for Analysis" ON "Accrual Transactions"."Transaction Date" > "Period for Analysis"."From Date"

AND "Accrual Transactions"."Transaction Date" < "Period for Analysis"."To Date"

WHERE "Entity Type" in ( 'debit_note' )

UNION ALL

SELECT

/*Opening Balance for Customers*/

"Customer ID",

"Transaction Amount (BCY)",

FROM "Accrual Transactions"

JOIN "Period for Analysis" ON "Accrual Transactions"."Transaction Date" <"Period for Analysis"."From Date"

WHERE "Entity Type" in ( 'customer_ob_invoice' )

AND "Accrual Transactions"."Debit or Credit" = 'debit'

UNION ALL

SELECT

/*Opening Balance for Customers*/

"Customer ID",

"Transaction Amount (BCY)",

FROM "Accrual Transactions"

JOIN "Period for Analysis" ON "Accrual Transactions"."Transaction Date" > "Period for Analysis"."From Date"

AND "Accrual Transactions"."Transaction Date" < "Period for Analysis"."To Date"

WHERE "Entity Type" in ( 'customer_ob_invoice' )

AND "Accrual Transactions"."Debit or Credit" = 'debit'

Join Tables with Customer Data

Join the query table with your customer data to get the above details for each customer.

Create Customer Balance Report

Create a new Pivot View and add columns as follows.

Row - Customer Name for Customers table.
Data - Opening Balance, Debit and Credit from Customer Balance query table.

Get Closing Balance (Report Formula)

To complete the customer balance report, you need to find the Closing Balance. Create a Report Formula to get this. Click the formula icon and enter the below formula.

"1. Customer Balance.Opening Balance" + "2. Opening Balance.Debit" - "3. Opening Balance.Credit"