Project analysis and advanced data calculation and conditional formatting

54
PROJECT SIMULATION AND ADVANCED DATA CALCULATION IN EXCEL Dr. Chachrist Srisuwanrat สาขาวิชาการบริหารงานก่อสร้าง คณะวิศวกรรมศาสตร์และสถาปัตยกรรมศาสตร์ มหาวิทยาลัยเทคโนโลยีราชมงคลตะวันออก วิทยาเขตอุเทนถวาย

Transcript of Project analysis and advanced data calculation and conditional formatting

Page 1: Project analysis and advanced data calculation and conditional formatting

PROJECT SIMULATION AND

ADVANCED DATA CALCULATION

IN EXCEL

Dr. Chachrist Srisuwanrat

สาขาวชิาการบริหารงานก่อสร้าง คณะวศิวกรรมศาสตร์และสถาปัตยกรรมศาสตร์ มหาวทิยาลัยเทคโนโลยีราชมงคลตะวันออก วทิยาเขตอุเทนถวาย

Page 2: Project analysis and advanced data calculation and conditional formatting

Topics

• Project Risk Analysis using Simulation

• Determine project duration under uncertainty

• Plot density and cumulative distribution of

project duration

• Advanced Data Calculation and Formatting

• Construct “Two-way Table”

• Apply “Conditional Formatting”

2

Page 3: Project analysis and advanced data calculation and conditional formatting

Project Risk Analysis using Simulation

3

Page 4: Project analysis and advanced data calculation and conditional formatting

Probabilistic Activity Duration

4

Page 5: Project analysis and advanced data calculation and conditional formatting

CPM Calculation Table

5

Page 6: Project analysis and advanced data calculation and conditional formatting

Use constant duration to verify formula

6

Page 7: Project analysis and advanced data calculation and conditional formatting

Forward Calculation (ESD, EFD)

• ESDj = MAX( EFDi )

• ESDj = ESDj + DURj

7

Page 8: Project analysis and advanced data calculation and conditional formatting

Backward Calculation (LSD, LFD)

• LFDj = MIN(LSDk)

• LSDj = LFDj - DURj

8

Page 9: Project analysis and advanced data calculation and conditional formatting

Float Calculation (TF)

• TF = LFD – EFD

• Is Critical = if(TF = 0, 1, 0)

9

Page 10: Project analysis and advanced data calculation and conditional formatting

Apply probabilistic duration

• Copy code in “Duration Description” to Duration column

• Note: your numbers will be different because of

randomization. Press F9 to view change.

10

Page 11: Project analysis and advanced data calculation and conditional formatting

So what is the project duration???

Best case? Most likely? Worst case?

11

Page 12: Project analysis and advanced data calculation and conditional formatting

Use “Home->Fill->Series” to create

project-duration collection table

12

Page 13: Project analysis and advanced data calculation and conditional formatting

Use “What-If Analysis->Data Table” to

collect data (project duration)

13

Page 14: Project analysis and advanced data calculation and conditional formatting

Use “countif” function to count project

durations that are less than or equal to

14

Page 15: Project analysis and advanced data calculation and conditional formatting

Plot Graph

15

Page 16: Project analysis and advanced data calculation and conditional formatting

Construct Is-Critical Table for Activities

to collect probability of being critical

Collection 2,000 data points

Using

Fill->Series

What-If Analysis->Data Table

16

Page 17: Project analysis and advanced data calculation and conditional formatting

Use “Data->What-If Analysis->Data Table”

to collect 2,000 Is-Critical data points

17

Page 18: Project analysis and advanced data calculation and conditional formatting

CountIf to determine %Critical

18

Page 19: Project analysis and advanced data calculation and conditional formatting

Project Duration Analysis

19

Page 20: Project analysis and advanced data calculation and conditional formatting

Do you? • Believe A-B-E-G almost being critical half of the time?

• Know that A -D-G could be critical?

• Think all 3 paths could be critical at the same time?

