Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

Data Analysis and Business Modelling

BMI602

Data Analysis with Planning

Assessment 2

Background

This is a case study assessment.  For this assessment, you are to assume that you have just joined a local organisation as a “Data Analyst” and have been asked to support the Transport Fleet Manager in a review of current usage and options for the future.

The Transport Fleet Manager manages a fleet of economy cars that are powered either by petrol or diesel engines.  Recently, however, due to concerns associated with environmental impact, the Fleet Manager was asked to buy four (4) electric vehicles.  There is a concern that this might have increased the cost of the fleet.  You have been asked to review the data which has been automatically generated by all vehicles in the fleet, and the new charging stations, in order to assess the resource implications of each type of car.

You may find it useful to review the article GRANOVSKII, M., I. DINCER and M.A. ROSEN, 2006. Economic and environmental comparison of conventional, hybrid, electric and hydrogen fuel cell vehicles. Journal of Power Sources, 159(2), 1186–1193 which is provided on the ‘Assessment 2’ tab of Moodle.

Data

Under the ‘Assessment 2’ tab on Moodle, you will find an EXCEL file.  This provides data for 4 months of fleet car usage.

· The ‘parameter’ tab contains information about each type of car.  The aggregated trips shows the total number of miles that each car is driven per month, and the number of ‘sessions’.  

· ‘Session’ shows when the key is inserted in the car and ends when the key is removed.  This means that if a car is used to make a round trip to a customer, this will be counted as 2 sessions.

· The ‘Charging Data’ tab contains charging information about the charging of the new electrical vehicles.

Be aware that this is real data and as such it may contain errors.  If you spot something that you feel does not look right, be prepared to raise this as part of your answer and explain any presumptions you have therefore made. 

As part of the initiative to introduce 4 electrical cars, the company installed 3 charging points at the following costs: £2,931 for each of the two 7kW charger, £25,743 for a fast charger, £6,869 for civil works, and £16,414 for cable installation.  This total fixed cost of £54,888 also comes with annual back-office support and maintenance costs of £210 per charger per year.

You have been asked to analyse this data and then consider the best possible Fleet for the future.  This assessment will take you through a series of questions, before then analysing the data and then making your recommendations for the future composition of the Fleet.

Your submission for this assessment will be a presentation, supported by an EXCEL file.  Imagine you are presenting your findings to the Fleet Manager, beginning with your analysis, and ending with your justified proposal for the future Fleet Composition.  You are to record your presentation using Panopto and share it with the tutor.

Part 1 – Analysis (100 Marks)

The first part of this assessment therefore is the questions and analysis of data.  Answer the following questions and include the results in an EXCEL file.  You will note the breakdown of marks against the questions.  Where relevant, you may also include some of this analysis in your presentation.

1. Prepare a table showing the number of miles driven by each car for each month and for the 4 months.  Include tables and graphs including interesting additional driving statistics (5+10 marks).

2. Prepare a table showing the electricity consumed (in kWh) to charge each electrical car for each month and for the 4 months together (10 marks).

3. Calculate the utilisation for each type of car.  You will note that some cars are not used in all 4 months.  Show how you take this into consideration in your calculation of the estimate average utilisation (15 marks).

4. Draw a graph showing the probability that an electrical car is charged between midnight and 05:00, 05:00 and 10:00, 10:00 and 15:00, 15:00 and 20:00, and 20:00 and midnight. Do some research and explain why such a graph is important to look at (20 marks).

5. Prepare a table showing the average cost, in £/mile, of using each type of car.  Include all costs, i.e. fixed cost of the lease, fuel costs, electricity costs, cost of charging infrastructure, etc.  You may have to make some assumptions and may have to do some additional research.  Also show the results as a graph (20 marks).

6. Calculate the total emissions for each car, and in total for the fleet, per month.  Show the results as a table and as a graph (10 marks).

7. Summarise the cost and environmental performance of each car (10 marks).

Part 2 – Presentation – Future Fleet (50 Marks)

In preparation for the fleet renewal, it has been decided to invest in 3 more charging points (not rapid), which can be installed without adding new cables.  The priority is to lower carbon emissions and to try to make the fleet greener.  Decreasing total cost is the second priority.  

It can be assumed that the total number of miles driven will be the same (no increase nor decrease in demand).  The lease contract can be changed, i.e. how many miles are specified in the contract.  If more, or less miles are used, the cost of lease will be proportional.

· Prepare a plan and then deliver the presentation showing the best possible fleet for the company.  

· Carefully explain how you are designing the fleet by presenting a clear decision-making approach in your presentation.

· Support each decision with data and analysis from Part 1 – Analysis, and new analysis.

· Conclude your presentation with a table showing why it is better than the existing fleet.  This table should show the total cost in £/year of each fleet (current and planned), the fleet composition (number and type of cars), and total CO2 emissions.

Through your plan and presentation make sure that you address the following questions:

1. What is your planned utilisation?

2. What is the maximum number of electrical cars that you can have?

3. How many cars do you need?

4. How will you compose your fleet (EVs, non EVs)?

5. What is the cost of different fleets?