Winemaker Modelling and Application (~500 words)

A winemaker in Barossa Valley in South Australia must decide how much of two types of wine she will produce from a particular variety of grades. Each litre of table wine yields $16 profit, while each litre of dessert wine produces $10 profit. The labour (hour), bottling process time (hour), and grapes required (in kg) used for each litre of table wine is 0.4, 0.3, and 1.5, respectively. The labour (hour), bottling process time (hour), and grapes required (in kg) used for each litre of dessert wine is 0.6, 0.2, and 0.8, respectively. Resources available include 1000 labour hours, 800 hours of bottling process time, and 2000 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?

A market research reveals that the forecasted demand for both wines are 600 litres for table wine and 600 litres for dessert wine. Given this new information, how should the production plan be changed? How much profit can the winemaker expect to get?

The market research consultant then tells the winemaker that if she focus on producing only one type of wine, the market demand for that wine could be tripled (i.e. 1800 litres). If this is the case, which wine should the winemaker produce? How much profit can the winemaker expect?

Justify your numerical answers.

You need to include at least 1-2 references.

What is the seven-step modelling process?

Determine the objective function for the winemaker case.

Determine the decision variables for the winemaker case.

What are the constraints for the winemaker case?