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 :
JANUARY
FEBRUARY
MARCH
APRIL
MAY
JUNE
JULY
AUGUST
SEPTEMBER
OCTOBER
NOVEMBER
DECEMBER
2019
962
1125
1354
1546
1385
1388
1533
1431
1722
1248
1759
1031
2020
2082
1536
1950
615
458
850
650
980
1065
998
1015
675
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.
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:
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.
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?
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?
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.
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.