Hello, Guest   |   Login here

Sensitivity Analysis

A. NPV and IRR Sensitivity Analysis: the target is NPV to be positive and IRR to be higher than the discount rate (10%)

This analysis is carried out to find out the impact on NPV and IRR of changes in key variables, such as, sales, price, expenses. By running simulations of changes in these variables we can arrive at the right value of sales, price and expenses to achieve an NPV and IRR acceptable to investors and lenders so to proceed and implement the investment.

1. Sales

1.1. Increase sales from Baseline Case by 12 additional licences in Year 2 The following calculations are performed as above:
Summary Cash Flow Table
Cash Inflow Cash Outflow
Year Initial Investment
(1)
Total Revenue
(2)
Total Expenses
(3)
Net Cash Flow (NCF)
(4) = (2) – (3)
Y1 800,000 420,000 909,200 -489,200
Y2 2’270,000 -2’286,000 -16,000
Y3 3’070,000 -2’379,000 691,000
Y4 4’070,000 -3’163,000 907,000
NPV and IRR are calculated using the above NCF values:
NPV at Selected Discount Rates
0% 5% 10% 15% 20%
NPV 292,800 59,700 -108,453 -230,057 -317,908
IRR 6,6%
Conclusion

The investment is rejected as NPV, -108,453, is negative at 10% discount or market rate, meaning investors will lose money if proceed with this investment. They would rather choose other investment that will at least provide a 10% return.

Also the IRR, 6.6%, is below the cut-off or market rate 10%, again investors will not be interested to participate in this project as they stand to lose out of it.

The NPV and IRR both yield the same information and one indicator expresses the same than the other: whether the investment is profitable or not and serve as an acceptance or rejection criteria.

1.2. Increase sales from Baseline Case by 70 additional licences in Year 2 The following calculations are performed as above:
Summary Cash Flow Table
Cash Inflow Cash Outflow
Year Initial Investment
(1)
Total Revenue
(2)
Total Expenses
(3)
Net Cash Flow (NCF)
(4) = (2) – (3)
Y1 800,000 420,000 909,200 -489,200
Y2 4’010,000 -3’504,000 506,000
Y3 3’650,000 -2’379,000 1’271,000
Y4 4’070,000 -3’163,000 907,000
NPV and IRR are calculated using the NCF values:
NPV at Selected Discount Rates
0% 5% 10% 15% 20%
NPV 1’394,800 987,791 679,880 444,783 263,881
IRR 30.7%
Conclusion
The investment is accepted as NPV, 680,046, is positive and adds value to the investment, although less than the initial investment, 800,0oo, at 10% discount or market rate. However, the IRR, 30.7%, is well above the cut-off or market rate 10%. Investors will be interested to participate in this project as the IRR provides a high profitability over the discount rate. The NPV and IRR both yield the same information and one indicator expresses the same than the other: whether the investment is profitable or not and and serve as an acceptance or rejection criteria. section 5 NPV Sensitivity number of unitssection 5 NPV Sensitivity number of units IRR at 10

2. Prices

2.1. NPV and IRR in the Case of an Increase in Unit Price to 35,000 in Y2 The following calculation is performed as above:
Summary Cash Flow Table
Cash Inflow Cash Outflow
Year Initial Investment
(1)
Total Revenue
(2)
Total Expenses
(3)
Net Cash Flow (NCF)
(4) = (2) – (3)
Y1 800,000 420,000 909,200 -489,200
Y2 2’205,000 -2’034,000 171,000
Y3 3’048,000 -2’379,000 669,000
Y4 4’070,000 -3’163,000 907,000
NPV and IRR are calculated using the above NCF values:
NPV at Selected Discount Rates
0% 5% 10% 15% 20%
NPV 458,000 203,320 17,181 -119,128 -220,161
IRR 10,55%
Conclusion

The investment is rejected as NPV, 17,181, is positive but much less than the initial investment, 800,0oo, at 10% discount or market rate. It is practically zero in value, meaning that it does not add not subtract value to the investment. Nor provides a buffer for any risk such as a drop in sales or unexpected higher expenses, or higher tax imposed by government.

