Customer Debtors Dashboard

Customer Debtors Dashboard

New in Retailer 2605.1! The Customer Debtors Dashboard gives a retailer a single screen with critical information to assist in managing customer debt. The important information includes how overdue debts are, and which accounts might be a problem. This is ideal for anyone responsible for managing outstanding customer accounts.

Accessing the Dashboard

To open the Customer Debtors Dashboard, in the main screen of Retailer, go to Reports → Debtors Dashboard in the top main menu.
The feature is password-protected at level D by default (Area code 427). See Visibility and Security for information on changing password protection here.

Using the Dashboard

There are some common elements in the screen that will appear on all the tabs of this dashboard.
Alert
For multistore businesses, this dashboard will always display the total across all locations. There is no filter to show single locations.
A Date picker box is available at the top right corner of the screen. It will be set to today's date by default.
All calculations and indicators of a transaction being 'overdue' or 'number of days old' are based on this date. You may click the drop-down box to select a different date, and click Refresh for the dashboard to recalculate based on the new date. Handy for backdating to see debtor positions on a previous date.

There are two ways of indicating aging on this screen, using the next two buttons in the top right corner, Due Date Aging, and Transaction Aging. The screen will display based on Due Date Aging by default but you may click the Transaction Aging button to switch, and vice versa.
Notes
This changes the Aging Summary charts in the Overview tab (the donut charts) and the Customer Detail Aging Report grids in the Customer Aging tab.
  1. Due Date Aging - Ages each transaction by the number of days past the due date, regardless of when the transaction was created. The Aging buckets will show as Current, 1-30 days, 31-60 days, 61-90 days, 90+ days.
    Info
    The due dates are set based on Customer account aging. See Aging for more information on this.
    So if a sale was due end of the month, and today is the 10th of the following month, then this sale is 10 days overdue. This is what many retailers will use as it matches the payment terms.
  2. Transaction Aging - Ages each transaction by the number of days since the transaction was created, regardless of when the payment is due. The Aging buckets will show based on months as Current, 1 Month, 2 Months, 3+ Months, Future. So if a sale was entered on 16 April, and today was 10 May, this sale would come under Current as it's less than a month old.
The Refresh button should be used anytime the screen needs to be updated to reflect current information. The dashboard generally doesn't automatically refresh.

The last button on the right is the Export button and it allows you to save what is currently on the screen to a file. The information exported and the file type depends on the tab selected, which will be explained in later sections.

Key Performance tiles

The 7 tiles shown along the top of the dashboard also display persistently in all tabs. These are not affected by the date selection at the top, and always shows where things stand today. These are the tabs:
1. Total Debtors
2. Overdue Amount
3. Current Month Sales
4. Payments Received
5. Credit Utilization
6. Oustanding Invoices (30+)
7. Total Proforma Payments
8. Total Layby
1. Total Debtors
This shows the total dollar value of all outstanding account transactions (e.g. invoices, weekly billing charges) across all customers.
It only includes outstanding balances of all customer transactions, and sums them up. Any transactions that have been fully paid or credited will have no balances, and will not be included here. The figure consists of current, and overdue transactions together.

It also indicates how many unique customer accounts are included in this total.
2. Overdue Amount
This figure comprises the total of amounts unpaid that are now overdue. In other words, it is always based on the due date, for all transactions that still have outstanding balances, never based on transaction date, regardless of which Aging type is used at the top (Due Date and Transaction Aging buttons).
Ideally, if your customers all pay on time, then you may expect this figure to show $0.

It also indicates the number of unique customer accounts that are now in arrears.
3. Current Month Sales
This shows the total of account sales/charges done in the current month, minus payments and credits done.
A negative figure here indicates more customers are paying off their account (or getting credited) than there are new customer sales/charges.

This tile can be clicked to open a screen (Debtors List - Current Sales this month) breaking down the transactions that are included in this total.
The information shown includes:
  1. Sale date
  2. Sales stamp
  3. Customer number
  4. Customer name
  5. Sale amount
  6. Sale balance
Search boxes are available at the top to search through the list. The From and To boxes can be used to set date ranges to search, followed by clicking Search Date.
The text box on the right can be used to search a Customer name, Customer number, or sales stamp.
Click Clear Filter to remove search criteria and see the full list.

This can be printed/saved as a report, as explained further below.

The transactions can each be double clicked to reprint a receipt or invoice.
4. Payments Received
This displays the total amount of payments received in the current month, for customer accounts.
It can also be clicked to see a breakdown of the payments in a new screen (Debtors List - Payments Received).
The information displayed includes:
  1. Sale Date
  2. Sales stamp
  3. Customer number
  4. Customer name
  5. Sale amount - full amount of payment
  6. Sale balance - amount of unallocated payment
