Sensitivity data analysis Excel is a method that allows us to immediately visualize the economic advantages and disadvantages of a project. This method is widely used to identify the project that will give us the best performance.
Sensitivity Data Analysis Excel Example
Let’s suppose that my neighbor has proposed to me to create a new business. That implies an initial investment of 100,000 dollars. After doing the deep analysis of the project we have estimated that we will have sales for 85,000 dollars in the first year.
It seems to be a profitable business, but we must also consider the fixed expenses that will be of 35,000 dollars. The variable expenses that will be of 30,000 dollars. With these expenses we will have a profit of 20,000 dollars.
As we know, every project has variables that we can not control and that will inevitably change over time. In our example, it will be sales and variable expenses that can vary and therefore modify our profit.
In the image above you can see that the formula to calculate the earnings. That depends on the values of the sales and the fixed and variable expenses. If we can construct several scenarios where we modify the amounts that are variable (sales and variable expenses). Then we can easily know how the profits will be affected in each case.
Create the Sensitivity Data Analysis Excel
Before creating the sensitivity data analysis Excel I must add the data for which the different scenarios in Excel will be created. As you can see, in row 7 I have placed different amounts of sales. Then in column B (below the previous calculations) I have placed different amounts of variable expenses.
In order to create the sensitivity data analysis in Excel we must select the data range B10:F14. Then press the What-If Analysis button that is on the Data tab and then select the Data Table… command
In the option Row Input cell I will place the reference to the cell that contains the original sales amount ($B$3). And as Column input cell I will specify the cell that contains the variable expenses ($B$5). Pressing the OK button will perform the calculations to obtain the gains for each of the combinations of values indicated.
Sensibility Analysis Results
With this data table we can analyze and reach very interesting conclusions about our information. We can see that if the sales were actually $65,000. The variable expenses rose to $35,000, then we would begin to have losses in the business.
On the other hand, you can see that the point of financial equilibrium occurs when sales are $75,000 and variable expenses are $40,000. That leaving us without losses or gains. In short, we can spend considerable time analyzing the information to be able to decide if it is worth investing our money in this business.
What Excel has done behind the scenes is that for every possible sales value and variable costs that we have specified. You have calculated the profit formula that is in cell B7. This is a great benefit for us because instead of replacing the values in cells B4 and B6 to see the behavior in the gains Excel will perform all the calculations in one step and place them in the data table.
The analysis will become interesting if for each investment project you have an Excel sensitivity data analysis. That allows to compare them and decide on the alternative. That represents the lowest possible risk and that has the highest profitability.