Docstoc

SIMPLIFIED CFROI VALUATION MODEL AUDIT - Excel - Excel

Document Sample
SIMPLIFIED CFROI VALUATION MODEL AUDIT - Excel - Excel Powered By Docstoc
					SIMPLIFIED CFROI VALUATION MODEL AUDIT
-----START UP----2 10.0% 20.0% BIG "T" 7 6.0% 12.0% ---STOP INVESTING--8 9 10

10% COST OF CAPITAL (A) (B) Growth Rate Project ROI Project Cash Flows 1 2 3 4 5 6 7

1

3 10.0% 20.0%

4 10.0% 20.0%

5 8.5% 17.0%

6 7.5% 15.0%

20.0%

42.0

42.0 46.2

42.0 46.2 50.8

46.2 50.8 55.9

50.8 55.9 57.2

55.9 57.2 59.1

57.2 59.1 58.8

59.1 58.8

58.8

(C) (D) (E) (F) (G) (H)

Gross Cash Flow Investment NWC Released NWC Net Change NWC (D - E) CAPEX NCR (C - F - G)

0.0 20.0 0.0 20.0 80.0 (100.0)

42.0 22.0 0.0 22.0 88.0 (68.0)

88.2 24.2 0.0 24.2 96.8 (32.8)

138.9 26.6 20.0 6.6 106.5 25.8

152.8 28.9 22.0 6.9 115.5 30.4

163.9 31.0 24.2 6.8 124.2 32.8

172.1 32.9 26.6 6.3 131.6 34.2

175.0 0.0 28.9 (28.9) 0.0 203.9

117.8 0.0 31.0 (31.0) 0.0 148.9

58.8 0.0 32.9 (32.9) 0.0 91.7

(I) (J) (K) (L)

Balance Sheet - NWC Balance Sheet - Gross Assets % Non-Depreciating CFROI Gross CF(t) & Gross Assets(t-1)

20.0 100.0 20.0%

42.0 210.0 20.0% 20.0%

66.2 331.0 20.0% 20.0%

72.8 364.1 20.0% 20.0%

79.7 398.5 20.0% 20.0%

86.6 432.8 20.0% 18.9%

92.8 464.2 20.0% 17.2%

64.0 319.8 20.0% 14.6%

32.9 164.6 20.0% 13.5%

0.0 0.0

12.0%

VALUE #1
= PV NCR(t+1) to YR 10 [H]

354.3

363.9

369.9

374.0

377.3

211.1

83.3

0.0

EXISTING ASSETS (M) (N) (O) PV This Year of Cash Flow / Wind Down PV Released NWC PV of Total Receipts From Existing Assets (M + N) 244.6 54.5 299.2 269.1 60.0 329.1 285.4 65.7 351.1 296.9 71.4 368.3 300.6 76.6 377.3 155.7 55.4 211.1 53.4 29.9 83.3 0.0 0.0 0.0

FUTURE INVESTMENTS (P) (Q) (R) (S) Investment (D + G) PV of Investment Incremental Wealth Created (P - Q) PV Incremental Wealth Stream 121.0 144.5 23.5 55.2 133.1 158.9 25.8 34.8 144.4 163.9 19.5 18.8 155.2 170.2 14.9 5.7 164.6 170.9 6.3 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0

VALUE #2
= (O + S)

354.3

363.9

369.9

374.0

377.3

211.1

83.3

0.0

(T)

Shareholder Return ((Value(t) + NCR(t) ) / Value(t-1)) - 1 % Future ( S / Value(t) )

10.0%

10.0%

10.0%

10.0%

10.0%

10.0%

10.0%

(U)

9.6%

5.1%

1.5%

0.0%

0.0%

0.0%

SIMPLIFIED CFROI VALUATION MODEL AUDIT
Project Life Discount Rate Growth Rate Initial Investment % Non-Depreciating Last yr. of Investment Big "T", ROI = Discount Rate End Year 3 10.0% 10.0% 100 20.0% 7 8 20

1 (A) (B) Growth Rate Project ROI Project Cash Flows 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 (C) (D) (E) (F) (G) (H) Gross Cash Flow Investment NWC Released NWC Net Change NWC (D - E) CAPEX NCR (C - F - G) 0 20

2 10.0% 20.0%

