Purcahse Quantity

[Nard Cox]

Hi all,

I have a problem that I just can't figure out. I'm building a Excel tool that will provide a suggested order quantity to purchase per SKU. It has a lot of formula with which i'm happy (forecast, safety stock, re order points, frequency, etc), however there is 1 that I just can't seem to get right, it's the one that calculates the actual quantity to purchase.

I have a 'theoretical' quantity that I would like to purchase, the only thing I need to do is to MROUND that to carton qty(quantity per carton as supplied by the supplier), Fachart qty (quantity per Karton that we use internally) and grab qty(the quantity in which the products are bundled together within a carton.

There are some limitations to the MROUND though:
The suggested quantity has to be within x% (up and down) of the suggested quantity (named MAX in my formulas, yes the name is a bit unfortunate).
Preferably I will buy full cartons (for my inbound process) and fill that into full Fachart cartons (for my inbound, replenishment and picking processes that follow). However since fachart and carton qtys are connected/related this often doesn't match.

Examples of what I want are attached.

BESTELL_MENGE = theoretical order quantity
MAX = maximum deviation from the theoretical order quantity
GRAB_QTY = quantity products are bundled within a carton
BOX_QTY = carton quantity of the supplier
FAM = quantity of products that fit in our internal boxes

This is what I made so far

IF(BESTELL_MENGE*MAX<GRAB_QTY;BESTELL_MENGE;IF(BESTELL_MENGE*MAX<GRAB_QTY*1,5;MAX(ROUND(BESTELL_MENGE/GRAB_QTY;0);1)*GRAB_QTY;IF(MIN(ABS(BESTELL_MENGE/BOX_QTY-ROUND(BESTELL_MENGE/BOX_QTY;0));ABS(BESTELL_MENGE/FAM-ROUND(BESTELL_MENGE/FAM;0)))=ABS(BESTELL_MENGE/FAM-ROUND(BESTELL_MENGE/FAM;0));MAX(ROUND(BESTELL_MENGE/GRAB_QTY;0);1)*GRAB_QTY;IF(BESTELL_MENGE/BOX_QTY-ROUNDDOWN(BESTELL_MENGE/BOX_QTY;0)>0,5;ROUNDDOWN(BESTELL_MENGE/BOX_QTY*BOX_QTY/GRAB_QTY;0)*GRAB_QTY;ROUNDDOWN(MAX(ROUNDDOWN(BESTELL_MENGE/BOX_QTY;0);1)*BOX_QTY/GRAB_QTY;0)*GRAB_QTY))))

and I've tried

IF(MIN(ABS(BESTELL_MENGE-P2);ABS(BESTELL_MENGE-Q2))=ABS(BESTELL_MENGE-P2);MROUND(BESTELL_MENGE;BOX_QTY);MROUND(FAM-1;BOX_QTY))

And I've tried:

IF(AND(FAM>BESTELL_MENGE*MAX;BOX_QTY>BESTELL_MENGE*MAX;GRAB_QTY>BESTELL_MENGE*MAX);BESTELL_MENGE;IF(AND(FAM>BESTELL_MENGE*MAX;BOX_QTY>BESTELL_MENGE*MAX);MAX(ROUND(BESTELL_MENGE/GRAB_QTY;0);1)*GRAB_QTY;IF(FAM>BESTELL_MENGE*MAX;MAX(ROUND(BESTELL_MENGE/BOX_QTY;0);1)*BOX_QTY;ROUNDDOWN(MAX(ROUNDDOWN(BESTELL_MENGE/FAM

;1)*FAM/BOX_QTY;0)*BOX_QTY)))

 

[Nard Cox]

Since I can't edit via this way: the fachart and carton qtys aren't connected. Unfortunately I can't add Attachements just jet.

 

[Nard Cox]

Topic kick