Question

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:

  1. What are the decision variables for this problem?
  2. Using decision variables identified in part (a), formulate the objective function for this problem. Is the quantity of interest to be maximised or minimised?
  3. 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:

  1. 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?

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

    Which Solver report allows you to answer this question?

  1. 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?

  1. 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:

  1. 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).

  1. 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.
  2. The final step is interpreting the solution that has been found, which is Task 3.
  3. The report in Task 4 is a summary of the results from linear programming and sensitivity analysis in Tasks 2 and 3.

Solutions

Expert Solution

Task 2.

Set up the LP as shown in the screenshot below.

Take note the formulas used to calculate the value.

The solver input are given below.

The final solution is given below.


Sensitivity report

Answer report


Related Solutions

Kraft is a Wholesale distributor of artisan cheese and ice cream. The following information is available...
Kraft is a Wholesale distributor of artisan cheese and ice cream. The following information is available for April 2017. The beginning cash balance is $400,000. Required: Prepare the following for April 2017 – Be sure to read the additional information below a. Sales budget b. Purchase Budget c. Cash Budget d. Budgeted Income Statement. Additional info: • Beginning cash balance is $400,000 • Purchases of merchandise are paid 60% in the current month and 40% in the following month. Purchases...
You manage an ice cream factory that makes two flavors: Creamy Vanilla and Continental Mocha. Into...
You manage an ice cream factory that makes two flavors: Creamy Vanilla and Continental Mocha. Into each quart of Creamy Vanilla go 2 eggs and 3 cups of cream. Into each quart of Continental Mocha go 1 egg and 3 cups of cream. You have in stock 500 eggs and 900 cups of cream. You make a profit of $3 on each quart of Creamy Vanilla and $2 on each quart of Continental Mocha. How many quarts of each flavor...
You manage an ice cream factory that makes two flavors: Creamy Vanilla and Continental Mocha. Into...
You manage an ice cream factory that makes two flavors: Creamy Vanilla and Continental Mocha. Into each quart of Creamy Vanilla go 2 eggs and 3 cups of cream. Into each quart of Continental Mocha go 1 egg and 3 cups of cream. You have in stock 400 eggs and 750 cups of cream. You make a profit of $3 on each quart of Creamy Vanilla and $2 on each quart of Continental Mocha. How many quarts of each flavor...
The Old Tyme Ice Cream Shoppe sold 8,700 servings of ice cream during June for $5...
The Old Tyme Ice Cream Shoppe sold 8,700 servings of ice cream during June for $5 per serving. The shop purchases the ice cream in large tubs from the Amazing Ice Cream Company. Each tub costs the shop $9 and has enough ice cream to fill 20 ice cream cones. The shop purchases the ice cream cones for $0.25 each from a local warehouse club. Located in an outdoor​ mall, the rent for the shop space is $1,600 per month....
Kiwidale Dairy is considering purchasing a new ice-cream maker. Two models, Smoothie and Creamy, are available...
Kiwidale Dairy is considering purchasing a new ice-cream maker. Two models, Smoothie and Creamy, are available and their information is given below (all costs and profits are in dollars): Smoothie Creamy First Cost 16,000 35,500 Service Life 14 Years 12 Years Annual Profit 4,200 10,950 Annual Operating Cost 1,200 3,450 Salvage Value 2,400 4,850 Which alternative is better if the MARR for Kiwidale Dairy is 17.5%. Assume that each alternative can be repeated indefinitely {Perform all calculations using 5 significant...
Kiwidale Dairy is considering purchasing a new ice-cream maker. Two models, Smoothie and Creamy, are available...
Kiwidale Dairy is considering purchasing a new ice-cream maker. Two models, Smoothie and Creamy, are available and their information is given below. (perform all calculation using 5 significant figures, and give your final answer to 1 decimal place). (a) What is Kiwidale's MARR that makes the two alternatives equivalent? Use a present worth comparison. Smoothie Creamy First Cost 19,000 38,000 Service Life 12 years 12 years Annual profit 4,500 11,200 Annual operating cost 1,200 3,920 Salvage value 2,250 5,400 A...
1. You manage an ice cream factory that makes two flavors: Creamy Vanilla and Continental Mocha....
1. You manage an ice cream factory that makes two flavors: Creamy Vanilla and Continental Mocha. Into each quart of Creamy Vanilla go 2 eggs and 3 cups of cream. Into each quart of Continental Mocha go 1 egg and 3 cups of cream. You have in stock 850 eggs and 1,650 cups of cream. How many quarts of each flavor should you make in order to use up all the eggs and cream? 2. Enormous State University's Math Department...
Helena Lorimer runs a set of ice cream cafes that sell mainly three flavors of ice...
Helena Lorimer runs a set of ice cream cafes that sell mainly three flavors of ice cream: vanilla, chocolate, and strawberry. Hot weather and high demand have caused her to run short of the main ingredients: milk, sugar, and cream. She has decided to make the best assortment of ice cream quantities in these three flavors and ration out the deliveries to the cafes. She has collected data on the profitability of the various flavors, availability of supplies, and the...
Helena Lorimer runs a set of ice cream cafes that sell mainly three flavors of ice...
Helena Lorimer runs a set of ice cream cafes that sell mainly three flavors of ice cream: vanilla, chocolate, and strawberry. Hot weather and high demand have caused her to run short of the main ingredients: milk, sugar, and cream. She has decided to make the best assortment of ice cream quantities in these three flavors and ration out the deliveries to the cafes. She has collected data on the profitability of the various flavors, availability of supplies, and the...
John runs a bakery making artisan bread. Which of the following would be considered a product...
John runs a bakery making artisan bread. Which of the following would be considered a product cost? (check all that apply) Group of answer choices energy utilities the monthly rental payment on the bakery the cost of the dough used to make the bread the cost of the packaging materials (plastic bags) used for a loaf of bread
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT