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: Mastering Excel 2013 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, inclu 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 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. s, including: e Excel Set-up 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 Use the same file for each consecutive homework assignment, simply saving the new homework with a new name. For example, Smith_John_Homework 3_2.xls 2-1 Excel Homework I: Scatter Plots 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 scatter plot from a given set of data, 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. NEWS has gathered data over the last 52 weeks. Two of the data items that have been gathered are Profit and the Number of Defective Items. Question 1: Using the data given below, complete Task 1 and provide a very brief, general description of whether or not a relationship exists between Profit and the Number of Defective Items. ANSWER: Question 2: Using the data given below, complete Task 2 and provide a statistical description of whether or not a relationship appears to exist between Profit and the Number of Defective Items. ANSWER: Week 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 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 Profit (thousands) $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 35.00 490.00 777.00 922.00 519.00 520.00 899.00 391.00 577.00 419.00 667.00 399.00 540.00 954.00 1,078.00 563.00 619.00 625.00 351.00 674.00 547.00 578.00 609.00 228.00 871.00 188.00 632.00 442.00 442.00 1,114.00 864.00 825.00 750.00 615.00 445.00 282.00 409.00 637.00 646.00 999.00 232.00 152.00 874.00 981.00 289.00 771.00 806.00 921.00 150.00 113.00 1,084.00 350.00 Number of Defective Items 974 693 248 277 509 635 200 743 563 715 397 720 659 123 8 444 464 483 715 444 639 503 565 785 286 842 480 721 571 25 272 241 252 500 674 732 701 401 536 156 824 964 212 218 747 356 303 113 883 910 85 745 Task 1: Create a Scatter Plot Highlight the two columns of data (Profit, Defective Units) and click the Quick Analysis icon on the bottom right Select Charts and Scatter Place the Chart below this row Task 2: Correlation and Regression Fitted Line Place your mouse over any point within your Scatter Plot above and right click. Then select Add Trendline. Select Linear, then scroll down and Display Equation and R squared Value on Chart Place the values in a visible area of the chart so that they are legible and not covered by any of the data Determine the Correlation Coefficient (R), using the CORREL function and highlighting each column (Profit, Defective). CORRELATION COEFFICIENT = Check the Correlation Coefficient (R), by taking the square root (SQRT) of the R squared value in the chart above. Determine the sign (+ or -) of R based on the direction of the regression line. CORRELATION COEFFICIENT = NEWS customers require a delivery time of 30 days at their door from the date that the order was received. NEWS has provided data of their last 50 shipments: Duration = Delivery Date at the Door - Date Order was Received Question 1: Using the data given below, complete Task 1 and provide a very brief, general description of whether or not you believe NEWS is effectively satisfying their customer's desired shipment date. ANSWER: Question 2: Using the data given below, complete Task 2 and provide a brief statistical description of how the results relate to the customer's requirements. NOTE: A statistically significant answer to the question requires a hypothesis test using inferential statistics, which is not required. ANSWER: Order 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 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 Duration (Calendar Days) 11 30 19 35 13 39 4 23 34 37 6 24 9 26 35 25 5 18 14 29 20 25 21 24 22 13 14 28 26 34 18 40 15 18 29 26 23 36 42 33 15 22 39 22 21 14 16 44 18 22 Task 1: Create a Histogram Select DATA from the tool bar above and then the DATA ANAYSIS TOOL icon on the far right. Select HISTOGRAM and in the Input Range highlight the column of DURATION Days. Leave the Bin Blank (Excel will determine this for you). In "New Worksheet Ply" type Histogram Bins Excel creates a new Worksheet named Histogram Bins with a count of each data point per Bin In the Histogram Bin worksheet highlight the data and then select the Quick Analysis icon. Select Charts. And select Clustered Vertical Bars and then repeat and select Lines. The first provides a Histogram; the second provides a Frequency Plot Place the Histogram and Frequency Plot side by side below this row Task 2: Descriptive Statistics Select DATA from the tool bar above and then the DATA ANAYSIS TOOL icon on the far right. Select DESCRIPTIVE STATISTICS and in the Input Range highlight the column of DURATION Days, Grouped by Columns In "New Worksheet Ply" type Descriptive Statistics and Excel creates a new Worksheet containing the calculations Check the box SUMMARY STATISTICS. Open the new worksheet and observe: Mean, Median and Standard Deviation MEAN = MEDIAN = STD DEV = NEWS has decided to purchase a new parcel of land in order to expand operations into a brand-new facility and retake the competitive advantage. The Amortization Schedule below has been generated for your use in this assignment and in future professional/personal endeavors. NOTE: Payment is calculated by using the PMT function in Excel, based on a given interest rate, total periods, present value borrowed (-), and future value 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. ANSWER: I believe Parcel Blue would be the best option because: if they can afford the higher monthly payments it allows for the loan to be paid off quicker in order to not have long term debt. Although it has a higher interest rate, since the loan term is shorter, more of the payment goes towards the Principal than the Interest in opposed to Parcel Green. 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 analyst? ANSWER: If NEWS was concerned with short term profit margins (and having a longer loan period wasn't an issue) then they should go with Parcel Green that spreads out smaller payments over a longer span of time, this would enable them to keep more money in their pockets each month/year in oppose to having high monthly loan payments. If NEWS doesn't want long term debt then they should go with Parcel BLUE in order to have a loan with the shorter life span. Amount Borrowed (Present Value) Future Value Amount Borrowed (Present Value) $ 10,000,000.00 $ Future Value 25 Years Payments per Year $ 7,500,000.00 $ 15 Years Payments per Year 12 12 Total Periods 300 Total Periods 180 Annual Interest Rate 11.00% Annual Interest Rate 20.00% Compound Rate 0.92% Compound Rate 1.67% Payment Amount Payment Number 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 28 29 30 31 32 33 34 35 36 37 38 39 40 Payment Amount $98,011.31 Payment $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 Interest $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 91,666.67 91,608.51 91,549.82 91,490.58 91,430.81 91,370.49 91,309.62 91,248.18 91,186.19 91,123.63 91,060.49 90,996.77 90,932.47 90,867.58 90,802.10 90,736.01 90,669.32 90,602.02 90,534.10 90,465.56 90,396.39 90,326.59 90,256.15 90,185.06 90,113.32 90,040.92 89,967.86 89,894.13 89,819.72 89,744.63 89,668.85 89,592.38 89,515.20 89,437.32 89,358.73 89,279.41 89,199.37 89,118.60 89,037.08 88,954.81 Principal $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 6,344.64 6,402.80 6,461.49 6,520.72 6,580.50 6,640.82 6,701.69 6,763.12 6,825.12 6,887.68 6,950.82 7,014.54 7,078.84 7,143.72 7,209.21 7,275.29 7,341.98 7,409.29 7,477.20 7,545.74 7,614.91 7,684.72 7,755.16 7,826.25 7,897.99 7,970.39 8,043.45 8,117.18 8,191.59 8,266.68 8,342.46 8,418.93 8,496.10 8,573.98 8,652.58 8,731.89 8,811.94 8,892.71 8,974.23 9,056.49 Balance $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 9,993,655.36 9,987,252.56 9,980,791.07 9,974,270.34 9,967,689.85 9,961,049.03 9,954,347.34 9,947,584.21 9,940,759.10 9,933,871.41 9,926,920.59 9,919,906.06 9,912,827.22 9,905,683.50 9,898,474.29 9,891,198.99 9,883,857.01 9,876,447.73 9,868,970.52 9,861,424.78 9,853,809.86 9,846,125.15 9,838,369.99 9,830,543.74 9,822,645.75 9,814,675.36 9,806,631.91 9,798,514.73 9,790,323.14 9,782,056.46 9,773,714.00 9,765,295.07 9,756,798.97 9,748,224.98 9,739,572.41 9,730,840.51 9,722,028.58 9,713,135.86 9,704,161.63 9,695,105.14 $131,722.24 Payment Number Payment 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 28 29 30 31 32 33 34 35 36 37 38 39 40 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 Interest $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ Principal 125,000.00 124,887.96 124,774.06 124,658.26 124,540.52 124,420.83 124,299.14 124,175.42 124,049.64 123,921.76 123,791.75 123,659.58 123,525.20 123,388.58 123,249.69 123,108.48 122,964.92 122,818.96 122,670.57 122,519.71 122,366.34 122,210.41 122,051.88 121,890.70 121,726.84 121,560.25 121,390.89 121,218.70 121,043.64 120,865.66 120,684.72 120,500.76 120,313.74 120,123.60 119,930.28 119,733.75 119,533.94 119,330.81 119,124.28 118,914.32 $6,722.24 $6,834.28 $6,948.18 $7,063.98 $7,181.72 $7,301.41 $7,423.10 $7,546.82 $7,672.60 $7,800.48 $7,930.48 $8,062.66 $8,197.04 $8,333.65 $8,472.55 $8,613.76 $8,757.32 $8,903.28 $9,051.66 $9,202.52 $9,355.90 $9,511.83 $9,670.36 $9,831.53 $9,995.39 $10,161.98 $10,331.35 $10,503.54 $10,678.60 $10,856.57 $11,037.52 $11,221.48 $11,408.50 $11,598.64 $11,791.95 $11,988.49 $12,188.29 $12,391.43 $12,597.96 $12,807.92 Balance $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 7,493,277.76 7,486,443.49 7,479,495.31 7,472,431.33 7,465,249.61 7,457,948.20 7,450,525.10 7,442,978.28 7,435,305.68 7,427,505.20 7,419,574.72 7,411,512.06 7,403,315.02 7,394,981.37 7,386,508.82 7,377,895.06 7,369,137.74 7,360,234.47 7,351,182.80 7,341,980.28 7,332,624.38 7,323,112.55 7,313,442.19 7,303,610.65 7,293,615.26 7,283,453.27 7,273,121.92 7,262,618.39 7,251,939.79 7,241,083.21 7,230,045.70 7,218,824.22 7,207,415.72 7,195,817.08 7,184,025.12 7,172,036.64 7,159,848.34 7,147,456.91 7,134,858.96 7,122,051.03 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $98,011.31 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 88,871.80 88,788.02 88,703.47 88,618.15 88,532.05 88,445.15 88,357.46 88,268.97 88,179.66 88,089.54 87,998.59 87,906.81 87,814.18 87,720.71 87,626.38 87,531.18 87,435.12 87,338.17 87,240.33 87,141.60 87,041.96 86,941.41 86,839.93 86,737.53 86,634.18 86,529.89 86,424.65 86,318.44 86,211.25 86,103.08 85,993.93 85,883.77 85,772.60 85,660.41 85,547.19 85,432.94 85,317.64 85,201.28 85,083.85 84,965.35 84,845.76 84,725.08 84,603.29 84,480.38 84,356.35 84,231.18 84,104.86 83,977.38 83,848.74 83,718.92 83,587.90 83,455.69 83,322.26 83,187.61 83,051.73 82,914.60 82,776.21 82,636.56 82,495.62 82,353.39 82,209.86 82,065.02 81,918.84 81,771.33 81,622.46 81,472.23 81,320.62 81,167.63 81,013.22 80,857.41 80,700.17 80,541.48 80,381.34 80,219.73 80,056.64 79,892.06 79,725.96 79,558.35 79,389.20 79,218.49 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 9,139.51 9,223.29 9,307.84 9,393.16 9,479.26 9,566.16 9,653.84 9,742.34 9,831.64 9,921.77 10,012.72 10,104.50 10,197.12 10,290.60 10,384.93 10,480.12 10,576.19 10,673.14 10,770.98 10,869.71 10,969.35 11,069.90 11,171.38 11,273.78 11,377.12 11,481.41 11,586.66 11,692.87 11,800.06 11,908.22 12,017.38 12,127.54 12,238.71 12,350.90 12,464.11 12,578.37 12,693.67 12,810.03 12,927.45 13,045.96 13,165.54 13,286.23 13,408.02 13,530.93 13,654.96 13,780.13 13,906.45 14,033.92 14,162.57 14,292.39 14,423.40 14,555.62 14,689.05 14,823.70 14,959.58 15,096.71 15,235.10 15,374.75 15,515.69 15,657.91 15,801.44 15,946.29 16,092.46 16,239.98 16,388.85 16,539.08 16,690.68 16,843.68 16,998.08 17,153.90 17,311.14 17,469.83 17,629.97 17,791.58 17,954.67 18,119.25 18,285.34 18,452.96 18,622.11 18,792.81 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 9,685,965.63 9,676,742.34 9,667,434.51 9,658,041.35 9,648,562.09 9,638,995.93 9,629,342.09 9,619,599.75 9,609,768.10 9,599,846.34 9,589,833.62 9,579,729.12 9,569,532.00 9,559,241.40 9,548,856.47 9,538,376.35 9,527,800.16 9,517,127.02 9,506,356.04 9,495,486.33 9,484,516.98 9,473,447.08 9,462,275.70 9,451,001.92 9,439,624.80 9,428,143.38 9,416,556.72 9,404,863.85 9,393,063.80 9,381,155.57 9,369,138.19 9,357,010.65 9,344,771.94 9,332,421.04 9,319,956.93 9,307,378.56 9,294,684.89 9,281,874.86 9,268,947.40 9,255,901.45 9,242,735.90 9,229,449.67 9,216,041.66 9,202,510.73 9,188,855.77 9,175,075.64 9,161,169.19 9,147,135.27 9,132,972.70 9,118,680.31 9,104,256.91 9,089,701.29 9,075,012.24 9,060,188.55 9,045,228.97 9,030,132.26 9,014,897.16 8,999,522.41 8,984,006.73 8,968,348.81 8,952,547.37 8,936,601.08 8,920,508.61 8,904,268.64 8,887,879.79 8,871,340.71 8,854,650.03 8,837,806.35 8,820,808.26 8,803,654.37 8,786,343.22 8,768,873.40 8,751,243.43 8,733,451.85 8,715,497.19 8,697,377.94 8,679,092.59 8,660,639.63 8,642,017.52 8,623,224.71 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $131,722.24 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 118,700.85 118,483.83 118,263.19 118,038.87 117,810.81 117,578.96 117,343.24 117,103.59 116,859.94 116,612.24 116,360.40 116,104.37 115,844.07 115,579.44 115,310.39 115,036.86 114,758.77 114,476.05 114,188.61 113,896.38 113,599.29 113,297.24 112,990.15 112,677.95 112,360.55 112,037.85 111,709.78 111,376.24 111,037.14 110,692.39 110,341.89 109,985.55 109,623.27 109,254.96 108,880.50 108,499.80 108,112.76 107,719.27 107,319.22 106,912.51 106,499.01 106,078.62 105,651.23 105,216.71 104,774.96 104,325.83 103,869.23 103,405.01 102,933.06 102,453.24 101,965.42 101,469.47 100,965.26 100,452.64 99,931.48 99,401.64 98,862.96 98,315.31 97,758.52 97,192.46 96,616.97 96,031.88 95,437.04 94,832.29 94,217.45 93,592.37 92,956.88 92,310.79 91,653.93 90,986.12 90,307.19 89,616.94 88,915.18 88,201.73 87,476.39 86,738.96 85,989.24 85,227.02 84,452.10 83,664.27 $13,021.39 $13,238.41 $13,459.05 $13,683.37 $13,911.42 $14,143.28 $14,379.00 $14,618.65 $14,862.30 $15,110.00 $15,361.84 $15,617.87 $15,878.16 $16,142.80 $16,411.85 $16,685.38 $16,963.47 $17,246.19 $17,533.63 $17,825.85 $18,122.95 $18,425.00 $18,732.08 $19,044.29 $19,361.69 $19,684.39 $20,012.46 $20,346.00 $20,685.10 $21,029.85 $21,380.35 $21,736.69 $22,098.97 $22,467.28 $22,841.74 $23,222.43 $23,609.47 $24,002.96 $24,403.01 $24,809.73 $25,223.23 $25,643.61 $26,071.01 $26,505.52 $26,947.28 $27,396.40 $27,853.01 $28,317.23 $28,789.18 $29,269.00 $29,756.82 $30,252.76 $30,756.98 $31,269.59 $31,790.75 $32,320.60 $32,859.28 $33,406.93 $33,963.71 $34,529.77 $35,105.27 $35,690.36 $36,285.20 $36,889.95 $37,504.78 $38,129.86 $38,765.36 $39,411.45 $40,068.31 $40,736.11 $41,415.05 $42,105.30 $42,807.05 $43,520.51 $44,245.85 $44,983.28 $45,733.00 $46,495.22 $47,270.14 $48,057.97 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 7,109,029.65 7,095,791.24 7,082,332.19 7,068,648.82 7,054,737.39 7,040,594.11 7,026,215.11 7,011,596.46 6,996,734.16 6,981,624.16 6,966,262.32 6,950,644.46 6,934,766.29 6,918,623.50 6,902,211.65 6,885,526.27 6,868,562.81 6,851,316.61 6,833,782.99 6,815,957.13 6,797,834.18 6,779,409.18 6,760,677.09 6,741,632.81 6,722,271.12 6,702,586.73 6,682,574.27 6,662,228.27 6,641,543.17 6,620,513.32 6,599,132.97 6,577,396.28 6,555,297.32 6,532,830.04 6,509,988.30 6,486,765.87 6,463,156.39 6,439,153.43 6,414,750.41 6,389,940.68 6,364,717.46 6,339,073.84 6,313,002.84 6,286,497.31 6,259,550.03 6,232,153.63 6,204,300.61 6,175,983.39 6,147,194.21 6,117,925.21 6,088,168.39 6,057,915.62 6,027,158.65 5,995,889.05 5,964,098.30 5,931,777.70 5,898,918.42 5,865,511.49 5,831,547.78 5,797,018.00 5,761,912.73 5,726,222.37 5,689,937.18 5,653,047.23 5,615,542.44 5,577,412.58 5,538,647.22 5,499,235.77 5,459,167.46 5,418,431.34 5,377,016.29 5,334,911.00 5,292,103.94 5,248,583.44 5,204,337.59 5,159,354.31 5,113,621.31 5,067,126.10 5,019,855.96 4,971,797.99 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 was 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: Combo PIP 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 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 Quality 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 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 ### ### 1 1 ### 1 1 ### ### ### 1 1 1 1 ### ### - 2 7 1 2 7 6 6 6 1 5 5 5 7 2 2 3 7 4 6 4 1 4 6 5 5 1 2 4 1 6 6 3 7 4 2 1 1 6 1 6 5 6 3 1 1 4 1 Quality Speed (Defective (Reduction Free out of in Days) 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 318 892 241 672 367 441 312 741 152 421 664 394 342 495 138 497 600 70 328 738 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 1 22 25 12 1 19 11 7 15 14 18 17 15 6 10 13 8 9 17 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) (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 Results 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 - Task 1: Calculate Basic Probability of each PIP Determine the number of Quality, Speed or Costs projects using the COUNTIF function. Highlight each BLUE column and use a criteria of 1 Divide the COUNTIF value by the total number of PIP projects which is 50 to determine % of each. Next determine the number of combination projects using the COUNTIFS function. Again, Highlight each BLUE column now in question, and use a criteria of 1; divide value by 50 Find probability of PIP success due to quality, speed or costs or combination using COUNTIFS and COMBO 1-7; divide value by 50 Count PIP Percentage PIP Success Quality (1) Speed (2) Costs (3) Q & S (4) Q & C (5) S & C (6) Q, S, C (7) Task 2: Calculate Basic Probability Using new BOD Success Criteria Find the probability of PIP success in Results using the COUNTIF function; divide value by 50 PIP Success (OLD) = Given the criticality of this new decision, the BOD has provided conservative PIP success criteria to analyze past projects Quality > 300 Defective Free per 1000 Speed > 15 Days Reduced Costs < $ 200,000 per 5 Years Find probability of PIP success using COUNTIFS due to new BOD quality, speed or costs criteria. PIP Success (NEW) = 48 49 50 1 1 ### ### 1 1 - 6 3 4 282 905 220 17 7 5 $ (437,307.00) $ 376,547.00 $ (183,168.00) 1 1