Your company is preparing an estimate of its production costs for the coming period. The controller estimates that direct materials costs are $65 per unit and that direct labor costs are $27 per hour. Estimating overhead, which is applied on the basis of direct labor costs, is difficult The controller's office estimated overhead costs at $4.400 for fixed costs and $21 per unit for variable costs. Your colleague, Lance, who graduated from a rival school, has already done the analysis and reports the correct cost equation as follows: Overhead = $9,769 + $15.97 per unit Lance also reports that the correlation coefficient for the regression is 0.81 and says. With 81% of the variation in overtoad explained by the equation, it certainly should be adopted as the best basis for estimating costs." When asked for the data used to generate the regression, Lanos produces the following: Month Overhead Unit Production 1 $57,135 3,110 2 80,848 3 320 3 77.181 4.250 140 4 56,371 3080 82,100 3480 72,230 3.990 7 63,796 3,410 73,674 4090 77,753 4200 10 80,103 3270 11 61,612 3440 12 73,744 4,160 13 73,385 3960 15 B The company controller is somewhat surprised that the cost estimates are so different. You have therefore been assigned to check Lance's equation. You accept the assignment with glee. Required: Use Excel to perform a regression analysis to analyze Lance's results and state your reasons for supporting or rejecting his cost equation. (Round "Intercept" value to 5 decimal places and "Unit Production" to 7 decimal places.) Coefficients Intercept Unit Production What is the cost equation with the new results? (Round variable costs per unit to 2 decimal places) Overhead Units. Cost = Prepare a scattergraph relating overhead cost to the number of units produced. Instructions: 1. On the e graph below, use the point tool to plot the point corresponding to the number of units produced and its relating overhead cost (Month 1). 2. Repeat the process for the remainder of the sample (Month 3 Month 3, ...). 3. To enter exact coordinates, double click on the point and enter the exact co-ordinates of x and y. Interpretation of Regression Results Simple Regression Month 1 esc Month 2 181 ICE- Month Overtus est Month 4 CCX PTT Month 5 PE Month 6 22 one E Month Unt Productor reset