Assuming that it would take one year for production facilities to come up
Five Year System
If project is sold after 5 years. Assuming a S.L Depreciation.
Depriciation rate (SL in years) 7
WC 0.1
Initial Cashflows
Initial Investment ₹ 100,000,000.00
NWC ₹12,000,000.00
Total 112000000
Terminal Cash-flows
Salvage value 65000000
WC recovered at end of project 19521830.10948
TCF excluding PV of tax savings 84521830.10948
Discounted TCF excluding PV of 44676303.7672495
Operating Cashflows
Particular Year 1 Year 2
Sales
BULB 3000000 3090000
Price 50 53
BULB Sales 150000000 163770000
Total Units 3000000 3090000
Total Sales 150000000 163770000
Variable Cost
variable cost for materials 10 10.6
variable cost others 20 21.2
per unit 30 31.8
total 90000000 98262000
Sales and General Exp ₹4,000,000.00 ₹ 4,200,000.00
Depreciation ₹ 14,285,714.29 14285714.2857143
EBIT ₹41,714,285.71 47022285.7142857
Tax @ 40% ₹ 16,685,714.29 18808914.2857143
EAT ₹ 25,028,571.43 28213371.4285714
Cashflow ₹ 39,314,285.71 42499085.7142857
WC 15000000 16377000
Used Working Capital ₹ 3,000,000.00 1377000
New Capital Investment
OFCFF -₹ 37,158,056.98 41122085.7142857
Discounted CF -32709557.2024231 31865333.7277184
NPV excluding PV of Tax savings 29917903.4322846
NPV with the PV of tax savings 26066841.7104981
SL Assignment
Payback 5
Discounted Payback 5
IRR 18.855985%
NPV 26066841.7104981
PI 0.521480374114941
MIRR 18.855985%
0 1
FCFF -112000000 -₹ 37,158,056.98
Discounted FCFF -112000000 -32709557.2024231
Payback -149158056.981953
Discounted payback -144709557.20
Sensitivity analysis
Sales 26066841.7104981
2910000 23404878.7516088 -10.21%
2940000 24292199.7379052 -6.81%
2970000 25179520.7242017 -3.40%
3000000 26066841.7104981 0.00%
3030000 26954162.6967946 3.40%
3060000 27841483.683091 6.81%
3090000 28728804.6693875 10.21%
Market growth 26066841.7104981
0.9991 232842208.88378 -3.78%
1.0094 235888677.603398 -2.52%
1.0197 238935146.323016 -1.26%
1.03 241981615.042633 0.00%
1.0403 245028083.762251 1.26%
1.0506 248074552.481869 2.52%
1.0609 251121021.201487 3.78%
inflation 26066841.7104981
0.0388 25992477.0843828 -0.29%
0.0392 26017271.306486 -0.19%
0.0396 26042059.5205169 -0.10%
0.04 26066841.7104981 0.00%
0.0404 26091617.8604441 0.10%
0.0408 26116387.9543609 0.19%
0.0412 26141151.9762466 0.29%
market share 26066841.7104981
0.2910897 12042481.5845212 -16.12%
0.29403 12814093.4436149 -10.75%
0.297 13585705.3027087 -5.37%
0.3 14357317.1618024
0.303 15128929.0208962 5.37%
0.306 15900540.87999 5.10%
0.309 16672152.7390837 4.85%
cost of capital 26066841.7104981 -8.13%
0.13192 28373497.5845495 2.80%
0.13328 27599591.1267263 2.87%
0.13464 26830718.9323408 2.93%
0.136 26066841.7104981 0.00%
0.13736 25307920.5274856 -2.91%
0.13872 24553916.8030766 -2.98%
0.14008 23804792.3068774 -3.05%
26066841.7104981 -5.30%
tax rate 0.388 27524496.9689755 1.80%
0.392 27038611.8828164 1.83%
0.396 26552726.7966573 1.86%
0.4 26066841.7104981 0.00%
0.404 25580956.624339 -1.86%
0.408 25095071.5381798 -1.90%
0.412 24609186.4520207 -1.94%
26066841.7104981 -1.46%
working capital 0.097 26451727.0338724 0.49%
0.098 26323431.926081 0.49%
0.099 26195136.8182895 0.49%
0.1 26066841.7104981 0.00%
0.101 25938546.6027067 -0.49%
0.102 25810251.4949152 -0.49%
0.103 25681956.3871238 -0.50%
26066841.7104981 -0.44%
0.194 26182373.5621517 0.15%
Capital gains Tax Rate 0.196 2614386294.49% 0.15%
0.198 2610535232.77% 0.15%
0.2 2606684171.05% 0.00%
0.202 2602833109.33% -0.15%
0.204 2598982047.61% -0.15%
0.206 2595130985.88% -0.15%
% change in bulb SP price per bulb #REF!
-3 0 6363001859.09946
-2 0 6358357094.4438
-1 0 6356081159.76252
0 ₹ 50.00 6356081159.76252
1 0 6358334335.09699
2 0 6362885749.2726
3 0 6369849412.96129
Tax Rate 0.4
Growth Factor of Inflation 106.00%
Cost of capital 0.136
Capital gains Tax Rate 0.2
(inflation) 0.04
bulbs 10000000
bulb market share 0.3
bulb mkt yr 1 3000000
Growth in Market Size 1.03
Depriciation ₹ 71,428,571.43
Depreciated value ₹ 28,571,428.57
Salvage value 65000000
Capital loss ₹ -36,428,571.43
Tax savings ₹ -7,285,714.29
PV of tax savings -3851061.72178651
Year 3 Year 4 Year 5
3182700 3278181 3376526.43
56.18 59.5508 63.123848
178804086 195218301.0948 213139341.135
3182700 3278181 3376526.43
178804086 195218301.0948 213139341.135
11.236 11.91016 12.6247696
22.472 23.82032 25.2495392
33.708 35.73048 37.8743088
107282451.6 117130980.65688 127883604.681
4410000 4630500 4862025
14285714.2857143 14285714.2857143 14285714.2857
52825920.1142857 59171106.1522057 66107997.1684
21130368.0457143 23668442.4608823 26443198.8674
31695552.0685714 35502663.6913234 39664798.301
45981266.3542857 49788377.9770377 53950512.5868
17880408.6 19521830.10948 0
1503408.6 1641421.50948 -19521830.109
44477857.7542857 48146956.4675577 73472342.6962
30339531.3102766 28910494.0653014 38835797.7642
2 3 4 5
41122085.7142857 44477857.7542857 48146956.4676 150708458.52
31865333.7277184 30339531.3102766 28910494.0653 79661039.8096
-108035971.267667 -63558113.5133812 -15411157.046 135297301.474
-112844223.47 -82504692.16 -53594198.10 26066841.71
Direction Magnitude
(+) 3.40%
Direction Magnitude
(+) 1.26%
Direction Magnitude
(+) 0.10%
Direction Magnitude
(+) 5.37%
Direction Magnitude
(-) -2.91%
Direction Magnitude
(-) -1.86%
Direction Magnitude
(-) -0.49%
Direction Magnitude
(-) -0.15%
% change in NPV
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
Assuming that it would take one year for production facilities to come up
Five Year System
If project is sold after 5 years. Assuming a S.L Depreciation.
Depriciation rate (SL in years) 7
WC 0.1
Initial Cashflows
Initial Investment ₹ 100,000,000.00
NWC ₹12,000,000.00
Total 112000000
Terminal Cash-flows
Salvage value 65000000
WC recovered at end of project 34163141.056512
TCF excluding PV of tax savings 99163141.056512
Discounted TCF excluding PV of 52415365.4341951
Operating Cashflows
Particular Year 1 Year 2
Sales
BULB 5100000 5304000
Price 50 53
BULB Sales 255000000 281112000
Total Units 5100000 5304000
Total Sales 255000000 281112000
Variable Cost
variable cost for materials 10 10.6
variable cost others 20 21.2
per unit 30 31.8
total 153000000 168667200
Sales and General Exp ₹4,000,000.00 ₹ 4,200,000.00
Depreciation ₹ 14,285,714.29 14285714.2857143
EBIT ₹83,714,285.71 93959085.7142857
Tax @ 40% ₹ 29,300,000.00 32885680
EAT ₹ 54,414,285.71 61073405.7142857
Cashflow ₹ 68,700,000.00 75359120
WC 25500000 28111200
Used Working Capital ₹ 13,500,000.00 2611200
New Capital Investment
OFCFF -₹ 78,722,586.23 72747920
Discounted CF -69298051.2573318 56372061.5949216
NPV excluding PV of Tax savings 105356059.120872
NPV with the PV of tax savings 101504997.399085
SL Assignment
Payback 4
Discounted Payback 5
IRR 29.615287%
NPV 101504997.399085
PI 1.90629461963469
MIRR 29.615287%
0 1
FCFF -112000000 -₹ 78,722,586.23
Discounted FCFF -112000000 -69298051.2573318
Payback -190722586.228329
Discounted payback -181298051.26
Sensitivity analysis
Sales 101504997.399085
4947000 96508192.653326 -4.92%
4998000 98173794.2352456 -3.28%
5049000 99839395.8171654 -1.64%
5100000 101504997.399085 0.00%
5151000 103170598.981005 1.64%
5202000 104836200.562925 3.28%
5253000 106501802.144844 4.92%
Market growth 101504997.399085
1.0088 97341955.7795721 -4.10%
1.0192 98736182.2932324 -2.73%
1.0296 100124056.482096 -1.36%
1.04 101504997.399085 0.00%
1.0504 102878415.831803 1.35%
1.0608 104243714.302534 2.70%
1.0712 105600287.068242 4.03%
inflation 101504997.399085
0.0388 101357032.346079 -0.15%
0.0392 101406364.895308 -0.10%
0.0396 101455686.589972 -0.05%
0.04 101504997.399085 0.00%
0.0404 101554297.291649 0.05%
0.0408 101603586.236646 0.10%
0.0412 101652864.203043 0.15%
market share 101504997.399085
0.32990166 96557994.1406253 -4.87%
0.333234 98190450.2510649 -3.27%
0.3366 99839395.8171654 -1.64%
0.34 101504997.399085
0.3434 103170598.981005 1.64%
0.3468 104836200.562925 1.61%
0.3502 106501802.144844 1.59%
cost of capital 101504997.399085 -3.50%
0.13192 105182738.901044 1.19%
0.13328 103948859.401637 1.19%
0.13464 102722966.198826 1.20%
0.136 101504997.399085 0.00%
0.13736 100294891.668101 -1.19%
0.13872 99092588.2250191 -1.20%
0.14008 97898026.8367517 -1.21%
101504997.399085 -2.45%
tax rate 0.3395 104053888.923557 0.82%
0.343 103204258.4154 0.83%
0.3465 102354627.907242 0.84%
0.35 101504997.399085 0.00%
0.3535 100655366.890928 -0.84%
0.357 99805736.3827706 -0.84%
0.3605 98956105.8746133 -0.85%
101504997.399085 -0.65%
working capital 0.097 102173910.852391 0.22%
0.098 101950939.701289 0.22%
0.099 101727968.550187 0.22%
0.1 101504997.399085 0.00%
0.101 101282026.247983 -0.22%
0.102 101059055.096882 -0.22%
0.103 100836083.94578 -0.22%
101504997.399085 -0.11%
0.194 101620529.250739 0.04%
Capital gains Tax Rate 0.196 10158201863.35% 0.04%
0.198 10154350801.63% 0.04%
0.2 10150499739.91% 0.00%
0.202 10146648678.19% -0.04%
0.204 10142797616.46% -0.04%
0.206 10138946554.74% -0.04%
% change in bulb SP price per bulb #REF!
-3 0 6363001859.09946
-2 0 6358357094.4438
-1 0 6356081159.76252
0 ₹ 50.00 6356081159.76252
1 0 6358334335.09699
2 0 6362885749.2726
3 0 6369849412.96129
Tax Rate 0.35
Growth Factor of Inflation 106.00%
Cost of capital 0.136
Capital gains Tax Rate 0.2
(inflation) 0.04
bulbs 15000000
bulb market share 0.34
bulb mkt yr 1 5100000
Growth in Market Size 1.04
Depriciation ₹ 71,428,571.43
Depreciated value ₹ 28,571,428.57
Salvage value 65000000
Capital loss ₹ -36,428,571.43
Tax savings ₹ -7,285,714.29
PV of tax savings -3851061.72178651
Year 3 Year 4 Year 5
5516160 5736806.4 5966278.656
56.18 59.5508 63.123848
309897868.8 341631410.56512 376614467.007
5516160 5736806.4 5966278.656
309897868.8 341631410.56512 376614467.007
11.236 11.91016 12.6247696
22.472 23.82032 25.2495392
33.708 35.73048 37.8743088
185938721.28 204978846.339072 225968680.204
4410000 4630500 4862025
14285714.2857143 14285714.2857143 14285714.2857
105263433.234286 117736349.940334 131498047.517
36842201.632 41207722.4791168 46024316.631
68421231.6022857 76528627.4612169 85473730.8861
82706945.888 90814341.7469312 99759445.1718
30989786.88 34163141.056512 0
2878586.88 3173354.176512 -34163141.057
79828359.008 87640987.5704192 133922586.228
54453049.6713918 52625221.5493422 70788412.1284
2 3 4 5
72747920 79828359.008 87640987.5704 225800012.999
56372061.5949216 54453049.6713918 52625221.5493 119352715.841
-117974666.228329 -38146307.220329 49494680.3501 275294693.349
-124925989.66 -70472939.99 -17847718.44 101504997.40
Direction Magnitude
(+)
1.64%
Direction Magnitude
(+) 1.35%
Direction Magnitude
(+) 0.05%
Direction Magnitude
(+) 1.64%
Direction Magnitude
(-) -1.19%
Direction Magnitude
(-) -0.84%
Direction Magnitude
(-) -0.22%
Direction Magnitude
(-) -0.04%
% change in NPV
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
MARKET DEMAND
MARKET SHARE
TAX RATE
DEMAND INCREASED
Sales 1.64
market growth 1.35
inflation 0.05
market share 1.64
cost of capital 1.19
tax rate 0.84
working capital 0.22
Capital gains Tax 0.04
Assuming that it would take one year for production facilities to come up
Five Year System
If project is sold after 5 years. Assuming a S.L Depreciation.
Depriciation rate (SL in years) 7
WC 0.1
Initial Cashflows
Initial Investment ₹ 100,000,000.00
NWC ₹12,000,000.00
Total 112000000
Terminal Cash-flows
Salvage value 65000000
WC recovered at end of project 14365052.4038
TCF excluding PV of tax savings 79365052.4038
Discounted TCF excluding PV of 41950549.1670389
Operating Cashflows
Particular Year 1 Year 2
Sales
BULB 2240000 2296000
Price 50 53
BULB Sales 112000000 121688000
Total Units 2240000 2296000
Total Sales 112000000 121688000
Variable Cost
variable cost for materials 10 10.6
variable cost others 20 21.2
per unit 30 31.8
total 67200000 73012800
Sales and General Exp ₹4,000,000.00 ₹ 4,200,000.00
Depreciation ₹ 14,285,714.29 14285714.2857143
EBIT ₹26,514,285.71 30189485.7142857
Tax @ 40% ₹ 10,605,714.29 12075794.2857143
EAT ₹ 15,908,571.43 18113691.4285714
Cashflow ₹ 30,194,285.71 32399405.7142857
WC 11200000 12168800
Used Working Capital -₹ 800,000.00 968800
New Capital Investment
OFCFF -₹ 23,626,148.05 31430605.7142857
Discounted CF -20797665.5386874 24355446.0566214
NPV excluding PV of Tax savings 7177089.56067143
NPV with the PV of tax savings 3326027.83888492
SL Assignment
Payback 5
Discounted Payback 5
IRR 14.341073%
NPV 3326027.83888492
PI 1.0296966771329
MIRR 14.341073%
0 1
FCFF -112000000 -₹ 23,626,148.05
Discounted FCFF -112000000 -20797665.5386874
Payback -135626148.051949
Discounted payback -132797665.54
Sensitivity analysis
Sales 3326027.83888492
2172800 1346289.29614398 -59.52%
2195200 2006202.14372429 -39.68%
2217600 2666114.9913046 -19.84%
2240000 3326027.83888492 0.00%
2262400 3985940.68646522 19.84%
2284800 4645853.53404554 39.68%
2307200 5305766.38162585 59.52%
Market growth 3326027.83888492
0.99425 1700314.27423028 -48.88%
1.0045 2244769.03931812 -32.51%
1.01475 2786748.14053697 -16.21%
1.025 3326027.83888492 0.00%
1.03525 3862381.23353739 16.13%
1.0455 4395578.26184768 32.16%
1.05575 4925385.69934663 48.09%
inflation 3326027.83888492
0.0388 3272505.78530921 -1.61%
0.0392 3290350.80713519 -1.07%
0.0396 3308191.49548071 -0.54%
0.04 3326027.83888492 0.00%
0.0404 3343859.82588119 0.54%
0.0408 3361687.44499688 1.07%
0.0412 3379510.68475353 1.61%
market share 3326027.83888492
0.27168372 1366020.69028662 -58.93%
0.274428 2012801.27220009 -39.48%
0.2772 2666114.9913046 -19.84%
0.28 3326027.83888492
0.2828 3985940.68646524 19.84%
0.2856 4645853.53404554 16.56%
0.2884 5305766.38162586 14.20%
cost of capital 3326027.83888492 -36.14%
0.13192 5208182.88270224 13.80%
0.13328 4576694.87802027 15.89%
0.13464 3949320.60152297 18.74%
0.136 3326027.83888492 0.00%
0.13736 2706784.66953338 -18.62%
0.13872 2091559.46360042 -22.73%
0.14008 1480320.87891113 -29.22%
3326027.83888492 -21.96%
tax rate 0.388 4261752.9923373 7.90%
0.392 3949844.60785317 8.57%
0.396 3637936.22336904 9.38%
0.4 3326027.83888492 0.00%
0.404 3014119.45440079 -9.38%
0.408 2702211.06991666 -10.35%
0.412 2390302.68543253 -11.54%
3326027.83888492 -7.87%
working capital 0.097 3610242.42087002 2.69%
0.098 3515504.22687499 2.77%
0.099 3420766.03287995 2.85%
0.1 3326027.83888492 0.00%
0.101 3231289.64488986 -2.85%
0.102 3136551.45089484 -2.93%
0.103 3041813.25689979 -3.02%
3326027.83888492 -3.36%
0.194 3441559.69053851 1.13%
Capital gains Tax Rate 0.196 340304907.33% 1.14%
0.198 336453845.61% 1.16%
0.2 332602783.89% 0.00%
0.202 328751722.17% -1.16%
0.204 324900660.44% -1.17%
0.206 321049598.72% -1.19%
% change in bulb SP price per bulb #REF!
-3 0 6363001859.09946
-2 0 6358357094.4438
-1 0 6356081159.76252
0 ₹ 50.00 6356081159.76252
1 0 6358334335.09699
2 0 6362885749.2726
3 0 6369849412.96129
Tax Rate 0.4
Growth Factor of Inflation 106.00%
Cost of capital 0.136
Capital gains Tax Rate 0.2
(inflation) 0.04
bulbs 8000000
bulb market share 0.28
bulb mkt yr 1 2240000
Growth in Market Size 1.025
Depriciation ₹ 71,428,571.43
Depreciated value ₹ 28,571,428.57
Salvage value 65000000
Capital loss ₹ -36,428,571.43
Tax savings ₹ -7,285,714.29
PV of tax savings -3851061.72178651
Year 3 Year 4 Year 5
2353400 2412235 2472540.875
56.18 59.5508 63.123848
132214012 143650524.038 156076294.367
2353400 2412235 2472540.875
132214012 143650524.038 156076294.367
11.236 11.91016 12.6247696
22.472 23.82032 25.2495392
33.708 35.73048 37.8743088
79328407.2 86190314.4228 93645776.6204
4410000 4630500 4862025
14285714.2857143 14285714.2857143 14285714.2857
34189890.5142857 38543995.3294857 43282778.4612
13675956.2057143 15417598.1317943 17313111.3845
20513934.3085714 23126397.1976914 25969667.0767
34799648.5942857 37412111.4834057 40255381.3624
13221401.2 14365052.4038 0
1052601.2 1143651.2038 -14365052.404
33747047.3942857 36268460.2796057 54620433.7662
23019759.7803519 21777889.6653143 28871110.43
2 3 4 5
31430605.7142857 33747047.3942857 36268460.2796 126699771.884
24355446.0566214 23019759.7803519 21777889.6653 66970597.8753
-104195542.337663 -70448494.9433775 -34180034.664 92519737.2205
-108442219.48 -85422459.70 -63644570.04 3326027.84
Direction Magnitude
(+) 19.84%
Direction Magnitude
(+) 16.13%
Direction Magnitude
(+) 0.54%
Direction Magnitude
(+) 19.84%
Direction Magnitude
(-) -18.62%
Direction Magnitude
(-) -9.38%
Direction Magnitude
(-) -2.85%
Direction Magnitude
(-) -1.16%
% change in NPV
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
MARKET SHARE
GROWTH IN MARKET SIZE
MARKET DEMAND
Sales 19.84
market growth 16.13
inflation 0.54
market share 19.84
cost of capital 18.62
tax rate 9.38
working capital 2.85
Capital gains Tax 1.16
Variable NPV relation normal case Best case Worst case
Sales + 3.4 1.64 19.84
market growth + 1.26 1.35 16.13
inflation + 0.1 0.05 0.54
market share + 5.37 1.64 19.84
cost of capital - 2.91 1.19 18.62
tax rate - 1.86 0.84 9.38
working capital - 0.49 0.22 2.85
Capital gains Tax Rate - 0.15 0.04 1.16
Worst case