Question

An important application of regression analysis in accounting is in the estimation of cost. By collecting data on volume and cost and using the least squares method to develop an estimated regression equation relating volume and cost, an accountant can estimate the cost associated with a particular manufacturing volume.

In the Microsoft Excel Online file below you will find a sample of production volumes and total cost data for a manufacturing operation. Conduct a regression analysis to explore the relationship between total cost and production volume and then answer the questions that follow.

PLEASE REFER TO BOTH PICTURES IN ORDER TO CORRECTLY ANSWER ALL PARTS OF THIS QUESTION. THANK YOU :)

Video Excel Online Structured Activity: Regression Equations and the Coefficient of Determination An important application of regression analysis in accounting is in the estimation of cost. By collecting data on volume and cost and using the least squares method to develop an estimated regression equation relating volume and cost, an accountant can estimate the cost associated with a particular manufacturing volume In the Microsoft Excel Online file below you will find a sample of production volumes and total cost data for a manufacturing operation. Conduct a regression analysis to explore the relationship between total cost and production volume and then answer the questions that follow Open spreadsheet a. Compute b1 and bo (to 1 decimal). b1 bo Complete the estimated regression equation (to 1 decimal). b. According to this model, what is the change in cost (in dollars) for every unit produced (to 1 decimal)? c. Compute the coefficient of determination (to 3 decimals). Note: report between 0 and 1. 2- What percentage of the variation in total cost can be explained by the production volume (to 1 decimal)? d. The company's production schedule shows 500 units must be produced next month. What is the estimated total cost for this operation (to the nearest whole number)? Check My Work Reset Problem 1 Production Volume (units) Total Cost (S)After reading these instructions delete all text in this shaded area 3600 4600Use the XLMiner Analysis ToolPak to conduct your regression analysis 5000 5500After deleting all text in this shaded area, set the output range in the ToolPak to the top left cell of this area (D1) 6000 6600 400 450 550 600 700 750 Your regression output should fit into this shaded area Est. Cost ($) 10 Production Target 500 12 13 14 15 16 17 18 19 20 21 Cost Estimation 23 24 25 26 27 28 29 30 31 32 6500 5500 5000 34 35 4500 36 37 38 39 40 41 42 43 3500 800 750 700 650 600 550 500 450 350 45 46 47 48 Production (Units)

Answer 1

prodduction volume total cost SUMMARY OUTPUT 3600 4600 5000 5500 6000 6600 400 450 550 600 700 750 Regression Statistics 0.9791 0.9587 0.9484 241.5229 Multiple R R Square Adjusted R Square Standard Error Observations ANOVA df Significance F 0.0006 MS Regression Residual Total 1 5415000.0000 5415000.0000 4 233333.3333 58333.3333 5 5648333.3333 92.8286 Coefficients Standard Errort Stat P-value Lower 95% Upper 95% 846.6667 7.6000 464.1599 0.7888 1.8241 9.6348 0.1422 -442.0479 2135.3812 9.7901 Intercept prodduction volume 5.4099 0.0006

on gad 846-7 0.953V tyuh = 4646.7 n 4(47 Answer드