Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complex problem in calculating when to order an item
Hi,
Using Excel 2007 with SP2 I have a report which has 4 buckets, each with a different date, my objective is to determine when to trigger a message to place an order to purchase a part, the 4 buckets come pre calculated from a system report, bucket 1 keeps the system calcualations, buckets 2 to 4 are re- calculated in Excel The key columns are, Lead Time, Order Quantity and Order Point The Order Point acts like a saftety stock, meaning that most times the part will always have that quantity in stock If the On Hand + Supply - Demand dips below the Order Point and the Lead Time is greater than the next bucket date, then an order should be triggered which will be the Order Quantity (or multiples of) until the balance is greater than or equal to the Order Point It may look a simple process, in effect it is IF there are NO open purchase orders in later buckets Lead Time is 42 Days Order Quantity is 500 Order Point is 600 Example: (Bucket 1 Date is 21st September, today's date is the 7th September) In the example there is already a purchase order for 500 due between now and the 21st September AND in Bkt 3 there is a an order for 1000 due between the 16th November and the 14th December. In the case of Bkt1, as the lead time is greater than the next bucket date the balance must be equal to or greater than the order point, if the lead time was less than the bucket 2 date then there isno need to calculate. For Bkt1 we would expedite 500 from the 1000 that is due in bkt3 Bkt1 On Hand = 350 Supply = 500 Demand = 650 Balance1 200 (On Hand + Supply - Demand) The 3 colums below are what I need to add after each bucket: Buy Expedite New Balance1: (Balance1 + Buy + Expedite) Bkt2 (19th October) Balance1 = 200 Supply = 0 Demand = 450 Balance2 = -650 Buy Expedite New Balance2 Bkt3 (16th November) Balance2 = -250 Supply = 1000 Demand = 800 Balance3 = -50 Buy Expedite New Balance3 Bkt4 (14th December) Balance3 = -50 Supply = 0 Demand = 600 Balance4 = -650 Buy Expedite New Balance4 Any help or guidance on this would be extremely appreciated I have a sample workbook if anyone wishes to tackle this, I have spent weeks trying to resolve this task Regards Raymond Allan Place an underscore between my first and last name if you wish te email me direct |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complex problem in calculating when to order an item
The formula I would use is:
(Total On Hand + Total On Order) - Demands < Max Order Point = Reorder Your lesser Order points can be fillded from the Max Order Point Quantity. If you try to satisfy all Order points in the formula, it gets too complicated. Just use your Max Order Point location as a primary location and the others as secondary locations. Always issue from the primary location first to fill secondary locations before ordering. If you are looking for code to handle this, I do not expect you will get it for nothing. There are people who monitor this site who provide those kinds of services for a fee, but I am not one of them. "FmEdit" wrote in message ... Hi, Using Excel 2007 with SP2 I have a report which has 4 buckets, each with a different date, my objective is to determine when to trigger a message to place an order to purchase a part, the 4 buckets come pre calculated from a system report, bucket 1 keeps the system calcualations, buckets 2 to 4 are re- calculated in Excel The key columns are, Lead Time, Order Quantity and Order Point The Order Point acts like a saftety stock, meaning that most times the part will always have that quantity in stock If the On Hand + Supply - Demand dips below the Order Point and the Lead Time is greater than the next bucket date, then an order should be triggered which will be the Order Quantity (or multiples of) until the balance is greater than or equal to the Order Point It may look a simple process, in effect it is IF there are NO open purchase orders in later buckets Lead Time is 42 Days Order Quantity is 500 Order Point is 600 Example: (Bucket 1 Date is 21st September, today's date is the 7th September) In the example there is already a purchase order for 500 due between now and the 21st September AND in Bkt 3 there is a an order for 1000 due between the 16th November and the 14th December. In the case of Bkt1, as the lead time is greater than the next bucket date the balance must be equal to or greater than the order point, if the lead time was less than the bucket 2 date then there isno need to calculate. For Bkt1 we would expedite 500 from the 1000 that is due in bkt3 Bkt1 On Hand = 350 Supply = 500 Demand = 650 Balance1 200 (On Hand + Supply - Demand) The 3 colums below are what I need to add after each bucket: Buy Expedite New Balance1: (Balance1 + Buy + Expedite) Bkt2 (19th October) Balance1 = 200 Supply = 0 Demand = 450 Balance2 = -650 Buy Expedite New Balance2 Bkt3 (16th November) Balance2 = -250 Supply = 1000 Demand = 800 Balance3 = -50 Buy Expedite New Balance3 Bkt4 (14th December) Balance3 = -50 Supply = 0 Demand = 600 Balance4 = -650 Buy Expedite New Balance4 Any help or guidance on this would be extremely appreciated I have a sample workbook if anyone wishes to tackle this, I have spent weeks trying to resolve this task Regards Raymond Allan Place an underscore between my first and last name if you wish te email me direct |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating Averages - Very Complex PLEASE HELP | Excel Discussion (Misc queries) | |||
VBA: Calculating Complex Numbers | Excel Discussion (Misc queries) | |||
one row per item one row per order | Excel Programming | |||
Item order in ListBox | Excel Discussion (Misc queries) | |||
Calculating dates - complex scheduling problem | Excel Worksheet Functions |