6 problems (Excel Assignment)

  Excel assignment for Geography class in College. There are 6 questions ( problems) to answer. Please read the instruction carefully. READ THESE INSTRUCTIONS CAREFULLY BEFORE YOU BEGIN.  Open the Excel file posted under the “Test” folder on Blackboard.  ENTER YOUR FULL NAME IN CELL A1 on the first worksheet (Question 1).  SAVE THE FILE OFTEN, USING YOUR LAST NAME as the file name.  All answers and work must be done in the Excel file—no additional files can be submitted  When finished, you must upload your Excel file back to Blackboard by midnight Monday, March 26.  You will only be allowed to upload one file, so make sure it is your complete and correct file before you submit it. You may not email files afterwards either. The purpose of this assignment is to demonstrate the skills you have learned during the semester. In particular, you are being assessed on your ability to conduct statistical analysis using Microsoft Excel. As such, all analyses in this assignment must be done using Excel tools and functions. Equations must use cell references, rather than specific values when possible. -Show all work for each question on the same worksheet, at the top of the worksheet, right beside the given data so it is clearly visible. Indicate question numbers (e.g. 1b) beside your answers. -Make sure all work is clearly labelled. i.e. if you calculate a value, put a label in the adjacent cell, e.g. “Z =” or “Tcalc=”. Do not leave unidentified numbers scattered all over the worksheet as they may not get marked. -All graphs must have titles, labels, scales, legends, etc as needed. -For hypothesis tests, when deciding whether or not to reject the null hypothesis, give the reason(s) for your decision (e.g Tcalc > Tcrit, etc), and include a concluding statement. -No statistical tables are required for this test as Excel can be used for everything, but you may consult them if you want. Start by adding the Data Analysis Tools as you have done for most assignments. Questions: 1. The worksheet Question 1 lists concentrations of microplastics (particles/m3) in multiple samples of ocean water. Also shown are bin (class values) to be used in the creation of a histogram. a) Generate descriptive statistics for the microplastic concentration data, including the 95% Confidence Level for the mean. (2 marks) b) Determine Q1, Q3, and the interquartile range. (3 marks) c) Are there any outliers in the data? How did you determine this? (1 mark) d) Determine the 95% Confidence Interval for the mean. (2 marks) e) Create a Histogram using the given bin values. (2 marks) f) Are these data normally distributed? Give at least 2 reasons for your answer. (2 marks) 2. The worksheet Question 2 shows data for the number of customers per day at a fast food chain, and the distance of each restaurant from city centre (km). a) Create a scatter plot of the data. Show the regression equation on the graph. Include all necessary labels, titles etc to make the graph complete. (3 marks) b) Describe the relationship (if any) shown by your graph. (Place your answer below the graph.) (1 mark) c) Conduct both a correlation and a regression analysis for the variables using Data Analysis Tools. (2 marks) d) Examine the output from c above and enter the values of r, R2, slope and y-intercept. Create a small table to list these values. Use cell references to display the values (refer to the appropriate cell in the output table). (2 marks) e) Below the table, comment on the strength and direction of the relationship and, the goodness of fit of the regression equation. Refer to the appropriate values to support your statements. E.g. …the r value -0.66 indicates the relationship is _________ (3 marks) f) Use the regression equation to estimate the number of visitors expected for restaurants 18 km from the city centre. Use cell references in your calculations. (1 marks) 3. Question 3 lists observations from a sample of 78. The values have a normal distribution. a) Sort the numbers from smallest to largest (1 mark) b) Calculate the mean and standard deviation of the data (use Excel functions) (2 marks) c) Calculate z scores for all values (place in the second column). (1 mark) d) Calculate probabilities for all Z scores (place in the 3rd column). (1 mark) e) Complete the table with the indicated values of Z and p. YOU MUST USE CELL REFERENCES OR CALCULATIONS THAT INCLUDE CELL REFERENCES IN THE TABLE. DO NOT ENTER VALUES DIRECTLY. (2 marks) 4. Question 4 shows results of a study investigating whether test performance following a night of sleep deprivation differs between males and females. a) Conduct a full hypothesis test to determine if there is a significant difference between male and female test scores (test at 5% significance, assume unequal variances). (4 marks) 5. Question 5 lists annual precipitation amounts at several weather stations the year before, and the year after a coal-fired power plant was built in the region. a) Conduct a full hypothesis test (5% significance) to determine if the annual precipitation changed after the plant was built. List and number all steps in your hypothesis test in the worksheet. (4 marks) 6. Question 6 shows data indicating mode of transportation to and from work from a sample of workers in Vancouver, along with the expected proportions based on national data. a) Determine the value of chi-squared (χ²) for these data. You may do this the ‘long way’ using columns of observed and expected frequencies etc, along with the necessary sums. Or, you may use Excel functions to determine the observed significance and Chi-squared. (Bonus mark if you do both and confirm you get the same chi-square value) (2 marks) b) Conduct a full hypothesis test to determine if the Vancouver transportation patterns are the same as the national pattern. List all steps in your hypothesis test. (4 marks)