ICA24-I
pdf
keyboard_arrow_up
School
University of Minnesota-Twin Cities *
*We aren’t endorsed by this school
Course
2551
Subject
Industrial Engineering
Date
Jan 9, 2024
Type
Pages
2
Uploaded by AdmiralTree7176
BA2051
In Class Assignment #24
Optimization
Goal Seek
#1
A soft drink company packages its drinks in cans. The mould that they are
currently using for the top of the can has a radius of 3.5 cm.
We want each can to hold 375 ml of drink.
The volume of a can (cylinder) is calculated by the formula:
pi*radius*radius*height (cell C5).
You can use the PI() function to get the value of pi.
In cell C2, use Goal Seek to determine the height of the can.
Data - What if Analysis - Goal Seek
Data Table
#2
The company wants to look at the various cases that different sized cans of
radius 2 - 6 cm and different heights of 7 - 12 cm can hold.
Consider increments of 0.5 cm for both the radius and the height of the
can.
Construct a Data Table with the volume formula in cell C8, cells C1 and C2
can be used for the input values.
Data - What if Analysis - Data Table
Solver
#3
Make a copy of this sheet before proceeding (name it Steps 3-4).
The company is considering reducing the cost of production of cans, which
means minimizing the surface area of the can.
This is because a smaller surface area results in a lower metal cost.
Use solver to determine the dimensions of the can (radius and height) to
minimize the surface area while ensuring the can still holds 375 ml.
The formula for the surface area of a can (cylinder) is:
(2*pi*radius*height) + (2*pi*radius*radius).
Install the Solver if you haven't already:
File - Options - Addins - Excel
Addins - Go - check the box for Solver - OK
The Solver will appear on the right side of the Data menu.
Data - Solver - minimize surface area (C4) by changing radius (C1) and
height (C2) subject to the constraint that volume must equal 375 ml.
General
#4
The company uses cubic crates to pack the cans in. The current dimensions
of the crate are 120 cm * 120 cm * 120 cm.
Calculate how many cans can be packed into the crate, from a
mathematical point of view (column R).
Remember that the radius is only half the width of the can.
From a practical point of view, we will have to round each of the values
downwards.
Calculate the total cans from a practical point of view.
=ROUNDDOWN(number, number of digits 0)
Total cans = cans across width * cans across length * cans tall
Radius
BA2051
Solver
#5
Make a copy of this sheet before proceeding (name it Steps 5-7).
What is the difference in the
number of cans? Let’s call this "potential cans
(wasted space)".
Use solver to determine the dimensions of the cubic crate that minimizes
the "potential cans (wasted space)" to reduce waste.
Data - Solver - minimize potential cans (R7) by changing cube crate length
(P2) subject to no constraints.
SUMPRODUCT
#6
Now let's estimate the cost of the metal.
The actual metal price is currently
$0.002
per cm of surface area (enter in
Q17), however, metal prices change frequently so there is only a
40% probability it will remain at this level.
Other possible prices and their
probabilities are as follows:
10% probability that the metal price would double (the high price), and a
10% probability that the metal price would halve (the low price).
20% probability that the metal price will be the midpoint between the
current and high price.
20% probability that the metal price will be the midpoint between the
current price and the low price.
These probabilities are displayed starting in Q15.
Use
SUMPRODUCT
to calculate an expected estimate of the metal price
per cm in Q10.
Copy this cell
’
s result and paste as values.
=SUMPRODUCT (array 1, array 2) - this sums the product of the values in
the first array when multiplied by the respective value in the second array.
Based on this expected cost now calculate the metal price per can (Q11)
and crate (Q12).
Scenario
Manager
#7
Use
Scenario Manager
to generate a summary to present the Total Metal
Cost to management.
This should include 3 scenarios:
the expected cost
that you calculated, a
Low Price = 0.001
and
High Price = .004
.
Data - What If Analysis - Scenario Manager - build the High and Low
scenarios (the current one will be automatically included) - Summary -
Scenario Summary - Result cells (Q11:Q12, metal cost per can and total
metal cost per crate).
Notice that the cells have been named to make the titles on the Scenario
summary meaningful - otherwise it would just show the cell reference
number.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help