Report - Accounting Debtors

Introduction

This report will show the age of the debt with all transactions, then separate the summaries into a combination of filters such as general ledger/accounting, by user and by contract.

Before you start 

To access this report, please add the following employee permissions to the suitable employee roles in PGM -> Clubs -> Employee permissions.

• Reports
  • Custom
    • Accounting Debtors

Feature description

To access the report, navigate to PGM -> Reports -> All.

You can utilize the search filter to locate Accounting debtors, or alternatively, select the Custom tab to access the report.

In the report filters, the following options are available:

• Clubs - Choose the specific club for which this report will be generated. If this field is left blank, the report will encompass all clubs accessible to your employee profile.
• Date - Select a specific date; this will retrieve all transactions that are in debt up to and including that date.
• Ledger - Choose a single ledger for comparison; this will be used to analyze all transactions for the selected club(s).

Press XLSX to generate the report.

Report Extract

The report includes a variety of tabs that have been generated, each serving a specific purpose and providing distinct information. These multiple tabs are designed to enhance the usability and accessibility of the report, allowing users to navigate through different sections efficiently. Each tab is likely to contain relevant data or insights that contribute to the overall understanding of the report's subject matter.

Tab 1: Aged Debt by Accounting Category

This tab summarizes the total unsettled debt, grouped by the main accounting categories it relates to.

• AccountNumber: The unique number assigned to the specific accounting account that the debt is mapped to. If the item is not mapped to an account in the selected ledger, it will appear as "Item not mapped in this ledger".

• AccountingName: The descriptive name of the accounting account (e.g., 'Membership Revenue', 'Facility Booking'). If the item is not mapped, it will appear as "Item not mapped in this ledger".

• AmountGross: The total gross amount (including tax) of the unsettled transactions that are mapped to this accounting account.

• AmountNet: The total net amount (excluding tax) of the unsettled transactions that are mapped to this accounting account.

• AmountTax: The total tax amount of the unsettled transactions that are mapped to this accounting account.

• AmountToSettle: The total net remaining amount of all transactions that are yet to be fully paid (settled) for this accounting account.

• Past30Days: The portion of the AmountToSettle where the original transaction date was between 1 and 30 days before the Date Until filter.

• Past60Days: The portion of the AmountToSettle where the original transaction date was between 31 and 60 days before the Date Until filter.

• Past90Days: The portion of the AmountToSettle where the original transaction date was between 61 and 90 days before the Date Until filter.

• Past120Days: The portion of the AmountToSettle where the original transaction date was between 91 and 120 days before the Date Until filter.

• MoreThan120Days: The portion of the AmountToSettle where the original transaction date was more than 120 days before the Date Until filter.

Tab 2: All Transactions with Aged Debt

This is a detailed list of every individual unsettled transaction included in the report.

• ClubName: The name of the club where the transaction took place.

• UserId: The unique system ID for the customer associated with the transaction.

• UserNumber: The customer's unique number/identifier.

• UserName: The full name of the customer.

• CompanyName: The name of the company associated with the customer's payment source (if the customer is linked to a company, contract transactions only).

• UserMembershipStatus: The current status of the customer's membership or transaction type.

  • If the status comes from the user's statistics, it shows that value.

  • If the status comes from the contract: if it is 'Current' it shows as 'Live'; if it is 'Freezed' it shows as 'Freeze'; if it is 'Ended' it shows as 'Cancelled'; if it is 'NotStarted' it shows as 'Not started'.

  • If the transaction is a standalone POS transaction (not tied to a contract), the status will show 'Guest'.

• ContractStatus: The status of the contract this transaction relates to.

  • For POS transactions, it is shown as NULL (blank).

• IsPaymentInProgress: Shows if there is currently an automated payment process attempting to settle the debt for the user's payment source.

  • 1 is shown as True (A payment process is running).

  • 0 is shown as False (No payment process is running).

• PaymentPlanName: The name of the payment plan for the contract, or 'POS Transaction' if it was a point-of-sale purchase.

• TransactionDescription: A brief description of the item or reason for the transaction (e.g., 'Monthly Fee', 'Rejection fee%', 'Towel purchase').

• StartDate: The start date of the associated contract (if applicable).

• EndDate: The end date of the associated contract (if applicable).

