Question

Use Excel

The United States Department of Agriculture (USDA), in conjunction with the Forest Service, publishes information to assist companies in estimating the cost of building a temporary road for such activities as a timber sale. Such roads are generally built for one or two seasons of use for limited traffic and are designed with the goal of reestablishing vegetative cover on the roadway and adjacent disturbed area within ten years after the termination of the contract, permit, or lease. The timber sale contract requires out sloping, removal of culverts and ditches, and building water bars or cross ditches after the road is no longer needed. As part of this estimation process, the company needs to estimate haul costs. The USDA publishes variable costs in dollars per cubic-yard-mile of hauling dirt according to the speed with which the vehicle can drive. Speeds are mainly determined by the road width, the sight distance, the grade, the curves and the turnouts. Thus, on a steep, narrow, winding road, the speed is slow; and on a flat, straight, wide road, the speed is faster. Shown below are data on speed, cost per cubic yard for a 12 cubic yard end-dump vehicle, and cost per cubic yard for a 20 cubic yard bottom-dump vehicle. Use these data and simple regression analysis to develop models for predicting the haul cost by speed for each of these two vehicles. Discuss the strength of the models. Based on the models, predict the haul cost for 35 mph and for 45 mph for each of these vehicles.

SPEED (MPH)	HAUL COST 12-CUBIC-YARD END-DUMP VEHICLE $ PER CUBIC YD.	HAUL COST 20-CUBIC-YARD BOTTOM-DUMP VEHICLE $ PER CUBIC YD.
10	$2.46	$1.98
15	$1.64	$1.31
20	$1.24	$0.98
25	$0.98	$0.77
30	$0.82	$0.65
40	$0.62	$0.47
50	$0.48	$0.40

Answer 1

For HAUL COST 12-CUBIC-YARD END-DUMP VEHICLE $ PER CUBIC YD.:

The simple regression model is:

y = 2.3549 - 0.0434*x

For speed = 35, y = 0.83623

For speed = 45, y = 0.40234

The output is:

	r²	0.794
	r	-0.891
	Std. Error	0.341
	n	7
	k	1
	Dep. Var.	HAUL COST 12-CUBIC-YARD END-DUMP VEHICLE PER CUBIC YD.
ANOVA table
Source	SS	df	MS	F	p-value
Regression	2.2457	1	2.2457	19.32	.0070
Residual	0.5810	5	0.1162
Total	2.8267	6
Regression output				confidence interval
variables	coefficients	std. error	t (df=5)	p-value	95% lower	95% upper
Intercept	2.3549
SPEED (MPH)	-0.0434	0.0099	-4.396	.0070	-0.0688	-0.0180
Predicted values for: HAUL COST 12-CUBIC-YARD END-DUMP VEHICLE PER CUBIC YD.
		95% Confidence Intervals	95% Prediction Intervals
SPEED (MPH)	Predicted	lower	upper	lower	upper	Leverage
35	0.83623	0.44965	1.22280	-0.12155	1.79400	0.195
45	0.40234	-0.15889	0.96356	-0.63827	1.44294	0.410

For HAUL COST 20-CUBIC-YARD BOTTOM-DUMP VEHICLE $ PER CUBIC YD.:

The simple regression model is:

y = 1.8805 - 0.0348*x

For speed = 35, y = 0.66407

For speed = 45, y = 0.31653

The output is:

	r²	0.781
	r	-0.884
	Std. Error	0.285
	n	7
	k	1
	Dep. Var.	HAUL COST 20-CUBIC-YARD BOTTOM-DUMP VEHICLE PER CUBIC YD.
ANOVA table
Source	SS	df	MS	F	p-value
Regression	1.4408	1	1.4408	17.80	.0083
Residual	0.4047	5	0.0809
Total	1.8455	6
Regression output				confidence interval
variables	coefficients	std. error	t (df=5)	p-value	95% lower	95% upper
Intercept	1.8805
SPEED (MPH)	-0.0348	0.0082	-4.219	.0083	-0.0559	-0.0136
Predicted values for: HAUL COST 20-CUBIC-YARD BOTTOM-DUMP VEHICLE PER CUBIC YD.
		95% Confidence Intervals	95% Prediction Intervals
SPEED (MPH)	Predicted	lower	upper	lower	upper	Leverage
35	0.66407	0.34144	0.98670	-0.13528	1.46342	0.195
45	0.31653	-0.15187	0.78492	-0.55196	1.18501	0.410

HAUL COST 12-CUBIC-YARD END-DUMP VEHICLE $ PER CUBIC YD. is better because it is explaining more variation in the data.