& Cut Calibri LE Copy Paste BIU K16 Format Painter Clipboard A 1 Information: Font 11 ' == A Wrap Text General Normal Bad Merge & Center $ % 9 500 00 Conditional Format as Formatting Good Neutral Table Alignment Number Styles B C D E F G H I < < | < | >> > AutoSum TTX T Fill Insert Delete Format Clear Sort & Find & Filter Select Sensitivity Add-ins Analyze Data Cells Editing Sensitivity Add-ins J K L M N P R S T U V W X Y Z AA AR AC AD Your company is preparing an estimate of its production costs for the coming period. The controller estimates that direct materials costs are $50 per unit and that direct labor costs are $24 per hour. Estimating overhead, which is applied on the 2 basis of direct labor costs, is difficult. The controller's office estimated overhead costs at $4,100 for fixed costs and $18 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 4 as follows: 6 Overhead = $10,855 + $15.82 per unit 7 Lance also reports that the correlation coefficient for the regression is 0.81 and says, "With 81% of the variation in 8 overhead explained by the equation, it certainly should be adopted as the best basis for estimating costs." 9 10 When asked for the data used to generate the regression, Lance produces the following: Question 2: Run regression analysis based on the original data with an outlier Now Run the regression with the data: Click on "File". Go to Excel "Options". Click on "Add-In", select "Analysis ToolPak", and press OK. You will find "Data Analysis" appearing on the right side of the "DATA". Run DataAnalysis 11 12 Month Overhead Unit Production 13 1 $57,556 3,080 Month Overhead it Production 14 2 60,781 3,290 1 $57,556 3,080 15 3 77,273 4,220 2 60,781 3,290 16 4 56,594 3,050 3 77,273 4,220 17 5 81,800 3,450 4 56,594 3,050 18 6 72,347 3,960 5 81,800 3,450 19 7 63,969 3,380 6 72,347 3,960 20 8 73,547 4,060 7 63,969 3,380 21 9 77,612 4,170 8 73,547 4,060 22 10 60,247 3,240 9 77,612 4,170 23 11 61,528 3,410 10 60,247 3,240 24 12 73,884 4,130 11 61,528 3,410 25 13 73,316 3,930 12 73,884 4,130 26 13 73,316 3,930 The company controller is somewhat surprised that the cost estimates are so different. You have therefore been assigned to 27 check Lance's equation. You accept the assignment with glee. 28 29 30 31 32 33