Managerial Accounting Investigating Cost Drivers Part A Instructions The object of this exercise to gain a better understanding of overhead costs through the use of regression analysis. In particular, you will investigate which service department activities appear to drive or cause departmental overhead. The data you will use is from a proprietary database which contains detailed departmental (i.e. USED, NEW CAR, SERVICE, BODY SHOP) financial information for approximately 250 U.S. car dealerships. The data is in EXCEL format and is called ABC.xls. We will direct our attention to the service department overhead which includes the following costs: office supplies; tools; advertising; policy expense; laundry and uniforms; travel and entertainment; membership dues; legal and auditing; freight and express; telephone; postage; training; bad-debts; data processing; heat/light/power/water; equipment repair and maintenance, plus other miscellaneous departmental expenditures. As you are well aware, because a dealerships annual overhead amounts are highly serially correlated from one year to the next, we will need to conduct our analysis in changes. The spreadsheet contains the following variables: dOH = The change in service department overhead costs. dSALES = The change in service department sales revenue dSUB = The change in departmental revenues that were subcontracted out-of-shop dUCRET = The change in the number of USED CAR retail sales units sold dUCWHOLE = The change in the number of USED CAR wholesaled (auctioned) units sold dIRO = The change in the number of Internal (Dealership) Repair Orders dRRo = The change in the number of Retail (Customer) Repair Orders dEMPL = The change in the number of employees LOSS = 1 if the department profit < 0. If the department is profitable then LOSS=0. Your assignment: A1. Using regression estimate the relationship between overhead expenses as a function of the possible overhead drivers: dOH= 0 + 1*dSALES + 2*dSUB + ..... + 7*dEMPL + 8*LOSS [Check your results: The coefficient on 1 should be 0.04749] Print your results. Explain your answers. Do your results (i.e. the signs and magnitudes) make sense based on your intuition? What are the benefits of subcontracting out some of your work? A2. Examine your results for the UCRET and UCWHOLE. The retail (new and used car) departments require that the service department fix any problems with trade-ins before they can then be sold. The dealership then keeps the more desirable trade-ins and sells them as used cars (UCRET). The less desirable clunkers are sold to wholesalers at auction (UCWHOLE). Does the department spend more overhead fixing the good trade-ins or the clunkers? Is the difference statistically significant? Can you say that they spend more on one or the other? You may need to refer to the attached note on hypothesis testing of coefficients. A3. What can you tell me about the difference between the overhead spent on internal work-orders (IRO) for the dealership (i.e. warranty work, used car trade-ins, promotional vehicles) versus the work orders that are for the paying customers (RRO)? Again is there a statistical difference? Part B Instructions B1. Examining the data, you will find that of the 249 departments, 207 are profitable and 42 are operating at a loss. Split the data into two groups and repeat question A1. above for the two groups separately. IMPORTANT: DO NOT INCLUDE THE VARIABLE LOSS in your regressions for this part as it is the variable that you are using to separate the data. What can you tell me about profitable and loss-making departments? What do your findings suggest? Is there anything that you would like to look at further if you had more data? If you wish you can also repeat questions 2 and, however it is not required. Often times you will need to benchmark your division/firm against others to see if there are financial areas of concern where you are significantly different. It is not unusual to compare profit and loss divisions separately in this way to glean some insights. For Both Parts you are required to turn in: All regression output (but NOT the data, I already have it) and your answers to my questions. Good Luck! Managerial Accounting A Note on Hypothesis Testing Special Case: Testing Equality of Coefficients Rather than testing whether or not a particular variable is a statistically significant determinant (i.e. the p-value is less than 0.05), sometimes we wish to test whether the effects of two independent variables are significantly different from each other. For the following regression: (1) Y = 0 + 1*X1 + 2*X2 + 3*X3 + 4*X4 + ... + e We wish to test whether or not 1 = 2 . You cannot determine the result of this test by simply examining whether or not the individual s are significant. For example, you may find that 1 is significantly different from zero, while 2 is not, thus, you might expect that you would reject the hypothesis that 1 = 2. However, the appropriate F-test may reveal that you can NOT reject equality. NOTE: This is NOT the F-test which you may be aware of, as that tests the model fit as a whole (i.e. whether all the coefficients are jointly zero or 1 = 2 = 3 = i = 0) There is a simple way of testing equality of coefficients within EXCEL-Regression. Simply create a new variable SUMX = X1 + X2. Then run the following regression (note that X1 is now removed): (2) Y = 0 + 1*SUMX + 2*X2 + 3*X3 + 4*X4 + ... + Relating the coefficients between the two equations, you will find: 0 = 0, 1 = 1 and 2 = 1 + 2 The difference between the two effects, X1 and X2 is captured by the coefficient 2. There are two cases: (1) If p-value for 2 <0.05 then the effects of X1 and X2 are significantly different. (1 2) or (2) If 2 is insignificantly different from zero (i.e. the p-value > 0.05) , then you cannot reject the hypothesis that the effects due to X1 and X2 are equal. Thus, you have no evidence is that X1 and X2 have different effects. NOTE: In equation (2) you can run the regression with SUMX and either X1 or X2, but not with all three variables (if you use all three, the regression will be ill formed with no result). Example: Suppose that you had a sample of job salary data and grade-point averages. You could run a regression of starting salaries on MBA Core GPA, and MBA Elective GPA. You may wish to know whether your evidence indicates that recruiters weight the grade performances differently, not whether each one is a determinant of your starting salary. Define: CGPA = Grade point average for Required Core classes EGPA = Grade point average for Elective classes. SAL = Starting salary in $1000s Using the data in the following table, you have the technology (from Stats class) to perform the following regression [similar to equation (1)]: SAL = 0 + 1*CGPA + 2*EGPA SAL ($K) CGPA EGPA SUMGPA 106 3.6 3.9 7.50 109 3.3 3.7 7.00 90 3.2 3.4 6.60 105 3.7 3.3 7.00 92 3.4 3.1 6.50 104 2.9 3.8 6.70 107 3.8 3.5 7.30 120 4.0 3.9 7.90 97 3.3 3.7 7.00 99 3.6 3.1 6.70 98 3.8 2.9 6.70 91 3.7 3.1 6.80 112 4.0 3.4 7.40 Your regression results are: Coefficients Standard Error t Stat P-value Intercept -25.49 24.83 -1.02 0.329 CGPA 16.84 4.58 3.67 0.004 EGPA 19.68 4.44 4.43 0.001 Both CORE and ELECTIVE GPAs are significant determinants of starting salary, with ELECTIVE Classes GPA apparently receiving a greater weighting by recruiters (since 19.68> 16.84). But, Is the difference statistically significant ? Can you say that you have evidence that ELECTIVES are more important to recruiters ? To Test whether 1 = 2 we run the following regression: SAL = 0 + 1*SUMGPA + 2*EGPA Coefficients Standard Error t Stat P-value Intercept -25.49 24.83 -1.02 0.329 SUMGPA 16.84 4.58 3.67 0.004 EGPA 2.84 5.62 0.51 0.624 Check: 0 = 0, 1 = 1 and 2 = 1 + 2 Since 2 is insignificant ( p-value>0.10), the difference between the coefficients for first and second year GPAs is not statistically significant. Thus, we can NOT reject the hypothesis that recruiters weight the GPAs equally. In other words, although it looks like the recruiters weigh electives more than core classes (19.68> 16.84) the difference is not large enough to be conclusive.