Chapter 11, Problem 20RQ

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 locks like, but it should be constructed at the minimum cost possible.

1. 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.
2. b. Create a proper plot of the material cost, 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. Indicate the location of this answer on your graph.

3. 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.