Task 1: Calculate Basic Probability of each PIP
Task 2: Calculate Basic Probability Using new BOD Success Criteria
Question 1: NEWS would like to compare two different parcels, each of which is tied to a specific lender and the lender's terms.
Parcel GREEN has the following conditions: Cost = $ 10,000,000; Annual Interest Rate = 11%; Loan Term = 25 Years
Parcel BLUE has the following conditions: Cost = $ 7,500,000; Annual Interest Rate = 20%; Loan Term = 15 Years
Provide your recommendation on which option is the best option and why.
Question 2: How does your answer change if NEWS is concerned with short-term profit margins?
How does your answer change if NEWS is concerned with long-term debt, due to a poor book value rating from the most recent stock analysis?
READ ME If you need assistance using Excel, you can access a tutorial that is appropriate for your experience level and your version of Excel. Access these tutorials at Atomic Learning using your SNHU login at:Excel 2016 The Data Analysis ToolPak is an add-in program for Microsoft Excel. It must be added in to the software before it can be used. If you have "DATA" already on the upper main menu, then simply click on it and you will open up a tool bar of assorted new Excel tools, including: Get External Data, Connections, Sort & Filter, Data Tools, Outline, and Analysis. If you do not see "DATA" on the upper main menu, then you must add this program into Excel by doing the following: Click FILE in the upper tool bar, followed by OPTIONS, then select ADD-INS. Next, on the bottom near Manage, select EXCEL ADD-INS and GO. Ensure the ANALYSIS TOOLPAK is checkmarked and click OK. This ToolPak will provide additional data analysis tools for statistics. NOTE: If you are unable to load this ToolPak into your version of Excel, you may have to consult your installation CD and reinstall the Excel set-up The DATA ANALYSIS TOOLPAK provides 18 additional statistical tools in the areas of: Descriptive Statistics; Sampling; Hypothesis Testing; Analysis of Variance; Regression and Correlation; and Time Series Forecasting The ToolPak is valuable to business analysts and leaders who desire additional capability from the Excel software. Excel 2016 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 has provided in the last three Excel modules 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) Question 1: Using the data given below, complete 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. ANSWER: 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? ANSWER: PIPQualitySpeedCostsComboQuality (Defective Free out of 1000)Speed (Reduction in Days)Project Cost over 5 YearsResults 1- 01- 0284918$ 18,786.001 2111796925$ (99,911.00)1Task 1: Calculate Basic Probability of each PIP 31- 0- 0137514$ (127,462.00)1Step 1: Determine the number of Quality, Speed, or Costs projects using the COUNTIF function. 4- 01- 025425$ 180,987.00- 0Step 2: Highlight each BLUE column and use a criterion of 1 5111723119$ 141,891.00- 0Step 3: Divide the COUNTIF value by the total number of PIP projects (which is 50) to determine % of each. 6- 01162944$ (337,779.00)1Step 4: Next, determine the number of combination projects using the COUNTIFS function. 7- 011611318$ 359,480.00- 0Step 5: Again, highlight each BLUE column now in question, and use a criterion of 1; divide value by 50 8- 011681525$ (105,450.00)1Step 6: Find probability of PIP success due to quality, speed, or costs or combination using COUNTIFS and COMBO 1-7; divide value by 50 91- 0- 01758$ (358,460.00)1 101- 01557416$ 204,531.00- 0CountPIP PercentagePIP Success 111- 0155814$ (423,775.00)1Quality (1) 121- 01547613$ 27,067.00- 0Speed (2) 13111791315$ 183,020.001Costs (3) 14- 01- 0290118$ (189,459.00)1Q & S (4) 15- 01- 0246116$ (440,744.00)1Q & C (5) 16- 0- 01390716$ (121,543.00)1S & C (6) 1711171137$ 279,995.00- 0Q, S, C (7) 1811- 0483614$ 119,375.001 19- 011611711$ 258,624.00- 0Task 2: Calculate Basic Probability Using new BOD Success Criteria 2011- 041402$ 490,621.00- 0Find the probability of PIP success in Results using the COUNTIF function; divide value by 50 211- 0- 0148612$ (29,548.00)- 0PIP Success (OLD) = 2211- 0469321$ 429,546.00- 0 23- 01167976$ 321,394.00- 0Given the criticality of this new decision, the BOD has provided conservative PIP success criteria to analyze past projects 241- 0156401$ 338,079.00- 0Quality> 300 Defective Free per 1000 251- 01580914$ 127,488.001Speed> 15 Days Reduced 261- 0- 0139518$ (452,635.00)1Costs< $ 200,000 per 5 years 27- 01- 026711$ (153,762.00)- 0 2811- 043181$ (87,959.00)- 0find probability of pip success using countifs due to new bod quality, speed, or costs criteria. 291- 0- 0189222$ 114,534.001 30- 011624125$ (155,861.00)- 0pip success (new) = 31- 011667212$ (365,375.00)1 32- 0- 0133671$ (47,545.00)- 0 33111744119$ (54,337.00)- 0 3411- 0431211$ (25,565.00)- 0 35- 01- 027417$ 218,901.001 361- 0- 0115215$ (174,572.00)- 0 371- 0- 0142114$ 364,862.00- 0 38- 011666418$ (285,424.00)1 391- 0- 0139417$ 11,445.00- 0 40- 011634215$ 381,951.00- 0 411- 0154956$ (364,384.00)1 42- 011613810$ 126,225.00- 0 43- 0- 01349713$ 314,030.00- 0 441- 0- 016008$ (204,767.00)1 451- 0- 01709$ (360,997.00)1 4611- 0432817$ 312,224.00- 0 471- 0- 0173811$ 395,810.00- 0 48- 011628217$ (437,307.00)1 49- 0- 0139057$ 376,547.00- 0 5011- 042205$ (183,168.00)1 $="" 200,000="" per="" 5="" years="" 27="" -="" 0="" 1="" -="" 0="" 2="" 67="" 11="" $="" (153,762.00)="" -="" 0="" 28="" 1="" 1="" -="" 0="" 4="" 318="" 1="" $="" (87,959.00)="" -="" 0="" find="" probability="" of="" pip="" success="" using="" countifs="" due="" to="" new="" bod="" quality,="" speed,="" or="" costs="" criteria.="" 29="" 1="" -="" 0="" -="" 0="" 1="" 892="" 22="" $="" 114,534.00="" 1="" 30="" -="" 0="" 1="" 1="" 6="" 241="" 25="" $="" (155,861.00)="" -="" 0="" pip="" success="" (new)="31" -="" 0="" 1="" 1="" 6="" 672="" 12="" $="" (365,375.00)="" 1="" 32="" -="" 0="" -="" 0="" 1="" 3="" 367="" 1="" $="" (47,545.00)="" -="" 0="" 33="" 1="" 1="" 1="" 7="" 441="" 19="" $="" (54,337.00)="" -="" 0="" 34="" 1="" 1="" -="" 0="" 4="" 312="" 11="" $="" (25,565.00)="" -="" 0="" 35="" -="" 0="" 1="" -="" 0="" 2="" 741="" 7="" $="" 218,901.00="" 1="" 36="" 1="" -="" 0="" -="" 0="" 1="" 152="" 15="" $="" (174,572.00)="" -="" 0="" 37="" 1="" -="" 0="" -="" 0="" 1="" 421="" 14="" $="" 364,862.00="" -="" 0="" 38="" -="" 0="" 1="" 1="" 6="" 664="" 18="" $="" (285,424.00)="" 1="" 39="" 1="" -="" 0="" -="" 0="" 1="" 394="" 17="" $="" 11,445.00="" -="" 0="" 40="" -="" 0="" 1="" 1="" 6="" 342="" 15="" $="" 381,951.00="" -="" 0="" 41="" 1="" -="" 0="" 1="" 5="" 495="" 6="" $="" (364,384.00)="" 1="" 42="" -="" 0="" 1="" 1="" 6="" 138="" 10="" $="" 126,225.00="" -="" 0="" 43="" -="" 0="" -="" 0="" 1="" 3="" 497="" 13="" $="" 314,030.00="" -="" 0="" 44="" 1="" -="" 0="" -="" 0="" 1="" 600="" 8="" $="" (204,767.00)="" 1="" 45="" 1="" -="" 0="" -="" 0="" 1="" 70="" 9="" $="" (360,997.00)="" 1="" 46="" 1="" 1="" -="" 0="" 4="" 328="" 17="" $="" 312,224.00="" -="" 0="" 47="" 1="" -="" 0="" -="" 0="" 1="" 738="" 11="" $="" 395,810.00="" -="" 0="" 48="" -="" 0="" 1="" 1="" 6="" 282="" 17="" $="" (437,307.00)="" 1="" 49="" -="" 0="" -="" 0="" 1="" 3="" 905="" 7="" $="" 376,547.00="" -="" 0="" 50="" 1="" 1="" -="" 0="" 4="" 220="" 5="" $="" (183,168.00)="">