Question

Capstone Project

In this project, you will analyze data for a company that processes and packages breakfast cereal. Complete the analysis required for each template and summarize your findings and recommendations in a memo to management. Justify all responses using your statistics.

Background Facts

Company: SneakyGood Cereals is a small producer of specialty organic breakfast cereal. Its signature product is Gluten-Free Organic Rainbow Crispies, sold directly online to consumers. The company sells approximately 3 million boxes annually.

Box weight: 20 ounces

Price per box: $6.00

Cost of goods sold (per box): $4.00

In the automated packaging process, equipment is set to fill boxes with a mean weight of 20.2 ounces and a standard deviation of .1 ounces. This is the company standard for the box weights. The company sets the weight slightly higher than the weight stated on the package label to avoid fines imposed by government inspectors when packages do not meet the minimum weight stated on the box.

The company recently took a random sample of 500 boxes and measured their exact weights. The data that resulted from this sample can be found on the Excel spreadsheet labeled Cereal Weights.

Capstone Project Part 1

Descriptive Statistics: Using Excel, calculate the following descriptive statistics and then interpret the results.

Descriptive Statistic	Value	Analysis/Interpretation
Mean
Median
Mode
Standard Deviation
Range
Lowest Value
Highest Value
Proportion of boxes with weight below 20.2 ounces
Proportion of boxes with weight above 20.2 ounces
Skewness

2. Using Excel, create and insert a frequency distribution and histogram of the box weights, using the following for the values for the upper limits of the bins (categories):

19.604

19.932

20.259

20.914

21.242

21.570

3. Based on the descriptive statistics, the histogram, and the empirical rule, does it appear that the box weights follow a normal distribution with a symmetrical, bell-shaped curve? Justify your response with your data analysis.

4. Based on your analysis, should management have any concerns about the packaging process from a business perspective? Explain.

Data:

Weight	Humidity
19.987	19
19.997	19
20.02	20
20.117	22
20.21	21
20.238	22
20.226	22
20.258	23
20.321	23
20.334	24
20.396	24
20.412	24
20.434	25
20.468	25
20.527	26
20.536	25
20.541	26
20.554	27
20.672	29
20.558	28
20.611	31
20.616	30
20.622	31
20.634	32
20.64	31
20.691	32
20.721	33
20.773	34
20.816	36
21.002	38

Answer 1

Descriptive Statistics: Using Excel, calculate the following descriptive statistics and then interpret the results.

Mean	20.59735	AVERAGE(B3:B502)
Median	20.6295	MEDIAN(B3:B502)
Mode	20.64	MODE(B3:B502)
Standard deviation	0.304656	STDEV.S(B3:B502)
Range	2.109	B509-B508
Lowest Range	19.187	MIN(B3:B502)
Highest value	21.296	MAX(B3:B502)
Proportion of boxes with weight below 20.2 ounces	30	COUNTIF(B3:B502,"<20.2")
Proportion of boxes with weight above 20.2 ounces	470	COUNTIF(B3:B502,">20.2")
skewness	-0.86714	SKEW(B3:B502)

2. Using Excel, create and insert a frequency distribution and histogram of the box weights, using the following for the values for the upper limits of the bins (categories):

Upper limit	F	FORMULA USED
19.753	8	COUNTIF($B$3:$B$502,">=0")-COUNTIF($B$3:$B$502,">=19.753")
20.038	12	COUNTIF($B$3:$B$502,"<=20.038")-COUNTIF($B$3:$B$502,"<=19.753")
20.323	53	COUNTIF($B$3:$B$502,"<=20.323")-COUNTIF($B$3:$B$502,"<=20.038")
20.893	354	COUNTIF($B$3:$B$502,"<=20.893")-COUNTIF($B$3:$B$502,"<=20.323")
21.178	61	COUNTIF($B$3:$B$502,"<=21.178")-COUNTIF($B$3:$B$502,"<=20.893")
21.463	12	COUNTIF($B$3:$B$502,"<=21.463")-COUNTIF($B$3:$B$502,"<=21.178")
SUM	500	SUM(B514:B519)

3. Based on the descriptive statistics, the histogram, and the empirical rule, does it appear that the box weights follow a normal distribution with a symmetrical, bell-shaped curve? Justify your response with your data analysis.

The box weight follows the normal distribution as the bell curved is formed and the data is symmetrically distributed on either side of the peak of the histogram.Also, we can find from descriptive statistics that mean, median and mode are approximately equal. The skewness value is between [-1,1] thus data is not far from symmetrical.

Based on your analysis, should management have any concerns about the packaging process from a business perspective? Explain.

yes, they should as the data has a mean weight 20.59 and standard deviation of 0.304 which is slightly above what the management want which 20.2 ounces and std of 0.1

95% confidence interval for the mean of population 20.2 ounces and since population standard deviation is not known we will be using t statistic

Thus the mean of the population lies between 20.57 and 20.62 with 95% confidence.

b) The packaging process is slightly above the mean of the company which is 20.2 ounces.

I need weights after Calibration and humidity spreadsheet to solve rest of the question. Please attach the spreadsheet.