Chris Hadfield, Keynote Speaker - Clio Cloud Conference New Orleans - Sept. 25, 2017
Get your tickets before they're gone!

Aging Receivables Report

Aging Receivables Report in QuickBooks Online and Xero

If you are syncing Clio with QuickBooks Online or Xero, you can leverage the sync to run additional reports. You can find step by step instructions here for QuickBooks Online and Xero.

Clio's Reporting functionality pulls Billing, Revenue, Contact and Matter information from the system into easily manipulated CSV files. Using basic spreadsheet software tools in programs such as Excel or Numbers you can create custom Reports.

In the following example we will create an Aging Receivables Report from the Accounts Receivable Report in Excel.

Content

Report Options
Step 1 - Convert the your CSV Report / Export in Excel to a table
Step 2 - Add a Totals row
Step 3 - Filter By Due Date
End Results
Other Optional Selections

Report Options

When generating a Report, select the appropriate filtering options to ensure you capture only the appropriate data. For details on the filtering options, see the "Accounts Receivable Report" Support Article.

Once you have selected your options, choose the CSV Output Format and click Generate Report.

Once generated, you can pull your cells to display more information, filter the cells, and perform other actions as well.

Step 1 - Convert the your CSV Report / Export in Excel to a table

Select the data range to be converted by holding the Shift key and clicking in the top-leftmost cell and the bottom-rightmost cell in the file.

Next, click on "Format as Table" and click on any of the style options.

In the "Format as Table" window, check the box beside "My table has headers", and then click OK.

If you receive a notification regarding formulas in the header row being removed, click "Yes" to continue.

Your data will now be formatted as a sortable table.

Step 2 - Add a Totals row

Once you have converted a range to a table, you can add a Totals row to that table for any columns that contain numeric values such as dollar amounts.

To add a Totals Row to your table, right click anywhere in the table to bring up a small menu. Next, click on "Table" and then "Totals Row".

In the added Totals row, click inside the cell to display a drop-down button. Click on the drop-down button for a list of common functions to add to the cell.

Selecting the "Sum" function, will add the total of all values in the column. This can be done on the Amounts, Payments, Credit Notes, and Balance Owing columns to see the Totals for those items. Other columns can be summed in the way way.

Step 3 - Filter By Due Date

Click on the drop-down button in the column header for "Due Date" to see the date filtering options.

Depending on the date range you used for your report, different year(s) and months will be available for you to toggle on and off. Leaving a month unchecked will hide the associated rows from your data set.

You can later come back to clear the filter, if you want to show all the rows again or change the month(s) you are viewing. As you make changes to the month you select, the Total row will automatically adjust. 

End Results

Users can toggle on or off, the separate months to display the totals due for what clients owe within the 1-30, 31-60, and 61-90 day range. Users who are more advanced in Excel can create custom date filters which can display the specific date groupings at the same time if desired. More about Advanced Filtering can be read in Microsoft's Advanced Filtering for Excel Support Article.

In our final results our Total row has been adjusted slightly with the exclusion of one month.

Other Optional Selections

Filtering by Client

Firms can also filter by a specific Client by only selecting that person or company's name when clicking the pull down button on the Client heading. Excel is capable of having multiple filter selections at once. 

Locking the Header Row

Select the "View" tab in the top ribbon. From the "Freeze Panes" options, select "Freeze Top Row". Now the column header is always visible regardless of how far down the sheet you scroll.

Comparing against the Web/PDF version of the A/R Report

Your firm want a list of the A/R totals based on Matter and Client. You can generate the report in the Web or PDF format that includes the Matter and Client based totals as a point of comparison. 

Comparing against the Matter Balance Summary Report (Web/PDF)

Your firm can compare their new Accounts Receivable Aging report against the Matter Balance Summary. 

This report displays the A/R, Work in Progress (WIP), and Expenses in Progress with Matter and Client based totals. The option to display the Client's Trust balance could also prove useful to help determine if there is Trust money that could be put towards any outstanding balances. To learn more, feel free to read the Matter Balance Summary Report Support Article.