2024 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 Assignment Help
GSB 622 Decision Support Systems LINEAR PROGRAMMING EXCEL SOLVER PART A LINEAR PROGRAMMING EXAMPLE MAXIMIZATION You have been 2023
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.