• Worst duration could be TWICE of best duration?

20

Page 21: Project analysis and advanced data calculation and conditional formatting

BREAK

21

Page 22: Project analysis and advanced data calculation and conditional formatting

Advanced Data Calculation and Formatting

22

Page 23: Project analysis and advanced data calculation and conditional formatting

Advanced Data Calculation and Formatting

• Main calculation table

• Two-way Table

• Text function

• Conditional Formatting

23

Page 24: Project analysis and advanced data calculation and conditional formatting

Simple Example:

Land Cost Valuation and %Profit

• Land Size 3 rais with Cost 200,000 Baht/sq.w.

• FAR = 3

• NFA/GFA = 65%

• Construction Cost 17,000 Baht/sq.m.

• Mgmt & Mkt Cost 10% of (Land and Construction Cost)

• Sale Price 75,000 Baht/sq.m.

• What is the %Profit?

24

Page 25: Project analysis and advanced data calculation and conditional formatting

Calculation Table

25

Page 26: Project analysis and advanced data calculation and conditional formatting

Two-way Table Example:

Land Cost Valuation and %Profit

• Land Size 3 rais with Cost 180,000 to 220,000 Baht/sq.w.

• FAR = 3

• NFA/GFA = 65%

• Construction Cost 17,000 Baht/sq.m.

• Mgmt & Mkt Cost 10% of (Land and Construction Cost)

• Sale Price 60,000 to 90,000 Baht/sq.m.

• What is the %Profit for each combination

of Land Cost and Sale Price?

26

Page 27: Project analysis and advanced data calculation and conditional formatting

Construct Two-way Table

• Land Cost 180,000 to 220,000 Baht/sq.w.

• Sale Price 60,000 to 90,000 Baht/sq.m.

27

Page 28: Project analysis and advanced data calculation and conditional formatting

Data->What-if Analysis->Data Table

28

Page 29: Project analysis and advanced data calculation and conditional formatting

After click OK and Formatting cells

29

Page 30: Project analysis and advanced data calculation and conditional formatting

Add Desired %Profit and

Minimum Required %Profit

for Conditional Formatting Only

30

Page 31: Project analysis and advanced data calculation and conditional formatting

Home->Conditional Formatting->New

Rule (selecting J5)

31

Page 32: Project analysis and advanced data calculation and conditional formatting

Select J5->Format Painter

• Select J5

• Click Format Painter

• Crop cells in the table for formatting

32

Page 33: Project analysis and advanced data calculation and conditional formatting

Conditional Formatting

for Minimum Required %Profit

33

Page 34: Project analysis and advanced data calculation and conditional formatting

Select J5->Format Painter

34

Page 35: Project analysis and advanced data calculation and conditional formatting

Now you can also change and study

• NFA/GFA from 65% to 60%

• Construction Cost from 17,000 to 18,000 Baht/sq.m.

• Mgmt & Mkt Cost from 10% to 15%

35

Page 36: Project analysis and advanced data calculation and conditional formatting

Com

parison

36

Page 37: Project analysis and advanced data calculation and conditional formatting

BREAK

Two-way Table

Conditional Formatting

37

Page 38: Project analysis and advanced data calculation and conditional formatting

Simulated Table Example:

Expected, Max, Min %Profit

• Land Cost 190,000 to 210,000 Baht/sq.w.

• NFA/GFA = 60-70%

• Construction Cost 17,000-21,000 Baht/sq.m.

• Mgmt & Mkt Cost 10-15% of (Land and Construction Cost)

• Sale Price 60,000 to 90,000 Baht/sq.m.

• What is the %Profit:

• Expected

• Max

• Min

38

Page 39: Project analysis and advanced data calculation and conditional formatting

39

Page 40: Project analysis and advanced data calculation and conditional formatting

Data->What-If Analysis->Data Table

40

Page 41: Project analysis and advanced data calculation and conditional formatting

Data Table runs and collects 10,000 data

of %Profit

41

