Managing Your Cash Flow – Analyzing Accounts Receivable In MS Excel

Every business depends on Cash Flow. It’s the lifeblood of the business. You can be operating at a loss on paper, but as long as you have positive cash flow to meet working capital needs you can stay in business. Furthermore a business can be worth something to a potential buyer even though it may not be profitable if it has cash flow. That cash flow can be looked at as an annuity and it also represents a force out there that is willing to pay for your product or service. This in itself has value so cash flow is really important in every business.

There are some great reports you can run in QuickBooks that will help you analyze your receivables and stay on top of your collections. Of course the key is to collect your receivables as quickly as possible and then knowing what your budget is making sure you reserve that cash to cover your operating costs and ultimately build equity in the business.

There are 3 main reports in QuickBooks for analyzing Accounts Receivable:

  1. Open Invoices (Shows you receivables by customer)
  2. A/R Aging Summary (Shows you A/R by customer with aging [ie 0-30, 31-60, etc..] as columns)
  3. A/R Aging detail (Shows you in sections each invoice in line with full customer and job name). This one is my favorite and when you watch the video below you will see why.

Once you run any report in QuickBooks it is very easy to export to Excel by simply clicking the export button at the top of the report:

Once this is done you can play with the data in excel to get all kinds of meaningful analysis out of it. For example, many people are visual and don’t necessarily read things in terms of “0-30, 31-60, 61-90, and so on”. What becomes much more meaningful to someone may be to see it simply in terms of the month it was due, as shown here:

This makes sense now doesn’t it? For whatever reason in the sample QuickBooks file there were many due dates that were off. In the updated spreadsheet (after the web cast) I re-formatted the due dates to reflect the terms shown and then updated the pivot table and included the year of the due date in the pivot data so that you can see how it looks when laid out completely and properly.

The video here will show you how to run the reports in QuickBooks and format the A/R Aging detail report once it is in excel. Then I go over how to sort and filter the data so that you can get it in the right format and create the pivot table. Please enjoy the web cast!


If you like this you will love our full length recorded webinar on Forecasting Your Statements In MS Excel. You and your business (or your client’s) will perform at your maximum potential with the right financial tools in place to enable you to plan the growth of your company here.

Visit my learning center right now and add this to your cart: