Ihab Saad – Cash flow Example

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.