Also the IRR, 10.55%, is just above the cut-off or market rate of 10%. Investors will not be interested to participate in this project as the IRR is almost equal to the discount or market rate, meaning that there is profitability to gain from this investment. The NPV and IRR both yield the same information and one indicator expresses the same than the other: whether the investment is profitable or not and serve as an acceptance or rejection criteria. 2.2. NPV and IRR in the Case of an Increase in Unit Price to 50,000 in Y2 The following calculation is performed as above:
Summary Cash Flow Table
Cash Inflow Cash Outflow
Year Initial Investment
(1)
Total Revenue
(2)
Total Expenses
(3)
Net Cash Flow (NCF)
(4) = (2) – (3)
Y1 800,000 420,000 909,200 -489,200
Y2 2’205,000 -2’034,000 171,000
Y3 3’048,000 -2’379,000 669,000
Y4 4’070,000 -3’163,000 907,000
NPV and IRR are calculated using the above NCF values:
NPV at Selected Discount Rates
0% 5% 10% 15% 20%
NPV 104,800 700,849 440,308 243,105 92,820
IRR 23%
Conclusion
The investment is Accepted as NPV, 440,308, is positive about half the initial investment, 800,0oo, at 10% discount or market rate. It does adds value to the investment and provides a buffer for any risk such as a drop in sales or unexpected higher expenses, or higher tax imposed by government. The IRR, 23%, is well above the cut-off or market rate of 10%. Thus investors will be interested to participate in this project as the IRR is more than twice the discount or market rate, meaning that there is a high profit to gain from this investment.

3. Cost

3.1. NPV and IRR in the Case of a Decrease in the Unit Purchase Cost of Licences to 19,000 in Y2 The following calculation is performed as above:
Summary Cash Flow Table
Cash Inflow Cash Outflow
Year Initial Investment
(1)
Total Revenue
(2)
Total Expenses
(3)
Net Cash Flow (NCF)
(4) = (2) – (3)
Y1 800,000 420,000 909,200 -489,200
Y2 1’910,000 -1’916,000 -6,000
Y3 2’950,000 -2’379,000 571,000
Y4 4’070,000 -3’163,000 907,000
NPV and IRR are calculated using the above NCF values:
NPV at Selected Discount Rates
0% 5% 10% 15% 20%
NPV -183,000 -30,200 -182,736 -291,941 -369,852
IRR 4.2%
Conclusion
The investment is rejected as NPV, -182,736, is negative. It is much less than the initial investment, 800,0oo, at 10% discount or market rate. It subtracts high value to the investment. Also the IRR, 4.2%, is below the cut-off or market rate of 10%. Investors will not be interested to participate in this project as the IRR is well below the discount or market rate, meaning that there is a loss to be made from this investment. 3.2.NPV and IRR in the Case of a Decrease in the Unit Purchase Cost of Licences to 14,000 in Y2 The following calculation is performed as above:
Summary Cash Flow Table
Cash Inflow Cash Outflow
Year Initial Investment
(1)
Total Revenue
(2)
Total Expenses
(3)
Net Cash Flow (NCF)
(4) = (2) – (3)
Y1 800,000 420,000 909,200 -489,200
Y2 1’910,000 -1’621,000 289,000
Y3 2’950,000 -2’379,000 571,000
Y4 4’070,000 -3’163,000 907,000
NPV and IRR are calculated using the above NCF values:
NPV at Selected Discount Rates
0% 5% 10% 15% 20%
NPV 478,000 224,628 38,901 -97,973 -199,135
IRR 11.3%
Conclusion
The investment is rejected as NPV, 38,901, is just positive, near to zero. It is much less than the initial investment, 800,0oo, at 10% discount or market rate. It adds a very small value to the investment, just not enough to provide coverage against market or costs risks. Also the IRR, 11.3%, is just above the cut-off or market rate of 10%. Investors will not be interested to participate in this project as the IRR is being just above the discount or market rate is a risky proposition as it will provide any cover against unexpected drop in revenue or raising costs. section 5 NPV Sensitivity cost section 5 NPV Sensitivity cost NPV at 10

B. Payback Period Sensitivity Analysis Target: Payback to be reached at the end of Year 2

This analysis is carried out to find out the impact on Payback Period of changes in key variables, such as, sales, price, expenses. By running simulations of changes in these variables we can arrive at the right value of sales, price and expenses to achieve a payback Period acceptable to investors and lenders so to proceed and implement the investment.

1. Sales Target: Payback at the end of Year 2

1.1. Increase sales from Baseline Case by 2 additional licences in Years 1 and 2. Total of 111 licences in years 1 and 2 After increasing the number of licences from 73 in Years 1 and 2 to 111 licences the following equity balances are obtained:
Expenses for Y1 through Y4
Year of Operation Equity
Y1 583,600
Y2 741,400
Y3 1’202,200
Y4 2’105,000
Conclusion
The investment is rejected as equity balance in December Year 2 is less than 800,000. 1.2. Increase sales from Baseline Case by 2 additional licences over the previous case in January Year 3. Total of 112 licences sold until January Year 3 After increasing the number of licences from 73 in Years 1 and 2 to 121 licences the following equity balances are obtained:
Expenses for Y1 through Y4
Year of Operation Equity
Y1 583,600
Y2 741,400
January Y3 793,350
December Y3 1’624,000
Y4 2’527,000
Conclusion
The investment is accepted as although equity balance in December Year 2 is less than the target, 800,000, by adding two more licences in January Y3 the target is reached.

