Forecasting/Projecting Ridership Stats

PCloadletter

New Member
Joined
May 14, 2020
Messages
13
Office Version
  1. 365
  2. 2016
  3. 2010
Platform
  1. Windows
Hi Everyone,

Seeking advice from those with accounting experience. I'm looking for a way to project/forecast transit ridership for 2021. I currently have monthly stats only for January-Dec of 2019 and 2020 :

JANUARYFEBRUARYMARCHAPRILMAYJUNEJULYAUGUSTSEPTEMBEROCTOBERNOVEMBERDECEMBER
2019​
96211251354154613851388153314311722124817591031
2020​
20821536195061545885065098010659981015675

Obviously, with the pandemic has skewed 2020 stats. Is there a good way to project MONTHLY ridership for 2021? When I use the Forecast function, it appears to calculate the yearly TOTAL and takes the average from that.

Thank you in advance.
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,133
Office Version
  1. 2019
Platform
  1. Windows
You've asked a complex question. Before moving forward with conventional statistical analyses, I would recommend trying to understand the causes of some trends seen in your data set. For example:
  1. Why do the first four months of 2019 show a dramatic increase in ridership (more than doubling from January 2019 - April 2019)? Was this due to any changes that might lead to long-term higher ridership, such as an increase in the potential ridership population, an expansion of the transit system, etc. ? If so, then one should expect subsequent months to reflect a newly established, higher "normal" ridership level. Or is the increase in ridership fairly typical because of season-related effects? In other words, are there a substantial number of fair-weather riders who choose not to use the transit system during the winter months, and then in March or April, normal ridership levels return? Either of these may be the case, as the monthly ridership levels through the remainder of 2019 (except for December) seem to vary around the April 2019 level, although variability appears to increase during the winter months.
  2. December 2019 sees a dramatic drop from the November 2019 level. Is this normal? Is this a seasonal effect due to riders choosing not to deal with bad weather or closures initiated by the transit authorities due to reduced demand? Or is the drop due to regular riders taking winter vacations, perhaps for extended holiday celebrations or winter recreational activities?
  3. January 2020 - March 2020 indicate a trend that is opposite that seen during the same months in 2019. During the first three months of 2020, ridership varies around a relatively high level that approximates the greatest levels seen in 2019. What could account for this?
  4. From April 2010 onward, one can surmise that the dramatic drop from the March 2020 level was due to COVID-related restrictions and shutdowns. The lowest levels seem to reflect the most aggressive restrictions in April 2020 - July 2020 before businesses, governments, and medical communities began introducing clearer guidance that might have promoted greater ridership (e.g., businesses being able to open to some less-than-full capacity).
Perhaps April 2019 - March 2020 offers a better picture of otherwise normal ridership in the pre-COVID era, with more variability to be expected during the winter months. And August 2020 -December 2020 should represent new expectations based on arrangements that have been made to deal with pandemic concerns. As more of the population receives COVID vaccinations, and more is learned about the risk of emerging COVID variants, it will be interesting to see if the ridership levels eventually begin to rise again to approach those seen during April 2019 - March 2020. For now, however, it appears that a new, lower baseline has been established.

Answers to these observations might enable you to develop an expected ridership profile. To visualize your tablular data, I created a somewhat confusing plot, with 2019 and 2020 data over/under each other, as well as 2020 shifted out to the right of 2019.

1612890713479.png
 

Watch MrExcel Video

Forum statistics

Threads
1,130,047
Messages
5,639,766
Members
417,109
Latest member
996

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top