Reference no: EM132880342

ACC 640 Accounting Theory and Policy Formulation Data Analytics - DePaul University

Divvy Bikes

Background Information:

Divvy is a bicycle sharing system in the City of Chicago and two adjacent suburbs that is operated by Motivate for the City of Chicago Department of Transportation. The system includes roughly 5,800 bicycles spread among 580 stations in an area bounded by 87th St. on the south, Central St. in Evanston on the north, Rainbow Beach Park near South Shore Dr. on the east, and Harlem Ave.in Oak Park on the west.Divvy bikes are available for use 24 hours a day, 7 days a week, 365 days a year, and riders have access to all bikes and all stations across the system

Divvy is a program of the Chicago Department of Transportation (CDOT), which owns the city's bikes, stations and vehicles. Initial funding for the program came from federal grants for projects that promote economic recovery, reduce traffic congestion and improve air quality. Additional funds came from the City's Tax Increment Financing program. On June 28, 2013, Divvy was launched with 750 bikes at 75 stations from the Loop north to Berwyn Ave, west to Kedzie Ave, and south to 59th St.

Contract with City of Chicago:
In April 2019 the City of Chicago entered into a nine-year contract with Lyft (owner of the current Divvy operator, Motivate) to give them (Lyft) the exclusive rights to operate the city-owned system and retain a portion of future advertisement revenue. This arrangement transferred the responsibility of runningthe Divvy Bikes system from the City of Chicago to Lyft. The arrangement requires Lyft/Motivate to collect all receipts from customers and pay an annual fee to the City of Chicago.

The contract between the City of Chicago and Lyft dramatically increases the annual guaranteed revenues payable to the City while reducing the City's financial risk. Lyft is obligated to pay the City an annual amount of $6 million that will increase by four percent annually (the first payment was made June 2019). Further, the City will receive a minimum of $1.5 million annually in guaranteed revenue from advertising and promotions as well as five percent of all revenue from riders in excess of $20 million per year. Over the nine-year life of the contract, the figures above result in a minimum of $77 million in guaranteed revenue to the City that will be available for investment in transportation improvements and programming. The contract also requires Lyft to invest $50 million to add 175 stations and 10,500 bikes to the Divvy system, expand to all 50 City of Chicago wards by 2021, and add electricpedal bikes that could lock to docks at Divvy stations and conventional bike racks. The contract between Lyft and the City of Chicago as well as expansion and modernization plans are for Divvy are posted on D2L with this assignment.

Divvy Pricing:
Divvy has two primary classes of users: Subscribers and Customers. In 2019 and 2020 Subscribers paid an annual fee of $99 for unlimited rides limited to 45 minutes in duration each. Customers paid either $3 for a 30-minute (or shorter) trip or a daily fee of $15 for a 24-hour pass that allowed riders an unlimited number of rides during the 24-hour period. As of January 1, 2021, the rate structure described above was adjusted to an annual fee of $108 for Subscribers and $3.30 for 30-minute rides for Customers.

Prior to the arrival of COVID-19 in 2019, Subscribers accounted for roughly 76% of total rides. As of December 31, 2019, there, were approximately 40,000 Subscribers. Prior to 2020, Subscribers make up approximately 76% of Divvy's total rides.

Assignment Part 1:
You have been hired as a business advisorby Divvy Bikes/Lyft. Your task involves assisting them in determining the steps to take in order to make the required payment to the City of Chicago, Your initial step is to become more familiar with the Divvy system and the presentation of Divvy-related data.Each group will perform a series of descriptive data analytics tasks in this assignment. Data for 2017 - 2019 are available on the City of Chicago data portal. Once on the City of Chicago data portal, type in "Divvy Bikes" in the "search" area. Then click on "Divvy Trips - Dashboard." A more direct route to the Divvy Dashboard.

There are four parts to this assignment: A, B, C and D. The assignment should be submitted via D2L in the Submissions folder labeled Divvy Bikes Data Analytics Case, Part 1 by6:00 p.m. on Monday, February 15th.

Directions:

A. Complete Tables 1, 2 and 3below related to Divvy operations for 2017, 2018 and 2019. The source of information is the Divvy Dashboard on the Chicago Data Portal.How to access the data is explained above.

B. Answer the following questions. Your answers should reflect the knowledge you gained from completing Tables 1, 2 and 3 above as well as additional information provided on the Divvy Trips Dashboard.

1. For the years 2017-2019, describe the usage/frequency of rides for Subscribers. For the same period, describe the usage/frequency of rides for Customers? For example, during what month(s) are a) Subscribersand b) Customers using the bikes the most/the least? Are these patterns consistent during the three-year period?

2. Using the Top 3 2018 and 2019 Subscriber departure stations (see Tables 1 & 2 above); identify what transportation hubs are located at or near those departure points. Given this information, why do you think that the majority of Subscribers typically used Divvy Bikes during 2018 and 2019?

3. Using the Top 3 2018 and 2019 Customer departure stations (see Tables 1 & 2 above) identify what is located at or near those departure points (e.g., Chicago landmarks or attractions). Given this information, why do you think that the majority of Customers typically used Divvy Bikes during 2018 & 2019?

4. In terms of "number of rides," do more Subscribers, or do more Customers use Divvy Bikes?

5. Assuming there were 40,000 Subscribers in 2019, what was the approximate annual Subscriber revenue?

6. Assume that an individualSubscriber took 175 rides in 2019 compared to the 160 rides she took in 2018. Will this increase in the quantity of rides provide additional revenue for Divvy Bikes? All rides were 45 minutes or less in duration.

7. Impact of COVID-19: Using the insight you gained from completing Tables 1-3 and answering questions 1-6 above, answer the following:

a. The change in total passenger rides on Metra from March 2019 to March 2020 decreased by 97%. What do you think happened to the total number ofDivvySubscriber rides in April 2020 compared to April 2019? You do not have to look up these amounts. Remember that Shelter-in-Place took effect Saturday, March 21, 2020. Provide at least two reasons to support your answer.

b. What impact do you predict that COVID-19 had on the number of Divvy Customer rides from April 1, 2020 through June 30, 2020? You do not have to look up these amounts. Provide at least two reasons to support your answer and explain your conclusion.

C. Accessing data for 2020

The City's data portal has not been updated for 2020 data. However, this information is available on the Divvy Bikes website. When you click on the link, you will see the following:

You will need the data for June (202006), August (202008) and October (202010) to complete this part of the assignment. The files you need are highlighted above. When you access data for a particular month, you will see the following column headings:
ride_id rideable_type started_at ended_at start_station_name start_station_id end_station_name start_lat start_lng end_lat end_lng member_casual end_station_id start_lat start_lng end_lat end_lng member_casual

Note that the last column heading is "member casual." "Members" are synonymous with "Subscribers" and "Casual" refers to "Customers."

You are to complete the tables below (Tables 4-8). For information for 2018 and 2019, use the Divvy Bikes Dashboard. For 2020 data, use the Excel files described above. I suggest you use "pivot tables" to manipulate the data in these latter files.

D. Additional Questions

1. What changes did you see in Subscriber(Member) rides/Subscriber(Member) behavior in 2020 compared to the pre-pandemic years examined? What conclusions can you make about the impact of COVID-19 on Subscriber (Member) rides? Identify at least two conclusions.

2. What changes did you see in Customer (Casual)rides/Customer (Casual)behavior in 2020 compared to the pre-pandemic years examined? What conclusions can you make about the impact of COVID-19 on Customer (Casual)rides? Identify at least two conclusions.

Attachment:- Data Analytics Assignment.rar