7 2 excel homework iv probability

Introduction

Company North-East-West-South (NEWS)

NEWS is struggling in the ultra-competitive high-tech market. They have called upon you and your analysis team to help them analyze their data in order

to make some key business decisions using the methods and tools recently learned throughout MBA 501.

Save this file for each homework assignment as follows: Last Name_First Name_Homework #.xls For example, Smith_John_Homework 2_1.xls

2-1 Excel Homework I: Scatterplots

This homework assignment will help you begin to familiarize yourself with the Excel software, creating graphs, and using the Data Analysis add-in feature.

Create a scatterplot from a given set of data and then create a regression fitted line and determine the correlation coefficient.

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 using the Data Analysis add-in feature.

In this assignment, you will create a histogram plot from a given set of data and then determine the mean, median, and standard deviation.

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 payment longevity.

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.

Template - Probability

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?

COUNTIF Functions Guide

COUNTIF

Purpose This function will return count cells that meet a single criteria.

Syntax =COUNTIF(range, criteria)

Quality Speed Costs Examples Range Criteria COUNT Function

1 - 1 Count of Quality B8:B17 "1" 5 =COUNTIF(B8:B17, "1")

- 1 1 Count of Speed C8:C17 "1" 7 =COUNTIF(C8:C17, "1")

- 1 -

1 1 -

- - - Although columns B, C, and D are adjacent to one another, the COUNTIF function

- 1 1 only performs a COUNT on the range in a single column.

1 1 1

1 - -

- 1 -

1 1 1

COUNTIFS

Purpose This function will return the count cells that meet one or more criteria

Syntax =COUNTIFS(range1, criteria1, range2, criteria2,…)

Quality Speed COSTS Examples Range1 Criteria1 Range2 Criteria2 Range3 Criteria3 COUNT Function

1 - 1 Count of Quality and Speed B25:B34 "1" C25:C34 "1" 3 =COUNTIFS(B25:B34, "1", C25:C34, "1")

- 1 1 Count of Quality, Speed, and Costs B25:B34 "1" C25:C34 "1" D25:D34 "1" 2 =COUNTIFS(B25:B34, "1", C25:C34, "1", D25:D34, "1")

- 1 -

1 1 - The COUNTIFS function uses one-to-many range and criteria sets. In this example, two range and criteria sets were used.

- - -

- 1 1

1 1 1

1 - -

- 1 -

1 1 1

Quality Speed Project Examples Range1 Criteria1 Range2 Criteria2 Range3 Criteria3 COUNT Function

(Defective Free (Reduction Cost over Count the number of projects that cost less than $100,000 over 5 years D39:D48 "<100000" 4 '=COUNTIFS(D39:D48, "<100000")

Out of 1000) in Days 5 years Count the number of projects where Quality > 200 Defective Free per 1000, B39:B48 ">200" C39:C48 ">12" E25:E34 ">100000" 1 '=COUNTIFS(B39:B48, ">200", C39:C48, ">12", D39:D48, ">100000")

901 10 $ 140,000.00 Speed > 12, Days Reduced, and Costs > $100,000 per 5 years.

920 5 $ 28,000.00

75 22 $ 275,000.00

350 16 $ 55,000.00 The COUNTIFS function uses one-to-many range and criteria sets. In this example, three range and criteria sets were used.

250 18 $ 95,000.00

180 3 $ 110,000.00

430 11 $ 250,000.00

640 20 $ 190,000.00

115 11 $ 101,000.00

746 17 $ 72,000.00

Applied Sciences

Architecture and Design

Biology

Business & Finance

Chemistry

Computer Science

Geography

Geology

Education

Engineering

English

Environmental science

Spanish

Government

History

Human Resource Management

Information Systems

Law

Literature

Mathematics

Nursing

Physics

Political Science

Psychology

Reading

Science

Social Science

Home

Blog

Archive

Contact

google+twitterfacebook

Copyright © 2019 HomeworkMarket.com