CIVL 3010 – CIVIL ENGINEERING ANALYSIS

Fall 2024 Homework 1: Introduction to Excel

A macro-enabled Excel workbook (.xlsm file) must be submitted via Canvas for all
homework assignments in this course. This workbook must contain a separate sheet for each problem
labeled “Problem_1,” “Problem_2,” etc.

The purpose of this homework is to demonstrate some of the functionalities of Excel, as well
as to help students who have never worked with Excel before to get up to speed with the rest of the
class. For this reason, a step-by-step solution to one of the problems is provided at the end of this
document.

1) Assume you recently bought a new car for $70,000 at an annual percentage rate (APR) of 6%
with no down payment. You want to pay the loan within 8 years. Create an amortization table
that shows your monthly payment, how much was the interest, how much principal was paid,
and the amount you still owe. Instructions on how to solve this problem can be found at the
end of this document.

2) Using the equation of a straight line (y = ax + b), generate data for 5 straight lines where the
slope “a” assumes the values of 0.1, 0.2, 0.3, 0.4, and 0.5, and the intercept “b” assumes the
value of 2 for all the 5 cases. Calculate y starting from x = 0 to x = 10 with a step of 1 for all
5 lines. Then, create a professional looking graph with all the generated data in it. Your graph
should look somewhat like the one shown below.

1
3) Explain what the dollar signs do for each term in the expression “=B$2*$A4+$B$1”. You
can create a text box inside an Excel tab by clicking: Insert -> Text -> Text Box, and then
draw the text box inside the spreadsheet.

4) Hydrographs are used by civil engineers to model rainfall-runoff processes. A simple

analytical model for describing a stream hydrograph after a rainfall event (i.e., increase in
stream flow as a function of time) can be described by the equation:

𝑡 𝑛 𝑛∙𝑡
𝑄(𝑡) = 𝑎 ∙ ( ) ∙ exp (𝑛 − ( ))
𝑏 𝑏

where a and b are known constants (model parameters), t is time in hours and Q is the
estimated flow in ft³/hr.

Use Excel to implement the above equation and plot Q(t) for t ranging from 0 to 20 hours
with a step of 0.5 hrs. Adopting n = 1.2 and b = 3.1, generate flow data and professionally
plot four hydrographs with: a = 200,000; a = 300,000; a = 500,000; and a = 600,000. What
happens when we vary the constant a?

5) Repeat the process from Problem 4, adopting n = 1, a = 300,000, and b = 1, 2, 4, 6. What

happens when we vary the constant b? You can make a copy from the previous tab and make
any necessary adjustments to quickly answer this problem.

6) A survey of CIVIL 3010 students was conducted to determine the experience/proficiency in

some skills. They were asked to self-assess their experience/proficiency using the following
scale:

1 – No experience/proficiency
2 – Minimal experience/proficiency
3 – Some experience/proficiency
4 – Good experience/proficiency
5 – Excellent experience/proficiency

The number of responses (out of a total of 50 students) for each skill are shown below:

2
 Skill/Rating 1 2 3 4 5
Excel Spreadsheets 19 12 18 1 0
Programming (any language) 14 2 14 11 9
Excel Visual Basic Programming 5 12 18 15 0
Excel Solver 15 17 2 2 14
Excel “what-if” analysis 2 12 12 7 17
Statistics 14 18 1 7 10
Linear Regression 9 5 15 6 15
Non-linear regression 7 14 12 8 9
Matrices 17 8 8 17 0

Calculate the average response for each skill and sort the skills/responses in order of
decreasing average scores.

7) Solve Problem 6 using Excel functions to make the spreadsheet automatically sort each skill
in decreasing order according to their respective average score.

3
 Problem 1 Solution

By completing this step-by-step solution, you will create a template that can be used to solve
Problem 1 for any input values. For this tutorial, assume a purchase of $25,000 at an annual rate
(APR) of 6% with no down payment. The loan is going to be paid in 5 years.

We start by setting up the inputs in the spreadsheet according to the figure below. You can
format the cells by selecting them and pressing Ctrl + 1.

After setting the inputs, we are going to calculate the periodic interest rate. Since the APR is
6%; the periodic interest rate is (6%/12), or 0.50%. The loan term is 5 years; hence the loan will be
paid in 12*5 = 60 payments. Then, we calculate the monthly payment amount if the formula below:

𝐿𝑜𝑎𝑛𝑎𝑚𝑜𝑢𝑛𝑡 ∙ 𝑖𝑛𝑡𝑒𝑟𝑒𝑠𝑡𝑝𝑒𝑟𝑖𝑜𝑑𝑖𝑐
𝑃𝑎𝑦𝑚𝑒𝑛𝑡𝑚𝑜𝑛𝑡ℎ𝑙𝑦 = −𝑝𝑎𝑦𝑚𝑒𝑛𝑡𝑠
1 − (1 + 𝑖𝑛𝑡𝑒𝑟𝑒𝑠𝑡𝑝𝑒𝑟𝑖𝑜𝑑𝑖𝑐 )

25,000 ∙ 0.005
𝑃𝑎𝑦𝑚𝑒𝑛𝑡𝑚𝑜𝑛𝑡ℎ𝑙𝑦 = = $483.32
1 − (1 + 0.005)−60

Your Excel spreadsheet should look like the left side of the figure below. You can also display
the formulas of all cells at once (shown at the right side of the figure below) by using the shortcut
Ctrl + Shift + Apostrophe.

Now let’s set up the rest of the spreadsheet that is going to calculate the monthly payments.
4
Starting from cell D2 and writing on the columns, make the following header: “Payment”, “Principal
before payment”, “Interest”, “Paid on principal”, “Principal after payment”. You can select the
“Wrap text” option inside “Home” ribbon to automatically size the cells to fit all the text inserted
into them (marked in red). Your file should look like the figure below:

Now set up the spreadsheet to calculate the payment information for every month. This
should calculate the interest for each month based on the principal, the amount of money paid towards
the principal after subtracting the interest amount and finally the principal amount after the payment.

The “Payment” column (D) should be populated with numbers from 1 to 60, as this column
represents the number of months. The cell E3 should pull the value from the Loan amount (cell B3).
The “Interest” should be calculated from the current amount before payment multiplied by the
periodic interest rate. The “Paid on principal” is calculated by subtracting the payment amount by
the interest rate of the month. The “Principal after payment” is then calculated as the “Principal
before payment” minus the “Paid on principal”. Finally, the second row of “Principal before
payment” (cell E4) pulls the value from the “Principal after payment” from the previous month, in

5
this case, from cell H3. The formulas of your spreadsheet should look like this:

The programming of the spreadsheet is complete, and all that is left for us to do is to drag the
formulas down until we complete all the calculations. To do this, place your mouse at the bottom
right corner of the cell you want to drag its formula (the mouse pointer will turn into a black plus
sign), and then click and drag. Repeat this process for each column. The end of the table should look
like the figure below. Don’t forget to use the appropriate inputs in your homework submission
and to carry the calculations until the end (Principal after payment = 0).