Hi, the first pic is the information for question A to E. The following 3 pic are the excel giving information to support the assignment. thanks

Lab 2: Step 1 (Diamond Regression Instructions and Data File) A Attached Files! Diamonds Regression HW V2B POST LAB HW-1A.xlsx (12.234 KB) Description of Assignment This exercise develops a multi-variable equation that estimates the market price of diamonds based on the '40s" of diamond valuation Carat, Color, Clarity, Cut Use the attached data file ABOVE. The data is from a very old edition of the Diamond Buyer's Guide [Don't do it for this exercise, but one can adjust for inflation by (1+average inflation rate) (number of compounding years)] Discussion and Suggested Approach We have numeric (ratio) and non-numeric (categorical) data appearing in the first worksheet of the attached workbook, Tables are provided to code the non-numeric data (substitute a number for a categorical variable) on the second worksheet. On the third worksheet, a blank coding table is provided with the first row already coded properly using the "vlookup" function. You may code this worksheet manually OR let Excel perform the "heavy lifting" by using the "vlookup" function Toggle it into formula view to function and its syntax. Note that the numeric data is referenced directly *CAUTION: Understand this function! It is introduced again in Chapter 10 Development and Interpretation Configure the regression tool to include the "4C's INPUT Options Perform a regression and inculde the column labels from the data table OUPUT Options Set the ouput on a New Worksheet Ply named Regression + Specify all residual options and the normal probability plot. Using Yellow highlight, highlight the cells that you referenced in your regression report to answer the questions below. [A] Linear Equation -- Develop a regression equation using the data provided based on the "4Cs" and estimate the market price of a diamond based on the parameters below (1 points + 2 for follow-on Q). Weight = 15 Carats Color Grade E Clarity Grade VS1 Cut Category Good Use Excel to perform the above sumproduct computation [B] Fit --Determine the error and what proportion of the total variation in the market price of diamonds that the model's selected regression variables explain (2 points) [C] Statistical Significance --Determine which regression variables are statistically significant and determine which one (parameter) you would eliminate, if any, if it was necessary to further refine the model (3 points) [D] Residual Analysis --Determine which sample observation represents the best value from the buyer's perspective and the largest windfall from the seller's perspective (when sold) (2 points) [E] Statistical Inference --Detemine if the model developed from this set of sample data could be applied to the general population Submission Part A (Step 2) Use the first link below to validate and interpret your model (Max 10 points per above) Part B (Step 3) Once you are satisfied with your model, attach your worksheet using the second link below for the balance of credit (Max 5 Points) A B D E F 1 Data from Diamond Buyer's Guide 2 3 Carat Color Price $511.00 4 0.3 I Clarity Cut vvsi Very Good VS2 Ideal 0.3 E $781.00 $839.00 0.3 D VS2 Ideal in ooo 0.3 F VVS1 Ideal $971.00 0.3 E VVS1 Ideal $1,130.00 9 0.5 E SI2 Ideal 10 0.5 D SI1 Ideal $1,263.00 $1,604.00 $1,844.00 11 0.5 E VS2 Ideal 12 0.5 VVS2 Ideal $2,026.00 $2,714.00 13 0.5 VS2 Ideal 14 1 VS1 Ideal $5,899.00 15 1 0 W 1 1 0 0 0 VVS2 Ideal $6,445.00 16 1.01 VS2 Ideal 22 - - nnnnnnn $6,892.00 $7,308.00 17 1.01 VS1 Ideal 18 1.01 IF Ideal 19 2 K SI2 Very Good $17,716.00 $8,620.00 $10,264.00 $11,751.00 20 2 K VS2 Very Good 21 2 I SI1 Premium 22 2 E SI2 Very Good $16,092.00 23 2 E VS1 Very Good 24 3 S12 $27,080.00 $14,305.00 $17,905.00 3 ) SI1 25 26 3.01 Premium Very Good Very Good Very Good G SI1 $29,396.00 27 3 F VS2 3.01 D $42,881.00 $112,079.00 IF Good 28 29 30 31 Data Code Encoded + Ready G H F D E B A 1 Regression Encoding 2 3 Grade Cut 4 Color Grade 1 Ideal 1 5 Clarity Grade IF 1 VVS1 2 WS2 3 VS1 4 1 2 6 2 7 3 Premium Very Good Good Fair 3 8 4 9 5 4 VS2 SI1 10 6 Poor 5 OwOI - - V - EZO 11 7 SI2 in ooooo 12 8 11 13 9 12 14 10 13 15 11 16 12 13 P 17 18 19 14 R 15 20 21 22 23 24 Data Code Encoded Ready F B E 1 Data from Diamond Buyer's Guide 2 3 Carat Color Clarity Cut 4 0.3 2 Price 6 2 $511.00 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 Data Code Encoded Ready Lab 2: Step 1 (Diamond Regression Instructions and Data File) A Attached Files! Diamonds Regression HW V2B POST LAB HW-1A.xlsx (12.234 KB) Description of Assignment This exercise develops a multi-variable equation that estimates the market price of diamonds based on the '40s" of diamond valuation Carat, Color, Clarity, Cut Use the attached data file ABOVE. The data is from a very old edition of the Diamond Buyer's Guide [Don't do it for this exercise, but one can adjust for inflation by (1+average inflation rate) (number of compounding years)] Discussion and Suggested Approach We have numeric (ratio) and non-numeric (categorical) data appearing in the first worksheet of the attached workbook, Tables are provided to code the non-numeric data (substitute a number for a categorical variable) on the second worksheet. On the third worksheet, a blank coding table is provided with the first row already coded properly using the "vlookup" function. You may code this worksheet manually OR let Excel perform the "heavy lifting" by using the "vlookup" function Toggle it into formula view to function and its syntax. Note that the numeric data is referenced directly *CAUTION: Understand this function! It is introduced again in Chapter 10 Development and Interpretation Configure the regression tool to include the "4C's INPUT Options Perform a regression and inculde the column labels from the data table OUPUT Options Set the ouput on a New Worksheet Ply named Regression + Specify all residual options and the normal probability plot. Using Yellow highlight, highlight the cells that you referenced in your regression report to answer the questions below. [A] Linear Equation -- Develop a regression equation using the data provided based on the "4Cs" and estimate the market price of a diamond based on the parameters below (1 points + 2 for follow-on Q). Weight = 15 Carats Color Grade E Clarity Grade VS1 Cut Category Good Use Excel to perform the above sumproduct computation [B] Fit --Determine the error and what proportion of the total variation in the market price of diamonds that the model's selected regression variables explain (2 points) [C] Statistical Significance --Determine which regression variables are statistically significant and determine which one (parameter) you would eliminate, if any, if it was necessary to further refine the model (3 points) [D] Residual Analysis --Determine which sample observation represents the best value from the buyer's perspective and the largest windfall from the seller's perspective (when sold) (2 points) [E] Statistical Inference --Detemine if the model developed from this set of sample data could be applied to the general population Submission Part A (Step 2) Use the first link below to validate and interpret your model (Max 10 points per above) Part B (Step 3) Once you are satisfied with your model, attach your worksheet using the second link below for the balance of credit (Max 5 Points) A B D E F 1 Data from Diamond Buyer's Guide 2 3 Carat Color Price $511.00 4 0.3 I Clarity Cut vvsi Very Good VS2 Ideal 0.3 E $781.00 $839.00 0.3 D VS2 Ideal in ooo 0.3 F VVS1 Ideal $971.00 0.3 E VVS1 Ideal $1,130.00 9 0.5 E SI2 Ideal 10 0.5 D SI1 Ideal $1,263.00 $1,604.00 $1,844.00 11 0.5 E VS2 Ideal 12 0.5 VVS2 Ideal $2,026.00 $2,714.00 13 0.5 VS2 Ideal 14 1 VS1 Ideal $5,899.00 15 1 0 W 1 1 0 0 0 VVS2 Ideal $6,445.00 16 1.01 VS2 Ideal 22 - - nnnnnnn $6,892.00 $7,308.00 17 1.01 VS1 Ideal 18 1.01 IF Ideal 19 2 K SI2 Very Good $17,716.00 $8,620.00 $10,264.00 $11,751.00 20 2 K VS2 Very Good 21 2 I SI1 Premium 22 2 E SI2 Very Good $16,092.00 23 2 E VS1 Very Good 24 3 S12 $27,080.00 $14,305.00 $17,905.00 3 ) SI1 25 26 3.01 Premium Very Good Very Good Very Good G SI1 $29,396.00 27 3 F VS2 3.01 D $42,881.00 $112,079.00 IF Good 28 29 30 31 Data Code Encoded + Ready G H F D E B A 1 Regression Encoding 2 3 Grade Cut 4 Color Grade 1 Ideal 1 5 Clarity Grade IF 1 VVS1 2 WS2 3 VS1 4 1 2 6 2 7 3 Premium Very Good Good Fair 3 8 4 9 5 4 VS2 SI1 10 6 Poor 5 OwOI - - V - EZO 11 7 SI2 in ooooo 12 8 11 13 9 12 14 10 13 15 11 16 12 13 P 17 18 19 14 R 15 20 21 22 23 24 Data Code Encoded Ready F B E 1 Data from Diamond Buyer's Guide 2 3 Carat Color Clarity Cut 4 0.3 2 Price 6 2 $511.00 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 Data Code Encoded Ready