Debtors Age Analysis for SYSPRO
Debtors Age Analysis for SYSPRO
Microsoft Power BI app for accounts receivable (AR) aging, aged debtors reporting and credit control on SYSPRO ERP, with charts that follow the International Business Communication Standards (IBCS).
Debtors Analysis at any month-end
Debtors Age Analysis for SYSPRO calculates outstanding balances, overdue invoices and collections from SYSPRO invoice and payment history for any selected month-end. Designed for credit controllers, accounts receivable teams and financial managers, the app monitors customer balances, days sales outstanding (DSO), collection performance and expected cash receipts.
KPIs at the selected month-end
The KPI Reference page in the app gives the purpose, calculation and a worked example for every measure.
| KPI | Calculation |
|---|---|
| Total outstanding | Balance of all open invoices at the selected month-end: invoice values less payments and credits allocated up to that month-end. |
| 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. |
| % Overdue | Overdue / Total outstanding. |
| Due in next 30 days | Outstanding balance falling due in the 30 days after the selected month-end. |
| Overdue ageing | Overdue amounts by days past the due date: 1-30, 31-60, 61-90 and 90+ days. |
| Invoice ageing | Outstanding amounts by invoice month: Current (invoiced this month), 30, 60, 90 and 120+ days. |
| 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. |
| Planned against collected | 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. |
| Credit used, Credit available | Outstanding / credit limit; credit limit less outstanding (customers with a credit limit). |
| Amount over limit, Days until limit reached | Outstanding less credit limit per customer; credit available / average daily invoicing of the last 90 days. |
Report pages
| Page | Content |
|---|---|
| Overview | The nine headline KPIs, with 12-month trends and cash expected by due date. |
| Overdue Age Analysis, By Group, Top Customers | Debtor balances by branch, area, salesperson, payment terms or customer class, and the largest customers. |
| Collections | Planned collections for the month against amounts collected, by customer. |
| Ageing | Overdue ageing by days past the due date, alongside invoice-month ageing. |
| Debtors Summary, Debtors Overdue | One row per customer with ageing columns, credit limit and credit used. |
| Drill-through | Each customer's open invoices and the payments allocated to them. |
| Unmatched Payments, Reconciliation | Payments with no matching invoice, and report balances reconciled to SYSPRO's invoice and payment tables (ArInvoice and ArInvoicePay). |
| KPI Reference | The purpose, calculation and a worked example for every measure. |
Installing Prerequisites
- A Microsoft Power BI Pro or Premium Per User licence, and a work or school account for Microsoft AppSource.
Install and connect SYSPRO data
Install from Microsoft AppSource
Open Debtors Age Analysis for SYSPRO on Microsoft AppSource, select Get it now and sign in with a work or school account. Power BI installs the app into a new workspace with sample data.
Connect your data: parameters
Open the app, select Connect your data and enter the parameters:
| Parameter | Value |
|---|---|
| Syspro SQL Server Name | The SQL Server that holds the SYSPRO company database SERVERNAME\INSTANCE |
| SysproSQLDatabase | The SYSPRO company database SysproCompanyA |
| License Key | The licence key from ForesightSA |
| Load History From Which Date | Start date of the Calendar table (the Calendar starts at this date or at the first invoice date, whichever is earlier) 2023-01-01 |
| Which Month Does the Fiscal/Fin Year Start | Month number of the first month of the financial year 3 for March |
Check the gateway is installed and online
In the Power BI service, open Settings > Manage connections and gateways > On-premises data gateways and check that the gateway status is Online. Installation guide: www.foresightsa.net/blogs/guides/install-power-bi-on-premises-gateway-syspro (video: www.youtube.com/watch?v=iEvz1r4nHC0). Connecting Power BI to an on-premises SYSPRO SQL Server: www.foresightsa.net/blogs/guides/power-bi-syspro-on-premises-connection.
Create a connection for the SYSPRO SQL Server
Create a new connection on the online gateway for the SYSPRO SQL Server and company database (the same values as the parameters in step 2), then select it in the semantic model settings under Gateway and cloud connections.
Sign in to the data sources
- SYSPRO SQL Server (gateway connection): a SQL login with read access to the SYSPRO company database; privacy level Organizational.
- Licence check (https://sysprobi-functions.azurewebsites.net): Anonymous; privacy level Organizational.
Refresh and schedule
Refresh the semantic model, open the Overview page and check the As Of Date. Then set a scheduled refresh in the semantic model settings.
ForesightSA
Product page: www.foresightsa.net/products/debtors-age-analysis-for-syspro
Support: www.foresightsa.net/pages/syspro-bi-support · info@foresightsa.net
ForesightSA is an independent company and is not affiliated with SYSPRO Software Ltd.