PROBLEM 1 (50 pts):

Sheet: Blue Ridge

Problem Statement:

Alyssa Wise owns and operates Blue Ridge Hot Tubs, a company that sells two models of hot tubs: the Aqua-Spa and the Hydro-Lux. Alyssa purchases prefabricated fiberglass hot tub shells and installs common water pumps and the appropriate amount of tubing into each hot tub.

• Every Aqua-Spa requires a pump, 12 hours of labor and 16 feet of tubing.
• Every Hydro-Lux requires 2 pumps, 16 hours of labor and 8 feet of tubing.
• Demand for these products is such that each Aqua-Spa produced can be sold to generate a profit of $275, and each Hydro-Lux produced can be sold to generate a profit of $250.
• The company expects to have 450 pumps, 91 workers, and 3,080 feet of tubing available during the next weeks production cycle.
• All employees work their 40 hours with no overtime.

Using the Blue Ridge Sheet and Excel Solver, determine the optimal number of Aqua-Spas and Hydro-Luxes to produce to maximize profit.

Problem 2 (50 pts):

Sheet: Chips

Problem Statement:

Kaitlin Bates owns a company that produces medical-grade knee braces. She has three manufacturing plants (in Atlanta, Tulsa, and Springfield) that produce the braces which are then shipped to one of four distribution centers A, B, C, or D.

• The three plants can produce 15, 22, and 15 truckloads of product each week, respectively.
• Distribution centers A & B need 12 truckloads of product each week and Distribution centers C & D need 13 truckloads of product each week.
• The shipping costs per truckload between the plants and distribution centers are given in the table.

She needs to determine how much to ship from each plant to each distribution center, and would like to minimize total shipping costs.

	Distribution Center
Plant	A	B	C	D
Atlanta	$ 800	$1300	$400	$ 700
Tulsa	$1100	$1400	$600	$1000
Springfield	$ 600	$1200	$800	$ 900

Using the Chips Sheet and Excel Solver, determine the optimal number bathtubs that need to be sent between plants and distribution centers minimize cost.

AutoSave Off HI HW 5 Starting File(1) - Excel Search Joseph Crawford JC File Home Insert Draw Page Layout Formulas Data Review View Developer Help Share Comments Trebuchet MS 22 ~ A == ab Wrap Text General WE AY O Paste BIU Insert Delete Format Ideas A 3 Merge & Center $ % -28 Conditional Format as Cell Formatting Table Styles Number Styles Sort & Find & Filter Select Clipboard Font Alignment Cells Editing Ideas A1 X fi Blue Ridge Hot Tubs B C D E F G H J - L N M 0 P 0 1 Blue Ridge Hot Tubs Aqua-Spa Hydro-Lux Total Profit 2 3 Decision Variables 4 5 Quantity 6 Profit/Tub| 7 8 9 10 Constraints # Used Per Aqua-Spa # Used Per Hydro-Lux Total Used Available 11 12 13 14 15 16 17 Pumps Working Hours Feet of Tubing