Question

Santana is a manager at a local Tex-Mex restaurant where she is responsible for the day to day operation of the restaurant. She has been tasked with several planning related duties in anticipation of the upcoming holiday season and the office parties that come with it. In previous years, decisions regarding ordering and staffing have been done using guess work and at times resulted in costly mistakes. This year, Santana has turned to you, Telfer School of Management Business Analytics students, for help with planning decisions for the holiday season. She has outlined her three problems below along with specific questions that she is interested in. The Tex-Mex restaurant is known for their chicken fajitas, served on a sizzling skillet with grilled vegetables, rice and 3 tortillas. Santana estimates that at least 5000 servings of the chicken fajitas are needed for December. The chicken is purchased marinated from a restaurant supplier, but the vegetables, rice, and tortillas can either be prepared in-house or purchased ready to serve from an outside supplier. The prepared in-house cost and prepared by supplier costs are shown below.

Prepared In-house Cost		Prepared by Supplier Cost
Vegetables (per serving)	$3.80		$5.10
Rice (per serving)	$1.15		$1.50
Tortillas (per tortilla)	$0.65		$0.75

Two types of cooks are involved in making each of the vegetables, rice, and tortillas in-house. The time (in minutes per serving) required to cook each component with each type of cook is given in the table below along with the total labour hours available for that month.

Prep Cook		Line Cook
Vegetables (min/serving)	3.5		2.2
Rice (min/serving)	1.3		1.7
Tortillas (min/unit)	-		0.8
Capacity (hours)	350		420

The linear programming model for this make-or-buy application is given below;
Let VM be the number of vegetable servings prepared in-house
Let VP be the number of vegetable servings purchased ready to serve from the supplier
Let RM be the number of rice servings prepared in-house
Let RP be the number of rice servings purchased ready to serve from the supplier
Let TM be the number of tortilla servings prepared in-house
Let TP be the number of tortilla servings purchased ready to serve from the supplier

??? ???? ? = 3.8?? + 5.1?? + 1.15?? + 1.5?? + 0.65?? + 0.75??

Subject to:

3.5 60 ?? + 1.3 60 ?? ≤ 350 ????? ???? ???? ???? ?????????

2.2 60 ?? + 1.7 60 ?? + 0.8 60 ?? ≤ 420. ????? ???? ???? ???? ?????????

?? + ?? ≥ 5000                                                      ????????? ???????? ??????

?? + ?? ≥ 5000                                                     ???? ???????? ??????

?? + ?? ≥ 15000                                                 ????????? ??????

??, ??, ??, ??, ??, ?? ≥ 0

1. Input the linear programming model into Excel and solve it using Solver. Use the answer report and sensitivity report to answer the following questions. Pay close attention to how you input the model because an incorrect sensitivity report will not allow you to answerthe following questions. Include your Excel formulation, answer report and sensitivity report in the assignment. Treat each of the following questions independently.

2. How many of each component of the fajitas should be made in-house and how many purchased ready to serve from the supplier?

3. What is the total cost of the in-house making and supplier purchasing plan?

4. How many hours of cooking time is used for the prep cooks and how many hours for the line cooks?

5. How much should Santana be willing to pay for an additional 20 hours of line cook? With minimum wage being $14 per hour is it worthwhile for Santana to hire someone to work 20 hours as a line cook for the month of December?

6. Another supplier has offered to sell the ready to serve vegetables to the restaurant for a cos of $4.50 per serving. Would the restaurant improve its position by pursuing this opportunity?

7. Santana believes that some of the prep cooks can be promoted to line cooks. Would the restaurant benefit from doing this? If it is advantageous to do so and Santana decided to go ahead with it, how many minutes can be shifted from prep cooks to line cooks before the advantage is no longer guaranteed?

Answer 1

Santana is a manager at a local Tex-Mex restaurant where she is responsible for the day to day operation of the restaurant

Let VM = Number of vegetable servings prepared in-house.

Let RM = Number of rice servings prepared in-house.

Let TM = Number of tortilla servings prepared in-house.

Let VP = Number of vegetable servings purchased from the supplier.

Let RP = Number of rice servings purchased from the supplier.

Let TP = Number of tortilla servings purchased from the supplier.

The Decision Variables are VM, RM, ………….., whose costs have to be minimized.

The Objective Function is:

Minimize Z = 3.8*VM + 1.15*RM + 0.65*TM + 5.1*VP + 1.5*RP + 0.75*TP.

Constraints:

Time Requirement for Prep Cook:- (3.5/60)*VM + (1.3/60)*RM 350.

Time Requirement for Line Cook:- (2.2/60)*VM + (1.7/60)*RM + (0.8/60)*TM 420.

Vegetable Servings Requirement:- 1*VM + 1*VP 5,000.

Rice Servings Requirement:- 1*RM + 1*RP 5,000.

Tortillas Servings Requirement:- 1*TM + 1*TP 15,000.

VM, RM, ……………………., TP 0 (Non-negativity constraints)

2.

Components of fajitas to be made in-house:

Vegetable servings: 5,000.

Rice servings: 2,692.

Tortilla servings: 12,029.

Components of fajitas to be purchased from the supplier:

Vegetable servings: 0.

Rice servings: 2,308.

Tortilla servings: 2,971.

3.

Total cost for making and purchasing = $ 35,604.81

4.

Hours of cooking time used for prep cooks = 350 hours.

Hours of cooking time used for line cooks = 420 hours.

5.

If line cook time is increased by 20 hours, the total cost of in-house making and supplier purchasing reduces by $ 150 from $ 35,604.81 to $ 35,454.81.

Therefore, Santana will be willing to pay $ 150 for an additional 20 hours of line cook.

If the minimum wage is $ 14 per hour, then cost incurred to hire someone as a line cook to work 20 hours = $ 14 x 20 = $ 280.

But the above cost of $ 280 is greater than $ 150 cost saving. Hence, it will not be worthwhile for Santana to hire the person.

6.

The restaurant would not improve its position if it goes for another supplier providing vegetable serving at $ 4.50 per serving, as the total cost of in-house making and supplier purchasing would remain the same, i.e., $ 35,604.81. This is because there is no upper limit on allowable increase of VP (i.e., number of vegetable servings purchased from the supplier).

1. The Excel Solver Solution is given below: