MGMT20130 MGMT Operations Management and Business Anaytics

Task Description –Task B

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. Thelabour(hour), bottling process time (hour), and grapes required (in kg) used for eachlitreoftable wine is 0.4, 0.3, and 1.5, respectively.Thelabour(hour),bottling process time (hour), and grapes required (in kg) usedfor eachlitreofdessert wine is 0.6, 0.2, and 0.8, respectively.Resources available include 1000labourhours, 800 hours of bottling process time, and 2000 kg of grapes.

Task Description –Task B

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?

Prepare details for your report Task B: Winemaker Application for Data AnalyticsIn your business report, please insert and use one section of your business report (preferably before your conclusion section) to response the winemaker questions posed and justify your numerical answers. You need to include at least 1-2 references. The following is the short guide for you:-

•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?