Ihab Saad – Cash flow Example
AI: Summary ©
The speaker explains how a construction project will be solved and discusses the pricing and construction process. They plan to use a table and bar chart to calculate costs and extend the bar chart to calculate totals. They also explain how to use the graph to calculate the total spend and use it to develop owner based on price. They discuss how to use the graph to show the differences between cumulative expenditure and cumulative billing and draw a graph to show the difference between cumulative expenditure and cumulative billing. They also discuss how to develop a graph to show the difference between cumulative expenditure and cumulative billing.
AI: Summary ©
Music. Hello and welcome to a solved example on construction
scheduling, on cash flow projections. Some of you have had
some questions about this problem, so let me go ahead and explain how
it should be solved. It's quite simple. So what we have here
initially is our network. It shows the durations of the activities
with lags and overlaps and so on and so forth. And then we are
given some information about each activity. So we are given this
table showing, first of all that we have a markup of 20%
we have a retainage of 10%
and we have a 10% advance payment the review period.
The review period is one month, and cost is distributed uniformly
over the activity duration. All costs are assumed to be spent
following the early days. What does each one of these lines mean?
First of all, we have to understand how the pricing is
being done, and you may have learned about that in your
estimating class. But basically, we first calculate our costs, we
estimate our costs, which are going to include direct costs and
indirect costs as well. And that's going to be how much is going to
cost us to build the project or to do the activities for the project.
And then we add something to this cost, which is what we call the
markup. The markup is the difference between the price that
you're going to be charging the client and the cost your actual
cost, or your estimated cost for the activity. So here we have the
markup is 20% the markup components include site overheads,
head office overheads, cost of finance permits, insurance
surety, premiums, risk contingency, and last, but not
least, your profit. So all of that forms 20% that's added to your
cost to get your price. So what we have here on this slide, we have
the price for the different activities. Therefore, by dividing
that by 1.2 or multiplying by point eight, we can get the cost
for each activity. And now we have also, here at the top, we have
durations for the different activities.
And this duration, we are going to assume that it is in months.
Therefore,
in order to get the duration of the activity per month, we're
going to divide the total cost by the cost per month. So here, for
example, is a very simple example on what we did here,
this cost, which is 80%
of the price, and then we divided the cost by the duration to get
the cost per month, we already have the duration in the table.
Therefore, what we have to start with here is we have to solve this
network. And all we need about this network, we don't really need
to solve the whole network. We need to just solve for the forward
pass of this network. So let's start doing that. And here's my
color, although it's Kentucky's out of the NCAA, but we're still
going to select Kentucky blue. So we start with zero,
with three. So here we have three. We're going to transfer three plus
four is seven. Here we have finished to finish. So we're going
to take the three plus five, which is eight,
minus four is four,
and then here we're going to have seven plus two is nine.
Plus two is 11.
Here we have three plus three is six.
Plus six is 12,
and then here we have eight
plus three is 1111.
Plus one is 12.
Here we have 11 and 12. We're going to take the 12
plus two is 1412.
And 14 we're going to take the 14.
Plus 14 is 18.
So what we're saying here is that the project is going to take 18
months to be complete. We assume that all the activities are going
to be done as early as possible. So that's that was one of the
assumptions here. All costs are assumed to be spent following the
early days. So I'm not interested in the backward pass. I'm not
interested in the critical activities, at least for the time
being. So all I need to do is just develop a table. First of all,
let's try to fill this table so
this is going to be equal to
this number times point A.
Eight. And
same thing
here. We're going to copy this formula, so I'm going to copy this
to the other
cells, and then I'm going to calculate the cost per month. I'm
going to divide this number by the duration of the activity. So I
have done it here. Let's copy these numbers from here.
So basically, what we're saying is that this is equal to this
divided by this, and
then we're going to copy this formula to the other cells. As you
can see, we're automating our calculations. We're doing it very
quickly. So basically, that's the cost distribution per month. So
activity A for four months is gonna spend $640
a month,
activity, b3, 20 and so on and so forth. What we're gonna do next,
we're gonna draw a table, or we're gonna draw, actually, a bar chart,
so
something like that.
And we're gonna start, we can actually
extend this line
to have 18 cells.
Okay? And then we're going to start plotting our activities. So
activity A is going to start on zero and end on three, so it has a
duration of three. Three cells. 123,
and I'm gonna highlight that
like this,
activity B is going to start after activity eight, from three to
seven. So 1234,
that's activity B,
C is going to be from four to eight. So activity c is going to
start here, four to eight.
Activity D, from nine to 11. So here's d9,
then
11.
Actually it's just two. So from nine to 11.
Activity e from six to 12.
Let's number these columns. So here we have 123,
or actually, let's start with 0012,
and We can copy the
I can copy these numbers,
45678, I
see what I'm saying. I'm just drawing a very simple bar chart
based on the calculations that we have here at the top.
I
so activity
e is going to be from six to 12. I
123456,
activity.
F is going to be so this is going to be
a
B,
C,
D,
E,
F is from eight to 11 and.
That's f,
g is going to be 1112,
to 14.
G,
12 to 14.
H, 11 to 12. I,
and then I 14 to 18,
and that's I, I high.
Okay,
so here's my
bar chart,
then what I'm going to do is I'm going to draw a table underneath
this bar chart,
and I'm going to plot in the numbers for the different
activities.
So I'm going to draw a table like that.
Okay, so activity A for the first three months, 640 so here's A
I'm gonna copy these to extend that. D, I
have
and activity, a 646
40
and 640
you can see now it aligns with the start and finish of the FTB
activity. B3, 20s. So from here we're going to have 320 and
C is going to start from here, and it's going to be 160s and
D is going to start from here,
and it's going to be
560s,
e starting here,
and 320
I'm gonna copy that
To the end
of E activity
F, also 320 and
g3, 60, i,
h4,
80,
and then I,
160,
I, these numbers can be in the 1000s, by the way. So just to have
large numbers,
the next step is to calculate the totals. So total expenditure,
I'm just going to add the numbers. So this is going to be the sum of
the column above it like that, and
I'm going to copy that across.
So now it's showing me on each day how much is spent. As you can see,
we can actually put.
Lock these into a graph to show our resource histogram.
So insert
a graph like that
here. It shows basically my expenditure for each month, and it
shows that months 10 and 11 are going to have the largest
expenditures, and so on.
That's based on these numbers.
The next step is to calculate the cumulative number, cumulative
expenditure. So by the end of the first month, I spent 640
during the second month, I spent 640 so by the end of the second
month I spent a total of 640 plus 640 so that's 1280 so I'm going to
do that math here. I'm going to say, in this cell, this is equal
to this plus
this.
Okay, and here, this is going to be equal to only this.
Now for this third cell, that's going to be equal to this plus
this,
and I'm going to copy that to the rest of the cells.
So that basically shows me again. If I want to draw a graph of that,
I'm
going to insert a graph this time. I'm going to make it a line.
That's my S curve for the expenditure. It shows the
expenditure growing with time, the total expenditure growing with
time.
See how simple it is to do it in Excel. It's very simple. I can
increase the size of this chart by the way I want to solve like that
next.
So basically, what I've done so far, when I add these costs here,
that gave me 9680
which is the number that I have here, 9680
so this is my total expenditure. This is my total cash out.
Now the review period is one month, which means I'm going to
submit now I'm going to put the Billings. Billings means I'm going
to bill based on the price. So I can have another column here, or a
few columns. I'm going to add three or four columns, and
I'm going to call this one billing I'm
so what is bidding? You're gonna bill the client for the price and
not for the cost. So
we're talking about
monthly billing. So in this case, the monthly billing is not going
to be so the bidding is going to be per month for activity is going
to be the total price.
This is going to be equal to total price divided by the duration.
And let's copy that to all the other ones.
So that's the price per month for each activity, which is basically
the total price divided by the duration. So at the end of the
first month, I'm gonna build the client for $800
okay, so the bidding is going to be here, 800
and
so on. So what? What I'm going to do, I'm going to multiply these
numbers times 1.2 I'm
or actually I'm going to divide it. Instead of multiplying by 1.2
I'm going to divide by point
eight to avoid the approximation. So here and I'm going to copy that
to all the other ones.
So now, if I want to have the cumulative billing,
I'm just going to do this. I'm
this is equal to this plus this,
and then this is going to be equal to this plus this, and I'm going
to copy that.
A cross,
and there you have your cumulative bidding. So if I add these
numbers, it
will stand up for one more cell.
So that should add up to the total that we have here. So
which is 12,100
notice that the billing is
delayed one month
from
the cost and the receivables are going to be delayed another month.
So you bill at the end of the month and you get your money one
month later.
I hope you're following so far. You spend the 640 in the first
month, you're going to send the payment request to the owner at
the first at the end of the first month for 800 but you're going to
receive it by the end of the second month, because it's it's
moved one month later.
So now we are not going to draw a an S curve for the receivables,
because the receivables, as we have learned from the lecture, are
going to be like a staggered line, something like this. We
and so on. The numbers are not drawn to scale, but you get the
idea it's going to be a stepped curve. So we can develop that
stepped curve as well.
Now, the difference between your
cumulative expenditure
and your cumulative Billings. This is expenditure, this is Billings.
That difference is going to be your cash negative cash flow. So
basically, to calculate the cash flow here,
but wait a second,
we said here that this is the billing, but what are the
receivables we have here that
there's a 10% retainage, so you are gonna build the owner based on
the price, but the owner is gonna give You only 90% of that, because
they're gonna hold 10% until the end of the warranty period. So
what your receivables are gonna be, are gonna be basically 90% of
that.
So this is Billings.
This is
cumulative billing,
and this is going to be
cumulative
receivables.
Okay, so the cumulative receivables are going to be equal
to
this times point nine,
90% of the billions, and we're going to copy that
right across
something like this.
So now we can actually draw a graph that's going to show the
difference between your costs your cumulative expenditure. This is
cumulative expenditure,
which is money going out of your pocket and your cumulative
receivables, which is the money that you're getting from the
client. So we're going to select this
and this,
and we're going to draw a graph. Let's, let's just do the math
first. Make it simple, so this is equal to
cumulative receivables minus cumulative expenditures,
and let's just draw that across a.
And we're going to draw the graph a histogram showing These numbers,
so that shows your net cash flow.
So what we're saying here is that
you are gonna have a negative cash flow until pretty much month 12.
That's where we're gonna start having money received exceeding
the money that was spent. So all of that is going to be in the red,
and that's going to be in the black.
Forget about the advanced payment. We're not going to discuss it this
time. So basically, we assume that the cost is uniformly distributed.
So if the cost of the activity is 1920 divided by three, so it's
going to be 640
for each month and so on. That's what, what's meant by uniformly
distributed all costs are assumed to be spent following the early
dates. So as soon based on these early dates, that's why you did
not need to calculate the late dates for for the project. So
that's how you develop a cash flow, knowing how much money do
you need to spend or do you need to have in your safe every month.
So obviously, the largest deficit is going to be that 1280 if you do
not have 1280 in your safe, then you cannot complete this project.
That's basically a quick review on the assignment. So I hope you are
going to be able to develop that assignment on your own. Try to
duplicate these steps and try to think about it in the same way you
can practice with other problems by changing these numbers and
trying to draw the cash flow and so on and so forth. So we're going
to discuss that in class, but I prefer to put that on Tegrity so
that you can view it at your own time. See you in class. You.