Sales return analysis

Sales return is an event when good are returned from the buyer to the supplier for one of the following reasons: wrong orders, more goods delivered than the requested, damaged product, damaged packaging, expiration of the term … By analysing the cause of the reimbursement we may , for a longer period of time, to decide what needs to be done to reduce their number to a minimum.

In the following example we have created a table containing the following: item number, item description, initial inventory level, ordered quantities, quantities delivered, quantities returned, reason for recovery and final inventory level. Table is filled with some values, and the last column (final inventory) is calculated as a sum of initial and delivered minus returned goods. In case of returned goods, in the foreseen column, the reason for the refund shall also be indicated. We choose it from the list we created using the Data Validation technique, where we collect the data from the table below, which lists the reasons for the return.

At the end, we should count all cases in which return of goods occurred depending the reason. For that reason should use a formula:

=COUNTIF($G$5:$G$9;B12)

which is entered in cell C12, and further copied to remaining cells within that column. Sum of all their values represents total of returns, and a percent of return depending the reason is calculated by dividing cell values with that total.

Finally, we can use these values to create a simple Pie Chart which shows reasons for return of goods.