Question

The Pat-A-Cake Pastry Shop makes chocolate cake in three sizes – Small, Medium, and Large. For each size, the number of cakes made is an integer (i.e. the shop does not bake only half of a cake). The shop has the following amounts of the three main ingredients on hand – 400 ounces of cake flour, 550 ounces of caster sugar, and 150 ounces of cocoa powder. The table below provides details on the amount of each ingredient required for each cake size as well as the profit contributions. The shop wants to make the appropriate amount of each cake size in order to maximize profit. Cake Small Medium Large Available Plain flour (Ounce) 8 16 21 400 Caster sugar (Ounce) 18 22 25 550 Cocoa powder (Ounce) 3 5 11 150 Profit/Unit $18 $25 $32

Develop a spreadsheet model and find the optimal solution using Excel Solver. What is the optimal total profit? Enter your answer without a dollar sign.

Question 2 Based on your answer to Question 1, what quantity of large cakes should be produced to maximize profit contribution? Remember that the number of cakes made should be an integer.

Question 3 Based on your answer to Question 1, what quantity of medium cakes should be produced to maximize profit contribution? Remember that the number of cakes should be an integer.

Question 4 Based on your answer to Question 1, what quantity of small cakes should be produced to maximize profit contribution? Remember that the number of cakes should be an integer.

Answer 1

The decision variables are as follows

Let, S, M, and L represents units Small, Medium, and Large cakes respectively

Objective function:

Objective is to maximize the profit:

Max Z = $18*S + $25*M + $32*L

Subject to:

Plain floor availability constraint: 8*S + 16*M + 21*L <= 400

Caster sugar availability constraint: 18*S + 22*M + 25*L <= 550

Cocoa Powder availability constraint: 3*S + 5*M + 11*L <= 400

Integer constraint for the variable, S, M, L should be integer variables

The excel model is developed as follows:

Copy-paste following table in excel as shown in the image.

Pat-A-Cake Pastry Shop - Product mix Problem
Chocolate Cakes	Small	Medium	Large
DV	S	M	L	Total Revenue
Revenue	18	25	32
Optimal Gallons	3	18	4	=SUMPRODUCT(B5:D5,B4:D4)
Subject To:				LHS		RHS
Cake Flour	8	16	21	=SUMPRODUCT($B$5:$D$5,B8:D8)	<=	400
Caster Sugar	18	22	25	=SUMPRODUCT($B$5:$D$5,B9:D9)	<=	550
Cocoa Powder	3	5	11	=SUMPRODUCT($B$5:$D$5,B10:D10)	<=	150

Optimal solution:

Small cake units = 3 units

Medium cake units = 18 units

Large cake units = 4 units

Optimal Profit = $632.00