# Ihab Saad – Cash flow Example

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.
00:00:01 --> 00:00:05

Music. Hello and welcome to a solved example on construction

00:00:05 --> 00:00:08

scheduling, on cash flow projections. Some of you have had

00:00:08 --> 00:00:12

00:00:12 --> 00:00:15

it should be solved. It's quite simple. So what we have here

00:00:15 --> 00:00:19

initially is our network. It shows the durations of the activities

00:00:19 --> 00:00:23

with lags and overlaps and so on and so forth. And then we are

00:00:23 --> 00:00:26

given some information about each activity. So we are given this

00:00:26 --> 00:00:29

table showing, first of all that we have a markup of 20%

00:00:30 --> 00:00:31

we have a retainage of 10%

00:00:33 --> 00:00:36

and we have a 10% advance payment the review period.

00:00:41 --> 00:00:45

The review period is one month, and cost is distributed uniformly

00:00:45 --> 00:00:49

over the activity duration. All costs are assumed to be spent

00:00:49 --> 00:00:52

following the early days. What does each one of these lines mean?

00:00:52 --> 00:00:55

First of all, we have to understand how the pricing is

00:00:55 --> 00:00:57

being done, and you may have learned about that in your

00:00:57 --> 00:01:02

estimating class. But basically, we first calculate our costs, we

00:01:02 --> 00:01:05

estimate our costs, which are going to include direct costs and

00:01:05 --> 00:01:08

indirect costs as well. And that's going to be how much is going to

00:01:08 --> 00:01:12

cost us to build the project or to do the activities for the project.

00:01:12 --> 00:01:16

And then we add something to this cost, which is what we call the

00:01:16 --> 00:01:19

markup. The markup is the difference between the price that

00:01:19 --> 00:01:23

you're going to be charging the client and the cost your actual

00:01:23 --> 00:01:26

cost, or your estimated cost for the activity. So here we have the

00:01:26 --> 00:01:31

markup is 20% the markup components include site overheads,

00:01:31 --> 00:01:36

00:01:37 --> 00:01:42

surety, premiums, risk contingency, and last, but not

00:01:42 --> 00:01:48

least, your profit. So all of that forms 20% that's added to your

00:01:48 --> 00:01:53

cost to get your price. So what we have here on this slide, we have

00:01:53 --> 00:01:58

the price for the different activities. Therefore, by dividing

00:01:58 --> 00:02:02

that by 1.2 or multiplying by point eight, we can get the cost

00:02:02 --> 00:02:06

for each activity. And now we have also, here at the top, we have

00:02:07 --> 00:02:09

durations for the different activities.

00:02:10 --> 00:02:15

And this duration, we are going to assume that it is in months.

00:02:16 --> 00:02:16

Therefore,

00:02:17 --> 00:02:21

in order to get the duration of the activity per month, we're

00:02:21 --> 00:02:27

going to divide the total cost by the cost per month. So here, for

00:02:27 --> 00:02:30

example, is a very simple example on what we did here,

00:02:33 --> 00:02:36

this cost, which is 80%

00:02:37 --> 00:02:43

of the price, and then we divided the cost by the duration to get

00:02:43 --> 00:02:46

the cost per month, we already have the duration in the table.

00:02:47 --> 00:02:51

Therefore, what we have to start with here is we have to solve this

00:02:51 --> 00:02:54

00:02:54 --> 00:02:57

to solve the whole network. We need to just solve for the forward

00:02:57 --> 00:03:01

pass of this network. So let's start doing that. And here's my

00:03:01 --> 00:03:05

color, although it's Kentucky's out of the NCAA, but we're still

00:03:05 --> 00:03:08

00:03:09 --> 00:03:15

with three. So here we have three. We're going to transfer three plus

00:03:15 --> 00:03:20

four is seven. Here we have finished to finish. So we're going

00:03:20 --> 00:03:22

to take the three plus five, which is eight,

00:03:24 --> 00:03:26

minus four is four,

00:03:29 --> 00:03:32

and then here we're going to have seven plus two is nine.

00:03:35 --> 00:03:36

Plus two is 11.

00:03:38 --> 00:03:40

Here we have three plus three is six.

00:03:42 --> 00:03:43

Plus six is 12,

00:03:45 --> 00:03:47

and then here we have eight

00:04:01 --> 00:04:02

plus three is 1111.

00:04:06 --> 00:04:07

Plus one is 12.

00:04:09 --> 00:04:12

Here we have 11 and 12. We're going to take the 12

00:04:13 --> 00:04:14

plus two is 1412.

