Question

Review Technology Plug-In 3 Problem Solving Using Excel 2016. Complete exercise 1. Production Errors
Established in 2002, t-shirts.com has rapidly become the place to find, order, and save on T-shirts. One huge selling factor is that the company manufactures its own T-shirts. However, the quality manager for the production plant, Kasey Harnish, has noticed an unacceptable number of defective T-shirts being produced. You have been hired to assist Kasey in understanding where the problems are concentrated. He suggests using a PivotTable to perform an analysis and has provided you with a data file, T3_TshirtProduction_Data.xls.

The following is a brief definition of the information within the data file:
A. Batch: A unique number that identifies each batch or group of products produced.
B. Product: A unique number 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.

Make fifteen pivottables as follows.

1. Four one-factor pivottables that investigate the defective rates. Use Product, Machine, Employee, and Batch Size as row fields and Num Defect as the value field to make four pivottables.

2. Six two-factor pivottables. Use Product x Machine, Product x Employee, Product x Batch Size, Machine x Employee, Machine x Batch Size, Employee x Batch Size as row and column fields respectively. For example, when you use Product x Machine factors, you put Product as the row field and Machine as the column field. Still, Num Defect is the value field.

3. Four three-factor pivottables. Use Product x Machine x Employee, Product x Machine x Batch Size, Product x Employee x Batch Size, Machine x Employee x Batch Size as row and column fields respectively. For example, when you use Product x Machine x Employee factors, you put Product and Machine as the row fields and Machine as the column field. Still, Num Defect is the value field.

4. One four-factor pivottable. Use Product x Machine x Employee x Batch size as row and column fields. The first three factors can be the row fields, and the last one is the column field. Still, Num Defect is the value field.

Here is the data for this question

BATCH	PRODUCT	MACHINE	EMPLOYEE	BATCH SIZE	NUM DEFECTIVE
1	10	5	3333	500	16
2	20	7	5555	10000	10
3	30	6	2222	5000	13
4	30	8	4444	1000	12
5	20	6	3333	1000	5
6	20	7	1111	5000	9
7	30	8	2222	10000	20
8	10	5	3333	10000	14
9	10	6	1111	5000	17
10	30	7	5555	500	19
11	20	5	3333	500	4
12	30	7	4444	1000	7
13	10	8	2222	5000	5
14	30	6	3333	10000	8
15	30	7	1111	10000	21
16	10	5	1111	5000	15
17	10	6	1111	500	6
18	10	8	4444	500	9
19	10	7	3333	500	0
20	20	6	2222	1000	15
21	10	7	5555	10000	12
22	20	8	3333	10000	10
23	30	7	4444	10000	8
24	30	8	2222	5000	12
25	20	7	5555	1000	6
26	20	6	1111	1000	8
27	20	6	2222	5000	5
28	30	7	3333	10000	18
29	30	8	4444	500	15
30	30	5	5555	500	4
31	10	6	4444	10000	13
32	10	5	5555	500	5
33	20	7	3333	5000	18
34	30	5	2222	1000	11
35	30	8	4444	1000	23
36	20	6	1111	5000	14
37	10	7	3333	500	3
38	30	8	2222	10000	9
39	10	6	4444	5000	1
40	10	7	2222	1000	15
41	20	5	2222	1000	19
42	20	6	2222	5000	3
43	30	8	2222	10000	0
44	20	6	3333	500	12
45	30	7	1111	500	6
46	20	8	4444	10000	8
47	20	7	2222	500	5
48	30	5	5555	5000	18
49	30	8	4444	1000	15
50	10	6	5555	1000	4
51	10	7	1111	5000	13
52	10	5	2222	500	5
53	10	6	3333	10000	18
54	20	7	4444	500	11
55	10	7	5555	5000	23
56	20	6	2222	1000	14
57	30	5	1111	1000	3
58	30	7	3333	5000	9
59	20	6	2222	500	17
60	20	7	4444	10000	19
61	20	8	3333	5000	4
62	30	8	5555	1000	7
63	30	6	4444	1000	5
64	30	7	1111	5000	8
65	10	5	2222	10000	21
66	10	6	1111	500	15
67	20	7	2222	500	6
68	30	6	3333	10000	9
69	20	7	4444	500	0
70	10	8	2222	10000	15
71	30	6	5555	5000	12
72	30	7	4444	1000	10
73	30	5	5555	500	8
74	20	7	3333	500	12
75	10	6	1111	10000	6
76	10	7	5555	5000	8

