Accounts Receivable Tracking Spreadsheet

Accounts Receivable Tracking Spreadsheet– What is the control of accounts receivable?
If we had to give a simple and objective definition for the control of accounts receivable, it would be the tracking of the income that the company has to receive from its customers who made purchases with parcel payment or future maturity.

Accounts Receivable Tracking Spreadsheet

Accounts Receivable Tracking Spreadsheet
Accounts Receivable Tracking Spreadsheet

In a simple glance you can think that accounts receivable is a mere formality of large companies, but if you look with a little more attention to the finances of your business, you will realize that even small businesses can have levels of non-compliance elevated damaging cash flow. That is why I believe that good control of your receipts can generate the following benefits for your business:

Knowledge of customers who pay per day
Analysis of receivables
Comparison with accounts payable (to understand the need for cash for future months)
Better collection schedule
Help in cash flow control
Now, if you are wondering the step by step of how to make a good control of accounts receivable, just read the post:

How to control accounts receivable
This process can vary from business to business depending on the type of system or spreadsheet of accounts receivable used, but in general, I think there are 4 essential steps in any company:

Step 1 – Record the types of accounts you can receive
This is the initial step of any minimally decent financial control. Here you will make a record of the hosting accounts that exist in your business. Normally, the most important accounts are related to the sale of products or services:


Step 2 – Register accounts receivable in unpaid sales or with future payment
With the hosting account plan ready, you can start recording accounts receivable from sales that were not paid or scheduled for future payment. That is to say, for any realized term sale, you can register a series of articles that will give you essential control information:

Reception forecast date
Reception date
Chart of accounts related to the reception
Original value
Final value (with interest, fine, etc.)
Payment status
See an example of filling in these fields in our accounts receivable spreadsheet for a future month (that is, you did not have any payments received in fact, just planned):


A good tool will show you some data automatically, the most important thing in my opinion, is the payment status, which allows you to control who did not pay and make due collections when necessary.

Step 3 – Track receipts month by month
Now that you have left all account launches to be registered, you need to control the day of receipt. Normally this practice is done in conjunction with bank statement and proof of payments sent by customers.

In that case, almost half of the clients did not make the payment due, which is a big problem for the person in charge of the control of accounts receivable from that company. Obviously, in different realities you can have different patterns and you must understand exactly how your market or business works to draw conclusions from this data.

Step 4 – Analyze your control of accounts receivable
To close, our last step refers to the possible analyzes of doing looking at a consolidated result every month. Next, I have compiled from January to April:


We could quickly have some ideas about that business:

The number of accounts receivable is falling gradually – important to understand if it is a sales problem or if we are with more customers paying sight (which would be very good)
In January we had a much higher payment delay than the average of the other months, maybe the person in charge will forget to make the payment for debtor clients
The amount of accounts receivable outstanding in February was very large (44% late) – it would be necessary to see who made those sales and under what conditions, in short, investigate further that large breach
Those are the main analyzes, but we could still do more detailed analyzes by product or by customer according to the filling of the information of their accounts receivable control.

Best practices for good control of accounts receivable
And now, if you already control your accounts to receive, maybe I like these best practices that can help you on a day-to-day basis:

-Collect (remember) payments daily
-Copper fine or interest for delays
-Offer benefits or discounts in advance
-Be careful with the use of tickets excessively
-Attention with your parcels policy (mainly for parceled tickets)
-Accompany your default indicator monthly
Use a tool for this control (indicate our accounts receivable spreadsheet)
If you want other suggestions to make, check out this post. 5 essential tips to control your accounts receivable.

Gallery of Accounts Receivable Tracking Spreadsheet

( Click Image to Enlarge )

See More!