Design a spreadsheet to estimate the dimensions of a tank for holding water. The

Question

Design a spreadsheet to estimate the dimensions of a tank for holding water. The spreadsheet should use input values from the user to return the height and diameter for a tank that will use the smallest amount of steel and therefore cost the least to produce.

Attached you will find an example of how a working spreadsheet should look. The layout of the sheet that you design does not have to match the layout of this example, but your spreadsheet must use the same input values and return the same results.

The spreadsheet must be capable of approximating the lowest cost dimensions for any value of the input variables.

The "Input Variables" are Volume (gal), Price of Steel ($/lb), Budget ($), Thickness (in), and Density (lb/ft^3).

The Calculations: Diameter (ft) are given and are 2-9. Height (ft), Surface area (ft^2), steel volume (ft^3), and cost ($).

The Results: Cost ($), under budget?, Diameter (ft), Height (ft).

Please Help!!!!

Explanation / Answer

i dont know how to send a excel sheet to you but i will solve the problem here
do it yourself on excel

your main problem is to maximise volume with minimum surfave area

Maximizing the volume for a given surface area is the same as
minimizing the surface area for a given volume...

I'll minimize surface area

Since we're not working with a specific volume, and want to end with a ratio of
r to h, we can substute any value for volume to simplify calculations.
Let volume =

v = r^2 h
= r^2 h
h = 1 / r^2

surface area (a)
a = 2 r^2 + 2 r h
. . . . . . . . . . substitute for h from the volume formula
a = 2 r^2 + 2 r * 1 / r^2
. . . . . . . . . . simplify
a = 2 r^2 + (2 ) / r

area is minimized when it's derivative = 0

a (r) = 2 r^2 + (2 ) / r
a ' (r) = 4 r - (2 ) / r^2

4 r - (2 ) / r^2 = 0
r = 1 / 2^(1/3)
. . . . . . knowing the optimum value of r, calculate h from the original formulas
h = 1 / r^2
h = 1 / (1 / 2^(1/3))^2
h = 2^(2/3)

r / h = [ 1 / 2^(1/3) ] / [ 2^(2/3) ]
r / h = 1/2
2r = h

volume of a cylinder is maximized or surface area is minimized when
diameter = height

volume of cylinder =pi * radius ^2 * height =pi*diameter^2*height /4

diameter = height=h

V=pi*h^3/4

h=(4*V/pi)

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.