Search boxes are available at the top to search through the list. The From and To boxes can be used to set date ranges to search, followed by clicking Search Date.
The text box on the right can be used to search a Customer name, Customer number, or sales stamp.
Click Clear Filter to remove search criteria and see the full list.

This can be printed/saved as a report, as explained further below.

The payment lines can be double clicked to reprint the payment receipt.
5. Credit Utilization
This tile shows the average of the percentage of credit used up by customers, and is only applicable if there are credit limits against customer accounts.
See Setting Up A Maximum Credit / Account Limit For A Customer for more information on setting credit limits.

The number shown is averaged across all customers with a credit limit.
E.g. if there are two customers, and one has 90% credit usage while another has 50% credit usage, the resulting number here will be 70%.
It's accompanied by a percentage bar below, to visually indicate how much credit has been used up by customers. Customers who have no credit limit will not be included in this calculation. This percentage can also exceed 100% if customers go over their limit and have been allowed to continue charging/invoicing stock to their accounts.
6. Oustanding Invoices (30+)
This shows the total dollar amount of outstanding invoices / charges, where the transaction is more than 30 days old. The figure is always based on the date of the transaction being created, and is never affected by the due date, regardless of which Aging type is used at the top (Due Date and Transaction Aging buttons).

It also indicates the number of unique customer accounts that have these outstanding invoices.

The tile can be clicked to bring up a screen (Debtors List - Outstanding Invoices) listing the different outstanding transactions contributing to the total.
It's intended to quickly indicate accounts that may require chasing as it has been more than a month since their original outstanding transaction.
The information shown includes:
  1. Sale date
  2. Sales stamp
  3. Customer number
  4. Customer name
  5. Sale amount - total of sale
  6. Sale balance - amount of unpaid balance
Search boxes are available at the top to search through the list. The From and To boxes can be used to set date ranges to search, followed by clicking Search Date.
The text box on the right can be used to search a Customer name, Customer number, or sales stamp.
Click Clear Filter to remove search criteria and see the full list.

This can be printed/saved as a report, as explained further below.

Each transaction line can be double clicked to reprint the receipt/invoice.
7. Total Proforma Payments
This tile gives the total amount of payments allocated to Proformas and Invoices (from Quote and Invoice Management).

It can be clicked to show a screen (Debtors List - Proforma Payments) listing all Proforma payments made in the QIM system.
The information given includes:
  1. Sale date
  2. Sales stamp
  3. Customer number
  4. Customer name
  5. Sale amount - total paid to the Proforma
  6. Sale balance - amount of unallocated payment, should be $0.
Search boxes are available at the top to search through the list. The From and To boxes can be used to set date ranges to search, followed by clicking Search Date.
The text box on the right can be used to search a Customer name, Customer number, or sales stamp.
Click Clear Filter to remove search criteria and see the full list.

This can be printed/saved as a report, as explained further below.

Each payment line can be double clicked to reprint the payment receipt.
The arrow on the left of each payment can be clicked to see a dropdown box showing which Proforma was paid.
8. Total Layby
The Total Layby tile shows the total amount of laybys outstanding.
This is followed by the number of active laybys contributing to this total, along with how many are overdue (based on the Layby settings).

Clicking the tile will bring up a screen (Outstanding Layby Manager) which lists all customer laybys that contribute to the total outstanding laybys.
Each line will show the following:
  1. Customer Number
  2. Customer Name
  3. Layby No
  4. Layby Total
    Total value of all items on this layby.
  5. Outstanding
    Remaining balance to be fully paid for the layby.
  6. Sale Date
  7. Due Date
    Due date for all payments to be completed for this layby, based on layby settings.
  8. Status
    A label indicating status of the layby, based on whether the layby has been completed, paid or cancelled.
    This can be a combination of: No payment yet, Partially paid, Partially collected, Partially credited, Paid, Collected, Credited, along with Overdue.
A search box is available at the top to type in search words which can be a layby number, customer name or amount.
A status filter is available at the top to selectively show laybys according to their status.
A sorting box is available to choose different orders to list the laybys; Overdue first, Due date (earliest due date first), Sale date (earliest sale date first), Balance (lowest balance first)

The button in the top right (Send Overdue Reminders) can be clicked to send out email or SMS reminders on overdue laybys, and the button will indicate how many are overdue.

Each layby line can be right-clicked for further options:
  1. Preview the layby receipt (each line can also be double clicked to do this)
  2. Send individual overdue reminder notification by email or SMS

Reports

