Case Study #$3$: Pricing and Production at Blue Ridge Hot Tubs

Howie Jones, owner of Blue Ridge Hot Tubs, is facing a new problem. Although sales of the two hot tubs manufactured by his company, Agua$-$Spa and Hydro$-$Luxe, have been brisk, the company is not earning the level of profits that Howie wants to achieve. Having established a reputation for high quality and reliability, Howie believes he can increase profits by increasing the prices of the hot tubs. However, he is concerned that a price increase might have a detrimental effect on demand, so Howie has engaged a marketing research firm to estimate the level of demand for Aqua$-$Spa and Hydro$-$Luxe at various prices. To analyze possible relationships between the price and market demand, Howie Jones collected information on the monthly product prices and respective demands for each hot tub in the last $24$ months. See the data below.

Demand and Price for Aqua Spa Price and Demand for Hydro Luxe

Period Price Demand Period Price Demand

$1$ $$1,090\mathrm{.}001401$ $$1,300\mathrm{.}00144$

$2$ $$1,225\mathrm{.}00952$ $$1,310\mathrm{.}00139$

$3$ $$1,170\mathrm{.}001133$ $$1,230\mathrm{.}00152$

$4$ $$1,270\mathrm{.}00994$ $$1,210\mathrm{.}00157$

$5$ $$1,275\mathrm{.}001005$ $$1,270\mathrm{.}00148$

$6$ $$1,190\mathrm{.}001076$ $$1,319\mathrm{.}00139$

$7$ $$1,000\mathrm{.}001497$ $$1,179\mathrm{.}00154$

$8$ $$899\mathrm{.}001548$ $$1,169\mathrm{.}00168$

$9$ $$1,075\mathrm{.}001209$ $$1,175\mathrm{.}00162$

$10$ $$1,075\mathrm{.}0012410$ $$1,215\mathrm{.}00154$

$11$ $$989\mathrm{.}0014811$ $$1,195\mathrm{.}00158$

$12$ $$979\mathrm{.}0013812$ $$1,230\mathrm{.}00155$

$13$ $$1,105\mathrm{.}0013013$ $$1,245\mathrm{.}00147$

$14$ $$980\mathrm{.}0014914$ $$1,210\mathrm{.}00149$

$15$ $$879\mathrm{.}0015815$ $$1,219\mathrm{.}00149$

$16$ $$1,080\mathrm{.}0012316$ $$1,230\mathrm{.}00148$

$17$ $$1,179\mathrm{.}0011617$ $$1,230\mathrm{.}00146$

$18$ $$1,239\mathrm{.}0011018$ $$1,270\mathrm{.}00144$

$19$ $$1,120\mathrm{.}0012619$ $$1,219\mathrm{.}00158$

$20$ $$1,009\mathrm{.}0014220$ $$1,169\mathrm{.}00155$

$21$ $$989\mathrm{.}0015621$ $$1,219\mathrm{.}00158$

$22$ $$909\mathrm{.}0016122$ $$1,132\mathrm{.}00167$

$23$ $$1,000\mathrm{.}0013423$ $$1,129\mathrm{.}00171$

$24$ $$1,109\mathrm{.}0012424$ $$1,135\mathrm{.}00169$

Howie determined that the costs of manufacturing Aqua$-$Spa and Hydro$-$Luxe are $$240$ and $$300$ per unit, respectively. Ideally, he wants to produce enough hot tubs to meet demand exactly and carry no inventory of hot tubs. Each Aqua$-$Spa requires $1$ pump, $8$ feet of tubing materials, and $3$ hours of labor; each Hydro$-$Luxe requires $1$ pump, $10$ feet of tubing materials, and $4$ hours of labor. Howie$$s suppliers have committed to supplying him with $280$ pumps and $2,600$ feet of tubing. Also, $1,100$ hours of labor are available for production. Howie wants to determine how much to charge for each type of hot tub to maximize the total monthly profit.

Questions.

$1.$ Based on the historical information on prices and demand, identify, using regression analysis, a constant elasticity demand function for each hut tub. Present and briefly describe these functions.

$2.$ Using the demand functions from question $1,$ formulate an NLP model that identifies the optimal prices of Aqua$-$Spa and Hydro$-$Luxe hot tubs that would maximize the total profit for next month. $($Hint: the number of units produced for each spa should be equal to the respective demand$).$

$3.$ Develop a spreadsheet model in Excel and solve the model using Excel Solver. Present the optimal solution and explain it$.$ Comment on the integer solution for the number of each spa.

$4.$ In order to avoid high prices for each hot tub and thus be more competitive, Howie decided to limit the price for Aqua$-$Spa to no more than $$1,100.$ At the same time, he wants the price for Hydro$-$Luxe not to exceed $$1,320.$ Present these constraints mathematically and add them to the spreadsheet from question $3.$ Solve this updated model using Excel Solver. Present and explain the optimal solution and compare it with the solution in question $3.$

$5.$ For optimal results in question $4,$ use SolverTable to analyze how changes in the maximum number of labor hours $($take the range between $500$ and $1500$ hours with an increment of $20)$ affect the optimal price for each hot tub, the number of units produced, and the maximum total profit. Present and briefly explain your results.