math
mfreeman321
mba501_excel_template-2-1_tw5.xlsxIntroduction
| 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 - Scatterplots
| 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. | |||||
| Week | Profit (thousands) | Number of Defective Items | |||
| 1 | $ 35.00 | 974 | Task 1: Create a Scatterplot with Trendline, Regression Equation, and R² Value | ||
| 2 | $ 490.00 | 693 | Step 1. Highlight the two columns of data (Profit, Defective Items). | ||
| 3 | $ 777.00 | 248 | Step 2. Click INSERT from the tool bar above. | ||
| 4 | $ 922.00 | 277 | Step 3. Select Charts → Scatter | ||
| 5 | $ 519.00 | 509 | Step 4. Once your graph generates, place it in the provided section below. | ||
| 6 | $ 520.00 | 635 | Step 5. After, place your mouse over any data point in the graph and right click. | ||
| 7 | $ 899.00 | 200 | Step 5. Select Add Trendline. | ||
| 8 | $ 391.00 | 743 | Step 6. Within the Trendline Options section, select Linear and check off the Display Equation on chart and Display R-squared value on chart boxes. | ||
| 9 | $ 577.00 | 563 | Step 7. Place the values in a visible area of the chart so that they are legible and not covered by any of the data. | ||
| 10 | $ 419.00 | 715 | Place chart here | ||
| 11 | $ 667.00 | 397 | |||
| 12 | $ 399.00 | 720 | |||
| 13 | $ 540.00 | 659 | |||
| 14 | $ 954.00 | 123 | |||
| 15 | $ 1,078.00 | 8 | |||
| 16 | $ 563.00 | 444 | |||
| 17 | $ 619.00 | 464 | |||
| 18 | $ 625.00 | 483 | |||
| 19 | $ 351.00 | 715 | |||
| 20 | $ 674.00 | 444 | |||
| 21 | $ 547.00 | 639 | |||
| 22 | $ 578.00 | 503 | |||
| 23 | $ 609.00 | 565 | |||
| 24 | $ 228.00 | 785 | |||
| 25 | $ 871.00 | 286 | |||
| 26 | $ 188.00 | 842 | |||
| 27 | $ 632.00 | 480 | |||
| 28 | $ 442.00 | 721 | |||
| 29 | $ 442.00 | 571 | Task 2: Correlation | ||
| 30 | $ 1,114.00 | 25 | Step 1. Determine the Correlation Coefficient (R), using the =CORREL function within the highlighted cell below. | ||
| 31 | $ 864.00 | 272 | CORRELATION COEFFICIENT (R) = | ||
| 32 | $ 825.00 | 241 | |||
| 33 | $ 750.00 | 252 | Step 2. Next, determine the Correlation Coefficient for Profit and Defective items using a different method. To do this, take the square root (=SQRT) of the R² value from the scatterplot you created in Task 1. | ||
| 34 | $ 615.00 | 500 | |||
| 35 | $ 445.00 | 674 | Step 3. Place your result in the highlighted cell below. | ||
| 36 | $ 282.00 | 732 | Step 4. Determine if this value should be positive (+) or negative (-) based on the direction of the scatterplot's trendline from Task 1. | ||
| 37 | $ 409.00 | 701 | CORRELATION COEFFICIENT (R) = | ||
| 38 | $ 637.00 | 401 | |||
| 39 | $ 646.00 | 536 | Question 1: Determine whether a relationship between Profit and Number of defective items exists, and support your response using the scatterplot you created in Task 1. | ||
| 40 | $ 999.00 | 156 | |||
| 41 | $ 232.00 | 824 | |||
| 42 | $ 152.00 | 964 | |||
| 43 | $ 874.00 | 212 | |||
| 44 | $ 981.00 | 218 | |||
| 45 | $ 289.00 | 747 | |||
| 46 | $ 771.00 | 356 | Question 2: Using the information from Task 2, provide a statitistical description of whether or not a relationship exists between Profit and Defective Items by interpreting the meaning of the correlation coefficient. | ||
| 47 | $ 806.00 | 303 | |||
| 48 | $ 921.00 | 113 | |||
| 49 | $ 150.00 | 883 | |||
| 50 | $ 113.00 | 910 | |||
| 51 | $ 1,084.00 | 85 | |||
| 52 | $ 350.00 | 745 | |||
