The Pizza Man is a local shop that plans to make its sales this Saturday from it
ID: 3222013 • Letter: T
Question
The Pizza Man is a local shop that plans to make its sales this Saturday from its sidewalk tables during the town’s holiday parade. On this occasion, the shop’s owners know that customers will buy by the slice and any kind of pizza offered will sell completely. The Pizza Man offers plain, meat, vegetable, and supreme pizzas. Each variety has its own requirement of sauce, cheese, dough, and toppings (in ounces per pizza as shown in the table), and each has its own selling price (per pizza).
Plain
Meat
Vegetable
Supreme
Available
Dough
5
5
5
5
200
Sauce
3
3
3
3
90
Cheese
4
3
3
4
120
Meat
0
3
0
2
75
Vegetables
0
0
3
2
40
Price
$8
$10
$12
$15
Write the Linear Program formulation for the above optimization problem. Make your formulation clear by defining the variables, constraints, objective function etc. clearly.
What is the maximum sales revenue? What mix of pizzas should be made to achieve this sales revenue?
Which ingredients are economically scarce (i.e., they are limiting to profits)?
Plain
Meat
Vegetable
Supreme
Available
Dough
5
5
5
5
200
Sauce
3
3
3
3
90
Cheese
4
3
3
4
120
Meat
0
3
0
2
75
Vegetables
0
0
3
2
40
Price
$8
$10
$12
$15
Write the Linear Program formulation for the above optimization problem. Make your formulation clear by defining the variables, constraints, objective function etc. clearly.
Solve it using Excel solver. Please attach your clearly annotated Solver model and output (including Answer and Sensitivity Report)What is the maximum sales revenue? What mix of pizzas should be made to achieve this sales revenue?
Which ingredients are economically scarce (i.e., they are limiting to profits)?
Explanation / Answer
Lets say amounts of plain, meat , vegetable and supreme pizza is A, B, C and D units
Total profit
TP = 8A + 10 B + 12C + 15D should be maximized
Constraint to:
5A + 5B + 5C + 5D >= 200...(i)
3A + 3B + 3C + 3 D >= 90...(ii)
4A + 3B + 3C +4D >= 120....(iii)
3B + 2D>= 75..................(iv)
3C + 2D>= 40.................(v)
A, B, C, D>= 0
I have solved the problem with solver
Sensitivity Report
Maximum sales revenue = $ 400
What mix of pizzas should be made to achieve this sales revenue?
Plain
Meat
Vegetable
Supreme
Ingredients which is scarce are Sauce and vegetable.
Microsoft Excel 12.0 Answer Report Worksheet: [Book1]Sheet1 Report Created: 4/16/2017 3:04:51 PM Target Cell (Max) Cell Name Original Value Final Value $G$3 Contribution Total 0 400 Adjustable Cells Cell Name Original Value Final Value $C$2 Decision Variable Plain 0 0 $D$2 Decision Variable Meat 0 10 $E$2 Decision Variable Vegetable 0 0 $F$2 Decision Variable Supreme 0 20 Constraints Cell Name Cell Value Formula Status Slack $G$5 Dough Total 150 $G$5<=$H$5 Not Binding 50 $G$6 Saue Total 90 $G$6<=$H$6 Binding 0 $G$7 cheese Total 110 $G$7<=$H$7 Not Binding 10 $G$8 meat Total 70 $G$8<=$H$8 Not Binding 5 $G$9 vegetable Total 40 $G$9<=$H$9 Binding 0 $C$2 Decision Variable Plain 0 $C$2>=0 Binding 0 $D$2 Decision Variable Meat 10 $D$2>=0 Not Binding 10 $E$2 Decision Variable Vegetable 0 $E$2>=0 Binding 0 $F$2 Decision Variable Supreme 20 $F$2>=0 Not Binding 20Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.