3 10.0% 20.0%

4 10.0% 20.0%

5 8.5% 17.0%

6 7.5% 15.0%

7 6.0% 12.0%

8

9

10

11

12

13

14

15

16

17

18

19

20

20.0%

42

42 46

42 46 51

46 51 56

51 56 57

56 57 59

57 59 59

59 59 0

59 0 0

0 0 0

0 0 0

0 0 0

0 0 0

0 0 0

0 0 0

0 0 0 0 0 0 0 0 0

0 0 0 0 0 0 0 0

0 0 0 0 0 0 0 0 0 0 0 0 0

42 22

88 24

139 27 20 7 106 26

153 29 22 7 116 30

164 31 24 7 124 33

172 33 27 6 132 34

175 0 29 (29) 0 204

118 0 31 (31) 0 149

59 0 33 (33) 0 92

0 0 0 0 0 0

0 0 0 0 0 0

0 0 0 0 0 0

0 0 0 0 0 0

0 0 0 0 0 0

0 0 0 0 0 0

20 80 (100)

22 88 (68)

24 97 (33)

(I) (J) (K) (L)

Balance Sheet - NWC Balance Sheet - Gross Investment % Non-Depreciating CFROI Gross CF(t) & Gross Inv(t-1)

20 100 20.0%

42 210 20.0% 20.0%

66 331 20.0% 20.0%

73 364 20.0% 20.0%

80 399 20.0% 20.0%

87 433 20.0% 18.9%

93 464 20.0% 17.2%

64 320 20.0% 14.6%

33 165 20.0% 13.5%

(0) (0)

(0) (0)

(0) (0)

(0) (0)

(0) (0)

(0) (0)

(0) (0)

(0) (0)

(0) (0)

(0) (0)

(0) (0)

12.0%

0.0%

0.0%

0.0%

0.0%

0.0%

0.0%

0.0%

0.0%

0.0%

0.0%

VALUE #1
= PV NCR(t+1) to End [H]

354

364

370

374

377

211

83

0

0

0

0

0

0

0

0

0

0

0

SIMPLIFIED CFROI VALUATION MODEL AUDIT
Project Life Discount Rate Growth Rate Initial Investment % Non-Depreciating Last yr. of Investment Big "T", ROI = Discount Rate End Year 3 10.0% 10.0% 100 20.0% 7 8 20

1 EXISTING ASSETS: Future Cash Flows From Existing Assets Year T+1 Year T+2 Year T+3 Year T+4 / (1+.1)^1 / (1+.1)^2 / (1+.1)^3 / (1+.1)^4 (M) (N) (O) (P) (Q) 1.10 1.21 1.33 1.46

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

88 88 46 0 80 73 35 0

139 97 51 0 126 80 38 0 245 55 299 97 258

153 107 56 0 139 88 42 0 269 60 329 80 283

164 113 57 0 149 93 43 0 285 66 351 58 312

172 116 59 0 156 96 44 0 297 71 368 31 343

175 118 59 0 159 97 44 0 301 77 377 0 377

118 59 0 0 107 49 0 0 156 55 211 0 211

59 0 0 0 53 0 0 0 53 30 83 0 83

0 0 0 0 0 0 0 0 0 0 0 0 0

0 0 0 0 0 0 0 0 0 0 0 0 0

0 0 0 0 0 0 0 0 0 0 0 0 0

0 0 0 0 0 0 0 0 0 0 0 0 0

0 0 0 0 0 0 0 0 0 0 0 0 0

0 0 0 0 0 0 0 0 0 0 0 0 0

0 0 0 0 0 0 0 0 0 0 0 0 0

0 0 0 0 0 0 0 0 0 0 0

0 0

0

0 0 0 0 0 0 0

0 0 0 0 0 0 0

0 0 0 0 0 0 0

PV This Year of Cash Flows PV Released NWC PV of Total Receipts From Existing Assets (M + N) PV NCR(t+1) to Big "T" [H] PV This Year Existing Asset at Big "T" [O]

0

0

0

0

VALUE #2
= (P + Q)

354

364

370

374

377

211

83

0

0

0

0

0

0

0

0

0

0

0

(R)

