Excel Solver LP problem

[bobpijnenburg]

Hi All,

I have a question about the excel solver. I can optimize via the GRG nonlinear, but that might give me solely local minimums, where I must find global minimums.

[TABLE="width: 898"]
<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]period[/TD]
[TD]forecast[/TD]
[TD]Stock[/TD]
[TD]Lost sales[/TD]
[TD]Stock keeping cost[/TD]
[TD]Cost of lost sales[/TD]
[TD]Sales[/TD]
[TD]Cost of sales[/TD]
[TD][/TD]
[TD]Stock capacity[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]80[/TD]
[TD][/TD]
[TD="align: right"]40[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]6000[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]4000[/TD]
[TD][/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]6000[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]4000[/TD]
[TD][/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]6000[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]4000[/TD]
[TD][/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]94[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]6000[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]4000[/TD]
[TD][/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]6000[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]4000[/TD]
[TD][/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]96[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]6000[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]4000[/TD]
[TD][/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]82[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]6000[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]4000[/TD]
[TD][/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]6000[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]4000[/TD]
[TD][/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]6000[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]4000[/TD]
[TD][/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]6000[/TD]
[TD="align: right"]94[/TD]
[TD="align: right"]4000[/TD]
[TD][/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]6000[/TD]
[TD="align: right"]73[/TD]
[TD="align: right"]4000[/TD]
[TD][/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]6000[/TD]
[TD="align: right"]73[/TD]
[TD="align: right"]4000[/TD]
[TD][/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Goal function[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]3761800[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Resources Used[/TD]
[TD][/TD]
[TD]Resources Available[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]80[/TD]
[TD][/TD]
[TD="align: right"]80[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD]<=[/TD]
[TD="align: right"]80[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]58[/TD]
[TD]<=[/TD]
[TD="align: right"]80[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]79[/TD]
[TD]<=[/TD]
[TD="align: right"]79[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]77[/TD]
[TD]<=[/TD]
[TD="align: right"]77[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]76[/TD]
[TD]<=[/TD]
[TD="align: right"]76[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]76[/TD]
[TD]<=[/TD]
[TD="align: right"]76[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]76[/TD]
[TD]<=[/TD]
[TD="align: right"]76[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]75[/TD]
[TD]<=[/TD]
[TD="align: right"]75[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]74[/TD]
[TD]<=[/TD]
[TD="align: right"]74[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]73[/TD]
[TD]<=[/TD]
[TD="align: right"]73[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]73[/TD]
[TD]<=[/TD]
[TD="align: right"]73[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="align: right"]73[/TD]
[TD]<=[/TD]
[TD="align: right"]73[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

This is the data, A1 = "period".

The solver:
Goal function: $B$18 (Min)

Variables: $B$22:$B$33

S.t.
$B$22:$B$33<=$D$22:$D$33
$B$22:$B$33 = integer
$C$3:$C$15<=$J$3:$J$15

With GMG nonlinear it works, but not with Simplex LP.

Linearity report:

[TABLE="width: 523"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Cel[/TD]
[TD]Naam[/TD]
[TD]Oorspronkelijke waarde[/TD]
[TD]Eindwaarde[/TD]
[TD]Lineaire functie[/TD]
[/TR]
[TR]
[TD]$B$18[/TD]
[TD]Goal function[/TD]
[TD="align: right"]3761800[/TD]
[TD="align: right"]3761800[/TD]
[TD]Nee[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cel[/TD]
[TD]Naam[/TD]
[TD]Oorspronkelijke waarde[/TD]
[TD]Eindwaarde[/TD]
[TD]Vindt lineair plaats[/TD]
[/TR]
[TR]
[TD]$B$22[/TD]
[TD]Resources Used[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]Nee[/TD]
[/TR]
[TR]
[TD]$B$23[/TD]
[TD]Resources Used[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]58[/TD]
[TD]Nee[/TD]
[/TR]
[TR]
[TD]$B$24[/TD]
[TD]Resources Used[/TD]
[TD="align: right"]79[/TD]
[TD="align: right"]79[/TD]
[TD]Nee[/TD]
[/TR]
[TR]
[TD]$B$25[/TD]
[TD]Resources Used[/TD]
[TD="align: right"]77[/TD]
[TD="align: right"]77[/TD]
[TD]Nee[/TD]
[/TR]
[TR]
[TD]$B$26[/TD]
[TD]Resources Used[/TD]
[TD="align: right"]76[/TD]
[TD="align: right"]76[/TD]
[TD]Nee[/TD]
[/TR]
[TR]
[TD]$B$27[/TD]
[TD]Resources Used[/TD]
[TD="align: right"]76[/TD]
[TD="align: right"]76[/TD]
[TD]Nee[/TD]
[/TR]
[TR]
[TD]$B$28[/TD]
[TD]Resources Used[/TD]
[TD="align: right"]76[/TD]
[TD="align: right"]76[/TD]
[TD]Nee[/TD]
[/TR]
[TR]
[TD]$B$29[/TD]
[TD]Resources Used[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]75[/TD]
[TD]Nee[/TD]
[/TR]
[TR]
[TD]$B$30[/TD]
[TD]Resources Used[/TD]
[TD="align: right"]74[/TD]
[TD="align: right"]74[/TD]
[TD]Nee[/TD]
[/TR]
[TR]
[TD]$B$31[/TD]
[TD]Resources Used[/TD]
[TD="align: right"]73[/TD]
[TD="align: right"]73[/TD]
[TD]Nee[/TD]
[/TR]
[TR]
[TD]$B$32[/TD]
[TD]Resources Used[/TD]
[TD="align: right"]73[/TD]
[TD="align: right"]73[/TD]
[TD]Nee[/TD]
[/TR]
[TR]
[TD]$B$33[/TD]
[TD]Resources Used[/TD]
[TD="align: right"]73[/TD]
[TD="align: right"]73[/TD]
[TD]Nee[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cel[/TD]
[TD]Naam[/TD]
[TD]Celwaarde[/TD]
[TD]Formule[/TD]
[TD]Lineaire functie[/TD]
[/TR]
[TR]
[TD]$C$3[/TD]
[TD]Stock[/TD]
[TD="align: right"]80[/TD]
[TD]$C$3<=$J$3[/TD]
[TD]Ja[/TD]
[/TR]
[TR]
[TD]$C$4[/TD]
[TD]Stock[/TD]
[TD="align: right"]10[/TD]
[TD]$C$4<=$J$4[/TD]
[TD]Nee[/TD]
[/TR]
[TR]
[TD]$C$5[/TD]
[TD]Stock[/TD]
[TD="align: right"]18[/TD]
[TD]$C$5<=$J$5[/TD]
[TD]Ja[/TD]
[/TR]
[TR]
[TD]$C$6[/TD]
[TD]Stock[/TD]
[TD="align: right"]67[/TD]
[TD]$C$6<=$J$6[/TD]
[TD]Ja[/TD]
[/TR]
[TR]
[TD]$C$7[/TD]
[TD]Stock[/TD]
[TD="align: right"]94[/TD]
[TD]$C$7<=$J$7[/TD]
[TD]Ja[/TD]
[/TR]
[TR]
[TD]$C$8[/TD]
[TD]Stock[/TD]
[TD="align: right"]100[/TD]
[TD]$C$8<=$J$8[/TD]
[TD]Ja[/TD]
[/TR]
[TR]
[TD]$C$9[/TD]
[TD]Stock[/TD]
[TD="align: right"]96[/TD]
[TD]$C$9<=$J$9[/TD]
[TD]Ja[/TD]
[/TR]
[TR]
[TD]$C$10[/TD]
[TD]Stock[/TD]
[TD="align: right"]82[/TD]
[TD]$C$10<=$J$10[/TD]
[TD]Ja[/TD]
[/TR]
[TR]
[TD]$C$11[/TD]
[TD]Stock[/TD]
[TD="align: right"]57[/TD]
[TD]$C$11<=$J$11[/TD]
[TD]Ja[/TD]
[/TR]
[TR]
[TD]$C$12[/TD]
[TD]Stock[/TD]
[TD="align: right"]21[/TD]
[TD]$C$12<=$J$12[/TD]
[TD]Ja[/TD]
[/TR]
[TR]
[TD]$C$13[/TD]
[TD]Stock[/TD]
[TD="align: right"]0[/TD]
[TD]$C$13<=$J$13[/TD]
[TD]Ja[/TD]
[/TR]
[TR]
[TD]$C$14[/TD]
[TD]Stock[/TD]
[TD="align: right"]0[/TD]
[TD]$C$14<=$J$14[/TD]
[TD]Ja[/TD]
[/TR]
[TR]
[TD]$C$15[/TD]
[TD]Stock[/TD]
[TD="align: right"]0[/TD]
[TD]$C$15<=$J$15[/TD]
[TD]Ja
[/TD]
[/TR]
</tbody>[/TABLE]

Thanks for helping me out, it might be a simple solution, but I'm stuck for days...

Kind regards,

Bob Pijnenburg

 

[bobpijnenburg]

Extra info: the stock is the positive value of the previous stock + resources used (production) - demand. The lost sales are the negative values. The goal funtio is the somproduct of: the lost sales and their costs, the stock and their costs and the production and their costs.

thanks!