Question

Samantha sells chopped beef brisket sandwiches at a barbeque stand inside SWOSU’s Milam stadium during home games and she has decided also to sell the sandwich that resulted from the decision analysis she performed earlier (see Appendix C in Module 2). Each chopped beef brisket sandwich contains 1 bun, 1 ounce of barbeque sauce, and 6 ounces of beef brisket. The specialty sandwich contains 1 bun, 1 ounce of chili, 1 ounce of Fritos, and 4 ounces of hamburger meat. There are 6,746 buns available, 4,746 ounces of barbeque sauce available, 28,480 ounces of chopped beef brisket available, and 8,000 ounces of hamburger meat, 2000 ounces each of chili and Fritos available. Each chopped beef brisket sandwich has a profit of $2.09 and the other sandwich has a profit of $1.50. Samantha wants to know if she were to maximize her profit what the profit might be and the number of chopped beef brisket sandwiches and the specialty sandwiches she will need to sell to maximize her profit?

a. Formulate a linear programming model for this problem using Excel’s Solver.

b. Write an analysis of your calculations and discuss what Samantha should do based on your calculations.

Solve using excel solver and please show the screen shot of the equation, constraints and results. Please write the b part by typing from the computer.. Please no hand written work.

Will rate the answer for sure. Thanks

Answer 1

(a)

Excel model:

Solver inputs:

Solution (Answer report)

Objective Cell (Max)
	Cell	Name	Original Value	Final Value
	$E$4	Profit	$0.00	$12,919.14
Variable Cells
	Cell	Name	Original Value	Final Value	Integer
	$C$3	No. of Chopped beef brisket sandwich	0	4,746	Contin
	$D$3	No. of Speciality sandwich	0	2,000	Contin
Constraints
	Cell	Name	Cell Value	Formula	Status	Slack
	$E$6	Buns consumed	6,746	$E$6<=$G$6	Binding	0
	$E$7	Barbeque sauce (oz) consumed	4,746	$E$7<=$G$7	Binding	0
	$E$8	Chopped beef brisket consumed	28,476	$E$8<=$G$8	Not Binding	4
	$E$9	Hamburger meat (oz) consumed	8,000	$E$9<=$G$9	Binding	0
	$E$10	Chili (oz) consumed	2,000	$E$10<=$G$10	Binding	0
	$E$11	Fritos (oz) consumed	2,000	$E$11<=$G$11	Binding	0

(b)

Samantha should 4,746 units of Chopped beef brisket sandwich and 2,000 units of Speciality sandwich in order to earn the maximum possible profit consider all the resource availabilities. The maximum profit that could be earned in this way is $12,919.14. The Chopped beef brisket resource is the only non-binding resource meaning, not all the Chopped beef brisket will be consumed in the production. Rest all the resources will be fully consumed (i.e. up to their maximum availability limits) and hence they are binding resources.