How to Calculate and Interpret Accounts Receivable Turnover in Excel

Accounts receivable turnover is a ratio that measures how efficiently a company collects its credit sales from its customers. It indicates how many times a company collects its average accounts receivable balance in a given period, usually a year. A higher ratio means that the company is able to collect its receivables faster and more frequently, while a lower ratio means that the company has difficulty in collecting its receivables or has a lenient credit policy.

The formula for accounts receivable turnover is:

Accounts Receivable Turnover=Net Credit Sales/Average Accounts Receivable​

Where:

• Net credit sales are the total sales made on credit, minus any sales returns or allowances.
• Average accounts receivable are the average of the beginning and ending balances of accounts receivable for the period.

To calculate the accounts receivable turnover in Excel, you can use the following steps:

1. Enter the net credit sales and the accounts receivable balances for the period in separate cells. For example, if the net credit sales are $500,000 and the accounts receivable balances at the beginning and end of the year are $50,000 and $60,000, respectively, you can enter them in cells A1, B1, and C1.
2. Calculate the average accounts receivable by adding the beginning and ending balances and dividing by 2. You can use the formula =(B1+C1)/2 in cell D1.
3. Calculate the accounts receivable turnover by dividing the net credit sales by the average accounts receivable. You can use the formula =A1/D1 in cell E1.
4. Format the result as a number with two decimal places.

Here is an example of how the Excel table would look like:

The accounts receivable turnover of 9.09 means that the company collects its average accounts receivable 9.09 times in a year, or every 40 days on average. This can be calculated by dividing the number of days in a year (365) by the accounts receivable turnover ratio. Alternatively, you can use the formula =365/E1 in cell F1 to get the same result.

Another approach to calculate the accounts receivable turnover is to use the total sales and the accounts receivable turnover in days. The accounts receivable turnover in days is the average number of days that it takes for a customer to pay the company for its credit sales. The formula for the accounts receivable turnover in days is:

Accounts Receivable Turnover in Days=Average Accounts Receivable/Total Sales ​×365

The formula for the accounts receivable turnover using this approach is:

Accounts Receivable Turnover=365/Accounts Receivable Turnover in Days​

To calculate the accounts receivable turnover using this approach in Excel, you can use the following steps:

1. Enter the total sales and the accounts receivable balances for the period in separate cells. For example, if the total sales are $600,000 and the accounts receivable balances at the beginning and end of the year are $50,000 and $60,000, respectively, you can enter them in cells A1, B1, and C1.
2. Calculate the average accounts receivable by adding the beginning and ending balances and dividing by 2. You can use the formula =(B1+C1)/2 in cell D1.
3. Calculate the accounts receivable turnover in days by dividing the average accounts receivable by the total sales and multiplying by 365. You can use the formula =D1/A1*365 in cell E1.
4. Calculate the accounts receivable turnover by dividing 365 by the accounts receivable turnover in days. You can use the formula =365/E1 in cell F1.
5. Format the results as numbers with two decimal places.

Here is an example of how the Excel table would look like using this approach:

The accounts receivable turnover of 10.92 means that the company collects its average accounts receivable 10.92 times in a year, or every 33 days on average. This is slightly higher than the previous approach, which means that the company has a lower proportion of credit sales to total sales.