Using Spreadsheets and VBA for Teaching Civil Engineering Concepts

Essam Zaneldin
Department of Civil and Environmental Engineering, United Arab Emirates University, P.O. Box 17555, Al Ain,
United Arab Emirates, Email: [email protected]
and
Bilal El-Ariss
Department of Civil and Environmental Engineering, United Arab Emirates University, P.O. Box 17555, Al Ain,
United Arab Emirates, Email: [email protected]
ABSTRACT
Spreadsheets are becoming increasingly popular in solving
engineering related problems. Among the strong features of
spreadsheets are their instinctive cell-based structure and easy
to use capabilities. Excel, for example, is a powerful
spreadsheet with VBA robust programming capabilities that can
be a powerful tool for teaching civil engineering concepts.
Spreadsheets can do basic calculations such as cost estimates,
schedule and cost control, and markup estimation, as well as
structural calculations of reactions, stresses, strains, deflections,
and slopes. Spreadsheets can solve complex problems, create
charts and graphs, and generate useful reports. This paper
highlights the use of Excel spreadsheet and VBA in teaching
civil engineering concepts and creating useful applications. The
focus is on concepts related to construction management and
structural engineering ranging from a simple cost estimating
problem to advanced applications like the simulation using
PERT and the analysis of structural members. Several
spreadsheet were developed for time-cost tradeoff analysis,
optimum markup estimation, simulating activities with
uncertain durations, scheduling repetitive projects, schedule and
cost control, and optimization of construction operations, and
structural calculations of reactions, internal forces, stresses,
strains, deflections, and slopes. Seven illustrative examples are
presented to demonstrate the use of spreadsheets as a powerful
tool for teaching civil engineering concepts.
Keywords: Spreadsheets, Engineering Education, VBA, Civil
Engineering.

1. INTRODUCTION
Spreadsheets are among the earliest software innovations that
had a profound effect on the widespread use of personal
computers. Spreadsheets made their first appearance for
personal computers in 1979 in the form of VisiCalc, an
application designed to help with accounting tasks [1; 2]. Since
that time, the diversity of applications of the spreadsheet
program is evidenced by its continual reappearance in scholarly
journals. Spreadsheets are, therefore, among the earliest
software innovations that had a profound effect on the
widespread use of personal computers. Among the strong
features of spreadsheets are their intuitive cell-based structure
and the simple interface that is easy to use, even for first time
users [3; 4]. Underneath the structure and the interface is a host
of powerful and versatile features that can be utilized in

teaching from data entry and manipulation to a large number of

functions, charts, and word processing capabilities. This is in
addition to its visual basic for applications (VBA) powerful
programming capabilities. Newer spreadsheet versions have
also added many productivity features for Internet connectivity,
workgroup sharing, powerful programmability options, and a
number of add-in programs. With their wide use, spreadsheets
have been used as tools for developing computer models that
can be used as a tool for teaching construction management
concepts, for which ease of use, versatility, and productivity are
the main issues. Hegazy and Ersahin [3] used Excel and VBA to
develop an information system for subcontractors and
small/medium-sized contractors. Their developed spreadsheet
stores resource data for labor, equipment, crews, material,
subcontractors, and alternative methods of construction for
various tasks. In addition, a separate worksheet is designed for
each project to be used for estimating and control purposes.
Ickert and Huston [5] developed a spreadsheet that can be used
to analyze multiple solutions for engineering problems
efficiently and accurately, and to produce graphs that convey the
solution to the end users. Thiriez [6] developed several
spreadsheets as educational tools for students. One example is
to use the drawing capabilities of Excel to represent decision
trees and where window switching and macros allow the
educator to animate his presentation. Another example
presented by Thiriez [6] is the use of Excel in dynamic
programming, deterministic or stochastic, where Excel
functions facilitate the development of specialized models.
From primary to tertiary levels, the spreadsheet is gradually
increasing in its importance as a tool for teaching and learning.
In this paper, Microsoft Excel 2007 [7] is used for developing
spreadsheets that can be used for teaching civil engineering
concepts. Two areas in civil engineering were selected namely
construction management and structural engineering.
Spreadsheet applications related to these two areas were
developed including construction data management, time-cost
tradeoff (TCT) analysis, optimum markup estimation,
simulating and scheduling construction activities with uncertain
durations, scheduling linear and repetitive projects, schedule
and cost control, and optimization of construction operations, as
well as the structural calculations of reactions, internal forces,
stresses, strains, deflections, and slopes. First, the use of
spreadsheets as a tool for teaching construction management
and structural engineering concepts in general is discussed.
Seven illustrative examples are then presented to demonstrate

