Sensitivity analysis in Excel
Sensitivity analysis in Excel , which should be introduced to examine the effectiveness of other external variables, based on input variables, a statistical model is called sensitivity analysis. It can be said that it is one of the methods through which to To change the inputs of a statistical model in order to predict the effects of these changes in the final stage of the model in its output, and thus determine the sensitivity of the model and its output variable based on any other input variable. The method used.
For a simpler and easier expression of the introduction of sensitivity analysis can be expressed as one of the methods through which to examine external and independent variables on a series of input dependent variables that thus on Examines a set of hypotheses to be effective. And through this method is used for other specific boundaries, and given that sensitivity analysis in Excel software is one of the best options, the user can use the option Its various variants to perform any analysis on its inputs.
In Excel, for sensitivity analysis, which is also called sensitivity analysis, and through the data table tool, which is one of the tools in the what if analysis menu, you can use it for analysis. Using this tool, by using it, you can use it to check the result of changes of different variables on the main parameters of your data, and through this, the effect of one variable or two variables on the variable can be Check your dependent In fact, in order to be able to create one-variable and two-variable analyzes on your data, we will review each of them and introduce them to you.
Analysis of a variable in the data table and sensitivity analysis in Excel
Sensitivity analysis in Excel – This type of analysis is used when you want to see the result of your changes as a variable in your formula, and for a deeper understanding of this issue, it should be stated with an example so that you can be more aware of it. So suppose, for example, that the cost of fueling a car’s energy for every 20 units of that area is one kilometer, and when you want to measure the effect of the distance traveled by the car on the cost of fuel for it. Analyze all distances of 1 to 10 km.
To do this, you have to use the formula A1 * 240 = and write it in cell A2 and finally put the numbers 1 to 10 in cells B1 to K1. Now you have to put the cells of the formula along with other cells for values. Select the variable you have placed. Immediately refer to the data tab in Excel environment and in the data tools section, and in this section, select the what if analysis option and click on it to create another menu. It should be shown to you that in this menu you should also select the data tools option.
Finally, after selecting the above option, it will show you another window in which you will see red arrows in front of two options. Now you have to click on the red arrow in the first option, which includes a row input cell. Click. And then immediately causes another window to open for you. In this window, select the cell that is related to your variable and click on it. In this part, our variable cell also contains A1. Finally, after selecting the variable cell, you will be returned to the red arrow of the main tab again, in which you must click on the ok option, and then you will see that the cost is related to different kilometers. It was displayed to you in the various cells that you placed and thus the analysis is displayed to you.
Bivariate analysis in data table and sensitivity analysis in Excel
Sensitivity analysis in Excel – As mentioned in the previous section, to analyze a variable, the tool in question was used in that section, which we want to examine how to check two variables in the data table to see the result on your data. Therefore, in this section, we will explain it to you through an example. Now, in this section, you will see that the growth or decrease in sales that exist in an organization can be changed according to two variables, and Variables that affect the sales of this organization also include the variable of advertising and also the variable of the quality of the product.
So that any increase that is increased through advertising in this organization is 17% and as much as the decrease in product quality is provided, the sales of the organization will be reduced by 18%. Therefore, in this section, we decide to examine the effect that these two variables have on the rate of increase and decrease in sales of an organization. Given that the amount of scope that exists for variables to reduce product quality and between half to 3 units And to increase the ads, its variable is between 0 to 5 units, so for the number in cell A3 in Excel environment, the formula for increasing and changing the sales of the organization should be written as A1 * _0.3 + A2 * 0.17 =.
Now, according to this formula, you know that cell A1 is one of the variables of quality reduction and also cell A2 is one of the variables of increasing advertising. Now in the next part, in cells B3 to G3, other numbers related to the domain should be The changes are quality reduction and we have mentioned it in the previous section, and in cells A4 to A9, the numbers related to the range of changes should be