Question

Freight car loadings over an 18-week period at a busy port are as follows: Week Week Number 480 520 560 540 Week 13 14 Number 390 410 430 410 430 440 Number 630 640 680 700 15 10 16 au AWN 11 580 17 720 12 620 18 740 a. Determine a linear trend line for expected freight car loadings. (Round your intermediate calculations and final answers to 2 decimal places.) Y = OD b. Use the above trend equation to predict expected loadings for Weeks 20 & 21. (Round your final answers to 2 decimal places.) The forecasted demand for Week 20 is and for Week 21 is 7. c. The manager intends to install new equipment when the volume exceeds 970 loadings per week. Assuming the current trend continues, in which week (at the earliest) should the loading volume reach that level? (Use the rounded answers, as required, from any previous part of this problem. Do not round any other intermediate calculations. Round your final answer to 2 decimal places.) It should reach 970 loadings in Week 7.

Answer 1

Solution-A:

Go to data>data analysis >Regression

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.988634
R Square	0.977397
Adjusted R Square	0.975984
Standard Error	18.27378
Observations	18
ANOVA
	df	SS	MS	F	Significance F
Regression	1	231034.9	231034.9	691.8641	1.35E-14
Residual	16	5342.896	333.931
Total	17	236377.8
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	343.6601	8.986354	38.24244	3.71E-17	324.6099	362.7104
week	21.83695	0.830198	26.30331	1.35E-14	20.077	23.59689

From output

y^=343.66+21.84*t

Solution-b:

for t=20

y^=343.6601+21.83695*20

y^= 780.3991

y^=780.40

the forecasted demand for week 20 is 780.40

For week 21

t=21

y^=343.6601+21.83695*21

y^=802.236

y^=802.24

the forecasted demand for week 20 is 802.24

Solution-c

we have

y^=343.66+21.84*t

given y^=970

970=343.66+21.84*t

t=970-343.66/21.84

=28.67857

it should reach 970 in 28.68

ANSWER:

28.68 weeks