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.

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

 

[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!