-1

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

Jeff Dammeyer
  • 656
  • 6
  • 16
Arthur Gelcer
  • 49
  • 7
  • Use an if() to set 1 or 0 according to that set point, which means if it is small then you get those suppliers and above the others, then sumproduct() will help. – Solar Mike Jul 24 '20 at 18:29
  • If you look at some of my answers you can see some solver based ones. – Solar Mike Jul 24 '20 at 18:43
  • Thank you Solar Mike for you reply. I may have not fully understood your response, but when I tried to use if(), I got the Linearity issue. Maybe, you suggested that the if() result is not based on the decision, but a setup prior to run solver. If that is the case, it won't help. Number of suppliers per group of product may reach up to 100. The idea is to get suggestions despite the amount needed. I want to avoid human errors. – Arthur Gelcer Jul 24 '20 at 20:04

1 Answers1

0

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...

enter image description here

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...

Solar Mike
  • 7,156
  • 4
  • 17
  • 32