Data Analysis

Data Analysis and Interpretation

CCL Project A: -

Capital expenditure Land & Resources

20cr

Plant & Machinery

13cr

R&R

18cr

HEMM Spares

10cr

Others

20cr

TOTAL

81cr

Expenses/year Salary & Wages

08cr

Spares

03cr

Contractual Expenses

12cr

Power & Fuel Expenses Others

12cr 10cr

Administrative Expense

52/Ton

Transportation

15.5/Ton

Life of Project: - 20 Years Total Production: - 300 Million Tons Year 1st 2nd – 5th 6th – 17th 18th 19th 20th

Production Nil 2 Million Tons/year Equal 3 Million Tons/year 2 Million Tons/year 2 Million Tons/year

Coal Grade G9 G10

Production % 50 50

Non Power % 60 60

Power % 40 40

Grade of coal prices: Coal Grade

Power (Rs./Tonne)

G1 G2 G3 G4 G5 G6 G7 G8 G9 G10 G11 G12

Non Power (Rs./Tonne)

0 3450 3210 3000 2750 1900 1600 1420 1100 980 810 760

0 3450 3210 3000 2750 2280 1920 1700 1320 1180 970 910

Evaluation of the project by using different techniques of Capital Budgeting: In Central Coalfields Limited the Techniques of Capital Budgeting used are:    

Net Present value Internal rate of return Payback Period Profitability ratio

Analysis of the project by using Net Present Value: STEPS:   

Computation of Net cash Outflow using discount rate. Computation of Net cash Inflow Using discount rate. Net Present Value= Present value of cash inflow – Present value of Cash outflow of the project Total Production (Cr. ton) Discount Rate: Capital Expense (Rs. Cr)

30 6% 81

Step 1: - Calculating Net present Cash Outflow of the Project

Capital Year exp S & W Spares Contractor 1 81 8 3 12 2 0 8 3 12 3 0 8 3 12 4 0 8 3 12 5 0 8 3 12 6 0 8 3 12 7 0 8 3 12 8 0 8 3 12 9 0 8 3 12 10 0 8 3 12 11 0 8 3 12 12 0 8 3 12 13 0 8 3 12 14 0 8 3 12 15 0 8 3 12 16 0 8 3 12 17 0 8 3 12 18 0 8 3 12 19 0 8 3 12 20 0 8 3 12

P&F Admin exp Transportation Others TOTAL PV @ 6% 12 0 0 10 126 0.94340 12 3.1 10.4 10 58.5 0.89000 12 3.1 10.4 10 58.5 0.83962 12 3.1 10.4 10 58.5 0.79209 12 3.1 10.4 10 58.5 0.74726 12 36.8125 123.5 10 205.3125 0.70496 12 36.8125 123.5 10 205.3125 0.66506 12 36.8125 123.5 10 205.3125 0.62741 12 36.8125 123.5 10 205.3125 0.59190 12 36.8125 123.5 10 205.3125 0.55839 12 36.8125 123.5 10 205.3125 0.52679 12 36.8125 123.5 10 205.3125 0.49697 12 36.8125 123.5 10 205.3125 0.46884 12 36.8125 123.5 10 205.3125 0.44230 12 36.8125 123.5 10 205.3125 0.41727 12 36.8125 123.5 10 205.3125 0.39365 12 36.8125 123.5 10 205.3125 0.37136 12 4.65 15.6 10 65.25 0.35034 12 3.1 10.4 10 58.5 0.33051 12 3.1 10.4 10 58.5 0.31180

Net present value of total cash outflow rs.(cr)

Present value 118.8679 52.0648 49.1177 46.3375 43.7146 144.7372 136.5445 128.8156 121.5242 114.6454 108.1561 102.0340 96.2585 90.8099 85.6697 80.8205 76.2458 22.8599 19.3350 18.2406

1656.7995

Step 2: - Calculating Net present Cash Inflow of the project Production

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

Production cr. tonne 0 0.2 0.2 0.2 0.2 2.375 2.375 2.375 2.375 2.375 2.375 2.375 2.375 2.375 2.375 2.375 2.375 0.3 0.2 0.2