Investment (D + G) Annual Gross Cash Flow from Investment Residual Value from Investment PV of Investment GCF & Residual Value / (1+.1)^1 / (1+.1)^2 / (1+.1)^3 / (1+.1)^4 Residual Value / (1.1)^4 PV of Investment Incremental Wealth Created (S - R) PV Incremental Wealth

121 51 24

133 56 27

144 57 29

155 59 31

165 59 33

0 0 0

0 0 0

0 0 0

0 0 0

0 0 0

0 0 0

0 0 0

0 0 0

0 0 0

1.10 1.21 1.33 1.46 1.33

(S) (T) (U)

46 42 38 0 18 144 23 55

51 46 42 0 20 159 26 35

52 47 43 0 22 164 20 19

54 49 44 0 23 170 15 6

53 49 44 0 25 171 6 0

0 0 0 0 0 0 0 0

0 0 0 0 0 0 0 0

0 0 0 0 0 0 0 0

0 0 0 0 0 0 0 0

0 0 0 0 0 0 0 0

0 0 0 0 0 0 0 0

0 0 0 0 0 0 0 0

0 0 0 0 0 0 0 0

0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

VALUE #3
= (O + U)

354

364

370

374

377

211

83

0

0

0

0

0

0

0

0

0

0

0

(V)

Shareholder Return ((Value(t) + NCR(t) ) / Value(t-1)) - 1

10.0%

10.0%

10.0%

10.0%

10.0%

10.0%

N/A

N/A

N/A

N/A

N/A

N/A

N/A

N/A

N/A

N/A

N/A

CFROI

CFROI

Gross Investment 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 (100.00) (210.00) (331.00) (364.10) (398.51) (432.76) (464.22) (319.80) (164.56) 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00

1 41.98 88.15 138.95 152.84 163.86 172.12 175.01 117.81 58.76 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00

Gross Cash Flows 2 3 41.98 88.15 138.95 152.84 163.86 172.12 175.01 117.81 58.76 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 61.98 130.15 205.15 225.66 243.57 258.67 267.85 181.77 91.67 (0.00) (0.00) (0.00) (0.00) (0.00) (0.00) (0.00) (0.00) (0.00) (0.00) 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00

Non-Depr. Asset 20.00 42.00 66.20 72.82 79.70 86.55 92.84 63.96 32.91 (0.00) (0.00) (0.00) (0.00) (0.00) (0.00) (0.00) (0.00) (0.00) (0.00)

CFROI 20.0% 20.0% 20.0% 20.0% 18.9% 17.2% 14.6% 13.5% 12.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%

PV NCR(t+1) to Big "T" [H] - CALCULATION P
Big T Year 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 4 23 5 49 28 6 73 55 30 7 97 80 58 31 8 223 220 211 200 185 9 307 312 313 311 308 135 10 354 364 370 374 377 211 83

PV This Year Existing Asset at Big "T" - CALCULATION O

Page 4

CFROI

Year 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

4 299 329 362 398 438 482 530 583 641 705 776 854 939 1,033 1,136 1,250 1,375 1,512

5 290 319 351 386 425 467 514 565 622 684 753 828 911 1,002 1,102 1,212 1,333 1,467

6 277 304 335 368 405 446 490 539 593 652 718 790 868 955 1,051 1,156 1,272 1,399

7 258 283 312 343 377 415 456 502 552 608 668 735 809 890 979 1,076 1,184 1,302

8 131 144 159 174 192 211 232 255 281 309 340 374 411 452 498 548 602 662

9 47 52 57 63 69 76 83 92 101 111 122 134 148 162 179 197 216 238

10 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

Page 5

CFROI

Big T 11 354 364 370 374 377 211 83 12 354 364 370 374 377 211 83 13 354 364 370 374 377 211 83 14 354 364 370 374 377 211 83 15 354 364 370 374 377 211 83 16 354 364 370 374 377 211 83 17 354 364 370 374 377 211 83 18 354 364 370 374 377 211 83 19 354 364 370 374 377 211 83 -

Page 6

CFROI

11 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

12 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

13 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

14 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

15 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

16 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

17 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

18 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

19 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

Page 7

CFROI

20 354 364 370 374 377 211 83 -

Page 8

CFROI

20 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

Page 9


				
DOCUMENT INFO
Description: SIMPLIFIED CFROI VALUATION MODEL AUDIT