Issue/Symptom/Question

How to create custom formulas/columns that would result in aging buckets of 0-30, 31-60, etc. for AP and AR workbench panels.

Applies To

• Workbench AP & AR Aging Panels
• Adding custom columns to the Panel for Aging Buckets

Resolution/Fix/Answer

Using the Formula button within the AR Aging report, you can add the five aging columns (Current, 31-60, 61-90, Over 90, and Retention) using the following steps:

1. To add a Current column, follow these steps:
   1. Enter Current in the ‘Name’ field.
   2. Enter the following in the ‘Formula’ field:
      • IIF([Days Total]<31,[Receivables - Invoice Currency],0)
   3. Use a data type of Number and select the appropriate Display Format.
   4. Click Add and you’ll see that column added to the grid.
      
      
2. To add a 31-60 column, follow these steps (overriding the already displayed information):
   
   1. Enter 31-60 in the ‘Name’ field.
   2. Enter the following in the ‘Formula’ field:
      • IIF([Current]<>0,0,IIF([Days Total]<61,[Receivables - Invoice Currency],0))
   3. Use a data type of Number and select the appropriate Display Format.
   4. Click Add and you’ll see that column added to the grid.
       
3. To add a 61-90 column, follow these steps:
   1. Enter 61-90 in the ‘Name’ field.
   2. Enter the following in the ‘Formula’ field:
      • IIF([Current]<>0,0,IIF([31-60]<>0,0,IIF([Days Total]<91,[Receivables - Invoice Currency],0)))
   3. Use a data type of Number and select the appropriate Display Format.
   4. Click Add and you’ll see that column added to the grid.
       
4. To add an Over 90 column, follow these steps:
   1. Enter Over 90 in the ‘Name’ field.
   2. Enter the following in the ‘Formula’ field:
      •  IIF([Days Total]>90,[Receivables - Invoice Currency],0)
   3. Use a data type of Number and select the appropriate Display Format.
   4. Click Add and you’ll see that column added to the grid.
      
      
5. To add a Retention column, follow these steps (not required but I’ll document just in case):
   1. Enter Retention in the ‘Name’ field.
   2. Enter the following in the ‘Formula’ field:
      •  IIF([Retention Receivables - Invoice Currency]<>0,[Retention Receivables – Invoice Currency],0)
   3. Use a data type of Number and select the appropriate Display Format.
   4. Click Add and you’ll see that column added to the grid. 
      
      

You don’t have to follow the 30/60/90/Over 90 format – you can adjust the values as needed. So this could be a 40/80/120/Over 120 format or a 30/55/95/Over 95 (for example).

Once you have created the columns, your grid will look something like the following: