This guide will explain how to perform sensitivity analysis for capital budgeting in Excel.
Sensitivity analysis is a method used in financial modeling to analyze how the different variables affect a specific dependent variable under certain conditions.
Capital budgeting is an essential activity for companies that require an evaluation of their projects and investments.
Companies use capital budgeting to decide how to allocate resources for purchasing assets. One method commonly used in capital budgeting is sensitivity analysis.
Sensitivity analysis or What-If analysis refers to a particular exercise to predict the outcome of a specific action. Sensitivity analysis allows us to forecast future values using historical data.
Let’s take a look at a quick example of how sensitivity analysis could be useful for capital budgeting.
Suppose you are a shop that sells beachwear. You have a stall placed next to the local beach, and you have a good idea of how many people pass by your stall during peak hours.
Assume that a 15% increase in foot traffic will lead to a 10% increase in beachwear sales. We can use sensitivity analysis to determine how much sales to expect when the foot traffic increases to an arbitrary value like 50%.
How can we do this in Excel?
Excel includes several tools for What-If analysis reports. The most useful tool for sensitivity analysis is the Data Table.
We can use a two-variable data table to see how different values of two variables in one formula will change the results of a particular formula. For example, we can use a two-variable data table to see how different combinations of growth rate and profit margins can affect the final earnings.
Now that we have a grasp on how sensitivity analysis can help capital budgeting, let’s learn how to use it on an actual sample spreadsheet.
A Real Example of Sensitivity Analysis for Capital Budgeting
The following section provides several examples of performing sensitivity analysis for capital budgeting. We will also go into detail about the formulas and tools used in these examples.
First, let’s take a look at a sample worksheet that aims to forecast annual earnings.
The sheet below shows actual financial data for a business in 2022. Assuming a certain growth rate and gross profit margin, we could determine that the diluted earnings per share will rise from $11.48 to $13.85.
We want to determine how this value could change if we assume different values for revenue growth rate and gross profit margin.
We can use Excel’s What-If Analysis tools to plot how the target value changes given a range of inputs. The Data Table is most useful in this case since it allows you to create a two-variable sensitivity analysis report.
Do you want to take a closer look at our examples? You can make your own copy of the spreadsheet above using the link attached below.
If you want to try using data tables for sensitivity analysis, head over to the next section to read our step-by-step breakdown on how to do it yourself!
How to Perform Sensitivity Analysis for Capital Budgeting in Excel
This section will guide you through all the steps necessary to perform a sensitivity analysis for capital budgeting. You’ll learn how to use Excel’s Data Table function to find how earnings fluctuate depending on the values of two variables.
Follow these steps to use the Data Table tool for sensitivity analysis in Excel:
- First, create a table with the format seen below. We’ll adjust two variables for this example: revenue growth rate and gross profit margin.
- Fill out the first row and first column with the range of values you want to test with.
- Link the upper-left cell to the cell in your worksheet that you want to track. In this example, we’ll trackbe tracking the 2023 diluted earnings per share in cell E19.
- Select the entire data table after filling out the headers.
- In the Data tab, click on the What-If Analysis option. Under the dropdown menu, click on Data Table.
- Type the required cells for the row input cell and column input cell input boxes. This will let Excel know which values to replace with our headers.
- Click on OK to fill out the data table. In the example below, we can see how the share of earnings is affected by a change in growth rate and gross profit margin.
These are all the steps you need to perform sensitivity analysis for capital budgeting in Excel.
Frequently Asked Questions (FAQ)
- What are the limitations of sensitivity analysis?
The accuracy of sensitivity analysis depends on how much historical data is used. There is always some level of error in the final result because of outside variables that may affect your model.
This step-by-step guide is a quick introduction to using the What-If Analysis function in Microsoft Excel for sensitivity analysis. Overall, this step-by-step guide should provide all the information required to use sensitivity analysis for capital budgeting.
This function is just one example of the many Excel functions that you can use in your spreadsheets. Our website also offers hundreds of other functions and methods to help you get more out of Microsoft Excel.
For example, you can read our guide on the Scenario Manager tool to get a general understanding of the feature.
With so many other Excel functions available, you can find one appropriate for your use case.
Want to learn more about Excel? Subscribe to our newsletter for helpful guides and tutorials!
