BSB131 Assignment 2 Car Data Analysis Australia 2023

Instructions

• Data for this Assessment can be found in the Excel Spreadsheet Car Data Australia 2023.xlsx.


• Please include all excel output necessary to answering the questions including data tables / graphs / data outputs / formulas in your answer document (PDF). I will only refer to the Excel solution file for working or to find where errors may have been made..


• It is highly recommended in doing your analysis that you copy data from the Original Data worksheet in the Excel file to a new worksheet when doing your analysis. In this way the original data will remain unaltered if you make any errors. You can then copy from this Original Data worksheet as you do each question so that errors are not carried across.


• Include any assumptions and / or checks you need to make to ensure any analysis can be conducted.


• Complete your solutions in a Word document but please save your final report as a PDF file with the title BSB131 Assignment 2.pdf. Please submit both this and your Excel working document.


• The Assignment is due on Friday 31^st May at 11:59PM

Dataset

The dataset for this assessment was created by Nidula Elgiriyewthana. It contains information on just under 14,000 car sales in Australia during 2023. Information has been provided on the following variables:

• Price Price of the car in $


• Brand name of the car manufacturer


• Model name or code of the car model


• Body Type shape or style of car (e.g SUV, Sedan, Hatchback etc)


• Transmission Manual or Automatic


• Fuel Type of Fuel (e.g. unleaded, diesel, hybrid, electric etc)


• Size Engine Capacity in Litres


• Consumption Fuel consumption in litres per 100km


• Year Year of manufacture or release


• Used/New Car is new or has had previous owners


• Kilometres Distance travelled by the car (in KM)

Your Task

We may not be at all interested in cars, but it is true that most of us will purchase one or more vehicles during our life. It is always a big decision given the cost involved and the fact that we want to be happy with our decisions as we use the vehicle we purchase constantly.

You have just had a visit from a friend who indicated they need to get another car. They had come across the attached data set and had heard you were studying data analytics. They want to know if you can help them analyse the information to help them make some decisions.

Most Popular Vehicles

1. The data set contains a lot of information on niche and luxury brands which do not really represent the market place. Graph sales (number of vehicles) for all brands and identify the three top selling brands. The graph should be ordered from most popular to least popular. (1 mark)


2. Nearly one third of the sales were made purchasing cars from the these three major sellers. Considering only these three brands:
   
   
   
   1. Split the data in to these three groups and create a full range of appropriate visualisations (graphs) and numerical statistics for the PRICES of each brand separately. What can you say about the comparative price distributions for each three. (5 marks)
   
   
   2. There is an obvious reason why Toyota is so much more expensive than the other brands. Identify the reason and remove the data related to that reason? Construct a 95% Confidence Interval for the average price of each of the three brands. Does this change your mind about the relative expensiveness of each brand? Indicate why. (3 marks)
   
   
   
   

Fuel Consumption

As much as they would like to, your friend is not in a position to purchase an electric vehicle because of the prices. As such they are considering only Internal Combustion Engines (Petrol, Diesel). With the current cost of living crisis and the cost of fuel, they are particularly interested in fuel consumption. They told you that in general they had heard that manual vehicles use less fuel.

3. Conduct a TEST to see if the average fuel consumption for manual vehicles is different to that of automatic vehicles. (5 Marks)


4. Does the result change if you are looking only at new vehicles (include Demo vehicles as new)? (3 marks)


5. Construct a Box and Whisker plot for each of new automatic and manual vehicles on the same graph. Does this change your opinion in any way? (2 marks)

New vs Used

We know that there have been changes in the types of cars people purchased over the last few years. One has been the increased interest in Electric / Hybrid vehicles, and another has been the shift away from Manual to Automatic vehicles. To examine these two contentions conduct TESTS to see if there is any evidence for the following:

6. That the proportion of New (include Demo) car sales that are Electric and Hybrid is now more than 10% (3 marks)


7. That the proportion of New manual vehicles is now less than the proportion of Used manual vehicles. (5 marks)

Which factors impact price

Finally both you and your friend are interested in determining which factors most affect the price of vehicles. To answer this question you have decided to exclude any vehicles over the value of $200,000 as you believe these vehicles do not represent the more mainstream vehicle market. Additionally, for reasons related to too many variables or lack of variation you can ignore the variables Brand, Model and Cylinders.

8. Run a regression to identify which factors best explain variation in price. For body type please include a dummy variable for Ute/Tray only. You should do this using a Stepwise regression. Include all of the steps of your regression in the Excel spreadsheet including any notes of why you make decisions to include or exclude variables. Once that has been completed copy ONLY the final best model to your answer document. (8 marks)


9. Write up the results of this final regression model fully. This should include full interpretation of any results and clearly show all hypothesis tests written up accurately. (6 marks)


10. What is the impact of the body type Ute / Tray. What do you notice about this value and why do you think this is happening? (1 mark)


11. Predict the price of a 5 year old, 4.5Litre, manual, diesel, ute that has done 65,000km and consumes 12litres per 100km. (1 mark)

Summary

12. In no more than 200 words summarise what you have learned about cars in Australia. Highlight the one thing you think is most important to tell your friend. (2 Marks)

Total = 45 marks