Solve a, b, c, and d.
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. 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
