QM 520-U. S. Steel Corporation strategic planner believes

There are two parts to this Case Study this week. Each part is worth 25 points….e the attached MS Excel Workbook to enter your answers. There is a separate tab in the Workbook for Part A, and PartB answers. Part A – Situation:U. S. Steel Corporation strategic planner believes he can predict steel production for his company based on annualvehicle sales in United States, since the big three auto companies are the major purchaser of steel from U. S. Steel. Hegathers the following data for the last ten years:U. S. Annual Vehicle Sales(in millions) Steel Production(100,000s of tons) 12.86 67.6 13.20 72.0 14.61 75.4 14.81 74.9 15.14 76.2 15.57 77.1 16.01 81.8 16.51 82.6 17.21 84.3 17.60 89.1 Based on steel production, can we predict the annual vehicle sales in the United States? Action:a. Use the data above to develop a regression model to predict steel production based on U. S. annual vehicle sales.What is the form of the regression equation?b. Comment on the sign of the slope of the regression equation and its interpretation.c. What percent of the data is explained by the estimating regression equation?d. Comment of the strength of the linear relationship between the two variables.e. Determine whether the slope of the regression equation is statistically significant with 5% as the significance level.f. Determine whether there is a statistically significant POSITIVE relation that exists between the two variables x andy. Part B – Situation:Can we predict the price of gold based on copper, silver, and aluminum prices?GoldCopperSilverAluminum($ per ounce) ($ per pound) ($ per ounce) ($ per pound) 159.21312.80598.99475.82380.00534.87 0.630.991.100.870.741.21 4.9012.3017.1011.109.3216.21 0.400.710.820.740.810.88 Action:Using Excel, conduct a multiple regression analysis and answer the following questions:a. What is the linear equation predicting the price of gold based on copper, silver, and aluminum prices?b. Conduct a test of hypothesis to test the overall significance of the multiple regression model based on significancelevel of 5%. Justify answer based on Excel generated output data.c. Determine which of the independent variables are statistically different from zero. Justify answer based on Excelgenerated output data.d. Comment on strength of the multiple regression model. Justify answer based on Excel generated output data.