Optimum Spread Footing Placement Using Excel

Use Excel to determine the optimum depth at which to place a square un-reinforced concrete footing. The size of the footing is b x b and it is b/2 thick such that minimal steel reinforcing is required. The concrete weighs 150 lbf/ft³, costs $2.00 per ft³ in place, and has an allowed compressive stress of 1.1 kip/in². A square concrete column c x c will be used to reach the footing if you choose to sink the footing below the ground. Please round the column size c to an even 2 inches. You must include the column load, the weight of the footing, the weight of the column, and the weight of the backfill in your stress calculations. The soil weighs 120 lbf/ft³. A soils report from Coyle, Thompson and Bartoskowitz indicates that the soil has a safe bearing stress of 3 kips/ft² on the surface, and that its strength increases by 0.5 kip/ft² for every foot below the surface – i.e. at 20 feet below the surface the strength of the soil would be given by:

Total bearing capacity = 3 kip/ft² + 0.5 kip/ft³ * 20 ft = 13 kip/ft²

The cost to excavate the hole increases with the square of the excavation depth due to shoring requirements, according to the formula:

Digging costs = $0.60/ft³ + $0.02/ft³ per foot² * (d feet)²

where d = the depth to the bottom of the footing. For example, the unit cost to dig a hole to a depth of 20 feet would cost you:

Unit digging cost = $0.60/ft³ + $0.02/ft³ per foot² * (20 ft)² = $8.60 per cubic foot.

There is also a cost associated with replacing the soil back into the hole on top of the footing and tamping it down around the column, after the footing and column have been poured. This cost is:

Cost to fill and tamp = $0.20/ft³

There is no penalty for depth when backfilling and tamping.

For a column load of P = 600 kips, determine the optimum depth at which the footing should be placed in order to minimize the cost of the 1000 footings which will be used. No formal engineering report is required for this assignment, nor any plots.

Hint: On Excel, if you look under “Tools” you will find a “Solver” which will let you optimize functions.

To get the cost of the footing at any depth d:

1. Set the footing depth d in your spreadsheet to any single desired value

2. Click on: Tools, Solver

3. “Set target cell”: (click on cell where footing “total cost” resides)

4. “Equal to”: click on minimize button

5. “By changing cells”: (click on cell where footing size “b” resides)

6. “Subject to the constraints”: (click on the cell where the actual soil stress is calculated) = (then click on cell where allowed soil stress has been calculated)

7. Solve.

This would let you check your hand solution.

Note that here you are actually lying. You don’t really want him to minimize anything. It’s just a trick to force him to set the actual stress equal to the allowed stress, and solve for the required value of b. You could just as easily put a “2” in cell A1, and tell him to minimize that.

To get the minimum possible cost at ANY depth:

1. Use the exact same steps as listed above, except let him tinker with both the footing depth “d” and the base dimension “b.” Here you set: “By changing cells”: (cell where footing depth “d” resides):(cell where footing size “b” resides), i.e. cells “depth” and “size”.

Here you are letting him tinker with both the depth and the footing size, and in this case he will be able to minimize the total cost for you.

Note that the hand solution you worked out for the previous homework will suffice, and need not be handed in again.  No formal engineering report is required, but you must include a clear and concise solution for the problem. Your solution should include the optimum depth you find. Submit a hard copy only. Do not email this solution.