. Dean runsThe Creamy Bar which specialises in artisan ice cream soldat a local farmer’s market. Prevailing prices in the local marketare $8 for a take-home tub of Classic Vanilla and $15 for a tub ofChocolate Almond Fudge.

The localdairy farmer delivers 48 litres of milk every Friday in preparationfor market day. Classic Vanilla will need 0.5 litres per tub andChocolate Almond Fudge requires 3 times as much. Both flavoursrequire 500g of sugar to enhance the taste. There is a total of20kg of sugar available per market day. For the signature velvetymouthfeel, Dean adds 0.5 litres of heavy cream to Classic Vanillaand double the amount for Chocolate Almond Fudge. He ordered 50litres of heavy cream from the supplier.

Task1

Construct amathematical model for this problem. In doing so, consider thefollowing:

1. What are the decision variables for thisproblem?
2. Using decision variables identified in part (a),formulate the objective function for this problem. Is the quantityof interest to be maximised or minimised?
3. What constraints are relevant to this problem? Usingthe decision variables from part (a), formulate thoseconstraints.

Task2

Use ExcelSolver to obtain a solution to the mathematical problem from Task1. Your submission should include:

• your Excel spreadsheet
• the Sensitivity Report
• the Answer Report

          Task 3

Use yourExcel output to answer the following questions:

1. Describe the linear programming solution to the Dean ofThe Creamy Bar in terms of:
   • The optimum number of take-home tubs of Classic Vanillaand Chocolate Almond Fudge to prepare each marketday.
   • The maximum revenue per market day.
   • Whether all the milk purchased will be fullyutilised.
   • Whether all the sugar allocated will be fullyutilised.
   • Whether all the heavy cream ordered will be fullyused.

    Which of the Solver reports helps youanswer these questions?

1. 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 Deanalso draws a $100 salary per market day.

    Which Solver report allows you toanswer this question?

1. Due to the popularity of the Chocolate Almond Fudgeflavour, Dean is hoping to increase the price to $20 per take-hometub. Would the solution obtained in Task 2 still be optimal? Whichof the EXCEL reports helps you answer this question? Justify youranswer carefully. How would the solution and The Creamy Bars’revenue change, if at all?

1. In preparation for the scorching heat in summer, Deanwould like to purchase an extra 10 litres of milk to increase icecream production. Would the solution obtained in Task 2 still beoptimal? Which of the EXCEL reports helps you answer this question?Justify your answer carefully. How would the solution and TheCreamy Bars' revenue change, if at all?

Attach thenew Answer Report ONLY, for the scenario in which Dean purchases 58litres of milk, verifying your calculated maximum revenue permarket day.

Task4

Write areport outlining the solution and discussing your findings fromTask 3 (at most two pages, double-spaced, at least 2cm margins,12pt Times New Roman font or equivalent).

Here are a few points to consider while workingthrough this assignment question:

1. The first step is always to work out the mathematicalset up for the problem. This means identifying decision variables,formulating the objective function and then formulatingconstraints. At this stage, we are not trying to solve the problemor work out interactions among constraints. We simply list allconditions that must be satisfied.

When youcomplete Task 1, you should have two decision variables, theobjective function written in terms of those decision variables,and five constraints, also written in terms of decision variables(some using both decision variables, others just one ofthem).

1. The second step is to find a solution. Task 2 tells youspecifically to use Excel Solver to find this solution. The keyhere is to translate all mathematical expressions from Task 1 intoExcel format. Instructions for doing so can be found under Topic 5in the Excel booklet, as well as in the Linear Programmingsupplement. In addition, the Lecture notes page in this websitegives you access to Excel spreadsheets used to generate Exceloutput shown in lecture slides for Week 5. It may be worthwhileexamining them before attempting Task 2.
2. The final step is interpreting the solution that hasbeen found, which is Task 3.
3. The report in Task 4 is a summary of the results fromlinear programming and sensitivity analysis in Tasks 2 and3.