I need a statistics project done.
The project needs to be done in Excel using the appropriate functions and tools. You may use MicrosoftWord to write up the discussions, assumptions, and the limitations (see below for more information).
• Excel is used significantly in business and industry and this project will provide you with an opportunity to hone your Excel skills. Examples on how to create certain types of charts (e.g. scatter, bar, pie) and use formulas (e.g. how to find the mean, normal probabilities, etc.) can be found in Canvas Modules under the appropriate chapters and in the class text. If you have questions on how to do the statistical analysis in Excel then please do not hesitate to contact your instructor prior to the due date.
• Please note that simply typing in your work (calculations) into Excel is not sufficient. You must use appropriate Excel functions (e.g. =AVERAGE) to find the probabilities and other calculations.
• The work needs to be clear and well organized. Points will be deducted for work that is not clear or missing.
• Clearly label each part of the project.
• All the data for the project can be found in the Excel file: Project Data. There are three worksheets in this Excel file. See below for information regarding the data.
Scenario
Maggie has recently opened a coffee shop near a college campus. She likes statistics and has been compiling information regarding favorite customer beverages and total daily sales.
Part 1: Qualitative (Categorical) Data:
Use the information given in the Table 1 worksheet for Part 1. Maggie surveyed 250 customers regarding their favorite beverage at the coffee shop.
• Organize the data by creating a frequency distribution and relative (or percentage) frequency distribution for the favorite beverages.
• Organize the data by creating a bar graph and pie chart. Be sure to include data labels and titles in your charts.
• Discuss the results of these tables and graphs. What are the least and most popular items? This needs to be at least one (1) paragraph in length. Note a paragraph contains at least three complete sentences. Watch your spelling and grammar.
Rubric for Part 1:
Frequency and relative (or percentage) frequency distribution: 6 possible points
Bar graph and pie chart:
Part 2: Quantitative Data
Use the information given in worksheet Table 2 for Part 2. The data is the past 45 days of total daily sales at the coffee shop. A histogram, the sample mean, median, and sample skewness values for this data is provided in the Table 2 worksheet.
• Discuss the results of the histogram and the shape of the data for total daily sales at the coffee shop. This needs to be at least one (1) paragraph in length. Note a paragraph contains at least three complete sentences. Watch your spelling and grammar.
• Conduct a hypothesis test on the population mean for total daily sales to see whether the population mean is $740 using critical value, p-value, and confidence interval approaches. You need to provide the following for the hypothesis test: o Null and alternative hypothesis statements o Test statistic o Critical values o P-value o Confidence intervals o Clearly explain whether one should reject or not reject the null hypothesis and why using the critical value, p-value, and confidence interval approaches at both 0.01 and 0.05 levels of significance.
• What assumptions did you make about the process when conducting your hypothesis test on the population mean for total daily sales. This needs to be at least one (1) paragraph in length. Note a paragraph contains at least three complete sentences. Watch your spelling and grammar.
Part 3: Normal Probability:
Maggie believes that daily sales are normally distributed with a mean of $750 and a standard deviation of $60. Determine the following and show your work using the appropriate Excel functions.
• Find the probability that daily sales are less than $800.
• Find the probability that daily sales are greater than $850.
• Find the probability that daily sales are between $715 and $785.
• Find the probability that daily sales are less than $650 or greater than $825.
• Find the amount ($) where the probability is 55% that sales are less than this value.
• Find the amount ($) where the probability is 7.5% that sales are greater than this value.
Part 4: Scatter Diagram, Covariance, Correlation, and Regression
Maggie has been offering monthly promotions at her business. Table 3 worksheet shows the number of promotions and total monthly sales for the past 15 months. Determine the following:
• Create a scatter diagram. Be sure to include axis titles on your graph.
• Calculate the sample covariance and sample correlation coefficient.
• Discuss the results of the correlation coefficient and scatter diagram. Is there a possible relationship between the number of promotions and sales? Explain and use your results to back up your statements. This needs to be at least one (1) paragraph in length. Note
A paragraph contains at least three complete sentences. Watch your spelling and grammar.
• What are the limitations of the correlation coefficient result? Explain. Watch your spelling and grammar.
• Run a linear regression using Excel's Data Analysis regression tool. Construct the linear regression equation and determine the predicted monthly sales value if the number of promotions per month is 6. Is the regression model significant? Clearly explain your reasoning using the regression results.
Attachment:- Project Data-stats.xlsx