how spreadsheets can be used as a powerful tool for teaching

civil engineering concepts.

3. USING SPREADSHEETS IN TEACHING CIVIL

ENGINEERING CONCEPTS

2. SPREADSHEET AS A POWERFUL TEACHING TOOL

Among the strong features of spreadsheets are their intuitive
cell-based structure and the simple interface that easy to use
even for first time users. Underneath the structure and the
interface is a host of powerful and versatile features, from data
entry and manipulation to a large number of functions, charts,
and word processing capabilities. Newer spreadsheet versions
have also added many productivity features for Internet
connectivity, workgroup sharing, programmability options, and
a number of add-in programs. With their wide use, spreadsheets
have been used as tools for developing computer models in
domains such as construction, for which ease of use, versatility,
and productivity are the main issues. In this paper, a spreadsheet
program, Microsoft Excel 2007, is used to develop applications
to solve civil engineering problems. Several powerful and
infrequently used Excel features are available and can be used
to develop practical and powerful models for teaching civil
engineering concepts. These features include data lists, data
menu options, data filtering, referencing and searching lists,
basic spreadsheet functions such as Vlookup, and pivot table
reports. In Excel, a data list is a simple structure of columns and
rows that contain data. Entering data into a large spreadsheet list
may become extremely tedious and prone to error. The Dataforms menu option in Excel provides a simple way for editing
or deleting existing records in a list or adding new records.
Sorting the data helps bring similar records together for visual
inspection or other purposes such as preparing reports and
charts. Using the Data, Sort menu option, the lists data can
be arranged in an order that is chosen by sorting the records.
Filtering the data is also a useful way to view a subset of the
records that compose a list. To filter a list is to extract records
from it, based on criteria set by the user. Referencing and
searching the list is another important part of the data
management process. In realistic systems, where several lists of
data are available, a link needs to be established among them
(similar to the relational database concept). One simple and
important spreadsheet function Vlookup can be used to link
separate lists of information by making a reference to where the
original data are. Accordingly, it is possible to determine the a
cost of a certain project item, for example, by using a Vlookup
function to search the original list and determine its associated
rate per hour value. The Match, Index, and Offset are
other functions that provide further control over the data in a
list. Reporting is another essential requirement for obtaining
summary data. In Excel, the pivot table wizard provides an
automated report generator.
In addition to the features and functions of Excel, its
programming capabilities, called visual basic for applications
(VBA) are very powerful and can be used to solve complex
civil engineering problems. Excel can also be used in linear
programming, where the solver is used, and where the
spreadsheet's graphing capabilities are used both to represent
the feasible set and the objective function, and to interactively
animate the objective function or constraint movements. Excel
can also be used in simulation, where stochastic simulation may
be facilitated through the use of an add-in, and a decision
support system may thus be built from scratch.

The basic paradigm of an array of rows-and-columns in Excel

