Objective

This assignment aims to use your spreadsheet to develop a feasible detention pond design for one of the sites assigned to your group in the lab.

This part of the assignment aims to estimate the cost of cut-and-fill and the detention pond volume. This application will support your designs for the detention pond project. Your spreadsheet should allow users to enter the row and column spacing of the elevation grid, the original elevations at each gridline intersection, and the proposed elevation. In addition, your spreadsheet should graph the original and the proposed elevations. The data in this table is available in spreadsheet form by clicking here: Cut-and-Fill Example data.

Your proposed elevations should be input on a separate worksheet in cells corresponding to the original elevations. A set of proposed elevations for this example problem is shown below. Note that the top of the proposed detention pond embankment is orange, the outside slope changes are green, and the slope changes inside the pond are gray.

Once you have entered the original and the proposed elevations at the grid points, subtract the proposed elevations from the original elevations to compute the change-in-elevation value for each grid point. In this case, negative (-) values will indicate fill points, and positive (+) values will indicate cut values.

The user should also be able to enter a value for the fill loss due to compaction. Once the change-in-elevations are computed, adjust the fill values (negative values) for compaction. Divide the value by (1 - %compaction) to change each negative change-in-elevation value. The example below divides each negative change-in-elevation value by 80%. The adjustment for fill effectively increases the cut volume to balance the required compacted fill volume.

The cut-and-fill volume should be estimated after the change-in-elevation values have been corrected for compaction. To calculate the value of cut-and-fill for each grid cell, multiply the average of the four corner change-in-elevation values by the area of the grid cell [remember negative (-) area is fill and (+) area is cut]. Sum the cut-and-fill volumes for all the grid cells to estimate the total cut-and-fill for the entire site. Report if the cut-and-fill is balanced or if more cut or fill is required to reach the proposed elevations. Also, compute the total volume of cut and the total volume of fill needed for the site. All volumes should be reported in cubic yards (yd³).

Next, estimate the cost of the cut-and-fill earthwork. Recall, on-site cut and fill costs $2.50 per yd³, off-site fill costs $5.00 per yd³, and off-site cut costs $3.00 per yd³. Remember, if the cut and fill are not balanced, the remaining material must be removed/brought off-site at the off-site rate. An estimate for the cut and fill volumes and the associated earthwork costs using the data presented in the example is shown below. Note that the volume calculation results (in purple) have one less column and one less row in the worksheet due to the averaging of adjacent cells. In other words, four cells in the proposed elevation worksheet define one volume cell. The total cut and fill is shown in green , and the earth costs are in blue.

An estimate of the pond volume can be computed like that used in the can-and-fill volume calculation. First, estimate a pond elevation by subtracting the proposed elevations from the pond elevation. Inside the proposed embankment structure, positive (+) values of the difference between the pond elevation and the proposed elevations indicate that water is detained in the pond (DP). The DP's negative (-) values suggest that the proposed elevation is above the pond elevation. The pond volume calculation should ignore the positive (+) values of DP outside the embankment structure. Using the data presented in the example above, the values of DP are shown in the worksheet below. Note that the proposed pond elevation is given in cell I3.

The pond volume for this example is computed in the worksheet shown below. Note that the volume is calculated in ft³ and converted to gallons (1 ft³ = 7.48 gallons). The positive (+) DP cells outside the proposed embankment are deleted from the pond volume calculation.

This website was originally developed by Charles Camp for CIVL 1112.
This site is maintained by the Department of Civil Engineering at the University of Memphis.
Your comments and questions are welcomed.