The average receivable collection period (ARCP) is a measure of how long it takes a company to collect cash from its customers who buy on credit. It is also known as the days sales outstanding (DSO) or the average collection period (ACP). The ARCP indicates the efficiency and effectiveness of a company’s credit policy and collection process. A lower ARCP means that the company can collect cash faster and improve its liquidity. A higher ARCP means that the company has more money tied up in accounts receivable and may face cash flow problems.
The ARCP is calculated by dividing the average accounts receivable balance by the average daily credit sales and multiplying by the number of days in a year. The formula is:
The average accounts receivable is the average of the beginning and ending accounts receivable balances for a given period, usually a month, a quarter, or a year. The average daily credit sales is the total credit sales for the period divided by the number of days in the period. The days in a year can be either 360 or 365, depending on the convention used by the company.
The Procedure of Calculating ARCP in Excel
To calculate the ARCP in Excel, we need to have the following data:
- The beginning and ending accounts receivable balances for the period
- The total credit sales for the period
- The number of days in the period
- The number of days in a year
We can use the following steps to calculate the ARCP in Excel:
- Enter the data in a worksheet, such as the one shown below:
| A | B | C | |
|---|---|---|---|
| 1 | Beginning Accounts Receivable | $50,000 | |
| 2 | Ending Accounts Receivable | $60,000 | |
| 3 | Total Credit Sales | $300,000 | |
| 4 | Days in Period | 90 | |
| 5 | Days in Year | 365 |
- Calculate the average accounts receivable by using the formula
=(A1+A2)/2in cell C1. The result is $55,000. - Calculate the average daily credit sales by using the formula
=C3/C4in cell C2. The result is $3,333.33. - Calculate the ARCP by using the formula
=C1/C2*C5in cell C3. The result is 60.27. - Format the result in cell C3 as a number with two decimal places.
The final worksheet should look like this:
| A | B | C | |
|---|---|---|---|
| 1 | Beginning Accounts Receivable | $50,000 | $55,000 |
| 2 | Ending Accounts Receivable | $60,000 | $3,333.33 |
| 3 | Total Credit Sales | $300,000 | 60.27 |
| 4 | Days in Period | 90 | |
| 5 | Days in Year | 365 |
The ARCP of 60.27 means that the company takes about 60 days on average to collect cash from its credit customers.
Example of ARCP Calculation
To illustrate the ARCP calculation with a real-world example, let us consider the following scenario:
ABC Inc. is a company that sells widgets on credit to its customers. The company’s financial statements for the year 2023 show the following information:
- The beginning accounts receivable balance on January 1, 2023 was $100,000.
- The ending accounts receivable balance on December 31, 2023 was $120,000.
- The total credit sales for the year 2023 were $1,200,000.
- The company uses 365 days as the number of days in a year.
We can use the same procedure as above to calculate the ARCP for ABC Inc. in Excel. The worksheet should look like this:
| A | B | C | |
|---|---|---|---|
| 1 | Beginning Accounts Receivable | $100,000 | $110,000 |
| 2 | Ending Accounts Receivable | $120,000 | $3,287.67 |
| 3 | Total Credit Sales | $1,200,000 | 122.18 |
| 4 | Days in Year | 365 |
The ARCP of 122.18 means that ABC Inc. takes about 122 days on average to collect cash from its credit customers. This is a very high ARCP, indicating that the company has a lot of money stuck in accounts receivable and may face liquidity issues.
Other Approaches to Calculate ARCP
There are some variations and alternatives to the ARCP formula that can be used to measure the collection efficiency of a company. Some of them are:
- Using net sales instead of credit sales. Net sales are the total sales minus returns, allowances, and discounts. This approach may provide a more accurate measure of the actual sales that are collectible.
- Using the receivables turnover ratio instead of the average daily credit sales. The receivables turnover ratio is the ratio of net credit sales to average accounts receivable. It measures how many times a company collects its receivables in a year. The ARCP can be calculated by dividing the number of days in a year by the receivables turnover ratio.
- Using the weighted average accounts receivable instead of the simple average. The weighted average accounts receivable is calculated by multiplying the accounts receivable balance at the end of each month by the number of days in that month, and then dividing the sum by the total number of days in the year. This approach may reflect the changes in the accounts receivable balance more accurately.
