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.

DO NOT USE MINITAB, but you can use EXCEL.

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<Megastst<Correlation/regression<regression

Regression Analysis
	r²	0.972
	r	0.986
	Std. Error	1.987
	n	20
	k	1
	Dep. Var.	Delivery Time
ANOVA table
Source	SS	df	MS	F	p-value
Regression	2,443.4660	1	2,443.4660	619.20	2.15E-15
Residual	71.0315	18	3.9462
Total	2,514.4975	19
Regression output					confidence interval
variables	coefficients	std. error	t (df=18)	p-value	95% lower	95% upper
Intercept	24.8345
Number of Cases	0.1400	0.0056	24.884	2.15E-15	0.1282	0.1518
Predicted values for: Delivery Time
		95% Confidence Interval		95% Prediction Interval
Number of Cases	Predicted	lower	upper	lower	upper	Leverage
150	45.8385	44.8761	46.8009	41.5555	50.1215	0.053

i. 95% confidence interval estimate of the meantime to unload 150 cases of beer: (44.8761,46.8009)

We are 95% confident that the meantime to unload 150 cases of beer lies in  (44.876, 46.801)

95% prediction interval of the unloading time for a single delivery of 150 cases of beer: (41.5555, 50.1215).

We are 95% confident that the unloading time for a single delivery of 150 cases of beer lies in (41.556, 50.122).

j. Since from ANOVA table,

We get the p-value of F test is less than the level of significance 0.05 so there is sufficient evidence to conclude that there is a significant linear relationship between unloading time and the number of cases of beer delivered.

Since R-Sq = 97.2% so 97.2% of the total variation in the sample of unloading time is explained by this regression line.

k.  If your delivery cost is $100 per hour, then the variable cost for unloading 150 cases of beer=45.8385*100=$4583.85