Established by Ira Kelly in 2015, Kelly's Gloves has grown rapidly. Kelly's produces it own unique branded gloves in 3 styles known as A, B & C. However, while ramping up its production, Ken Dolls (Kelly's quality manager for the production plant) has noticed a disturbing number of defective gloves coming out of production.

In every production batch exactly one out of 3 types of gloves is produced by one employee on one production machine. Kelly's has 4 production machines (1,2,3,4) and 5 employees (known as 111,222,3333, 444 & 555). So, there are 3* 4* 5 (60) possible combinations of employee, machine and product.

Indeed, since there are 4 different batch sizes that are run (10000, 20000, 100000 & 200000) it might be more accurate to say that there are really 240 potential permutations. Of course, many combinations will wind up never occurring.

Mr. Dolls has hired you to assist Kelly's in determine where the production defects are concentrated. He has provided you with a listing of the most recent 75+ production batches from the plant. He would like you to use Excel's PivotTable and conditional formatting features to perform analyses on the data in order to figure out what factors (or factor) are driving these production errors.

Here is a description of the data he has provided you with:

a. Batch: A unique number that identifies each batch produced. b. Product: A unique letter that identifies each product. c. Machine: A unique number that identifies each machine on which products are produced. d. Employee: A unique number that identifies each employee producing products. e. Batch Size: The number of products produced in a given batch. f. Num Defect: The number of defective products produced in a given batch.

• Analyze the source data and determine what factor (or factors) are causing errors (defective gloves) in the production batches
• Write up a document file describing your approach to this problem and what your analysis revealed
• Provide recommendations
• Reference the labeled worksheets you have created in your Spreadsheet analysis

You can obtain the required spreadsheet data file for this assignment by clickinghere (it is also attached to the assignment folder)

Your solution must contain at least two Pivot tables AND onePivotChart but will likely contain at least several more. Each should be labeled - and referenced by label in your written presentation of your analysis and your solution & recommendations.

In creating your pivot tables (one per worksheet please), you are free to consider interaction effects between 2 or more factors (e.g., a pivot table that looks at the error rate in respect to both machine and product type).

1. The secret to this assignment is that all the pivot table computations should not be utilizing as data the raw data column of Num Defect (i.e., the actual number of production errors for each batch) but should instead use a new computed column you will create (in the source data worksheet) that is a % error rate for each batch. This is intuitively obvious since 200 defective products in a batch of 10,000 products is more significant than the same number of errors in a batch of 100,000. Therefore, your first activity is copying the raw batch data into a new worksheet and then add a column onto this data: A computed batch error rate for each production batch (i.e.,each row of data).
2. Instead of computing pivot table results using SUMs or COUNTs in your pivot tables you will usually have to use AVERAGE instead. Computing a sum of the error rates for each employee will be meaningless as the basis of comparison among any of the factors (Products, Employees etc.) that characterize each production batch. You will need to be comparing averages of computed error rates, which means that you have to change the basis of computation in your Pivot tables from Sum(X) to Average(X) - where X is a batch error rate.

BATCH	PRODUCT	MACHINE	EMPLOYEE	BATCH SIZE	NUM DEFECTIVE
1	A	1	333	10000	320
2	B	3	555	200000	200
3	C	2	222	100000	260
4	C	4	444	20000	240
5	B	2	333	20000	100
6	B	3	111	100000	180
7	C	4	222	200000	400
8	A	1	333	200000	280
9	A	2	111	100000	340
10	C	3	555	10000	380
11	B	1	333	10000	80
12	C	3	444	20000	140
13	A	4	222	100000	100
14	C	2	333	200000	160
15	C	3	111	200000	420
16	A	1	111	100000	300
17	A	2	111	10000	120
18	A	4	444	10000	180
19	A	3	333	10000	0
20	B	2	222	20000	300
21	A	3	555	200000	240
22	B	4	333	200000	200
23	C	3	444	200000	160
24	C	4	222	100000	240
25	B	3	555	20000	120
26	B	2	111	20000	160
27	B	2	222	100000	100
28	C	3	333	200000	360
29	C	4	444	10000	300
30	C	1	555	10000	80
31	A	2	444	200000	260
32	A	1	555	10000	100
33	B	3	333	100000	360
34	C	1	222	20000	220
35	C	4	444	20000	460
36	B	2	111	100000	280
37	A	3	333	10000	60
38	C	4	222	200000	180
39	A	2	444	100000	20
40	A	3	222	20000	300
41	B	1	222	20000	380
42	B	2	222	100000	60
43	C	4	222	200000	0
44	B	2	333	10000	240
45	C	3	111	10000	120
46	B	4	444	200000	160
47	B	3	222	10000	100
48	C	1	555	100000	360
49	C	4	444	20000	300
50	A	2	555	20000	80
51	A	3	111	100000	260
52	A	1	222	10000	100
53	A	2	333	200000	360
54	B	3	444	10000	220
55	A	3	555	100000	460
56	B	2	222	20000	280
57	C	1	111	20000	60
58	C	3	333	100000	180
59	B	2	222	10000	340
60	B	3	444	200000	380
61	B	4	333	100000	80
62	C	4	555	20000	140
63	C	2	444	20000	100
64	C	3	111	100000	160
65	A	1	222	200000	420
66	A	2	111	10000	300
67	B	3	222	10000	120
68	C	2	333	200000	180
69	B	3	444	10000	0
70	A	4	222	200000	300
71	C	2	555	100000	240
72	C	3	444	20000	200
73	C	1	555	10000	160
74	B	3	333	10000	240
75	A	2	111	200000	120
76	A	3	555	100000	160