Charley’s Ice Cream Charley’s Ice Cream (CIC) specializes in small-batch homemad
ID: 3913376 • Letter: C
Question
Charley’s Ice Cream
Charley’s Ice Cream (CIC) specializes in small-batch homemade ice cream. The first stage in ice cream making is the blending of ingredients to obtain a 50 pounds mix, which meets pre-specified requirements based on 7 constituents. The ingredients currently used in CIC’s ice cream are shown in Table 1, while Table 2 shows the constituent requirements, which are based on the percentages of a 50-pound batch.
Table 1: Ingredients in Pounds
Ingredient
Pounds
Plastic cream
9.73
Sugared frozen egg yolk
0.44
Water
25.24
Skim milk powder
3.03
Stabilizer
0.12
Liquid sugar
11.27
Emulsifier
0.07
Table 2: Requirements by Constituent
Constituent
Requirement
Fat
16.00%
Serum Solids
8.00%
Sugar Solids
16.00%
Egg Solids
0.35%
Stabilizer
0.25%
Emulsifier
0.15%
Water
59.25%
There are additional ingredients that could be used to make a batch of ice cream. In other words, there are other combinations of ingredients that could meet the 50-pound batch size and the constituent requirements. These additional ingredients are shown in Table 3 along with the current price per pound.
Each ingredient contributes to the constant requirement differently. For example, there are many sources of fat. The input/output matrix showing the pounds of each of the 7 constituents provided by each of the 14 potential ingredients in shown in Table 4. For example, one pound of 40% cream adds 0.4 pound of fat, 0.1 pound of serum solids and 0.5 pounds of water to the mix. In addition, one pound of skim milk powder adds one pound of serum solids. For several years, CIC has been using the recipe shown in Table 1 for 50 pounds batches.
Table 3: Ingredient costs in $ per pound
40% Cream
23% Cream
Butter
Plastic Cream
Butter Oil
4% Milk
Cond Skim Milk
Skim Milk Powder
Liquid Sugar
Sugared Frozen Egg Yolk
Powdered Egg Yolk
Stabilizer
Emulsifier
Water
$1.19
$0.70
$2.32
$2.30
$2.87
$0.25
$0.35
$0.65
$0.25
$1.75
$4.45
$2.45
$1.65
$0.01
Table 4: Input/output ratios for 14 ingredients by 7 constituents
40% Cream
23% Cream
Butter
Plastic Cream
Butter Oil
4% Milk
Cond Skim Milk
Skim
Milk Powder
Liquid Sugar
Sugared Frozen Egg Yolk
Powdered Egg Yolk
Stabilizer
Emulsifier
Water
Fat
0.4
0.2
0.8
0.8
0.9
0.1
0.5
0.5
Serum Solids
0.1
0.1
0.1
0.3
1.0
Sugar Solids
0.7
0.1
Egg Solids
0.4
0.5
Stabilizer
1.0
Emulsifier
1.0
Water
0.5
0.8
0.2
0.1
0.1
0.8
0.7
0.3
1.0
Based on the information above, determine the cost of the original recipe.
HINT: Later, you will be asked to use linear programming to determine additional recipes. Before you are asked to utilize Solver,
develop a worksheet that clearly shows the 14 possible decisions cells along with the ingredients listed in Table 1. In addition, formulate your worksheet where you are checking that the 50 pound batch requirement is met, along with the percent recruitments of constituent shown in Table 2 are met. In other words, set up the spreadsheet as if you were setting it up for a linear program. However, for this part of the problem, you will not need to run Solver. That portion of the problem will come later.
HINT: Since the batch size (50 pounds) is a specified constant, you may use it in the divisor of formulas for actual percentages of constituents without creating a nonlinear ratio. In other words, to ensure that the current recipe shown in Table 1 meets the percentage constraints shown in Table 2, you should divide the constituent by 50 pounds. Remember, you cannot have decision cells in the numerator of a calculation, but there is no rule about a constant in the numerator of a calculated cell.
CIC proposes to use linear programming to determine if there is a better mixture of ingredients that is more cost effective that will still met the constituent requirements shown in Table 2. Create a copy of your original worksheet to develop a linear program and determine the minimum cost using all 14 possible ingredients to meet all requirements.
CIC is also considering loosening the requirements for the 7 constituents from exact percentages to the ranges shown in Table 5. Create a copy of your solution for part 2. Use Solver to determine the minimum cost recipe based on the proposed ranges of percentages for the constituents.
Table 5: Proposed Minimum and Maximum Requirements by Constituent
Constituent
Minimum
Maximum
Fat
15.00%
17.00%
Serum Solids
7.00%
9.00%
Sugar Solids
15.50%
16.50%
Egg Solids
0.30%
0.40%
Stabilizer
0.20%
0.30%
Emulsifier
0.10%
0.20%
Water
58.00%
59.50%
4. Create a worksheet to summarize the ingredients and the costs of the three recipes above. In other words, you will have 4 worksheets for the solution to this problem. 1) Original Recipe with Total Cost (no Solver), 2) Exact Percentages Recipe with Total Cost (Solver Req.), 3) Proposed Ranges Recipe with Total Cost (Solver Req.), and 4) a Summary that shows the 3 recipes with their total cost.
Table 1: Ingredients in Pounds
Ingredient
Pounds
Plastic cream
9.73
Sugared frozen egg yolk
0.44
Water
25.24
Skim milk powder
3.03
Stabilizer
0.12
Liquid sugar
11.27
Emulsifier
0.07
Explanation / Answer
Total of all the ingredients comes out to be $28.60
Other parts of the question is not answered as you have not marked where does other question starts and not specified them and asked this whole doubt as one continuous question and not distributed it in parts.
The total cost of the original recipe Ingredients Pounds Price per pound Total price Plastic Cream 9.73 2.3 22.379 Sugared frozen egg yolk 0.44 1.75 0.77 Water 25.24 0.01 0.2524 Skim Milk powder 3.03 0.65 1.9695 Stabilizer 0.12 2.45 0.294 Liquid Sugar 11.27 0.25 2.8175 Emulsifier 0.07 1.65 0.1155