Accounting Excel Project Assignment

Please use this template below to solve project 2 which has 2 parts on separate worksheets and save your file using your first initial, last name, name of assignment, so hdedenproblem2.xlsUpload your solution in area 2 of the project 2 assignment area. Make sure you type your name in cell A3You should always verify that your problem has been loaded correctly by either going back to the assignment and clicking on Ok and you shouldsee 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 yourresponsibility to take the time to ensure 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, too,in course documents chapter 5 folder and an extensive regression demonstration in the chapter 6 course document folder.I will post my solution at 8 am on January 9th, so I will not accept any submissions late for partial credit.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.Please make sure you read the academic honesty policy, since I want to make sure everyone realizes that they should not be discussingthis problem outside the discussion board. This is an individual assignment and should be completed by each student on their own.I realize some of you maybe best friends or relatives, but this is an individual assignment and you should not be working as a team.Academic Honesty Policy: Each student is assumed to be a mature andresponsible adult with high academic and moral standards. There is noplace in the business profession or the classroom for dishonesty.Therefore, any student found cheating or presenting material in a dishonestfashion would be dismissed from the course and given a course grade ofF. You should not exchange files or discuss turn in problems or takehome exams outside the discussion board. It is unfair to your classmatesto discuss turn-in problems and take home exams via e-mail or to sendfiles with the solution to any member of the class. I always have adiscussion thread set up for students to ask questions, so this is the placeyou should ask questions on these assignments and exams. Remember, itdoes not matter what type of assignment it is, if you are found cheating youwill receive an F for the course and the College of Business will beinformed of your actions for further proceedings.Part 1Industries Inc. manufactures 2 products: A and B. A review of the company’s accounting records revealed the following per-unit costs and production volume:Product AProduct BProduction volume(units)30005000Direct labor hours used23Direct material cost per unit$30Direct Labor cost: $14 per hour2 hours at $143 hours at $14$50$28$72Manufacturing overhead2 hours at $823 hours at $82$164.00$246Manufacturing overhead is budgeted at :$1,722,000Required:a. Using direct labor hours as the base for assigning manufacturing overhead cost to products, compute the direct labor needed and the predetermined overhead rate using direct labor hours.Hint: the predetermined overhead rate you compute should be $100, since I have given you the answer but I want to make sure you know how to determine the $100.Direct labor hours, budget hoursGuidance:Product AProduct BLookatexhibit52onpage172Total direct labor hoursPredetermined Overheadrateper direct labor hourb. Using the predetermined overhead rate you determined in "a" above, determine the unit product cost of each product.Hint: Don’t forget that unit product cost includes direct material, direct labor, and Manufacturing overhead. Don’t make this complicated, since the numbers are already there for you.Product ADirect material cost per unitDirect labor cost per unitManufacturing overhead per unitProduct B$30Guidance:Makesureyouareusingcellreferences,byreferencingthecellsabove.Total product cost per unitc. You are a recent hire to Industries Inc. and you have made it known that you just recently completed an MBA program that had a management accountingcourse that taught you about another costing system that might be appropriate. During a meeting, you suggested that an ABC system might be a moreaccurate costing system. Management has asked you to recompute the unit cost using the ABC costing approach, since the company is showing declining profits.First complete the following chart that is set up like exhibit 5-7 on page 177, and there is a more detailed explanation of the 3 step calculation approach exampleon page 176 exhibit 5-6. I have highlighted the columns in red you need to complete and I have started the table to guide you.ActivitySetupsGeneral FactoryMachine processingGrand TotalActivity Cost poolProductCost driverActivity cost linequantity forfor product productionCost Driver Quantity Pool rateProduct line product linelinevolume100$2,020.00 Product A90$181,800Product B10Total10020,000Product A5000Product B15000Total200002,200Product A1600Product B600Total2200Cost driver$202,000 Number of setups1,300,000 Direct labor hours220,000 Machine hours$1,722,000d. Complete the following table to determine the unit product costs from ABC, just like the exhibit 5-8 on page 178Product ADirect material cost per unitDirect labor cost per unitTotal direct costs per unitProduct B$30Manufacturing overhead(based on ABC)SetupsGeneral FactoryMachine processingTotal product cost per unite. Write a short report (at least 75 words) that identifies factors that may account for the company’s declining profits.Activitycost perunitofproduct3,000$60.605,0003,0005,0003,0005,000Part 2Please make sure you have reviewed the demonstration in the Course Documents areafor chapter 6 before attempting this problem. I have an audio step by step excel explanationof multiple regression, simple regression, and the scattergraph. If you have a dial up modem this could take awhile to download.I do have a word tutorial and my excel solution is also available in the course documents area. You need to have the data analysis tool available.It would be good practice for you to try to prepare the demonstration problem using my tutorial before attempting to solve this problem.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 recentlyhired 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) foroverhead costs. You have decided to include number of deliveries in your analysis, too.MonthMealsproducedDeliveries123456789101,3401,1801,05093084078070063068076012,69011,98010,95010,2809,0208,1307,5406,9808,9309,800TotalOverheadCost$69,09464,92760,33257,95355,98453,11952,70653,87453,44554,869Do 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 thecost 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 thecontrol (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 overheadcost 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 produced11,500Deliveries1,059Solution:Meals ProducedAnswer:$62,750.74You need to answer:DeliveriesAnswer:Guidance:Ihaveestimatedtheoverheadcostsusingthesimpleregressiontableoutputandtheestimatedcostdriverlevel.PleaseclickonthecellwithmyanswerandyouwillseethatIhavereferencedthesimpleregressionoutputworksheetthatusesmealsastheindependentvariable.Multiple regression4. Using the simple and multiple regression analyses above, what would you recommendto 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 OUTPUTRegression StatisticsMultiple R0.921893664R Square0.849887928Adjusted R Square0.8311239192Standard Error2286.50349Observations10ANOVAdfRegressionResidualTotalInterceptX Variable 1Y=31261.39+2.74XX=MealsSSMSFSignificance F1 236798946.412 236798946.4 45.293515319 0.00014805568 41824785.6885 5228098.2119278623732.1Coefficients Standard Errort StatP-valueLower 95%Upper 95% Lower 95.0% Upper 95.0%31261.38684 3984.24716921 7.846246859 5.02014E-005 22073.6963955 40449.07728 22073.696396 40449.0772762.738204898 0.4068627486 6.730045714 0.0001480556 1.7999777177 3.676432078 1.7999777177 3.6764320776Chart Title$ 80,000$ 70,000f(x) = 2.7382048976x + 31261.3868357893R = 0.8498879282$ 60,000$ 50,000TotalO verheadCostLinear Regression for TotalO verheadCost$ 40,000$ 30,000$ 20,000$ 10,000$6 ,0007,0008 ,0009 ,00010,00011,00012,00013,00014,000