“The U-Pack Box Company”

Excel Activity

Polynomial Functions

The U-Pack Company wants to develop a new product line. They want to manufacture cardboard boxes that fold together for use and unfold for storage. The CEO of the company hires you to report on the feasibility and profitability of this potential concept. The engineers at the company are to design a stamping machine that will cut the raw material and imprint the fold markings. The finance department will determine the cost and price of the product.

The engineers plan to design a stamping machine that will cut the same size square from each corner of the cardboard to allow the box to fold properly as shown in the diagram below:

l

w

h h

The raw materials used by the stamping machine are 48” x 60” flat sheets of cardboard. The machine will produce 3000 boxes every hour of operation for an eight-hour day.

So how does this relate to Algebra? Since the volume of a rectangular box is dependent upon its height, length and width, a function can be written to produce the volume of the box dependent upon one of these dimensions. Moreover, the cost to produce such a box then can be written in terms of this same cut since the price is dependent upon the volume. As the volume of the box changes, so will the profit realized by the company. Your objective is to determine what size square cut will create a box whose volume will in turn provide the greatest profitability.

The goal is to maximize the profit of one box. Making the biggest box may cost more money to produce; therefore the biggest volume may not result in the largest profit. Design engineers must design a stamping machine and the finance department must analyze the cost to produce the box and the price to charge to sell. The cost to produce one box is based on the dimensions of the box. These costs include the cost for labor to produce and materials to make.

Based on this information, the finance department has determined that:

1) The cost to produce a box can be modeled by the function, , where

h = height of box (cut size)

2) The selling price of a single box can be modeled by the function, P(v) = , where v = volume of box

Working with a partner, appropriately design a spreadsheet, using Microsoft Excel that will display all the different boxes that could be produced by the stamping machine. Include the following in the spreadsheet:

(1) The length of the cut (height of box) (in ½ inch intervals)

(2) The corresponding length of the box

(3) The corresponding width of the box

(4) The resulting volume of the box

(5) The cost to produce one box

(6) The sell price of one box

(7) The revenue per box

(8) The Profitability of the box for one day

Along with the spread sheet, design a graph using Excel’s Chart Wizard that compares the following: (Include a regression or trend line on each graph and be sure axes are defined and scaled. Include the equation of the trend line with slope and y-intercept rounded to the nearest tenth.)

(1) Scatter plot comparing the length of the cut to the resulting volume

(2) Scatter plot comparing the volume and revenue; and volume and cost together

(3) Scatter plot comparing the volume and profit

Print out your spreadsheet, graphs, and the spreadsheet displaying the formulas you used to determine these values.