Question

In: Operations Management

PLEASE SHOW ALL THE FORMULA IN AN EXCEL FORMAT AND ANSWER ALL QUESTIONS All problems must...

PLEASE SHOW ALL THE FORMULA IN AN EXCEL FORMAT AND ANSWER ALL QUESTIONS

All problems must be solved using Excel formulas to receive credit.

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)

Solutions

Expert Solution

Question Set 1

1) Average number of defects is obtained by calculating the average of each column pertaining to each defect type.

The average number of defects per batch is calculated in row 28 of the above excel sheet

2) The sum of defects for each batch is calculated by adding the five defect types for each batch as shown below

3) The columns are sorted as shown below in descending order of average defect rate.

4) The batches are sorted in ascending order of total defects as below

5) Data in row 2 and 28 is used to construct the Pareto chart


Related Solutions

Show in excel please with correct formula Solve all the problems using after tax cash flow...
Show in excel please with correct formula Solve all the problems using after tax cash flow analysis, even the book ask in some of them to use BTCF The Container Corporation of America is considering replacing an automatic painting machine purchased 9 years ago for $700,000. It has a market value today of $40,000. The unit costs $350,000 annually to operate and maintain. A new unit can be purchased for $800,000 and will have annualO&Mcosts of $120,000. If the old...
Complete the following problems using R. PLEASE SHOW ANSWER IN R FORMAT and be sure to...
Complete the following problems using R. PLEASE SHOW ANSWER IN R FORMAT and be sure to turn in explanations and interpretations where appropriate. 2.A random sample of 15 values of PAR (photosynthetically active radiation) were taken at noon in a certain forest (in moles per meter2per second). Data are included in the assignment .xlsx file. Assume that PAR is approximately normally distributed with a known standard deviation of 40.71 moles/(m2s). a.What is the mean value observed for PAR? b.What is...
Answer the following problems in an Excel file. Please upload only one Excel file with all...
Answer the following problems in an Excel file. Please upload only one Excel file with all of your answers, including #3 (which requires an explanation rather than a calculation). All problems must be solved using the PV and FV functions in Excel. If I deposit $8,000 in a bank account that pays interest of 1.5%, compounded annually, how much will I have in the account after 10 years? If I deposit $8,000 in a bank account that pays simple interest...
Answer using excel and please provide excel instructions for all outputs meaning what formula was used...
Answer using excel and please provide excel instructions for all outputs meaning what formula was used to calculate the output and how was it implemented using excel and which excel functions to use. Thank you. Conch Republic Electronics is a midsized electronics manufacturer located in Key West, Florida. The company president is Shelly Couts, who inherited the company. The company originally repaired radios and other household appliances when it was founded over 70 years ago. Over the years, the company...
****ANSWERS ARE GIVEN BUT MUST SHOW ALL WORK; PLEASE (NO EXCEL OR SPSS)***** ---------------------------------------------------------------------- 1. To...
****ANSWERS ARE GIVEN BUT MUST SHOW ALL WORK; PLEASE (NO EXCEL OR SPSS)***** ---------------------------------------------------------------------- 1. To answer this question, refer to the following hypothetical data collected using replicated measures design. Subject 1 2 3    4 5 6 7 8    9 10 Pre 50 49 37 16 80 42 40 58 31 21 Post 56 50 30 25 90 44 60 71 32 22 A. In a two-tailed test of H 0 using α=0.05, what is p(obtained) for the...
Please show all work and formula: Please use the information on the table below to answer...
Please show all work and formula: Please use the information on the table below to answer this question. Security                       Actual Return             Beta A                                 12%                             1.2 B                                  10%                             1.0 C                                  14%                             1.4 Assume the risk-free interest rate is 1% and the market risk premium is 5.5%. An investor would like to invest $40,000 in Security A, $25,000 in security B and $50,000 in Security C. Find the portfolio’s expected return. Find the portfolio’s actual return. Based on your answers to a...
Please answer these questions using SPSS. Thank you Note: For all assignments, you must show the...
Please answer these questions using SPSS. Thank you Note: For all assignments, you must show the requested output from SPSS. Example. Determine the descriptive statistics for three quantitative variables. Which variable has the highest mean? The most variability? Answer: Output should include 3 boxes of descriptive statistics, one for each variable. There should also be one page that gives the answer to the other two questions. The following sample data are used. We are interested in the descriptive statistics from...
PLEASE ANSWER ALL QUESTIONS PLEASE. 1A.) What is the forward price (formula) for an asset that...
PLEASE ANSWER ALL QUESTIONS PLEASE. 1A.) What is the forward price (formula) for an asset that provides no income using the parameters of S0 , risk-free rate r (annual rate with continuous compounding), and time to maturity of T? Using this formula, if the spot market price of gold is given as 1715 per ounce, the risk-free rate is 1% per annum with continuous compounding, and two years to maturity, what is the fair price for a two-year forward contract?...
Please answer the following questions in essay format. Must be at least one full page of...
Please answer the following questions in essay format. Must be at least one full page of written work (excluding quotes/references), Times New Roman, 12 point font, double-spaced; and cited in the proper APA format.         Reflecting on what you have learned in this class, what do you think are 5 challenges that corporate and executive level leadership may come across, as it relates to diversity policy and procedures in an organization?         Define “Diversity Audit”. What does a diversity audit entail? How...
Please use ONLY one Excel file to answer the below questions. ( including the formula using...
Please use ONLY one Excel file to answer the below questions. ( including the formula using for Excel) In 2011, when the Gallup organization polled investors, 34% rated gold the best long-term investment. In April of 2013 Gallup surveyed a random sample of U.S. adults. Respondents were asked to select the best long-term investment from a list of possibilities. Only 241 of the 1005 respondents chose gold as the best long-term investment. By contrast, only 91 chose bonds. Compute the...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT