MBA700 HOMEWORK 3 DIAMONDS The dataset contained in the diamonds worksheet includes retail prices on 158 diamond stones for sale at a jewelry retailer. A diamond for sale in the retail jewelry market is typically characterized by the "four Cs": the stone's cut, color, clarity, and carat weight. "CUT" refers to the proportions of the diamond, and it takes the label "excellent," "very good," "good," "fair," or "poor." All of the diamonds represented in the spreadsheet carry the "very good" rating. "COLOR" refers to the tinting apparent in the stone, with a clear diamond considered the most valuable. Color is rated on a letter scale with "D" representing the clearest rating, "E" the next clearest rating, and so on. All the diamonds represented in the spreadsheet are rated "D," "E," "CLARITY" refers to the presence of blemishes in the diamond. All the diamonds represented in the spreadsheet carry the "VS1" clarity rating, indicating "very small" inclusions. "CARAT" refers to the size of the diamond measured in "carats," a unit of weight measurement. For each diamond listed, we include its CARAT weight and PRICE expressed in US dollars. Questions 1. Using the Data Analysis ToolPak in Excel, generate Descriptive Statistics for both CARAT and PRICE. Also, calculate the correlation coefficient R between CARAT and PRICE. 2. Create an appropriately labeled scatterplot in Excel with CARAT weight on the horizontal axis and PRICE on the vertical axis. Right-click any of the data points, and insert a linear trend line. Display the trend line equation and R-squared value on the chart. 3. Using the Data Analysis ToolPak in Excel, run a simple linear regression of PRICE (the dependent variable) on CARAT (the independent variable). Include the regression output. On the output, clearly label the intercept and slope coefficients, the sum of squared errors, the 4. 5. Create an appropriately labeled residual plot (that is, a scatterplot of residuals as a function of carat weight). Calculate the estimated prices predicted by the regression model for diamonds weighing 0.39 and 0.95 carats. 6. Examining the regression output and plots, briefly (in no more than a quarter page) discuss the effectiveness of this regression as a model for diamond prices. What is good and bad about this model? If you think there is room for improvement, what specific steps might improve the OBS CARAT PRICE 1 0.23 540 2 0.23 390 3 0.23 334 4 0.24 315 5 0.24 407 6 0.25 343 7 0.25 343 8 0.26 375 9 0.26 400 10 0.26 441 11 0.27 365 12 0.27 415 13 0.27 415 14 0.28 378 15 0.28 430 16 0.28 430 17 0.28 430 18 0.28 473 19 0.29 425 20 0.3 511 21 0.3 533 22 0.3 581 23 0.3 762 24 0.31 519 25 0.31 539 26 0.31 596 27 0.31 596 28 0.31 596 29 0.31 650 30 0.32 557 31 0.32 584 32 0.32 615 33 0.32 615 34 0.34 693 35 0.35 698 36 0.35 732 37 0.35 744 38 0.37 817 39 0.38 659 40 0.38 738 41 0.38 787 42 0.38 814 43 0.38 873 44 0.38 873 45 0.38 935 46 0.39 774 47 0.4 803 48 0.4 803 49 0.4 829 50 0.4 847 51 0.4 867 52 0.4 867 53 0.4 930 54 0.4 930 55 0.4 930 56 0.41 737 57 0.41 800 58 0.41 860 59 0.41 890 60 0.41 899 61 0.42 814 62 0.42 911 63 0.44 1101 64 0.46 1252 65 0.47 1256 66 0.5 1369 67 0.5 1369 68 0.5 1464 69 0.5 1535 70 0.5 1550 71 0.5 1591 72 0.51 1547 73 0.52 1571 74 0.52 1625 75 0.53 1629 76 0.53 1636 77 0.54 1460 78 0.54 1460 79 0.54 1481 80 0.54 1756 81 0.55 1637 82 0.55 1715 83 0.56 1512 84 0.57 1716 85 0.58 1768 86 0.59 1991 87 0.6 1923 88 0.6 2148 89 0.61 1789 90 0.61 1852 91 0.62 1897 92 0.65 2125 93 0.66 2313 20 94 0.7 2521 95 0.7 2557 96 0.7 2706 97 0.7 2710 98 0.7 2734 99 0.7 2834 100 0.7 2981 101 0.7 3072 102 0.7 3617 103 0.7 2300 104 0.71 2571 105 0.71 2707 106 0.71 2749 107 0.71 2778 108 0.71 2805 109 0.71 2944 110 0.72 2844 111 0.72 2844 112 0.72 2883 113 0.73 2827 114 0.73 2850 115 0.73 3186 116 0.74 2517 117 0.75 2889 118 0.78 3181 119 0.78 3307 120 0.78 3357 121 0.78 3466 122 0.79 3002 123 0.79 3381 124 0.8 3286 125 0.8 3638 126 0.9 4589 127 0.9 4868 128 0.92 6664 129 1 6508 130 1 6581 131 1 6584 132 1 6811 133 1 7135 134 1.01 5688 135 1.01 6180 136 1.01 6477 137 1.01 6507 138 1.01 6586 139 1.01 6692 140 1.01 7156 141 1.02 6407 142 1.02 6872 143 1.05 6428 144 1.05 6644 145 1.07 6288 146 1.07 6637 147 1.1 8746 148 1.14 7345 149 1.2 8130 150 1.21 8530 151 1.21 8842 152 1.23 9479 153 1.24 8732 154 1.26 9085 155 1.51 12792 156 1.51 13230 157 1.52 13974 158 1.54 14207