MSCI 342 Advanced Spreadsheet Modelling 2024

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

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

 




2024-03-05