Formula error

[Tina]

Could anyone tell me where I went wrong?

I have sales (21), Balance on hand (136), Quantity Shipped
(0), and time in transit (56 days). My reorder point is
21*56 = 1176. My reorder quantity is 1176-136 = 1040.
This number is enormous...I can't possibly order that
much. I need help...please!!!!

Tina

 

[JMay]

Your reorder point of (21*56) = 1176 looks suspiciously wrong!

 

[Myrna Larson]

If you mean that you sell 21 each day, then your current stock is sufficient for only 6 days.
Since it takes 56 days to receive more from the supplier, you are going to run out of stock very
soon. I suspect there's something wrong with the number 21. 21 in what period of time?

 

[CLR]

You need more info......you show sales of 21, but no reference as to
time......if it's 21 units per day, you're already in trouble with only 136
in stock and a 56 day lead time......you'll run out in about 6 days.........

Vaya con Dios,
Chuck, CABGx3

 

[Tina]

21 is the number of sales for that month. I changed the
56 days to 2 months. Now it is 21*2 = 42. Then subtract
the Balance 42-136 = reorder is -93. So I wouldn't have
to reorder this product.

Tina

-----Original Message-----
If you mean that you sell 21 each day, then your current

stock is sufficient for only 6 days.

Since it takes 56 days to receive more from the supplier,

you are going to run out of stock very

soon. I suspect there's something wrong with the number 21. 21 in what period of time?

 

[CLR]

Good.......just be sure to validate your "21" figure and don't try to play
it too close to the vest in case it's just an "average" figure and you could
sell considerable more one month........and you've got "delivery problems"
to consider too, is your product available from more than one source,
etc......so probably you should keep more than just "one ordering cycle's
worth" in stock.......

Vaya con Dios,
Chuck, CABGx3

21 is the number of sales for that month. I changed the
56 days to 2 months. Now it is 21*2 = 42. Then subtract
the Balance 42-136 = reorder is -93. So I wouldn't have
to reorder this product.

Tina

-----Original Message-----
If you mean that you sell 21 each day, then your current

stock is sufficient for only 6 days.

Since it takes 56 days to receive more from the supplier,

you are going to run out of stock very

 

[Paul Corrado]

Tina,

Reorder Point = monthly demand * Lead Time + Safety Stock

Safety Stock @ 95% level of service (5% expected Out of Stock)

= standard deviation in monthly demand * square root of lead time * 1.65

In your case

ROP = 21 (units/month) * 2 months + SS
SS = ?? * 2^(1/2) * 1.65

where ?? is the standard deviation of monthly demand which you can estimate
using XL's Standard Deviation function and historical monthly sales.

HTH

PC