# Excel Solver LP problem

#### bobpijnenburg

##### New Member
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.

 period forecast Stock Lost sales Stock keeping cost Cost of lost sales Sales Cost of sales Stock capacity 0 0 0 80 40 100 1 70 10 0 40 6000 70 4000 100 2 50 18 0 40 6000 50 4000 100 3 30 67 0 40 6000 30 4000 100 4 50 94 0 40 6000 50 4000 100 5 70 100 0 40 6000 70 4000 100 6 80 96 0 40 6000 80 4000 100 7 90 82 0 40 6000 90 4000 100 8 100 57 0 40 6000 100 4000 100 9 110 21 0 40 6000 110 4000 100 10 100 0 6 40 6000 94 4000 100 11 90 0 17 40 6000 73 4000 100 12 80 0 7 40 6000 73 4000 100 Goal function 3761800 Resources Used Resources Available 0 80 80 1 0 <= 80 2 58 <= 80 3 79 <= 79 4 77 <= 77 5 76 <= 76 6 76 <= 76 7 76 <= 76 8 75 <= 75 9 74 <= 74 10 73 <= 73 11 73 <= 73 12 73 <= 73

<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

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:

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

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

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

Kind regards,

Bob Pijnenburg

### Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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!

Replies
10
Views
349
Replies
2
Views
181
Replies
6
Views
316
Replies
1
Views
138
Replies
1
Views
203

1,203,487
Messages
6,055,713
Members
444,810
Latest member
ExcelMuch

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back