Debtors Age Analysis for SYSPRO in Power BI: Full App Walkthrough

Debtors Age Analysis for SYSPRO is a Microsoft Power BI app for accounts receivable (AR) aging, aged debtors reporting and credit control on SYSPRO ERP data, with charts that follow the International Business Communication Standards (IBCS). Ageing follows Statement Date Age Analysis. Overdue amounts are aged by days past the due date.

One As Of Date slicer sets the reporting date for the whole report. When a past month-end is selected, every report page shows the debtors position as it stood on that date: the outstanding balance, the age of each invoice, the overdue amount and the amounts collected. The Latest (to date) option shows the current month up to today.

The app has 13 report pages, one reconciliation page and three information pages. This guide follows the video, page by page.

Key features

• Month-end history: ageing, overdue balances and KPIs at any past month-end, not only at today's date.
• IBCS charts with the prior month-end as the comparison on every chart: dark solid bars for actual values, outlined bars for the prior month-end, red for an increase in overdue balances and green for a decrease. Every chart is a standard Power BI visual.
• Drill-through from a customer to the unpaid and partially paid invoices and the payments allocated to them.
• Debtor balances by branch, area, salesperson, payment terms or customer class.
• Reconciliation of report balances to the SYSPRO invoice and payment tables.
• Scheduled refresh in the Power BI service.

KPI Reference

The KPI Reference page gives the purpose, calculation and a worked example for every measure, and shows which measures go back in time. Credit limits and on-hold status are current SYSPRO master data; they do not change with the As Of Date.

Config and Data Stats

The Config page sets the collection target percentage and Show Values In (Auto, K or M). It also includes or excludes unmatched payments: payments with no matching invoice, usually old payments at the start of the dataset. Matched leaves them out of the calculations; Unmatched includes them.

The Data Stats page lists the tables loaded from SYSPRO and the estimated size of the dataset.

Overview

The nine headline KPIs, with 12-month trends and cash expected by due date. Each card has a tooltip with the calculation.

KPI	Calculation
Total outstanding	Balance of all unpaid and partially paid invoices at the selected month-end: invoice values less payments and credits allocated up to that month-end. Drills through to the Debtors Summary.
Not yet due	Outstanding balance of invoices whose due date is after the selected month-end.
Overdue	Outstanding balance of invoices whose due date has passed at the selected month-end. Drills through to Debtors Overdue.
% Overdue	Overdue / Total outstanding.
Due in next 30 days	Outstanding balance falling due in the 30 days after the selected month-end.
DSO (trailing 90 days)	Total outstanding / credit sales of the last 90 days x 90.
Collection effectiveness	(Prior month-end balance + credit sales in the month - Total outstanding) / (Prior month-end balance + credit sales in the month - Not yet due).
Avg days delinquent	DSO less the DSO of the not yet due balance only.
Avg days to pay	Days from invoice date to payment date for payments allocated up to the month-end, weighted by payment value.

The credit limit and hold position shows Credit used (outstanding / credit limit), Credit available (credit limit less outstanding) and Days until limit reached (credit available / average daily invoicing of the last 90 days). The credit risk counts list the customers over limit and overdue, over limit but not overdue, and on hold; each count drills through to those customers.

Total outstanding by month and cash expected

Total Outstanding by Month shows Not yet due and Overdue for the 12 month-ends up to the As Of Date, with the overdue movement against the prior month-end above each bar. A month drills through to the customers at that month-end or to the collections for that month. Cash Expected by Due Date shows the overdue balance by age, then the amounts falling due over the next 90 days.

Overdue Age Analysis

KPIs for Total outstanding, the prior month-end and the change, Overdue, Over 90 days overdue as a % of outstanding, Overdue collected as a % of the start of the month, and Rolled to older bucket.

• Overdue ageing: overdue amounts by days past the due date (1-30, 31-60, 61-90 and 90+ days), against the prior month-end.
• Overdue Movement PM%: the change in the overdue balance since the prior month-end, as a % of the prior month-end.
• Collected against plan: plan = balance at the prior month-end of invoices falling due in the month; collected = payments allocated to those invoices in the month.
• Rolled to older bucket: balances that moved from one age bucket to an older bucket since the prior month-end.

By Group

Debtor balances by branch, area, salesperson, payment terms or customer class, selected at the top of the page. The page shows total outstanding and overdue for each group against the prior month-end, the change since the prior month-end as a % of the prior month-end, and cash expected by due date with overdue buckets. Each bar drills through to the customer transactions: the unpaid and partially paid invoices on the left and the payments allocated to them on the right.

Collections

Planned collections for the month against amounts collected, by month and by customer. KPIs: Planned this month, Collected (due this month), Not paid (now overdue), Collection rate (CR%) vs target, Overdue collected and % of start of month, and Total collected.

Top Customers

Top customers % of total outstanding. A Top N selection sets how many customers are shown, for example the top 5 or top 20, and the rest are grouped as Other. The charts compare total outstanding by customer with the prior month-end, in value and in %, and every customer drills through to the invoices and payments.

Ageing

Overdue ageing by days past the due date, alongside invoice-month ageing: outstanding amounts by invoice month, Current (invoiced this month), 30, 60, 90 and 120+ days. Every customer line drills through to the invoices and payments.

Debtors Summary and Debtors Overdue

One row per customer with ageing columns, credit limit and credit used. The Debtors Summary lists all customers with an outstanding balance; Debtors Overdue lists only the customers with an overdue balance, to start collecting the overdue amounts.

Unmatched Payments and Reconciliation

Payments with no matching invoice, and report balances reconciled to the SYSPRO invoice and payment tables (ArInvoice and ArInvoicePay). The Matched setting on the Config page leaves unmatched payments out of the calculations.

Requirements

• A Microsoft work or school account to get the app from Microsoft AppSource or Marketplace.
• A Power BI Pro or Premium Per User (PPU) licence to install the app, and permission from the Power BI admin to install template apps.
• A Microsoft on-premises data gateway for a hosted or on-premises SYSPRO SQL Server.
• A licence key from ForesightSA to connect a live SYSPRO database. The app installs with sample data, so every report page can be explored first.

Related guides

• How to install the Power BI on-premises gateway for SYSPRO
• Microsoft Power BI SYSPRO® template app on-premises connection
• SYSPRO Profit and Loss in Power BI: full app walkthrough

Useful Microsoft documentation

• Install, share and update template apps
• What is an on-premises data gateway?
• Configure scheduled refresh

SYSPRO® is a registered trademark of SYSPRO Software Ltd. ForesightSA is an independent company and is not affiliated with, endorsed by, or sponsored by SYSPRO Software Ltd.