Project Cash Flow Estimation
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View Project Cash Flow Estimation as PDF for free.
More details
- Words: 1,189
- Pages: 9
0
1
2
3
4
5
6
7
Revenue Less: COGS EBITDA -Depreciation EBIT EBIT(1-t) Add: Dep Add: Fixed Assets Savaged Add: Working Capital Salvaged Less: Capital Gains on FA CFAT -200 IRR 55%
250 100 150 37.50 112.50 78.75 37.50
250 100 150 28.13 121.88 85.31 28.13
250 100 150 21.09 128.91 90.23 21.09
250 100 150 15.82 134.18 93.93 15.82
250 100 150 11.87 138.13 96.69 11.87
250 100 150 8.90 141.10 98.77 8.90
116.25
113.44
111.33
109.75
108.56
107.67
250 100 150 6.67 143.33 100.33 6.67 48.00 50.00 2.80 202.20
Opening BV Less: Depreciation Closing BV
150.00 37.50 112.50
112.50 28.13 84.38
84.38 21.09 63.28
63.28 15.82 47.46
47.46 11.87 35.60
35.60 8.90 26.70
26.70 6.67 20.02
IO Plant W/C
150 50
0
6
7
80.0 120.0 160.0 200.0 160.0 120.0 24.0 36.0 48.0 60.0 48.0 36.0 8.0 12.0 16.0 20.0 16.0 12.0 10.0 10.0 10.0 10.0 10.0 10.0 8.0 12.0 16.0 20.0 16.0 12.0 4.0 6.0 8.0 10.0 8.0 6.0 26.0 44.0 62.0 80.0 62.0 44.0 30.0 22.5 16.9 12.7 9.5 7.1 (4.0) 21.5 45.1 67.3 52.5 36.9 (2.8) 15.1 31.6 47.1 36.8 25.8 30.0 22.5 16.9 12.7 9.5 7.1 10 10 10 -10 -10 -10 Less: Incremental Investment in Non Cash W/C
80.0 24.0 8.0 10.0 8.0 4.0 26.0 5.3 20.7 14.5 5.3 -16 25 1.2 0.9 7 54.1
Capital Equipment Working Capital Total Initial Investment
120 20
1
2
3
4
5
120 20 140
Sales Raw Material 30% Variable Mfg Cost 10% Fxd A Op & Maint Cost Variable Selling Expenses 10% Incremental Overhead Allocation 5% EBITDA -Depreciation (see schedule below) EBIT EBIT(1-t) 30% Add: Depreciation
Add: Fixed Assets Salvaged Add: Tax Shield on Working Capital Loss Less: Capital Gains Tax on Fixed Assets 10% Less: Product Cannabilization Cash Flow after tax -140
7 10.2
7 20.6
7 31.5
Future Value interest factor
1
1.150
1.3225
1.5209
-140
8.87
15.54
20.69
Discounted Cash Flow Net Present Value
Internal Rate of Return
1.36 15.26%
7 62.8
7 49.2
7 35.9
1.7490
2.0114
2.3131
2.6600
35.90
24.48
15.54
20.34
1
2
3
4
5
6
7
120 25% 30 90 0 1 Working Capital 25% 20 30 Incremental Investment in Working Capital 10
90.0 22.5 67.5 2 40 10
67.5 16.9 50.6 3 50 10
50.6 12.7 38.0 4 40 -10
38.0 9.5 28.5 5 30 -10
28.5 7.1 21.4 6 20 -10
21.4 5.3 16.0 7
Opening Book Value Less: Depreciation Closing Book Value
Key Points: Reinvestment in Working Capital Salvage of Working Capital and Tax Shield Sale of Equipment at a Profit and consequent Tax treatment Product Cannabilization [Side Cost] Initial Investment [Every Rupee that goes out because we said yes to this project]
Total Funds { 450
Cost Long Equity { Equity Term 100 Term Loan 15% Explicit Cost Funds { Funds { Current 300 Short Term Loan 18% Liabilitie 400 Trade Credit s{ 1 500.0 320.0 180.0 83.3 96.8
2 500.0 320.0 180.0 55.5 124.5
3 500.0 320.0 180.0 37.0 143.0
4 500.0 320.0 180.0 24.7 155.3
100 200 100 50 450 5 500.0 320.0 180.0 16.5 163.5
18.0 30.0 48.8 14.6 34.1
18.0 28.5 78.0 23.4 54.6
18.0 22.5 102.5 30.7 71.7
18.0 16.5 120.8 36.2 84.6
18.0 10.5 135.0 40.5 94.5
18.0 4.5 146.5 44.0 102.6
Cash Inflow Salvage of Working Capital Salvage Value of Assets Total Cash Inflow Cash Outflow COGS Interest Tax Principal Repaid Term Loan Repaid Trade Credit Repaid Total Cash Outflow Surplus Add: Opening Cash Closing Cash
500.0
500.0
500.0
500.0
500.0
500.0
500.0
500.0
500.0
500.0
500.0 200.0 80.0 780.0
320.0 48.0 14.6 -
320.0 46.5 23.4 40
320.0 40.5 30.7 40
320.0 34.5 36.2 40
320.0 28.5 40.5 40
382.6 117.4 117.4
429.9 70.1 117.4 187.5
431.2 68.8 187.5 256.2
430.7 69.3 256.2 325.5
429.0 71.0 325.5 396.5
320.0 22.5 44.0 40 100 50.0 576.5 203.5 396.5 600.0
EBIT(1-t) Add: Dep Less: WC Leess: FA FCFF
67.73 83.3
87.13 55.5
100.07 37.0
108.71 24.7
114.47 16.5
118.31 11.0
150.98
142.66
137.11
133.41
130.94
129.30
260 120 100 50 530
4 345 80 100 50 575
5 440 40 100 50 630
6 600 600
250 176 74 200
250 201 49 200
250 217 33 200
250 228 -
Revenues Less: Op C EBITDA Less: Dep EBIT Interest Working Cap Term Loan EBT Less: Tax EAT
FCFE
Equity Term Loan Short Term Loan Trade Credit Total Capital
Balance Sheet 100 200 100 50 450
1 134 200 100 50 484
2 189 160 100 50 499
Fixed Assets Gross Less: Depreciation Net block of FA Working Capital
250 250 200
250 83 167 200
250 139 111 200
3
6 500.0 320.0 180.0 11.0 169.0
Cash Net Assets
450 Opening BV Less: Dep Closing BV
117 484 -
187 499 -
256 530 -
326 575 -
396 630 -
600 600 -
250.00 83.25 166.75
166.75 55.53 111.22
111.22 37.04 74.19
74.19 24.70 49.48
49.48 16.48 33.00
33.00 10.99 22.01 80 57.99
Capital Gain Term Loan i n
200 0.08 10 12 1 st year
Instalment No Time Month No Semi Annual Installment Balance O/S Interest
200 30
2nd year repaid 1 2 18 24 20 20 180 15
160 13.5 28.5
3rd year repaid 3 4 30 36 20 20 140 12
120 10.5 22.5
4th year repaid 5 42 20 100 9 16.5
Fixed Assets Current Assets
250 200
450
4th year repaid 6 48 20 80 7.5 16.5
5th year repaid 7 8 54 60 20 20 60 6
40 4.5 10.5
6th year repaid 9 10 66 72 20 20 20 3
0 1.5 4.5
You are examining the optimum capital structure for your firm and have collected the following information. Your firm currently does not employ and debt Rf 7% Tax 34% Rm 12% Debt Ratio Bond Rating KD β ke kd WACC MVE MVD 0% AAA 7.25% 1.2 200 0 10% AA 8.25% 180 20 20% A 9.25% 160 40 30% BBB 10.75% 140 60 40% BB 11.75% 120 80 50% B 12.75% 100 100 60% CCC 14% 80 120 70% CC 15% 60 140 80% C 16% 40 160 90% D 18% 20 180
MVF 200 200 200 200 200 200 200 200 200 200
0 Initial Outflow 1 2 3 4 5 Sum of DCF Less: NPV
Lower 24% 180.60 150
Sum of DCF IO
IRR=
Lower Rate plus
32.71%
1 2 3 4 5 6 7
-200 116 113 111 110 109 108 202
-150 50 60 70 80 90
45% 80.17 53.95 36.52 24.83 16.94 11.58 15.00 239.00 200 39.00
14%
24%
34%
32.71%
43.86 46.17 47.25 47.37 46.74 231.39 150 81.39
40.32 39.02 36.71 33.84 30.70 180.60 150.00 30.60
37.31 33.42 29.09 24.81 20.83 145.47 150.00 (4.53)
37.68 34.07 29.95 25.79 21.86 149.35 150.00 (0.65)
Higher 34% 145.47
Sum of the DCF at the Lower Rate-IO Sum of DCF at Lower-Sum of DCF at Higher
24% plus
30.60 multiplied 35.13
24% plus
8.71%
65% 70.45 41.67 24.78 14.81 8.88 5.34 6.07 172.00 200.00 (28.00)
45% 239.00 200
56.64%
10%
65% 172.00
45% plus
45%
High-Low
39.00 multiplied 67.00 11.64%
20%
1
2
3
4
5
6
7
Revenue Less: COGS EBITDA -Depreciation EBIT EBIT(1-t) Add: Dep Add: Fixed Assets Savaged Add: Working Capital Salvaged Less: Capital Gains on FA CFAT -200 IRR 55%
250 100 150 37.50 112.50 78.75 37.50
250 100 150 28.13 121.88 85.31 28.13
250 100 150 21.09 128.91 90.23 21.09
250 100 150 15.82 134.18 93.93 15.82
250 100 150 11.87 138.13 96.69 11.87
250 100 150 8.90 141.10 98.77 8.90
116.25
113.44
111.33
109.75
108.56
107.67
250 100 150 6.67 143.33 100.33 6.67 48.00 50.00 2.80 202.20
Opening BV Less: Depreciation Closing BV
150.00 37.50 112.50
112.50 28.13 84.38
84.38 21.09 63.28
63.28 15.82 47.46
47.46 11.87 35.60
35.60 8.90 26.70
26.70 6.67 20.02
IO Plant W/C
150 50
0
6
7
80.0 120.0 160.0 200.0 160.0 120.0 24.0 36.0 48.0 60.0 48.0 36.0 8.0 12.0 16.0 20.0 16.0 12.0 10.0 10.0 10.0 10.0 10.0 10.0 8.0 12.0 16.0 20.0 16.0 12.0 4.0 6.0 8.0 10.0 8.0 6.0 26.0 44.0 62.0 80.0 62.0 44.0 30.0 22.5 16.9 12.7 9.5 7.1 (4.0) 21.5 45.1 67.3 52.5 36.9 (2.8) 15.1 31.6 47.1 36.8 25.8 30.0 22.5 16.9 12.7 9.5 7.1 10 10 10 -10 -10 -10 Less: Incremental Investment in Non Cash W/C
80.0 24.0 8.0 10.0 8.0 4.0 26.0 5.3 20.7 14.5 5.3 -16 25 1.2 0.9 7 54.1
Capital Equipment Working Capital Total Initial Investment
120 20
1
2
3
4
5
120 20 140
Sales Raw Material 30% Variable Mfg Cost 10% Fxd A Op & Maint Cost Variable Selling Expenses 10% Incremental Overhead Allocation 5% EBITDA -Depreciation (see schedule below) EBIT EBIT(1-t) 30% Add: Depreciation
Add: Fixed Assets Salvaged Add: Tax Shield on Working Capital Loss Less: Capital Gains Tax on Fixed Assets 10% Less: Product Cannabilization Cash Flow after tax -140
7 10.2
7 20.6
7 31.5
Future Value interest factor
1
1.150
1.3225
1.5209
-140
8.87
15.54
20.69
Discounted Cash Flow Net Present Value
Internal Rate of Return
1.36 15.26%
7 62.8
7 49.2
7 35.9
1.7490
2.0114
2.3131
2.6600
35.90
24.48
15.54
20.34
1
2
3
4
5
6
7
120 25% 30 90 0 1 Working Capital 25% 20 30 Incremental Investment in Working Capital 10
90.0 22.5 67.5 2 40 10
67.5 16.9 50.6 3 50 10
50.6 12.7 38.0 4 40 -10
38.0 9.5 28.5 5 30 -10
28.5 7.1 21.4 6 20 -10
21.4 5.3 16.0 7
Opening Book Value Less: Depreciation Closing Book Value
Key Points: Reinvestment in Working Capital Salvage of Working Capital and Tax Shield Sale of Equipment at a Profit and consequent Tax treatment Product Cannabilization [Side Cost] Initial Investment [Every Rupee that goes out because we said yes to this project]
Total Funds { 450
Cost Long Equity { Equity Term 100 Term Loan 15% Explicit Cost Funds { Funds { Current 300 Short Term Loan 18% Liabilitie 400 Trade Credit s{ 1 500.0 320.0 180.0 83.3 96.8
2 500.0 320.0 180.0 55.5 124.5
3 500.0 320.0 180.0 37.0 143.0
4 500.0 320.0 180.0 24.7 155.3
100 200 100 50 450 5 500.0 320.0 180.0 16.5 163.5
18.0 30.0 48.8 14.6 34.1
18.0 28.5 78.0 23.4 54.6
18.0 22.5 102.5 30.7 71.7
18.0 16.5 120.8 36.2 84.6
18.0 10.5 135.0 40.5 94.5
18.0 4.5 146.5 44.0 102.6
Cash Inflow Salvage of Working Capital Salvage Value of Assets Total Cash Inflow Cash Outflow COGS Interest Tax Principal Repaid Term Loan Repaid Trade Credit Repaid Total Cash Outflow Surplus Add: Opening Cash Closing Cash
500.0
500.0
500.0
500.0
500.0
500.0
500.0
500.0
500.0
500.0
500.0 200.0 80.0 780.0
320.0 48.0 14.6 -
320.0 46.5 23.4 40
320.0 40.5 30.7 40
320.0 34.5 36.2 40
320.0 28.5 40.5 40
382.6 117.4 117.4
429.9 70.1 117.4 187.5
431.2 68.8 187.5 256.2
430.7 69.3 256.2 325.5
429.0 71.0 325.5 396.5
320.0 22.5 44.0 40 100 50.0 576.5 203.5 396.5 600.0
EBIT(1-t) Add: Dep Less: WC Leess: FA FCFF
67.73 83.3
87.13 55.5
100.07 37.0
108.71 24.7
114.47 16.5
118.31 11.0
150.98
142.66
137.11
133.41
130.94
129.30
260 120 100 50 530
4 345 80 100 50 575
5 440 40 100 50 630
6 600 600
250 176 74 200
250 201 49 200
250 217 33 200
250 228 -
Revenues Less: Op C EBITDA Less: Dep EBIT Interest Working Cap Term Loan EBT Less: Tax EAT
FCFE
Equity Term Loan Short Term Loan Trade Credit Total Capital
Balance Sheet 100 200 100 50 450
1 134 200 100 50 484
2 189 160 100 50 499
Fixed Assets Gross Less: Depreciation Net block of FA Working Capital
250 250 200
250 83 167 200
250 139 111 200
3
6 500.0 320.0 180.0 11.0 169.0
Cash Net Assets
450 Opening BV Less: Dep Closing BV
117 484 -
187 499 -
256 530 -
326 575 -
396 630 -
600 600 -
250.00 83.25 166.75
166.75 55.53 111.22
111.22 37.04 74.19
74.19 24.70 49.48
49.48 16.48 33.00
33.00 10.99 22.01 80 57.99
Capital Gain Term Loan i n
200 0.08 10 12 1 st year
Instalment No Time Month No Semi Annual Installment Balance O/S Interest
200 30
2nd year repaid 1 2 18 24 20 20 180 15
160 13.5 28.5
3rd year repaid 3 4 30 36 20 20 140 12
120 10.5 22.5
4th year repaid 5 42 20 100 9 16.5
Fixed Assets Current Assets
250 200
450
4th year repaid 6 48 20 80 7.5 16.5
5th year repaid 7 8 54 60 20 20 60 6
40 4.5 10.5
6th year repaid 9 10 66 72 20 20 20 3
0 1.5 4.5
You are examining the optimum capital structure for your firm and have collected the following information. Your firm currently does not employ and debt Rf 7% Tax 34% Rm 12% Debt Ratio Bond Rating KD β ke kd WACC MVE MVD 0% AAA 7.25% 1.2 200 0 10% AA 8.25% 180 20 20% A 9.25% 160 40 30% BBB 10.75% 140 60 40% BB 11.75% 120 80 50% B 12.75% 100 100 60% CCC 14% 80 120 70% CC 15% 60 140 80% C 16% 40 160 90% D 18% 20 180
MVF 200 200 200 200 200 200 200 200 200 200
0 Initial Outflow 1 2 3 4 5 Sum of DCF Less: NPV
Lower 24% 180.60 150
Sum of DCF IO
IRR=
Lower Rate plus
32.71%
1 2 3 4 5 6 7
-200 116 113 111 110 109 108 202
-150 50 60 70 80 90
45% 80.17 53.95 36.52 24.83 16.94 11.58 15.00 239.00 200 39.00
14%
24%
34%
32.71%
43.86 46.17 47.25 47.37 46.74 231.39 150 81.39
40.32 39.02 36.71 33.84 30.70 180.60 150.00 30.60
37.31 33.42 29.09 24.81 20.83 145.47 150.00 (4.53)
37.68 34.07 29.95 25.79 21.86 149.35 150.00 (0.65)
Higher 34% 145.47
Sum of the DCF at the Lower Rate-IO Sum of DCF at Lower-Sum of DCF at Higher
24% plus
30.60 multiplied 35.13
24% plus
8.71%
65% 70.45 41.67 24.78 14.81 8.88 5.34 6.07 172.00 200.00 (28.00)
45% 239.00 200
56.64%
10%
65% 172.00
45% plus
45%
High-Low
39.00 multiplied 67.00 11.64%
20%
Related Documents
Project Cash Flow Estimation
May 2020 8
Cash Flow Estimation & Their Types
June 2020 11
Cash Flow
December 2019 40
Cash Flow
November 2019 45
Cash Flow
November 2019 54