00:04:16 --> 00:04:18

And 14 we're going to take the 14.

00:04:20 --> 00:04:21

Plus 14 is 18.

00:04:23 --> 00:04:26

So what we're saying here is that the project is going to take 18

00:04:26 --> 00:04:30

months to be complete. We assume that all the activities are going

00:04:30 --> 00:04:34

to be done as early as possible. So that's that was one of the

00:04:34 --> 00:04:36

assumptions here. All costs are assumed to be spent following the

00:04:36 --> 00:04:40

early days. So I'm not interested in the backward pass. I'm not

00:04:40 --> 00:04:42

interested in the critical activities, at least for the time

00:04:42 --> 00:04:48

being. So all I need to do is just develop a table. First of all,

00:04:48 --> 00:04:51

let's try to fill this table so

00:04:53 --> 00:04:55

this is going to be equal to

00:04:56 --> 00:04:59

this number times point A.

00:05:03 --> 00:05:04

Eight. And

00:05:05 --> 00:05:06

same thing

00:05:08 --> 00:05:11

here. We're going to copy this formula, so I'm going to copy this

00:05:11 --> 00:05:11

to the other

00:05:13 --> 00:05:17

cells, and then I'm going to calculate the cost per month. I'm

00:05:17 --> 00:05:22

going to divide this number by the duration of the activity. So I

00:05:23 --> 00:05:25

have done it here. Let's copy these numbers from here.

00:05:36 --> 00:05:41

So basically, what we're saying is that this is equal to this

00:05:42 --> 00:05:45

divided by this, and

00:05:47 --> 00:05:50

then we're going to copy this formula to the other cells. As you

00:05:50 --> 00:05:53

can see, we're automating our calculations. We're doing it very

00:05:53 --> 00:05:59

quickly. So basically, that's the cost distribution per month. So

00:05:59 --> 00:06:01

activity A for four months is gonna spend \$640

00:06:03 --> 00:06:03

a month,

00:06:05 --> 00:06:09

activity, b3, 20 and so on and so forth. What we're gonna do next,

00:06:10 --> 00:06:13

we're gonna draw a table, or we're gonna draw, actually, a bar chart,

00:06:13 --> 00:06:14

so

00:06:17 --> 00:06:18

something like that.

00:06:36 --> 00:06:39

And we're gonna start, we can actually

00:06:40 --> 00:06:41

extend this line

00:06:43 --> 00:06:44

to have 18 cells.

00:06:50 --> 00:06:54

Okay? And then we're going to start plotting our activities. So

00:06:54 --> 00:06:59

activity A is going to start on zero and end on three, so it has a

00:06:59 --> 00:07:03

duration of three. Three cells. 123,

00:07:05 --> 00:07:06

and I'm gonna highlight that

00:07:08 --> 00:07:08

like this,

00:07:11 --> 00:07:14

activity B is going to start after activity eight, from three to

00:07:14 --> 00:07:17

seven. So 1234,

00:07:20 --> 00:07:22

that's activity B,

00:07:23 --> 00:07:28

C is going to be from four to eight. So activity c is going to

00:07:28 --> 00:07:30

start here, four to eight.

00:07:33 --> 00:07:38

Activity D, from nine to 11. So here's d9,

00:07:40 --> 00:07:40

then

00:07:44 --> 00:07:44

11.

00:07:47 --> 00:07:50

Actually it's just two. So from nine to 11.

00:07:54 --> 00:07:57

Activity e from six to 12.

00:08:00 --> 00:08:03

Let's number these columns. So here we have 123,

00:08:07 --> 00:08:09

00:08:16 --> 00:08:18

and We can copy the

00:08:36 --> 00:08:37

I can copy these numbers,

00:08:39 --> 00:08:40

45678, I

00:08:55 --> 00:08:57

see what I'm saying. I'm just drawing a very simple bar chart

00:08:57 --> 00:09:00

based on the calculations that we have here at the top.

00:09:02 --> 00:09:02

I

00:09:04 --> 00:09:06

so activity

00:09:07 --> 00:09:09

e is going to be from six to 12. I

00:09:27 --> 00:09:27

123456,

00:09:34 --> 00:09:35

activity.

00:09:36 --> 00:09:39

F is going to be so this is going to be

00:09:41 --> 00:09:41

a

00:09:44 --> 00:09:44

B,

00:09:46 --> 00:09:47

C,

00:09:49 --> 00:09:49

D,

00:09:52 --> 00:09:52

E,

00:09:55 --> 00:09:58

F is from eight to 11 and.

00:10:08 --> 00:10:09

That's f,

