Question

2) Pizza and Calzones, Revisited A pizza vendor sells pizzas and calzones at a sporting event. Since it is very well attended, the vendor is certain all of their pizzas and calzones will sell out. Pizzas can be sold for a profit of $3.50 and use 500 grams of dough, 300 grams of sauce, and 150 grams of cheese. Calzones can be sold for a profit of $2.50 and use 750 grams of dough, 200 grams of sauce, and 250 grams of cheese. If the vendor has 112,500 grams of dough, 60,000 grams of sauce, and 36,000 grams of cheese, how many pizzas and calzones should they make?

(a) How many pizzas and calzones should they make? What is the profit they would make from this? Use Solver to find the answer. (3 points)

(b) If you were to have a sale on pizza of $1.50 off, would the optimal number of pizza and calzones you should make change? Why or why not? (2 points)

(c) Your manager is worried about wasting ingredients and tasks you with coming up with new amounts of dough, sauce, and cheese to order for the next day assuming everything remains the same. If you want to keep the same optimal solution, what ingredient(s) can you order less of, and how much less can you afford to order? (2 points) (d) What is the most you could increase the price (which would increase the profit by the same amount) of pizza by without changing the optimal solution? What about the price of calzones? (2 points)

(e) Your manager suggest making a new item: Cheesy breadsticks. This item can generate $1 of profit and uses 200 grams of dough and 200 grams of cheese, along with 50 grams of sauce for the dipping sauce. Would it be worth it to produce cheesy breadsticks as well? (2 points)

(f) Another option is to instead make regular breadsticks. This would take 200 grams of dough and 50 grams of sauce, but only generate $0.50 of profit. Would it be worth it to produce breadsticks? (2 points)

Answer 1

a)

Let,
x1 = number of pizzas to be produced
x2 = number of calzones to be produced

Objective is to maximize profit = 3.5x1+2.5x2

Subject to,
500x1+750x2 <= 112500 (Dough constraint)

300x1+200x2 <= 60000 (Sauce constraint)

150x1+250x2 <= 36000 (Cheese constraint)

x1,x2,x3 >= 0 (Non-negativity constraint)

Solving in excel solver we get,

x1 = number of pizzas to be produced = 180
x2 = number of calzones to be produced = 30

and maximized profit = 705

Solver screenshot

x1 X2 180 30 Objective function 705 4 Constraints 6 Dough 7 Sauce 8 Cheese 500 300 150 750 112500 20060000 25034500 112500 60000 36000

Sensitivity report

A B 1 Microsoft Excel 16.0 Sensitivity Report 2 Worksheet: [Travel Sheet.xlsx]Sheet53 3 Report Created: 4/19/2019 11:02:11 PM 6 Variable Cells Final Reduced oObjective Allowable Allowable Decrease Cell Name Value Cost Coefficient Increase 9 B$2 x1 180 30 3.5 2.5 0.25 1.833333333 2.75 0.166666667 10 $C$2 x2 12 Constraints 13 14 Cell Name Value Price R.H. Side Increase 15 SD$6 Dough 112500 0.0004 16 SD$7 Sauce 60000 0.011 17 $D$8 Cheese 34500 18 Final Shadow Constraint Allowable Allowable Decrease 12500 15000 1500 112500 4166.666667 7500 1E+30 60000 36000

b)

No, Optimal number of pizzas and calzones would not change as reduction of $1.5 is within allowable decrease of 1.833 up to which optimal solution will remain intact
c)

We can see, only cheese is not utilized totally and is used 34500 grams from available 36000 grams

So Ordering 34500 grams of Cheese will keep optimal solution intact

d)

Price of pizza can be increased up to (3.5+0.25) = 3.75 as allowable increase is 0.25 to keep present solution intact

Price of calzones can be increased up to (2.5+2.75) = 5.25 as allowable increase is 2.75 to keep present optimal solution intact