Reference no: EM132233750

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 3 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 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 answer the 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 cost 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 4 advantage is no longer guaranteed?