Reference no: EM132784052

Magnolium Bakery would like to expand their operations by introducing a new cupcake caramel cake. Caramel cake cupcakes sell for a profit of $5 per cupcake. In addition, the bakery now needs to keep track of a new ingredient for each of their cupcakes, butter. The number of cups of each ingredient needed to make each cupcake flavor are given in the chart below.

Ingredient Blueberry Lemon Red Velvet Caramel Cake

Flour 2 4 2
Sugar 4 3 4
Butter 0.2 0.3 0.1

Magnolium Bakery has 100 cups of our, 120 cups of sugar, and 5 cups of butter available to make their cupcakes. As before, they would like to make at least 5 blueberry lemon cupcakes. In order to maximize their profit, the bakery has entered the following LP into Excel.

maximize 4x1 + 4x2 + 5x3
subject to
2(x1) + 4(x2) + 2(x3) < 100
4(x1) + 3(x2) + 4(x3) < 120
0.2(x1) + 0.3(x2) + 0.1(x3) < 5
x1 > 5
x1; x2; x3 > 0

Solve the above LP using Excel and answer the following questions.

a. What is the optimal number of each cupcake flavor (fractions are allowed if necessary)? What is the maximum possible profit that Magnolium Bakery can earn? Please, print a copy of the Excel output (a screenshot of the spreadsheet, the answer report, and the sensitivity report).

b. Suppose that the profit of each blueberry lemon cupcake increases to $6. Will this change the optimal number of blueberry lemon cupcakes?

c. By how much would the profit of a red velvet cupcake have to decrease before the bakery decides to reduce the number of red velvet cupcakes to make? (Assume that the profit of each blueberry lemon cupcake remains $4.)

d. Suppose now that Magnolium Bakery is given an offer to buy one additional cup of butter at a price of $1. Should they accept this offer? Why or why not? If the answer is yes, then what will be the new optimal amounts of each cupcake flavor?