Question

Complete the questions below. Submit only an Excel spreadsheet with your name, one tab for each question, and your Excel Solver solution; ensure that you clearly identify each answer.

A local pastry shop is planning their next morning’s production. The shop has three main items that they sell: cakes, cupcakes, and cannolis. All of these are made in-house with the shop’s own ingredients. The recipes for these items are as follows:

Cakes: 5 eggs, 2 cups flower, 15 g of sugar, 1 cup of milk

Cupcakes: 2 egg, 0.5 cups of flower, 10 g of sugar, 0.25 cup of milk

Cannoli: 4 eggs, no flower, 25 g of sugar, 0.25 cup of milk

The pastry shop sells cakes for $8, cupcakes for $3, and cannolis for $6. At the moment, there are 150 eggs, 50 cups of flower, 5 kg of sugar, and 4 gallons of milk available.

1. How much of each item should the pastry shop make in order to maximize their potential profit? What is their maximum potential profit?

2. Suppose the shop had to make at least 5 of each item in order to keep a sufficient variety ready? What would be the new optimal combination? What is their maximum
potential profit now?

3. Now suppose that tomorrow they are receiving an order of 100 eggs, 35 cups of flour, 3 kg of sugar, and 2 gallons of milk. What production plan maximizes your potential profit for the next two days? What is this maximum potential profit? Remember that they must still make at least 5 of each item every day.

Answer 1

Please note that there is now way we can give you an excel sheet. You will have to prepare your own sheet. However, I have laid down step by step procedure so that you can prepare your own excel at your end. I have also provided the snapshots of the answers so that you can check.

Please note that requirement of sugar is in gms while opening inventory of sugar is in kg. Hence, we will use 5 kg of sugar = 5000 gms. 1 gallon of milk = 16 cups of milk. Hence 4 gallons = 64 cups.

1. How much of each item should the pastry shop make in order to maximize their potential profit? What is their maximum potential profit?

Set up your excel as shown below. Adjacent cells contain the excel formula i have used in the blank cells currently displaying 0.

The solution will look like this. Yellow colored cells contain the answers.

Hence, your final answers are:

the pastry shop make 24 cakes, 3 cupcakes and 6 cannoli in order to maximize their potential profit. Their maximum potential profit = $ 237.

2. Suppose the shop had to make at least 5 of each item in order to keep a sufficient variety ready? What would be the new optimal combination? What is their maximum potential profit now?

We will have to add three more constraints. Please see this below:

And the solution now looks like this:

the new optimal combination:

Cake - 23, Cupcake - 5, Cannoli - 6.

Their maximum potential profit now = $ 235

3. Now suppose that tomorrow they are receiving an order of 100 eggs, 35 cups of flour, 3 kg of sugar, and 2 gallons of milk. What production plan maximizes your potential profit for the next two days? What is this maximum potential profit? Remember that they must still make at least 5 of each item every day.

For the next day, everything else will remain same. Our opening inventory will then become what we see as slack in the solution (b) above + new orders they are receiving. The slack tells us how much of each item will remain unutilized after day 1.

Opening inventory for egg = slack of 1 + fresh order of 100 = 101; Flower = 1.5 + 35 = 36.5; Sugar = 4455 + 3000 = 7455, Milk = 38.25 + 2 x 16 = 70.25

And the solution is:

Production plan day 1: Cake - 23, Cupcake - 5, Cannoli - 6. (same as b above)

Day 2: Cake - 14, Cupcake - 5, Cannoli - 5.

Profit potential = 235 + 157 = $ 392