Reference no: EM132912103

ACC 640 Accounting Theory and Policy Formulation

Data Analytics Assignment Part 1:

Assignment Part 1:

You have been hired as a business advisor by 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 at https://data.cityofchicago.org/. 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?

For Subscribers, the maximum trips happen in mind of the year Aug in 2017, 2018 and 2019 while least trips happen in start of the year Jan in 2017, Feb in 2018, 2019

For Customers, the maximum trips happen in mind of the year July in 2017, 2018 and Aug in 2019 while least trips happen in start of the year Jan in 2017, Feb in 2018, 2019

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?

The top three departure points are located near train station. Hence the subscribers use the Divvy Bikes to travel to stations and from the station to different locations

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 ofCustomers typically used Divvy Bikes during 2018 & 2019?

The top three departure points are located near tourist attractions.
- Streeter Dr & Grand Ave - Navy Pier
- Lake Shore Dr & Monroe St - multilevel expressway that runs alongside the shoreline of Lake Michigan
- Millennium Park - Bean, world's largest rooftop garden

Hence the customers use the Divvy Bikes to travel to these tourist attractions

4. In terms of "number of rides," do more Subscribers, or do more Customers use Divvy Bikes?
Subscribers have done a greater number of riders than customers. In 2019, total riders by subscriber = 2.9M while total riders by customers = 881K

5. Assuming there were 40,000 Subscribers in 2019, what was the approximate annual Subscriber revenue?
Amount charged from each subscriber = $99
Total Revenue = 40000*99 = $396000

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.
The amount charged to Subscriber is a fixed charge for 45 min duration riders for the entire year irrespective of the number of riders done by the Subscriber in the year. Since all riders mentioned are less than 45 mins, same revenue will be earned.

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.

The number of Divvy subscribers would have decreased in April 2020 compared to April 2019. The reason for decrease would be
1. With Covid-19 setting in, lot of subscribers started to stay at home and not travel to office or other workplaces regularly
2. Most Subscribers used to travel to train station using bikes. But with Covid-19 in place they would have avoided using public transport to travel. Leading to lower rides

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.

The number of customers rides would have decreased April 2020 to June 2020. The reason for decrease would be
1. Most of the customers were tourists and with Covid-19 the tourists flow into the country reduced.
2. A lot of tourist attraction places were closed in April 2020. Hence even local people couldn't visit these places, leading to lower rides

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.
The subscribers have decreased significantly in 2020 compared to pre covid years. This can be due to significant increase in annual fee of the subscribers. Also, the top start locations have changed in 2020

a. The subscriber has decreased significantly in 2020 compared to pre covid years. The reason would be less frequent travel to work and school.

b. The top 5 start stations have also changed for subscribers. Most of the subscribers used to visit train stations in pre-pandemic years. Now the top stations for subscribers are tourist attractions or stores

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.

The customers have increased significantly in 2020 compared to pre covid years. This can be due to significant increase in annual fee of the subscribers. Also, the top start locations have remained the same as pre covid

a. The customer has increased significantly in 2020 compared to pre covid years. The reason would be less frequent travel to work and school, people would have opted for casual rides than member rides

b. The start locations for the customers is near tourist locations. Hence, customers are visiting these places for casual rides even post covid

Data Analytics Assignment Part 2:

Recall from Part 1 of this assignment that your group was hired as business advisors(consultants) to Divvy Bikes /Motivate/Lyft (DML). Part 1 was designed to familiarize you with the Divvy Bikes system and the presentation of Divvy-related data. Remember from Part 1 that your group was initially hired by DML to propose and outline plans to increase its operating revenue and cash flow as they continue their recovery from the financial effects of COVID-19. Also recall from Part 1 that the contract DML entered into with the City of Chicago required your client to pay the City $6,000,000 on June 30, 2019 with future payments of $6,489,600 due on June 30, 2021 and $6,749,184 due on June 30, 2022. These payments are in addition to an annual minimum guarantee of $1,500,000 due to the City from advertising and promotions related revenue.

In completing Part 1 of this assignment, you learned that the ratio of Subscriber (Member) rides to Customer (Casual) rides changed noticeably from 2018 and 2019 to 2020. In addition, the most frequently used stations in 2018 and 2019 were not always the same in 2020, particularly for Subscribers (Members).

Given the information you gained from completing Part 1 of that assignment, your group is to prepare and post a video presentation to the Board of Directors (BOD) for DML. Your presentation should include the following:

» To the best of your ability, explain to the BOD the effects that COVID-19 had on ridership by both Subscribers (Members) and Customer (Casual) riders in 2020. Feel free to use graphics or any other visual aids to assist in your explanation. Then illustrate the determination of operating revenue (by month) for 2020. Information related to monthly subscription sales and customer rides is available in the Excel spreadsheet that is posted with this assignment. Keep in mind that prior to January 1, 2021, Subscribers (Members) paid $99 for an annual membership and Customer (Casual) riders paid $3.00 for a ride up to 30 minutes in duration.

» Prepare a forecast of operating revenue (by month) for 2021. Information regarding monthly projected subscription sales and monthly projected Customer (Casual) rides (actual data for January 2021 is provided) can also be found in the Excel spreadsheet that is posted with this assignment. Remember that as of January 1, 2021, Subscribers (Members) will be charged $108 for an annual membership and Customer (Casual) riders will pay $3.30 for a ride that is up to 30minutes in duration.

» Prepare two proposals for enhancing operating revenue in 2021. One of the proposals should focus on Subscribers (Members) and the other on Customer (Casual) riders. Alternatively, either or both of your proposals may address both Subscribers (Members)and Customer (Casual) riders. With each proposal be sure to identify, explain and justify any assumptions that you make. Also, keep in mind that a proposal that addresses Subscribers (Members) may also affect Customer (Casual) riders in some way. In other words, if a proposal would increase the number of Subscribers (Members) by 10%, how would Customer (Casual) riders be impacted? For each proposal, indicate how the 2021 operating revenue that is forecasted above (see bullet 2) would be impacted. Finally, illustrate the effect of both proposals on the forecasted revenue for 2021. Your proposals should focus only on the "traditional" Divvy Bikes. Do not include any operating revenue projections related to the E-Bikes.

Each of your proposals should reflect your knowledge of the Divvy Bikes system data including trends that were observed from completing Part 1 of this assignment. This includes incorporating specific information about Divvy's operations and behavior of the two "rider groups." Remember from above that all assumptions made in both proposals must be identified, explained, and justified. Finally, both proposals should focus on enhancing operating revenue and cash flow. In other words, neither proposal should require Divvy to spend additional funds to implement your individual proposals.

Please do not waste the time of DML's BOD with obvious or overly simplistic proposals. DML is compensating you well for your collective analytical expertise to analyze data and develop and propose meaningful strategies for DML to "weather the storm" associated with COVID-19. To be more clear, do not propose "increasethe Subscriber (Member) fee" or "increase the Customer (Casual) single ride charge." These changes have already been implemented in 2021.

Attachment:- Accounting Theory and Policy Formulation.rar