00:10:14 --> 00:10:16

g is going to be 1112,

00:10:18 --> 00:10:19

to 14.

00:10:20 --> 00:10:21

G,

00:10:22 --> 00:10:24

12 to 14.

00:10:34 --> 00:10:36

H, 11 to 12. I,

00:10:44 --> 00:10:47

and then I 14 to 18,

00:10:59 --> 00:11:00

and that's I, I high.

00:11:15 --> 00:11:15

Okay,

00:11:16 --> 00:11:18

so here's my

00:11:20 --> 00:11:20

bar chart,

00:11:22 --> 00:11:25

then what I'm going to do is I'm going to draw a table underneath

00:11:25 --> 00:11:26

this bar chart,

00:11:28 --> 00:11:31

and I'm going to plot in the numbers for the different

00:11:31 --> 00:11:31

activities.

00:11:34 --> 00:11:36

So I'm going to draw a table like that.

00:11:39 --> 00:11:44

Okay, so activity A for the first three months, 640 so here's A

00:11:53 --> 00:11:55

I'm gonna copy these to extend that. D, I

00:12:03 --> 00:12:03

have

00:12:10 --> 00:12:11

and activity, a 646

00:12:15 --> 00:12:15

40

00:12:18 --> 00:12:18

and 640

00:12:24 --> 00:12:27

you can see now it aligns with the start and finish of the FTB

00:12:27 --> 00:12:31

activity. B3, 20s. So from here we're going to have 320 and

00:12:47 --> 00:12:51

C is going to start from here, and it's going to be 160s and

00:13:03 --> 00:13:05

D is going to start from here,

00:13:07 --> 00:13:08

and it's going to be

00:13:09 --> 00:13:10

560s,

00:13:17 --> 00:13:19

e starting here,

00:13:21 --> 00:13:21

and 320

00:13:25 --> 00:13:26

I'm gonna copy that

00:13:27 --> 00:13:28

To the end

00:13:32 --> 00:13:33

of E activity

00:13:34 --> 00:13:36

F, also 320 and

00:13:47 --> 00:13:49

g3, 60, i,

00:14:03 --> 00:14:04

h4,

00:14:07 --> 00:14:08

80,

00:14:10 --> 00:14:11

and then I,

00:14:14 --> 00:14:14

160,

00:14:22 --> 00:14:27

I, these numbers can be in the 1000s, by the way. So just to have

00:14:27 --> 00:14:27

large numbers,

00:14:28 --> 00:14:34

the next step is to calculate the totals. So total expenditure,

00:14:35 --> 00:14:41

I'm just going to add the numbers. So this is going to be the sum of

00:14:41 --> 00:14:43

the column above it like that, and

00:14:44 --> 00:14:46

I'm going to copy that across.

00:14:53 --> 00:14:58

So now it's showing me on each day how much is spent. As you can see,

00:14:58 --> 00:14:59

we can actually put.

00:15:00 --> 00:15:04

Lock these into a graph to show our resource histogram.

00:15:05 --> 00:15:07

So insert

00:15:09 --> 00:15:10

a graph like that

00:15:11 --> 00:15:17

here. It shows basically my expenditure for each month, and it

00:15:17 --> 00:15:22

shows that months 10 and 11 are going to have the largest

00:15:22 --> 00:15:24

expenditures, and so on.

00:15:26 --> 00:15:27

That's based on these numbers.

00:15:28 --> 00:15:31

The next step is to calculate the cumulative number, cumulative

00:15:31 --> 00:15:34

expenditure. So by the end of the first month, I spent 640

00:15:35 --> 00:15:39

during the second month, I spent 640 so by the end of the second

00:15:39 --> 00:15:44

month I spent a total of 640 plus 640 so that's 1280 so I'm going to

00:15:44 --> 00:15:47

do that math here. I'm going to say, in this cell, this is equal

00:15:47 --> 00:15:50

to this plus

00:15:52 --> 00:15:52

this.

00:15:55 --> 00:15:59

Okay, and here, this is going to be equal to only this.

00:16:00 --> 00:16:06

Now for this third cell, that's going to be equal to this plus

00:16:07 --> 00:16:07

this,

00:16:09 --> 00:16:11

and I'm going to copy that to the rest of the cells.

00:16:18 --> 00:16:22

So that basically shows me again. If I want to draw a graph of that,

00:16:22 --> 00:16:22

I'm

00:16:25 --> 00:16:29

going to insert a graph this time. I'm going to make it a line.

00:16:32 --> 00:16:36

That's my S curve for the expenditure. It shows the

