Accounts Receivable Ledger Format: Excel Template for Free Download

Page content

Simplify the Bookkeeping Process

The accounts receivable general ledger page does not contain details you can use for monitoring customers’ individual account balances. The accounting entries reflected on this page represent daily summary totals of all sales extended on credit and all payments collected, involving different customers’ accounts.

Standard accounting practice makes use of the accounts receivable subsidiary ledgers as means of monitoring individual balances on a per-customer basis. Since bookkeeping is a repetitive process, it would be best practice to make use of a template for accounts receivable ledger, in Excel format.

A simple template will lessen the tediousness and possible occurrences of errors for basic addition and subtraction. You can find a downloadable sample of an accounts receivable Excel subsidiary ledger at Bright Hub’s Media Gallery, under the same title as this article.

Guidelines on Using the AR Subsidiary Ledger Excel Template

In case you’re not too familiar on how to use an AR subsidiary ledger, you may refer to the following guidelines provided below. Kindly click on the image above to get a larger view, in case you need some form of reference:

In changing the general information, such as business name of customer, business address, etc., simply replace the data on the formula bar, which you can find located in the spreadsheet’s headings.
The same goes for the terms, the invoice dates, due dates, official receipt numbers, and descriptions.
Make sure that the due date is properly computed in order to take note of any prompt payment discounts or late payment charges. In addition, the user-bookkeeper can easily glean if the account is already past due. As such, it should be segregated from the regular accounts.
Invoice numbers and official receipts do not have to appear in numerical sequence since the transactions reflected on subsidiary ledgers are on a per-customer basis.
However, it is important that the dates appearing as transactions in your accounts receivable subsidiary ledger in Excel format will be in chronological order.
It is always the best practice to properly describe the nature of the transaction under the Descriptions column.
Sales invoices increase the balance of a customer’s account. In the AR-general ledger page this will be included in the total debit entries for the day’s transactions.,
A copy of an official receipt issued by the cash custodian to acknowledge the receipt of a receivable payment will serve as the subsidiary ledger’s source document. This will have a reducing effect on the balance column. In the AR-GL ledger, this forms part of the total credit entries posted for the day.
The Balance column (M) automatically reflects the effect of the amounts posted under the credit sales or Accounts Receivable column (H) and the payments under the Collection columns (I) or (J).
In cases of prompt payment discounts, as illustrated in the sample entries, the payment is allocated unde the Collections column (I), based on the discounted amount. The discount premium will be posted under the Prompt Payment Discount column (K).
It is important that the sum of the allocated amounts will equal the amount of payment received based on the cash custodian’s official receipt and the separate accounting ticket for the discounts.
The same procedure goes for the Late Payment Charges received as part of the payment collected from the customer.
Note that under the Invoice column, the reference numbers of the accounting ticker are likewise indicated to serve as trace-back information in cases of audit or locating for discrepancies.

Sound accounting practices suggest that the total of all subsidiary ledgers should be extracted and verified against the balance reflected on the AR-GL account. Proving the equality between the two will ensure that the supervising bookkeeper was able to take up all transaction entries appearing on the subsidiary accounts receivable ledgers maintained for each customer.

Reference Materials and Image Credits:

Author cscantoria’s personal accounting files.