Accounts receivable investment is the amount of money that a company invests in its accounts receivable, which are the payments owed by customers for products or services delivered on credit. Accounts receivable investment is calculated by multiplying the average accounts receivable balance by the number of days in the accounting period, and then dividing by 365. The formula is:
![\[Accounts\ receivable\ investment = \frac{Average\ accounts\ receivable \times Number\ of\ days}{365}\]](https://excelcalculations.refrigeratordiagrams.com/wp-content/ql-cache/quicklatex.com-bd402c66aa6f21fb53b825cc7c6e00ba_l3.svg "Rendered by QuickLaTeX.com")
The basic theory behind accounts receivable investment is that it represents the opportunity cost of extending credit to customers, as the company could have used the cash for other purposes, such as investing in inventory, paying off debt, or earning interest. Therefore, accounts receivable investment should be minimized as much as possible, while still maintaining good customer relationships and sales volume.
The procedures for calculating accounts receivable investment in excel are:
- Obtain the net credit sales and the accounts receivable balances for each month or quarter of the accounting period from the income statement and the balance sheet, respectively.
- Calculate the average accounts receivable balance for each month or quarter by adding the beginning and ending balances and dividing by two.
- Calculate the accounts receivable turnover ratio for each month or quarter by dividing the net credit sales by the average accounts receivable balance.
- Calculate the days sales outstanding (DSO) for each month or quarter by dividing 365 by the accounts receivable turnover ratio.
- Calculate the accounts receivable investment for each month or quarter by multiplying the average accounts receivable balance by the DSO, and then dividing by 365.
- Sum up the accounts receivable investment for each month or quarter to get the total accounts receivable investment for the accounting period.
To illustrate these steps, let’s make a scenario with some hypothetical data. Suppose a company has the following net credit sales and accounts receivable balances for the year 2023:
Table
| Month | Net Credit Sales | Accounts Receivable |
|---|---|---|
| Jan | $100,000 | $20,000 |
| Feb | $120,000 | $25,000 |
| Mar | $150,000 | $30,000 |
| Apr | $140,000 | $35,000 |
| May | $160,000 | $40,000 |
| Jun | $180,000 | $45,000 |
| Jul | $200,000 | $50,000 |
| Aug | $220,000 | $55,000 |
| Sep | $240,000 | $60,000 |
| Oct | $260,000 | $65,000 |
| Nov | $280,000 | $70,000 |
| Dec | $300,000 | $75,000 |
Using the formulas above, we can calculate the average accounts receivable balance, the accounts receivable turnover ratio, the DSO, and the accounts receivable investment for each month as follows:
Table
| Month | Average Accounts Receivable | Accounts Receivable Turnover Ratio | DSO | Accounts Receivable Investment |
|---|---|---|---|---|
| Jan | $20,000 | 5 | 73 | $3,986 |
| Feb | $22,500 | 5.33 | 68.5 | $4,479 |
| Mar | $27,500 | 5.45 | 66.9 | $5,352 |
| Apr | $32,500 | 4.31 | 84.7 | $8,012 |
| May | $37,500 | 4.27 | 85.5 | $9,329 |
| Jun | $42,500 | 4.24 | 86.3 | $10,677 |
| Jul | $47,500 | 4.21 | 86.7 | $11,974 |
| Aug | $52,500 | 4.19 | 87.1 | $13,314 |
| Sep | $57,500 | 4.17 | 87.5 | $14,687 |
| Oct | $62,500 | 4.16 | 87.7 | $15,993 |
| Nov | $67,500 | 4.15 | 88 | $17,315 |
| Dec | $72,500 | 4.14 | 88.2 | $18,589 |
The total accounts receivable investment for the year 2023 is the sum of the accounts receivable investment for each month, which is $144,697.
To explain the results using an excel table, we can use the following format:
Table
| Year | Net Credit Sales | Average Accounts Receivable | Accounts Receivable Turnover Ratio | DSO | Accounts Receivable Investment |
|---|---|---|---|---|---|
| 2023 | $2,400,000 | $45,000 | 53.33 | 6.8 | $144,697 |
This table shows that the company had net credit sales of $2,400,000 and an average accounts receivable balance of $45,000 for the year 2023. The accounts receivable turnover ratio was 53.33, which means that the company collected its average accounts receivable balance 53.33 times during the year. The DSO was 6.8, which means that it took the company 6.8 days on average to collect cash from its customers. The accounts receivable investment was $144,697, which means that the company invested $144,697 in its accounts receivable for the year 2023.
One possible approach to reduce the accounts receivable investment is to offer discounts or incentives to customers who pay early or on time. This can increase the cash flow of the company and reduce the DSO. Another possible approach is to implement stricter credit policies and collection procedures to ensure that customers pay their invoices promptly. This can also decrease the DSO and the average accounts receivable balance.
