Reference no: EM134049131

Assignment:

A distributor imports olive oil from Spain and Italy in large casks. He then mixes these oils in different proportions to create three grades of olive oil that are sold domestically in the United States. The domestic grades include.

(a) commercial, whose combination of the olive oils must be no more than 35% Italian and the balance Spanish;(b) virgin, which may be any mix of the two olive oils; and (c) extra virgin, whose combination of the olive oils must be at least 55% Spanish and the balance Italian. The cost to the distributor for the Spanish olive oil is $6.50 per gallon. Italian olive oil cost the distributor $5.75 per gallon. The weekly demand for the three types of olive oils sold domestically is 700 gallons of commercial, 2,200 gallons of virgin, and 1400 gallons of extra virgin. Formulate and solve this problem in Excel (and run a sensitivity analysis) to determine how much of each type of imported olive oil (Spanish and Italian) is needed which will meet the requirements and be the most cost effective for the distributor.

a) What is the minimum cost in your optimal solution (the value of the objective function)?

b) Are there alternate optimal solutions for this problem?

c) Based on your solution what is the total amount of Spanish olive oil that should be purchased (total amount used in all three domestic blends)?

d) Based on your solution what is the total amount of Italian olive oil that should be purchased (total amount used in all three domestic blends)?

e) Use the sensitivity analysis to answer the following: Consider the situation where the weekly demand for commercial, virgin or extra virgin were increased by 100 gallons an increase in which one on these demands (assume only one demand is changed) would increase the cost by the least amount.}