Using Solver in Constrained Optimization Model

Hi all,

I am trying to use solver to determine optimal assortment size by product to maximize sales while being below a space constraint.

See attached example.

However, when I try to use solver with the parameters:

Maximize Total Sales while constrained to Total Space Units < Max Space Units Possible while changing Product Sizes (B7:B9) it does not understand and makes the values in B7:B9 1.05 (which do not correspond to anything and return sales/space values of 0.

I want the solver to figure out while product size to reduce to fit the space requirement while maximizing sales. In this basic example, it would be by changing cell B9 to 4 and cell B8 to 3.

Is that possible to do with Solver? Might be too complex I'm not sure!

Here is a quick fix.

- Added constraint to max space;
- Added integer constraint to variables B7:B9;
- Added max and min values to variables;
- Switched to evolutionary engine, since there are LOOKUP functions.

I'll post later a linear model.

HTH,

Francesco

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

A linear model can be built adding 12 binary variables. Then total space and sales can be expressed with simple SUMPRODUCT formulas.

One additional constraint will force Solver to chose only one size for each product.

For your last question, we must first define a starting size in column P, then find the ending one through max_size in Q; finally we instruct Solver that

$R$8:$R$10 <= $Q$8:$Q$10
$R$8:$R$10 >= $P$8:$P$10

Using this formula in column Q

=IF(AND(P8>=1,P8<=Max_size-1),P8+1,Max_size)

if one size is set to 0, Solver is free to find any value that maximizes the objective function (i.e., the constraint does not apply).

HTH,

Francesco