2. Unit Price Target: Payback at the end of Year 2

2.1. Increase unit price in years 1 and 2 from 30,000 in the Baseline Case to 35,000 in these two years After increasing the unit price to 35,000 in years 1 and 2 the following equity balances are obtained:
Expenses for Y1 through Y4
Year of Operation Equity
Y1 527,600
Y2 647,738
Y3 1’244,475
Y4 2’175,675
Conclusion
Equity balance at the end of Year 2, 647,738, is less than the 800,000 target. Thus the investment is rejected. 2.2. Increase unit price in years 1 and 2 from 30,000 in the Baseline Case to 40,000 in these two years After increasing the unit price to 40,000 in years 1 and 2 the following equity balances are obtained:
Expenses for Y1 through Y4
Year of Operation Equity
Y1 597,600
Y2 1’036,069
Y3 1’779,535
Y4 2’662,339
Conclusion
Equity balance at the end of Year 2 is about 20% higher than the 800,000 target. The investment is accepted.

3. Sales Target: Payback at the end of Year 2

3.1. Decrease unit purchase cost in years 1 and 2 from 21,000 in the Baseline Case to 20,000 in these two years After reducing the unit purchase cost to 20,000 in years 1 and 2 the following equity balances are obtained:
Expenses for Y1 through Y4
Year of Operation Equity
Y1 541,600
Y2 720,738
Y3 1’365,875
Y4 2’248,675
Conclusion
The investment is rejected as equity balance in December Year 2, 720,738, is less than 800,000. 3.2. Decrease unit purchase cost in years 1 and 2 from 21,000 in the Baseline Case to 19,000 in these two years After reducing the unit purchase cost to 20,000 in years 1 and 2 the following equity balances are obtained:
Expenses for Y1 through Y4
Year of Operation Equity
Y1 555,600
Y2 793,738
Y3 1’438,875
Y4 2’321,675
Conclusion
The investment is accepted as equity balance in December Year 2, 793,738, is practically equal to 800,000.

C. Break-even Point Target: Break-even Point to be reached at the end of Year 2

This analysis is carried out to find out the impact on Break-even Point of changes in key variables, such as, sales, price, expenses. By running simulations of changes in these variables we can arrive at the right value of sales, price and expenses to achieve a Break-even Point acceptable to investors and lenders so to proceed and implement the investment.

1. Sales Target: Break-even Point at the end of Year 2

1.1. Increase sales from Baseline Case by 2 additional licences per month in Year 1 and 4 licences per month in Year 2. Total of 111 licences in years 1 and 2. A total of 135 licences in the two first years. After increasing the number of licences from 73 in Years 1 and 2 to 135 licences the following values are obtained:
Cumulative Margins December
Year Total Revenue Total Variable Cost Total Fixed Cost Cumulative Fixed Cost by Year Quantity of Licences Sold Unit Contribution to Absorb Fixed Costs Total Margin Cumulative Margin
Y1 840,000 588,000 518,400 518,400 28 9,000 252,000 252,000
Y2 3’490,000 2’163,000 819,200 1’337,600 107 9,000 671,000 1’579,000
Y3 3’430,000 1’652,000 751,200 2’088,000 59 12,000 1’298,000 3’357,000
Y4 4’070,000 2’436,000 751,200 2’840,000 87 12,000 1’634,000 4’991,000
Breakeven has occurred at the expected target date December Year 2 as Cumulative Margin, 1’579,000, is higher than cumulative fixed cost, 1’337,600. Breakeven happens somewhat earlier as the difference between the two values is relatively small.
Conclusion
The investment is accepted as cumulative margin in December Year 2, 1’579,000, is just higher than cumulative fixed cost, 1’337,400.

2. Unit Price Target: Breakeven at the end of Year 2