50%

Discount rate:

50%

G9

G10

0 0.1 0.1 0.1 0.1 1.1875 1.1875 1.1875 1.1875 1.1875 1.1875 1.1875 1.1875 1.1875 1.1875 1.1875 1.1875 0.15 0.1 0.1

0 0.1 0.1 0.1 0.1 1.188 1.188 1.188 1.188 1.188 1.188 1.188 1.188 1.188 1.188 1.188 1.188 0.15 0.1 0.1

6%

G9 G10 power N.P 60% 40% N.P 60% Power 40% TOTAL Cr.rs PV @ 6% Present value 0 0 0 0 0 0.94340 0 79.2 44 70.8 39.2 233.2 0.89000 207.54717 79.2 44 70.8 39.2 233.2 0.83962 195.79922 79.2 44 70.8 39.2 233.2 0.79209 184.71624 79.2 44 70.8 39.2 233.2 0.74726 174.26061 940.5 522.5 840.75 465.5 2769.25 0.70496 1952.21198 940.5 522.5 840.75 465.5 2769.25 0.66506 1841.70941 940.5 522.5 840.75 465.5 2769.25 0.62741 1737.46171 940.5 522.5 840.75 465.5 2769.25 0.59190 1639.11482 940.5 522.5 840.75 465.5 2769.25 0.55839 1546.33474 940.5 522.5 840.75 465.5 2769.25 0.52679 1458.80635 940.5 522.5 840.75 465.5 2769.25 0.49697 1376.23241 940.5 522.5 840.75 465.5 2769.25 0.46884 1298.33246 940.5 522.5 840.75 465.5 2769.25 0.44230 1224.84195 940.5 522.5 840.75 465.5 2769.25 0.41727 1155.51127 940.5 522.5 840.75 465.5 2769.25 0.39365 1090.10497 940.5 522.5 840.75 465.5 2769.25 0.37136 1028.40092 118.8 66 106.2 58.8 349.8 0.35034 122.55026 79.2 44 70.8 39.2 233.2 0.33051 77.07563 79.2 44 70.8 39.2 233.2 0.31180 72.71286

NPV of total cash inflow Rs.(cr)

18383.725

Step 3:- Calculation of Net Present value of the Project Net Present Value = Present value of cash Inflow – Present value of cash outflow NPV of total cash inflow 18383.725

NPV of total cash Outflow

Net Present Value Rs.(cr)

1656.7995

16726.925

Analysis of the project by using Internal rate of Return:Internal rate of return is the discount rate at which the total cash inflow is equal to the total cash outflow. Internal rate of return is calculated on the basis of trail and errors. By taking various discounting factor, so that the appropriate percentage of internal rate of return can be judged out. At 39.02% Total cash inflow is equal to total cash outflow. Year 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 TOTAL Rs. Cr

Cash Outflow 118.8679 52.0648 49.1177 46.3375 43.7146 144.7372 136.5445 128.8156 121.5242 114.6454 108.1561 102.0340 96.2585 90.8099 85.6697 80.8205 76.2458 22.8599 19.3350 18.2406

1656.80

Cash Inflow 0 207.5471698 195.7992168 184.7162423 174.2606059 1952.211977 1841.709412 1737.461709 1639.11482 1546.334736 1458.806355 1376.23241 1298.332462 1224.841946 1155.511269 1090.104971 1028.400916 122.5502581 77.07563405 72.71286231

18383.72

Cash Inflow @ 39.02% 0 120.6586212 86.79071456 62.42925754 44.90586598 383.5761539 275.909406 198.4638501 142.7566402 102.685997 73.86286176 53.13014926 38.21694277 27.48975365 19.77360043 14.22330948 10.23094066 DISCOUNT RATE 0.929582871 0.445770946 INTERNAL RATE OF RETURN 0.320646619

1656.80

39.02%

Analysis of the project by using payback period method:Payback period is the numbers of years at which project repays its total coast of investment through cash inflows. Payback of the project starts from the initial year of the project cash inflow. Suggestions: Any project which has a payback period of 3 to 5 years is considered as a good project. Cash Outflow for calculating Payback period of the project is 1656.7994 Rupees (crores). Cumulative Cash flow of the payback is the subtraction of all the cash inflow from the cash Outflow in a row one by one.

