Reference no: EM132227323
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 restaurant plans to add a hand-shaken blackberry margarita containing Hornitos Plata tequila, triple sec, citrus juice, and blackberries for the month of December. To meet taste specifications, the blackberry margarita must consist of at least 40% Hornitos Plata tequila, at least 20% and no more than 30% triple sec, and exactly 30% citrus juice. The restaurant purchases the tequila and triple sec from a local liquor distributor and citrus juice from a processing plant in Southern Ontario. For the anticipated production period, 1000 L of Hornitos Plata tequila and 800 L of triple sec can be purchased; an unlimited amount of citrus juice can be ordered.
The restaurant recently bought a plentiful supply of blackberries for $17.00 per kg and Santana believes they will not run out of blackberries for the month of December; any unused blackberries will be tossed out. The costs for the liquor are $43.00 per L for the Tequila and $32.00 per L for the triple sec; the citrus juices can be purchased for $2.50 per L. Santana believes that the restaurant can sell all of the Blackberry margarita they produce at $28.20 a liter. Hence, she has the following questions for you.
1. What is the optimal blend of the three ingredients that Santana should ask her bartenders to mix in order to maximize the total profit contribution? Formulate the linear programming model and solve it to determine the number of liters of each ingredient that Santana should order and the total profit contribution that will be realized from selling this blackberry margarita at the restaurant. Include your written formulation, the Excel layout, and both Excel reports. The formulation is critical, and you must be confident in your formulation before moving on to answer the subsequent questions. No part marks are awarded for the subsequent questions if the formulation is incorrect.
2. If Santana can obtain additional amounts of the tequila from the local distributor, should she do so? If so, how much would she be willing to pay for each additional liter and for how many liters is the price valid?
3. Santana is concerned about the supply of triple sec. Should she secure a contract with another distributor for additional triple sec? If so, how much would she be willing to pay for each additional liter and how many liters will she want to purchase?
4. Santana is worried that the price of triple sec can increase to $35.00 per liter by next week, if that is the case, will she be required to alter the recipe?