The Pitts Barbecue Company makes three kinds of barbecue sauce: Extra hot, Hot, and Mild. Pitts's vice president of marketing estimates that the company can sell 8,000 cases of its Extra Hot sauce plus 10 extra cases for every dollar it spends promoting this sauce; 10,000 cases of Hot sauce plus 8 extra cases for every dollar spent promoting this sauce; and 12,000 cases of its Mild sauce plus 5 extra cases for every dollar spent promoting this sauce. Although each barbecue sauce sells for $10 per case, the cost of producing the different types of sauce varies. It costs the company $6 to produce a case of Extra Hot sauce, $5.50 to produce a case of Hot sauce, and $5.25 to produce a case of Mild sauce. The president of the company wants to make sure the company manufactures at least the minimum amounts of each sauce that the marketing vice president thinks the company can sell. A budget of $25,000 total has been approved for promoting these items of which at least $5,000 must be spent advertising each item. How many cases of each type of sauce should be made and how do you suggest that the company allocate the promotional budget if its wants to maximize profits?
1. Formulate an LP model for this problem
2. Create a spreadsheet model for this problem and solve it using Solver
3. What is the optimal solution?
Please include solver solution and mathematical equation