Question

1. Magnolium Bakery needs to decide how many and what types of cupcakes to make for this week. they offer two flavors of cupcakes: blueberry lemon and red velvet. Blueberry lemon cupcakes sell for a profit of $4 per cupcake, while red velvet cupcakes sell for the same profit of $4 per cupcake. There are two main ingredients in making their cupcakes: flour and sugar. Each blueberry lemon cupcake requires 2 cups of flour and 4 cups of sugar. Each red velvet cupcake requires 4 cups of flour and 3 cups of sugar. Magnolium has a total of 100 cups of flour and 120 cups of sugar to use. Also, they would like to make at least 5 blueberry lemon cupcakes. Magnolium would like to determine how many of each type of cupcake to make. a. Formulate the problem as a linear program. b. Solve the linear program from part (a) above using Excel. Specifically, find the optimal number of each type of cupcake to make and the optimal profit of Magnolium. Please, submit a screenshot of your Excel spreadsheet including the solver parameters. Assume that fractions of cupcakes can be made.

Answer 1

1.

a.

Let

X = number of blueberry lemon

Y = number of red velvet

Formulation

Maximize 4X + 4Y

Subject to

2X + 4Y <=100

4X + 3Y >= 120

X >= 5

X, Y >= 0

This completes the formulation.

b.

The excel model is shown below

|: X ✓ D E F G H fx - B C Blueberry Red velvet X Y Number 100 5 Profit 6 Flour 7 Sugar 8 Minimum ☺ ☺ a 120 5 00 0°F

The spreadsheet formulas are shown below

A1 1 ✓ fx X A B Blueberry Red Velvet 0 Blueberry Red velvet 3 Number + 100 5 Profit 6 Flour 7 Sugar 8 Minimum =SUMPRODUCT($B$3:$C$3,B5:05) =SUMPRODUCT($B$3:$C$3,B6:06) =SUMPRODUCT($B$3:$C$3,B7:07) =SUMPRODUCT($B$3:$C$3,38:08) m Ý î û 120 O

The solver parameters are shown below

D5 X fc =SUMPRODUCT($B$3:$C$3,85:C5) Solver Parameters A Set Objective: SD55 B Blueberry Red velvet X Y To: Max Min value of: 0 By Changing Variable Cells: $B$3:5053 3 Number Subject to the Constraints: SD56:5D57 <= SF56:SF57 SDS8 >= $F$8 Add Change Delete 5 Profit 6 Flour 7 Sugar 8 Minimum Reset All Load/Save Make Unconstrained Variables Non-Negative Select a Solving Method: Simplex LP Options Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth. Help Solve Close

The result is shown below

D5 X f r =SUMPRODUCT($B$3:$C$3,05:05) A D E F G B C Blueberry Red velvet хү | 18 | 16 Solver Results nm Number Solver found a solution. All Constraints and optimality conditions are satisfied. Reg Keep Solver Solution 136 O Restore Original Values Return to Solver Parameters Dialog 100 <= 100 5 Profit 6 Flour 7 Sugar Minimum ок | Cancel 120 <= 120 18 >= 5 Solver found a solution. All Constraints and optimality con satisfied. When the GRG engine is used, Solver has found at least a solution. When Simplex LP is used, this means Solver has optimal solution. coora