We have obtained a contract to construct metal boxes (square bottom, rectangular sides, no top) for storing sand. Each box is to contain a specified volume, and all edges are to be welded. Each box will require the following information: A volume (V, in units of cubic inches), the length of one side of the bottom (L, in units of inches), the box height (H, in units of inches), and the material cost (M, in units of dollars per square inch). To determine the total cost to manufacture a box, we must include not only the cost of the material, but also the cost of welding all the edges. Welding costs depend on the number of linear inches that are welded (W, in units of dollars per inch). The client does not care what the box looks like, but it should be constructed at the minimum cost possible. (a) Construct a worksheet that will depict the cost of the material for one box, the welding cost for one box, and the total cost for the box, First, create at the top of your worksheet a section to allow the user to specify as absolute references the variables V, M, and W. Next, create a column for length ranging from 2 to 20 inches in increments of 2 inches. Finally, determine the material cost per box, welding cost per box, and total cost. (b) Create a proper plot of the material cost per box, welding cost, and total cost (all shown as ordinate values) versus the box length. For the following values, use the graph to determine the box shape for minimum cost: V = 500 cubic inches, M = $1.00 per square inch, and W = $3.00 per inch. (c) Below the table created in part (a), create a row to determine the minimum value for the material cost, the welding cost, and the total cost shown in the table. Use the information to create conditional formatting in the table to show the minimum values in the table as cells with a dark color background and white text. The highlighted cells should verify the solution found in part (b) using the graph.
We have obtained a contract to construct metal boxes (square bottom, rectangular sides, no top)
for storing sand. Each box is to contain a specified volume, and all edges are to be welded. Each
box will require the following information: A volume (V, in units of cubic inches), the length of
one side of the bottom (L, in units of inches), the box height (H, in units of inches), and the
material cost (M, in units of dollars per square inch). To determine the total cost to manufacture a
box, we must include not only the cost of the material, but also the cost of welding all the edges.
Welding costs depend on the number of linear inches that are welded (W, in units of dollars per
inch). The client does not care what the box looks like, but it should be constructed at the
minimum cost possible.
(a) Construct a worksheet that will depict the cost of the material for one box, the welding cost
for one box, and the total cost for the box, First, create at the top of your worksheet a section to
allow the user to specify as absolute references the variables V, M, and W. Next, create a column
for length ranging from 2 to 20 inches in increments of 2 inches. Finally, determine the material
cost per box, welding cost per box, and total cost.
(b) Create a proper plot of the material cost per box, welding cost, and total cost (all shown as
ordinate values) versus the box length. For the following values, use the graph to determine the
box shape for minimum cost: V = 500 cubic inches, M = $1.00 per square inch, and W = $3.00
per inch.
(c) Below the table created in part (a), create a row to determine the minimum value for the
material cost, the welding cost, and the total cost shown in the table. Use the information to
create conditional formatting in the table to show the minimum values in the table as cells with a
dark color background and white text. The highlighted cells should verify the solution found in
part (b) using the graph.
Trending now
This is a popular solution!
Step by step
Solved in 3 steps
