Data Analytics: Cost behavior
Steam Precision manufactures machined metals and infection-molded plastics for use in industries requiring a high level of accuracy, such as nuclear power generation. The specialized manufacturing processes at Steam Precision require large fixed costs for machinery and equipment. Its variable costs are comprised mainly of precious and rare earth metals, for which prices can be volatile. Because of the mix of fixed assets and direct materials required in manufacturing its products, management at Steam Precision would like to know more about the cost behavior of the products. Management has asked you to provide an estimate of fix costs for two of uts products
Fined cosis ares tarlable costs for twe of iti preducts. X Whi the spreadsheet provided, perferm the folseming siebs: because they proyibe ouobal coceent neteded in anr anaivib. 2. Cpen fecel and prepare ta run a anear recrestan by instaling the Analysis Teolpak and Solver Add-ia. If rou ore usig winasm and have fuckl 7010 and lates, peiform the folowing seps to matal the folyer Aad-in. Lo tu file > Options. 3. Girk add-ins. In the Marape bax, weiect Excel Addelifi. 3. Run a linear regression to estimate fixed and variable costs. A linear regression is a statistical calculation that is used to predict rel. a. Open the Regression tool. 1. Starting on the tab Dataset for Product 1, navigate to the Data tab on the top ribbon. ii. Click Data Analysis. 1. In the dialog box, scroll to find Regression. 2. Select Regression. a. Open the Aroreci in tool. fi cins thata Analyais. 1. to the diogg bien, inen ta fieat Aegression 2. Lestet Aegresuim 3 Cin 0K L. Gik nuge tf * Enpyt \& fange box. 2. Sirwet the cavienss of vekum Ear tride scstiscsso. 3. Cick inude the Tnput x Range nos A. conter it output optione matiani 1. Cias die madie authon Hew Warkaheer Bly. b. Navigate to the Data tab on the top ribbon. c. Click Data Analysis. 1. In the dialog box, scroll to find Regression. ii. Select Regression. iii. Click OK. d. In the Regression dialog box that appears, begin to input the information needed to calculate the regression. i. Click inside the Input Y Range box: ii. Select the contents of column C or type $C$1:$C$50. iii. Click inside the Input X Range box. iv. Select the contents of column B or type $B$1:$B$50. v. Because labels were included in your selection, check the Labels box, vi. Under the Output options section, 1. Click the radio button New Worksheet Ply. 2. Type Regression 2 in the blank space to the right. vii. Select OK. e. Interpret the results of the regrebsion output. Using the dota Mt proviled, provide managenent with the folowing andiries: 1. Estimate fixed and variable costs using a linear regression for the first dataset. (Round vour answers to 2 decimal places.) Fured cost 1 Variable cost : per unit 2. Einmate fixed and varable coests uting a linear regression for the second dataset. (Round your anvers to 2 decienal places.) Fixea cost 1 Warable cost: per unit. 3. Wia this estimate be completely accurate? Why or whiy not? The input in the box beles will not be grased, but may be reviewed and cocsidered by yoor instructor 4. Whet additienal cata pould entiance your akalysis? The input in the bas belon will hat be graded, but eay be reviewed and considered by your instruttor The input in the box below will not be graded, but may be reviewed and considered by your instructor. 4. What additional data could enhance your analysis? The input in the box below will not be graded, but may be reviewed and considered by your instructor. 5. What should management be warned about in regard to the output of the regression analysis? The input in the box below will not be graded, but may be reviewed and considered by your instructor. A B C D SUMMARY OUTPUT \begin{tabular}{|l|l|l|} \hline 2 & & \\ \hline 3 & \multicolumn{2}{|c|}{ Regression Statistics } \\ \hline 4 & Multipie R & 0.93101981 \\ \hline 5 & R Square & 0.86679788 \\ \hline 6 & Adjusted R Square & 0.86396379 \\ \hline 7 & Standard Error & 15179.4259 \\ \hline 8 & Observations & 49 \\ \hline \end{tabular} 10 ANOVA \begin{tabular}{|l|l|l|c|c|c|c|c|c|c|c|} \hline 15 & & & & & & & & \\ \hline 16 & & Coefficients & Standard Error & t Stat & P-value & Lower 95\% & Upper 95\% & Lower 95.0\% Upper 95.0\% \\ \hline 17 & Intercept & 38073.5477 & 6278.861099 & 6.06376652 & 2.1552E07 & 25442.10845 & 50704.987 & 25442.1084 & 50704.987 \\ \hline 18 & Quantity & 8.33014529 & 0.476321623 & 17.4884886 & 3.3058E22 & 7.371909787 & 9.2883808 & 7.37190979 & 9.2883808 \\ \hline \end{tabular} Fined cosis ares tarlable costs for twe of iti preducts. X Whi the spreadsheet provided, perferm the folseming siebs: because they proyibe ouobal coceent neteded in anr anaivib. 2. Cpen fecel and prepare ta run a anear recrestan by instaling the Analysis Teolpak and Solver Add-ia. If rou ore usig winasm and have fuckl 7010 and lates, peiform the folowing seps to matal the folyer Aad-in. Lo tu file > Options. 3. Girk add-ins. In the Marape bax, weiect Excel Addelifi. 3. Run a linear regression to estimate fixed and variable costs. A linear regression is a statistical calculation that is used to predict rel. a. Open the Regression tool. 1. Starting on the tab Dataset for Product 1, navigate to the Data tab on the top ribbon. ii. Click Data Analysis. 1. In the dialog box, scroll to find Regression. 2. Select Regression. a. Open the Aroreci in tool. fi cins thata Analyais. 1. to the diogg bien, inen ta fieat Aegression 2. Lestet Aegresuim 3 Cin 0K L. Gik nuge tf * Enpyt \& fange box. 2. Sirwet the cavienss of vekum Ear tride scstiscsso. 3. Cick inude the Tnput x Range nos A. conter it output optione matiani 1. Cias die madie authon Hew Warkaheer Bly. b. Navigate to the Data tab on the top ribbon. c. Click Data Analysis. 1. In the dialog box, scroll to find Regression. ii. Select Regression. iii. Click OK. d. In the Regression dialog box that appears, begin to input the information needed to calculate the regression. i. Click inside the Input Y Range box: ii. Select the contents of column C or type $C$1:$C$50. iii. Click inside the Input X Range box. iv. Select the contents of column B or type $B$1:$B$50. v. Because labels were included in your selection, check the Labels box, vi. Under the Output options section, 1. Click the radio button New Worksheet Ply. 2. Type Regression 2 in the blank space to the right. vii. Select OK. e. Interpret the results of the regrebsion output. Using the dota Mt proviled, provide managenent with the folowing andiries: 1. Estimate fixed and variable costs using a linear regression for the first dataset. (Round vour answers to 2 decimal places.) Fured cost 1 Variable cost : per unit 2. Einmate fixed and varable coests uting a linear regression for the second dataset. (Round your anvers to 2 decienal places.) Fixea cost 1 Warable cost: per unit. 3. Wia this estimate be completely accurate? Why or whiy not? The input in the box beles will not be grased, but may be reviewed and cocsidered by yoor instructor 4. Whet additienal cata pould entiance your akalysis? The input in the bas belon will hat be graded, but eay be reviewed and considered by your instruttor The input in the box below will not be graded, but may be reviewed and considered by your instructor. 4. What additional data could enhance your analysis? The input in the box below will not be graded, but may be reviewed and considered by your instructor. 5. What should management be warned about in regard to the output of the regression analysis? The input in the box below will not be graded, but may be reviewed and considered by your instructor. A B C D SUMMARY OUTPUT \begin{tabular}{|l|l|l|} \hline 2 & & \\ \hline 3 & \multicolumn{2}{|c|}{ Regression Statistics } \\ \hline 4 & Multipie R & 0.93101981 \\ \hline 5 & R Square & 0.86679788 \\ \hline 6 & Adjusted R Square & 0.86396379 \\ \hline 7 & Standard Error & 15179.4259 \\ \hline 8 & Observations & 49 \\ \hline \end{tabular} 10 ANOVA \begin{tabular}{|l|l|l|c|c|c|c|c|c|c|c|} \hline 15 & & & & & & & & \\ \hline 16 & & Coefficients & Standard Error & t Stat & P-value & Lower 95\% & Upper 95\% & Lower 95.0\% Upper 95.0\% \\ \hline 17 & Intercept & 38073.5477 & 6278.861099 & 6.06376652 & 2.1552E07 & 25442.10845 & 50704.987 & 25442.1084 & 50704.987 \\ \hline 18 & Quantity & 8.33014529 & 0.476321623 & 17.4884886 & 3.3058E22 & 7.371909787 & 9.2883808 & 7.37190979 & 9.2883808 \\ \hline \end{tabular}
