In: Math
. Dean runs
The Creamy Bar which specialises in artisan ice cream sold
at a local...
. 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.
Task
1
Construct a
mathematical model for this problem. In doing so, consider the
following:
- What are the decision variables for this
problem?
- Using decision variables identified in part (a),
formulate the objective function for this problem. Is the quantity
of interest to be maximised or minimised?
- What constraints are relevant to this problem? Using
the decision variables from part (a), formulate those
constraints.
Task
2
Use Excel
Solver to obtain a solution to the mathematical problem from Task
1. Your submission should include:
- your Excel spreadsheet
- the Sensitivity Report
- the Answer Report
Task 3
Use your
Excel output to answer the following questions:
- 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 market 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?
- 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?
- 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?
- 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.
Task
4
Write a
report outlining the solution and discussing your findings from
Task 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 working
through this assignment question:
- The first step is always to work out the mathematical
set up for the problem. This means identifying decision variables,
formulating the objective function and then formulating
constraints. At this stage, we are not trying to solve the problem
or work out interactions among constraints. We simply list all
conditions that must be satisfied.
When you
complete Task 1, you should have two decision variables, the
objective 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 of
them).
- The second step is to find a solution. Task 2 tells you
specifically to use Excel Solver to find this solution. The key
here is to translate all mathematical expressions from Task 1 into
Excel format. Instructions for doing so can be found under Topic 5
in the Excel booklet, as well as in the Linear Programming
supplement. In addition, the Lecture notes page in this website
gives you access to Excel spreadsheets used to generate Excel
output shown in lecture slides for Week 5. It may be worthwhile
examining them before attempting Task 2.
- The final step is interpreting the solution that has
been found, which is Task 3.
- The report in Task 4 is a summary of the results from
linear programming and sensitivity analysis in Tasks 2 and
3.