Total Excel work
Regression Assignment
ACC 843
Question 1
Kemps Dairy Company produces and distributes dairy products such as milk, yogurt, sour cream, ice cream, and cheese. It is caught between two opposing pressures: There is pressure to reduce selling prices, but costs are creeping upwards. It is facing a market where customers have monopsony power. Thus, Kemps has much less power relative to its customers such as Wal-Mart, Target, etc. These customers want more services such as specialized packaging and distribution services, inventory management such as warehousing and just-in-time delivery, and small runs of special-recipe products. Kemps used just to try to meet all customer demands. However, it now believes that it can’t be “all things to all people. Therefore, it needs an accurate handle on costs by product, brand, & customer.
Managers and accountants at Kemps LLC worked out Time Driven Activity Based Costing (TDABC) cost equations to estimate the costs of orders. They want to make sure that these equations do not become outdated as processes are modified, personnel turns over, and so on. One way of checking the validity of the equations is to observe the production and warehousing processes at regular intervals and measure current times with a stopwatch. This is a costly way of checking, however. It may disrupt work and not provide time measures that are representative of normal, unobserved activity times.
If accurate data is available on total activity time per day and the characteristics of orders processed each day, then a less obtrusive way of checking is to estimate regression models. Although the firm does not record minutes spent on sub-activities like changing labels in the production plant, it does attempt to record total actual minutes spent in activities like production of specific products.
For sour cream production, Kemps has worked out the following time equation to estimate the production run time:
Run time (in minutes) = {shipping quantity/200 units} if size = 24 ounces OR
{shipment quantity/255 units} if size = 12 ounces
+ {10 minutes} if size change occurs
+ {5 minutes} if label change occurs
This equation can be expressed as the following regression model:
y = (1/200)*x1 + (1/255)*x2 + 10*x3 + 5*x4,
where y = minutes of sour-cream run time per day, x1 = number of units of 24-ounce sour cream produced and shipped that day, x2 = number of units of 12-ounce sour cream produced and shipped that day, x3 = number of size changes required in that day’s production and x4 = number of label changes required in that day’s production.
The data file on D2L provides data on total minutes of run time for sour cream orders and characteristics of the orders processed that day, for each of 30 recent days. Estimate a regression model using these data, and attach your output.
a) Does your regression analysis suggest that the time equation for sour cream production needs updating? Explain.
b) The regression model above does not explicitly include an intercept, and the model you estimate probably will not have an intercept that is significantly different from zero. (This doesn’t mean you should force the intercept to be zero when you run the regression!) If you did have a positive intercept mean in this regression, what would it represent?
The following information applies to questions 2 – 4.
Premier Lodge is a hotel chain that operates properties in small to mid-size American cities. Management is concerned about the variation in profits across individual hotel properties and is trying to determine what factors cause profits to be so different at hotels that are in many ways similar. Some potential profit drivers that have been put forward are:
· Size of property (the hotels were built to two different designs one for small cities and one with more rooms for larger cities)
· Expenditure on local advertising (there is some national brand advertising managed by corporate headquarters, but local managers choose the amount and type of local advertising to do
· The rating the manager received at his or her last performance evaluation
· Rating of the physical attractiveness of the property: building, gardens, immediate surroundings (e.g., surrounded by noisy freeways versus pleasant walking areas)
· Neighboring amenities: rating of the quantity and quality of restaurants, shopping, art venues, etc. near the property
The data file on D2L provides information these factors from 60 of Premier Lodge’s hotels. Performance ratings of managers are made by Premier Lodge’s upper-level management based on a variety of factors besides current profit at the manager’s property. Ratings of physical attractiveness of the property and neighboring amenities are made by a consulting firm that specializes in this area. For purposes of this exercise, assume that these ratings are reasonably accurate though not perfect.
Use regression analysis to answer the following questions (attach relevant output):
1. How important is property attractiveness as a profit driver? Does this answer vary depending on which of the following regressions you looked at?
a) Dependent variable is profit, independent variable is attractiveness only
b) Dependent variable is profit, independent variables are attractiveness, size, managerial performance, advertising expenditure, and neighboring amenities.
Run both of these regressions and explain: which of the two regression models gives you more credible information about the pure effect of attractiveness, unconfounded with the effects of other factors? Why does the coefficient on the attractiveness variable differ in models (a) and (b)?
2. Does local advertising expenditure show diminishing returns, within the range of expenditures that are actually observed at these properties?
3. Some people at Premier headquarters believe that it is important to put more talented managers at the bigger properties, because they have more scope there and can make a bigger difference to company profits. Assume that the manager performance ratings are reasonable estimates of manager talent. Does regression analysis support the belief that talent has a different impact on profits at small versus large properties? Estimate the relationship between managerial talent and profitability using two different regression models. Which model is more powerful and why?
4. Managerial talent could have a non-linear effect on profits. That is, when managerial talent increases, profit increases, but at an increasing or at a diminishing rate. Using regression analysis, estimate whether managerial talent (measured by performance ratings) has a nonlinear effect on profit. Explain your findings.
Note the following
· Sometimes we test for effects of factors that turn out to be quite insignificant. Don’t let these uninformative factors pile up in your models. If they’re clearly irrelevant, take them out when you do subsequent analyses. If a factor is significantly related to profits, keep it in the model when you do subsequent analyses
· You might see factors with marginal significance (p > 0.05 but not far from it). Although these don’t meet the conventional p < .05 cutoff, we shouldn’t be quite as comfortable concluding that they are unrelated to profits as we would be if p = .67, for example. It’s legitimate in these cases to say that the factor has marginal significance, i.e., we have some uncertainty about it. (It’s best to leave these in the model.)
3