You have recently been hired as a cost accountant at Traveno

You have recently been hired as a cost accountant at Travenol Laboratories. The controller is an \"old school\" accountant and has heard that you recently graduated with a degree in accounting. One day he summons you to his office to assign you a task. He says, \"I understand that recently educated accountants are using a variety of statistical tools to determine causality between costs and their respective drivers. We have been using direct labor hours as our cost driver for our manufacturing overhead costs for as long as I have been here. In the last few years our production processes have become more automated and I am not sure whether direct labor hours is the appropriate allocation basis for our manufacturing overhead costs. I would like you to use some of those statistical tools to determine whether there is a more appropriate cost driver.\"

You leave his office recognizing that this is a tremendous career opportunity. If you can convince your boss that you can use statistical analysis to determine the best cost driver, you will have established yourself in the department as a knowledgeable professional. It is good fortune that one of your projects in your cost class dealt specifically with this type of analysis.

Requirement:

Year	MOH	DLH	DLS	MH	DMS
2000	948,768	7,595	113,932	19,650	149,712
2001	833,153	14,235	173,518	12,767	111,754
2002	753,039	14,997	184,961	12,002	126,155
2003	799,757	12,901	153,511	15,420	140,550
2004	972,624	8,555	168,322	11,107	167,648
2005	967,537	10,565	198,476	13,759	143,981
2006	945,057	12,878	153,169	19,230	110,323
2007	750,112	8,888	93,322	12,319	115,301
2008	884,112	11,287	169,311	13,489	158,897
2009	923,244	10,127	111,900	14,603	167,418
2010	929,320	11,690	215,349	12,126	120,126
2011	785,210	7,707	75,606	11,334	121,555
2012	862,449	12,182	142,734	17,987	101,168
2013	865,873	5,095	36,429	18,015	156,535
2014	804,287	11,464	211,962	15,504	152,855
2015	797,726	9,989	149,840	12,472	148,269
MOH=Manufacturing Overhead			MH=Machine Hours
DLH=Direct Labor Hours				DM$=Direct Material Dollars
DL$=Direct Labor Dollars

Solution

Year MOH DLH DLS MH DMS 2000 948,768 7,595 113,932 19,650 149,712 2001 833,153 14,235 173,518 12,767 111,754 2002 753,039 14,997 184,961 12,002 126,155 2003 799,757 12,901 153,511 15,420 140,550 2004 972,624 8,555 168,322 11,107 167,648 2005 967,537 10,565 198,476 13,759 143,981 2006 945,057 12,878 153,169 19,230 110,323 2007 750,112 8,888 93,322 12,319 115,301 2008 884,112 11,287 169,311 13,489 158,897 2009 923,244 10,127 111,900 14,603 167,418 2010 929,320 11,690 215,349 12,126 120,126 2011 785,210 7,707 75,606 11,334 121,555 2012 862,449 12,182 142,734 17,987 101,168 2013 865,873 5,095 36,429 18,015 156,535 2014 804,287 11,464 211,962 15,504 152,855 2015 797,726 9,989 149,840 12,472 148,269 MOH=Manufacturing Overhead MH=Machine Hours DLH=Direct Labor Hours DM$=Direct Material Dollars DL$=Direct Labor Dollars Refression on DLH Using Data analysis with regression tool: SUMMARY OUTPUT Regression Statistics Multiple R 0.193764 R Square 0.037544 Adjusted R Square -0.0312 Standard Error 78215.79 Observations 16 ANOVA df SS MS F Significance F Regression 1 3341045092 3.34E+09 0.546127 0.472113755 Residual 14 85647932515 6.12E+09 Total 15 88988977607 Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0% Intercept 924361.6 84130.142 10.98728 2.88E-08 743920.411 1104803 743920.4 1104803 X Variable 1 -5.6861 7.694272289 -0.739 0.472114 -22.18867014 10.81648 -22.1887 10.81648 Equation: Y=924361.6-5.6861X Y=MOH X=DLH R squared=Goodness of fit=0.037544 This means only 3.75% of data fits with the regression line Correlation coefficient=Square root of R squared=(0.037544^0.5)= 0.193764 There is very ngligible correlation between MOH and DLH Regression on DL$ SUMMARY OUTPUT Regression Statistics Multiple R 0.17708 R Square 0.031357 Adjusted R Square -0.03783 Standard Error 78466.79 Observations 16 ANOVA df SS MS F Significance F Regression 1 2790455798 2.79E+09 0.453214 0.511773449 Residual 14 86198521809 6.16E+09 Total 15 88988977607 Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0% Intercept 823665.3 62890.63928 13.09679 3.02E-09 688778.342 958552.4 688778.3 958552.4 X Variable 1 0.273609 0.406423706 0.673212 0.511773 -0.598082941 1.145301 -0.59808 1.145301 Equation: Y=823665.3+0.273609X Y=MOH X=DL$ R squared=Goodness of fit=0.031357 This means only 3.14% of data fits with the regression line Correlation coefficient=Square root of R squared=(0.031357^0.5)= 0.17708 There is very ngligible correlation between MOH and DL$ Regression on MH SUMMARY OUTPUT Regression Statistics Multiple R 0.318609 R Square 0.101512 Adjusted R Square 0.037334 Standard Error 75571.89 Observations 16 ANOVA df SS MS F Significance F Regression 1 9033434358 9.03E+09 1.58173 0.229085936 Residual 14 79955543249 5.71E+09 Total 15 88988977607 Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0% Intercept 739301.9 100849.6225 7.330736 3.73E-06 523000.9886 955602.8 523001 955602.8 X Variable 1 8.60041 6.838375771 1.257668 0.229086 -6.066447643 23.26727 -6.06645 23.26727 Equation: Y=739301+8.60041X Y=MOH X=MH R squared=Goodness of fit=0.101512 This means only 10.15% of data fits with the regression line Correlation coefficient=Square root of R squared=(0.101512^0.5)= 0.318609 There is some amount of correlation between MOH and MH Regression on DMS SUMMARY OUTPUT Regression Statistics Multiple R 0.316841 R Square 0.100388 Adjusted R Square 0.03613 Standard Error 75619.12 Observations 16 ANOVA df SS MS F Significance F Regression 1 8933452881 8.93E+09 1.56227 0.231824118 Residual 14 80055524726 5.72E+09 Total 15 88988977607 Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0% Intercept 709892.4 124650.4848 5.695063 5.53E-05 442543.6714 977241.1 442543.7 977241.1 X Variable 1 1.123956 0.899231327 1.249908 0.231824 -0.804702967 3.052616 -0.8047 3.052616 Equation: Y=709892+1.123956X Y=MOH X=DMS R squared=Goodness of fit=0.100388 This means only 10.04% of data fits with the regression line Correlation coefficient=Square root of R squared=(0.100388^0.5)= 0.316841 There is some amount of correlation between MOH and DMS 2 Machine hour has better correlation between with MOH Hence, the Manufacturing overhead will be better parameter for charging Manufacturing Overhead under absorbsion costing But, the Correlation is still low. Hence it is recommended that the company use Activity Based Costing system