How to setup Excel tables and formulas to work with Solver.

I'm trying to setup an excel model using Solver to tell me what Suppliers to buy from based on lowest cost. Each month I download a list of all open purchase requisitions (what we need to buy). This is a list of about 30-100 materials #s including quantity needed, what plant it's going to, and some purchasing specs. I have 5 different suppliers and each one has different pricing parameters. Supplier costs are a Base Price minus X dollars, the Base price varying each month plus extras(grade, gauge, width, slitting, pickling, freight). Each supplier also has different minimum and maximum buy requirements. I'd like solver to tell me which supplier to buy each material from and how much to buy based on the lowest cost. I've never used solver before so I'm not sure how to setup my tables and formulas to work with solver.

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.

First, in order to use Solver all of your data must be laid out in one single sheet: equations, constraints and the function to optimize cannot make reference to different sheets in your workbook.
You can, however, copy the table(s) and in each cell insert a formula to reference the original one, e.g. write in cell B17 in Export =Costs!E4.

Second, your constraints seem to be clear, but your basic equations to calculate the price for an x quantity from an Y producer is not (or at least, not for me).

If you can write down a representative example and explain us the logic behind, we may be able to help you set up Solver.

After looking further into your workbook, I must admit that your problem is anything but easy.

I have reordered some tables, so that equations may eventually be written only as sums and/or multiplications, but I miss too many informations to even propose a small sample solution.

Moreover, if you really have between 30 and 100 materials from 5 producers, we are speaking about 150-500 variables, and this is assuming only quantities as unknown. With fixed costs and maybe some time constraint you would need to add some binary variables, and Solver has a limit at 200. You should therefore switch to OpenSolver, a free add-in that has no limits on the number of variables.

On the bright side, the problem looks as if it is (or can be made) linear, and this means that the optimal solution can be found.

HTH,

Francesco

Yes, you can find it here.

I'll take a look at your updated workbook during the weekend.

I have a partial solution for your problem.

The objective function that calculates costs has two terms: one that depends on the quantity, and a fixed cost that will be summed only if we buy something from a seller (for a given order).

Ob = Fc*x + Vc*y

where Fc and Vc are fixed and variable costs, x and y are binary and continuous variables.
We can express the condition that if some material is bought, then there are fixed costs to be taken into account, that is

y>0 -> x=1

This constraint can be written as

y - M*x <= 0

where M is a quantity "big enough". In your problem, it is the quantity in cell G11. In cell G12 you can change transport mode and see what happens.
The remaining constraints should be self-explanatory: total quantity for each seller must be somewhere between given min and max values; and bought quantities must be at least (>=) as much as needed.
You can still use the traditional Solver, as there are 60 variables. And I do believe the setup will let you add more rows simply by dragging existing formulas.


Why is the solution partial? Because we are not seeking for the cheapest means of transportation. If you want to study this possibility, I'll give you some hint.

We should add 24 binary variables for each seller: rows(6)*transp_mode(4). Then we will write their sum in a new column, and add the constraint that this sum must be equal to 1.
The price of the chosen transp_mode can easily be found with a formula, but unfortunately the cost expression now shows products of binary variables b and continuous ones c.
If we want to linearize the problem, we must add a new variable z = b*c and add three clever constraints:

z - M*b <= 0
-c + z <= 0
c - z + M*b <= M

where M, as before, must be appropriately chosen.

It is a tedious task using Excel, and you will have to switch to OpenSolver; but don't fall for the siren song of GRG and evolutionary engines.

HTH,

Francesco

P.s. Forgot to add: with OpenSolver you find the solution in less than a second at 0% Branch and Bound tolerance, Solver needs a couple of minutes.

Ciao Francesco, thank you for your help! I've looked over this and I have to say I'm not understanding the Max qty constraint that's in column BG. Also, I've never used Solver before so I'm new to setting up constraints, can you give me a little more direction on that?

Originally Posted by osugirl7

I've looked over this and I have to say I'm not understanding the Max qty constraint that's in column BG.

We want to express costs as a linear function

(1) Ob = Fc*x + Vc*y

instead of a non-linear one

(2) Ob = x*(Fc + y*Vc)

To this end, we devise a constraint that links binary and continuous variables

(a) y - M*x <=0

The max qty in cell G11 is needed so that (a) will always hold true, no matter what y we choose.
If y = 0, x can be 0 or 1. But x = 1 would add some fixed cost to our function, therefore Solver (actually, the algorithm beneath it) will never make this choice.

What happens for y > 0?
Let y = 10 : if we set M = 11, x (our binary variable) will be forced to take the value of 1 because of (a).

The trick here is finding a value for M so that (a) is always satisfied: the max qty over all the suppliers (multiplied by a small coefficient just to be on the safe side) will suffice.

A good reference (and a much clearer explanation) can be found in
Model building in mathematical programming
H. Paul Williams
Wiley

Originally Posted by osugirl7

Also, I've never used Solver before so I'm new to setting up constraints, can you give me a little more direction on that?

You can take a look at this tutorial.

HTH,

Francesco