2.1. Increase unit price of licences from Baseline Case, 30,000, to 40,000 in Year 1 and 2 After increasing the unit price to 40,000 in Years 1 and 2 the following breakeven calculations are obtained:
Cumulative Margins December
Year Total Revenue Total Variable Cost Total Fixed Cost Cumulative Fixed Cost by Year Quantity of Licences Sold Unit Contribution to Absorb Fixed Costs Total Margin Cumulative Margin
Y1 560,000 294,000 518,400 518,400 14 19,000 266,000 266,000
Y2 2’546,669 1’239,000 819,200 1’337,600 59 19,000 1’307,600 1’573,669
Y3 3’146,670 1’652,000 751,200 2’088,000 59 12,000 1’494,670 3’068,339
Y4 4’070,000 2’436,000 751,200 2’840,100 87 12,000 1’634,000 4’702,339
Conclusion
The investment is rejected as Cumulative Margin, 1’573,669, is less than Cumulative Fixed Cost, 1’337,600. The investors would stand to lose money if they proceed with the investment when Breakeven does not achieve the target at the end of Year 2. 2.2. Increase unit price of licences from Baseline Case from 30,000 to 50,000 in Year 1 and 2 After increasing the unit price to 50,000 in Years 1 and 2 the following breakeven calculations are obtained:
Cumulative Margins December
Year Total Revenue Total Variable Cost Total Fixed Cost Cumulative Fixed Cost by Year Quantity of Licences Sold Unit Contribution to Absorb Fixed Costs Total Margin Cumulative Margin
Y1 700,000 294,000 518,400 518,400 14 29,000 406,000 406,000
Y2 3’183,338 1’239,000 819,200 1’337,600 59 29,000 1’711,000 2’117,000
Y3 3’343,337 1’652,000 751,200 2’088,800 59 12,000 708,000 2’825,000
Y4 4’070,000 2’436,000 751,200 2’840,000 87 12,000 1’044,000 3’869,000
Conclusion
The investment is accepted as Cumulative Margin, 2’117,000, is higher than Cumulative Fixed Cost, 1’337,600. The investors would stand to profit from this investment. NEXTPAGENEXT

3. Unit Purchase Cost Target: Breakeven at the end of Year 2

3.1. Decrease unit purchase cost of licences from Baseline Case, 21,000, to 15,000 in Year 1 and 2 After decreasing the unit price from 21,000 to 15,000 in Years 1 and 2 the following breakeven calculations are obtained:
Cumulative Margins December
Year Total Revenue Total Variable Cost Total Fixed Cost Cumulative Fixed Cost by Year Quantity of Licences Sold Unit Contribution to Absorb Fixed Costs Total Margin Cumulative Margin
Y1 420,000 210,000 518,400 518,400 14 15,000 210,000 210,000
Y2 1’910,000 885,000 819,200 1’337,600 59 15,000 885,000 1’095,000
Y3 3’146,670 1’652,000 751,200 2’088,000 59 12,000 708,000 1’803,000
Y4 4’070,000 2’436,000 751,200 2’088,000 87 12,000 708,000 2’847,000
Conclusion
The investment is rejected as Cumulative Margin, 1’095,000, is less than Cumulative Fixed Cost, 1’337,600 in December Y2. The investors would stand to lose money if they proceed with the investment when Breakeven does not achieve the target at the end of Year 2. 3.2. Decrease unit purchase cost of licences from Baseline Case, 21,000, to 12,000 in Year 1 and 2 After decreasing the unit price from 21,000 to 12,000 in Years 1 and 2 the following breakeven calculations are obtained:
Cumulative Margins December
Year Total Revenue Total Variable Cost Total Fixed Cost Cumulative Fixed Cost by Year Quantity of Licences Sold Unit Contribution to Absorb Fixed Costs Total Margin Cumulative Margin
Y1 420,000 168,000 518,400 518,400 14 18,000 252,000 252,000
Y2 1’910,000 708,000 819,200 1’337,600 59 18,000 1’062,000 1’314,000
Y3 2’950,000 1’652,000 751,200 2’088,000 59 12,000 708,000 2’022,000
Y4 4’070,000 2’436,000 751,200 2’840,000 87 12,000 1’044,000 3’066,000
Conclusion
The investment is accepted as Cumulative Margin, 1’314,000, is practically equal to Cumulative Fixed Cost, 1’337,600 in December Y2.

Subscribe today and learn the fundamentals

Hello,
It seems you don't have access to the exclusive content of The Course.

In order to gain access to the content of The Course you will have to register with EntreprenAble. Membership with EntreprenAble will also give you access to our interactive Business Game and Accounting Simulations.

Subscribe today and get:

  • Unlimited website access to The Course
  • Optimized view for Desktop, Tablet and Mobile devices
  • Free 7-day, no obligation trial - normally £15
Start free trial
Test-drive the system for free
Page 1 of 5 1 2 3 4 5