Excel can sometimes be one of the most confusing applications to use. One small mistake can result in the dreaded “#VALUE!” popping up, and then wasted time trying to figure out what mistake you could have possibly made. Instead of spending all that time trying to create your own accounts receivable ledger in Excel, we’ve created it for you. Follow the directions and formulas below to quickly create your own accounts receivable ledger in Excel.
This informational guide is simply an example of how you can set up your aging report in Excel. Every business is different, so customize your sheet accordingly.
Set up your Excel sheet to include “Invoice Dates” in column A, “Invoice Numbers” in column B, and “Due Dates” in column C. Enter in your corresponding information.
Add a column for “Total Amount Due” in column E and add the corresponding information. In columns F through I, add payment columns. If customer’s are on payment plans, you can add as many or as little payment columns as needed. Enter in the corresponding information in these columns. In column J, add “Balance Due”.
In cell J3, the first cell under the “Balance Due” column, add the following formula: =E3-SUM(F3:I3). This formula finds the difference between the total amount due and what the customer has paid so far.
After the number in cell J3 has been populated, drag the small green square on the right side of the cell down to the very last customer account in your spreadsheet. This will auto-populate the formula for you, so you do not have to enter it in for every cell.
Below the last balance due cell, create a cell for “Outstanding Receivables“. This will add up all of your balances due to let you know how money is uncollected. For this cell, you will enter in the following formula: =SUM(J3:J11)
If you want to simplify accounts receivable in industrial distribution, you should automate the process as much as your inventory, order fulfillment and distribution processes. You can automate accounts receivable to increase efficiency, accelerate cash flow and reduce costs. Accounts receivable software applications available through cloud-based software subscriptions can be seamlessly integrated with your ERP […]
The main focus of industrial distribution is inventory management, order fill rates, transaction cycle times, low costs and satisfied customers. Not surprisingly, industrial distributors use ERP systems that focus on these areas. Accounts receivable management is often handled with other basic software applications lacking automated features, and even manually with spread sheets. As a consequence, […]
WHAT WE DO
Anytime Collect is a leading CLOUD-BASED accounts receivable software. Made specifically for businesses selling on credit terms, Anytime Collect AUTOMATES invoice dispute management, cash forecasting, customer communications, invoice presentment, online bill pay and credit management. The entire collections process becomes QUICKER AND EASIER.
This website stores cookies on your computer. These cookies are used to collect information about how you interact with our website and allow us to remember you. We use this information in order to improve and customize your browsing experience and for analytics and metrics about our visitors both on this website and other media. Find out more.