Solving for Returnable Container Turns/Year

[schmidtd1989]

Hello, new to the forum, I am sick of beating my head against a wall and I am looking for some help. It may be a shot in the dark and there may not be a way to solve my problem but I figured I would take the shot.

I need to determine how many returnable containers I need to ship a product. To determine this I need to know on average how many turns per year my pool of containers make.

All I know is the average amount of months by percentage that it takes to get containers back from customers. For example, 6% of containers shipped are returned in the same month in which they are shipped (month 1), 23% are returned the month after (month 2), 29% are returned month 3, 16% will be returned in month 4, 7% in month 5, 5% in month 6, 3% in month 7, 2% in month 8, 1% in month 9, 1% in month 10, 1% in month 11, and 1% in month 12. A total of 5% of what we ship is not returned.

So for example, what I want to know is if we ship 12,000 units per year, (1,000 per month average) how many containers do I need if I get return rates consistant with the percentages in the previous paragraph. Say if the data above told me I average 3 turns per year I would know I need 4,000 containers.

I have ran this out in an excel table with a lengthy formula but I want to be able to simplify this so I can quickly figure out how many containers I need for new programs that I need to budget for.

Like I said, I am not sure there is a way to get what I want but I would be greatly appreciative to anyone that can solve this riddle!

 

[JeffM]

I am going to look at your problem, but I am busy right now.

 

[Denis]

...1% in month 9, 1% in month 10, 1% in month 11, and 1% in month 12. A total of 5% of what we ship is not returned.

How precise do you need to be?
Like why not use: a total of 9% not returned for the above?
However, I don't know if that would simplify the process...
I'm sure Jeff will enlighten us

 

[JeffM]

The short answer to your question is that, based on your assumed numbers, you will need to order 4070 containers for the first year of operation. After that you will have to order an additional 50 more each month to cover unreturned containers plus whatever you need to replace damaged containers.

This of course is an answer that assumes a highly idealized situation. As a practical matter, you would almost certainly need to order more to handle variations in monthly shipment and return rates. Moreover, depending on quantity discounts and order times, you might want to order initially somewhat fewer in case shipment rates are below expectation. But all those practical details are business, not math.

Let's start with the formula.

\(\displaystyle n = s * \left \{12 - \displaystyle \left ( \sum_{j=0}^{10} \dfrac{p_{j+1} * (11 - j)}{100} \right ) \right \} \text {, where}\)

\(\displaystyle n = \text { number of containers needed for first year;}\)

\(\displaystyle s = \text { number of containers shipped monthly, and}\)

\(\displaystyle p_j = \text { percentage of containers returned during the } j^{th} \text { month after shipment.}\)

In terms of your example, that is

\(\displaystyle n = 1000 * \left \{12 - \left ( \dfrac{6 * 11}{100} + \dfrac{23 * 10}{100} + \dfrac{29 * 9}{100} + \dfrac{16 * 8}{100} +\\
\\
\dfrac{7 * 7}{100} + \dfrac{5 * 6}{100} + \dfrac{3 * 5}{100} + \dfrac{2 * 4}{100} + \dfrac{1 * 3}{100} + \dfrac{1 * 2}{100} + \dfrac{1 * 1}{100} \right) \right \} =\)

\(\displaystyle = 10 * (1200 - 66 - 230 - 261 - 128 - 49 - 30 -\\
\\
15 - 8 - 3 - 2 - 1) =\\
\\
10 * (1200 - 793) = 10 * 407 = 4070.\)

What is the logic here?

First, we assume as an idealization that the returns each month are received at end of business on the last day of the month. (If we do not make this simplifying assumption, the formula becomes more complex. Probably, the simplest alternative assumption is that the returns are all received at mid-month.)

For the first month then, we need 1000 containers.

By the start of the second month, we get 60 back, but we need 1000 more containers, for a net of 940 in addition to the 1000 needed for month 1.

By the start of the third month, we get back 60 from the second month's shipments and 230 from the first month's shipments ( a total of 290), but we need 1000 more containers, for a net of 710 in addition to the 1940 needed for months 1 and 2.

If you work this out month by month, you get 4070.

At the end of the first year, you will have shipped 12000, and will never get 5% or 600 back so you will need to order 50 a month to cover those containers never returned.

EDIT: The number that need to be ordered for delivery each month after the first year follow the formula:

\(\displaystyle \displaystyle \left ( 100 - \sum_{j=1}^{12} p_j \right) * \dfrac{s}{100}.\)

I hope denis is now enlightened.