The Pat-A-Cake Pastry Shop makes chocolate cake in three sizes –Small, Medium, and Large. For each size, the number of cakes madeis 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 onhand – 400 ounces of cake flour, 550 ounces of caster sugar, and150 ounces of cocoa powder. The table below provides details on theamount of each ingredient required for each cake size as well asthe profit contributions. The shop wants to make the appropriateamount of each cake size in order to maximize profit. Cake SmallMedium 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
a. Develop a spreadsheet model and find the optimal solutionusing Excel Solver. What is the optimal total profit? Enter youranswer without a dollar sign.
b. Based on your answer for a., what quantity of large cakesshould be produced to maximize profit contribution? Remember thatthe number of cakes made should be an integer.
c. Based on your answer for a., what quantity of medium cakesshould be produced to maximize profit contribution? Remember thatthe number of cakes should be an integer.
d. Based on your answer for a., what quantity of small cakesshould be produced to maximize profit contribution? Remember thatthe number of cakes should be an integer.
