MSCI 242M Spreadsheet Modelling for Management
Individual Coursework 2024
Emissions Modelling
With around 1.5 billion cars in the world today, vehicle emissions area highly relevant topic, in terms of climate change, pollution, electric vehicles usage, safety, health, noise and sustainability.
The supplied dataset by the Vehicle Certification Agency (VCA) provides fuel efficiency and emissions data for 53 car manufacturers, for over 40,000 vehicles tested between 2015 to 2023.
Part A – BMW Analysis [20 marks]
1. How many BMW cars have been tested?
2. How many petrol BMW cars have been tested?
3. What is the average MPG value for BMW cars, rounded to 1 decimal place?
4. What is the average MPG value for petrol3 BMW cars, rounded to 1 decimal place?
5. What is the median CO2 value for BMW cars, rounded to the nearest integer?
6. How many BMW cars are missing a numerical MPG value?
7. How many BMW cars have a transmission code which contains a number?
8. What is the highest CO2 value for a BMW car?
9. What is the lowest CO2 value for a BMW car, excluding electricity-only5 fuel types?
10. How loud (in decibels) is the loudest BMW petrol3 car?
Part B – Global Analysis [20 marks]
11. How many total cars have been tested by the VCA in the supplied dataset?
12. How many petrol cars have been tested?
13. How many diesel cars have been tested?
14. How many electricity-only cars have been tested?
15. How many electric hybrid cars have been tested?
16. What is the maximum engine capacity?
17. How many cars do not have a numerical CO2 value?
18. How loud (in decibels) is the loudest car?
19. How loud (in decibels) is the loudest diesel4 car?
20. What is the model of the loudest diesel4 car?
Part C – Analysis by Year [20 marks]
21.Which year saw the fewest cars tested?
22.Which year saw the biggest increase in the number of tests, compared to the previous year?
23.Which year saw the biggest increase in the numbers of tests for electric hybrid6 cars, compared to the previous year?
24.Which year has the highest number of non-numerical MPG1 values?
25.Which manufacturer registers the highest yearly average CO2?
Part D – Analysis by Manufacturer [20 marks]
26.Which manufacturer has the most electric hybrid cars tested?
27. How many manufacturers have no electric hybrid cars tested?
28.Which manufacturer has the loudest cars (dB), on average?
29. Which type of transmission is the loudest, on average, for petrol cars?
30. Ignoring manufacturers that produce solely electric-only cars, which manufacturer8 is missing the most MPG1 values?
Part E – Fleet Modelling [20 marks]
The sheet ‘Fleet Database’ contains data on 100 journeys made by 16 drivers.
31. Calculate the fuel cost for each of the 100 journeys, rounded to the nearest pence, based on the fuel prices given on the ‘Fleet Database’ sheet. Present the total cost of all 100 journeys in the answer cell.
32. What is the total fuel cost for female drivers of petrol cars, rounded to the nearest pence?
33. Of the female drivers with at least 6 journeys, which driver registers the highest distance per
journey? If there are no female drivers with at least 6 journeys, then return ‘no drivers’ in the answer cell.
34. It has been suggested that some of the 12 fleet cars may have an ‘incorrect’ MPG figure, in that it is it different to the MPG value for the same car listed in the VCA dataset (or the average MPG value if there are multiple versions of the car in the VCA dataset). Can you investigate this? Which of the 12 fleet cars (car code), if any, has an incorrect MPG value?
If more than one of the fleet cars is incorrect, then list/spill the car codes down from the answer cell. If none of the 12 fleet cars have an incorrect MPG value, then return ‘all correct’ in the answer cell.
** Single-term students only **
You must also complete the following task.
Part F – Datastack Creation [20 marks]
Include a macro that will create a datastack of the electric hybrid6 vehicles only, for a single manufacturer, as chosen by the user.
The datastack should be constructed on a new sheet, which should not already exist in the submitted workbook (hidden or otherwise).
Ensure that the macro has no bugs, runs without crashing, can be run repeatedly without issues, and runs in a reasonable amount of time. A button has been provided on the user sheet to run the macro.
Model Design
The design of your model will not be assessed directly. All marks are based solely on the accuracy of your results. However,a well-structured and well-organised model will tend to be more efficient in its calculations, more accurate, and less prone to errors.
Data Collection
This coursework must be completed individually.
The dataset you must use is the last 3 digits of your ID number.
For example, if your ID number is 12345678, then you should use the dataset 678.xlsx
All the datasets are different, sousing the incorrect dataset will generate incorrect results.
If you have any issues about this then you must contact the tutor beforesubmission. Nothing can be done post-submission.
All datasets are held on moodle in a folder called ‘Coursework Data’
Submission
Deliverable is an Excel 2021 workbook addressing the tasks, uploaded to moodle.
The model rules areas follows. Failure to comply with these rules will result in a mark penalty
The workbook should open on the User sheet
All answers should be presented in the correct cells on the User sheet
The User sheet should also include your ID number
Only correct results will receive full marks
The workbook will be assessed on a university-specification PC running Excel 2021
All answers must be formula or function-based
they must not be simple/static/pasted values
they must not be generated by simple sorting/filtering methods
they must not be found manually by the user
The workbook should not contain links to external files
The workbook should not have any hidden sheets
The workbook should not have anyun-named sheets
The workbook should not have any circular references
The workbook should not exceed 15MB in size