spreadsheets with automatic update and display of results has
been extended with libraries of mathematical and statistical
functions, versatile graphing and charting facilities, powerful
add-ins such as Microsoft Excels Solver, attractive and highly
functional graphical user interfaces, and the ability to write
custom code in languages such as Microsofts Visual Basic for
Applications. Spreadsheets with these powerful features and
programming capabilities can be used as an effective tool for
use in civil engineering applications. Examples include
construction data management, TCT analysis, optimum markup
estimation, simulating and scheduling construction activities
with uncertain durations, scheduling linear and repetitive
projects, schedule and cost control, optimization of construction
operations, design of bolted steel connections, and analysis and
design of different steel members such as beams, columns, base
plates, etc. Since the core of any information system is the
storage of the data and information for management operations,
database capabilities in spreadsheet programs can be used as an
effective tool to develop complex data management system for
construction information. Few basic, but infrequently used,
spreadsheet features need to be known and can be used to
develop practical and powerful models, which can be used for
teaching civil engineering concepts. In this study, several
spreadsheets were developed to be used for teaching civil
engineering concepts. In the following subsections, example
applications are presented in two areas in civil engineering: 1)
construction management, and 2) structural engineering.
Applications in Construction Management
Four examples are presented in this paper to demonstrate the
use of spreadsheets in teaching construction management
concepts. The first example illustrates the use of Excel for the
management of construction information. In Excel, a data list is
a simple structure of columns and rows that contain data.
Entering data into a large spreadsheet list may become
extremely tedious and prone to error. The Data-forms menu
option in Excel provides a simple way for editing or deleting
existing records in a list or adding new records. This option can
be used to develop a construction data management system to
manage information related to construction equipment, labor,
material, cost, methods of construction, and bills of quantities.
Sorting the data helps bring similar records together for visual
inspection or other purposes such as preparing reports and
charts. Using the Data-Sort menu option, the lists data can be
arranged in an order that is chosen by sorting the records.
Filtering the data is also a useful way to view a subset of the
records that compose a list. To filter a list is to extract records
from it, based on criteria set by the user. Referencing the list is
another important part of the data management process. Also,
Excel has several important and powerful functions. For
Example, one simple and important spreadsheet function
Vlookup can be used to link separate lists of information by
making a reference to where the original data are. For example,
consider the situation when a new list is used for estimating
purposes (Figure 1) and this list refers to the Code of the labor
being used. Accordingly, it is possible to determine the cost by

using the Vlookup function to search the original labor list

and determine its associated Rate/hr value (Figure 1).

Gates [9], on the other hand, suggested that by comparing its

own bid to the winning bid a contractor could calculate the
markup which would have been needed in order to win the
contract. Again this implies that cost is the most important
criterion:
P(winall) =

Vlookup function in Cell C4:

= vlookup (A4, Labour!A1:C6, 3, 0)
Search
value

List

Column
number

Exact
match

Figure 1: Using the Vlookup Function.

If the resource code specified in cell A4 of the estimate is
changed (e.g., L5 is used), the costs will be adjusted
automatically in cells C4 and D4 of the estimate. In addition to
the Vlookup function, the Match, Index, and
Offset functions provide further control over the data in a
list. The help system of Excel can be used to obtain information
about the syntax and use of these important functions. Reporting
is another essential requirement for obtaining summary data on
resources and operations. In Excel, the pivot table wizard
provides an automated report generator. Pivot tables can be used
to arrange projects information in the needed format,
summarize long lists in a compact format, find relationships
within lists that are hidden by all details, display data in the
form of averages, percentages, summations, etc.
The second example illustrates the use of probabilistic models
to analyze the behavior of a contractors competitors bidding for
a job in order to optimize the markup used in bidding for a job.
The probability approach has historically been the most popular
technique for the construction of bidding models. Most bidding
models based on probability theory have been derived from the
work of Friedman [8]. Friedman claimed that, in a tender, it was
possible to model each bidder's behavior as a function of the
estimated cost by means of a probability distribution. The
general approach assumes that there are a number of bidders
competing regularly against each other in the same market place
and, given a sufficient number of opportunities to tender against
known competitors, any one player can collect sufficient
information to model the relationship between its own markup
on future projects against the probability of submitting the
lowest tender. This approach also assumes that the basic prime
cost is similar for all competitors, and by comparing the
competitors' tender figures for past projects with its own
estimate of prime cost, a contractor can therefore develop a
probability distribution for each of its competitors showing the
likelihood of winning future tenders with different markups.
Using Friedmans model, the probability of winning against a
range of contractors P(winall) is the product of the chances of
beating them each individually, and the maximum expected
profit (EP) can be calculated as follows:
Expected profit (EP) = Profit x Pwinall
Where: P(winall) = Pwin1 x Pwin2 x x Pwinn
and n: is the number of competitors.

