(2 points) The honorary business fraternity at Tech is planning an all-day bake sale on Saturday...

   

Transcribed Image Text:

(2 points) The honorary business fraternity at Tech is planning an all-day bake sale on Saturday of a home football game as a charity fund-raising project. The members will bake cakes and cupcakes in one of the member's apartment oven. All the items bake at the same temperature (350 degrees) but for different times-45 minutes for a cake and 20 minutes for cupcakes-so they will bake one item at a time. The oven has enough space to bake one batch of cakes or one batch of cupcakes at a time. The recipe for a batch of cake calls for 8 eggs, 10 cups of butter, 12 cups of flour, and 6 cups of sugar. One batch of cupcakes requires 12 eggs, 6 cups of butter, 10 cups of flour, and 8 cups of sugar. A local grocery store has donated 96 eggs, 84 cups of butter, 200 cups of flour, and 120 cups of sugar. The members plan to sell one batch of cakes for $18 and one batch of cupcakes $15. The members want to know the number of batches of cakes and cupcakes to bake in order to maximize the money they make. We formulated the following linear programming model and solved it using excel solver. max 18x + 15x 8x + 12 x 96 (eggs) 10x + 6x2 84 (butter) 12x + 10 x < 200 (flour) 6x +8 x 120 (sugar) 45x + 20x 400 (baking time) X1, X 20 Variable Cells Final Cell Name Value $C$6 Cakes 6 $D$6 Cupcakes 4 Constraints Final Cell Name Value $F$8 Eggs $F$9 Butter $F$10 Flour $F$11 Sugar $F$12 Baking 96 84 112 68 350 Reduced Cost Shadow Price 0 0 0.58 1.33 0 0 0 Objective Allowable Allowable Coefficient Increase Decrease 18 15 Constraint R.H. Side 96 84 200 120 400 7 12 Allowable Allowable Increase Decrease 72 9.4 8 4.2 1E+30 1E+30 1E+30 28.8 36 88 52 50 Answer the following questions using excel solver sensitivity report. Show your work. a. (0.1 points) What is the sensitivity range for profit from cakes? b. (0.1 points) What are the optimal numbers of cakes and cupcakes to produce to maximize the money the team makes? c. (0.1 points) What is the sensitivity range for baking time? (2 points) The honorary business fraternity at Tech is planning an all-day bake sale on Saturday of a home football game as a charity fund-raising project. The members will bake cakes and cupcakes in one of the member's apartment oven. All the items bake at the same temperature (350 degrees) but for different times-45 minutes for a cake and 20 minutes for cupcakes-so they will bake one item at a time. The oven has enough space to bake one batch of cakes or one batch of cupcakes at a time. The recipe for a batch of cake calls for 8 eggs, 10 cups of butter, 12 cups of flour, and 6 cups of sugar. One batch of cupcakes requires 12 eggs, 6 cups of butter, 10 cups of flour, and 8 cups of sugar. A local grocery store has donated 96 eggs, 84 cups of butter, 200 cups of flour, and 120 cups of sugar. The members plan to sell one batch of cakes for $18 and one batch of cupcakes $15. The members want to know the number of batches of cakes and cupcakes to bake in order to maximize the money they make. We formulated the following linear programming model and solved it using excel solver. max 18x + 15x 8x + 12 x 96 (eggs) 10x + 6x2 84 (butter) 12x + 10 x < 200 (flour) 6x +8 x 120 (sugar) 45x + 20x 400 (baking time) X1, X 20 Variable Cells Final Cell Name Value $C$6 Cakes 6 $D$6 Cupcakes 4 Constraints Final Cell Name Value $F$8 Eggs $F$9 Butter $F$10 Flour $F$11 Sugar $F$12 Baking 96 84 112 68 350 Reduced Cost Shadow Price 0 0 0.58 1.33 0 0 0 Objective Allowable Allowable Coefficient Increase Decrease 18 15 Constraint R.H. Side 96 84 200 120 400 7 12 Allowable Allowable Increase Decrease 72 9.4 8 4.2 1E+30 1E+30 1E+30 28.8 36 88 52 50 Answer the following questions using excel solver sensitivity report. Show your work. a. (0.1 points) What is the sensitivity range for profit from cakes? b. (0.1 points) What are the optimal numbers of cakes and cupcakes to produce to maximize the money the team makes? c. (0.1 points) What is the sensitivity range for baking time?