Md. Shamim Reza
This article illustrates how to perform sensitivity analysis in excel. Sensitivity Analysis is a tool to analyze the impact of a change in an independent variable associated with a product. This is a method used in financial modeling to study the uncertainties in the output of a product associated with the uncertain changes within the inputs related to that product. This article will guide you through easy steps to perform sensitivity analysis for net present value or NPV in excel.
Download Sample Workbook
You can download the free template from the download button below.
What Is Sensitivity Analysis?
Sensitivity Analysis is done to determine the impact of a change in an independent variable associated with a product. This is a method used in financial modeling to study the uncertainties in the output of a product associated with the uncertain changes within the inputs related to that product.
Steps to Perform Sensitivity Analysis for NPV in Excel
Assume you have a three-year project running under your supervision. The revenue from the project mainly depends on 3 variables: selling price, variable cost, and annual sales. You need to know how changing these variables will affect the Net Present Value or NPV of the project.
Follow the steps below to be able to do that.
📌 Steps 1: Calculate Profit Margin
- First, enter the following formula in cell C13 to get the margin between cost and sales.
=D9-D10
📌 Steps 2: Estimate Annual Cash Flow
- Then, apply the following formula in cell C14 to estimate the annual cash flow.
=C13*D11
📌 Steps 3: Create NPV Table
- Next, create a table showing yearly cash flows as follows. The numbers in brackets are negative.
- After that, enter the following formula in cell C21 to get the NPV based on the expected cash flow.
=NPV($C$5,C18:C20)+C17
- Suppose you need to forecast how the NPV will change if the selling price falls to a certain minimum. Then change the formula in cell C13 to the following one.
=C9-D10
You can change only one variable at a time and keep the others as expected.
- After that, extend the NPV table to get the NPV at the minimum selling price.
- Now, change the formula in cell C14 to know what will happen if annual sales drop to a minimum. But, you should calculate the margin from the expected values.
=C13*C11
- After that, change the formulas for a different variable one by one to complete the following table.
Read More: One and Two Ways (Variables) Sensitivity Analysis in Excel!
📌 Steps 4: Perform Sensitivity Analysis for NPV
- Next, create another table to compare the change in NPV with the change in a variable. Then, apply the following formula in cell F24 to get the change in NPV resulting from the change in the variables.
=E24-C24
Remarks:
You can see from the above result that the selling price is the most sensitive variable for the NPV. You can also calculate that a 20% increase in selling price may increase the NPV by more than 700%.
Read More: How to Build a Sensitivity Analysis Table in Excel (With 2 Criteria)
Things to Remember
- You should change only one variable at a time and keep others as the base value.
- Don’t forget that the numbers in brackets are negative.
Conclusion
Now you know how to perform sensitivity analysis in excel. Hope this article helps you with this. Do you have any further queries or suggestions? Please let us know using the comment section below. You can also visit our ExcelDemy blog to explore more about excel. Stay with us and keep learning.
