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,

My specialty :) You are missing 2 key components to make this truly useful,
you need to know your sales velocity and lead time. I see you have weeks on
hand so you must have velocity somewhere. What calc did you use to get weeks
in inventory?

-John

"Angela" wrote:

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,

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,

John,

The sales velocity is for each of my products is different but the one
paritcular one I am having problems is manufacturing uses 2,550 pounds every
24 hours. The lead time on this particular product is 9 working days from
day of ordering to day of receipt.

I used total pounds in inventory+amount due/weekly usage to get my weeks of
inventory on hand. The formula actually looks like this =((R2+M2)/Q2).

Thank you in advance for any help you can provide.
Angela

"John Bundy" wrote:

My specialty :) You are missing 2 key components to make this truly useful,
you need to know your sales velocity and lead time. I see you have weeks on
hand so you must have velocity somewhere. What calc did you use to get weeks
in inventory?

-John

"Angela" wrote:

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,

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,

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,

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,

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,

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,