1
[(1-P(Win1))/ P(Win1)]+...+ [(1-P(Winn))/P(Winn)]+1

To illustrate this concept to, the two approaches were modeled

using Excel and a spreadsheet was developed to calculate the
optimum markups using both Friedman and Gates approaches.
When a contractor bids for a new job, it is assumed that he/she
knows other competitors bidding for the same job. To estimate
the optimum markup for the contractor, the user will only enter
the number of previous bids for each competitor bidding for the
job, the competitors bid values for these bids, and the
contractors estimated cost. Excel then counts the number of
bids for each competitor and the bid to cost ratio for each bid
and for and each competitor. It also calculates the mean and
standard deviation of the bid/cost for previous bids of each
competitor. The mean is calculated based on the summation of
the bid price to bid cost ratios for all available bids for each
competitor divided by the number of available bids. Excel will
also calculate the probability to win each competitor separately
and the probability to win all competitors (Figure 2), using
Friedman and Gates models. For both models, the maximum
expected profit (max. EP) is found using the Max function.
As shown in Figure 2, the maximum expected profit using
Friedmans model is found to be $2,389.47 (cell I27) and using
Gates model is $23,361.82 (cell J27). Excel will then find the
markup corresponding to the maximum expected profit for both
Friedman and Gates models using the Vlookup function. For
example, the Vlookup function used to calculate the markup
corresponding to the maximum expected profit for Friedmans
model (Cell I28) = Vlookup(I27, I15:K25, 3, 0). For the
example in hand, the optimum markups using Friedman and
Gates models were found to be 3% and 7%, respectively.

Figure 2: Optimum Markup Estimation Sheet.

The third example illustrates the use of spreadsheets for
scheduling construction activities with uncertain durations using
the Program Evaluation and Review Technique (PERT). Using
this technique, each activity has three durations; the optimistic
time (a), the pessimistic time (b), and the most likely time (m).
PERT uses a weighted average of the three times to find the
overall project duration. This average time is called the

expected time (te), which is equal to (a+4m+b)/6. To determine

the probability of a project to be completed earlier or later than
expected, the variance (v) of each activity along the critical path
is calculated as follows: v = (b - a)2/36. Sine the duration of
each activity is uncertain; the time of occurrence of each
activity is also subject to uncertainty. The measure of
uncertainty of the final event in a PERT diagram is the standard
deviation of the expected time (TE). The TE for the last event is
the square root of the sum of the variance of all activities along
the critical path. Also the expected time of the last event in the
project is denoted as TE. To determine the probability of
completing a project earlier or later than expected, the deviation
(z) needs to be calculated; where z = (TS TE)/TE and TS is the
scheduled time to finish the project. A spreadsheet was
developed to illustrate the PERT concept using MonteCarlo
simulation. For the five-activity project shown in Figure 3 (b),
the user needs to enter two times only (a and b) for each activity
and their corresponding variances (v = 2) will then be
calculated automatically.

(a) Input of Activities Durations.

(b) Activities Random Durations.

Figure 3: Activities Durations Using PERT.
Several iterations are used by selecting random numbers
between the optimistic and pessimistic times using the Excel
rand() function. For the purpose of this example, four
iterations are used. Each path in the network is then identified
and, following the logic of the network, the critical path is
automatically calculated by identifying the longest path in the
project. The expected time of the last event in the project (TE) is
calculated and the standard deviation (TE) for critical activities
in each path for each of the four iterations is also calculated.