Page 42: Project analysis and advanced data calculation and conditional formatting

Let’s get Land Cost, NFA/GFA,

Construction Cost, Mgmt&Mkt Cost, and

Sale Price

42

Page 43: Project analysis and advanced data calculation and conditional formatting

Data->What-If Analysis->Data Table

43

Page 44: Project analysis and advanced data calculation and conditional formatting

ORIGINAL

Land Cost 190,000 to 210,000 Baht/sq.w.

NFA/GFA = 60-70%

Construction Cost 17,000 to 21,000 Baht/sq.m.

Mgmt & Mkt Cost 10 to 15% of (Land and Construction Cost)

Sale Price 60,000 to 90,000 Baht/sq.m.

DESIRED

Land Cost 190,000 to 200,000 Baht/sq.w.

NFA/GFA = 60-70%

Construction Cost 17,000 to 18,000 Baht/sq.m.

Mgmt & Mkt Cost 10-15% of (Land and Construction Cost)

Sale Price 70,000 to 85,000 Baht/sq.m.

44

Page 45: Project analysis and advanced data calculation and conditional formatting

Example: Very Difficult Problem

• A group of consultant agrees to manage and sell a land.

• This land has 18 rais.

• They will be spited to 4 to 6 plots.

• These 4-6 plots will be sold at

• Plot 1: 190,000 Baht/rais for 2 to 4 rais or not at all

• Plot 2: 200,000 Baht/rais for 2 to 3 rais or not at all

• Plot 3: 200,000 Baht/rais for 2 to 3

• Plot 4: 210,000 Baht/rais for 2 to 3

• Plot 5: 210,000 Baht/rais for 2 to 3

• Plot 6: 200,000 or 210,000 Baht/rais depending on remaining land

• In the end, all 18 rais must be sold.

(continue next page)

45

Page 46: Project analysis and advanced data calculation and conditional formatting

Continue

• Consulting Fee 2% of all sale

• Performance Fee 15% of land sold above 195,000 Baht/rais

• If using sale agents, consultant must pay them 1% of the

land price sold by agents

• Probabilities:

• Plot 1 has 50% chance to be sold at this price or not at all

• Plot 2 has 50% chance to be sold at this price or not at all

• If sold, size of land (2, 3, or 4 rais depending), which is sold, has equal

chance (uniform distribution)

• There is 50% chance using sale agents in each plot

46

Page 47: Project analysis and advanced data calculation and conditional formatting

Questions

• What is the expected, max, min of:

• Land price per sq.w.?

• Net owner revenue after all fees in Baht?

• Net consulting + performance fees Baht?

• Absolute commission calculated from consulting +

performance fees in percent?

• Sale agent commission in Baht?

47

Page 48: Project analysis and advanced data calculation and conditional formatting

Calculation Table with Random Formula

48

Page 49: Project analysis and advanced data calculation and conditional formatting

Simulated Table with 30,000 Replications

49

Page 50: Project analysis and advanced data calculation and conditional formatting

Summary

50

Page 51: Project analysis and advanced data calculation and conditional formatting

Density and Cumulative Function of

Consultant Revenue

51

Page 52: Project analysis and advanced data calculation and conditional formatting

Consultant Revenue upon

Number of rais (0-18 rais) sold by Sale Agents

52

Page 53: Project analysis and advanced data calculation and conditional formatting

WHAT IF???

The consultant have already sold

3 rais at 200,000 Baht/sq.w. via sale agents

Can he increase number of rais to be sold at

190,000 Baht/rai?

If Yes, how many?

53

Page 54: Project analysis and advanced data calculation and conditional formatting

THANK YOU

Dr. Chachrist Srisuwanrat

สาขาวชิาการบริหารงานก่อสร้าง คณะวศิวกรรมศาสตร์และสถาปัตยกรรมศาสตร์ มหาวทิยาลัยเทคโนโลยีราชมงคลตะวันออก วทิยาเขตอุเทนถวาย