00:16:36 --> 00:16:39

expenditure growing with time, the total expenditure growing with

00:16:39 --> 00:16:39

time.

00:16:41 --> 00:16:44

See how simple it is to do it in Excel. It's very simple. I can

00:16:44 --> 00:16:49

increase the size of this chart by the way I want to solve like that

00:16:52 --> 00:16:53

next.

00:16:57 --> 00:17:01

So basically, what I've done so far, when I add these costs here,

00:17:07 --> 00:17:07

that gave me 9680

00:17:09 --> 00:17:11

which is the number that I have here, 9680

00:17:12 --> 00:17:15

so this is my total expenditure. This is my total cash out.

00:17:18 --> 00:17:22

Now the review period is one month, which means I'm going to

00:17:22 --> 00:17:27

submit now I'm going to put the Billings. Billings means I'm going

00:17:27 --> 00:17:33

to bill based on the price. So I can have another column here, or a

00:17:33 --> 00:17:35

few columns. I'm going to add three or four columns, and

00:17:37 --> 00:17:39

I'm going to call this one billing I'm

00:17:50 --> 00:17:53

so what is bidding? You're gonna bill the client for the price and

00:17:53 --> 00:17:55

not for the cost. So

00:17:58 --> 00:17:59

00:18:00 --> 00:18:05

monthly billing. So in this case, the monthly billing is not going

00:18:05 --> 00:18:09

to be so the bidding is going to be per month for activity is going

00:18:09 --> 00:18:11

to be the total price.

00:18:12 --> 00:18:17

This is going to be equal to total price divided by the duration.

00:18:20 --> 00:18:23

And let's copy that to all the other ones.

00:18:24 --> 00:18:28

So that's the price per month for each activity, which is basically

00:18:28 --> 00:18:33

the total price divided by the duration. So at the end of the

00:18:33 --> 00:18:36

first month, I'm gonna build the client for \$800

00:18:39 --> 00:18:43

okay, so the bidding is going to be here, 800

00:18:47 --> 00:18:48

and

00:18:49 --> 00:18:53

so on. So what? What I'm going to do, I'm going to multiply these

00:18:53 --> 00:18:56

numbers times 1.2 I'm

00:19:09 --> 00:19:12

or actually I'm going to divide it. Instead of multiplying by 1.2

00:19:13 --> 00:19:14

I'm going to divide by point

00:19:16 --> 00:19:20

eight to avoid the approximation. So here and I'm going to copy that

00:19:21 --> 00:19:23

to all the other ones.

00:19:32 --> 00:19:35

So now, if I want to have the cumulative billing,

00:19:37 --> 00:19:39

I'm just going to do this. I'm

00:19:45 --> 00:19:49

this is equal to this plus this,

00:19:52 --> 00:19:59

and then this is going to be equal to this plus this, and I'm going

00:19:59 --> 00:19:59

to copy that.

00:20:00 --> 00:20:01

A cross,

00:20:07 --> 00:20:12

and there you have your cumulative bidding. So if I add these

00:20:12 --> 00:20:12

numbers, it

00:20:21 --> 00:20:23

will stand up for one more cell.

00:20:30 --> 00:20:34

So that should add up to the total that we have here. So

00:21:01 --> 00:21:02

which is 12,100

00:21:04 --> 00:21:05

notice that the billing is

00:21:07 --> 00:21:08

delayed one month

00:21:10 --> 00:21:10

from

00:21:12 --> 00:21:17

the cost and the receivables are going to be delayed another month.

00:21:17 --> 00:21:20

So you bill at the end of the month and you get your money one

00:21:20 --> 00:21:21

month later.

00:21:22 --> 00:21:26

I hope you're following so far. You spend the 640 in the first

00:21:26 --> 00:21:29

month, you're going to send the payment request to the owner at

00:21:29 --> 00:21:33

the first at the end of the first month for 800 but you're going to

00:21:33 --> 00:21:37

receive it by the end of the second month, because it's it's

00:21:37 --> 00:21:39

moved one month later.

00:21:42 --> 00:21:50

So now we are not going to draw a an S curve for the receivables,

00:21:50 --> 00:21:52

because the receivables, as we have learned from the lecture, are

00:21:52 --> 00:21:57

going to be like a staggered line, something like this. We

00:22:10 --> 00:22:13

and so on. The numbers are not drawn to scale, but you get the

00:22:13 --> 00:22:18

idea it's going to be a stepped curve. So we can develop that

00:22:18 --> 00:22:19

stepped curve as well.

00:22:20 --> 00:22:22

Now, the difference between your

