BM9400 Preparatory Task

Description

BM9400 Preparatory Task
Academic Year 2018-19
Page 1 of 2
Overall Scenario
“GO RIDE” 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 km travelled.
You will asked to conduct some data analysis of behalf of this company. This has been divided into FIVE
tasks, which utilise the Excel spreadsheet. Once this preparation work has been completed you will be
invited to an examination where you will asked to report on the work you have carried out and in some
circumstances 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 the majority of the data which is available in an Excel file. The data is spread
across several worksheets and there will be an expectation that you will add to this file at least one
additional worksheet. 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 print out(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
BM9400 Preparatory Task
Academic Year 2018-19
Page 2 of 2
Task 1
The figures in the Excel file GO RIDE 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 being United Kingdom, Netherlands and the Republic of Ireland.
1. For each of the three countries please convert the figures provided into
a. Simple index numbers using the year 2010 as the base point.
b. Chain index number using the year 2000 as the starting point.
2. Construct two graphs which
a. Compares the simple index numbers for the three countries
b. Compares the chain base index numbers for the three countries
The results you have produced will be referred to in the examination that takes place at the end of the
semester. Therefore 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.
Please note when preparing your work for printing that there is a maximum limit of 24 sides of A4 for
all tasks.
There will be 10 marks allocated to the section in the examination that refers directly to this
preparatory work.

 

 

 

BM9400 Preparatory Task
Academic Year 2018-19
Page 1 of 2
Overall Scenario
“GO RIDE” 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 km travelled.
You will asked to conduct some data analysis of behalf of this company. This has been divided into FIVE
tasks, which utilise the Excel spreadsheet. Once this preparation work has been completed you will be
invited to an examination where you will asked to report on the work you have carried out and in some
circumstances 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 the majority of the data which is available in an Excel file. The data is spread
across several worksheets and there will be an expectation that you will add to this file at least one
additional worksheet. 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 print out(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
BM9400 Preparatory Task
Academic Year 2018-19
Page 2 of 2
Task 2
The figures in the Excel file GO RIDE on the worksheet named Task 2 represent a SAMPLE of 175
transactions across three of the cities in which the company operate their 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 kilometres) and at the
end of the transaction the distance the bike is from the a housing unit are automatically recorded via
the companies GPS system.
This data set will also be used for the preparation works linked to Task 4. The Task 4 preparation work
will require the original data set to be manipulated (e.g. reordered, extra columns of data to be
created) and therefore for your convenience this is supplied again as a separate worksheet.
In preparation for the examination questions linked to Task 2 you are asked to complete the following:
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 coeffcients for the two potential relationships
3. For the strongest of the two relationships calculate the values which will be required to
represent the relationship as a regression line.
4. The European Cycling Barometer in 2015 showed that the top 8 European countries for cycling
were in order: Denmark; Netherlands; Sweden; Finland; Germany; Belgium; Slovenia and
Hungary
The company have carried out a survey in 2018 amongst their users and asked respondents to
place what they felt were the most popular cycling country. The results, based on percentage
response rate were:
Denmark 13% Germany 3%
Netherlands 57% Belgium 13%
Sweden 6% Slovenia 1%
Finland 5% Hungary 2%
Calculate a suitable correlation coefficient to measure the level of agreement between the two
data sets.
The results you have produced will be referred to in the examination that takes place at the end of the
semester. Therefore 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.
Please note when preparing your work for printing that there is a maximum limit of 24 sides of A4 for
all tasks.
There will be 20 marks allocated to the section in the examination that refers directly to this
preparatory work.

 

 

 

 

BM9400 Preparatory Task
Academic Year 2018-19
Page 1 of 2
Overall Scenario
“GO RIDE” 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 km travelled.
You will asked to conduct some data analysis of behalf of this company. This has been divided into FIVE
tasks, which utilise the Excel spreadsheet. Once this preparation work has been completed you will be
invited to an examination where you will asked to report on the work you have carried out and in some
circumstances 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 the majority of the data which is available in an Excel file. The data is spread
across several worksheets and there will be an expectation that you will add to this file at least one
additional worksheet. 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 print out(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
BM9400 Preparatory Task
Academic Year 2018-19
Page 2 of 2
Task 3
The figures in the Excel file GO RIDE on the worksheet named Task 3 have been downloaded from the
National Office of Statistics. This data shows the quarterly data relating to bike sales in the UK between
2010 and 2017. Due to time lags relating to data collection and publication sales for 2018 are currently
not available
In preparation for the examination questions linked to Task 3 you are asked to complete the following:
1. Construct a suitable graph to display the data.
2. Carry out a time series decomposition on the data for
a. An additive model
b. A multiplicative model
3. Determine which model most appropriately represents the data set
The results you have produced will be referred to in the examination that takes place at the end of the
semester. Therefore 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.
Please note when preparing your work for printing that there is a maximum limit of 24 sides of A4 for
all tasks.
There will be 20 marks allocated to the section in the examination that refers directly to this
preparatory work.

 

 

 

 

 

BM9400 Preparatory Task
Academic Year 2018-19
Page 1 of 2
Overall Scenario
“GO RIDE” 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 km travelled.
You will asked to conduct some data analysis of behalf of this company. This has been divided into FIVE
tasks, which utilise the Excel spreadsheet. Once this preparation work has been completed you will be
invited to an examination where you will asked to report on the work you have carried out and in some
circumstances 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 the majority of the data which is available in an Excel file. The data is spread
across several worksheets and there will be an expectation that you will add to this file at least one
additional worksheet. 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 print out(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
BM9400 Preparatory Task
Academic Year 2018-19
Page 2 of 2
Task 4
The figures in the Excel file GO RIDE on the worksheet named Task 4 represent a SAMPLE of 175
transactions across three of the cities in which the company operate their 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 kilometres) and at the
end of the transaction the distance the bike is from a housing unit are automatically recorded via the
companies GPS system.
This data set was also used in the preparation of materials for Task 2
As part of the preparation for this task, you will also require the information provided in the initial
scenario. In brief this was:
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 pickup
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 4 you are asked to complete the following:
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 km, >5 to 10 km, >10 to 15 km
through to >30 to 35 km.
The results you have produced will be referred to in the examination that takes place at the end of the
semester לויטרה 20 מ”ג מחיר. Therefore 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.
Please note when preparing your work for printing that there is a maximum limit of 24 sides of A4 for
all tasks.
There will be 40 marks allocated to the section in the examination that refers directly to this
preparatory work.

 

 

 

 

BM9400 Preparatory Task
Academic Year 2018-19
Page 1 of 2
Overall Scenario
“GO RIDE” 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 km travelled.
You will asked to conduct some data analysis of behalf of this company. This has been divided into FIVE
tasks, which utilise the Excel spreadsheet. Once this preparation work has been completed you will be
invited to an examination where you will asked to report on the work you have carried out and in some
circumstances 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 the majority of the data which is available in an Excel file. The data is spread
across several worksheets and there will be an expectation that you will add to this file at least one
additional worksheet. 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 print out(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
BM9400 Preparatory Task
Academic Year 2018-19
Page 2 of 2
Task 5
Within Newcastle the GO RIDE management team are considering the best ways in which they should
distribute the bikes across the city centre at the start of each day. There are four main housing units
used across the city: Central Railway Station, Monument Metro Station, the Newcastle Quayside and
Northumbria University’s City Campus East.
Each day they have a total of up to 150 bikes to distribute across these locations. Based on experience,
the average revenue they accumulate from these bikes differs by location. On average each bike ride
which starts from the University site results in an average revenue of £6.80, bikes which start from
central station result in an average revenue of £1.75, Monument £2.30 and the Quayside £1.20. The
company need to supply a minimum of 10 bikes at each of the locations each day. However, at both
the Monument and the Central station the housing unit can hold a maximum of 30 bikes with the
Quayside and University locations housing a maximum of 50 bikes. The company requires two staff to
transport and set-up every 10 bikes at each of the locations, apart from the Quayside. Due to the
location of the housing unit on the Quayside three staff for every 10 bikes is required. There are a total
of 30 staff per day available for transport and set up. The company has a budget of £40 set aside each
day for transport and set-up. Costs per bike being 30p at the Central Station, 20p at Monument, 25p at
the Quayside and 40p at the University.
In preparation for the examination you are asked to use the linear programming technique (known as
Data Solver on Excel) to determine how many bikes should be placed in each of the four locations in
order to maximise revenue. When undertaking this process, please ensure you request a sensitivity
report as part of the solution.
The results and sensitivity report you have produced will be referred to in the examination that takes
place at the end of the semester. Therefore 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.
Please note when preparing your work for printing that there is a maximum limit of 24 sides of A4 for
all tasks.
There will be 10 marks allocated to the section in the examination that refers directly to this
preparatory work.