Please answer each question set on a separate Excel worksheet,labeled appropriately. (Include all formulas)
Question Set 1.
You are in charge of quality control for computer monitors atDell. You have data on twenty-five batches of monitors, trackingfive 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 averagenumber of defects per batch. So, you should have an average defectrate for brightness, another for color, and so on. (2pts)
2. For each batch, find the total number ofdefects (the sum of all five types). So, you should have one numberfor batch 1, another for batch 2, and so on. (2pts)
3. Sort the five columns of defects by descending average defectrate. The lowest rate should be on the right. (4pts)
4. Sort the batches by ascending total defects. The batch withthe lowest total should be at the top. This will not affect thecolumn sorting from the question 1.3 above. (4pts)
5. Create a Pareto chart showing the average defect rate foreach of the five defect types. This will be a column chart, indescending left-to-right order, with each column and the axeslabeled. (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 bulkquantities of washers. The washers are purchased in boxes of 1000and are consumed at a constant rate. The operation expects topurchase 20,000 boxes over the coming year. Each box costs $120,the annual holding cost per box is $15, and the cost of placing anorder is $120 (regardless of the quantity ordered). For thefollowing 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 theaverage inventory level and annual holding cost per box. (2pts)
3. Calculate the annual inventory ordering costs based on thenumber 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 of50). You must use a data table structure to receive full credit forthis 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 smoothlines) showing how total inventory costs are a function of theorder quantity. Be sure to label your axes appropriately. (6p
