E- Project Paper

MSCI – Spreadsheet Modelling Individual Project 2022/23 – Resit Task

Resit Task
This is a single task, worth 100%. The task involves two components; the analysis in Excel, and producing a short report documenting what you did, and providing all the answers to the 14 questions. You must use the provided data, in the Excel file: DriverCasualties (2023 resit).xlsx
This is an individual work, and it is expected that your models and report will be your own work. You should not give your spreadsheet model or report to anyone and you should not use files of anyone else. If we notice that some files have resemblance, this will be treated as plagiarism.

The file DriverCasualties (2023 resit).xlsx contains data on the number of deaths of drivers in car accidents with the following variables:
• DriversKilled – number of drivers killed per month
• front – front-seat passengers killed or seriously injured (KSI) per month
• rear – rear-seat passengers killed or seriously injured (KSI) per month
• kms – distance driven on average per month
• PetrolPrice – average petrol price per month
• law – binary variable, showing when the law for wearing seatbelts came into effect

You need to construct an analytical model in Excel, including any necessary diagnostics and answer the following questions based on the model:
1) Which month, on average, produces the highest number of deaths?
2) Which individual month (month and year) produced the highest number of deaths?
3) Which month, on average, produced the lowest number of deaths?
4) Which individual month (month and year) produced the lowest number of deaths?
5) Which year produced the highest number of total deaths?
6) Which year produced the lowest number of total deaths?
7) Is there any evidence that the number of deaths is changing over time? Produce a visual representation of the data and comment on any relevant patterns.
8) Is there any seasonality in the deaths? For example, are deaths more likely in the winter months, when road conditions (visibility, grip levels, ice etc.) are worse? Be clear how you have defined ‘winter’.
9) Does the data support the statement that seatbelt use has decreased the number of drivers deaths? Has seatbelt use also reduced the front-seat and rear-seat KSI figures?
10) How does the kilometres travelled change over time? Present a visual analysis of this relationship and comment on any relevant patterns? If driving habits continue at the same rate, what would be your estimate for the average kms driven for 1985, 1995 and 2005?
11) Is there a relationship between deaths and kilometres travelled? Present a visual analysis of this relationship and comment on any relevant patterns?
12) Is there a relationship between petrol price and kilometres travelled? Present a visual analysis of this relationship and comment on any relevant patterns? Is there any evidence that petrol price impacts on distance travelled?
13) Is there a relationship between drivers killed and the KSI figures (either just the front, just the back or the entire car)? Present a visual analysis of these relationships and comment on any relevant patterns?
14) Is there a relationship between front-seat KSI and rear-seat KSI? Put another way, is the proportion of rear-seat KSI to front-seat KSI consistent over time? Could you suggest any potential explanations in this regard, for example in terms of driving behaviours and car safety? Are there any implications for seat-belt usage policy for rear-seat passengers?

Submission
You need to submit:
• The report written in Microsoft Word or PDF file:
• The title of the report should contain the ID of student
• The report should not contain the name of student
• Excel file with all supporting models and analysis
• All results presented in the report must also appear in the Excel file, for transparency
The files must be submitted on Moodle as they are (ZIP archives are not accepted) latest by:
10am Monday 2nd October 2023
Note that the Moodle submission system is very strict in terms of deadline. Do not wait until
the last minute to submit your work.

Plagiarism
Software to detect plagiarism will be used in marking. Standard university procedure for
plagiarism applies for any detected plagiarism attempt. It is important to cite all the sources
used in the report (such as: url, a paper, a textbook). The code is also checked for plagiarism,
so make sure that you did not “borrow” it from anywhere else.

Late submission policy
Standard departmental penalties will apply for late submission unless you have been given
an extension (by departmental coordinators) for exceptional reasons.