Project Input Sheet for Transcendence Ltd.

Project Cost and Depreciation

Partiulars Rs. In Crs Dep%
Land 4000 0
Plant and Machinery 52000 10%
Building 3000 5%
Othe Civil work 3500 5%
Office Equipments 1000 10%
Electrical Work 1100 10%
Total Project Cost 64600
Project Construction Phase
1st Year Completion 60%
2nd Year Completion 40%
Production of Cars
Installed Capacity 300000 F-cars
Production in first operating year 50%
Ramp up in Production 8%
Capital Structure Details
Debt Proportion 40%
Equity Proportion 60%
Morotoium Period 2 Years
Loan Repayment Period 10 Years
Cost of Debt(Kd) 11% pa
Cost of Equity(Ke) 18% pa
Pricing Details
Sales Price for First Year of Operation 12000000 Per F cars
Hike in salesprice 20% Every 4th operating year
Domestic Inventory of F-cars 1.00 Month Production
Export details Quantity pa Selling price Exch. Rate
United States (Currency=$) 50000 175000 74
Euro-Zone (Currency = Euro) 55000 150000 88
Cost details and Current Assets and Liabilities
Material Cost 6500000
Hike in Material Cost 8% pa
Factory Overheads (Excluding Dep) 5% of Material Cost
Administrative overheads 5% of factory Cost
Marketing and Promotional expenses 3% of total Sales
Creditors for material 15 Days Credit
Credit offered to foreign dealers 3 months
Domestic Institutional Sales 5% of domestic Sales
Credit to Domestic Institutional Customers 2 months
Corporate Tax 30%
Appreciation pa
2%
2.50%
 Debt Modelling for Project F-Car
Total Project Cost (Rs. In Cr) 64600
Debt Proportion 40%
Amount of Debt (Rs. In Crores) 25840
Interest on Debt 11% pa
Morotorium Period 2 years
Loan Repayment Period 10 Years
Interest Accrued during the morotorium period 5997
Debt incl Morotorium Interest Repayable in 10 EAIs 31837
Equated Tenure Installments (EAI) ₹ 5,406

Loan Schedule
Particulars 1 2 3 4
Opening Balance 0 28682 31837 29934
Loan Taken 25840
Morotorium Interest Accrued 2842 3155
Principal repayment 1904 2113
Closing Balance 28682 31837 29934 27820

Interest paid as a part of EAI 3502 3293

hedule
5 6 7 8 9 10 11 12
27820 25474 22870 19980 16772 13211 9258 4870

2346 2604 2890 3208 3561 3953 4388 4870

25474 22870 19980 16772 13211 9258 4870 0

3060 2802 2516 2198 1845 1453 1018 536

 Fixed Asset Model f
Particulars Rs Pro-rata Interest Total Capex Rs. Dep. %
Land 4000 371 4371 0%
Plant & Machinery 52000 4828 56828 10%
Building 3000 279 3279 5%
Civil Work 3500 325 3825 5%
Office Equipment 1000 93 1093 10%
Electric Work 1100 102 1202 10%
Total Project Cost 64600 5997 70597
Moratorium Interest Accured 5997 Capital Expenditure

Calculation of Net Block of Year 1 & 2

Particulars Completion Capex. Mora Interest Net Block
Year 1 60% 38760 2842 41602
Year 2 40% 25840 3155 28995
Fixed Asset Model for Project F-Car (Rs in Crs)
1 2 3 4 5 6 7 8
0 0 0 0 0 0
5683 5683 5683 5683 5683 5683
164 164 164 164 164 164
191 191 191 191 191 191
109 109 109 109 109 109
120 120 120 120 120 120
Depreciation pa 6267 6267 6267 6267 6267 6267
Acc. Dep. 6267 12535 18802 25070 31337 37605
Net Block 41602 70597 64330 58063 51795 45528 39260 32993
9 10 11 12
0 0 0 0
5683 5683 5683 5683
164 164 164 164
191 191 191 191
109 109 109 109
120 120 120 120
6267 6267 6267 6267
43872 50139 56407 62674
26725 20458 14191 7923
 Revenue Model for Project F-Car
Installed Capacity 300000 F-Cars pa
Production in First Operating Year 50% of Installed Capacity
Ramp up in Production pa 8% pa
Finished Goods Inventory (Domestic) 1.00 Month
Selling Price in 1st Operating Year 12000000 per F-Car
Sales Price Hike 20% Every 4th Operating Year
Export details Quantity pa Selling price Exch. Rate
United States (Currency=$) 50000 175000 74
Euro-Zone (Currency = Euro) 55000 150000 88
Credit given to foreign dealers 3 Months
Domestic Institutional sales 5% of domestic sales
Credit to domestic Institutional Customers 2 Months