Answer 1

*****Please please please LIKE THIS ANSWER, so that I can get a small benefit, Please*****

Before putting the pivot we will insert a column called "Defective %" in which we calculate the percentage of

defectives given by

Defective % = Num Defective/Batch Size

Pressing the symbol "%" in the Home tab of the excel sheet gives percentage of the defective.

The excel sheet thus prepared is as shown in the snap shot below:

Now we prepare the piviot table by selecting the data and using "pivot table" option in the insert tab

The snapshot of the pivot looks like this:

Analysis of the data based on defective %

Now, coming to the analysis part, first drag the "Employee" field to the Row Labels and "Machine" field to the Column Labels in the pivot table. From the table we can infer the following:

(1) Employee with id's 2222 and 4444 are comparitively producing more defective products (15% as highlighted in Red) than others.

(2) From the table we can see that the Machine No 7 is producing more number of defectives followed by machine number 6 (namely 19% and 17% as highlighted in red).

The snapshot of the above is given below:

Now remove the "Employee" field in the the Row lable and drag the Product field. We can see that there are maximum number of defectives in the Product with codes 20 and 30 (22% each as highlighted in red)

The snapshot is as shown below:

Hence the analysis of the defectives from the data can be summarized as follows:

(1) Employees with Id's 2222 and 4444 are least efficient.

(2) Machine No 7 and 6 are more inefficient compared to the other two

(3) More defectives are seen in the product with codes 20 and 30

ALTERNATIVE ANSWER:

Introduce a new column % DEFECTIVE where % DEFECTIVE = NUM DEFECTIVE / BATCH SIZE. This would be the right measure to figure out what is not performing in the right way.

Creating a PIVOT table:

In Excel, to create pivot table, click on the insert tab and then click pivot table. In the select table / range, select the entire data.

Table 1: PRODUCT, NUM DEFECTIVE and % DEFECTIVE

Choose PRODUCT in row labels, NUM DEFECTIVE and % DEFECTIVE in sum values. Make sure NUM DEFECTIVE has an attribute of SUM and % DEFECTIVE has an attribute of average. The default attribut is sum. To change it, click on the drop down arrow in % DEFECTIVE and choose value field settings -> Average

We find that Product 20 and Product 30 have higher % of defects than the average. Also Product 30 is somewhat close to the average but product 20 has a much higher % of defects. Hence we will identify Product 20 as the one which has to be improved.

Table 2: EMPLOYEE, NUM DEFECTIVE and % DEFECTIVE

Choose EMPLOYEE in row labels, NUM DEFECTIVE and % DEFECTIVE in sum values. Make sure NUM DEFECTIVE has an attribute of SUM and % DEFECTIVE has an attribute of average.

Employees 2222, 4444 and 5555 have much more higher % defects than the average. In particular employee 4444 has produced the highest % of defectives. The training provided to employee 4444 has to be reviewed and necessary training has to be imparted to improve his productivity

Table 3: BATCHSIZE, NUM DEFECTIVE and % DEFECTIVE

Choose BATCHSIZE in row labels, NUM DEFECTIVE and % DEFECTIVE in sum values. Make sure NUM DEFECTIVE has an attribute of SUM and % DEFECTIVE has an attribute of average.

We find a huge difference in batchsize to % defective. The lower the batch size is the higher is the % defective. We can conclude that it is advisable to produce in larger batches to have lesser number of defectives.

The conclusions are as below

1. Product 20 requires improvement to reduce the num of defectives

2. Employee 4444 needs training to improve his productivity and reduce defective

3. Higher the batch size lesser the number of defectives

*****Please please please LIKE THIS ANSWER, so that I can get a small benefit, Please*****