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)
The post Diamond Regression Instructions and Data File first appeared on COMPLIANT PAPERS.