Excel then calculates the average expected time (TE) and the
average standard deviation (TE). For a scheduled time (TS) = 47
days; for example, the probability (Prob.) to complete the
project in this duration using iteration 2 is calculated using the
normdist function and found to be 68.74%. For example,
using iteration 2, the number of days that corresponds to
completing the project with a probability of 68.74% is
calculated using the norminv function and found to be 47
days.
The last example is a VBA application. In this example, an
Excel spreadsheet was developed and a VBA macro code was
written for time-cost trade-of analysis. The sheet provides
information about project activities with their logical
relationships and their corresponding durations, resource
requirements, methods of construction, and costs associated
with each method of construction. The VBA macro was
developed to find the optimum method of construction for all
project activities. The basic premise of the model is to allocate
optional construction methods for each activity, varying from
cheap and lengthy to expensive and short. Using the model,
users will be able to select different construction methods and
find the corresponding total project cost. The proposed model,
as such, is usable not only at the planning stage but also during
construction. The VBA macro then automatically generates a
Microsoft Project file for the project using the data of the
spreadsheet. The code was written to generate a Microsoft
Project file for the project example in hand. The code copies
activities data such as activities names, durations, and start
times, construction methods, and activities actual costs from
the spreadsheet and then transfers the data to a newly generated
Microsoft Project file for the project. A complete schedule for
the project using the selected data is finally automatically
generated.
Applications in Structural Engineering
Three examples are presented in this paper to demonstrate the
use of spreadsheets in teaching structural engineering concepts.
The first example illustrates the use of Excel to enhance the
concept and efficiency of moment distribution method when
spreadsheet is used. A continuous beam subjected to uniform
distributed load was used (Figure 4) to illustrate the concept and
to show that spreadsheets reduce the number of random
arithmetic errors and time in doing the tedious momentdistribution computations. Moment distribution method is a
classical method that has been included in every civil
engineering program. It is one of the first numerical methods for
structural analysis. The procedure of the method for prismatic
members can be summarized as follows: 1) compute the relative
stiffness k = I/L for each member that is either connected or
supported at both ends. This excludes overhang members; 2)
compute the distribution factor (DF) for each member
connected to a joint or support; 3) compute fixed end moments
(FEM) for members with intermediate loads; 4) compute end
moments for overhanging members; 5) pre-determine a
tolerance limit; 6) balance the moments at each joint and
distribute the balancing moment according to DF of each
member; 7) carry over the balanced moment to the adjacent end
of each member; 8) repeat steps 6 and 7 until the unbalanced
moments are within the tolerance limit; and 9) sum the moments
in each column for each member end.

Figure 4: Moment Distribution Calculation Sheet.

The second example illustrates the construction of the shear
force and bending moment diagrams for any structural member
using a spreadsheet. Figure 5 shows the shear force and bending
moment diagrams for the continuous beam used in the first
example (the moment distribution example). Beams are long
and slender structural elements that support transverse as well
as axial loads. Figure 5(a) shows the bending moment and shear
force values at different span distances while Figure 5(b) shows
the bending moment and shear force diagrams.
These diagrams are important in structural engineering as they
show not only the variation of the internal forces along the
length of the structural member but also the most critical values
that the structural engineer needs to design the relevant
structural member. They are the most popular techniques to
show the changes in the internal forces of a structural member
along its length. These diagrams are obtained using the
relationships between the external loads on the member and the
internal forces generated in the member and the free body
diagrams (FBDs) to the left or right of sections that cut the
member transversely and show all the forces on those FBDs.
Then equations of equilibrium are applied to ensure equilibrium
is satisfied. The shearing forces and the ending moments
obtained are basically the internal stress distribution resultants.
Typical examples of beams include bridges, roof structural
elements, floor beams, machine elements such as levers, cranks,
manipulator arms, etc.
The third example illustrates the use of Excel in calculating the
internal stresses and deflection in a beam. Having calculated the
design bending moment and shear force for the beam above, all
that remains to be done now is to assess the size and strength of
the beam. The process involved in a selection depends on
whether the construction material behaves elastically or
inlastically. For the demonstration in this paper, elastic behavior
has been presented. The calculation of the internal stresses,
whose resultants are the shearing forces and bending moments
produced by the external loads, and the deflections in a beam of
know section and geometry uses the section modulus (S) and the
cross section area (A) and its moment of inertia (I).

