Question

You are the manager of a beer distribution center and you want to determine a method for allocating beer delivery costs to the customers. Although one cost clearly relates to travel time within a particular route, another variable cost reflects the time required to unload the cases of beer at the delivery point. You want to develop a model to predict this cost, so you collect a random sample of 20 deliveries within your territory. The unloading time and the number of cases are shown in the Excel data file for this assignment, under the tab Delivery.

e. Should you use the model to predict the delivery time for a customer who is receiving 500 cases of beer? Why or why not?

f. Determine the coefficient of determination, R², and explain its meaning in this problem.   

g. Perform a complete residual analysis. Does the analysis support all the assumptions required for a valid model? Explain.

h. At the 0.05 level of significance, is there evidence of a linear relationship between unloading time and the number of cases of beer?

i. Construct a 95% confidence interval estimate of the mean time to unload 150 cases of beer and a 95% prediction interval of the unloading time for a single delivery of 150 cases of beer. Explain your results.

j. What conclusions can you reach from the analysis above regarding the relationship between unloading time and the number of cases of beer delivered?

k. If your delivery cost is $100 per hour, what variable cost for unloading 150 cases of beer should you add to that customers invoice?   Explain your thought process.

Customer	Number of Cases	Delivery Time
1	52	32.1
2	64	34.8
3	73	36.2
4	85	37.8
5	95	37.8
6	103	39.7
7	116	38.5
8	121	41.9
9	143	44.2
10	157	47.1
11	161	43
12	184	49.4
13	202	57.2
14	218	56.8
15	243	60.6
16	254	61.2
17	267	58.2
18	275	63.1
19	287	65.6
20	298	67.3

Answer 1

Using Excel

data -> data analysis -> regression

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.9858
R Square	0.9718
Adjusted R Square	0.9702
Standard Error	1.9865
Observations	20
ANOVA
	df	SS	MS	F	Significance F
Regression	1	2443.4660	2443.4660	619.1956	0.0000
Residual	18	71.0315	3.9462
Total	19	2514.4975
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	24.8345	1.0542	23.5573	0.0000	22.6197	27.0494
Number of Cases	0.1400	0.0056	24.8836	0.0000	0.1282	0.1518

e. Should you use the model to predict the delivery time for a customer who is receiving 500 cases of beer? Why or why not?

No, as 500 is outside the scope of model

f. Determine the coefficient of determination, R², and explain its meaning in this problem.   

R^2 = 0.9718

it means 97.18 % of variation in delivery time is explained by this regression model

g. Perform a complete residual analysis. Does the analysis support all the assumptions required for a valid model? Explain.

residuals are random and there is no visible pattern,

normal probability plot is linear, hence assumption of normal distribution of residuals are also satisfied

Overall assumptions are satisfied

h. At the 0.05 level of significance, is there evidence of a linear relationship between unloading time and the number of cases of beer?

since p-value = 0.0000 < alpha, , we reject the null hypothesis

there is sufficient evidence of a linear relationship between unloading time and the number of cases of beer

Please give me a thumbs-up if this helps you out. Thank you! :)

By HomeworkLib answering guidelines, we have to answer only first 4 sub-parts in multiple sub-parts

Please post rest questions again as separate post if you want the solution to them too