PROJECT 3 INSTRUCTIONS!

USE EXCEL PROPERLY!!!!!

Please download the template attached and complete the requirements for this project using my template. You should upload the one Excel file to this assignment by clicking on the Project 3 link. This project has 2 parts set up within the workbook on separate worksheets. You will need to use the Data Analysis add on Tool in Excel for part 2, so make sure you do not wait until the last minute to complete this project in case you have problems. Please make sure you save your file using the first initial of your first name along with your last name. If you have generic questions, please ask them in the office forum and for more specific questions please e-mail me directly. Make sure you review the demonstrations from chapters 5 and 6 in the course documents and the textbook. Good Luck!

This project requires extensive use of excel but the professor has provided the template.

Project 3 Due August 7th by 11:59 pm central Name: TYPE in your name here Please use this template below to solve project 3 which has 2 parts on separate worksheets. Save your file using your first initial, last name, name of Project. Upload your solution by clicking on the project 2 assignment link. Make sure you type your name in cell A3 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 problem solution is worth a total of 15 pts. I have a demonstration exercise that demonstrates the traditional and the ABC method and an audio PowerPoint of this process in the chapter 5 folder . I have an extensive regression demonstration in the chapter 6 folder. I do not pregrade projects, so if you have questions please make sure you ask a specific question. Grading Rubric: I will take off 10 pts. if you do not use the excel application appropriately. Part 1 Worth 5 pts. Part 2 Worth 10 pts. Simple regression worth 2 1/2 pts., Scattergraph 2 pts., Multiple regression 2 1/2 pts., each estimation worth 1 pt. each, and comments 1 pt. Project 2 Objectives: 1. Analyze manufacturing overhead 2. 3. 4. 5. 6. Develop a comparison of application of manufacturing overhead using a traditional and an ABC approach. Recommend a course of action on how to apply manufacturing overhead. Develop scattergraphs, simple regression output tables, and multiple output table. Develop cost estimation based on your regression analysis. Analyze results and recommend the best approach for the cost estimation. Part 1--covers chapter 3 using a predetermined overhead rate and chapter 5 using ABC XYZ company manufactures chemicals used in radiological imaging systems. The company had originally used machine hours as the cost driver to develop one predetermined overhead rate. You have been recently hired by this company to review the costing of chemicals and realize that maybe a predetermined overhead rate does not give an accurate cost picture. You remember discussing overhead cost issues in MBA642 and want to implement ABC, so you have done some research and come up with the following activity cost pools and cost drivers. Activity cost Pool Material handling Hazardous waste control Other overhead costs Total Budgeted overhead cost Budgeted Overhead Cost driver Cost 20,000 Weight of raw materials in pounds Weight of hazardous chemical 10,000 used in pounds 200,000 Machine hours $230,000 Budgeted Level Activity for cost Driver Pool Rate 40,000 $0.50 5,000 50,000 Required: 1. Using the information above calculate the single predetermined overhead rate this company is using based on machine hours as the cost driver. Predetermined overhead rate: 2. If the company uses the ABC approach, what would be the pool rates for the activities you have identified above. I have computed the first activity pool rate above in column E and you should compute the remainder of the rates for the other 2 activities. 3. An order for radiological development chemicals has the following production requirements: Material handling Hazardous waste control Other overhead costs 1000 Raw material in pounds 600 Hazardous Chemical used in pounds 1500 Machine hours If the company is using the predetermined overhead rate based on machine hours, what is the overhead cost for this order? Total Overhead cost of this order using a predetermined overhead rate If the company is using the ABC method, what is the overhead cost for this order? Material handling Hazardous waste control Other overhead costs Total Overhead cost of this order using ABC Part 3 Write a short report (at least 75 words) that identifies factors that explain why the two product-costing systems result in differing costs. Which system do you recommend? Why? Part 2 Please make sure you have reviewed the demonstration in the Chapter 6 folder before attempting this problem. I have an audio step by step excel explanation of multiple regression, simple regression, and the scattergraph. In the chapter 6 folder there are a word tutorial and my excel solution the is available to help you understand the regression process. You need to have the data analysis tool available to complete this project. It would be good practice for you to try to prepare the demonstration problem using my tutorial before attempting to solve this project. This project focuses on chapter 6, but ties in chapter 5 by trying to choose an independent variable that relates to the cost we are trying to measure. 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 Meals produced Deliveries 1 2 3 4 5 6 7 8 9 10 1,340 1,180 1,050 930 840 780 700 630 680 760 12,690 11,980 10,950 10,280 9,020 8,130 7,540 6,980 8,930 9,800 Total Overhead Cost $ 69,094 64,927 60,332 57,953 55,984 53,119 52,706 53,874 53,445 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. Complete the following 4 Requirements: 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 cannot 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. You should use the applicable regression table to estimate the cost by referencing the cost drivers below and the output from the applicable regression table. Cost Driver usage for the independent variables, which are Meals produced and Deliveries: Meals produced 10,500 Deliveries 1,000 Solution: Meals Produced Answer: $ 60,012.54 You need to answer: Deliveries Answer: Guidance: I have estimated the overhead costs using the simple regression table output and the estimated cost driver level. Please click on the cell with my answer and you will see that I have referenced the simple regression output worksheet that uses meals as the independent variable. 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. SUMMARY OUTPUT Regression Statistics Multiple R 0.9218936642 R Square 0.8498879282 Adjusted R Square 0.8311239192 Standard Error 2286.5034903 Observations 10 ANOVA df Regression Residual Total Intercept X Variable 1 Y=31261.39+2.74X X=Meals SS MS F Significance F 1 236798946.412 236798946.41 45.293515319 0.0001480556 8 41824785.6885 5228098.2111 9 278623732.1 Coefficients Standard Error t Stat P-value Lower 95% 31261.386836 3984.24716921 7.8462468587 5.02014E-005 22073.6963955 2.7382048976 0.4068627486 6.7300457145 0.0001480556 1.7999777177 Upper 95% Lower 95.0% Upper 95.0% 40449.077276 22073.696396 40449.077276 3.6764320776 1.7999777177 3.6764320776 Total Overhead Cost $80,000 $70,000 $60,000 f(x) = 2.7382048976x + 31261.3868357893 R = 0.8498879282 Total Overhead Cost Linear (Total Overhead Cost) $50,000 $40,000 $30,000 $20,000 $10,000 $6,000 7,000 8,000 9,000 10,000 11,000 12,000 13,000 14,000