MSCI 342 Advanced Spreadsheet Modelling 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 and noise. A supplied datafile by the Vehicle Certification Agency (VCA) provides fuel efficiency and emissions data for over 50 manufacturers, for vehicles tested between 2015 to 2023.
Part A – General Analysis [20 marks]
1. How many total cars have been tested by the VCA in the supplied dataset?
2. How many petrol1 cars have been tested?
3. How many diesel2 cars have been tested?
4. How many electric3 cars have been tested?
5. How many hybrid4 cars have been tested?
6. Which manufacturer5 has the most cars tested?
7. Which manufacturer5 has the most petrol1 cars tested?
8. Which manufacturer5 has the most hybrid4 cars tested?
9. How many manufacturers have had onlypetrol1 cars tested?
10. How many manufacturers have had onlyhybrid4 cars tested?
Part B – Audi Analysis [20 marks]
11. How many Audi cars have been tested?
12. How many diesel2 Audi cars have been tested?
13. What is the average MPG6 value for Audi cars, rounded to 2 decimal places?
14. What is the average MPG6 value for diesel Audi cars, rounded to 2 decimal places? 15. What is the median CO2 value for Audi cars, rounded to the nearest integer?
16. How many Audi cars are missing a numerical MPG value?
17. How many Audi cars are missing a numerical CO2 value?
18. What is the highest CO2 for an individual Audi car?
19. What is model for the individual Audi car with the highest CO2?
20. What is fuel type for the individual Audi car with the lowest CO2?
Part C – Analysis by Year [10 marks]
21. Which year saw the most 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 tests for hybrid4 cars compared to the previous year?
24. Which year has the highest number of non-numerical MPG values?
25. The highest average CO2 for any manufacturer occurs in which year?
Part D – Extremes and Gears [10 marks]
26. How many different types of transmission (code) are there?
27. Which type of transmission is the most numerous in the dataset?
28. How many cars feature a transmission code which does not contain a number?
29. Which type of transmission (code) produces the maximum power, on average, for petrol1 cars? 30. What is the maximum power for an automatic7 transmission car?
Part E – Fleet Costing [20 marks]
The sheet ‘Fleet Car Database’ contains a database of 44 journeys made by a fleet of cars. Based on the fuel economy values6 in the main VCA dataset for each fleet car (manufacturer, model, year and fuel type) estimate the amount of fuel used for each journey, and the subsequent fuel cost.
Note1: If there are multiple versions of a particular model listed in the VCA dataset, then use the average MPG value6 across all the versions of that particular model, year and fuel type.
Note2: Include a facility to add newjourneys to the database, based on a userselection of the following items: a car in the fleet,a driver (code) and a mileage.
Note3: Include a graphical display of the fuel and mileage data for each member of staff
Note4: Ensure all your calculations will update when new journeys are added to the database, up to a maximum of 250 journeys.
31. What is the total fuel cost for the 44 journeys, to the nearest pence, based on the fuel prices given on the ‘Fleet Cars’ sheet?
32. Is there a particular fleet car that is preferred by female drivers? If so, which car (code)?
Model Design [20 marks]
High marks will goto models which follow the design principles of the module, as laid out in the documentation and described during the lectures. The model should be intuitive to use, well-structured, and not be more complicated than it needs to be, particularly in terms of data management.
The model should also, if practical, be dynamic in terms of its calculations – in other words if any changes are made to the data, then the model should be capable of updating the results/calculations in response.
Data Collection
The data itself is held on moodle in a folder called ‘Coursework Data’.
Each student will receive a unique dataset, based on a codenumber, also listed on moodle You must use the correct dataset, as all are different.
Failure to do so will generate incorrect answers, and incura mark penalty.
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 model 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 marks
All answers must be function-based i.e. not simple/static values, not generated by sorting/filtering, and not found manually by the user
The model will be assessed on a university-spec. PC
- The model should not contain external links
- The model should not have any hidden sheets
- The model should not have any circular references
- The model should not exceed 20MB in size