Production-Sales-Deb
Particulars 1 2 3
No. of Cars produced (Units) 150000
Finished goods Inventory 12500
No. of Fcars Sold (Units) 137500
No. of Fcars Exported 105000
Domestic Sale of Fcars (UNITS) 32500
Domestic Sales Price of Fcars per unit 12000000
Domestic Sales of Fcars (Rs in Crores) 39000

Exchange Rate (US Dollar = INR) 74

Price of Fcar in US $ 175000
Price of Fcar converted in INR 12950000
Fcars exported to US 50000
US Exports of Fcars (Rs. In Crores) 64750

Exchange Rate (Euro = INR) 88

Price of Fcar in Euro 150000
Price of Fcar converted in INR 13200000
Fcars exported to Euro Zone 55000
Euro Zone Exports of Fcars (Rs. In Crores) 72600

Total Sales of Fcars (Rs. In Crs) 176350

Domestic Institutional Sales 1950

Calculation of Debtors
Domestic Debtors 325
US Debtors 16188
Euro zone Debtors 18150
Total Debtors 34663
ting Year
Appreciation pa
2.0%
2.5%

Production-Sales-Debtors Schedule
4 5 6 7 8 9 10
162000 174960 188957 204074 220400 238032 257075
13500 14580 15746 17006 18367 19836 21423
161000 173880 187791 202814 219039 236563 255488
105000 105000 105000 105000 105000 105000 105000
56000 68880 82791 97814 114039 131563 150488
12000000 12000000 14400000 14400000 14400000 14400000 17280000
67200 82656 119219.04 140852 164216 189451 260043

75.48 76.99 78.53 80.10 81.70 83.34 85.00

175000 175000 175000 175000 175000 175000 175000
13209000 13473180 13742644 14017496 14297846 14583803 14875479
50000 50000 50000 50000 50000 50000 50000
66045 67366 68713 70087 71489 72919 74377

90.20 92.45 94.77 97.14 99.56 102.05 104.60

150000 150000 150000 150000 150000 150000 150000
13530000 13868250 14214956 14570330 14934588 15307953 15690652
55000 55000 55000 55000 55000 55000 55000
74415 76275 78182 80137 82140 84194 86299

207660 226297 266115 291076 317846 346563 420719

3360 4133 5961 7043 8211 9473 13002

560 689 993 1174 1368 1579 2167

16511 16841 17178 17522 17872 18230 18594
18604 19069 19546 20034 20535 21048 21575
35675 36599 37717 38730 39776 40857 42336
 11 12
277641 299852
23137 24988
275927 298001
105000 105000
170927 193001
17280000 17280000
295362 333506

86.70 88.44
175000 175000
15172989 15476449
50000 50000
75865 77382

107.22 109.90
150000 150000
16082918 16484991
55000 55000
88456 90667

459683 501555
14768 16675

2461 2779
18966 19346
22114 22667
43542 44792
 Material cost model for project F - car
Material cost per F - car 6500000
Hike in material cost pa 8%
Creditors for material (days) 15

Material cost and creditors schedule

Particulars 1 2 3 4 5 6
Material cost per F - car 6500000 7020000 7581600 8188128
No. of F - cars produced 150000 162000 174960 188957
Total material cost (Rs. In crores) 97500 113724 132648 154720
Creditors for material (Rs. In crores) 4007 4674 5451 6358
edule
7 8 9 10 11 12
8843178 9550632 10314683 11139858 12031046 12993530
204074 220400 238032 257075 277641 299852
180466 210496 245522 286378 334031 389614
7416 8651 10090 11769 13727 16012
 Cost of production model for project F - car
Factory overheads ( Excl. Dep.) 5% of material cost
Admn. Overheads 5% of factory cost

Cost of production schedule

Particulars 1 2 3 4 5 6
Total material cost (Rs. In crs) 97500 113724 132648 154720
Factory overheads (Excl. Dep.) 4875 5686 6632 7736
Dep. Per annum 6267 6267 6267 6267
Factory cost (total of 3 above) 108642 125678 145547 168724
Administrative expenses 5432 6284 7277 8436
Total cost of production (Rs. In crs.) 114075 131962 152825 177160
dule
7 8 9 10 11 12
180466 210496 245522 286378 334031 389614
9023 10525 12276 14319 16702 19481
6267 6267 6267 6267 6267 6267
195757 227288 264066 306964 357000 415362
9788 11364 13203 15348 17850 20768
205545 238653 277269 322312 374850 436130
 Inventory model for Project F - car
