GSB 622 Decision Support Systems LINEAR PROGRAMMING & EXCEL SOLVER PART A: LINEAR PROGRAMMING EXAMPLE -- MAXIMIZATION You have been hired as a financial manager for Real Cool Hot Tubs (you cannot wait to get promoted high enough to change the company name

GSB 622 Decision Support Systems

LINEAR PROGRAMMING & EXCEL SOLVER

PART A: LINEAR PROGRAMMING EXAMPLE -- MAXIMIZATION

You have been hired as a financial manager for Real Cool Hot Tubs (you cannot wait to get promoted high enough to change the company name!). The company sells two models of hot tubs: Aqua-Max and Hydro-Super. The company purchases prefabricated fiberglass hot tub shells and installs a common water pump and tubing into each hot tub. Every Aqua-Max requires 9 hours of labor and 12 feet of tubing. Every Hydro-Super requires 6 hours of labor and 16 feet of tubing. Demand for these products is such that each Aqua product can be sold to generate a profit of $350 and each sale of Hydro generates a profit of $300. The company expects to have 200 pumps, 1566 hours of labor and 2880 feet of tubing available for the next production cycle. You have been charged with determining the optimal number of each model to produce in order to maximize profits.

The linear programming model representation of this problem consists of:

 

Maximize: 350X1 + 300 X2

Constraints: 1X1 + 1X2 <-.. 200

9X1 + 6X2 <= 1566

12X1 + 16X2 <= 2880

1X1 >. 0

1X2 >= 0

 

Use Excel Solver (as explained on subsequent pages) to determine the optimal number of each model of hot tub to produce in order to maximize profit.

Analyze the Answer Report that is produced and answer the following questions. Then upload the spreadsheet AND your answer to these questions for credit.

1. According to the Solver solution, what is the optimal number of Aqua tubs to produce given the constraints of the problem?

2. According to the Solver solution, what is the optimal number of Hydro tubs to produce given the constraints of the problem?

3. If the company does indeed produces these optimal numbers of tubs, what is the projected profit if all are sold?

4. If the company does indeed produces these optimal numbers of tubs, how many pumps will be left unused?

5. If the company does indeed produces these optimal numbers of tubs, how many labor hours will remain unused?

 

REAL COOL HOT TUBS

 

 

Aqua

 

Hydro

 

Number to Make Unit Profits

 

Total Profit

0

 

7 

8  Constraints

9  - Pumps Required

10 - Labor Requited

11  - Tubing Required

12 

13

14

15Excel Formulas:

16

17

18

19 

20 

21

22 

23

1

6

12

D6: B6*B5+C6*C5

D9: B9*B5+C9*C5

D10: B10*B5+C10*C5

D11: B11*B5+C11*C5

 

Solver Parameters

 

 

Also Click Options and check Assume Non-Negative Be sure to select the Answer Reports

Upload the spreadsheet AND your answers to the questions as well for credit.