Scenario Summary
Current Values: Excellent Good bad worst
Changing Cells:
$O$3 3000 3000 3000 3000 3000
$O$4 3.1999997269 3.1999997269 3.1999997269 3.1999997269 3.1999997269
$O$5 5400 5400 5400 5400 5400
$O$6 1200 1200 1200 1200 1200
Result Cells:
$P$26 3,442.79 3,442.79 3,442.79 3,442.79 3,442.79
Notes: Current Values column represents values of changing cells at
time Scenario Summary Report was created. Changing cells for each
scenario are highlighted in gray.
excellent good
sales unit 3000 units 3300 3150
SP 3.199999727 SP/unit 3.35999971 3.007999743
VC 1.8 (unit) 5940 5400
FC 1200 (Rs) 1260 1140
Depreciation 1200 Probability 15% 20%
Tax 40%
Discount rate 15%
Life 10
Initial cost 8000
excellent good
Sales 11087.9991 9475.199191
Less: VC 5940 5400
Less: FC 1260 1140
EBITDA 3887.99905 2935.199191
Less: D&A 1200 1200
EBIT 2687.99905 1735.199191
Less: Tax@40% 1075.19962 694.0796766
EBIT(1-t) 1612.79943 1041.119515
Add: D&A 1200 1200
Operating cash flows 2812.79943 2241.119515
PV 14,116.79 11,247.66
NPV 6,116.79 3,247.66
Prob*NPV 917.52 649.53
Expected NPV
Cash Flows(X) Probability Cash Flow*P
2812.7994 15% 421.91991
2241.119515 20% 448.223903
2279.999508 30% 683.9998524
1646.3996 20% 329.27992
395.999336 15% 59.3999004
X bar 1942.823486
For 1 rs of return the risk is of 0.
normal bad worst For scenario analysis
3000 2850 2700 sales unit 3000
3.199999727 3.03999974 2.87999975 SP 3.1999997269
5400 5400 5940 VC 5400
1200 1320 1440 FC 1200
30% 20% 15% Depreciati 1200
Tax 40%
Discount r 15%
Life 10
Initial cost 8000
Normal
normal bad worst Sales 9599.99918
9599.999181 8663.99926 7775.99934 Less: VC 5400
5400 5400 5940 Less: FC 1200
1200 1320 1440 EBITDA 2999.99918
2999.999181 1943.99926 395.999336 Less: D&A 1200
1200 1200 1200 EBIT 1799.99918
1799.999181 743.999261 -804.000664 Less: Tax@40% 719.999672
719.9996723 297.599704 0 EBIT(1-t) 1079.99951
1079.999508 446.399556 -804.000664 Add D&A 1200
1200 1200 1200 Operating cash flows 2279.99951
2279.999508 1646.39956 395.999336
11,442.79 8,262.90 1,987.43 PV 11,442.79
3,442.79 262.90 -6,012.57 NPV 3,442.79
1,032.84 52.58 -901.89
1,750.58
(X-X bar) (X-x bar)2 (X-x bar)2*P
869.9759142 756858.091 113528.714
298.2960292 88980.521 17796.1042
337.1760222 113687.67 34106.301
-296.4238858 87867.1201 17573.424
-1546.82415 2392665 358899.743
variance 541904.285
SD 736.141485
COV 0.42
r 1 rs of return the risk is of 0.42 paise
sales unit 20000 sales unit
SP 3 SP
VC 2.1 VC
FC 8000 FC
Dep on Buliding 1600 Probability
Dep on Equipment 2000
Depreciation 3600
Tax 40% Sales
Discount rate 12% Less: VC
Life 4 Less: FC
Initial cost : EBITDA
Buiding 12000 Less: D&A
Equipment 8000 EBIT
Net Op WC 6000 Less: Tax@40%
26000 EBIT(1-t)
Add: D&A
Salvage Value on Building 5600 Operating cash flows
WC realised 6000
0
Optimistic Normal Pessimistic
26000 20000 14000
3.9 3 2.1
1.47 2.1 2.73
8000 8000 8000
25% 50% 25%
Optimistic Normal Pessimistic
101400 60000 29400
38220 42000 38220
8000 8000 8000
55180 10000 -16820
3600 3600 3600
51580 6400 -20420
20632 2560 0
30948 3840 -20420
3600 3600 3600
34548 7440 -16820
1 2 3 4 Total NPV NPV
25% 34548 34548 34548 46148
PV 30,846.43 27,541.45 24,590.58 29,327.89 112,306.35 86,306.35
50% 7440 7440 7440 19040
PV 6,642.86 5,931.12 5,295.65 12,100.26 29,969.89 3,969.89
25% -16820 -16820 -16820 -5220
PV -15,017.86 -13,408.80 -11,972.14 -3,317.40 -43,716.21 -69,716.21
Expected NPV 6,132.48
