2. (50 marks). There are 4 tasks in this part. Dean runs The Creamy Bar which specialises...
Question:
2. (50 marks). There are 4 tasks in this part. Dean runs The Creamy Bar which specialises in artisan ice cream sold at a local farmer's market. Prevailing prices in the local market are $8 for a take-home tub of Classic Vanilla and $15 for a tub of Chocolate Almond Fudge.
The local dairy farmer delivers 48 litres of milk every Friday in preparation for market day. Classic Vanilla will need 0.5 litres per tub and Chocolate Almond Fudge requires 3 times as much. Both flavours require 500g of sugar to enhance the taste. There is a total of 20kg of sugar available per market day. For the signature velvety mouthfeel, Dean adds 0.5 litres of heavy cream to Classic Vanilla and double the amount for Chocolate Almond Fudge. He ordered 50 litres of heavy cream from the supplier.
2-Task 1 (8 marks): Construct a mathematical model for this problem. In doing so, consider the following: (a) (1 mark) What are the decision variables for this problem?
(b) (2 marks) Using decision variables identified in part (a), formulate the objective function for this problem. Is the quantity of interest to be maximised or minimised?
(c) (5 marks) What constraints are relevant to this problem? Using the decision variables from part (a), formulate those constraints.
2-Task 2 (7 marks): Use Excel Solver to obtain a solution to the mathematical problem from Task 1. Your submission should include:
your Excel spreadsheet
the Answer Report
the Sensitivity Report
2-Task 3 (27 marks): Use your Excel output to answer the following questions:
(a) (7 marks) Describe the linear programming solution to the Dean of The Creamy Bar in terms of:
The optimum number of take-home tubs of Classic Vanilla and Chocolate Almond Fudge to prepare each market day.
The maximum revenue per day.
Whether all the milk purchased will be fully utilised.
Whether all the sugar allocated will be fully utilised.
Whether all the heavy cream ordered will be fully used.
Which of the Solver reports helps you answer these questions?
(b) (6 marks) What is the maximum profit per market day if Dean paid $1.2 per litre for milk and cream and $45 for sugar? Note that Dean also draws a $100 salary per market day. Which Solver report allows you to answer this question?
(c) (6 marks) Due to the popularity of the Chocolate Almond Fudge flavour, Dean is hoping to increase the price to $20 per take-home tub. Would the solution obtained in Task 2 still be optimal? Which of the EXCEL reports helps you answer this question? Justify your answer carefully. How would the solution and The Creamy Bars' revenue change, if at all?
(d) (8 marks) In preparation for the scorching heat in summer, Dean would like to purchase an extra 10 litres of milk to increase ice cream production. Would the solution obtained in Task 2 still be optimal? Which of the EXCEL reports helps you answer this question? Justify your answer carefully. How would the solution and The Creamy Bars' revenue change, if at all?
Attach the new Answer Report ONLY, for the scenario in which Dean purchases 58 litres of milk, verifying your calculated maximum revenue per market day.
2-Task 4 (8 marks): construct a paragraph outlining the solution and discussing your findings from Task 3