A winemaker in California's Napa Valley must decide how much of two types of wine he will produce from a particular variety of grapes. Each liter of table wine yields $2 profit, while each liter of dessert wine produces $5 profit. The labor hours, bottling process time and grapes in pounds/bottle used for type of wine are given in the table below. Resources available include 2000 labor hours and 480 hours of bottling process time and 9,000 lbs of grapes. Assume the winemaker has 4,000 bottles available to fill and can sell all of the wine he produces.
Table Dessert
Labor (hours) 0.30 0.60
Process (hours) 0.10 0.15
Grapes (lbs) 2.50 3.00
A. Use Excel Solver to determine the optimal production plan for the winemaker who wishes to maximize profit. Obtain an Answer Report and a Sensitivity Report. What is the production plan? What is their profit?
B. Which constraint(s) are binding on the optimal solution? Explain your answer using the Reports from Part a.
C. How much should the winemaker be willing to pay for an additional labor hour? Please explain.
D. Suppose the winemaker can obtain 100 additional labor hours. Does the Sensitivity Report obtained for Part a provide the information necessary to determine his optimal expected profit with the additional labor hours? If not, why not? If so, would his bottling plan change? How? Explain your answers using the Reports from Part a.