Solve a, b, c, and d

It's been a year since Cellar Door opened and in spite of the global economic conditions, Cellar Door at Orange Wines has somehow defied the odds and has quickly become a must-visit experience for wine lovers from near and far. Now you've had a moment to catch your breath and take in the overnight success of Cellar Door, you want to start planning to secure and grow Cellar Door's future as part of the winery landscape in South Australia. The first thing you want to do is evaluate the performance of the four major wine types Orange Wines produces and sells, Premium, Reserve, Varietal and Bulk. With the aim to maximise the daily profit from sales of these products, it might be worth considering whether at least one of these wines should be dropped from production. Then there's the Epicurean Way. This is the ultimate food and wine 4-day tourist route in South Australia starting in McLaren Vale before passing through the Adelaide Hills, Barossa Valley and finishing in Clare, with visitors to the Epicurean Way staying at local accommodation each evening. Blogs around the world are dedicated to discussing the great experiences they had following the Epicurean Way. You're wondering whether it is worth getting Cellar Door as a recommended destination on the trail. The problem is competition is fierce with wineries in the Barossa Valley, which is unarguably the most popular wine destination for tourists. You think that if tourist numbers into the Adelaide Hills are comparable to the Barossa Valley then you should spend money on promoting Orange Wines on the Epicurean Way website, otherwise you'll consider alternative promotion avenues. Finally, at Cellar Door you have branched out by selling wine and non-wine items such as food and dining, through to classes on pairing wine with chocolate as well as gift store products such as bespoke wine glasses and locally made products such as olive oil, vinegar and organic almonds. In particular, you are interested to know if purchasing wine at Cellar Door would lead to increased spending on these non-wine items or whether Cellar Door should focus solely on the sales of wine items only. It's a good thing you have a head for numbers and lots of data to play with for the next board report which is due very soon. Your brother who does not have a head for numbers has asked if you could figure this out. Like that's a big surprise - yes he's your older brother but he's such a maths-phoebe he won't even say "Excel" because it has the letter "x" in it, so there's no chance he'll be able to help you with "that numbers software" as he calls it. However he might have a word or two to say about your graphs since he's a graphics designer. You don't need him anyway - you wrote a flawless board report for the mysterious Mr X and there were all ticks and no crosses, so you've totally got this. Maximising Profit from Wine Sales Orange Wines makes and sells wine to suit a range of tastes from the less expensive bulk wine, for younger demographics, through to Premium wines which form part of the higher-end market that has become popular in recent years. Your family would like to maximise the profit for Orange Wines based on the different types of wine sold. Your brother, the one who likes pretty pictures but not Excel, said "You know how to use that numbers software you figure it out". You have recorded all the relevant information you need to construct a Linear Programming model which you won't use to scare anyone, perhaps just your brother when he annoys you or you want to win an argument with him. Objective: Orange Wines would like to maximise the daily profit made from selling bottles of wine. Decision Variables: Orange Wines makes the following profit per wine type: $100 for each bottle of Premium wine, $62.60 per bottle of Reserve wine, $36.50 per bottle of Varietal wine and $18.50 for each bottle of Bulk wine. Constraints: Orange Wines has a number of constraints to meet when making and selling wine: Grade A Grapes: Orange Wines has a total of 96,000 clusters of Grade A grapes available per day. Each Premium bottle of wine uses 375 clusters of Grade A grapes, a Reserve bottle of wine uses 425 clusters of Grade A grapes, a Varietal bottle of wine uses 325 clusters of Grade A grapes while a bottle of Bulk wine uses only 100 clusters of Grade A grapes. Grade B Grapes: On the other hand, Orange Wines use Grade B grapes only for their Varietal and Bulk blends. Each Varietal bottle of wine needs 150 clusters and a Bulk bottle of wine needs 400 clusters of Grade B grapes. There are a total of 96,000 clusters of Grade B grapes available each day. Sugar: All wines need sugar! Orange Wines has 80kg of sugar available daily. Premium and Reserve wines each use 100 grams of sugar while Varietal uses 200 grams and Bulk uses 400 grams of sugar. Labour: Given the size of the workforce at Orange Wines, they have 225 hours of labour available each day. A Premium bottle of wine and a Reserve bottle each take 12 minutes to produce, while a Varietal bottle of wine takes 24 minutes. Finally, a Bulk blend bottle takes 36 minutes. Production: It will cost $1.10 to produce each bottle of Premium wine, $1.15 to produce each bottle of Reserve wine while Varietal and Bulk wines will cost 93 cents and 52 cents, respectively. Orange Wines has cost production at $500 per day. Sales: Finally, the number of Varietal and Bulk bottles sold should be at least twice the number of Premium and Reserve bottles sold combined. (a) Before you begin, the model should look like the picture attached below. Formulate a linear programming model for this problem, filling in the template below. Type up the full mathematical model in Word and include it here, fill in the template provided below, clearly indicating: The decision variables. Define them precisely. The objective. Using your decision variables, formulate the objective function. The constraints. Using your decision variables, formulate these constraints. Decision Variables list and define the decision variables here Objective and Objective Function state the objective (min or max) and include the objective function here Constraints enter 1 constraint per line including a name for each constraint that contains your initials (b) The Linear Programming template is in Data.xlsx in the worksheet Appendix 1(b) - use this to enter your LP model because it is setted up. Your constraint names should begin with your initials, e.g. BC Budget if your initials are BC. Use EXCEL Solver to obtain a solution to the linear programming model from part (a), together with an Answer Report and a Sensitivity Report. Round the objective function answer to 2 decimal places. (c) You are considering increasing the use of sugar by an extra 10kg each day. Using the output for the problem solved in part (b), rounded to 2 decimal places, determine the impact on daily operations by: i. Stating and interpreting the range of feasibility for Sugar; ii. Stating and interpreting the shadow price for Sugar; and iii. Calculating the new maximum daily profit. By referring to the shadow price, would it be worthwhile increasing the amount of Daily Labour instead of Sugar? Explain briefly. (d) Finally, you want to visualise the results to present at the next board meeting to communicate the number of daily sales by wine type and somehow include the wine prices too. You decide to use a bubble plot because you like the idea of bubbles - might have something to do with sparkling wine Complete Table 1 and use this table to produce a bubble plot. Number of Wine Type Bottles Sold Price per Bottle Each Day Premium From (b) round Enter the price of each Reserve your answer for wine type from your Varietal each wine type to objective function in Bulk the nearest whole (a). You can keep the number prices to 2 decimal places Table 1: Bottles sold daily by wine type | B C D E | Orange Wines Variables Parameters 10 11 12 Objective function (max profit) Grade A Grapes Grade B Grapes Sugar Labour Production Costs Sales 13 14 Objective function Maximise Profit 15 16 17 18 19 20 Constraints LHS RHS Grade A Grapes Grade B Grapes 21 Sugar 22 Labour 25 Production Costs Sales Non-neg. P Non-neg. R 26 v XXL Non-neg. V 28 Non-neg. B D E F G H I J C Price Wine Type Number of Bottles Sold each Day Premium Reserve Fill in the table with your results from (a) (Price) andthe output from Solver in (b) (Number of Bottles Sold each Day) Varietal Bulk For the Number of Bottles Sold each Day you can round the values to the nearest whole number Price can be left to 2 decimal places. It's been a year since Cellar Door opened and in spite of the global economic conditions, Cellar Door at Orange Wines has somehow defied the odds and has quickly become a must-visit experience for wine lovers from near and far. Now you've had a moment to catch your breath and take in the overnight success of Cellar Door, you want to start planning to secure and grow Cellar Door's future as part of the winery landscape in South Australia. The first thing you want to do is evaluate the performance of the four major wine types Orange Wines produces and sells, Premium, Reserve, Varietal and Bulk. With the aim to maximise the daily profit from sales of these products, it might be worth considering whether at least one of these wines should be dropped from production. Then there's the Epicurean Way. This is the ultimate food and wine 4-day tourist route in South Australia starting in McLaren Vale before passing through the Adelaide Hills, Barossa Valley and finishing in Clare, with visitors to the Epicurean Way staying at local accommodation each evening. Blogs around the world are dedicated to discussing the great experiences they had following the Epicurean Way. You're wondering whether it is worth getting Cellar Door as a recommended destination on the trail. The problem is competition is fierce with wineries in the Barossa Valley, which is unarguably the most popular wine destination for tourists. You think that if tourist numbers into the Adelaide Hills are comparable to the Barossa Valley then you should spend money on promoting Orange Wines on the Epicurean Way website, otherwise you'll consider alternative promotion avenues. Finally, at Cellar Door you have branched out by selling wine and non-wine items such as food and dining, through to classes on pairing wine with chocolate as well as gift store products such as bespoke wine glasses and locally made products such as olive oil, vinegar and organic almonds. In particular, you are interested to know if purchasing wine at Cellar Door would lead to increased spending on these non-wine items or whether Cellar Door should focus solely on the sales of wine items only. It's a good thing you have a head for numbers and lots of data to play with for the next board report which is due very soon. Your brother who does not have a head for numbers has asked if you could figure this out. Like that's a big surprise - yes he's your older brother but he's such a maths-phoebe he won't even say "Excel" because it has the letter "x" in it, so there's no chance he'll be able to help you with "that numbers software" as he calls it. However he might have a word or two to say about your graphs since he's a graphics designer. You don't need him anyway - you wrote a flawless board report for the mysterious Mr X and there were all ticks and no crosses, so you've totally got this. Maximising Profit from Wine Sales Orange Wines makes and sells wine to suit a range of tastes from the less expensive bulk wine, for younger demographics, through to Premium wines which form part of the higher-end market that has become popular in recent years. Your family would like to maximise the profit for Orange Wines based on the different types of wine sold. Your brother, the one who likes pretty pictures but not Excel, said "You know how to use that numbers software you figure it out". You have recorded all the relevant information you need to construct a Linear Programming model which you won't use to scare anyone, perhaps just your brother when he annoys you or you want to win an argument with him. Objective: Orange Wines would like to maximise the daily profit made from selling bottles of wine. Decision Variables: Orange Wines makes the following profit per wine type: $100 for each bottle of Premium wine, $62.60 per bottle of Reserve wine, $36.50 per bottle of Varietal wine and $18.50 for each bottle of Bulk wine. Constraints: Orange Wines has a number of constraints to meet when making and selling wine: Grade A Grapes: Orange Wines has a total of 96,000 clusters of Grade A grapes available per day. Each Premium bottle of wine uses 375 clusters of Grade A grapes, a Reserve bottle of wine uses 425 clusters of Grade A grapes, a Varietal bottle of wine uses 325 clusters of Grade A grapes while a bottle of Bulk wine uses only 100 clusters of Grade A grapes. Grade B Grapes: On the other hand, Orange Wines use Grade B grapes only for their Varietal and Bulk blends. Each Varietal bottle of wine needs 150 clusters and a Bulk bottle of wine needs 400 clusters of Grade B grapes. There are a total of 96,000 clusters of Grade B grapes available each day. Sugar: All wines need sugar! Orange Wines has 80kg of sugar available daily. Premium and Reserve wines each use 100 grams of sugar while Varietal uses 200 grams and Bulk uses 400 grams of sugar. Labour: Given the size of the workforce at Orange Wines, they have 225 hours of labour available each day. A Premium bottle of wine and a Reserve bottle each take 12 minutes to produce, while a Varietal bottle of wine takes 24 minutes. Finally, a Bulk blend bottle takes 36 minutes. Production: It will cost $1.10 to produce each bottle of Premium wine, $1.15 to produce each bottle of Reserve wine while Varietal and Bulk wines will cost 93 cents and 52 cents, respectively. Orange Wines has cost production at $500 per day. Sales: Finally, the number of Varietal and Bulk bottles sold should be at least twice the number of Premium and Reserve bottles sold combined. (a) Before you begin, the model should look like the picture attached below. Formulate a linear programming model for this problem, filling in the template below. Type up the full mathematical model in Word and include it here, fill in the template provided below, clearly indicating: The decision variables. Define them precisely. The objective. Using your decision variables, formulate the objective function. The constraints. Using your decision variables, formulate these constraints. Decision Variables list and define the decision variables here Objective and Objective Function state the objective (min or max) and include the objective function here Constraints enter 1 constraint per line including a name for each constraint that contains your initials (b) The Linear Programming template is in Data.xlsx in the worksheet Appendix 1(b) - use this to enter your LP model because it is setted up. Your constraint names should begin with your initials, e.g. BC Budget if your initials are BC. Use EXCEL Solver to obtain a solution to the linear programming model from part (a), together with an Answer Report and a Sensitivity Report. Round the objective function answer to 2 decimal places. (c) You are considering increasing the use of sugar by an extra 10kg each day. Using the output for the problem solved in part (b), rounded to 2 decimal places, determine the impact on daily operations by: i. Stating and interpreting the range of feasibility for Sugar; ii. Stating and interpreting the shadow price for Sugar; and iii. Calculating the new maximum daily profit. By referring to the shadow price, would it be worthwhile increasing the amount of Daily Labour instead of Sugar? Explain briefly. (d) Finally, you want to visualise the results to present at the next board meeting to communicate the number of daily sales by wine type and somehow include the wine prices too. You decide to use a bubble plot because you like the idea of bubbles - might have something to do with sparkling wine Complete Table 1 and use this table to produce a bubble plot. Number of Wine Type Bottles Sold Price per Bottle Each Day Premium From (b) round Enter the price of each Reserve your answer for wine type from your Varietal each wine type to objective function in Bulk the nearest whole (a). You can keep the number prices to 2 decimal places Table 1: Bottles sold daily by wine type | B C D E | Orange Wines Variables Parameters 10 11 12 Objective function (max profit) Grade A Grapes Grade B Grapes Sugar Labour Production Costs Sales 13 14 Objective function Maximise Profit 15 16 17 18 19 20 Constraints LHS RHS Grade A Grapes Grade B Grapes 21 Sugar 22 Labour 25 Production Costs Sales Non-neg. P Non-neg. R 26 v XXL Non-neg. V 28 Non-neg. B D E F G H I J C Price Wine Type Number of Bottles Sold each Day Premium Reserve Fill in the table with your results from (a) (Price) andthe output from Solver in (b) (Number of Bottles Sold each Day) Varietal Bulk For the Number of Bottles Sold each Day you can round the values to the nearest whole number Price can be left to 2 decimal places