Beyond the tiles, you may also right click the space around the tiles to bring up a screen to select reports. These are 4 reports for the information shown when clicking the tiles above, ready for printing or saving to PDF. These reports show each transaction date, sales stamp, customer number, customer name, transaction amount, transaction balance and due date.
  1. Outstanding Invoices
    This report shows a list of all outstanding invoices, balance adjustments and charges.
  2. Payments Received
    This report shows the payments received from customers this month. The payment balance indicates if a payment is not fully allocated and the due date may be ignored.
  3. Current Month Sales
    This report shows the sales and charges put through to customer accounts this month, including any balance adjustments and payments.
  4. Proforma Payment List
    This report shows all payments made to Proformas or Invoices, and should have a transaction balance of $0 as they are all allocated.

Dashboard tabs

The 6 tabs can be clicked to switched between them to look at summarised information for different aspects of debtors that may need follow-up actions.

Overview tab
Customer Aging tab
Credit Utilization tab
Payment Reliability tab
Customer Value tab
Payment Forecast tab
Overview tab
The Overview tab is the first one shown by default, anytime the Dashboard is opened. It has 5 visual elements designed for a quick snapshot of debtors:

7-Day Terms Aging (Donut chart)
This shows a breakdown of the balances for all customers using 7 day terms, and only customers with a positive balance. The colour coded donut slices indicate the amount of balance owing in each 7 day period, with the total of all owed balances shown in the middle.
Slices can be included/excluded by ticking/unticking the legend tickboxes for the corresponding aging period on the right of the chart.
This chart will update if changing from Due Date Aging to Transaction Aging, and vice versa with the top buttons.
The chart does not include any customers who are overall in credit (negative balances).

30-Day+ Terms Aging (Donut chart)
This shows a breakdown of the balances for all customers using 30 day or monthly terms, and only customers with a positive balance. The colour coded donut slices indicate the amount of balance owing in each monthly period, with the total of all owed balances shown in the middle.
Slices can be included/excluded by ticking/unticking the legend tickboxes for the corresponding aging period on the right of the chart.
This chart will update if changing from Due Date Aging to Transaction Aging, and vice versa with the top buttons.
The chart does not include any customers who are overall in credit (negative balances).

Top 10 Debtors Outstanding (Bar chart)
This shows a horizontal bar chart of the top 10 customer debtors that owe the most, along with the dollar value owed on each account and the customer name. The bars are ranked with the account owing the highest balance at the top.

At-Risk Accounts (Card view)
This lists the customer accounts that are at-risk with each account shown as a card with the customer name, and the reason for the risk. It prioritises customers that are urgent at the top, and in order of:
  1. Customers over the credit limit
  2. Customers over due on payments (90+ days and 60+ days overdue)
  3. Customers approaching the credit limit (> 90% credit usage)
These cards can be clicked to open the Customer Detail screen. The Customer name will be shown at the top with the customer number, and a summary of the customer is shown in six boxes that indicate:
  1. Total Outstanding
  2. Last Payment Date
  3. Payment Terms
  4. Average Days to Pay
  5. Credit Limit
  6. Last Payment Amount
The Edit Customer Notes button in the top right can be used to add/edit notes on the account.

The customer's transactions history is also listed in the grid below the summary.
The grid is split into Invoices and Payments, and the tabs for each can be clicked to view the relevant transactions. The text search bar can be used to quickly find specific transactions based on the columns (e.g. type in OVERDUE to find all overdue transactions, or type in an amount to find transactions with that amount).

The buttons at the bottom can be used to perform the following actions:
  1. View Transaction - highlight a transaction and click this button to print/preview a receipt or invoice
  2. Email Statement - this lets you run a new statement for this customer, similar to HERE in the Customer screen.
  3. Record Payment - this button opens the Payment menu to enter a payment for this customer.
  4. Add Note - highlight a transaction and use this button to add a sale note to the transaction.
  5. Over Due Reminder - this opens the Overdue account screen where you can choose to send email or SMS reminders, similar to the process HERE.

Payment Trends (Line chart)
This shows a line chart, covering the last 6 months, and there will usually be two lines: one for Payments, and another for Invoice sales. The chart is meant to give a quick indicator of whether customer payments are keeping pace with account charges/sales.
The period is the 6 months before the current month selected in the Date picker at the top.

Export
The Export button in the top right will open a screen to choose where to save the image file of the Overview tab. This allows capturing a picture of the Overview, as a PNG image file.
Customer Aging tab
The Customer Aging tab mainly displays two grids listing the details of aged transactions for outstanding customers (positive balance). It serves to give details on what makes up the aged balances in each aging bracket for each customer.

