The Skimmer Boat Company manufactures three kinds of molded fiberglass recreational boats--a bass fishing boat, a ski boat, and a speedboat. The profit for a bass boat is $20,500, the profit for a ski boat is $12,000, and the profit for a speedboat is $22,300. The company believes it will sell more bass boats than the other two boats combined but no more than twice as many. The ski boat is its standard production mode, and bass boats and speedboats are modifications. The company has production capacity to manufacture 210 standard (ski-type) boats; however, a bass boat requires 1.3 times the standard production capacity, and a speedboat requires 1.5 times the normal production capacity. In addition, only 160 of the high-powered engines that are installed in the bass boats and 2 of those installed in the speedboats are available. The company wants to know how many boats of each type to produce to maximize profit. Formulate and solve an integer programming model for this problem.

Please provide screenshots of excel used to solve the problem and show formulas used. Also provide a screenshot of the solver parameters showing constraints.

Answer :

 

Bass Boat (X1)

 

Ski Boat (X2)

 

Speed Boat (X3)

 

Resource Available

 

Profit / Unit

 

$20500

 

$12000

 

$22300

 

Standard Production capacity / unit [taken to be the standard capacity of a ski boat]

 

1.3t

 

t

 

1.5t

 

210t

 

High powered engine / unit

 

1

 

0

 

2

 

160

 

Objective Function:

 

Maximize Z = 20500X1 + 12000X2 + 22300X3

 

Subject to constraint:

 

X1 <= 2(X2+X3) or X1 - 2X2 - 2X3 <= 0 [First Constraint]

 

X1 >= X2 + X3 or X1 - X2 - X3 >=0 [Second Constraint]

 

1.3tX1 + tX2 + 1.5tX3 <= 210t or 1.3X1 + X2 + 1.5X3 <= 210 [Third Constraint]

 

X1 + 2X3 <= 160 [Fourth Constraint]

 

Non Negativity Constraint

 

X1,X2,X3>=0

 

Solving it by Simplex: Optimal Solution: z = 98134000/31; x1 = 3400/31, x2 = 920/31, x3 = 780/31

 

X1 =19+21/31 ; X2 = 29+21/31; X3 = 25+5/31;

 

If the nearest integer values of variable X1 , X2 and X3 is taken then number of units required for production will be: Bass Boat = 19, Ski Boat =29 and Speed Boat = 25 and Max Z =389500 + 348000 + 557500 = $1295000

 

[Note the working of simplex to achieve optimality is given below]

 

Tableau #1

 

x1   x2   x3   s1   s2   s3   s4   z       

 

1   -2   -2   1   0   0   0   0   0   

 

1   -1   -1   0   -1   0   0   0   0   

 

1   0   2   0   0   1   0   0   160  

 

13   10   15   0   0   0   10   0   2100  

 

-20500 -12000 -22300 0   0   0   0   1   0   

 

Tableau #2

 

x1   x2   x3   s1   s2   s3   s4   z       

 

1   -2   -2   1   0   0   0   0   0   

 

-1   1   1   0   1   0   0   0   0   

 

1   0   2   0   0   1   0   0   160  

 

13   10   15   0   0   0   10   0   2100  

 

-20500 -12000 -22300 0   0   0   0   1   0   

 

Tableau #3

 

x1   x2   x3   s1   s2   s3   s4   z       

 

-1   0   0   1   2   0   0   0   0   

 

-1   1   1   0   1   0   0   0   0   

 

3   -2   0   0   -2   1   0   0   160  

 

28   -5   0   0   -15  0   10   0   2100  

 

-42800 10300 0   0   22300 0   0   1   0   

 

Tableau #4

 

x1   x2   x3   s1   s2   s3   s4   z       

 

0   -2   0   3   4   1   0   0   160  

 

0   1   3   0   1   1   0   0   160  

 

3   -2   0   0   -2   1   0   0   160  

 

0   41   0   0   11   -28  30   0   1820  

 

0   -54700 0   0   -18700 42800 0   3   6848000

 

Tableau #5

 

x1   x2   x3   s1   s2   s3   s4   z       

 

0   0   0   41   62   -5   20   0   3400  

 

0   0   41   0   10   23   -10  0   1580  

 

41   0   0   0   -20  -5   20   0   3400  

 

0   41   0   0   11   -28  30   0   1820  

 

0   0   0   0   -55000 74400 547000 41   126774000

 

Tableau #6

 

x1   x2   x3   s1   s2   s3   s4   z       

 

0   0   0   41   62   -5   20   0   3400  

 

0   0   31   -5   0   18   -10  0   780  

 

31   0   0   10   0   -5   20   0   3400  

 

0   62   0   -11  0   -41  40   0   1840  

 

0   0   0   27500 0   52900 427000 31   98134000