• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Purchase Quantity

N

Nard Cox

New Member
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)))
 

Attachments

  • 20180724 - Order quantity.xlsx
    8.9 KB · Views: 13
Last edited:
Nard Cox
Many challenges would be possible to solve.
Have You compared Your the 1st text with that file?
Have You wrote details
that someone else could get clear image of Your challenge?
Screen Shot 2018-07-27 at 16.04.26.png
eg 23 -- 2 -- 20 -- 25 == 20 is the actual quantity to purchase? How?
Do You know how many are there?
How many should be there?
Hmm... ?
 
@vletm, thanks for your reply. Unfortunately I didn't receive an email otherwise I would have responded sooner.

What do you mean compared my 1st text with that file?

My formula indicated ideally I should buy 23 pcs. However there are 20 within a full karton from the supplier. 25 fit into my karton that I use within the warehouse. In this case it wouldn't make sense to order 23 because then I have 1 full karton and 3 single pieces that I need to put together info 1 fachart carton. It's easier for both the supplier and us to just order 20 pcs, so 1 full carton and be done with it. That should be OK because the 3 deviation is within x% of the allowed deviation from the theoretical quantity.
 
Nard Cox
Compare:
Your original reply has three 'formulas' - file none; values
and one Your 'desired solution has two possible answers ... would be challenge to normal formula... hmm?

Missing answers:
Do You know how many are there?
How many should be there?
>> Isn't those difference Your desired solution?
 
@vletm, yes I've used the formula's on my data (50.000+ rows) but the results aren't satisfying, hence I didn't include them into the example file.

Regarding the 2 answers, both would be fine do it doesn't matter which one the file suggests.

Regarding the questions:
Do I know how many what there are?
How many what should be there?
What are you referring to?
 
>> hmm...? Do You really use those kind of formulas with 50k+rows?

What are you referring to?
>> Isn't those difference Your desired solution? = Purchase Quantity
>> Or what are You looking for?
 
Yes, I need an order proposal per SKU and since I need to order around 50.000 SKU's in 50.000 lines I use it :)

The column desired solution is the solution the formula should give me, I've put them in manually so we can check if the formula is working correctly.
 
@vletm, I don't understand what you don't understand.

I'll try to explain it as simple as I can:
1. I need to order x amount of pieces for 50.000 different SKU's.

2. The quantity I want to purchase per SKU is my 'Desired Solution' column in the example file. So I have that data available for 50.000 SKU's. The problem is I can't create a formula that provides me that specific value.

3. The formulas in my first message are things I've tried but that didn't work, they don't have any further relevance.
 
Nard Cox
There is one thing that I cannot understand ...
if somebody asks a question ... many times no answer.
Those answers would help those, who try to get help from here.
... and also those, who try to help ( no need to try to read others mind ).
There are no problems ... sometimes challenges.
 
@vletm, sorry but I don't understand your questions see post #5, if I don't understand the question I can't answer it unfortunately.
I'm willing to answer all your questions if you could formulate them once more in full sentences and not refer to 'that' 'what' 'those' but name them specifically.
 
Nard Cox
Yes... if someone understand question then should get answer
... but otherwise ... I would ask 'please explain one more time'.
Questions are always okay ... answers would be sometimes something else!
I try ... (#6)
'1st what' = one row 'something', which You have eg eggs.
'2nd what' = same row 'something, which You should have eg eggs again.
Isn't difference of '1st what' and '2nd what' number You should purchase?
... as You 'answered' in the begin of #8.
also (#3)
I tried to ask 'almost' with formula (and it was same as with #6).
Yes, You answered ... but Your answer wasn't in this world! - sorry!
There were only some connections with You data.
> Should I try to ask same same third time ... hmm?
> You would give information which helps others to help You
or is that other duty to do?
 
@vletm, sorry I just don't understand what it is you are asking.
(Your English isn't really helping either). I'm not trying to offend you but I simply don't understand what it is that you don't understand.

Could you perhaps ask one of you fellow ninja's to try and help me out?
 
Nard Cox
You asked help.
I tried to ask questions which would help You and Your case,
but if You cannot figure questions, then this's is not my challenge.
I do not ask difficult questions.
You wrote that, You don't understand, what did I asked ... but ...
You 'answered' Yes ... but ... You didn't mean it ... I see.
Do You understand Yourself, what have You tried to write?
Was that same as You think?
Anybody would help You as soon as they can help You.
Maybe something is missing ...
Take care.
Ps. If You write this - and think that - You'll get this - not that!
 
@vletm, like I said. I really want to answer your question but I simply don't understand your question.
Please believe me there is no foul play here, I really need help (hence this topic) and believe me if I would understand what you are asking I would be more than happy to give you all the answer so you could help me in return.

This language barrier just isn't working out so I'm hoping someone else will come along and help out.

Thank you for trying though!
 
You must log in or register to reply here.
Back
Top