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: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 |
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: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 |
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.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: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 |
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: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 |
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: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 |
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: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 |
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.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: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: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: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: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: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: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: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 |
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: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: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. NEXTPAGENEXT3. 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: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: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
Test-drive the system for free