I am stuck working through these can you please help me?
Project 1 Name: TYPE in your name here Please use this file as your template to complete project 1 using the Data worksheet to develop responses and generate regression tables and scattergraphs. Save your file using your first initial, last name, and name of Project. Upload your solution by clicking on the project 1 assignment link. Make sure you type your name in cell A2 You should always verify that your project has been loaded correctly by either going back to the assignment and clicking on Ok and you should |see the file you uploaded and you can also go to view grades and click on the exclamation to see the file you have loaded. Remember it is your responsibility to take the time to verify that your file has uploaded correctly. If there are problems, please contact me immediately. You will be graded on the accuracy of your answer and the usage of excel. You must use excel cell referencing in all of your calculations and use the data analysis tool in development of your regression tables. The project 1 solution is worth a total of 30 pts. have an extensive regression demonstration in the Module 15 folder in the course resources area. do not pregrade projects, so if you have questions please make sure you ask a specific question Project 1- Regression Analysis 1. Develop scattergraphs, simple regression output tables, and multiple output table. 2. Develop cost estimation based on your regression analysis. 3. Analyze results and recommend the best approach for the cost estimation Project 1-Part 2 Grading Rubric: 2 Simple Regression Tables-type out equation of line 2 Scattergraphs-provide the trendline and the equation of line and R square Multiple Regression-provide equation of line. Also identify what X2 and X3 variables are. O 4 3 Cost Estimations-using cell referencing Comments Total points possible 4 30 Scattergraph-volume Project1 Simple-Volume DATA Project 1 You have just been hired as a consultant to a large manufacturing company of sodas, and have decided to use your new MBA skills to make a recommendation to management on what should be the cost driver(s) for estimating the monthly overhead cost for one of the plants that manufactures this drink outside the United States Volume of liquid produced hectoliters Total Overhead Number of Month January February March April May June Water used in hectoliters costs for the plant $57,266 Batches 890 54 6,005 980 58 61,020 6,588 1,094 7,336 65 64,622 1,212 73 68,630 8,002 1,262 8,435 75 70,653 1,494 9,940 89 79,927 July August September 1,557 82,867 10,420 95 1,528 94 81,748 10,326 1,215 8,284 72 68,819 October 1,145 69 66,375 7,746 November December January February March 64 1,072 7,168 63,767 1,032 62 62,545 6,933 56,837 872 5,902 50 1,006 6,759 60 61,298 1,04 62 63,791 6,990 Do not retype the information above. You should use this worksheet as your data sheet and your solution should be on separate sheets. Required: 1. Prepare 3 scattergraphs and 3 simple regression analyses**to estimate the overhead costs using each of the cost drivers above. Make sure you show(insert) the equation of the line and R-squared on each of your scattergraphs. The output for your simple regression table should be on a separate sheet and you should type out the equation of the line below the table output. I have prepared the volume of liquid produced scattergraph and regression for you, therefore, you actually only have to do 2. You may want to redo this scattergraph and regression analysis just to make sure you understand the process. If you have done your scattergraph and simple regression correctly the equation of the line and the R-squared should match the respective output To make a scattergraph with data from nonadjacent columns highlight the X data (i.e. independent variable-cost driver) first and then hold down the control (ctrl) key and highlight the Y data (i.e. the dependent variable-cost) 2. Prepare a multiple regression table using all three of the activities as the independent variable. Using the output from the multiple regression, give the equation of the line to estimate factory cost in terms of the 3 cost drivers below the table output Also, make sure you identify which cost driver relates to X1, X2, and X3-I have done the first one for you.(excel reads left to right) Identify the 2 other X variables(l have identified the X1 variable for you, which will be the first column highlighted): X1 X2 Volume of liquid produced Project1 DATA Simple-Volume Scattergraph-volume SUMMARY OUTPUT Regression Statistics Multiple R R Square Adjusted R Square Standard Error 0.998318363 0.996639553 0.996381057 499.9286178 Observations 15 ANOVA Significance F df SS MS Regression Residual Total 1 963608071.2 963608071.2 3855.533073 1.81484E-17 13 3249072.097 249928.6228 14 966857143.3 P-value Upper 95% 21984.195 25083.35021 Coefficients Standard Error t Stat Lower 95% Lower 95.0% Upper 95.0% 21984.195 25083.35021 Intercept 23533.77261 37.76772476 717.2746215 32.80998923 6.87938E-14 X Variable 1 0.608245082 62.09293899 1.81484E-17 36.45369116 39.08175837 36.45369116 39.08175837 Y-23,533.77+37.77X X=Volume of liquid broduced Project1 Simple-Volume Scattergraph-volume DATA Total Overhead costs for the plant $90,000 $80,000 y 37.768x 23534 R2 0.9966 $70,000 $60,000 $50,000 Total Overhead costs for the plant Linear (Total Overhead costs for the plant) $40,000 Linear (Total Overhead costs for the plant) $30,000 $20,000 $10,000 $0 0 200 400 600 800 1,000 1,200 1,400 1,600 1,800 Project1 Scattergraph-volume Simple-Volume DATA Project 1 Name: TYPE in your name here Please use this file as your template to complete project 1 using the Data worksheet to develop responses and generate regression tables and scattergraphs. Save your file using your first initial, last name, and name of Project. Upload your solution by clicking on the project 1 assignment link. Make sure you type your name in cell A2 You should always verify that your project has been loaded correctly by either going back to the assignment and clicking on Ok and you should |see the file you uploaded and you can also go to view grades and click on the exclamation to see the file you have loaded. Remember it is your responsibility to take the time to verify that your file has uploaded correctly. If there are problems, please contact me immediately. You will be graded on the accuracy of your answer and the usage of excel. You must use excel cell referencing in all of your calculations and use the data analysis tool in development of your regression tables. The project 1 solution is worth a total of 30 pts. have an extensive regression demonstration in the Module 15 folder in the course resources area. do not pregrade projects, so if you have questions please make sure you ask a specific question Project 1- Regression Analysis 1. Develop scattergraphs, simple regression output tables, and multiple output table. 2. Develop cost estimation based on your regression analysis. 3. Analyze results and recommend the best approach for the cost estimation Project 1-Part 2 Grading Rubric: 2 Simple Regression Tables-type out equation of line 2 Scattergraphs-provide the trendline and the equation of line and R square Multiple Regression-provide equation of line. Also identify what X2 and X3 variables are. O 4 3 Cost Estimations-using cell referencing Comments Total points possible 4 30 Scattergraph-volume Project1 Simple-Volume DATA Project 1 You have just been hired as a consultant to a large manufacturing company of sodas, and have decided to use your new MBA skills to make a recommendation to management on what should be the cost driver(s) for estimating the monthly overhead cost for one of the plants that manufactures this drink outside the United States Volume of liquid produced hectoliters Total Overhead Number of Month January February March April May June Water used in hectoliters costs for the plant $57,266 Batches 890 54 6,005 980 58 61,020 6,588 1,094 7,336 65 64,622 1,212 73 68,630 8,002 1,262 8,435 75 70,653 1,494 9,940 89 79,927 July August September 1,557 82,867 10,420 95 1,528 94 81,748 10,326 1,215 8,284 72 68,819 October 1,145 69 66,375 7,746 November December January February March 64 1,072 7,168 63,767 1,032 62 62,545 6,933 56,837 872 5,902 50 1,006 6,759 60 61,298 1,04 62 63,791 6,990 Do not retype the information above. You should use this worksheet as your data sheet and your solution should be on separate sheets. Required: 1. Prepare 3 scattergraphs and 3 simple regression analyses**to estimate the overhead costs using each of the cost drivers above. Make sure you show(insert) the equation of the line and R-squared on each of your scattergraphs. The output for your simple regression table should be on a separate sheet and you should type out the equation of the line below the table output. I have prepared the volume of liquid produced scattergraph and regression for you, therefore, you actually only have to do 2. You may want to redo this scattergraph and regression analysis just to make sure you understand the process. If you have done your scattergraph and simple regression correctly the equation of the line and the R-squared should match the respective output To make a scattergraph with data from nonadjacent columns highlight the X data (i.e. independent variable-cost driver) first and then hold down the control (ctrl) key and highlight the Y data (i.e. the dependent variable-cost) 2. Prepare a multiple regression table using all three of the activities as the independent variable. Using the output from the multiple regression, give the equation of the line to estimate factory cost in terms of the 3 cost drivers below the table output Also, make sure you identify which cost driver relates to X1, X2, and X3-I have done the first one for you.(excel reads left to right) Identify the 2 other X variables(l have identified the X1 variable for you, which will be the first column highlighted): X1 X2 Volume of liquid produced Project1 DATA Simple-Volume Scattergraph-volume SUMMARY OUTPUT Regression Statistics Multiple R R Square Adjusted R Square Standard Error 0.998318363 0.996639553 0.996381057 499.9286178 Observations 15 ANOVA Significance F df SS MS Regression Residual Total 1 963608071.2 963608071.2 3855.533073 1.81484E-17 13 3249072.097 249928.6228 14 966857143.3 P-value Upper 95% 21984.195 25083.35021 Coefficients Standard Error t Stat Lower 95% Lower 95.0% Upper 95.0% 21984.195 25083.35021 Intercept 23533.77261 37.76772476 717.2746215 32.80998923 6.87938E-14 X Variable 1 0.608245082 62.09293899 1.81484E-17 36.45369116 39.08175837 36.45369116 39.08175837 Y-23,533.77+37.77X X=Volume of liquid broduced Project1 Simple-Volume Scattergraph-volume DATA Total Overhead costs for the plant $90,000 $80,000 y 37.768x 23534 R2 0.9966 $70,000 $60,000 $50,000 Total Overhead costs for the plant Linear (Total Overhead costs for the plant) $40,000 Linear (Total Overhead costs for the plant) $30,000 $20,000 $10,000 $0 0 200 400 600 800 1,000 1,200 1,400 1,600 1,800 Project1 Scattergraph-volume Simple-Volume DATA
