ADVANCED in Excel !!!
Sheet1
SIC Code Number of Employees Number of Products Value Added by Mfg. Cost of Materials Value of the Shipments New Captital Epenses End of Year Invetory Industrial Classification
201 433 370 23518 78713 4 1833 3630 1
202 131 83 15724 42774 4 1056 3157 1
203 204 169 24506 27222 4 1405 8732 1
204 100 70 21667 37040 4 1912 3407 1
205 220 137 20712 12030 4 1006 1155 1
206 89 69 12640 13674 3 873 3613 1
207 26 18 4258 19130 3 487 1946 1
208 143 72 35210 33521 4 2011 7199 1
209 171 126 20548 19612 4 1135 3135 1
211 21 15 23442 5557 3 605 5506 2
212 3 2 287 163 1 2 42 2
213 2 2 1508 314 1 15 155 2
214 6 4 624 2622 1 27 554 2
221 52 47 2471 4219 2 292 929 3
222 74 63 4307 5357 2 454 1427 3
223 13 12 673 1061 1 20 325 3
224 17 13 817 707 1 84 267 3
225 169 147 8986 10421 3 534 2083 3
226 51 41 3145 4140 2 220 697 3
227 55 44 4076 7125 2 176 1446 3
228 84 76 3806 8994 2 423 1014 3
229 61 47 4276 5504 2 464 1291 3
231 27 22 1239 716 1 22 356 4
232 200 178 9423 8926 3 200 2314 4
233 294 250 11045 11121 3 189 2727 4
234 38 32 1916 2283 1 29 682 4
235 17 14 599 364 1 21 197 4
236 34 28 2063 1813 1 20 450 4
237 1 1 34 71 1 2 17 4
238 31 25 1445 1321 1 16 526 4
239 224 179 10603 12376 3 465 2747 4
241 83 68 5775 9661 3 539 578 5
242 172 147 10404 19285 4 1071 3979 5
243 257 209 13274 18632 4 711 3329 5
244 51 43 1909 2170 1 88 355 5
245 82 68 4606 7290 2 182 580 5
249 94 78 5518 8135 2 715 1604 5
251 273 233 12464 12980 3 481 3535 6
252 70 53 5447 4011 2 358 829 6
253 37 29 2290 5101 2 128 447 6
254 81 61 4182 3755 2 177 956 6
259 54 39 2818 2694 2 109 718 6
261 15 11 2201 3279 2 698 725 7
262 116 90 18848 20596 4 3143 4257 7
263 55 42 9655 10604 3 2360 1502 7
265 212 163 15668 24634 4 1352 3976 7
267 232 182 25918 28963 4 1750 5427 7
271 403 136 30692 8483 4 1277 894 8
272 121 16 17982 6940 3 311 1216 8
273 136 57 17857 8863 3 618 3736 8
274 69 25 9699 2823 2 144 874 8
275 604 437 38407 29572 4 2959 4300 8
276 41 28 3878 3811 2 198 688 8
277 21 12 3989 1047 2 66 577 8
278 65 50 4388 2055 2 130 504 8
279 55 39 4055 1098 2 210 236 8
281 80 45 16567 11298 3 2002 2644 9
282 115 79 25025 34596 4 3731 6192 9
283 213 106 59813 27187 4 4301 11533 9
284 126 75 31801 19932 4 1304 4535 9
285 51 28 8497 9849 3 404 2178 9
286 126 75 28886 46935 4 6269 8577 9
287 37 24 12277 11130 3 1025 2354 9
289 76 45 11547 13085 3 1006 2749 9
291 67 43 26006 132880 4 5197 10718 10
295 25 18 3464 6182 2 251 658 10
299 14 8 2187 4446 2 124 670 10
301 65 54 7079 7091 3 579 1067 11
302 8 7 442 496 1 9 175 11
305 61 46 4528 3805 2 341 1057 11
306 122 95 7275 7195 3 435 1411 11
308 763 598 55621 57264 4 5658 11874 11
311 15 12 1313 1865 1 52 404 12
313 3 2 162 163 1 1 35 12
314 37 31 1907 1682 1 35 716 12
315 2 2 53 85 1 12 62 12
316 6 4 747 395 1 18 199 12
317 8 7 328 255 1 6 75 12
319 7 6 233 177 1 4 40 12
321 12 9 1717 943
Project Part 1
DIRECTIONS: For this assignment you will conduct a descriptive statistics investigation and analysis on the data set associated with this assignment that is poste to Moodle. Please complete each of the following numerical prompts. Copy and paste the numerical prompts into your submission document and post your responses directly below each prompt.
DO NOT submit Excel datasets. Do save your submission and submit to Moodle in PDF FORMAT ONLY. Copy and paste all requested charts, tables, and graphs from Excel into your submission document. Tables, charts, and graphs are to be formatted to expectations for the class (see Week 1 materials). Points are deducted for formatting and illegibility. Please use complete sentences when providing discussions or reporting interpretations for results (see week 1 materials). Report calculations when requested.
1. Review and study ALL course materials and textbook chapter material prior to beginning this assignment.
2. Open the Data set associated with Project Part I
3. Create a table which lists each variable and its most likely level of measurement. Format the table to class expectations as required on ALL assignments.
4. Generate an Excel descriptive statistic table for eacb variable. Discuss and report the measures of central tendency (mean and median), measures of variability (standard deviation, IQR, and range), kurtosis, and skewness numerical summaries. See exemplar (posted at the top of the course list in Moodle) for sample write up. DO NOT JUST LIST the numerical summaries. Each of the numerical summary areas (measure of center, variability, kurtosis, and skewness) must have an associated meaningful interpretation to receive credit.
5. Based on the descriptive statistic tables generated in #3 above, which is the better measure of the center (mean or median) for each variable? Justify your response using rationale presented in the course materials provided for this week. That is, REVIEW AND APPLY justification presented in the course materials prior to responding to this prompt.
6. Create a table and which contains the 5-number summary for the End of Inventory variable. Calculate and interpret the IQR for this variable. Report the formula you used to calculate the IQR.
7. In Excel, generate a box plot for the End of Inventory variable. Referencing the box plot, interpret the shape of the distribution of this variable. That is, state specifically which characteristics of the boxplot lead you to your claim regarding the distribution shape
8. In Excel, generate a histogram for the Number of Employees variable. Reference characteristics of the histogram as you discuss the shape of the variable distribution.
9. Can the empirical rule be applied to the Number of Employees variable? Justify your response referencing details contained with the course materials for this week.
10. Calculate the Z-score for the Cost of Materials value for SIC ODE 262. Report the formula you used. Is the C
Introduction
Numerical Summaries: summarize the data set
Parameters : calculated using population data
Statistics: calculated using sample data
with a well defined random sample, can be used to estimate the population parameter
Examples:
1 variable numerical summaries
measures of the center (mean, median, mode)
Measures of spread (variance, standard deviation, range, IQR, standard error)
Skewness: how symmetric is the distribution? (perfect symmetric then skewness = 0)
positive (right skewed)
negative (left skewed)
Kurtosis: how much probability is in the tails of the distirbution?
usually compares to Normal distirbution tails (kurtosis = 3)
Skewed distributions Kurtosis
2 variable numerical summaries (will get to these later)
correlation
coefficient of determination
Numerical summaries of relative position of data values
z-scores
percentiles
Symbols
Descriptive Statistics
Can use Data Analysis Toolpak in Excel (File … Options … Data Analysis Toolpack … Go)
Data … Data Analysis…Descriptive Statistics
Age
80
91 Age
77
85 Mean 79.1046728209
71 Standard Error 0.9521947114
89 Median 79.2189722038
82 Mode ERROR:#N/A
79 Standard Deviation 9.5694384163
74 Sample Variance 91.5741516027
68 Kurtosis 0.0747918953
103 Skewness 0.2033261591
92 Range 47.803631584
85 Minimum 55.4111721156
81 Maximum 103.2148036997
82 Sum 7989.5719549096
64 Count 101
81
84
100
86
83
67
84
78
63
70
85
99
93
79
88
69
86
103
85
73
61
71
89
98
71
81
83
81
69
65
77
82
80
78
60
71
71
73
86
84
79
77
85
72
83
89
85
91
73
76
87
73
86
85
78
94
79
76
79
76
64
78
65
71
72
68
81
84
77
68
70
82
66
81
82
79
55
99
68
74
80
76
89
69
79
Frequency Distribution Table1
Un-grouped Frequency Distribution Table
Class Frequency
Dr77: Frequency:
Unit counts for each class Percent
Dr77: Percent:
=100*(Frequency/ Frequency Total)
Relative Frequency
Dr77: Relative Frequency:
= Frequency / Total Frequency
Cummulative Frequency
Dr77: Cumulative Frequency: = Sum of all prior and the current Frequency
Plastic 9 25.7142857143
Wheat 7 20
Insect 12 34.2857142857
Metal 2 5.7142857143
Liquid 5 14.2857142857
Total
Dr77: Total:
Use =Sum feature in Excel
35
Additional table columns can be added beyond just the Frequency column
Percent Plastic Wheat Insect Metal Liquid 25.714285714285712 20 34.285714285714285 5.7142857142857144 14.285714285714285
z-score
Standard deviation unit distance a data value is from the mean of the distribution
You need
mean
standard deviation
Age Z-score
80
91
77
85
71
89
82
Why Choose Us
- 100% non-plagiarized Papers
- 24/7 /365 Service Available
- Affordable Prices
- Any Paper, Urgency, and Subject
- Will complete your papers in 6 hours
- On-time Delivery
- Money-back and Privacy guarantees
- Unlimited Amendments upon request
- Satisfaction guarantee
How it Works
- Click on the “Place Order” tab at the top menu or “Order Now” icon at the bottom and a new page will appear with an order form to be filled.
- Fill in your paper’s requirements in the "PAPER DETAILS" section.
- Fill in your paper’s academic level, deadline, and the required number of pages from the drop-down menus.
- Click “CREATE ACCOUNT & SIGN IN” to enter your registration details and get an account with us for record-keeping and then, click on “PROCEED TO CHECKOUT” at the bottom of the page.
- From there, the payment sections will show, follow the guided payment process and your order will be available for our writing team to work on it.