• Source: The payment method or type used by the customer for this transaction or contract (e.g., 'Direct Debit', 'CreditCard', 'MoneyTransfer', 'Online').

• Date: The date the original transaction was recorded.

• AccountNumber: The accounting account number this item is mapped to. If not mapped, it shows "Item not mapped in this ledger".

• AccountingName: The accounting account name this item is mapped to. If not mapped, it shows "Item not mapped in this ledger".

• Split: The percentage (out of 100) of the transaction amount that is allocated to this specific accounting account, based on your accounting configuration. For example, 1.00 would be 100%.

• AmountGross: The gross amount (including tax) of the unsettled transaction.

• AmountNet: The net amount (excluding tax) of the unsettled transaction.

• AmountTax: The tax amount of the unsettled transaction.

• AmountToSettle: The net remaining amount of this individual transaction that is yet to be fully paid (settled).

• Past30Days, Past60Days, Past90Days, Past120Days, MoreThan120Days: These columns show the full AmountToSettle value in only one column, based on its age (how far the transaction date is from the 'Date Until' filter).

Tab 3: Total Debt by User

This tab summarizes the total unsettled debt for each customer, regardless of the individual transaction or accounting category. This is ideal for collection follow-up.

• ClubName: The club associated with the user's transactions.

• UserId: The unique system ID for the customer.

• UserNumber: The customer's unique number/identifier.

• UserName: The full name of the customer.

• CompanyName: The name of the company associated with the customer's payment source (if applicable, contract transactions only).

• UserMembershipStatus: The current status of the customer's membership (same logic as Tab 2).

• AmountToSettle: The total unsettled debt owed by this customer across all transactions.

• Past30Days: The total unsettled debt for this user that is aged 1-30 days.

• Past60Days: The total unsettled debt for this user that is aged 31-60 days.

• Past90Days: The total unsettled debt for this user that is aged 61-90 days.

• Past120Days: The total unsettled debt for this user that is aged 91-120 days.

• MoreThan120Days: The total unsettled debt for this user that is aged over 120 days.

Tab 4: First Date in Debt by User

This tab helps you quickly identify when a customer first started having unsettled debt.

• ClubName: The club where the first unsettled debt occurred.

• UserId: The unique system ID for the customer.

• UserNumber: The customer's unique number/identifier.

• UserName: The full name of the customer.

• CompanyName: The name of the company associated with the user (if applicable, contract transactions only).

• UserMembershipStatus: The current status of the customer's membership (same logic as Tab 2).

• FirstDate: The earliest date of an unsettled transaction for this customer.

• AmountToSettle: The total unsettled debt owed by this customer (same as Tab 3).

Tab 5: All Contracts with Aged Debt

This tab groups the debt by the active or ended contract, which is useful for contract-level management.

• ClubName: The club where the contract originated.

• UserId: The unique system ID for the customer.

• UserNumber: The customer's unique number/identifier.

• UserName: The full name of the customer.

• CompanyName: The name of the company associated with the user (if applicable, contract transactions only).

• UserMembershipStatus: The current status of the customer's membership (same logic as Tab 2).

• ContractStatus: The status of the contract (same logic as Tab 2).

• PaymentPlanName: The name of the contract's payment plan.

• StartDate: The start date of the contract.

• EndDate: The end date of the contract.

• Past30Days: The total unsettled debt for this specific contract that is aged 1-30 days.

• Past60Days: The total unsettled debt for this specific contract that is aged 31-60 days.

• Past90Days: The total unsettled debt for this specific contract that is aged 61-90 days.

• Past120Days: The total unsettled debt for this specific contract that is aged 91-120 days.

• MoreThan120Days: The total unsettled debt for this specific contract that is aged over 120 days.

Hints

• Single Ledger Only: Remember, you must select only one ledger for the report to process the data correctly and avoid an error message.

• Negative Amounts: Outstanding debt is often represented as a negative number in the system. The report displays it as a negative amount to indicate that money is owed to the business.

• Aging Calculation: The aging columns (Past30Days, Past60Days, etc.) are calculated by looking at the Transaction Date compared to the Date Until you specified in the filters.

• "Item not mapped": If you see this in the Accounting columns, it means the underlying transaction type (e.g., product, service, or contract) has not been correctly linked to an Account Number in your system's accounting configuration for the selected Ledger.