XIRR
Date 1/1/2024 2/3/2024 3/5/2024 4/11/2024 5/1/2025 6/25/2025
Cash flows -5000 -5000 -5000 -5000 -5000 -5000
XIRR 3.53%
Pbm 2
Date 1/1/2024 7/15/2024 12/31/2024 6/1/2025
Cash flows -50000 -25000 15000 100000
XIRR 43.19%
Pbm 3
Date 6/8/2018 8/8/2019 12/8/2020 6/8/2021 6/8/2022 1/8/2023
Cash flows -800 100 200 300 400 500
XIRR 19.58%
7/1/2025
31000
d Date of cash flow
n Number of periods
P Cash flow
rate XIRR
Problem
Sensitivity Analysis and Break-Even Point We are evaluating a
project that costs $588,000, has an eight-year life, and has no salvage
value. Assume that depreciation is straight-line to zero over the life of the
project. Sales are projected at 70,000 units per year. Price per unit is $36,
variable cost per unit is $20, and fixed costs are $695,000 per year. The
tax rate is 35 percent, and we require a 15 percent return on this project.
a. Calculate the accounting break-even point.
b. Calculate the base-case cash flow and NPV. What is the sensitivity of
NPV to changes in the sales figure? Explain what your answer tells you
about a 500-unit decrease in projected sales
c. What is the sensitivity of OCF to changes in the variable cost figure?
Explain what your answer tells you about a $1 decrease in estimated
variable costs.
d. Calculate the financial break-even and observe how it is different from
Accounting break-even
Scenario Analysis In the same problem, suppose the projections given
for price, quantity, variable costs, and fixed costs are all accurate to
within +/-10 percent. Calculate the best-case and worst-case NPV figures.
Solution a)
Cost of the Project
Life of the project in years
Depreciation
Projected Sales Volume
Price per unit
Variable cost per unit
Fixed Costs
Tax rate
Required rate of return
Contribution per unit
Accounting Break even Volume
Break even in Units
Solution b)
Calculation of OCF using the tax shield approach
OCFbase = [(P – v)Q – FC](1 – tc) + tcD
Base OCF
Now we can calculate the NPV using our base-case projections. There is
no salvage value or NWC, so the NPV is:
Cash Flows
NPV
Sensitivity Analysis for change in Sales Volume
Using One variable Data Table
Sales Volume
70,000.00
68500
69000
69500
70000
70500
71000
71500
Sensitivity of NPV = Change in NPV/Change in Sales Volume
Change in NPV for a drop of 500 units in sales
Solution c)
Sensiivity Analysis for change in Variable Cost on OCF
Variable cost per unit
$ 20.00
₹ 17.00
₹ 18.00
₹ 19.00
₹ 20.00
₹ 21.00
₹ 22.00
₹ 23.00
Sensitivity of OCF = Change in OCF/Change in Variable Cost
Change in OCF for a decrease of VC by $1
Financial Break even
Sales volume at which NPV will be zero
EAC = PV of costs/PVIFA
Fixed cost
Depreciation
Tax rate
Financial BE
Alternate for OCF calculation
₹ 588,000.00 Revenue
8 VC
₹ 73,500.00 Contribution
70,000.00 FC
₹ 36.00 Depreciation
₹ 20.00 PBT
₹ 695,000.00 Tax
35% PAT
15% OCF
$ 16.00 NPV
(Fixed Cost+Depreciation)/Contribution per unit
48,031
$ 301,975.00
Year 0 Years 1-8 Steps
$ (588,000.00) $ 301,975.00 Fill the first row with the reference to
$767,058.91 Choose the range of cells
Select Data - What if analysis - Data t
Give the column input as the input va
Output will be
NPV Sensitivity of NPV Sales Volume
$767,058.91 70,000
$697,056.70 46.67 68,500
$720,390.77 46.67 69,000
$743,724.84 46.67 69,500
$767,058.91 70,000
$790,392.98 46.67 70,500
$813,727.06 46.67 71,000
$837,061.13 46.67 71,500
(23,334.07)
OCF Sensitivity of OCF Variable cost per unit
$ 301,975.00 ₹ 20.00
₹ 438,475.00 -45500 ₹ 17.00
₹ 392,975.00 -45500 ₹ 18.00
₹ 347,475.00 -45500 ₹ 19.00
₹ 301,975.00 ₹ 20.00
₹ 256,475.00 -45500 ₹ 21.00
₹ 210,975.00 -45500 ₹ 22.00
₹ 165,475.00 -45500 ₹ 23.00
$ 45,500.00
Using pmt function Step by step FBE
131,035.85 ₹ 131,035.85 FC (1-t)
₹ 695,000.00 t*Depn
₹ 73,500.00 Denominator
35% FBE
53,563.54
OCF calculation
$ 2,520,000
$ 1,400,000
$ 1,120,000
$ 695,000
$ 73,500
$ 351,500
$ 123,025
$ 228,475
$ 301,975
₹ 767,058.91
with the reference to sales volume cell and the formula for NPV
ge of cells
hat if analysis - Data table
n input as the input variable - sales volume - B14
NPV
767,058.91
697,056.70
720,390.77
743,724.84
767,058.91
790,392.98
813,727.06
837,061.13
OCF Variable cost per unit OCF
₹ 301,975.00
₹ 438,475.00
₹ 392,975.00
₹ 347,475.00
₹ 301,975.00
₹ 256,475.00
₹ 210,975.00
₹ 165,475.00
₹ 451,750.00
₹ 25,725.00
10.40
53563.5435267
Problem
Scenario Analysis In the previous problem, suppose the
projections given for price, quantity, variable costs, and
fixed costs are all accurate to within +/-10 percent.
Calculate the best-case and worst-case NPV figures.
Solution
Cost of the Project ₹ 588,000.00
Life of the project in years 8
Depreciation ₹ 73,500.00
Projected Sales Volume 70,000.00
Price per unit ₹ 36.00
Variable cost per unit ₹ 20.00
Fixed Costs ₹ 695,000.00
Tax rate 35%
Required rate of return 15%
Contribution per unit $ 16.00
Calculation of OCF using the tax shield approach
OCFbase = [(P – v)Q – FC](1 – tc) + tcD
Base OCF $ 301,975.00
Year 0
Cash Flows $ (588,000.00)
NPV $767,058.91
Best Case Scenerio
The price and quantity increase by 10 percent, and the
variable and fixed costs both decrease by 10 percent 10%
Projected Sales Volume 77,000.00
Price per unit $ 39.60
Variable Cost $ 18.00
Fixed Cost $ 625,500.00
OCF $ 700,230.00
Worse Case Scenario
The price and quantity decrease by 10 percent, and the
variable and fixed costs both increase by 10 percent
Projected Sales Volume 63,000.00
Price per unit $ 32.40
Variable Cost $ 22.00
Fixed Cost $ 764,500.00
OCF $ (45,320.00)
Scenario Summary
Changing Cells:
Projected Sales Volume $B$8
Price per unit $B$9
Variable Cost $B$10
Fixed Cost $B$11
Result Cells:
NPV $B$22
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.
Step-by-step method
Initial investment
Projected Sales Volume
Price per unit
Variable cost per unit
Fixed Costs
Revenue
Total variable cost
Contribution
Depreciation
Profit
Tax rate
Tax
PAT
OCF
NPV
Years 1-8 Scenario Summary
$ 301,975.00
Changing Cells:
Result Cells:
Notes: Current Values column represents values of c
time Scenario Summary Report was created. Chang
scenario are highlighted in gray.
Current Values: Best Case Worse case
70,000.00 77,000.00 63,000.00
$ 36.00 $ 39.60 $ 32.40
$ 20.00 $ 18.00 $ 22.00
$ 695,000.00 $ 625,500.00 $ 764,500.00
$767,058.91 $2,554,157.14 ($791,365.41)
Current scenario Best case Worse case
₹ 588,000 ₹ 588,000 ₹ 588,000
70,000.00 77,000.00 63,000.00
₹ 36.00 39.60 ₹ 32.40
₹ 20.00 ₹ 18.00 ₹ 22.00
₹ 695,000.00 ₹ 625,500.00 ₹ 764,500.00
₹ 2,520,000 ₹ 3,049,200 ₹ 2,041,200
₹ 1,400,000 ₹ 1,386,000 ₹ 1,386,000
₹ 1,120,000 ₹ 1,663,200 ₹ 655,200
₹ 73,500.00 ₹ 73,500.00 ₹ 73,500.00
₹ 351,500.00 ₹ 964,200.00 ₹ -182,800.00
35% 35% 35%
₹ 123,025.00 ₹ 337,470.00 ₹ -63,980.00
₹ 228,475.00 ₹ 626,730.00 ₹ -118,820.00
₹ 301,975.00 ₹ 700,230.00 ₹ -45,320.00
₹ 767,058.91 2,554,157.14 $ (791,365.41)
Current Values: Current Best case
$G$5 70,000.00 70,000.00 77,000.00
$G$6 ₹ 36.00 ₹ 36.00 ₹ 39.60
$G$7 ₹ 20.00 ₹ 20.00 ₹ 18.00
$G$8 ₹ 695,000.00 ₹ 695,000.00 ₹ 625,500.00
$G$18 ₹ 767,058.91 ₹ 767,058.91 ₹ 2,554,157.14
Values column represents values of changing cells at
ummary Report was created. Changing cells for each
hlighted in gray.
Worse case
63,000.00
₹ 32.40
₹ 22.00
₹ 764,500.00
-₹ 791,365.41
Project Engineers required NPV (million Rs.) NPV per engineer Ranking
A 30 60 2.00 2
B 20 25 1.25 3
C 60 70 1.17 4
D 50 150 3.00 1
Capital restriction 90
Decision D and A
Project Investment ([Link]) NPV PV PI
A 8 18 26 3.25
B 5 16 21 4.2
C 5 12 17 3.4
Capital restriction 10 9
Decision B and C A
Project NPV Investment PV PI
1 5000 10000 15000 1.50
2 5000 50000 55000 1.10
3 10000 90000 100000 1.11
4 15000 60000 75000 1.25
5 15000 75000 90000 1.20
6 3000 15000 18000 1.20
Capital restriction 150000 100000
Decision 1,4,5 1,4
Project Investment ('000) NPV ('000) PV PI
1 300 66 366 1.22
2 200 -4 196 0.98
3 250 43 293 1.17
4 100 14 114 1.14
5 100 7 107 1.07
6 350 63 413 1.18
7 400 48 448 1.12
Capital restriction 1000000
Decision 1,6,3
Rank
3
1
2
Rank
1
6
5
2
3
4
Rank
1
7
3
4
6
2
5