arlow Company is an online discount retailer. The CEO wants to better understand
ID: 3303239 • Letter: A
Question
arlow Company is an online discount retailer. The CEO wants to better understand their distribution costs, and has asked his accounting team to look into it. Discussion with the workers in the distribution department have determined that distribution expense are operationally associated with:
Number of orders
The number of parcels in an order
Number of large items shipped
Data for the past 24 months as compiled by the accounting department:
Using the data regression methodology from Chapter 3, determine:
What is the best dependent variable to use as a predictor? (Run 3 single variable regressions)
What variables (if any) have little to no effect on distribution cost? (Analyze single variable regression output)
What is the best single variable equation?
What is the best multiple variable equation? (Run Multiple regression analyses)
Using your equation from 3 and 4, If Harlow expects to have 95,000 orders, with 150,000 parcels, and 9,500 large items, what is distribution cost?
What other factors do you think might play a part in distribution cost that are not outlined?
Month Distribution Cost Number of Orders Number of parcels in an order Number of Large items 1 $ 45,000 11,200 38,000 1,120 2 $ 58,000 14,000 45,000 1,400 3 $ 155,000 40,000 55,000 1,000 4 $ 450,000 110,000 50,000 850 5 $ 90,000 20,000 38,600 4,000 6 $ 126,000 33,100 68,000 5,500 7 $ 90,600 21,000 50,000 1,800 8 $ 54,000 12,800 30,000 2,000 9 $ 175,000 43,860 85,000 1,500 10 $ 287,000 50,000 75,000 2,500 11 $ 350,000 92,700 85,000 5,000 12 $ 425,000 85,000 60,000 14,000 13 $ 110,000 28,220 35,000 1,500 14 $ 95,000 21,200 50,000 1,100 15 $ 160,000 38,560 70,000 1,500 16 $ 85,000 19,630 35,000 1,340 17 $ 135,000 35,800 40,000 3,000 18 $ 75,000 18,900 25,000 2,000 19 $ 125,000 33,070 45,000 1,900 20 $ 175,000 43,420 70,000 1,430 21 $ 150,000 35,720 66,800 1,750 22 $ 138,000 35,300 62,500 2,200 23 $ 260,000 68,000 85,000 9,000 24 $ 325,000 87,750 63,000 15,250Explanation / Answer
1) Best would be number of orders as the p-value is low and R^2 is high which means higher proportion of variation in dependent variable is explained by indepedent variable.
2) Looking at the single variable equation, we can see that No of large items has the lowest r squared amongst all the single variable equations. Also, it has the highest p-value in the multiple regression equation. Hence, that variable has the least effect amongst the three on distribution cost.
3) Best single variable equation is No of orders because that has the lowest p-value and also highest r squared.
4) Best multiple variable equation amongst the three above would be No of orders and No of parcels in an order because it has the highest r squared amongst all the three.
5)
equation is -4400.7+3.74215(No of orders)+0.3348(No of parcels in an order)-0.25553(No of large items)
-4400.7+3.74215(95000)+0.3348(150000)-0.25553(9500)=-4400.7+355504.25+50200-2427.535=398876.015
6) Other factors that might affect the distribution cost could be distance to destination and distribution channel and also value of goods (higher value goods might have to be shipped through a premium distributor)
P.S:- I am not able to post the excel output (As answer can only be 65000 characters long ) for all of them but posting for all the three variables (multiple regression) and individual variables below. Rest should be combination of two independent variables (so other than this would be 3). The same can be calculated from data analysis tab in excel.
a) All three as independent variables:-
b) No of orders as independent variable:-
c) No of parcels in an order as independent variable:-
Regression Statistics Multiple R 0.974808 R Square 0.95025 Adjusted R Square 0.942788 Standard Error 27692.13 Observations 24 Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0% Intercept -4400.7 13417.83 -0.32797 0.74634 -32389.8 23588.41 -32389.8 23588.41 No of orders 3.74215 0.621881 6.017474 0.00 2.44493 5.03937 2.44493 5.03937 No of parcels in an order 0.3348 0.488282 0.68567 0.500793 -0.68374 1.353338 -0.68374 1.353338 No of large items -0.25553 1.796565 -0.14223 0.888319 -4.0031 3.492038 -4.0031 3.492038b) No of orders as independent variable:-
SUMMARY OUTPUT Regression Statistics Multiple R 0.974199 R Square 0.949064 Adjusted R Square 0.946749 Standard Error 26716.4 Observations 24 Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0% Intercept 1434.809 10053.88 0.142712 0.887817 -19415.7 22285.28 -19415.7 22285.28 No of orders 4.107327 0.202868 20.24633 0.00 3.686605 4.528049 3.686605 4.528049c) No of parcels in an order as independent variable:-
SUMMARY OUTPUT Regression Statistics Multiple R 0.925496 R Square 0.856543 Adjusted R Square 0.850022 Standard Error 44835.88 Observations 24 Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0% Intercept -32845.7 20114.41 -1.63294 0.12 -74560.4 8869.08 -74560.4 8869.08 No of parcels in an order 3.128386 0.272957 11.46108 0.00 2.562307 3.694466 2.562307 3.694466 d) No of large items as independent variable:-Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.