Payback period of the project is 5.55 years

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

CASH FLOWS 1656.799487 0 207.5471698 195.7992168 184.7162423 174.2606059 1952.211977 1841.709412 1737.461709 1639.11482 1546.334736 1458.806355 1376.23241 1298.332462 1224.841946 1155.511269 1090.104971 1028.400916 122.5502581 77.07563405 72.71286231

Cumulative CF of Payback Period(yr) payback 1656.799487 1656.799487 1 1449.252317 1 1253.453101 1 1068.736858 1 894.4762523 1 -1057.735724 0.549823234 -2899.445136 5.550 -4636.906846

Analysis of the project by using Profitability Index Ratio:Profitability Ratio is the ratio of total cash Inflow by total cash outflow of the project.

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

Cash Outflow

Cash Inflow

118.8679 52.0648 49.1177 46.3375 43.7146 144.7372 136.5445 128.8156 121.5242 114.6454 108.1561 102.0340 96.2585 90.8099 85.6697 80.8205 76.2458 22.8599 19.3350

0 207.5472 195.7992 184.7162 174.2606 1952.2120 1841.7094 1737.4617 1639.1148 1546.3347 1458.8064 1376.2324 1298.3325 1224.8419 1155.5113 1090.1050 1028.4009 122.5503 77.0756

18.2406

72.7129

1656.799

18383.725

Total Cash outflow of the project is 1656.799 Total Cash Inflow of the project is 18383.725 Profitability index of the project is 11.0959

Profitability Index Total Cash Inflow/Total cash outflow

11.0959

CCL Project B

Capital expenditure Land & Resources

15cr

Plant & Machinery

20cr

YEAR 1 2 3

HEMM Spares 5cr 3cr 1cr

Revenue expenditure/year Salary & Wages

3cr

Petroleum, oil & Lubricants Contractor

0.50Lakh 2cr

Electricity Transportation

0.50Lakh 10cr

Others

10cr

Life of Project: - 15 Years Total Production: - 25 Million Tons Year 1st 2nd – 6th 6th – 15th

Coal Grade G9 G10

Production Nil 1 Million Tons/year Equal

Production % 60 40

Non Power % 100 100

Power % 0 0

Grade of coal prices: Coal Grade G1 G2 G3 G4 G5 G6 G7 G8 G9 G10 G11 G12

Power (Rs./Tonne)

Non Power (Rs./Tonne)

0 3450 3210 3000 2750 1900 1600 1420 1100 980 810 760

0 3450 3210 3000 2750 2280 1920 1700 1320 1180 970 910

Analysis of the project by using Net Present Value: STEPS:   

Computation of Net cash Outflow using discount rate. Computation of Net cash Inflow Using discount rate. Net Present Value= Present value of cash inflow – Present value of Cash outflow of the project Total Production (Million Tons) Discount Rate: Capital Expense (Rs. Cr)

25 6% 35

Step 1: - Calculating Net present Cash Outflow of the Project

Present Year L & R P&M HEMM Wages POL Contractor Electricity Transportation Others TOTAL DF @ 6 % value 1 15 20 5 3 0.005 2 0.005 5 10 60.01 0.9434 56.6132075 2 3 3 0.005 2 0.005 5 10 23.01 0.8900 20.4788181 3 1 3 0.005 2 0.005 5 10 21.01 0.8396 17.6404011 4 3 0.005 2 0.005 5 10 20.01 0.7921 15.8497942 5 3 0.005 2 0.005 5 10 20.01 0.7473 14.952636 6 3 0.005 2 0.005 5 10 20.01 0.7050 14.1062604 7 3 0.005 2 0.005 5 10 20.01 0.6651 13.3077928 8 3 0.005 2 0.005 5 10 20.01 0.6274 12.5545216 9 3 0.005 2 0.005 5 10 20.01 0.5919 11.8438883 10 3 0.005 2 0.005 5 10 20.01 0.5584 11.1734795 11 3 0.005 2 0.005 5 10 20.01 0.5268 10.5410184 12 3 0.005 2 0.005 5 10 20.01 0.4970 9.94435697 13 3 0.005 2 0.005 5 10 20.01 0.4688 9.38146884 14 3 0.005 2 0.005 5 10 20.01 0.4423 8.8504423 15 3 0.005 2 0.005 5 10 20.01 0.4173 8.34947387

