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.
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.
Sensitivity Analysis for NPV in Excel: with Easy Steps
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.
📌 Steps 2: Estimate Annual Cash Flow
- Then, apply the following formula in cell C14 to estimate the annual cash flow.
Read More: How to Use What If Analysis in Excel
📌 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.
- 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.
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.
- After that, change the formulas for a different variable one by one to complete the following table.
Read More: What-If Analysis in Excel with Example
📌 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.
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%.
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.
Download Sample Workbook
You can download the free template from the download button below.
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. Stay with us and keep learning.
- How to Perform Sensitivity Analysis for Capital Budgeting in Excel
- How to Do IRR Sensitivity Analysis in Excel
- How to Get Sensitivity Report from Solver in Excel
- Types of What-If analysis in Excel
- What If Analysis Data Table Not Working
- How to Delete What If Analysis in Excel