Solve a, b, c, and d

Maximising Prot 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 higherend market that has become popular in recent years. Your family would like to maximise the prot 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 gure it out'. You have recorded all the relevant information you need to construct in 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 prot made from selling bottles of wine. Decision Variables: Orange Wines makes the following prot 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 $16.50 for each bottle of Bulk wrne. Constraints: Orange Wines has a number of constraints to meet when making and selling wine: Grade A Grams: 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 Grams: 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 B C D Orange Wines IN Variables P R V Parameters P R V Objective function (max profit) Grade A Grapes 9 Grade B Grapes 10 Sugar 11 Labour 12 Production Costs 13 Sales 14 15 Objective function 16 Maximise Profit 17 18 Constraints LHS RHS 19 Grade A Grapes 20 Grade B Grapes = 26 Non-neg. R > = 27 Non-neg. V 28 Non-neg. B B C D E G Wine Type Number of Bottles Sold each Day Price Premium Fill in the table with your results from (a) (Price) andthe Reserve output from Solver in (b) (Number of Bottles Sold each Varietal Day). Bulk For the Number of Bottles Sold each Day you can round the values to the nearest whole number 10 Price can be left to 2 decimal places.(a) Before you begin. the model should look like the picture attached below. Formulate a linear programming model for this problem. lilting in the template below. Type up the full mathematical model in Word and include it here. till In the template provided below. cleariy indicating: e The decision variables. Dene them precisely. e The objective. Using your decision variables. formulate the objective function. e The constraints. Using your decision variables. formulate these constraints. Decision Variables list and dene 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 Soiver to obtain a solution to the linear programming model from part (a). togetherwith an Answer Report and a Sensitivity Report. Round the objective function answer to 2 decimal places. to) 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 prot. By referring to the shadow price. would It be worthwhile increasing the amount of Daily Labour instead of Sugar? Explain briey. (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. Nunlbcr of Bottles Sold Price per Bottle \\Vino 'l'ypo Each Day 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 u laces 69 Table 1: Bottles sofd daily by wine type