Question

Please answer each question set on a separate Excel worksheet, labeled appropriately and include excel Formulas

Question Set 1.

You are in charge of quality control for computer monitors at Dell. You have data on twenty-five batches of monitors, tracking five types of defects: brightness, color, contrast, dead pixels, and stuck pixels. These data are given in the table below.

1. For each defect type, find the average number of defects per batch. So, you should have an average defect rate for brightness, another for color, and so on. (2pts)

2. For each batch, find the total number of defects (the sum of all five types). So, you should have one number for batch 1, another for batch 2, and so on. (2pts)

3. Sort the five columns of defects by descending average defect rate. The lowest rate should be on the right. (4pts)

4. Sort the batches by ascending total defects. The batch with the lowest total should be at the top. This will not affect the column sorting from the question 1.3 above. (4pts)

5. Create a Pareto chart showing the average defect rate for each of the five defect types. This will be a column chart, in descending left-to-right order, with each column and the axes labeled. (8pts)

	Defects
Batch	Brightness	Color	Contrast	Dead Pixels	Stuck Pixels
1	2	0	0	0	0
2	2	1	0	0	0
3	3	0	0	0	1
4	0	0	1	0	2
5	0	3	0	1	2
6	0	2	0	0	0
7	6	2	0	1	2
8	2	0	0	0	0
9	1	0	0	0	3
10	0	0	0	0	0
11	1	0	0	1	3
12	2	0	1	2	1
13	0	1	0	1	0
14	1	0	0	0	1
15	0	3	0	0	5
16	3	0	1	1	0
17	0	5	0	0	3
18	2	0	0	1	0
19	0	4	1	0	2
20	0	0	0	1	0
21	0	0	1	2	0
22	0	3	1	0	2
23	1	1	1	0	6
24	4	0	0	2	0
25	1	0	0	0	3

Question Set 2.

A manufacturing operation must periodically purchase bulk quantities of washers. The washers are purchased in boxes of 1000 and are consumed at a constant rate. The operation expects to purchase 20,000 boxes over the coming year. Each box costs $120, the annual holding cost per box is $15, and the cost of placing an order is $120 (regardless of the quantity ordered). For the following questions, use the basic economic order quantity model (without quantity discounts).

1. What is the economic order quantity (in boxes)? (2pts)

2. Calculate the annual inventory holding costs based on the average inventory level and annual holding cost per box. (2pts)

3. Calculate the annual inventory ordering costs based on the number of orders expected to be placed during the coming year. (2pts)

4. Create a data table showing the total inventory costs (only) for order quantities varying from 100 to 1200 (use a step size of 50). You must use a data table structure to receive full credit for this problem. If you have any doubt as to what a data table is, please ask your lab TA. (8pts)

5. Create a scatter chart (use the one with markers and smooth lines) showing how total inventory costs are a function of the order quantity. Be sure to label your axes appropriately. (6pts)

Answer 1

Excel sheet

1.

Batch	Brightness	Color	Contrast	Dead Pixels	Stuck Pixels
1	2	0	0	0	0
2	2	1	0	0	0
3	3	0	0	0	1
4	0	0	1	0	2
5	0	3	0	1	2
6	0	2	0	0	0
7	6	2	0	1	2
8	2	0	0	0	0
9	1	0	0	0	3
10	0	0	0	0	0
11	1	0	0	1	3
12	2	0	1	2	1
13	0	1	0	1	0
14	1	0	0	0	1
15	0	3	0	0	5
16	3	0	1	1	0
17	0	5	0	0	3
18	2	0	0	1	0
19	0	4	1	0	2
20	0	0	0	1	0
21	0	0	1	2	0
22	0	3	1	0	2
23	1	1	1	0	6
24	4	0	0	2	0
25	1	0	0	0	3
Avergae defects	=AVERAGE(D6:D30)	=AVERAGE(E6:E30)	=AVERAGE(F6:F30)	=AVERAGE(G6:G30)	=AVERAGE(H6:H30)

2.

Batch	Brightness	Color	Contrast	Dead Pixels	Stuck Pixels	Total No of defects
1	2	0	0	0	0	=SUM(D6:H6)
2	2	1	0	0	0	=SUM(D7:H7)
3	3	0	0	0	1	=SUM(D8:H8)
4	0	0	1	0	2	=SUM(D9:H9)
5	0	3	0	1	2	=SUM(D10:H10)
6	0	2	0	0	0	=SUM(D11:H11)
7	6	2	0	1	2	=SUM(D12:H12)
8	2	0	0	0	0	=SUM(D13:H13)
9	1	0	0	0	3	=SUM(D14:H14)
10	0	0	0	0	0	=SUM(D15:H15)
11	1	0	0	1	3	=SUM(D16:H16)
12	2	0	1	2	1	=SUM(D17:H17)
13	0	1	0	1	0	=SUM(D18:H18)
14	1	0	0	0	1	=SUM(D19:H19)
15	0	3	0	0	5	=SUM(D20:H20)
16	3	0	1	1	0	=SUM(D21:H21)
17	0	5	0	0	3	=SUM(D22:H22)
18	2	0	0	1	0	=SUM(D23:H23)
19	0	4	1	0	2	=SUM(D24:H24)
20	0	0	0	1	0	=SUM(D25:H25)
21	0	0	1	2	0	=SUM(D26:H26)
22	0	3	1	0	2	=SUM(D27:H27)
23	1	1	1	0	6	=SUM(D28:H28)
24	4	0	0	2	0	=SUM(D29:H29)
25	1	0	0	0	3	=SUM(D30:H30)

3.

	Stuck Pixels	Brightness	Color	Dead Pixel	Contrast
Sorted Average Defects	1.44	1.24	1	0.52	0.28

Use sort option from largest to smallest for this

3.

Batch	Sorted Batches on Total Defects
10	0
20	1
1	2
6	2
8	2
13	2
14	2
2	3
4	3
18	3
21	3
3	4
9	4
25	4
11	5
16	5
5	6
12	6
22	6
24	6
19	7
15	8
17	8
23	9
7	11

--------------------------------------------------------------------------------------------------------------------------------------------------------

Question 2->

1.Annual Demand (R) = 20000 boxes

Ordering cost (C) = $120/order

Holding cost (H)= $15/box/year

Economic order quantity (Q)= = = 565.68 = 566 boxes

2. Annual holding cost = Q/2 * H = 566/2 * 15= $4245

3. Annual Ordering cost = no of prders * ordering cost = R/Q * C = 20000/ 566 * 120 =$ 4240.28

4. Excel table

		Order quantity	inventory cost
Annual inventory cost	15	100	=D3/2*$C$3
		150	=D4/2*$C$3
		200	=D5/2*$C$3
		250	=D6/2*$C$3
		300	=D7/2*$C$3
		350	=D8/2*$C$3
		400	=D9/2*$C$3
		450	=D10/2*$C$3
		500	=D11/2*$C$3
		550	=D12/2*$C$3
		600	=D13/2*$C$3
		650	=D14/2*$C$3
		700	=D15/2*$C$3
		750	=D16/2*$C$3
		800	=D17/2*$C$3
		850	=D18/2*$C$3
		900	=D19/2*$C$3
		950	=D20/2*$C$3
		1000	=D21/2*$C$3
		1050	=D22/2*$C$3
		1100	=D23/2*$C$3
		1150	=D24/2*$C$3
		1200	=D25/2*$C$3

5.