When carrying out the most various researches the so-called correlation and regression analysis is applied. It is the statistical method investigating interrelation between one dependent variable and several independent. At the same time the method does not give the chance to estimate the cause and effect relations. The regression analysis is quite widely applied in financial analysis of the enterprises.
Instruction
1. Use the analysis package which is built in Microsoft Office of Excel for carrying out the regression analysis. Open the program and prepare it for work.
2. Choose in the menu the Service/analysis command data/correlation for creation of a matrix of coefficients of correlation. It is required for assessment of force of influence of factors at each other and on a dependent variable.
3. At creation of regression model you make an assumption that there is a functional independence of the studied variables. If between factors there is a communication called multicollinear it makes finding of parameters of the constructed model impossible, or will significantly complicate interpretation of results of modeling.
4. To bring model to the state demanded for the regression analysis, include in it one of the factors which are functionally connected with other significant factors. At the same time it is necessary to choose that factor which is most connected with a dependent variable. Achieve that the coefficient of pair correlation between two studied variables did not exceed 0.8 that excludes the multicollinearity phenomenon in basic data.
5. Having constructed a matrix of coefficients of pair correlation, calculate characteristics of exponential and linear regression models. Use for calculation of both parameters the corresponding functions of a package and the Regression tool in a superstructure of a package of the analysis of MS of Excel.
6. For exponential and linear models of the analysis separately consider cases when the argument "Constant" is equal in the corresponding functions of a package to Truth and Lie values.
7. Finish the analysis conclusions about that, the coefficients entering model and also about whether the received model is adequate to the actual basic data are how significant. Define a type of the model which is most precisely describing basic data. Using the chosen model, calculate its expected values. If the divergence between the actual and design data is revealed, determine its size. In conclusion for descriptive reasons reflect calculations in graphics.