Diamond Regression Instructions and Data File

This exercise develops a multi-variable equation that estimates the market price of diamonds based on the "4Cs" 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. [ Add t 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 and non-numeric 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. Regression Model [A] 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 (3 points): Weight = 1.5 Carats Color Grade: E Clarity Grade: VS1 Cut Category: Good [B] Determine what proportion of the total variation in the market price of diamonds that the model explains. (2 points) [C] Determine which parameter you would eliminate if any if it was necessary to further refine the model. (1 point)