Meraz Al Nahian
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.
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.
