Complex problem in calculating when to order an item

[FmEdit]

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

(e-mail address removed)

Place an underscore between my first and last name if you wish te
email me direct

 

[JLGWhiz]

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.