Optimizing Volumes and Surface Areas Using a Spreadsheet


The U.S Postal Service will accept a box for domestic shipment only if the sum of the length and girth (distance around) does not exceed 108 in. Find the dimensions fo the largest acceptable box with a square end.


To help you through the problem, there are several questions for you to answer. When you are asked to fill something in on your spreadsheet do so!!


1. The spreadsheet is set up for you. Just download the excel spreadsheet file girth.xls, and you're ready to go. Your first job is to fill the length column with consecutive numbers, starting with 1. You'll have to figure out when to stop!! When you're done, fill in the following:
I stopped at the number ______, because _____________________________________

______________________________________________________________________



2. a) Write an expression that represents the Postal Service's specifications for acceptable boxes in terms of l (length) and g (girth).
108 = _________________

b) Solve the above equation for g. This equation will be used to find the dependent variable, g, in the spreadsheet. g = __________________
Before you type the formula into the spreadsheet in cell, C6, write it down using the cell references. For the greatest acceptable length and girth cell reference you will need to use absolute references, because it will never change, so use $E$2. s = ________________Type this into the spreadsheet and fill down.

c) Next, write an expression that represents the edge of the square end of the box, in terms of g (girth). Before you type the formula into the spreadsheet in cell, D6, write it down using the cell references. s = _______________ Type this into the spreadsheet and fill down.


3. a) Write an equation that represents the volume of the box in terms of l (length) and s (side). volume = __________________ Now you will need to write this formula using cell references. You only need to do it for the first cell in the first row, because you will "fill down" for the rest of the column. volume = ___________________


4. Now that the spreadsheet is filled in you need to determine the dimensions of the largest possible box with a square end and record them here. ________________________



problem: A tank with a rectangular base and rectangular sides is to be open at the top. It is to be constructed so that its width is 4 meters and its volume is 36 cubic meters. If building the tank costs $10 per square meter for the base and $5 per square meter for the sides, what is the cost of the least expensive tank?


1. a) Write an equation for the volume of the box in terms of h (height) and l (length). Given that the volume is to be 36 cubic meters, and the width is to be 4 meters, write an equation for the height of the box in terms of l (length).
h = _________________


2. a) Write an equation for the area of the base in terms of l (length), given the width is to be 4 meters. area of base = ________________Write an equation for the cost of materials for the base in terms of l (length), given the width is to be 4 meters. cost of base = __________________

b) Write an equation for the lateral area of the box in terms of l (length) and h (height), given the width of the box is to be 4 meters. lateral area = _______________ Write an equation for the cost of materials for the lateral area in terms of l (length) and h (height), given the width is to be 4 meters. cost of lateral area = ______________

c) Write an equation for the total cost of materials for the open box, in terms of l (length) and h (height), given the width is to be 4 meters. total cost = ______________

The formulas you have written above are all you will need to solve the problem, GOOD LUCK!!

Need more problems on which you can practice? Go on to the next page.