Net Present value of an outflow Rs.Cr

235.5876

Step 2: - Calculating Net present Cash Inflow of the project

60%

Year Production G9 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

0 0.1 0.1 0.1 0.1 0.1 0.222 0.222 0.222 0.222 0.222 0.222 0.222 0.222 0.222

0 0.06 0.06 0.06 0.06 0.06 0.1333 0.1333 0.1333 0.1333 0.1333 0.1333 0.1333 0.1333 0.1333

Discount rate:

40%

G11 0 0.04 0.04 0.04 0.04 0.04 0.0889 0.0889 0.0889 0.0889 0.0889 0.0889 0.0889 0.0889 0.0889

G9 0 79.2 79.2 79.2 79.2 79.2 176 176 176 176 176 176 176 176 176

G11

TOTAL

0 38.8 38.8 38.8 38.8 38.8 86.222 86.222 86.222 86.222 86.222 86.222 86.222 86.222 86.222

0 118 118 118 118 118 262.22222 262.22222 262.22222 262.22222 262.22222 262.22222 262.22222 262.22222 262.22222

Net Present value of an Inflow Rs.Cr

6%

DF @ Present 6% value 0.9434 0 0.8900 105.0195799 0.8396 99.0750754 0.7921 93.46705226 0.7473 88.1764644 0.7050 83.18534377 0.6651 174.3927542 0.6274 164.5214663 0.5919 155.2089304 0.5584 146.4235193 0.5268 138.1353955 0.4970 130.3164109 0.4688 122.9400103 0.4423 115.9811418 0.4173 109.4161715

1726.2593

Discount rate 6% is taken to calculate the present value of cash inflow per year of the project

Step 3:- Calculation of Net Present value of the Project Net Present Value = Present value of cash Inflow – Present value of cash outflow NPV of total cash inflow

NPV of total cash Outflow

1726.2593

Net Present Value Rs.(cr)

235.5876

1490.6718

Analysis of the project by using Internal rate of Return:-

YEAR

Cash Outflow 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

Total Rs.cr

56.61321 20.47882 17.64040 15.84979 14.95264 14.10626 13.30779 12.55452 11.84389 11.17348 10.54102 9.94436 9.38147 8.85044 8.34947

235.588

Cash Inflow 0 105.01958 99.07508 93.46705 88.17646 83.18534 174.39275 164.52147 155.20893 146.42352 138.13540 130.31641 122.94001 115.98114 109.41617

1726.259

Cash Inflow @ 42% DF 0 58.4378814 41.12450565 28.94055918 20.36634732 14.33241496 22.41367588 15.77318202 11.10006553 7.811452037 5.497155197 3.868514472 2.722390707 1.915828728 1.348226655

235.652

DISCOUNT RATE INTERNAL RATE OF RETURN 42%

Total Cash outflow of the project is 235.588 Total Cash Inflow at 6% discount rate is 1726.259 By Trial and Error method, at 42.09% discount factor the Total cash inflow is equal to total cash outflow of the project.

Analysis of the project by using Payback period method:-

YEAR 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

Cash Flows Cumulative CF of payback Payback Period(year) 235.5875599 235.5875599 0 235.5875599 1 105.0195799 130.56798 1 99.0750754 31.49290458 1 93.46705226 -61.97414768 0.40433 88.1764644 -150.1506121 3.40433 83.18534377 -233.3359558 174.3927542 -407.7287101 164.5214663 155.2089304 146.4235193 138.1353955 130.3164109 122.9400103 115.9811418 109.4161715

The total cash outflow of the project is 235.5875599 Crore rupees. Cumulative cash flow of Payback is calculates by subtracting the cash outflow from the cash Inflow one by one in a column until cash inflow reaches negative in number. Payback period is calculated by adding all the years of payback period year column. For the 15 year project the payback period is 3.40 year which means that all the initial cash outlay can be recovered with the payback time period. So the project is viable for the Organization.