00:22:24 --> 00:22:26

cumulative expenditure

00:22:27 --> 00:22:32

and your cumulative Billings. This is expenditure, this is Billings.

00:22:32 --> 00:22:35

That difference is going to be your cash negative cash flow. So

00:22:35 --> 00:22:37

basically, to calculate the cash flow here,

00:22:40 --> 00:22:41

but wait a second,

00:22:43 --> 00:22:46

we said here that this is the billing, but what are the

00:22:46 --> 00:22:50

receivables we have here that

00:22:51 --> 00:22:57

there's a 10% retainage, so you are gonna build the owner based on

00:22:57 --> 00:23:01

the price, but the owner is gonna give You only 90% of that, because

00:23:01 --> 00:23:06

they're gonna hold 10% until the end of the warranty period. So

00:23:06 --> 00:23:10

what your receivables are gonna be, are gonna be basically 90% of

00:23:10 --> 00:23:11

that.

00:23:14 --> 00:23:17

So this is Billings.

00:23:20 --> 00:23:20

This is

00:23:22 --> 00:23:23

cumulative billing,

00:23:26 --> 00:23:27

and this is going to be

00:23:29 --> 00:23:30

cumulative

00:23:31 --> 00:23:31

receivables.

00:23:41 --> 00:23:45

Okay, so the cumulative receivables are going to be equal

00:23:45 --> 00:23:45

to

00:23:47 --> 00:23:50

this times point nine,

00:23:51 --> 00:23:55

90% of the billions, and we're going to copy that

00:23:56 --> 00:23:57

right across

00:24:00 --> 00:24:01

something like this.

00:24:03 --> 00:24:07

So now we can actually draw a graph that's going to show the

00:24:07 --> 00:24:13

00:24:13 --> 00:24:14

cumulative expenditure,

00:24:19 --> 00:24:21

00:24:21 --> 00:24:24

receivables, which is the money that you're getting from the

00:24:24 --> 00:24:28

client. So we're going to select this

00:24:30 --> 00:24:32

and this,

00:24:37 --> 00:24:41

and we're going to draw a graph. Let's, let's just do the math

00:24:41 --> 00:24:45

first. Make it simple, so this is equal to

00:24:46 --> 00:24:51

cumulative receivables minus cumulative expenditures,

00:24:53 --> 00:24:55

and let's just draw that across a.

00:25:23 --> 00:25:26

And we're going to draw the graph a histogram showing These numbers,

00:25:38 --> 00:25:42

so that shows your net cash flow.

00:25:49 --> 00:25:51

So what we're saying here is that

00:25:54 --> 00:25:59

you are gonna have a negative cash flow until pretty much month 12.

00:26:00 --> 00:26:04

That's where we're gonna start having money received exceeding

00:26:04 --> 00:26:08

the money that was spent. So all of that is going to be in the red,

00:26:08 --> 00:26:10

and that's going to be in the black.

00:26:12 --> 00:26:15

Forget about the advanced payment. We're not going to discuss it this

00:26:15 --> 00:26:20

time. So basically, we assume that the cost is uniformly distributed.

00:26:20 --> 00:26:24

So if the cost of the activity is 1920 divided by three, so it's

00:26:24 --> 00:26:25

going to be 640

00:26:26 --> 00:26:30

for each month and so on. That's what, what's meant by uniformly

00:26:30 --> 00:26:33

distributed all costs are assumed to be spent following the early

00:26:33 --> 00:26:37

dates. So as soon based on these early dates, that's why you did

00:26:37 --> 00:26:41

not need to calculate the late dates for for the project. So

00:26:41 --> 00:26:45

that's how you develop a cash flow, knowing how much money do

00:26:45 --> 00:26:49

you need to spend or do you need to have in your safe every month.

00:26:49 --> 00:26:54

So obviously, the largest deficit is going to be that 1280 if you do

00:26:54 --> 00:26:58

not have 1280 in your safe, then you cannot complete this project.

00:26:59 --> 00:27:04

That's basically a quick review on the assignment. So I hope you are

00:27:04 --> 00:27:08

going to be able to develop that assignment on your own. Try to

00:27:08 --> 00:27:13

duplicate these steps and try to think about it in the same way you

00:27:13 --> 00:27:16

can practice with other problems by changing these numbers and

00:27:16 --> 00:27:19

trying to draw the cash flow and so on and so forth. So we're going

00:27:19 --> 00:27:23

to discuss that in class, but I prefer to put that on Tegrity so

00:27:23 --> 00:27:28

that you can view it at your own time. See you in class. You.

Share Page