1008 - Analyzing and Visualizing Car Sales data Using Excel

Assignment Task

Questions

1. Using data in the Vehicle Sales excel sheet contain time series data on number of cars sold in Australia from 2010 to 2017. Using appropriate graphs analyse the trend and explain the changes in data over time.

2. Based on data given in State level excel sheet, explain which graphical technique would you use for making comparison in the number of cars sold in each state in 2016 and 2017? Explain your reasoning in selecting this graphical chart. Using Excel, construct and present the chart.

3. Which graphical technique would you use for making comparison of the proportion of the number of cars sold in each state given in State level excel sheet in 2017? Explain your reasoning in selecting this graphical chart. Using Excel, construct and present the chart.

4. Utilizing the data in Brand excel sheet, conduct a comprehensive analysis of the sales data for the period spanning from 2022 to 2023.

5. A researcher wishes to draw a histogram in presenting graphical summaries of the data on the two main variables concerned – training time in hours and number of errors committed, as stored in ‘Training’ worksheet in the provided excel file.

6. She decided to use 7 class intervals to construct a histogram for each variable. Explain how the researcher could have decided in using 7 as the number of class intervals to be used. The researcher specified class intervals 0 < X>

7. Using Excel, draw and display the histogram for the training time variable using the class intervals specified in part (b). Comment on the shape of the distribution of the data set as displayed in the histogram. 

8. Using the relevant Excel function, prepare and display a numerical summary report for each of the two variables including measures such as mean, median, mode, range, variance, standard deviation, smallest and largest values, and the three quartiles. Also, calculate and display the coefficient of variation value for each of the two variables.

9. Using the relevant Excel function, compute the correlation value of the relation between the training hours and number of errors. Display the correlation value. Then, provide an interpretation of this correlation value in terms of the relationship between the two variables. 

10. The Statistician wishes to estimate a linear regression analysis to predict the number of errors and training time. Based on the information provided above, specify the dependent variable and the independent variable for doing the regression analysis. Briefly explain the reasoning for your selection. 

11. Using Excel, draw a chart to evaluate relationship between training hours and number of errors. Display your chart and provide a brief comment on what you can observe from the chart. 

12. Use Excel to estimate a simple linear regression model for training hours and number of errors. Display the Excel regression summary table and write the estimated linear equation. Then, provide an interpretation of the estimated coefficients and coefficient of determination Predict the number of errors when the training is 150hrs.

13. A fertilizer manufacturing company is conducting experiments to develop a new fertilizer. This new fertilizer would be considered profitable if it could yield 100kg per square meter of crops. Based on 50 samples, the average yield per square meter has been calculated as 112kg, with a standard deviation of 46kg. Assuming that the crop yield follows a normal distribution, we need to conduct a test at the 1% significance level to determine whether it would be profitable to introduce the new fertilizer. How would this test change if it were conducted at the 5% significance level?

14. A company claims that 70% of its customers are satisfied with their product. To investigate this claim, a random sample of 1000 customers was taken, and 670 of them expressed satisfaction. Conduct a hypothesis test at a 2.5% significance level to determine if there is enough evidence to support the company's claim. What is the p-value of the test?

This Statistics has been solved by our PHD Experts at My Uni Papers.