Management of a soft-drink bottling company wants to develop a method for alloca

Question

Management of a soft-drink bottling company wants to develop a method for allocating delivery costs to customers. Although one cost clearly relates to travel time within a particular route, another cost variable reflects the time required to unload the cases of soft drink at the delivery point. A sample of 20 deliveries within a territory was selected. The delivery times (in minutes) and the number of cases delivered were recorded in the file WK3_X2.xls (see attached). An analyst computes the delivery time per case delivered and averages these to get 0.33 minutes. Using this he gives an estimated delivery time of 50 minutes for 150 cases to be delivered. Is this a reasonable delivery time? Explain why or why not. Looking at the data in the file, can you give a better estimate? Make sure that you justify your estimate.

Customer Number of cases Delivery Time 52 64 73 85 95 103 116 121 143 157 161 184. 202 218 243 254 267 275 287 298 32.1 34.8 36.2 37.8 37.8 39.7 38.5 41.9 44.2 47.1 43 49.4 57.2 56.8 60.6 61.2 58.2 63.1 65.6 67.3 10 12 13 15 16 17 18 19 20

Explanation / Answer

Use Excel to model simple linear regression with delivery tiime (Y) as dependent variable and Number of cases (X) as independent variable.

You would get the following output -

The estimated time of 50 minutes for 150 number of cases is not reasonable because it has been calculated using the overall mean. As the delivery time increases with increase in number of cases, so using the overall mean will not give a good estimate of the delivery time.

From the regression output we get:

Y = 24.83 + 0.14(X)

So, estimated delivery time for X = 150 is -

Y = 24.83 + 0.14(150)

= 45.8385

So, estimated delivey time would be approximately 45.84 minutes.

SUMMARY OUTPUT Regression Statistics Multiple R 0.985774425 R Square 0.971751217 Adjusted R Square 0.97018184 Standard Error 1.986502982 Observations 20 ANOVA df SS MS F Significance F Regression 1 2443.466006 2443.466006 619.1955958 2.15212E-15 Residual 18 71.03149378 3.946194099 Total 19 2514.4975 Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Intercept 24.83453095 1.054218648 23.55728671 5.60965E-15 22.61969976 27.04936214 Number of Cases 0.140026304 0.005627243 24.88364113 2.15212E-15 0.128203904 0.151848704

Related Questions

Navigate

• Browse (All)
• Browse M
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.