EF4004: Analysis for Business Decision Making Preparatory Task: Take-a-Bike
Overall Scenario
“Take-a-Bike” are a European company whom run city centre bicycle hire schemes. Distinctive low maintenance bikes are available from housing units (e.g. bike stands) in a variety of locations across a city centre.
Users are able to hire these bikes through a mobile phone application. A user is required to download the application, register a number of personal details with the company e.g. Age, gender, payment details and pay a 50 Euro returnable deposit. Once a user is registered, any bike for any location can then be hired for a cost of 1.50 Euros for every block of 30 minutes. This is always rounded up to the nearest 30 minutes. Users are able to lock the bikes but register them as still active for a maximum period of 72 hours. There is no need to return bikes to the housing units as the company also operates a free pick-up service from anywhere within a 30 km radius of any housing unit. Pick-ups are also available outside of this radius but incur a charge of 5 Euros. The company uses GPS technologies to automatically record for each transaction the total hire time, distance a bike is from the nearest housing unit and the number of miles travelled.
You have been asked to conduct some data analysis of behalf of this company. This has been divided into THREE tasks, which utilise the Excel spreadsheet. Once this preparation work has been completed you will be invited to an examination where you will ask to report on the work you have carried out and in some circumstances you will be asked some additional questions.
You will be able to take the preparation work with you into the examination and you are welcome to make any additional notes which you feel may assist you with the subsequent questions asked.
However, this will be limited to no more than 12 pages or 24 sides of A4 paper.
The company has provided majority of the data in an Excel file. The data is spread across several worksheets and there will be an expectation that you will add at least one additional worksheet to this file. If preferred, an individual can create several excel files from the original data sets if they find this an easier way to carry out and save their work.
Please note that in carrying out data analysis, you will produce figures, which will contain many decimal places. For the purposes of the examination please make sure that any figures of this type have a least four decimal places shown on the printout(s).
In creating this assessment scenario, the following websites have been used as inspirational sources for the data sets created: Cycling UK, European Cyclists Federation, the Office of National Statistics and Mobike
Task 1
The figures in the Excel file Take-a-Bike on the worksheet named Task 1 represent the ownership of bicycles per 1000 habitants across three European Countries between 2000 and 2017. The countries featured are United Kingdom, Netherlands and the Republic of Ireland.
1. For each country please convert the figures provided into:
a. Simple index numbers using the year 2010 as the base point.
b. Chain index numbers using the year 2000 as the starting point.
2. Construct two graphs that :
a. Compare the simple index numbers for the three countries.
b. Compare the chain base index numbers for the three countries.
You will need these results to answer the exam questions that will take place at the end of the semester. Therefore, it is vital that you print out the completed work, add any extra notes you may find useful and bring these to the examination with you.
There will be 30 marks allocated to the section in the examination that refers directly to this preparatory work.
Task 2
The figures in the Excel file Take-a-Bike on the worksheet named Task 2 represent a SAMPLE of 175 transactions across three of the cities in which the company operates its bike hire scheme. When a customer registers with the mobile application they are asked to provide their gender and date of birth (date of birth is collected as the mobile application is restricted to users aged 16 or above). Every time a bike is used, hire time (in minutes), and the total distance travelled (in kilometers) are automatically recorded via the company’s GPS.
In preparation for the examination questions linked to Task 2 you are asked to complete the followings:
1. Construct two suitable graphs which illustrate whether the total distance travelled could be being influenced by
a. Age
b. Hire time.
2. Calculate suitable correlation coefficients for the two potential relationships.
3. For the strongest correlation relationship, calculate the values which will be required to represent the relationship as a regression line.
You will need these results to answer the exam questions that will take place at the end of the semester. Therefore, it is vital that you print out the completed work, add any extra notes you may find useful and bring these to the examination with you.
There will be 30 marks allocated to the section in the examination that refers directly to this preparatory work.
Task 3
The figures in the Excel file Take-a-Bike on the worksheet named Task 3 represent a SAMPLE of 175 transactions across three cities in which the company operates its bike hire scheme. When a customer registers with the mobile application they are asked to provide their gender and date of birth (date of birth is collected as the mobile application is restricted to users aged 16 or above). Every time a bike is used, the location, hire time (in minutes), the total distance travelled (in kilometers) and at the end of the transaction the distance the bike is from a housing unit are automatically recorded via the company’s GPS.
Users need to register with the company and pay a 50 Euro returnable deposit. Users are charged 1.50 Euros for each block of 30 minutes, which is always rounded up. The company also operates a free
pick-up service from anywhere within a 30 km radius of any housing unit. However, pick-ups outside of this radius incur a charge of 5 Euros.
In preparation for the examination questions linked to Task 3 you are asked to complete the followings:
1. Create a new column of data, which identifies whether the transaction resulted in a pick-up fee.
2. For each location calculate the percentage of transactions which resulted in a pick-up fee.
3. Create a new column of data, which calculates the revenue for each of the transactions.
4. For each location, calculate the median and interquartile range for revenue generated.
5. By constructing a two-way table or otherwise, calculate the mean and standard deviation for the total distance travelled by location and gender.
6. Using the data relating to the distance to the pick-up point, create four frequency distribution tables and accompanying frequency polygons. The four tables and four graphs will represent:
a. All the locations combined.
b. Amsterdam
c. Dublin
d. Newcastle
Please use the following categories when constructing your tables: 0 to 5 miles, >5 to 10 miles, >10 to 15 miles through to >30 to 35 miles.
You will need these results to answer the exam questions that will take place at the end of the semester. Therefore, it is vital that you print out the completed work, add any extra notes you may find useful and bring these to the examination with you.
There will be 40 marks allocated to the section in the examination that refers directly to this preparatory work.
