Chapter 5 section 2

Excel Companion Chapter 5 section 2

ClsscMin.xls

Situation 1:

A company must mass produce a closed box that holds a fixed volume, in this case 1000 cubic units. The top and bottom of this box is a square. The cost of the building material for the top, bottom, and sides is all the same. Consequently, the greater the surface area the more expensive the box. The objective is to build a box that satisfies this volume condition and has the minimum surface area

A(x) = 2*x^2 + 4 * Volume/x

ClsscMin.xls - min area (box)

Questions

1.	If we let x represent the side of the square base and we let h be the height of the box then write each of the following in terms of these variables:
a)	an equation for volume = 1000.
b)	an expression for twice the surface area of the square base.
c)	an expression for four times the surface area of one of the sides
d)	an expression for the total surface area.
2.	Explain exactly how to obtain the Excel formula *= 2x^2 + 4 Volume/x* for the total surface area as a function of the side of the square base where the Volume is fixed.

Notice the block of cells with headings guess, bounds, and target.	guess	bounds	target
Target contains an expression for surface area in terms of guess	8.00	15	628.00

On the spreadsheet, each of the shaded cells has been named 'guess', 'bounds', and 'target' respectively. This block of cells will make it easy for us to apply the Solver Add-in.

Invoke Solver from the Tools menu and fill in the dialog box as shown on the left. Next press the Solve button to start Solver on its search for a value between 8 and 15 (our chosen upper bound) that minimizes this target. If a solution exits it will appear in 'guess' and the minimum surface area will appear in 'target'

3.	What solution is found by Solver?
4.	Use derivatives and algebra to find the x coordinate that optimizes this area function for a volume of 1000 cubic units and prove that your result is actually a local minimum.
5.	Suppose a smaller box must be produced to hold exactly 100 cubic units. a) Without using Solver or calculus, make a conjecture about the optimal solution.
	b) Then use both Solver and calculus to solve the problem.
6.	Modify the conditions of the box with minimum surface area as follows: Instead of a closed box the new box does not have a top and its volume must be 108 cubic units. On the spreadsheet *ClsscMin.xls - min area (box*) put a 1 instead of a 2 in the upper right for an open box. You must also change the volume to 108.
	a) Use Solver to find the optimal solution of this open box problem.
	b) Verify your conclusion algebraically by applying calculus.
7.	Experiment with the sheet min area (box) in ClsscMin.xls for open and closed boxes with different volumes. Apply Solver and keep a record of the values of your parameters and the corresponding optimal solutions. Then consider the area function where n and V are constants.
	a) Determine the first and second derivatives of A(x)
	b) If x is positive then what is the sign of the second derivative?
	c) Find the value of x that minimizes A(x) in terms of n and V
	d) Compare and contrast the spreadsheet approach with the calculus approach to optimization.

Enter your name and ID here:

First name Last name ID

Return to Table of Contents

Return to Chapter 5 Index