Regression Question
jeanywh20
question.xlsxProject 1
| Project 1 | Please use this file as your template to complete project 1 using the Data worksheet to develop responses and generate regression tables and scattergraphs. |
| Name: Tammie Moore | 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. | |
| I have an extensive regression demonstration in the Module 15 folder in the course resources area. | |
| I 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: | |
| 1 Simple Regression Tables-type out equation of line | 6 |
| 1 Scattergraphs-provide the trendline and the equation of line and R-square | 6 |
| Multiple Regression-provide equation of line. | 6 |
| 2 Cost Estimations-using cell referencing | 6 |
| Comments | 6 |
| Total points possible | 30 |
Data
| Project 1 | |||
| Food services company is looking at the costs involved with their catering services. | |||
| In the past the company has used meals produced as the cost driver for Overhead costs, but you have been recently | |||
| hired as a consultant and have decided to use your new MBA skills to make a recommendation to management on what should be the cost driver(s) for | |||
| overhead costs. You have decided to include number of deliveries in your analysis, too. | |||
| Month | Deliveries | Meals produced | Total Overhead Cost |
| 1 | 1,340 | 12,690 | $ 69,094 |
| 2 | 1,180 | 11,980 | 64,927 |
| 3 | 1,050 | 10,950 | 60,332 |
| 4 | 930 | 10,280 | 57,953 |
| 5 | 840 | 9,020 | 55,984 |
| 6 | 780 | 8,130 | 53,119 |
| 7 | 700 | 7,540 | 52,706 |
| 8 | 630 | 6,980 | 53,874 |
| 9 | 680 | 8,930 | 53,445 |
| 10 | 760 | 9,800 | 54,869 |
| Do not retype the information above. You should use this sheet as your data sheet and your solution should be on separate worksheets. | |||
| When you have completed the requirements you should have this data sheet, 2-simple regression tables, 1-multiple regression table, | |||
| and 2-scattergraphs when you are done. I already did a simple regression and scattergraph using meals produced as the independent variable for you. | |||
| Required: | |||
| 1. Prepare 2 scattergraphs and 2 simple regression analyses***** to estimate the Overhead costs using each of the | |||
| cost drivers above. Make sure you show the equation of the line and R-squared on each of your scattergraphs. | |||
| I have gone ahead and developed the simple regression table and the scattergraph using Meals produced as the cost driver (I.e. independent variable) | |||
| You do not need to redo these, but you may want to use these as a verification that you know how to develop a scattergraph and a simple regression table. | |||
| The output and chart for your simple regression should be on separate sheets that should be named appropriately. | |||
| Type the equation of the line under the regression output on each of the simple regression worksheets. | |||
| 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). | |||
| If you have done your scattergraphs correctly the equation of the line and the R-squared should match the output you have on your regression table. | |||
| (i.e. R-squared, Intercept, and X Variable.) Hint: the Y(dependent) variable is the same in all your scattergraphs and regression analyses. | |||
| 2. Prepare a multiple regression table the 2 independent variables-Deliveries and meals produced. Do not try to prepare a scattergraph, since you can make a chart with multiple X's. | |||
| Using the output from the multiple regression, give the equation to estimate overhead costs in terms of these 2 cost drivers. Excel reads left to right, so X1 will be Deliveries and X2 will be meals produced. | |||
| Type out the equation of the line on your multiple regression worksheet. | |||
| 3. Assuming the following level of cost-driver volume, what is the estimated overhead | |||
| cost using simple regression for each of the cost drivers and using multiple regression to estimate the cost of overhead. You should have | |||
| 3 computations for this answer (you need to determine 2 since I have calculated the first one for you). | |||
| Your calculation should only use cell references, since you should be estimated the cost by referencing the cost drivers below and the output from the regression tables. | |||
| Cost Drivers (independent variables): | |||
| Meals produced | 9,050 | ||
| Deliveries | 845 | ||
| Solution: | |||
| Meals Produced | |||
| Answer: | |||
| $ 56,042.14 | |||
| You need to answer: | |||
| Deliveries | |||
| Answer: | |||
| Multiple regression | |||
| 4. Using the simple and multiple regression analyses above, what would you recommend | |||
| to estimate the Overhead Costs? Why? Make sure you discuss the Coefficient of Determination in your response. Your comments should be at least 75 words. | |||
Simple Reg- Meals
| SUMMARY OUTPUT | ||||||||
| Regression Statistics | ||||||||
| Multiple R | 0.9218936642 | |||||||
| R Square | 0.8498879282 | |||||||
| Adjusted R Square | 0.8311239192 | |||||||
| Standard Error | 2286.5034902791 | |||||||
| Observations | 10 | |||||||
| ANOVA | ||||||||
| df | SS | MS | F | Significance F | ||||
| Regression | 1 | 236798946.411534 | 236798946.411534 | 45.2935153189 | 0.0001480556 | |||
| Residual | 8 | 41824785.6884664 | 5228098.2110583 | |||||
| Total | 9 | 278623732.1 | ||||||
| Coefficients | Standard Error | t Stat | P-value | Lower 95% | Upper 95% | Lower 95.0% | Upper 95.0% | |
| Intercept | 31261.3868357893 | 3984.2471692085 | 7.8462468587 | 0.0000502014 | 22073.6963955091 | 40449.0772760695 | 22073.6963955091 | 40449.0772760695 |
| X Variable 1 | 2.7382048976 | 0.4068627486 | 6.7300457145 | 0.0001480556 | 1.7999777177 | 3.6764320776 | 1.7999777177 | 3.6764320776 |
| Y=31261.39+2.74X | ||||||||
| X=Meals |
Scattergraph-Meals
Total Overhead Cost
12690 11980 10950 10280 9020 8130 7540 6980 8930 9800 69094 64927 60332 57953 55984 53119 52706 53874 53445 54869
