supply chain management on excel

Assignment 5 Statistics and Data Analysis Businesses often survey their customers and employees to find out what they want and what they are doing. In the era of big data where everything is connected to the Internet and every device collects data, meaningful data analysis has become ever more important. Excel is a great tool to help managers decipher data and make sense out of it. Please read the following few articles to understand what surveys are and what Excel can do to help you analyze data. https://www.surveymonkey.com/mp/business-surveys/ (from one of the most popular FREE online survey site) http://www.willamette.edu/~dnegri/courses/econ230/Data_Analysis_Toolpack_Guide.pdf (brief introduction of Excel’s Data Analysis functions) In Excel, one would need to install the optional Data Analysis Tookpak to use these statistical tools. Once installed, one may choose Data, Data Analysis, then select the analysis you want

done. It’s that simple!

Assignment: Please recreate the spreadsheet per the PDF file using the shell provided. Be sure to use formulas in cells with blue figures. Use the data analysis tool to create the t-test and histogram results and the histogram chart. Modify the chart so that it looks pretty. Do the same for all survey questions then answer the following questions:

1. For question 2, are there significant differences between group 1 and group 0? How confident can we say that the two groups have different opinions about question 2?

2. How about questions 3, 4, and 5?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48

A B C D E F G H I J SURVEY ANALYSIS: T-TEST AND HISTOGRAM

LAST REVISION: 11/2/15 Internal instructions: FILENAME: SCM 350 Assignments Shaded areas are user input. Blue font is for formulas.

Developer: John Wu, [email protected], x7743 Gender 1:Men, 0:Women Use Data, Data Analysis , t-Test to test differences between groups

Tasks: Use conservative measure of two groups w/ unequal variances. Use Data, Data Analysis, Histogram to do histogram analysis.

Creat a chart that shows frequencies and cumulative % for answers to Q1. Do the same for each of the remaining questions, t-tests and charts.

2. How about questions 3, 4, and 5?

Gender Q. 1 Q. 2 Q. 3 Q. 4 Q. 5 Sample t-test for Q. 1. 1 2 3 2 5 2 t-Test: Two-Sample Assuming Unequal Variances 1 1 1 4 3 2

1 3 3 3 4 2 Variable 1 Variable 2

1 4 2 2 4 1 Mean 2.411764706 3.307692308

1 2 3 5 5 3 Variance 1.132352941 1.064102564

1 2 3 2 4 3 Observations 17 13

1 3 5 1 2 1 Hypothesized Mean Difference 0

1 2 5 4 5 2 df 26

1 1 3 2 3 2 t Stat ‐2.325218353

1 1 2 3 3 3 P(T<=t) one‐tail 0.014066146

1 3 3 2 3 3 t Critical one‐tail 1.70561792

1 2 4 1 4 2 P(T<=t) two‐tail 0.028132291

1 3 4 3 3 2 t Critical two‐tail 2.055529439

1 3 5 3 4 4

1 5 3 3 5 4 Bin Frequency Cumulative %

1 2 3 2 3 3 1 3 10.00%

1 2 4 4 3 3 2 10 43.33%

0 3 2 1 2 2 3 10 76.67%

0 3 4 3 4 2 4 4 90.00%

0 4 4 3 2 3 5 3 100.00%

0 3 2 2 2 3 More 0 100.00%

0 5 5 2 3 2

0 5 4 4 2 2

0 3 3 4 3 1

0 2 1 2 5 3

0 4 3 1 4 4

0 2 5 1 4 4

0 4 4 2 2 3

0 2 4 2 3 2 0 3 3 3 4 2

Mean 2.80 3.33 2.53 3.43 2.50 Std. Dev. 1.13 1.12 1.07 1.01 0.86

1 2 3 4 5 T-tests 0.03 0.83 0.32 0.10 0.84

1. For question 2, are there significant differences between group 1 and group 0? How confident can we say that the two groups have different opinions about question 2?

0.00% 20.00% 40.00% 60.00% 80.00% 100.00% 120.00%

0 2 4 6 8 10 12

1 2 3 4 5 More

Fr e q u e n cy

Bin

Histogram

Frequency Cumulative %