The top grid shows the aged transactions for customers with monthly or 30 day aging, and the bottom grid shows transactions for customers with 7 day aging (normally newsagency subagents).

Both grids will display these columns:
  1. Customer number
  2. Customer name
  3. Terms
    The customer's aging type (7 days, 30 days, EOM Current, EOM Following).
  4. Credit Limit
    Customer's credit limit. A blank or zero value means no limit.
  5. Total Outstanding
    Total amount outstanding for customer, regardless of aging.
  6. Current, followed by Aging columns by month or day
    The amounts outstanding, broken down into aging brackets.
  7. Max Days
    Highest number of days past due date or transaction date for any single invoice/charge of this customer.
  8. Status
    A label indicating the worst condition (Over Limit, Critical, Overdue, Late, Current).
Clicking on a line will bring up the Customer Details screen. The Customer name will be shown at the top with the customer number, and a summary of the customer is shown in six boxes that indicate:
  1. Total Outstanding
  2. Last Payment Date
  3. Payment Terms
  4. Average Days to Pay
  5. Credit Limit
  6. Last Payment Amount
The Edit Customer Notes button in the top right can be used to add/edit notes on the account.

The customer's transactions history is also listed in the grid below the summary.
The grid is split into Invoices and Payments, and the tabs for each can be clicked to view the relevant transactions. The text search bar can be used to quickly find specific transactions based on the columns (e.g. type in OVERDUE to find all overdue transactions, or type in an amount to find transactions with that amount).

The buttons at the bottom can be used to perform the following actions:
View Transaction - highlight a transaction and click this button to print/preview a receipt or invoice
Email Statement - this lets you run a new statement for this customer, similar to HERE in the Customer screen.
Record Payment - this button opens the Payment menu to enter a payment for this customer.
Add Note - highlight a transaction and use this button to add a sale note to the transaction.
Over Due Reminder - this opens the Overdue account screen where you can choose to send email or SMS reminders, similar to the process HERE.

Right-clicking on a line brings up options to do the following:
  1. Send Overdue Account Message - All Customers on grid
  2. Send Overdue Account Message - Selected Customer

Export
The Export button in the top right will open a screen to choose where to save a copy of the grid information. This can be saved as a spreadsheet file (XLSX or CSV), or as a web file (HTML or XML).
Credit Utilization tab
The Credit Utilization tab details how much of each customer's credit limit is being used, along with figures showing the customer's payment and sales history in the last 12 months. Only customers with an outstanding balance in the last 12 months are included.

The columns will show:
  1. Customer Number
  2. Customer Name
  3. Credit Limit
    Customer's credit limit. A blank or zero value means no limit.
  4. Limit Status
    LIMITED if the customer has a credit limit, otherwise, NO LIMIT.
  5. Outstanding
    Current total outstanding balance for customer.
  6. Utilization Pct
    Percentage showing how much of credit limit is used (comparing outstanding balance).
  7. Available Credit
    Amount of credit available for customer. This can be negative if they go over the credit limit.
  8. Payments 12m
    Total of payments received from customer in last 12 months.
  9. Sales 12m
    Total of invoice sales or charges on customer account in last 12 months.
  10. Avg Monthly Sales
    The total sales divided over 12 months - average.
  11. Avg Days to Pay
    The average number of days from account invoice or charge being made and the first payment allocated to it. This does not include transactions done today.
  12. Risk Level
    A label indicating how risky the customer is based on how much credit has been used. This is blank if the customer has no credit limit. The labels, in order of increasing risk are; Low, Moderate (> 50%), High (> 75%), Critical (> 90%), Over Limit.
  13. Recommendation Action
    A label indicating the recommended follow-up action for customer. One of the following:
    1. HOLD ORDERS - Collect payment before processing
      This customer is over the limit, and should pay account before any further sales.
    2. CONSIDER INCREASE - Good payment history
      Using more than 80% of credit limit, but paying account promptly, average days to pay has been 45 or fewer.
    3. MONITOR CLOSELY - New customer
      Using more than 75% of credit limit, and account is less than 6 months old.
    4. REVIEW - Low utilization or inactive
      Using less than 10% of credit limit, and account is more than 12 months old. Low activity.
    5. NO ACTION - Within normal range
      None of the above conditions apply to this account. Likely active account and paying on time.
    6. STANDARD TERMS - No limit set
      Customer has no credit limit.
