Turnover ratio of receivables in Excel
The turnover ratio of receivables is one of the financial indicators of business activity. It shows how many times the accounts receivable turns during the analyzed period. For the calculation the required numbers from the balance sheet and the income statement (the report on profits and losses).
The significance and economic meaning of the turnover ratio of receivables
Any parameters (coefficients) of turnover illustrate the turnover rate of application of assets or liabilities: how actively and effectively the company conducts its business.
The accounts receivable reflects to cash liabilities third-party contractors of our firm. This is the money that we need (for services rendered, work performed, goods shipped).
The receivables can be called indirectly by the losses of the business. If the service is provided but money yet, the turnover we can't. In addition, there is a risk of liquidation or bankruptcy of the debtor. Therefore, the company is the lender to evaluate the financial stability and liquidity of the company necessarily.
The coefficient of accounts receivable turnover shows the speed of the refund for goods or services, describes the efficiency of interaction between company and contractors. The higher the score, the faster a company pays with their customers.
For what is needed the ratio? For finding of ways to increase of the enterprise`s profitability. The calculation of the indicator gives an idea about the dynamics of receivables. Its management is to increase in the turnover ratio. For this you need to either increase revenue or to reduce receivables.
The calculation of the ratio of receivables
For calculating ratio of receivables (RoR) you need the desired balance sheet (Section 2) and statement of financial performance (Section 1).
The classic formula looks like this:
RoR = sales revenue / average accounts receivable
To find the denominator, we take the sum of a measure at the beginning and the ending of the analyzed period and divide by 2.
Let's calculate based on the financial statements, the turnover ratio of receivables. The formula for balance:
RoR = code 2110/(code 1230 + code 1230)*0.5
The data for the numerator are taken from the Section 1, for the denominator - from the Section 2.
Use the calculation features in Excel.
To the coefficients of business activity are counted automatically, the balance sheet and the financial report should be done in Excel.
The example of the balance with the needed for calculations line:
The example of the report on the company's financial results (the desired line is highlighted):
We calculate now to the coefficient of accounts receivable turnover – substitute into the formula to the cell references of relevant reports:
You can analyze not only for the year but during the month, quarter.
The number of days for which receivables are converted into cash, is called the period of receivables turnover. From an economic point of view, the importance of this indicator is obvious: it is the average number of days in the analyzed period of time for which the money from the buyers goes into the account of the company.
The coefficient of accounts receivable turnover in days (RD) is calculated by the formula:
RD = 365(360) / RoR
Download turnover ratio of receivables in Excel
To find the rotation period by means of Excel:
To display in dynamics on the chart:
After analyzing to the calculations and the graph, we conclude: the receivable is not extinguished within the set deadlines. The company incurs to the significant losses because counterparties are unable or unwilling to repay their debts. Either the firm sells goods on credit, because the turnover ratio is so low, or the firm is a wrong credit policy.
