Visual basic and excel

Question: Discuss about the Visual basic and excel. Answer: The scatter plot for the given data set of companys sales in the time period 2000 to 2011 is given below: Figure 1: Scatter plot of companys sales (Source: created by author) The chart shows that the companys sale has increased with the increase in national income. The national income was seen to increase with time over the years. The scatter plot shows that the companys sale is also increasing with the increase in national income. The data was used to develop an estimated regression equation, which could be used to estimate the companys sales based on the national income (Kleinbaum et al., 2014). On performing the regression analysis, the output was found as follows: SUMMARY OUTPUT Regression Statistics Multiple R 0.893639481 R Square 0.798591522 Adjusted R Square 0.778450674 Standard Error 21.80419761 Observations 12 ANOVA df SS MS F Significance F Regression 1 18850.69 18850.69 39.65034 8.94E-05 Residual 10 4754.23 475.423 Total 11 23604.92 Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0% Intercept 328.9816769 34.89144 9.428723 2.72E-06 251.2387 406.7246 251.2387 406.7246 national income ( in millions of dollars) X 0.534023863 0.084808 6.296852 8.94E-05 0.34506 0.722988 0.34506 0.722988 Table 1: Output of the regression analysis (Source: created by author) It was seen the estimated model of the regression is y = 328.98167 + 0.534023863 * x; where y is the Companys sales in thousand dollars and x is the national income in millions of dollars. On performing the t-test of equal variance, it was seen that the p-value of the test less than 0.05 level of significance. This suggests that the test is significant. The null hypothesis was considered to be the coefficients of regression is equal to zero and the alternative hypothesis was considered to be the coefficients of regression is not equal to zero. Since the test is significant, H0 is rejected and H1 is accepted (Pearson et al., 2012). This suggests that the coefficients of regression are not equal to zero. From the values of the coefficients of regression, it could be said that in absence of national income in millions of dollars, the companys sales would be 328.981 thousand of dollars (Draper Smith, 2014). The national income had a positive influence on the companys sales and it affects the companys sales with the coefficient of 0.534023863. On conducting the F-test in order to determine the overall significance of the relationship between the variables it was seen that F statistics was found to be 0.136813 and the p value for one-sided tail was 3.28 * e-5 (Sun Kim, 2015). The considered level of significance for this test was 0.05. At this level of alpha value, it was seen that the p value of the test was less than 0.05. The test was thus significant and the variables attend and books effect the grades. T-test was also conducted at 0.05 level of significance assuming equal variance. It was seen that the p value of one tailed test was less than 0.05 for books and grades and for attend and grades (Xie, 2013). This suggests that the t-test was significant for both books and grades and attend and grades. At 0.05 level of significance, it could be concluded that both the tests are significant and the variables books and attend influence the grades of the course. The model in part (a) states that the variable books had a positive influence on the variable grades and it influences the grades 4.299867 times (Pearson et al., 2012). It was also seen that the variable attend positively influences the variable grades 1.540879 times. Thus, the variable books had more positive influence on grades over attend. The hypothesis considered for this test is as follows: H0 : the overall regression relationship do not exist H1 : the overall regression relationship exists On performing the F-test at 0.05 level of alpha, it was seen that the p value of the test was less than 0.05. This suggest that the test is significant and it could be concluded that the overall regression relationship exist for this data set. T test is performed on each of the variables to determine its significance. The result of the t-test shows that the p value of the t-test between annual income and age is less than 0.05 level of alpha (Cameron Trivedi, 2013). This denotes the t-test is significant and the value of age has an effect on the annual income of the employees. The t-test between annual income and length of tenure in current employment (no. of years) had the p value of 1.69929e-8. This value is less than 0.05, which results that the test is significant. Thus, the variable length of tenure in current employment (no. of years) had influence on the annual income of employees. P-value was found to be 1.70231e-8, when one-tailed t-test was conducted between annual income and education (number of years) (Kruschke,, 2013). This value is less than 0.05. This suggests that the test is significant and education (number of years) affect the annual income of the employees. All the variables were found to be significant at 0.05 level of alpha for this test. There was no non significant variables and thus, there is no need to remove any variables that are non-significant at 0.05 level of alpha to get a new regression equation (Kleinbaum et al., 2014). The solution of this question is done in excel sheet named question 4. The multiple regression model developed to account the linear trend and seasonal effect in data was found to be y= 925.0287 = 3.260675 * t, where t is the time period (Box et al., 2015). Figure 2: Line diagram of the original data showing trend and seasonal component (Source: created by author) The forecasted value was found to be as follows: time period forecast 13 1352.72 14 1413.005 15 1372.66 16 1309.129 Table 2: Forecasted value of the quarters of next year (Source: created by author) References Pearson, R. A., Barber, A. C., Rizzi, M., Hippert, C., Xue, T., West, E. L., ... Luhmann, U. F. O. (2012). Restoration of vision after transplantation of photoreceptors. Nature, 485(7396), 99-103. Sun, Y., Kim, M. S. (2015). Asymptotic F-Test in a GMM Framework with Cross-Sectional Dependence. Review of Economics and Statistics, 97(1), 210-223. Xie, L., Kang, H., Xu, Q., Chen, M. J., Liao, Y., Thiyagarajan, M., ... Takano, T. (2013). Sleep drives metabolite clearance from the adult brain. science, 342(6156), 373-377. Kruschke, J. K. (2013). Bayesian estimation supersedes the t test. Journal of Experimental Psychology: General, 142(2), 573. Kleinbaum, D., Kupper, L., Nizam, A., Rosenberg, E. (2013). Applied regression analysis and other multivariable methods. Nelson Education. Draper, N. R., Smith, H. (2014). Applied regression analysis. John Wiley Sons. Cameron, A. C., Trivedi, P. K. (2013). Regression analysis of count data (Vol. 53). Cambridge university press. Box, G. E., Jenkins, G. M., Reinsel, G. C., Ljung, G. M. (2015). Time series analysis: forecasting and control. John Wiley Sons.