[SOLVED] MSCI 242M Spreadsheet Modelling for Management R

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