Analysis of the project by using Profitability Index Ratio:-

YEAR

Cash Outflow 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

Total Rs.Cr

56.61320755 20.47881808 17.64040114 15.8497942 14.95263604 14.10626041 13.30779284 12.55452155 11.84388826 11.17347949 10.54101838 9.944356965 9.381468835 8.850442297 8.349473865 235.58756

Cash Inflow 0 105.0195799 99.0750754 93.46705226 88.1764644 83.18534377 174.3927542 164.5214663 155.2089304 146.4235193 138.1353955 130.3164109 122.9400103 115.9811418 109.4161715 1726.2593

Profitability Index Total Cash Inflow/Total Cash outflow 7.3275

Total Cash outflow of the project is 235.58756 Total Cash Inflow of the project is 1726.2593 Profitability index for the project is 7.3275 which is obtained by dividing total cash inflow into total cash Outflow.

FINDINGS & SUGGESTIONS

Findings:CCL Project A  

The total cash Outflow for the project of 20 years is 1656.7995 Crore Rupees. The total cash Inflow for the project of 20 years is 18383.725 Crore rupees.

 The Net Present value of the project is 16726.925 Crore Rupees which is positive in number so the project is financially viable for the organization.

 The Internal rate of return or opportunity cost of capital for the project is 39.02% which is much higher than the company required rate of return which is 12% so this also ensures a profitable investment in project.

 The profitability Index of the investment is 11.0959 and accepted if ratio is (PI > 1).   

It has been found that the Payback Period of the investment 5.5 years. The Payback Period shows that the initial investment can be recovered within a short period of time. The investment is ideal because for 20 year project an investment should be recovered within 8-10 years.

CCL Project B  

The total cash Outflow for the project of 15 years is 235.5876 Crore Rupees. The total cash Inflow for the project of 15 years is 1726.2593 Crore Rupees.

 The Net Present value of the project is 1490.6718 Crore Rupees  Positive value of Net present value determines the greater feasibility of the project so the investment can be done by the company.

 The Internal rate of return or opportunity cost of capital for the project is 42% which is much higher than the company required rate of return which is 12% so this also ensures a profitable investment in project.

 The Profitability Index ratio of the investment is 7.3275  For acceptance of the project the Profitability Index should be greater than 1 and the ratio of investment is much higher than the accepted standards so it is a valuable investment.

 The Payback Period of the investment 3.4 years.  Since the capital invested in a project of 15 years are recovered within a short period of 3.4 years so the proposal is viable for the company.

SUGGESTIONS

   

  



The company cash inflow is much higher than the cash outflow in all the years expect in 1st year which is nil, so company can increase its profitability by focusing on 1st year. The company can use its opportunity cost of capital for expanding their business by investing in other future projects. The company may plan and control its capital expenditure. The company can use other capital budgeting techniques such as Profitability index, Accounting rate of return and discounting payback period method for evaluation of their investment. The company cash inflow of the investment is lower in certain year so they can maintain constant inflow by increasing their coal production. Both the investments are valuable for the company and they can generate huge profits in the future so the investment should be accepted by the organization. The company should reduce the year of the project because long term projects more than 10 years have higher unpredictability rate and chances of uncertainty of accurate cash Inflow in future. The company can increase its required rate of return which is now 12% to find out a suitable investment with higher earnings.

LIMITATIONS          

The period of the study is limited. Financial information are sensitive in nature, and difficult to acquire by an individual. Restrictions imposed by the management while acquiring the financial data of the organization. Present data of cash inflows were secured by the company. The study was conducted on the basis of the data made available by the project report Guide and analysis were made accordingly. Any limitations on data shall be equally applicable in the project. Data were made available for the future projects and analyzed accordingly. Certain data and information given in the research are assumed due to highly confidential of such information. Assumptions have been taken regarding analysis and interpretations of project due to lack of proper data. Data is collected only for two projects which is limited.

 Data used for this study are confined to Central coalfields Limited and not applicable to any other company.