| NEWS understands the issues that they must overcome in terms of quality, speed, and controlling costs. |
| NEWS believes that the analysis that your team provided in the last three Excel assignments has led to successful strategic plans. |
| The NEWS BOD would like to understand the probability of this success before granting permission for the CEO to execute the plan. |
| Data has been gathered on the last 50 process improvement program (PIP) projects that the NEWS BOD had approved. |
| The BLUE columns describe whether the PIP was initially approved as a quality, speed, or cost control project, or combination. (1 = Yes) |
| The GREEN columns describe the quality, speed, and $ results from each project. Regardless of how the PIP was initially chosen, the positive or negative results were gathered in terms of quality, speed and $ at the end of the project. |
| The RED column describes the BOD final determination of whether the PIP was successful or not; old BOD criteria were confidential. (1 = Yes) |
| PIP | Quality | Speed | Costs | Quality (Defective Free out of 1000) | Speed (Reduction in Days) | Project Cost over 5 Years | Results |
| 1 | - 0 | 1 | - 0 | 849 | 18 | $ 18,786.00 | 1 | Task 1A: Calculate Count (1-3) |
| 2 | 1 | 1 | 1 | 969 | 25 | $ (99,911.00) | 1 | Step 1: Use the =COUNTIF function in the hightlighted Count colunm below to determine the number of projects 1-3. |
| 3 | 1 | - 0 | - 0 | 375 | 14 | $ (127,462.00) | 1 | Step 2: Enter the following cell ranges for Quality (B13:B62), Speed (C13:C62), and Costs (D13:D62). |
| 4 | - 0 | 1 | - 0 | 54 | 25 | $ 180,987.00 | - 0 | Step 3: Use a Criteria of "1" |
| 5 | 1 | 1 | 1 | 231 | 19 | $ 141,891.00 | - 0 | Step 4: Ranges and Criteria must be entered correctly for the function to work. Please refer to the COUNTIF Functions Guide Tab for help on the correct format that should be used. |
| 6 | - 0 | 1 | 1 | 294 | 4 | $ (337,779.00) | 1 |
| 7 | - 0 | 1 | 1 | 113 | 18 | $ 359,480.00 | - 0 |
| 8 | - 0 | 1 | 1 | 815 | 25 | $ (105,450.00) | 1 | Task 1B: Calculate Count (4-7) |
| 9 | 1 | - 0 | - 0 | 75 | 8 | $ (358,460.00) | 1 | Step 5: Use the =COUNTIFS function in the hightlighted Count colunm below to determine the number of projects 4-7. |
| 10 | 1 | - 0 | 1 | 574 | 16 | $ 204,531.00 | - 0 | Step 6: Depending on the combinations you are solving for, use the following cell ranges for Quality (B13:B62), Speed (C13:C62), or Costs (D13:D62). |
| 11 | 1 | - 0 | 1 | 581 | 4 | $ (423,775.00) | 1 |
| 12 | 1 | - 0 | 1 | 476 | 13 | $ 27,067.00 | - 0 | Step 7: Use a Criteria of "1" |
| 13 | 1 | 1 | 1 | 913 | 15 | $ 183,020.00 | 1 | Step 8: Ranges and Criteria must be entered correctly for the function to work. Please refer to the COUNTIF Functions Guide Tab for help on the correct format that should be used. |
| 14 | - 0 | 1 | - 0 | 901 | 18 | $ (189,459.00) | 1 |
| 15 | - 0 | 1 | - 0 | 461 | 16 | $ (440,744.00) | 1 |
| 16 | - 0 | - 0 | 1 | 907 | 16 | $ (121,543.00) | 1 | Task 1C: Calculate PIP Percentage |
| 17 | 1 | 1 | 1 | 113 | 7 | $ 279,995.00 | - 0 | Step 9: Use the PIP Percentage colunm to divide each value you generated for the Count colunm by 50 to determine % of each. |
| 18 | 1 | 1 | - 0 | 836 | 14 | $ 119,375.00 | 1 |
| 19 | - 0 | 1 | 1 | 117 | 11 | $ 258,624.00 | - 0 | Task 1D: Calculate PIP Success |
| 20 | 1 | 1 | - 0 | 140 | 2 | $ 490,621.00 | - 0 | Step 10: Use the =COUNTIFS function within the PIP Success colunm to determine the number of project(s) that the NEWS BOD found successful. |
| 21 | 1 | - 0 | - 0 | 486 | 12 | $ (29,548.00) | - 0 | Step 11: Depending on the combinations you are solving for, use the following cell ranges for Quality (B13:B62), Speed (C13:C62), or Costs (D13:D62), as well as, Results (H13:H62). |
| 22 | 1 | 1 | - 0 | 693 | 21 | $ 429,546.00 | - 0 |
| 23 | - 0 | 1 | 1 | 797 | 6 | $ 321,394.00 | - 0 | Step 12: Then, divide the =COUNTIFS function by 50. |
| 24 | 1 | - 0 | 1 | 640 | 1 | $ 338,079.00 | - 0 |
| 25 | 1 | - 0 | 1 | 809 | 14 | $ 127,488.00 | 1 | Projects | Count | PIP Percentage | PIP Success |
| 26 | 1 | - 0 | - 0 | 395 | 18 | $ (452,635.00) | 1 | Quality (1) |
| 27 | - 0 | 1 | - 0 | 67 | 11 | $ (153,762.00) | - 0 | Speed (2) |
| 28 | 1 | 1 | - 0 | 318 | 1 | $ (87,959.00) | - 0 | Costs (3) |
| 29 | 1 | - 0 | - 0 | 892 | 22 | $ 114,534.00 | 1 | Quality & Speed (4) |
| 30 | - 0 | 1 | 1 | 241 | 25 | $ (155,861.00) | - 0 | Quality & Costs (5) |
| 31 | - 0 | 1 | 1 | 672 | 12 | $ (365,375.00) | 1 | Speed & Costs (6) |
| 32 | - 0 | - 0 | 1 | 367 | 1 | $ (47,545.00) | - 0 | Quality, Speed, Costs (7) |
| 33 | 1 | 1 | 1 | 441 | 19 | $ (54,337.00) | - 0 |
| 34 | 1 | 1 | - 0 | 312 | 11 | $ (25,565.00) | - 0 | Task 2: Calculate PIP Success (Old) |
| 35 | - 0 | 1 | - 0 | 741 | 7 | $ 218,901.00 | 1 | Step 1: Use the =COUNTIF function in the highlighted cell below to determine the number of "1s" in the Results colunm. |
| 36 | 1 | - 0 | - 0 | 152 | 15 | $ (174,572.00) | - 0 | Step 2: Enter the following cell ranges for Results (H13:H62). |
| 37 | 1 | - 0 | - 0 | 421 | 14 | $ 364,862.00 | - 0 | Step 3: Use a Criteria of "1" |
| 38 | - 0 | 1 | 1 | 664 | 18 | $ (285,424.00) | 1 | Step 4: Divide by 50. |
| 39 | 1 | - 0 | - 0 | 394 | 17 | $ 11,445.00 | - 0 | Step 5: Ranges and Criteria must be entered correctly for the function to work. Please refer to the COUNTIF Functions Guide Tab for help on the correct format that should be used. |
| 40 | - 0 | 1 | 1 | 342 | 15 | $ 381,951.00 | - 0 |
| 41 | 1 | - 0 | 1 | 495 | 6 | $ (364,384.00) | 1 |
| 42 | - 0 | 1 | 1 | 138 | 10 | $ 126,225.00 | - 0 | PIP Success (OLD) = |
| 43 | - 0 | - 0 | 1 | 497 | 13 | $ 314,030.00 | - 0 |
| 44 | 1 | - 0 | - 0 | 600 | 8 | $ (204,767.00) | 1 | Task 3: Calculate Basic Probability Using new BOD Success Criteria |
| 45 | 1 | - 0 | - 0 | 70 | 9 | $ (360,997.00) | 1 |
| 46 | 1 | 1 | - 0 | 328 | 17 | $ 312,224.00 | - 0 | Success Criteria |
| 47 | 1 | - 0 | - 0 | 738 | 11 | $ 395,810.00 | - 0 | Quality | > 300 Defective Free per 1000 |
| 48 | - 0 | 1 | 1 | 282 | 17 | $ (437,307.00) | 1 | Speed | > 15 Days Reduced |
| 49 | - 0 | - 0 | 1 | 905 | 7 | $ 376,547.00 | - 0 | Costs | < $ 200,000 per 5 Years |
| 50 | 1 | 1 | - 0 | 220 | 5 | $ (183,168.00) | 1 |
| STEP 1: Use the =COUNTIFS to determine the probability of PIP success. |
| Step 2: Enter the following cell ranges for Quality (Defective Free out of 1000) (E13:E62), Speed (Reduction in Days) (F13:F62), and Projects Cost over 5 Years (G13:G62). |
| Step 3: Use the table above to enter the appropiate Criteria for each project. |
| Step 4: Then, divide the entire =COUNTIFS function by 50. |
| Step 5: Ranges and Criteria must be entered correctly for the function to work. Please refer to the COUNTIF Functions Guide Tab for help on the correct format that should be used. |
| PIP Success (NEW) = |
| Question 1: Using the data from Task 1. The BOD would like to know the percentage of PIP projects completed per each category, since their short-term memory has hindered their ability to remember the percentage that began as an effort to overcome quality, speed, and cost issues. Also, briefly discuss the PIP success rate attributable to each type of PIP effort based on the BOD's confidential criteria shown only as success or failure Results. |
| Question 2: Using the data given below, complete Task 2. NEWS is very proud of their PIP initiative and has briefed the press that their success rate is greater than 50%. Is this true? Explain. The BOD is very concerned about this next process improvement project decision. It is truly a make or break initiative for the company, and therefore a more conservative set of success criteria has been provided. Does the new criteria change the rate of success of past PIP initiatives? Given this probability of success, what recommendation would you make to the BOD? |
mfreeman321
Mba501_excel_template-7-2_tw5.xlsx
