The article will illustrate how to undertake **capital budgeting** sensitivity analysis in **Excel**. Every businessman needs to predict how much money he would get as a profit and how he should invest his money. There are different types of data analysis processes that can help someone in this matter. **Sensitivity analysis **is one of them. It helps to determine the uncertainty using the change of a single variable in the system. Fortunately, we can do the **sensitivity analysis **in Excel by using some simple formulas for **capital budgeting**. Please stay tuned for the rest of the article to get to the topic.

**Table of Contents**hide

## Download Practice Workbook

**What Is Sensitivity Analysis?**

**Sensitivity analysis **is a statistical approach to calculate the uncertainty in a mathematical model. This analysis helps us to make assumptions about the best output of the model. Thus it can help to reduce the unwanted condition from the model. The analysis also helps to understand the relationships between the input and output variables in that model.

## Steps to Perform Sensitivity Analysis for Capital Budgeting in Excel

In the following image, I’ve shown you how a simple **capital budgeting sensitivity analysis **will look like. There are different types of parameters here such as **Investment Cost**, **Maintenance Cost**, **Project Duration** (here it is **4 Years**), **Depreciation Cost**, **Return Receipt Requested **(**RRR**), **Operating Cash Flow **(**OCF**), **Net Present Value **(**NPV**), **Variable Cost**, **Fixed Cost**, etc. These parameters help us to realize how much production would be a good choice for an investor.

Let’s undergo the following steps to learn the process of **capital budgeting sensitivity analysis**.

**Step-1: Inserting the Preliminary Data**

The first thing we need to do in **capital budgeting **is to find out how much we have to spend for production initially and determine the target value of unit production for **Base**, **Pessimistic **and **Optimistic **conditions.

- First, insert the necessary data which you estimate from your expenditure history. Say the initial
**Cost**of the project is**320,000.00**dollars and the duration of the production period is**4 years**. - Next, estimate the quantities of the product that you want to sell for
**Base**,**Pessimistic**and**Optimistic** - Also, calculate the cost per unit,
**Variable Cost per Unit**, and**Fixed Cost**.

**Step-2: Using Formulas to Determine Necessary Parameters**

In this step, we will apply some formulas to determine important terms regarding the **capital budgeting sensitivity analysis**.

- After that, use the following formula to determine the
**Depreciation Cost**.

`=(C4-C5)/C6`

You might have understood that the **Depreciation Cost** is calculated by computing the difference between cost and maintenance divided by the time period.

Now you need to calculate the **Operating Cash Flow** (**OCF**) amount. So, let’s calculate that.

- Write down the formula below in cell
**F10**and press**ENTER**.

`=(($G$6-$G$7)*F5-$G$8)*(1-$C$9)+$C$7*$C$9`

The formula will return the **Operating Cash Flow **amount for the **Pessimistic **condition. This is a measure of the amount of cash earned by the normal business operations of the company.

- Next, drag the
**Fill icon**to the right to**AutoFill**the adjacent cells. This operation will provide you with the**OCF**of**Base**and**Optimistic**

The next thing you need to do is to calculate the **Net Present Value**. To do that,

- Type the following formula in cell
**F11**and hit the**ENTER**

`=NPV($C$8,F10,F10,F10,F10,F10)-$C$4`

The formula here uses **the NPV function **to return the **Net Present Value **for the **Pessimistic **condition.

- Drag the
**Fill icon**to the right to determine the**NPV**amount for the**Base**and**Optimistic**conditions too.

The above process is known as the **sensitivity analysis **of **capital budgeting**. The **NPV **here represents an estimation of how much money will remain at the end of the period. You can get an idea of how many units you need to produce for the best profit.

**Read More:** **Sensitivity Analysis for NPV in Excel (with Easy Steps)**

**Step-3: Graphical Representation of Capital Budgeting Sensitivity Analysis**

Here, I’ll show you the **Sensitivity Analysis **with a graph.

- First, copy the
**Sold Units**and**NPV**amount and paste them side by side like the following picture. - You can also use a formula to do this if you find it difficult to copy those data. Check the following line.

`=TRANSPOSE(F4:H4)`

The formula here uses **the TRANSPOSE function **and returns the transposed form of the **F4:H4 **row.

Similarly, we can get the transposed form of the **E5:H5 **row from the following formula.

`=TRANSPOSE(E5:H5)`

And the next one will return the **E11:H11 **row as the **G13:G16 **column.

`=TRANSPOSE(E11:H11)`

- Now, select the range
**F13:G16**and go to**Insert**>>**Chart**>>**Scattered Line Chart**.

The command will return the graphical overview of **capital budgeting sensitivity analysis**. The graph helps us to determine the minimum production unit which won’t cause us any loss in the business and take the necessary initiative to **budget **the investment **capital** in the best way possible.

Thus, we can do **capital budgeting sensitivity analysis **in Excel.

**Read More: ****How to Build a Sensitivity Analysis Table in Excel (With 2 Criteria)**

## Conclusion

Suffice to say, I can consider that you have learned the basic ideas of how to do **capital budgeting sensitivity analysis **after reading this article. If you have any better suggestions or questions or feedback regarding this article, please share them in the comment box. This will help me enrich my upcoming articles. For more queries, kindly visit our website **ExcelDemy**.