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 profit

Constraints: 1X1 + 1X2 <= 200 pump constraint

9X1 + 6X2 <= 1566 labor constraint

12X1 + 16X2 <= 2880 tubing constraint

1X1>= 0simple lower bound

1X2>= 0 simple lower bound

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? ______
  6. If the company does indeed produces these optimal numbers of tubs, how many feet of available tubing will remain unused?

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.