Question

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
Herb1
Turmeric
5
3
1500
$3.50
Paprika
2
3
Unlimited
2.50
Availability
(ounces)
10000
8500
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?

A spice producer has a limited supply of two herbal ingredients that are used in the production of seasonings. It uses the two ingredients, Herbl 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 Herbl and $0.15 per ounce of Herb2. Additional data are shown in the following table 1. SEASONING INGREDIENTS (oz/bottle) DEMAND (bottles) SALES PRICE PER BOTTLE Herbl Herbl Turmeric Paprika Availability 1500 $3.50 Unlimited 2.50 10000 8500 ounces) 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?

Answer 1

(a)

Answer Report

(b)

Recommendations

Only Paprika bottles of 2833.33 units are to be produced. No Termeric bottles are to be produced. The total maximum revenue at the optimality will be $10,333.33.

One bottle of Turmeric uses 5 oz of herb 1 and 3 oz of herb 2. So, the potential revenue by selling this in raw herbs form is 5*0.75 + 3*0.15 = $4.2 while the revenue from a bottle is only $3.5. So, it is obvious that instead of producing turmeric, it is better to sell these herbs in raw form. That is why there are unsold herbs and zero turmeric production at the optimality.

(c)

Since the Slack values are zero, there are no unused ingredients here.

Total revenue = $8,527.78

(d)

Sensitivity report

Since the total % increase, 68.2% is less than 100%, the optimality condition will not change although the max revenue will change to:

1444.44 x (3.5+0.5) + 1388.89 x (2.5+0.5) = $9,944.44

(e)

Additional constriant: