C5-B Bus and subway ridership in Washington, D.C., during the summer months is b

Question

C5-B

Bus and subway ridership in Washington, D.C., during the summer months is believed to be heavily tied to the number of tourists visiting the city. The number of tourists and the ridership from the past 12 years (in millions) are given in the Excel file.

A) Give the regression equation for forecasting the ridership based on trend (y = ridership, x = year).

Use INTERCEPT and SLOPE formulas to find the regression equations (or use Data Analysis or scatter chart with trend line equation). Place these in cells B21 and B22.

B)Forecast the ridership in year 13 based on trend

Plug in x value of 13 in the equation you found in (a) to get the corresponding y value.

C) Give the regression equation for forecasting the ridership based on number of tourists (y = ridership, x = number of tourists).

Same as (a) except you are using a different x column. Put the results in cell C21 and C22.

D) Forecast the ridership (y) based on the number of tourists (x) assuming in year 13, 10 million tourists will visit the city.  

Plug in x value of 10 in the equation you found in (c) to get the corresponding y value.

E) For years 1 through 12, make forecasts from both methods.

Like in problem A, fill out the columns with forecasts and squared errors. Enter the formulas in the first row, then copy down to year 12 to fill in the entire columns.

For example, here are the starting formulas for columns E and F.

E6: Plug in year 1 in formula in (a), i.e., =$B$21+$B$22*A6

F6: =(C6-E6)^2       

F) Find the MSE’s for both methods and decide which forecast is better.

For each of the two methods, average the Squared Error values to get the MSE. Compare the resulting two MSE’s to determine which method is better.

Year Number of Tourists (Millions) Ridership (Millions) 1 7 1.5 2 2 1 3 6 1.3 4 4 1.5 5 14 2.5 6 15 2.7 7 16 2.4 8 12 2 9 14 2.7 10 20 4.4 11 15 3.4 12 7 1.7

Explanation / Answer

I have used data analysis in excel to find the required results. I will provide the link to download the original excel file on which I have done the regression at the end of this solution.

A) Slope = 0.17 ; Intercept = 1.13

B) Ridership = 1.13+13*0.17 = 3.34

C) Slope = 0.16 ; Intercept = 0.51

D) Ridership = 0.51+13*0.16 = 2.59

E)

F) Mean square error for first case is 0.51 while for 2nd case it is 0.14. This means that the second case more accurately represents the data.

Please download the orginal excel file from the link provided below.

https://www.dropbox.com/s/qubd6xlbivf8n9z/ridership.xlsx?dl=0

Year Number of Tourists (Millions) Ridership (Millions) Forecast(years) Forecast (# of tourists) 1 7 1.5 1.30 0.04 1.62 0.01 2 2 1 1.48 0.23 0.82 0.03 3 6 1.3 1.65 0.12 1.46 0.03 4 4 1.5 1.82 0.10 1.14 0.13 5 14 2.5 2.00 0.25 2.74 0.06 6 15 2.7 2.17 0.28 2.90 0.04 7 16 2.4 2.35 0.00 3.05 0.43 8 12 2 2.52 0.27 2.42 0.17 9 14 2.7 2.69 0.00 2.74 0.00 10 20 4.4 2.87 2.35 3.69 0.50 11 15 3.4 3.04 0.13 2.90 0.25 12 7 1.7 3.21 2.29 1.62 0.01

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

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