& safari File Edit View History Bookmarks Window Help % Wy T Q S SatSep28 5:19PM @ ng.cengage.com Updates Available Do you want to install the updates now or ol el X 7 I try tonight? 22 CENGAGE | MINDTAP Q_ Search this course 14.3 Excel Activity 1b - Regression Equations, Predictions, and Coefficient of Determination with XLMiner Analysis ToolPak (Structured) x Question1 @ 7.510 PROFILE H 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 LR S then answer the questions that follow. Due to a recent change by Microsoft you will need to open the XLMiner Analysis ToolPak add-in manually from the home ribbon. Screenshot of ToolPak Open spreadsheet COURSES v a. Compute b 1 and b (to 1 decimal). by 1 bo Study Tools College Success Tips Career Success Tips Complete the estimated regression equation (to 1 decimal). y= 10466 + 7.6 x Help . According to this model, what is the change in cost (in dollars) for every unit produced (to 1 decimal)? 7.6 ' [> SIGN ouT . Compute the coefficient of determination (to 3 decimals). Note: report r 2 between 0 and 1 0.9587 ) What percentage of the variation in total cost can be explained by the production volume (to 1 decimal)? 9580 % 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)? $ 4847 () Check My Work U m Autosaved at 5:19 PM Ll Jol LR el ooil | ReRelNha) ) Safari File Edit View History Bookmarks Window Help Q Sat Sep 28 5:19PM ng.cengage.com C + ChatGPT Course He.. POKEMON... Cengage L... MindTap -.. Excel Onli... Excel Onli.. Cengage L... (~);} 0662:... Course Hero C Search Re.. of template - Saved Search for tools, help, and more (Option + Q) File Home Share Page Layout Review Help Draw Editing Comments Insert Formulas Data Q Arial v 10 AA Wrap Text General U = Insert Delete Format Sort & Find & Add-ins Show Paste BIU Dab dov Merge & Center v 9 Conditional Format As Cell = = PX Formatting Table Styles Filter ~ Select v ToolPak Styles Cells Editing Add-ins Commands GI V Undo Clipboard Font Alignment Number B12 X V fx K M N O B C D E F G H Production Volume (units) Total Cost ($) SUMMARY OUTPUT 400 3800 450 4800 Regression Statistics 550 5200 Multiple R 0.979127101 60 5700 R Square 0.958689879 700 6200 Adjusted R Square 0.948362349 750 6800 Standard Error 241.5229458 Observations 10 Production Target Est. Cost ($) ANOVA 11 500 4847 df SS MS F Significance F 12 Regression 1 5415000 5415000 92.82857143 0.00064897 13 Residual 4 233333.3333 58333.33333 14 Total 5 5648333.333 15 16 Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95% Upper 95% 17 Intercept 1046.666667 464.1599341 2.254969871 0.087164229 -242.0479073 2335.381241 -242.0479073 2335.381241 18 Production Volume (units) 7.6 0.788810638 9.634758504 0.00064897 5.409910571 9.790089429 5.409910571 9.790089429 19 20 Cost Estimation 21 7000 22 23 24 6500 25 ............' 26 27 E Sheet1 + Give Feedback to Microsoft - 100% + Workbook Statistics SEP 28 T