4.4 Modelling sensitivity analysis
Spreadsheets allow us to see the impact of different possible outcomes on the net present value of the project. This form of sensitivity analysis can be very helpful as it allows us to see the effect of a single or a combination of possible outcomes as 'what if' scenarios. The following material shows the use of spreadsheets in this context.
First watch the video ‘Sensitivity analysis’ which shows you how to carry out break-even sensitivity analysis in investment appraisal.
Break-even sensitivity analysis finds the percentage change from the original value, in each of the project’s inputs in turn, that is required to achieve a zero NPV. The input or variable that shows the smallest change is the most sensitive.
The most sensitive variables are the variables where small changes or deviations from the project’s plan will have a greater impact on the outcome, or NPV, than changes in other variables.
Knowing which variables are the most sensitive helps you focus on the high risk areas of a project. It allows further testing of assumptions regarding the forecasts and projections associated with the sensitive variables.
The sensitive variables will be monitored closely by managers if the project goes ahead.
A company plans to invest £1,000,000 in a new product that will generate revenues (cash receipts) of £300,000 in Year 1, £500,000 in Year 2 and £600,000 in Years 3 to 5.
Costs (cash payments) will be 25% of revenues (cash receipts) and are paid in the same year as the relevant cash receipts.
This company uses a 10% discount rate for projects of this type.
The corporate tax rate is 28%. Taxation cash flows occur in the same year as the relevant taxable receipts and payments. There are no taxation cash flows arising from the investment of £1,000,000. Calculate the NPV of this investment.
This time, carry out break-even sensitivity analysis on the inputs of this investment.
Watch the video:
Make your own notes.
You can see the Sensitivity analysis (solution) here.