Hello, if you have any need, please feel free to consult us, this is my wechat: wx91due
MSCI 242L Spreadsheet Modelling for Management
Individual Coursework 2026: Emissions Modelling
With around 1.5 billion cars in the world today, vehicle emissions are a highly relevant topic, in terms of climate change, pollution, electric vehicles (EV) usage, safety, health, noise and sustainability.
The supplied dataset by the Vehicle Certification Agency (VCA) provides fuel efficiency and emissions data for 56 car manufacturers, for over 45,000 vehicles tested between 2015 to 2024.
Each row in the dataset is a test performed by the VCA on a car.
All answers must be formula-based.
Part A – Volvo Car Analysis [20 marks]
1. How many VCA tests have been carried out on Volvo cars?
2. How many petrol
1Volvo cars have been tested?
3. What is the average MPG2 value for Volvo cars, rounded3 to 2 decimal places?
4. What is the median CO2 value for Volvo cars, rounded3 to the nearest integer?
5. What is the average MPG2 value for electric-hybrid6 Volvo cars, rounded3 to 2 decimal places?
6. How many Volvo cars are missing a numerical MPG2 value? [note: zero is a number]
7. How many Volvo cars have a transmission code which contains a 5 or a 6?
8. What is the highest CO2 value for a Volvo car?
9. What is the lowest CO2 value for a Volvo car, ignoring all cars with a CO2 value of zero?
10. How loud (in decibels) is the loudest Volvo electricity-only4 car?
Part B – Complete the Summary Table by Manufacturer, with these results (columns) [20 marks]
11. For each Manufacturer, present the total number of cars tested.
12. For each Manufacturer, present the total number of diesel
5 cars tested.
13. For each Manufacturer, present the total number of electric-hybrid6 cars tested.
14. For each Manufacturer, present the total number of diesel-hybrid7 cars tested.
15. For each Manufacturer, present average diesel MPG2 value, rounded3 to 2 decimal places.
16. For each Manufacturer, present average diesel-hybrid7 MPG2 value, rounded3 to 2 decimal places.
17. For each Manufacturer, how many cars do not have a numerical MPG2 value? [note: zero is a number]
18. For each Manufacturer, what is the maximum CO2 value?
19. For each Manufacturer, which year saw the most cars tested8?
20. For each Manufacturer, what is the model of the loudest diesel-hybrid7 car?
If any Manufacturer has no diesel-hybrid7 cars listed, then return the text “n/a” in the cell.
Part C – Analysis of Diesel and Diesel-Hybrids [20 marks]
21. Which manufacturer9 has the most cars tested?
22. How many manufacturers have at least one diesel-hybrid7 car?
23. How many manufacturers are missing at least one MPG2 value and at least one CO2 value?
24. Which type of transmission is the quietest, on average, for diesel5 cars?
25. Which Manufacturer9 has the quietest cars, on average?
1 Petrol only fuel.
2 Use the ‘Imperial Combined’ MPG value.
3 Round the value using an Excel function. Do not simply format the cell to appear rounded as this will be awarded reduced marks.
4 Electricity-only cars. Do not include any hybrids.
5 Diesel only fuel.
6 An electric-hybrid car is ‘Petrol Electric’ or ‘Electricity / Petrol’ or ‘Petrol Hybrid’ or ‘Diesel Electric’ or ‘Electricity / Diesel’ fuel.
7 A diesel-hybrid is ‘Diesel Electric’ or ‘Electricity / Diesel’ fuel.
8 If two or more years share this value, then present the first time it occurred
9 If two or more manufacturers share this position, then present the first one alphabetically
Part D – Analysis by Year [20 marks]
26. Which year8 saw the most cars tested?
27. Which year8 saw the fewest diesel-hybrids7 tested?
28. Which year8 saw the highest number of manufacturers where all their cars tested were electricity-only4?
29. The following chart shows the number of electric-hybrids6 each year.
30. In which year8 does the highest yearly average CO2 value for any Manufacturer occur?
Part E – Fleet Car Modelling [20 marks]
The sheet ‘Fleet Database’ contains data for 90 journeys made by 14 drivers, using 12 fleet cars.
All answers must be formula-based.
31. Calculate the fuel cost for each journey, rounded3 to the nearest pence, based on the fuel prices given on the ‘Fleet Database’ sheet. Sum across all journeys and present the total cost in the answer cell.
32. Of the drivers with at least 5 journeys, which driver registers the highest cost for a journey?
Present the driver code.
If there is a tie for the highest cost, then the formula should return the text ‘tie’ in the answer cell.
33. Which of the fleet cars was the most popular, in terms of total distance driven?
Present the car code.
If there is a tie for the most popular, then the formula should return the text ‘tie’ in the answer cell.
34. Of the drivers with at least 3 journeys, how many drivers used a different fleet car for every journey?
35. Which drivers used the same fleet car for every journey they made?
If no drivers did, the formula should return the text ‘no drivers’ in the answer cell.
If only one driver did, the formula should return their driver code in the answer cell, e.g. M0044
If multiple drivers did, the formula should return all driver codes in the answer cell, each separated by a forward slash /
for example: M0044/F0013
Model Design
The design of your model will not be assessed directly. All marks are based solely on the correctness 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, and penalties may be applied for major design errors (see below).
Data Collection
This coursework is to 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, so using the incorrect dataset will generate incorrect results.
Do not alter the data in any way, unless directed to do so by the tutor.
If you have any issues about this then you must contact the tutor before submission.
All datasets are held on moodle in a folder called Coursework Data
Submission
Deliverable is an Excel 2024 workbook addressing the tasks, uploaded to moodle.
The model rules are as follows:
✓ All answers must be presented in the correct cells on the User sheet
✓ The User sheet should include your ID number
✓ You may add calculations, tables or sheets to the workbook
✓ Incorrect results will receive zero marks
✓ The workbook will be assessed on a university-specification PC running Excel 2024
✓ All answers must be formula or Excel function-based
Answers must not be simple/static/pasted values
Answers must not be found/derived manually by the user
Answers must not be generated by simple sorting/filtering methods. Sorting functions are fine.
Answers must not be based on VBA macros or VBA functions
Answers must not be based on the LET function [0 marks awarded]
The workbook should not contain links to external files. Any links will not be updated.
The workbook should not have any hidden sheets
The workbook should not have any un-named sheets e.g. Sheet1 [5 mark penalty]
The workbook should not have any circular references [5 mark penalty]
The workbook should not exceed 15MB in size [5 mark penalty]
Do not move/re-position any of the answer cells [5 mark penalty]
Submission by: Friday 24th April 2026
