I am trying to optimize the purchase orders using Excel simplex LP solver.
The problem is when I need to decide among suppliers based on the MOQs.
If the quantity to order is large, I can choose any supplier. However, if the necessary order size is small, I must choose among suppliers who accept small batches.
I need to create a decision on the amount to order that is 0 or >= MOQ.
Here is an example file: https://drive.google.com/file/d/1DnxsRe1vhsDxRL-G8jHdCBMGkO-qqfwV/view?usp=sharing
Well, I don't download files from here at all, so I chucked some data together to show you what you can do.
You can edit the approach as I have worked with cost but you may want to work with profit and solve for as maximum, then you need to think about the constraints...
The cells with if() are not used by the solver, but are in the calculation path as they are one of the arrays in the sumproduct().
You can see I have the linear engine selected as that is all that is necessary.
You may need to alter the approach as the standard solver is limited to size, but Frontline does an extended one for money...