Export
The Export button in the top right will open a screen to choose where to save a copy of the grid information. This can be saved as a spreadsheet file (XLSX or CSV), or as a web file (HTML or XML).
Payment Reliability tab
The Payment Reliability tab scores customers on how reliably they have paid their account invoices or charges, using the last 6 months of data. The grid of customers shown, sorts the lowest scores at the top, meaning the highest risk, or worst payers at the top, and lists the following information for each customer:
  1. Customer number
  2. Customer name
  3. Payment Reliability Score
    A percentage score of how well the customer has been paying. A score of 100% means a customer has paid all invoices/charges.
  4. Total Invoiced 6m
    The total amount of invoices or charges over the last 6 months .
  5. Total Paid 6m
    The total amount of payments made over the last 6 months.
  6. Outstanding 6m
    The remaining outstanding balance of invoices/charges that have not been paid, in the last 6 months.
  7. Risk Category
    A label indicating how reliable the customer is; Excellent, Good, Average, Poor, At Risk, No Activity
Export
The Export button in the top right will open a screen to choose where to save a copy of the grid information. This can be saved as a spreadsheet file (XLSX or CSV), or as a web file (HTML or XML).
Customer Value tab
The Customer Value tab ranks customers according to how much revenue they contribute over the last 12 months along with their tier.
The list shows the following:
  1. Customer name
  2. Revenue (12M)
    The total amount of invoice sales/charges for the customer over the last 12 months.
  3. Gross Profit
    The total amount of profit, after deducting total cost of stock from the total revenue from the customer.
  4. Margin %
    A percentage number taken from the Gross Profit divided by the Revenue.
  5. Customer Tier
    A tier label indicating how much revenue the customer has produced over the last 12 months. The tiers setup is explained below. The default tier names have colour-coded highlights: Bronze, Silver, Gold, Platinum.
  6. Avg Payment Days
    The average number of days between an invoice/charge being created for the customer, to the first payment allocated to it.
The tiers can be configured and changed under Setup → Customer → Customer Value Tiers.
To add a new tier:
  1. Click the Add button to add a new Tier.
  2. Enter the next sequential number in Tier ID.
  3. Enter a name in Tier Name. Make this something easy to identify.
  4. Set the Min Revenue and Max Revenue. This is the lower and upper limit of the value tier range. Make sure that values don't overlap.
    E.g. Bronze tier at 0 to 499.99 and Silver tier at 500 to 999.99 is fine, but Bronze at 0 to 500 and Silver at 500 to 1000 will not work due to 500 overlapping
To delete a tier, click and select the tier then click the Delete button below. Take care not to leave gaps in the tier values, as customers with revenue in missing ranges will show up as Standard tier (similar to customers with $0 revenue in the list).

Export
The Export button in the top right will open a screen to choose where to save a copy of the grid information. This can be saved as a spreadsheet file (XLSX or CSV), or as a web file (HTML or XML).
Payment Forecast tab
The Payments Forecast tab displays a bar chart forecasting when money is expected to be collected, based on the due dates of invoice sales or charges. This will generally cover the next 8 weeks.

Where applicable, up to 6 time periods will be displayed in the chart indicating when payment is expected:
  1. Week 1
    Due in next 7 days
  2. Week 2
    Due in 8 to 14 days
  3. Week 3
    Due in 15 to 21 days
  4. Week 4
    Due in 22 to 30 days
  5. Week 5-8
    Due in more than 30 days
  6. Overdue
    Already past due date
The chart does not predict or analyse customer payment behaviour and history, it only reports expectations based on the due dates from the customer account aging.

Export
The Export button in the top right will open a screen to choose where to save the image file of the Payment Forecast tab. This allows capturing a picture of the charts, as a PNG image file.
 


    • Related Articles

    • General Debtors Report

      Report Summary The General Debtors Report allows you to see a list of your customers and how much they owe at a chosen date. Effectively, the report can be backdated to see what customer accounts would have owed on a past date, and whether they were ...

    • newsXpress Weblink Dashboard

      To make accessing all of your web orders easier we have created a Web link Dashboard that aggregates all of your orders from all of the NewsXpress related websites.  To access the Web link Dashboard, please follow the steps below Go ...

    • Setting up add customer screen

      You can customize the screen when adding new customers to remove any information that isn't needed for your customers. Click on Customer > Search > Add This will bring up the New customer details screen. Click on the settings cog at the top right. ...

    • Change Customer Barcodes

      If required, you can change the barcode which is automatically generated when a new customer is created. This might be because you have got pre-printed customer cards \ loyalty which have their own barcode on them. Click HERE to learn more about ...

    • Changing Customer Menu or Pay Menu Buttons

      Customer Screen Menu In the Customers screen, when looking at a Customer account, a menu can be used to perform actions on the account such as paying the account, running a report, or starting/stopping newspaper runs. You can change what options ...