\[ \begin{align}\begin{aligned}\newcommand\blank{~\underline{\hspace{1.2cm}}~}\\% Bold symbols (vectors) \newcommand\bs[1]{\mathbf{#1}}\\% Differential \newcommand\dd[2][]{\mathrm{d}^{#1}{#2}} % use as \dd, \dd{x}, or \dd[2]{x}\\% Poor man's siunitx \newcommand\unit[1]{\mathrm{#1}} \newcommand\num[1]{#1} \newcommand\qty[2]{#1~\unit{#2}}\\\newcommand\per{/} \newcommand\squared{{}^2} \newcommand\cubed{{}^3} % % Scale \newcommand\milli{\unit{m}} \newcommand\centi{\unit{c}} \newcommand\kilo{\unit{k}} \newcommand\mega{\unit{M}} % % Percent \newcommand\percent{\unit{{\kern-4mu}\%}} % % Angle \newcommand\radian{\unit{rad}} \newcommand\degree{\unit{{\kern-4mu}^\circ}} % % Time \newcommand\second{\unit{s}} \newcommand\s{\second} \newcommand\minute{\unit{min}} \newcommand\hour{\unit{h}} % % Distance \newcommand\meter{\unit{m}} \newcommand\m{\meter} \newcommand\inch{\unit{in}} \newcommand\foot{\unit{ft}} % % Force \newcommand\newton{\unit{N}} \newcommand\kip{\unit{kip}} % kilopound in "freedom" units - edit made by Sri % % Mass \newcommand\gram{\unit{g}} \newcommand\g{\gram} \newcommand\kilogram{\unit{kg}} \newcommand\kg{\kilogram} \newcommand\grain{\unit{grain}} \newcommand\ounce{\unit{oz}} % % Temperature \newcommand\kelvin{\unit{K}} \newcommand\K{\kelvin} \newcommand\celsius{\unit{{}^\circ C}} \newcommand\C{\celsius} \newcommand\fahrenheit{\unit{{}^\circ F}} \newcommand\F{\fahrenheit} % % Area \newcommand\sqft{\unit{sq\,\foot}} % square foot % % Volume \newcommand\liter{\unit{L}} \newcommand\gallon{\unit{gal}} % % Frequency \newcommand\hertz{\unit{Hz}} \newcommand\rpm{\unit{rpm}} % % Voltage \newcommand\volt{\unit{V}} \newcommand\V{\volt} \newcommand\millivolt{\milli\volt} \newcommand\mV{\milli\volt} \newcommand\kilovolt{\kilo\volt} \newcommand\kV{\kilo\volt} % % Current \newcommand\ampere{\unit{A}} \newcommand\A{\ampere} \newcommand\milliampereA{\milli\ampere} \newcommand\mA{\milli\ampere} \newcommand\kiloampereA{\kilo\ampere} \newcommand\kA{\kilo\ampere} % % Resistance \newcommand\ohm{\Omega} \newcommand\milliohm{\milli\ohm} \newcommand\kiloohm{\kilo\ohm} % correct SI spelling \newcommand\kilohm{\kilo\ohm} % "American" spelling used in siunitx \newcommand\megaohm{\mega\ohm} % correct SI spelling \newcommand\megohm{\mega\ohm} % "American" spelling used in siunitx % % Capacitance \newcommand\farad{\unit{F}} \newcommand\F{\farad} \newcommand\microfarad{\micro\farad} \newcommand\muF{\micro\farad} % % Inductance \newcommand\henry{\unit{H}} \newcommand\H{\henry} \newcommand\millihenry{\milli\henry} \newcommand\mH{\milli\henry} % % Power \newcommand\watt{\unit{W}} \newcommand\W{\watt} \newcommand\milliwatt{\milli\watt} \newcommand\mW{\milli\watt} \newcommand\kilowatt{\kilo\watt} \newcommand\kW{\kilo\watt} % % Energy \newcommand\joule{\unit{J}} \newcommand\J{\joule} % % Composite units % % Torque \newcommand\ozin{\unit{\ounce}\,\unit{in}} \newcommand\newtonmeter{\unit{\newton\,\meter}} % % Pressure \newcommand\psf{\unit{psf}} % pounds per square foot \newcommand\pcf{\unit{pcf}} % pounds per cubic foot \newcommand\pascal{\unit{Pa}} \newcommand\Pa{\pascal} \newcommand\ksi{\unit{ksi}} % kilopound per square inch \newcommand\bar{\unit{bar}} \end{aligned}\end{align} \]

Jan 19, 2026 | 535 words | 5 min read

9.2.2. Task 2

Learning Objectives

In this task, you will practice using MS Excel for engineering calculations. You will gain proficiency in using cell referencing for calculations, organizing data effectively with descriptive variable names and appropriate units, and interpreting results to answer critical design and analysis questions.

Important

When organizing your work in MS Excel:

• Use cell referencing to perform the calculations

• Use descriptive variable names

• Include column and row headings with units

• Organize and format your work so it is easy to follow

Part A: Storage Tank Design

Introduction

Fig. 9.5 Cylinder for Part A: Storage Tank Design.

A team of engineers is designing a storage tank in a cylindrical shape. The total available surface area of the cylinder (\(A\)) is \(\qty{2000}{\sqft}\). The team’s objective is to make this cylinder hold the maximum possible volume. The dimensions for the cylinder are shown in Fig. 9.5.

Equations used:

• Volume: \(V = \pi R^2 H\)

• Surface area: \(A = 2 \pi R^2 + 2 \pi R H = \qty{2000}{\foot\squared}\)

Task Instructions

1. Open the answer sheet ex1_team_2a_teamnumber.xlsx. Save it with your team number replacing teamnumber in the file name.

2. On the Input Section of the sheet, you will see possible radii of the storage tank. Under the Calculation Section, calculate the possible height \(H\) and volume \(V\) for each radius \(R\).

   1. Use cell referencing to perform the calculations.

   2. Organize and format your work, so it is easy to follow. Be sure to include units.

3. Copy the following questions into the Output Section of the spreadsheet and then add your answers.

   1. Which combination of radius \(R\) and height \(H\) results in the largest volume \(V\)?

   2. Are the dimensions from the previous question acceptable considering maximizing volume and transportation to a new site? Why or why not?

4. Save the ex1_team_2a_teamnumber.xlsx file as ex1_team_2a_teamnumber_values.pdf displaying the values and ex1_team_2a_teamnumber_formulas.pdf displaying the formula.

Part B: Soil Stress Calculations

Introduction

Karl Terzaghi was a Czechoslovakian geotechnical engineer who lived between \num{1883} and \num{1963}. He is known today as the grandfather of soil mechanics. One of his many significant contributions includes the calculation of vertical stress in soil using the equation

(9.1)\[\sigma = \gamma * h\]

where, \(\sigma\) is the vertical stress (\(\psf\)), \(\gamma\) is the soil unit weight (\(\pcf\)), and \(h\) is the depth below soil surface (\(\foot\)).

On a particular site, engineers find three types of soil:

• Soil A: \(\gamma = \qty{115}{\pcf}\)

• Soil B: \(\gamma = \qty{124}{\pcf}\)

• Soil C: \(\gamma = \qty{140}{\pcf}\)

Task Instructions

1. Open the answer sheet ex1_team_2b_teamnumber.xlsx. Save it with your team number replacing teamnumber in the file name.

2. Calculate the vertical stress at \(5\) foot intervals from \(\qty{5}{\foot}\) to \(\qty{40}{\foot}\) for each of the soil types listed above.

3. Copy the following questions into the Output Section of the spreadsheet and then add your answers.

   1. A stress-measuring device is buried \(\qty{40}{\foot}\) under the surface. The maximum stress this device can stand is \(\qty{5,000}{\psf}\). In which type(s) of soil can this device be buried?

   2. What percentage of stress values calculated for soil C are above \(\qty{5000}{\psf}\)?

4. Save the ex1_team_2b_teamnumber.xlsx file as ex1_team_2b_teamnumber_values.pdf displaying the values and ex1_team_2b_teamnumber_formulas.pdf displaying the formula.

Deliverables

• Each team member is expected to contribute to every team task.

• You and your team members will be held responsible for all material.

• All collaborators should be clearly listed, and their contributions are properly referenced.

• One team member should upload all deliverables to Gradescope as a single submission.

• Be sure to assign all team members to the submission.

• For help with submitting team assignments, click here.

Table 9.3 Deliverables

Deliverables	Description
ex1_team_2a_teamnumber_values.pdf	PDF of your MS Excel document with values showing.
ex1_team_2a_teamnumber_formulas.pdf	PDF of your MS Excel document with formulas showing.
ex1_team_2b_teamnumber_values.pdf	PDF of your MS Excel document with values showing.
ex1_team_2b_teamnumber_formulas.pdf	PDF of your MS Excel document with formulas showing.