How to get a list of debtors from Sage Accounting

A client recently asked me “Where can I get a list of debtors from Sage Accounting?” This seams like a simple question but this question is actually loaded with a hidden need to really know where his business’ money is. As well as to beter understand the position of his business in terms of: assets, future cash flow and profit.

I know for a fact that the client actually wants more than just a list of debtors because the client was in the process of following up on long outstanding balances from customers that originated from sales made in the past.

What the client needs is a list of customers that show him how much is owed by his customers today or maybe on the previous month end.

Many small businesses are still under the impression that their business evolves around a month end. They have not yet recognised that money flows continuously, ignoring cut-off periods and that it will be most beneficial for that small business to run a live system. Anyway I am digressing from the point and this is a subject for another day.

There is an answer to the question of how to obtain a list of Customer Balances outstanding on a specific day. In standard accounting language this report is called a Customer Age Analysis. It is difficult to comprehend how this report was compiled by hand many years ago. Fortunately, in the Sage Accounting system it can be obtained by the click of a few buttons.

Let me describe to you how this is done and give you an example of how this report can be interpreted.

Customer Balances – Days Outstanding

In the top navigation bar click on:

• Customers, move down to
• Reports, move to the right to
• Customer Balances – Days Outstanding and
• Click on it.

In the next screen you select the

• date on which you want the outstanding customer balances for. I am going to select 30 June 2022 because the last day of the month is still the most popular day to run a Customer Balance Report for a small business.
• Leave the customer “From” and “To” empty. By leaving it empty you will get a report of all the customers.
• Select summary
• And lastly exclude customers with zero balances because I don’t want to include customers that do not own the business money. This will make the list unnecessarily long.
• Click on “View Report”

The Customer Balances – Days Outstanding Report is displayed on the next screen.

This report starts with the headings:

• Customer
• Category
• Date
• Which is self explanatory from the selections we have made in the previous step.

Next is the different exporting or saving options of this report.

Which is ideal for sharing the customer balance reports with someone else. The options are:

• Export and save as a pdf
• Excel
• Or CSV file format
• Print
• Or Email the report

When we look at the different columns of the report.

We start from the left with the

• customer names then the
• customer balances which are categorised according to their “age” or to put it differently for how long the total sales invoices are outstanding split into columns from
  • 120 days and older,
  • 90 days,
  • 60 days,
  • 30 days and then the
  • current debt for invoices outstanding since the current month of the report.
  • Lastly you get the total amount due to the business.

As a side note be aware that once a receipt from a customer is matched to an invoice it is removed from this report. An outstanding invoice remains on this report as a balance for as long as there are no receipts allocated or matched against that invoice. You can also choose for receipts to be automatically matched and taken off against the oldest balance of outstanding invoices. This option is executed in the customer set up section.

Grand totals due by customers

At the bottom you are presented with the grand total due by all the customers categorised according to how long outstanding it is.

Exporting options again

Lastly we have the option again to:

• export to excel,
• Export to pdf
• Print the page or pages
• Export to CSV and
• Email the report.

Customer Balances’ story about your business

The customer balance outstanding report is a very powerful tool and tells you a story about your business.

In my Demo company the story is as follow:

The total amount owed by all the customers is R909,816.68.

The biggest portion of the total debt is 120 days and older. It amounts R899,264.18 which constitutes 98% of the total debt outstanding.
Of this 120 days amount by far the biggest portion is owed by one customer Surf (Pty) ltd amounting to R764,265.35.

Immediately I have a ton of questions concerning these balances which answers is vital for my business’ survival:

1. Why is the business in this position with this customer?
2. Should we continue supplying them?
3. Are they able to pay this amount? Or is this a bad debt?
4. What is the impact of this outstanding amount on our ability to pay our suppliers and salaries?
5. For how long can we continue to finance this customer?
6. Who is managing this account and is intervention required on a high level.

There are more questions that can be asked and conclusions drawn from this report Please let me know in the comment section about any other conclusions or questions that you would like to be answered from a report like this.

We have 3 more debtor accounts which is significant and requires further investigation.

A positive point is the fact that for the last 90-30 days our customer balances are much smaller and appear to be under control. But hopefully it is not as a result of lower sales in those periods.

