Company North-East-West-South (NEWS) NEWS is struggling in the ultra-competitive high-tech market. They have called upon you and you to make some key business decisions using the methods and tools recently learned throughout M Save this file for each homework assignment as follows: Last Name_First Name_Homework #.xls For 2-1 Excel Homework I: Scatterplots This homework assignment will help you begin to familiarize yourself with the Excel software, creating gra Create a scatterplot from a given set of data and then create a regression fitted line and determine the co Provide a practical interpretation of the results. 3-2 Excel Homework II: Descriptive Statistics This homework assignment will continue to familiarize you with the Excel software, creating graphs, and u In this assignment, you will create a histogram plot from a given set of data and then determine the mean Provide a practical interpretation of the results. 6-2 Excel Homework III: Amortization Table This homework assignment will continue to familiarize you with the Excel software. In this assignment, you will create an amortization table based on a given principal, interest rate, and pay Analyze alternative criteria to determine the optimal conditions. 7-2 Excel Homework IV: Probability This homework assignment will continue to familiarize you with the Excel software. In this assignment, you will analyze a given business problem based on probability. Provide a practical interpretation of the results. d upon you and your analysis team to help them analyze their data in order NEWS understands the issues that they must overcome in terms of quality, speed, and controllin NEWS believes that the analysis that your team provided in the last three Excel assignments has The NEWS BOD would like to understand the probability of this success before granting permiss Data has been gathered on the last 50 process improvement program (PIP) projects that the NEW The BLUE columns describe whether the PIP was initially approved as a quality, speed, or cost c The GREEN columns describe the quality, speed, and $ results from each project. Regardless of 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 no PIP Quality 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 - Speed Costs 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 - Quality (Defective Free out of 1000) 849 969 375 54 231 294 113 815 75 574 581 476 913 901 461 907 113 836 117 140 486 693 797 640 809 395 67 Speed (Reduction in Days) 18 25 14 25 19 4 18 25 8 16 4 13 15 18 16 16 7 14 11 2 12 21 6 1 14 18 11 Project Cost over 5 Years $ 18.786,00 $ (99.911,00) $ (127.462,00) $ 180.987,00 $ 141.891,00 $ (337.779,00) $ 359.480,00 $ (105.450,00) $ (358.460,00) $ 204.531,00 $ (423.775,00) $ 27.067,00 $ 183.020,00 $ (189.459,00) $ (440.744,00) $ (121.543,00) $ 279.995,00 $ 119.375,00 $ 258.624,00 $ 490.621,00 $ (29.548,00) $ 429.546,00 $ 321.394,00 $ 338.079,00 $ 127.488,00 $ (452.635,00) $ (153.762,00) Results 1 1 1 1 1 1 1 1 1 1 1 1 1 1 - 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 - 318 892 241 672 367 441 312 741 152 421 664 394 342 495 138 497 600 70 328 738 282 905 220 1 22 25 12 1 19 11 7 15 14 18 17 15 6 10 13 8 9 17 11 17 7 5 $ (87.959,00) $ 114.534,00 $ (155.861,00) $ (365.375,00) $ (47.545,00) $ (54.337,00) $ (25.565,00) $ 218.901,00 $ (174.572,00) $ 364.862,00 $ (285.424,00) $ 11.445,00 $ 381.951,00 $ (364.384,00) $ 126.225,00 $ 314.030,00 $ (204.767,00) $ (360.997,00) $ 312.224,00 $ 395.810,00 $ (437.307,00) $ 376.547,00 $ (183.168,00) 1 1 1 1 1 1 1 1 1 Question 1: Using the data from Task 1. The BOD would like to know the percentage of PIP projec percentage that began as an effort to overcome quality, speed, and cost issues. Also, briefly discu success or failure Results. Answer: Question 2: Using the data given below, complete Task 2. NEWS is very proud of their PIP initiati concerned about this next process improvement project decision. It is truly a make or break initia criteria change the rate of success of past PIP initiatives? Given this probability of success, what Question 2: Using the data given below, complete Task 2. NEWS is very proud of their PIP initiati concerned about this next process improvement project decision. It is truly a make or break initia criteria change the rate of success of past PIP initiatives? Given this probability of success, what Answer: y, speed, and controlling costs. Excel assignments has led to successful strategic plans. efore granting permission for the CEO to execute the plan. ) projects that the NEWS BOD had approved. uality, speed, or cost control project, or combination. (1 = Yes) project. Regardless of how the PIP was initially chosen, the positive or negative ject. P was successful or not; old BOD criteria were confidential. (1 = Yes) Task 1A: Calculate Count (1-3) Step 1: Use the =COUNTIF function in the hightlighted Count colunm below to determine the n Step 2: Enter the following cell ranges for Quality (B13:B62), Speed (C13:C62), and Costs (D13 Step 3: Use a Criteria of "1" Step 4: Ranges and Criteria must be entered correctly for the function to work. Please refer to Guide Tab for help on the correct format that should be used. Task 1B: Calculate Count (4-7) Step 5: Use the =COUNTIFS function in the hightlighted Count colunm below to determine the Step 6: Depending on the combinations you are solving for, use the following cell ranges for Q (C13:C62), or Costs (D13:D62). Step 7: Use a Criteria of "1" Step 8: Ranges and Criteria must be entered correctly for the function to work. Please refer to Guide Tab for help on the correct format that should be used. Task 1C: Calculate PIP Percentage Step 9: Use the PIP Percentage colunm to divide each value you generated for the Count colu Task 1D: Calculate PIP Success Step 10: Use the =COUNTIFS function within the PIP Success colunm to determine the numbe Step 11: Depending on the combinations you are solving for, use the following cell ranges for Speed (C13:C62), or Costs (D13:D62), as well as, Results (H13:H62). Step 12: Then, divide the =COUNTIFS function by 50. Projects Quality (1) Speed (2) Count PIP Percentage Costs (3) Quality & Speed (4) Quality & Costs (5) Speed & Costs (6) Quality, Speed, Costs (7) Task 2: Calculate PIP Success (Old) Step 1: Use the =COUNTIF function in the highlighted cell below to determine the number of " Step 2: Enter the following cell ranges for Results (H13:H62). Step 3: Use a Criteria of "1" Step 4: Divide by 50. Step 5: Ranges and Criteria must be entered correctly for the function to work. Please refer to Guide Tab for help on the correct format that should be used. PIP Success (OLD) = Task 3: Calculate Basic Probability Using new BOD Success Criteria Quality Speed Costs Success Criteria > 300 Defective Free per 1000 > 15 Days Reduced < $ 200,000 per 5 Years 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), Spee (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 Guide Tab for help on the correct format that should be used. PIP Success (NEW) = percentage of PIP projects completed per each category, since their short-term memory has hindered their ab sues. Also, briefly discuss the PIP success rate attributable to each type of PIP effort based on the BOD's co roud of their PIP initiative and has briefed the press that their success rate is greater than 50%. Is this true? y a make or break initiative for the company, and therefore a more conservative set of success criteria has b ability of success, what recommendation would you make to the BOD? roud of their PIP initiative and has briefed the press that their success rate is greater than 50%. Is this true? y a make or break initiative for the company, and therefore a more conservative set of success criteria has b ability of success, what recommendation would you make to the BOD? colunm below to determine the number of projects 1-3. Speed (C13:C62), and Costs (D13:D62). function to work. Please refer to the COUNTIF Functions t colunm below to determine the number of projects 4-7. se the following cell ranges for Quality (B13:B62), Speed function to work. Please refer to the COUNTIF Functions ou generated for the Count colunm by 50 to determine % of each. colunm to determine the number of project(s) that the NEWS BOD found successful. use the following cell ranges for Quality (B13:B62), :H62). PIP Success ow to determine the number of "1s" in the Results colunm. function to work. Please refer to the COUNTIF Functions Criteria IP success. ree out of 1000) (E13:E62), Speed (Reduction in Days) r each project. function to work. Please refer to the COUNTIF Functions rm memory has hindered their ability to remember the PIP effort based on the BOD's confidential criteria shown only as s greater than 50%. Is this true? Explain. The BOD is very tive set of success criteria has been provided. Does the new s greater than 50%. Is this true? Explain. The BOD is very tive set of success criteria has been provided. Does the new COUNTIF Purpose Syntax This function will return count cells that meet a single criteria. =COUNTIF(range, criteria) Quality Speed 1 1 1 1 1 Costs 1 1 1 1 1 1 1 1 1 1 1 1 COUNTIFS Purpose Syntax This function will return the count cells that meet one or more criteria =COUNTIFS(range1, criteria1, range2, criteria2,…) Quality Speed 1 1 1 1 1 COSTS 1 1 1 1 1 1 1 Quality Speed (Defective Free (Reduction Out of 1000) in Days 901 10 920 5 75 22 350 16 250 18 180 3 430 11 640 20 115 11 746 17 1 1 1 1 1 $ $ $ $ $ $ $ $ $ $ Project Cost over 5 years 140.000,00 28.000,00 275.000,00 55.000,00 95.000,00 110.000,00 250.000,00 190.000,00 101.000,00 72.000,00 urn count cells that meet a single criteria. Examples Count of Quality Count of Speed Range B8:B17 C8:C17 Criteria "1" "1" Although columns B, C, and D are adjacent to one another, the COUNTIF function only performs a COUNT on the range in a single column. urn the count cells that meet one or more criteria 1, criteria1, range2, criteria2,…) Examples Count of Quality and Speed Count of Quality, Speed, and Costs Range1 B25:B34 B25:B34 Criteria1 "1" "1" The COUNTIFS function uses one-to-many range and criteria sets. In this example, two range and criteria sets were used. Examples Count the number of projects that cost less than $100,000 over 5 years Count the number of projects where Quality > 200 Defective Free per 1000, Speed > 12, Days Reduced, and Costs > $100,000 per 5 years. Range1 D39:D48 B39:B48 Criteria1 "200" The COUNTIFS function uses one-to-many range and criteria sets. In this example, three range and criteria sets were used. COUNT Function 5 =COUNTIF(B8:B17, "1") 7 =COUNTIF(C8:C17, "1") Range2 C25:C34 C25:C34 Criteria2 "1" "1" Range3 Criteria3 D25:D34 "1" COUNT 3 2 Function =COUNTIFS(B25:B34, "1", C25:C34, "1") =COUNTIFS(B25:B34, "1", C25:C34, "1", D25:D34, "1") COUNT 4 1 Function '=COUNTIFS(D39:D48, "200", C39:C48, ">12", D39:D48, ">100000" e and criteria sets were used. Range2 Criteria2 Range3 Criteria3 C39:C48 ">12" E25:E34 ">100000" nge and criteria sets were used. 12", D39:D48, ">100000")
Purchase answer to see full attachment