Company NorthEastWestSouth (NEWS)
NEWS is struggling in the ultracompetitive hightech 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
21 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.
32 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.
62 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.
72 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 (13)
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 (47)
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 shortterm 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 13.
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 47.
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 onetomany 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 onetomany 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