Assignment:
Linear Programming
A spice producer has a limited supply of two herbal ingredients that are used in the production of seasonings. It uses the two ingredients, Herb1 and Herb2, to produce either turmeric or paprika. The marketing department reports that although the firm can sell all of the paprika it can produce, it can only sell up to a maximum of 1500 bottles of turmeric. Unused herbal ingredients can be sold at $0.75 per ounce of Herb1 and $0.15 per ounce of Herb2. Additional data are shown in the following table.
SEASONING INGREDIENTS (oz/bottle) DEMAND (bottles) SALES PRICE PER BOTTLE
Herb1 Herb2
Turmeric 5 3 1500 $3.50
Paprika 2 3 Unlimited 2.50
Availability (ounces) 10,000 8,500
a. Organize the information on the spreadsheet as the examples given in the class. Formulate the problem as a spreadsheet model and find the optimal solution for the problem.
b. Make recommendation to the company. How many bottles of each seasoning should be produced? What is the total revenue? Analyze the sources of the revenue and explain why there are many unused ingredients?
c. If the unused ingredients cannot be sold for revenue, formulate and solve the problem. What is the optimal solution? Are there any ingredients not used and how many ounces?
d. From question c, the price of each product will increase by $0.50 per bottle. Would the optimal production plan change? Explain why. What would the new total revenue be? Show computation.
e. From question c, suppose only up to 1000 bottles of turmeric can be sold, would the optimal solution change? What is the new optimal solution?