Particulars 1 2 3 4 5 6
No. of F - cars produced 150000 162000 174960 188957
Finished goods inventory 12500 13500 14580 15746
Production cost per F - car 7604971 8145773 8734846 9375681
Value of finished goods stock (Rs. in crs.) 9506 10997 12735 14763
t F - car
7 8 9 10 11 12
204074 220400 238032 257075 277641 299852
17006 18367 19836 21423 23137 24988
10072076 10828157 11648406 12537681 13501254 14544835
17129 19888 23106 26859 31238 36345
 Income statement model for Project F - Cars (Rs. In crs.)
Particulars 1 2 3 4 5 6
Total sales 176350 207660 226297 266115
Increase / (Decrease) in inventory 9506 1491 1739 2028
Total revenue (A) 185856 209151 228036 268142

Expenses
Total cost of production 114075 131962 152825 177160
Marketing & promotion expenses 5291 6230 6789 7983
Total cost (B) 119365 138191 159614 185143

EAT (A-B) 66491 70959 68422 82999

Less:- Int. on debt 3502 3293 3060 2802
PBT 62989 67667 65362 80196
Less: Tax 18897 20300 19609 24059
PAT 44092 47367 45753 56137
Cars (Rs. In crs.)
7 8 9 10 11 12
291076 317846 346563 420719 459683 501555
2366 2760 3218 3754 4378 5107
293442 320605 349781 424473 464061 506662

205545 238653 277269 322312 374850 436130

8732 9535 10397 12622 13790 15047
214277 248188 287666 334934 388641 451176

79165 72417 62115 89539 75421 55486

2516 2198 1845 1453 1018 536
76649 70219 60270 88086 74402 54950
22995 21066 18081 26426 22321 16485
53654 49154 42189 61660 52082 38465
 Balance Sheet for Project Fcar
Particulars 1 2 3 4 5 6 7
Equity and Liabilities
Share Capital 38760 38760 38760 38760 38760 38760 38760
Add: Reserves 44092 91459 137212 193350 247004
Net Worth (A) 38760 38760 82852 130219 175972 232110 285764
Long term Liabilities - Debt (B) 28682 31837 29934 27820 25474 22870 19980
Current Liabilities - Creditors ( C) 0 0 4007 4674 5451 6358 7416
Total Liabilities (A)+(B)+( C) 67442 70597 116793 162713 206898 261339 313161

Assets
Fixed Assets - (Net Block) 41602 70597 64330 58063 51795 45528 39260
Debtors 0 0 34663 35675 36599 37717 38730
Inventory 0 0 9506 10997 12735 14763 17129
Cash and Bank Balance 25840 0 8294 57978 105768 163331 218042
Total Assets 67442 70597 116793 162713 206898 261339 313161

Balance Sheet Difference 0 0 0 0 0 0 0

 8 9 10 11 12

38760 38760 38760 38760 38760

296158 338347 400007 452088 490553
334918 377107 438767 490848 529313
16772 13211 9258 4870 0
8651 10090 11769 13727 16012
360340 400407 459794 509446 545325

32993 26725 20458 14191 7923

39776 40857 42336 43542 44792
19888 23106 26859 31238 36345
267683 309719 370140 420476 456265
360340 400407 459794 509446 545325

0 0 0 0 0
 Cash Flow Statement For Project Fcar
Particulars 1 2 3 4 5 6
Opening Balance 25840 0 8294 57978 105768
PAT 0 0 44092 47367 45753 56137
Depreciation 0 0 6267 6267 6267 6267
Equity 38760
Loan Taken 25840
Total Cash Inflow 64600 25840 50360 61928 109999 168173

Cash Outflow
Loan Principal Repayment 0 0 1904 2113 2346 2604
Capital Expenditure 38760 25840
Net WC 0 0 40162 1836 1885 2239
Total Cash Outflow 38760 25840 42066 3950 4231 4843
Closing Balance 25840 0 8294 57978 105768 163331
ject Fcar
7 8 9 10 11 12
163331 218042 267683 309719 370140 420476
53654 49154 42189 61660 52082 38465
6267 6267 6267 6267 6267 6267