More details directly from this report

You can click anywhere on the customer line in order to get more details directly from this page about customer transactions within a balance.

If you click on any one of the customer transactions you will get more information about that specific transaction.

My No.1 customer report recommendation

My preferred way to further investigate a customer balance to get to the bottom of a customer balance is to view the customer ledger .

You can quickly find the customer ledger at the top navigation bar under:

• customers
• Down to report
• then move to the right and down again to
• Customer transactions, click on it.

In the next screen you can specify:

• Date range.
• The specific customers that you need information on.
• Categories of customer if you are using it,
• And the transaction type, if you only want to view invoices or only receipts etc. In most cases I leave the transaction type blank to select all types of transactions.

You select a custom date in order to go back as far as you want for a specific customer. In this example I have selected the end date that corresponds with the Customer balances report which is 30 June 2022.

In the customer field you select the customer account that you would like to view in this example it is Surf (Pty) Ltd.

The customer “To” field is automatically completed by Sage. I only want info on this one account, I press on View Report.

Now one of my favourite reports is displayed, which I use regularly because it provides soo much powerful information.

You can see that in the period I have selected the account starts with a zero balance. The sales invoice for goods or services delivered as Tax Invoice transactions is allocated under the debit column and these entries increase the amount due by the customer whilst the customer receipts from this customer and credit notes are allocated in the credit column and reduce the balance due by the customer.

You can also find the:

• date,
• document numbers and
• other descriptions associated with each transaction.

Sending Customer Statements

You can start communicating with the customer by sending a statement informing the customer of his outstanding amount and the details of how what this balance consist of by going to:

The top navigation bar under:

• Customers
• Down to reports move to the right and down to
• customer statement and click on it

In the next screen we complete the details of the customer statement

Selecting the:

• Customer,
• The date range,
• I am going to show a balance brought forward if any,
• Exlcude fully allocated invoices that have been paid,
• And show detail of the invoice descriptions on the statement and then
• click on “view report”

Here is the statement:

Showing only a balance brought forward because from the start date I have selected as 1 June there have been no customer transactions.

When you change the start date of the statement and click on refresh you can get much more information about this customer’s transactions.

From here I can Email it directly, print it or save it as another file format if I maybe would like to add workings to it, or highlights etc. before sending it.

Now you can head back to the customer balance report if you need to look at the customer balances again.

Show Report Options

The Show Report Options is a very handy feature of the customer balances report and it is something you will regularly use. If you click on the Show Report options button you can change your search criteria with the ability to quickly navigate to different dates, different customers or different categories.

When you click on the blue button, the report options are added to the top of the page where you can make the amendments to the search criteria. In my example I am going to:

• change the date to today’s date to view what balances are outstanding today. This is handy when running a live, up-to-date accounting system.
• Such a system is easily achievable with the powerful integration capabilities of Sage, Dext, your business’ bank and Netcash.

When you click on refresh the data is updated to reveal the report based on the parameters that you have set.

You can also select to view only 1 customer’s balance report like I am doing here now for Surf Pty Ltd.

Let’s recap and wrap it up

• Keeping track of customer balances is one of the most important aspects and management tool of a business that will assist in achieving a healthy cash flow.
• The customer balance’s Days Outstanding Report is just another name for a customer age analysis.
• It not only gives you a list of customers with balances outstanding but also tells a story of the state of a business’s debtor book.
• Data concerning customer transactions are automatically kept in Sage Accounting from where it is easy to access information in the form of readable reports that can be shared in various reporting formats.
• The Customer Balances Days Outstanding report provides valuable intel enabling a business owner or manager to make good, informed decisions about customers’ accounts and to maintain healthy customer relationships because precise information can be easily and quickly obtained.
• This in turn give an opportunity to address certain shortcomings and potential disasters within customer accounts.

Hopefully with this article I have shed some light about how easy it is to quickly get a list of customers outstanding that can help you to find out where the money is. If you would like more information about Sage Accounting check out our other videos or visit our website. I also have a link in the description if you would like to sign up for a non-obligation 30-day-free trial to Sage Accounting.