Question 3 A winemaker in Barrossa Valley must decide how much of two types of wine she will produce from a particular variety of grapes. Each litre of table wine yields $8 profit, while each litre of dessert wine produces $5 profit. The labour (hour), bottling process time (hour), and grapes required (kg) used for each litre of table wine is 0.5, 0.2, and 1.2, respectively. The labour (hour), bottling process time (hour), and grapes required (kg) used for each litre of dessert wine is 0.6, 0.2, and 1.0, respectively. Resources available include 1000 labour hours, 800 hours of bottling process time, and 1800 kg of grapes.

Model the problem in Excel spreadsheet and solve it using Solver. What is the optimal production plan? How much profit would the winemaker be able to make?

Generate the sensitivity analysis report for the problem. Now, the winemaker has the opportunity to purchase additional labour and bottling hours at $2/hour, and the opportunity to purchase more grapes at $2/kg. If the winemaker has $1000 to spend, which resource should the winemaker purchase? Why? Please provide your answer based on the model that you developed