Sales returns to gross sales ratio is a financial metric that measures the percentage of sales that are returned by customers due to various reasons, such as defects, damages, or dissatisfaction. It is calculated by dividing the sales returns by the gross sales. A high ratio indicates that the company has a problem with its product quality or customer satisfaction, while a low ratio suggests that the company has a loyal customer base and a good reputation.
Formula
The formula for sales returns to gross sales ratio in Excel is:
=Sales_Returns/Gross_Sales
Where:
Sales_Returnsis the amount of money that the company refunded to customers for returned products.Gross_Salesis the total amount of money that the company earned from selling products before deducting any discounts, returns, or allowances.
Example
Let’s say we have the following data for a company:
| Month | Gross Sales | Sales Returns |
|---|---|---|
| Jan | $100,000 | $5,000 |
| Feb | $120,000 | $6,000 |
| Mar | $150,000 | $7,500 |
| Apr | $180,000 | $9,000 |
| May | $200,000 | $10,000 |
| Jun | $220,000 | $11,000 |
We want to calculate the sales returns to gross sales ratio for each month and the average ratio for the whole period. To do that, we can use the following steps:
- Enter the formula
=C2/B2in cell D2 and copy it down to cell D7. This will calculate the ratio for each month. - Enter the formula
=AVERAGE(D2:D7)in cell D8. This will calculate the average ratio for the whole period. - Format the cells D2:D8 as percentages with two decimal places.
The result should look like this:
| Month | Gross Sales | Sales Returns | Ratio |
|---|---|---|---|
| Jan | $100,000 | $5,000 | 5.00% |
| Feb | $120,000 | $6,000 | 5.00% |
| Mar | $150,000 | $7,500 | 5.00% |
| Apr | $180,000 | $9,000 | 5.00% |
| May | $200,000 | $10,000 | 5.00% |
| Jun | $220,000 | $11,000 | 5.00% |
| Avg | 5.00% |
Analysis
From the table, we can see that the sales returns to gross sales ratio is constant at 5% for each month and for the whole period. This means that the company has a consistent rate of customer returns, which may indicate that the company has a stable product quality and customer satisfaction. However, a 5% ratio may also be considered high for some industries, which may suggest that the company has room for improvement in reducing its returns and increasing its sales. Therefore, the company should compare its ratio with its competitors and industry benchmarks to evaluate its performance and identify areas of improvement.
Alternative Approaches
There are some alternative ways to calculate the sales returns to gross sales ratio in Excel, such as:
- Using the
SUMfunction to calculate the total sales returns and gross sales for the whole period, and then dividing them to get the average ratio. For example, the formula=SUM(C2:C7)/SUM(B2:B7)will give the same result as=AVERAGE(D2:D7). - Using the
SUMIFfunction to calculate the sales returns and gross sales for a specific month or a range of months, and then dividing them to get the ratio for that period. For example, the formula=SUMIF(A2:A7,"Jan",C2:C7)/SUMIF(A2:A7,"Jan",B2:B7)will give the ratio for January, while the formula=SUMIF(A2:A7,">=Jan",C2:C7)/SUMIF(A2:A7,">=Jan",B2:B7)will give the ratio for January to June. - Using a pivot table to summarize the data by month and calculate the ratio as a calculated field. This will allow the user to easily filter, sort, and analyze the data in different ways.
