Hello.
In the attached Excel file, the objective is to minimize total transportation cost.
But unless I state that the total quantity shipped to each customer = total quantity needed by each customer, Solver generates zeroes for quantity shipped. But it doesn't make sense to be foreced to say quantity shipped must equal quantity needed ... for example due to inventory constraint in a given warehouse.
Can you advise?
Thank you
Hi
Thank you for your email and question. Your problem setup is perfect.
The Solver needs one minor change:
If you run the Solver with the Simplex LP (it is a Linear Programming problem) option instead of the GRG Nonlinear - it will work the way you wanted it to work. I tried it.
Also, you do not need the Integer constraint. It will work the same way with and without it.
Do not hesitate to ask questions any time.
Cheers
Isaac
Hello Isaac,
Thanks for your response. In the attached revised file version, there are 4 tabs -- 2 each for Simplex and GRG Nonlinear methods. Each method has 2 tabs -- 1 each with and without integer constraint.
In all 4 tabs, Solver results in the same total transportation cost i.e. 348,750 -- cell B3
Between Simplex and GRG NonLinear, there is a slight difference in Solver's results regarding quantity shipped, for customers 3 and 4.
Consistent with what you mentioned, the results are the same, with or without the integer constraint. However, intuitively, it makes sense to have such constraint for quantity shipped i.e. it wouldn't make sense to ship a quantity fraction.
Row 14 reflects total quantity shipped, encompassing all 3 warehouses.
Currently, one constraint specifies quantity shipped should = quantity needed.
If I change that from = to <=, Solver results in zeroes for quantity shipped.
If quantity needed (and consequently quantity shipped) is greater than available inventory, Solver shows the message "Solver could not find a feasible solution". Is there a way to structure the dataset or Solver settings such that one can get shipment quantities that minimizes total transportation cost, while also living within available inventory?
Thank you.
Hi
Most users use the Simplex when it is a linear problem - as we have it here. I avoid the nonlinear when the problem is linear. This problem shows that you can get the same minimum but other problems may not.
As for the constraints, the Solver cannot solve the problem if you have a conflict with the constraints.
I did see recently people using the Open Solver. This one allows you to use more variables for free. The Solver that is part of Excel allows you to run with up to 200 variables for free. With the Open Solver - you can add IF functions to accomodate your question.
Since I used Linear Programming for many years I try to use it in a way that will not have conflicts. I keep it simple.
Isaac
