Formula help with ordering quantity
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, |
Formula help with ordering quantity
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, |
Formula help with ordering quantity
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, |
Formula help with ordering quantity
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, |
Formula help with ordering quantity
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, |
Formula help with ordering quantity
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, |
Formula help with ordering quantity
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, |
Formula help with ordering quantity
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, |
Formula help with ordering quantity
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, |
All times are GMT +1. The time now is 01:49 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com