John,

This worked perfectly.

Thank you so much and have a great day.
Angela

"John Bundy" wrote:

This is what you need

Usage per week 15,300
Usage per day 2,185.71
Min qty 3,400
Max qty 15,300
Lead time 9
Current inventory 10200
Lead cover 19,671.43
Order Amount 24,771

Your usage per week needs to be broken down by day, I assumed 7.
Next, for JIT you need to calculate the amount used during the dead time
daily usage*lead time=lead cover
So you take the max qty-current inventory (this is what has been used at
this moment and needs to be ordered, then add lead cover to cover 9 days
worth of usage. That's it!

-John

"Angela" wrote:

Len,

The relationship of the 1.75 is the lead time that it takes our facility to
get the material. The 15,300 is the max quantity is what we have space
capacity and no more (we go by the JIT system) the minimum is 3,400 lbs that
should be kept at our machines when the next arrival does come in.

I tried the =IF(OnHand TriggerAmount, 0, LeadWeeks*WeekUsage+Max-OnHand)
and came up with an answer of 0.00.

I also tried the Lead*Usage gives the amount used during the lead time or,
in other words,
the amount by which OnHand will have fallen when order arrives. Add this to
Max to get an adjusted max. Now subtract OnHand to find out how much to
order formula and gives me the total of 22125.00 which is way to much raw inventory to have on our floor.

Usage per week = 15,300
Min quantity = 3,400 (1,700 to 3,400 is kept at the machines)
Max quantity = 15,300
lead time = 9 days or 1.75 weeks
Current inventory = 10,200
Order amount = ?

Thank you,
Angela

"Len B" wrote:

Angela,
What then is the relationship between 1.75 weeks and a minimum of 3400. If
the usage is 15,300 pew week and the reorder trigger is 1.75 weeks' supply,
then I would expect the minimum to be 26,775.
Anyway, lets make a formula in words

=IF(OnHand TriggerAmount, 0, LeadWeeks*WeekUsage+Max-OnHand)

Lead*Usage gives the amount used during the lead time or, in other words,
the amount by which OnHand will have fallen when order arrives. Add this to
Max to get an adjusted max. Now subtract OnHand to find out how much to
order.

HTH
Regards
Len

"Angela" wrote in message
...
Len B,

The usage per week is 15,300 pounds (6 day work week) and the daily volume
is 2,550 pounds a day with a 9 day lead time on when the material was
ordered
to the arrival day of material. Order minimum is 1,700 pounds.

Thank you
Angela

"Len B" wrote:

You need to know usage per week so if
A2 = 15300
B2 = 2.06
C2 = =IF(B21.75,0,A2-(B2*E2))
D2 = 3400
E2 = 1940 per week say


"Angela" wrote in message
...
I have excel spreadsheet that shows how much inventory I have on hand in
weeks, how much minimum inventory to keep and the max inventory to have
I
need to know if there is a formula that can tell me exactly what to
order
for
the future?

Listed below is what I have so far:
Max Inv. # of Weeks in Inventory Order Amount Minimum Inv.
15300 2.06 ? 3400

If the inventory falls below 1.75 weeks I need a formula to show how
much
to
order according to min and max quantity.

Thank you,