(a) Bending Moment and Shear Force Values.

Bending Moment Diagram (BMD)

Span (ft)

Shear Force Diagram (SFD)

Span (ft)

(b) Bending Moment and Shear Force Diagram.

Figure 5: Bending Moment and Shear Force.
As shown in Figure 6, the spreadsheet reduces the arithmetic
errors of computing the S, A, and I, the frustrations, and the time
doing the tedious stress calculations. The procedure for

computing stress briefly involves: 1) computing the maximum

moment and determine the required section modulus (S) using
the flexural formula, S = M/fb; 2) selecting the most economical
(lightest) structural steel shape using the AISC Allowable Stress
Design Method; 3) checking the beam for shear by comparing
the actual shearing stress and the allowable shearing stress; and
4) computing the maximum deflection due to the design load
and compare it to the allowable deflection.

interface is a host of powerful and versatile features that can be

utilized in teaching, from data entry and manipulation to a large
number of powerful functions. In this paper, several spreadsheet
applications are developed for use in teaching civil engineering
concepts. For the purpose of illustration, seven of these
spreadsheet applications are then presented. The examples
demonstrate the simple and powerful features of spreadsheets
and its capability in solving complex construction management
and structural engineering problems. These examples can be
used by civil engineering students as templates for solving
similar problems or for developing models to solve other
problems. In addition, the developed spreadsheets, as such,
represent a transparent methodology that allows for quick whatif analysis in cost estimation, optimum markup estimation, risk
analysis, and structural members optimization. Finally, the
availability of Excel and its VBA programming capabilities
makes it a powerful tool in developing applications for teaching
civil engineering concepts and concepts related to other
engineering disciplines.
5. REFERENCES
[1] Baker, J. and Sugden, S., 2003. Spreadsheets in education
the first 25 years. SIE journal, 1(1), 18-43.
[2] Zaneldin, E., and Ashur, S., 2008. Using Spreadsheets as a
Tool in Teaching Construction Management Concepts and
Applications. The 2008 American Society of Engineering
Education (ASEE) Annual Conference. June 22-25, 2008,
Pittsburgh, PA, USA.
[3] Hegazy, T. and Ersahin, T., 2001. Simplified spreadsheet
solutions. I: Subcontractor information system. Journal of
Construction Engineering and Management. ASCE, 127(6),
461468.
[4] Hegazy, T., and Ersahin, T., 2001. Simplified spreadsheet
solutions. II: Overall schedule optimization. Journal of
Construction Engineering and Management. ASCE, 127(6),
469475.
[5] Ickert, R., and Hutson, A., 2005. Hydraulic modeling of
transmission systems using spreadsheets. The 2005 Pipeline
Division Specialty Conference. August 2124, 2005, Houston,
Texas, USA.
[6] Thiriez, H., 2001. Improved OR education through the use
of spreadsheet models. European Journal of Operational
Research, 135 (3), 461-476.

Figure 6: Section Properties and Stress Calculations.

4. CONCLUSIONS
Spreadsheets are becoming increasingly popular for use as a
powerful tool and are considered among the earliest software
innovations that had a profound effect on the widespread use of
personal computers. Among the strong features of spreadsheets
are their intuitive cell-based structure and the simple interface
that is easy in education. This is in addition to its powerful
programming capabilities. Underneath the structure and the

[7] Microsoft Excel Reference Guide 2007. Microsoft Corp.,

Redmond, Wash.
[8] Friedman, L., 1956. Competitive-bidding
Operations Research, 4, 104112.

strategy.

[9] Gates, M., 1967. Bidding strategies and probabilities.

Journal of Construction Div. ASCE, 93(1), 75107.