223252 273463 316140 377647 428489 465208

2890 3208 3561 3953 4388 4870

2320 2571 2859 3554 3626 4073

5210 5780 6420 7507 8013 8943
218042 267683 309719 370140 420476 456265
 Working Capital for Project Fcar
Particulars 1 2 3 4 5
Debtors 0 0 34663 35675 36599
Inventory 0 0 9506 10997 12735
Creditors 0 0 4007 4674 5451
Net Working Capital 0 0 40162 41998 43883

Change in NWC - Note

roject Fcar
6 7 8 9 10 11 12
37717 38730 39776 40857 42336 43542 44792
14763 17129 19888 23106 26859 31238 36345
6358 7416 8651 10090 11769 13727 16012
46122 48442 51013 53873 57427 61052 65125
 Weighted Average Cost of Capital Model for Project F-Car
Sources of Capital Amount Pre-Tax COC Tax Rate Post-Tax COC
Equity 38760 18% 18%
Debt 25840 11% 30% 7.7%
64600

WACC 13.88%
Product
6976.8
1989.68
8966.48
 Project Evaluation Model for Project F-Car
Evaluation of Project Cashflow(FCFF) (Rs. In Crores)
Particulars 1 2 3 4
PAT 0 0 44092 47367
Add: Depriciation 0 0 6267 6267
Add: Post -Tax Interest 0 0 2451 2305
Less: Capex 38760 25840 0 0
Less: Net Changes in Working Capital 0 0 40162 1836
FCFF -38760 -25840 12649 54103

NPV @ WACC ₹ 139,954.98

IRR 48.40%
NPV @ IRR ₹ 0.00

Evaluation of Equity Cashflow(FCFE) (Rs. In Crores)

Particulars 1 2 3 4
PAT 0 0 44092 47367
Add:Depriciation 0 0 6267 6267
Less: Net Changes in Working Capital 0 0 40162 1836
Less: Capex (Only Equity Contribution) 23256 15504 0 0
Less: Loan Principle Repayment 0 0 1904 2113
FCFE -23256 -15504 8294 49684

NPV @ WACC ₹ 106,508.37

IRR 63.34%
NPV @ IRR ₹ 0.00
Model for Project F-Car
hflow(FCFF) (Rs. In Crores)
5 6 7 8 9 10 11 12
45753 56137 53654 49154 42189 61660 52082 38465
6267 6267 6267 6267 6267 6267 6267 6267
2142 1962 1761 1538 1291 1017 713 375
0 0 0 0 0 0 0 0
1885 2239 2320 2571 2859 3554 3626 4073
52278 62128 59363 54388 46889 65391 55436 41035

hflow(FCFE) (Rs. In Crores)

5 6 7 8 9 10 11 12
45753 56137 53654 49154 42189 61660 52082 38465
6267 6267 6267 6267 6267 6267 6267 6267
1885 2239 2320 2571 2859 3554 3626 4073
0 0 0 0 0 0 0 0
2346 2604 2890 3208 3561 3953 4388 4870
47790 57562 54711 49641 42036 60421 50336 35790
 Financial Statement Analysis for Project Fcar
Particulars 1 2 3 4 5 6
Current Ratio 13.09 22.39 28.45 33.94
Quick Ratio 10.72 20.04 26.12 31.62
Debt Equity Ratio 0.74 0.82 0.36 0.21 0.14 0.10
Net Profit Ratio 25.00% 22.81% 20.22% 21.10%
Interest Coverage Ratio 18.99 21.55 22.36 29.62
ROCE 41.27% 31.43% 23.78% 22.79%
Project Fcar
7 8 9 10 11 12
36.93 37.84 37.04 37.33 36.08 33.56
34.62 35.54 34.75 35.05 33.80 31.29
0.07 0.05 0.04 0.02 0.01 0.00
18.43% 15.46% 12.17% 14.66% 11.33% 7.67%
31.47 32.95 33.67 61.62 74.06 103.57
18.12% 14.41% 11.14% 13.99% 10.65% 7.34%
 Sensitivity Analysis Model for Project Fcar
Particulars Basic 40% IRR Debt 60%
Project IRR 48.40% 40.01%
Equity IRR 63.34% 52.97%
Project NPV ₹ 139,954.98 ₹ 85,657.34
Equity NPV ₹ 106,508.37 ₹ 65,742.22
12th Years R&S 490553 322912
12th Years Cash Balance 456265 289030
12th Years BS Difference 0 0
12th Years Loan Balance 0 0