Joe Wilson

Joe Wilson

  • 241
  • 7.9k
  • 288.8k

How to solve the operational research problems in Excel ?

Nov 9 2016 3:14 PM
Hi,
I want to solve  an operational research model by excel's solver I explain below:
 
 
decision veriables:
 
x1 , x2 , x3 , x4 , x5, x6 , x7
 
y (i)= 0 or 1     i = 0,1,2,3,4,5,6,7   choose a x =1 or not. 
 
Goal function:
 
Max Z = 450*x1 +  375*x2 + 425*x3 + 550*x4 + 500*x5 + 500*x6 + 450*x7
 
 
Constraints :
 
1) 120*x1 + 80*x2 + 130*x3 + 195*x4 + 160*x5 + 175*x6 + 150*x7 <= 500
 
2) x1 + x2 + x3 + x4 + x5 + x6 + x7 >= 3
 
3) 23*x1 + 15*x2 + 32*x3 + 48*x4 + 34*x5 + 34*x6 + 90*x7 >= 90 - M*y1
 
4) 450*x1 + 375*x2 + 425*x3 + 550*x4 + 500*x5 + 500*x6 + 450*x7 >= 1400 - M*y2
 
5) 0.34*x1 + 0.41*x2 + 0.27*x3 + 0.3*x4 + 0.32*x5 + 0.26*x6 + 0.33*x7 >= 0.3*( x1+x2+x3+x4+x5+x6+x7) -M* y3
 
6) 2*x1 + x2 + 3*x3 + 2*x5 + x6 + 3*x7 <= 5 + M*y4
 
7) 3*x1 + 2*x2 + 7*x3 + 3*x4 + 6*x5 + 4*x6 + 5*x7 <= 13 + M*y5
 
8) 21*x1 + 8*x2 + 24*x3 + 45*x4 + 27*x5 + 30*x6 + 22*x7 >= 75 - M*y6
 
9) 75*x1 + 21*x2 + 102*x3 + 126*x4 + 74*x5 + 103*x6 + 57*x7 >= 225 - M*y7
 
y1 + y2 + y3 + y4 + y5 + y6 + y7 <= 3
 
 
 y1,y2,y3,y4,y5,y6,y7 = 0 or 1
 
so please help me to solve this kind of problems in operational research.
Thank you. 
 
 
 

Answers (2)