Loading ...

Using QQube and Excel, how do you create an AR aging trend? | CLEARIFY - Page 1

Posted in: QQube    Excel and Excel Add-In

Using QQube and Excel, how do you create an AR aging trend?

Subscribe to RSS
  • I want to show how whether our past due receivables are increasing or decreasing at each month end over the past 12 months, or something similar, as a way of measuring our collection performance.  I will show total receivables and the over 60 portion department.  I will put this into a trend / bar chart.

    The AR subject provides the current aging based on what is currently open.  I need more than this.  I need to capture what was open items and their age at each month end.

    Any ideas?

     

    AR
    1. We have Summary Trial Balance Activity/Balance where you can compare activity/balances for 36 months.  From this you could create Accounts Receivable Turnover (ART) which is Net Credit Sales/Average Accounts Receivable.  The higher the ratio the better.  You may have to use the General/Ledger Detail Subject or Sales Subject to filter out Sales Receipt/CC purchases and arrive at the true Credit Sales.
    2. Calculate the Average Collection Period Ratio by using the TB Activity/Balance.  This is A/R/Credit Sales/365 which will give you the # of Avg Days to Pay
    3. Use the Sales Subject which has an Average Days to Pay field to help you calculate the avg days by customer - giving you a comparison of your best/poorest paying customers to your overall average.
    4. Use the Credit Terms Days/Methods in the Sales Subject to compare credit terms to actual days of payment.

    The final piece would be to create a 'what if' using SAP Crystal Dashboards to see how your cash flow would be improved by looking at avg days to pay, vs credit terms vs credit sales.

    UPDATE:  QQube Version 6.0 introduced several hundred Power BI out-of-the box examples - including a daily graphical representation of A/R using the general ledger analytic:

Page 1 of 1 (2 items)