{"text_format":"3","workbook":"xls","n_sheets":8,"sheets":["Exercise 1","Exercise 2","Exercise 3","Exercise 4","Analysis 1","Analysis 2","Analysis 3","Analysis 4"],"read":"whole","date_system":"1900","formulas":"cached_only"}

{"sheet":"Exercise 1","index":0,"n_rows":46,"n_cols":23,"rows_kept":46,"flags":["formulas_cached_only"]}
[" #","Lot #","Vendor","City","Date","$ Price","Type","Ct Wt","Cut","Color","Clarity","Cert","Price  /Ct","Const $ Adj *","Const $/CT","Subj Ref $/Ct   /","Comp Ref $/Ct"," Ratio","Adj Price/CT","Subj  Ct Wt","Adj Price","Rounded","1 -Ratio"]
[3,1226,"Doyle","NY","2006-04-12",{"v":1700,"d":"$1,700"},"Ring",0.71,"RB","F","VS2","GIA",{"v":2394.366197183099,"d":"$2,394"},null,{"v":0,"d":"$0"},null,null,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},1.53,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"}]
[6,147,"Sotheby's","Amsterdam","2006-06-27",{"v":5060,"d":"$5,060"},"Ring",1.01,"RB","G","VS1","GIA",{"v":5009.9009900990095,"d":"$5,010"},null,{"v":0,"d":"$0"},null,null,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},1.53,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"}]
[14,152,"Bonham's","London","2005-04-07",{"v":27273,"d":"$27,273"},"Ring",2.04,"RB","F","VVS1","GIA",{"v":13369.117647058823,"d":"$13,369"},null,{"v":0,"d":"$0"},null,null,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},1.53,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"}]
[8,607,"Skinner","Boston","2004-06-15",{"v":5581,"d":"$5,581"},"Ring",1.14,"RB","G","VS1","GIA",{"v":4895.6140350877195,"d":"$4,896"},null,{"v":0,"d":"$0"},null,null,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},1.53,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"}]
[11,443,"Sotheby's","NY","2006-10-24",{"v":6300,"d":"$6,300"},"Ring",1.53,"OE","I","SI1","GIA",{"v":4117.64705882353,"d":"$4,118"},null,{"v":0,"d":"$0"},null,null,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},1.53,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"}]
[4,37,"Bonham's","London","2004-04-21",{"v":3260,"d":"$3,260"},"Ring",0.91,"RB","G","SI1","GIA",{"v":3582.4175824175823,"d":"$3,582"},null,{"v":0,"d":"$0"},null,null,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},1.53,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"}]
[null,null,"Subject",{"$nulls":3},"Ring",1.53,"RB","G","VS1","GIA",null,"Price reference:",null,"Guide  ","Rap, Guide, Palmieri, etc.",null,null,"Total",{"$error":"#DIV/0!"}]
[]
[{"$nulls":15},"What is the range?"]
["Col A-L: Enter data for each comparable sale",{"$nulls":14},"What is the mode?"]
["Col M: Divide “$ Price” by “Ct Wt” (Col F/Col H) to convert transaction prices to Price/ct",{"$nulls":14},"What is the median?"]
["Col N: Enter the adjustment to convert to constant dollars in Const $ Adj (US BLS Inflation Calculator)  ",{"$nulls":14},"What is the mean?"]
["Col 0: Multiply “Price/Ct” x “Const $ Adj” (Col M x Col N) to find \"Constant Dollars /Ct\"",{"$nulls":14},"Which comps needed least adjustment? (1 -ratio closest to \"0\")"]
["Col P: Enter the per/carat reference price for Subject (from an industry matrix) in every row under Subj Ref",{"$nulls":14},"What is your opinion of value?"]
["Col Q: Enter the per/carat reference price for each comp (from the same matrix) under Com Ref $/Ct",{"$nulls":14},"What is your degree of confidence? A reasonable  "]
["Col R: Calculate Ratio of subject/comp by dividing “Subject Reference $/Ct” by “Comp Reference $/Ct” (Col P/Col Q)",{"$nulls":14},"      degree of jewelry appraisal probability or certainty?"]
["Col S: Multiply each “Constant $/Ct” by “Ratio” (Col O x Col R) to get Adjusted $/Ct"]
["Col T: Enter the Subject Carat Weight into every row"]
["Col U: Multiply each “Adjusted $/Ct” times the “Subject Ct Weight” (Col S x Col T) to get each Adjusted Price—what"]
["          that comp would have sold for had its value attributes been the same as the subject "]
[]
[null,"Analysis"]
[null,"Col U: SORT data by adjusted price, low to high"]
[null,"Col V: It may be necessary to round the adjusted prices to a significant figure to find the Mode"]
[null,"Col W: Subtract “1” from each Ratio (Col R). The results closest to “1”, either higher or lower, are more reliable."]
[]
[null,"Of the sample selected: "]
[null,"What is the range?"]
[null,"What is the mode?"]
[null,"What is the median?"]
[null,"What is the mean?"]
[null,"Analyze the amount of adjustment in \"Ratio\""]
[null,"The comps that needed least adjustment are the most reliable. (Ratios closest + or - \"1\")"]
[]
[null,"Opinion of value"]
[null,"Do not use the mean as the indicated value. (Rejected by IRS.) Use the least adjusted, (above), the mode if there is one, and/or median."]
[null,"Formula to round to a significant figure is \"  =ROUND(COLrow, -3) \" for nearest thousand; -2 for nearest hundred might help establish a mode"]
[null,"What is your opinion of value? (Rounded to a significant figure.)"]
[null,"What is your degree of confidence: a reasonable degree of jewelry appraisal probability or certainty?"]
[null,"State your opinion of the (kind of value), to a reasonable degree of jewelry appraisal probability (more than 50% probable) or certainty (more than 80% certain). "]
[null,"Never state the % on an appraisal or in testimony. Probability and Certainty are legally understood measures."]
[]
[null,"Write a narrative explaining the similarities and differences between each comp and the subject, how and why you adjusted each"]
[null,"transaction price, your analysis of the results, and how you formed your opinion of value. "]
[null,"This is best done by breaking it into 2 parts, first comparing each comp to the subject, second explaining your analysis & opinion."]

{"sheet":"Exercise 2","index":1,"n_rows":59,"n_cols":23,"rows_kept":59,"flags":["formulas_cached_only"]}
[" #","Lot #","Vendor","City","Date","$ Price","Type","Ct Wt","Cut","Color","Clarity","Cert","Price  /Ct","Const $ Adj *","Const $/CT","Subj Ref $/Ct","Comp Ref $/Ct "," Ratio","Adj Price/CT","Subj  Ct Wt","Adj Price","Rounded","1 -Ratio"]
[2,632,"Hindman","Illinois","2006-05-21",{"v":1440,"d":"$1,440"},"Ring",{"v":1.2,"d":"1.20"},"OE","K/L","VS2 ",null,{"v":1200,"d":"$1,200"},null,{"v":0,"d":"$0"},null,null,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},1.53,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"}]
[5,640,"Christie's","NY","2006-07-11",{"v":4560,"d":"$4,560"},"Ring",1.95,"OE","I/J","SI",null,{"v":2338.4615384615386,"d":"$2,338"},null,{"v":0,"d":"$0"},null,null,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},1.53,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"}]
[3,46,"Christie's","NY","2004-09-14",{"v":3107,"d":"$3,107"},"Ring",{"v":1.8,"d":"1.80"},"OE","M+","VVS-VS",null,{"v":1726.111111111111,"d":"$1,726"},null,{"v":0,"d":"$0"},null,null,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},1.53,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"}]
[8,330,"Christie's","NY","2004-02-11",{"v":8963,"d":"$8,963"},"Ring",2.45,"OE","I/J/K","VVS-VS",null,{"v":3658.3673469387754,"d":"$3,658"},null,{"v":0,"d":"$0"},null,null,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},1.53,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"}]
[1,57,"Christie's","NY","2004-09-14",{"v":3646,"d":"$3,646"},"Ring",{"v":1.2,"d":"1.20"},"OE","J/K/L","VVS-VS",null,{"v":3038.3333333333335,"d":"$3,038"},null,{"v":0,"d":"$0"},null,null,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},1.53,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"}]
[{"$nulls":19},"Sum:",{"$error":"#DIV/0!"},null,{"v":1,"d":"1.00"}]
[{"$nulls":5},"Subject:","Ring",{"v":1.8,"d":"1.80"},"OE","K","VS-2","-",null,"  Price Reference:",null,"   Guide",null,"Guide","Rap, Guide, Palmieri, etc."]
[]
[null,"Evaluate the reported quality through the eyes of a buyer",{"$nulls":12},"Of the comps selected: "]
[null,"Col A-L: Enter data for each comparable sale",{"$nulls":12},"What is the range?"]
[null,"Col M: Divide “$ Price” by “Ct Wt” (Col F/Col H) to convert transaction prices to Price/ct",{"$nulls":12},"What is the mode?"]
[null,"Col N: Enter the adjustment to convert to constant dollars in Const $ Adj (US BLS Inflation Calculator)  ",{"$nulls":12},"What is the median?"]
[null,"Col 0: Multiply “Price/Ct” x “Const $ Adj” (Col M x Col N) to find \"Constant Dollars /Ct\"",{"$nulls":12},"What is the mean?"]
[null,"Col P: Enter the per/carat reference price for Subject (from an industry matrix) in every row under Subj Ref",{"$nulls":12},"Which comps needed least adjustment? (1 -ratio closest to \"0\")"]
[null,"Col Q: Enter the per/carat reference price for each comp (from the same matrix) under Com Ref $/Ct",{"$nulls":12},"To what do you attribute the weak central tendency?"]
[null,"Col R: Calculate Ratio of subject/comp by dividing “Subject Reference $/Ct” by “Comp Reference $/Ct” (Col P/Col Q)",{"$nulls":12},"What is your opinion of value?"]
[null,"Col S: Multiply each “Constant $/Ct” by “Ratio” (Col O x Col R) to get Adjusted $/Ct",{"$nulls":12},"What is your degree of confidence? A reasonable degree of jewelry "]
[null,"Col T: Enter the Subject Carat Weight into every row",{"$nulls":12},"    appraisal probability or certainty?"]
[null,"Col U: Multiply each “Adjusted $/Ct” times the “Subject Ct Weight” (Col S x Col T) to get each Adjusted Price—what"]
[null,"          that comp would have sold for had its value attributes been the same as the subject "]
[]
[null,"Analysis"]
[null,"Col U: SORT data by adjusted price, low to high"]
[null,"Col V: It may be necessary to round the adjusted prices to a significant figure to find the Mode"]
[null,"Col W: Subtract “1” from each Ratio (Col R). The results closest to “1”, either higher or lower, are more reliable."]
[]
[null,"Of the sample selected: "]
[null,"What is the range?"]
[null,"What is the mode?"]
[null,"What is the median?"]
[null,"What is the mean?"]
[null,"Analyze the amount of adjustment in \"Ratio\""]
[null,"The comps that needed least adjustment are the most reliable. (Ratios closest + or - \"1\")"]
[]
[null,"Opinion of value"]
[null,"Do not use the mean as the indicated value. (Rejected by IRS.) Use the least adjusted, (above), the mode if there is one, and/or median."]
[null,"Formula to round to a significant figure is \"  =ROUND(COLrow, -3) \" for nearest thousand; -2 for nearest hundred might help establish a mode"]
[null,"What is your opinion of value? (Rounded to a significant figure.)"]
[null,"What is your degree of confidence: a reasonable degree of jewelry appraisal probability or certainty?"]
[null,"State your opinion of the (kind of value), to a reasonable degree of jewelry appraisal probability (more than 50% probable) or certainty (more than 80% certain). "]
[null,"Never state the % on an appraisal or in testimony. Probability and Certainty are legally understood measures."]
[]
[null,"Write a narrative explaining the similarities and differences between each comp and the subject, how and why you adjusted each"]
[null,"transaction price, your analysis of the results, and how you formed your opinion of value. "]
[null,"This is best done by breaking it into 2 parts, first comparing each comp to the subject, second explaining your analysis & opinion."]
[null,"======================================"]
[null,"Comparison of comps to subject:"]
[null,"Comp 1 (compare and explain differences to subject, and explain significance) “Comp #1 weighed more than the subject but was lower in color."]
[null,"The larger size is more desirable, but the lower color is less desirable. I adjusted the transaction price down for weight and up for color to make "]
[null,"the comp equivalent to the subject”"]
[null,"Comp 2 (compare and explain differences to subject, and explain significance) etc. “Comp #2 weighed the same as the subject, but the color was "]
[null,"better while the clarity was lower. I adjusted the transaction price to compensate down for color and up for clarity.”"]
[]
[null,"Analysis of sales data:"]
[null,"In order to analyze the sales, I converted all transaction prices to $/carat"]
[null,"I adjusted the selling price of each comparator to 20xx dollars by . . . "]
[null,"Next, I adjusted the selling price of each comp for any differences in value attributes to make it equivalent to the subject"]
[null,"Etc, etc. . ."]

{"sheet":"Exercise 3","index":2,"n_rows":64,"n_cols":23,"rows_kept":64,"flags":["formulas_cached_only"]}
["#","Lot #","Vendor","City","Date","$ Price","TYPE","Ct Wt","Cut","Color","Clarity","Cert","Price/Ct","Const $ Adj","Const $/Ct","Subj Ref $/Ct","Comp Ref $/Ct","Ratio","Adj $/Ct","Subj Ct Wt","Adjusted Price","Rounded","1 -Ratio"]
[2,235,"Bonham's","London","2005-09-28",{"v":1636,"d":"$1,636"},"Ring-EF",0.62,"RB","D","VS2","GIA",{"v":2638.7096774193546,"d":"$2,639"},null,{"v":0,"d":"$0"},null,null,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},null,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"}]
[5,16,"Sotheby's","NY","2007-10-24",{"v":7200,"d":"$7,200"},"Ring T&Co",1.01,"RB","G","IF","GIA",{"v":7128.712871287129,"d":"$7,129"},null,{"v":0,"d":"$0"},null,null,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},null,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"}]
[6,147,"Sotheby's","Amsterdam","2006-06-27",{"v":5060,"d":"$5,060"},"Ring",1.01,"RB","G","VS1","GIA",{"v":5009.9009900990095,"d":"$5,010"},null,{"v":0,"d":"$0"},null,null,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},null,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"}]
[7,164,"Bonham's","London","2003-10-02",{"v":6953,"d":"$6,953"},"Ring & sap",1.02,"RB","E","VVS2","GIA",{"v":6816.666666666667,"d":"$6,817"},null,{"v":0,"d":"$0"},null,null,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},null,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"}]
[8,607,"Skinner","Boston","2004-06-15",{"v":5581,"d":"$5,581"},"Ring",1.14,"RB","G","VS1","GIA",{"v":4895.6140350877195,"d":"$4,896"},null,{"v":0,"d":"$0"},null,null,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},null,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"}]
[9,211,"Sotheby's","Amsterdam","2006-06-27",{"v":7952,"d":"$7,952"},"Ring Fr.",1.24,"RB","G","VVS2","GIA",{"v":6412.903225806452,"d":"$6,413"},null,{"v":0,"d":"$0"},null,null,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},null,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"}]
[11,443,"Sotheby's","NY","2006-10-24",{"v":6300,"d":"$6,300"},"Ring",1.53,"OE","I","SI1","GIA",{"v":4117.64705882353,"d":"$4,118"},null,{"v":0,"d":"$0"},null,null,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},null,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"}]
[13,247,"Bonham's","London","2004-12-09",{"v":22727,"d":"$22,727"},"Ring T&Co",1.92,"RB","D","IF","GIA",{"v":11836.979166666668,"d":"$11,837"},null,{"v":0,"d":"$0"},null,null,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},null,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"}]
[14,152,"Bonham's","London","2005-04-07",{"v":27273,"d":"$27,273"},"Ring",2.04,"RB","F","VVS1","GIA",{"v":13369.117647058823,"d":"$13,369"},null,{"v":0,"d":"$0"},null,null,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},null,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"}]
[17,570,"Skinner","Boston","2004-12-14",{"v":28200,"d":"$28,200"},"Ring +2 3.18 tw",2.52,"RB","F","VS1","GIA",{"v":11190.47619047619,"d":"$11,190"},null,{"v":0,"d":"$0"},null,null,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},null,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"}]
[19,149,"Dawson/Nye","MorrisPlns","2007-01-24",{"v":112100,"d":"$112,100"},"Ring & bag .80",9.85,"RB","K","SI1","GIA",{"v":11380.710659898477,"d":"$11,381"},null,{"v":0,"d":"$0"},null,null,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},null,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"}]
[{"$nulls":19},"Sum:",{"$error":"#DIV/0!"}]
[null,null,"Subject",{"$nulls":3},"Ring",2.02,"RB","G","VS1","GIA",null,null,"Data Source:",null,"Rap, Guide, Palmieri, etc."]
[]
[null,"Col A-L: Enter data for each comparable sale",{"$nulls":12},"What is the range?"]
[null,"Col M: Divide “$ Price” by “Ct Wt” (Col F/Col H) to convert transaction prices to Price/ct",{"$nulls":12},"What is the mode?"]
[null,"Col N: Enter the adjustment to convert to constant dollars in Const $ Adj (US BLS Inflation Calculator)  ",{"$nulls":12},"What is the median?"]
[null,"Col 0: Multiply “Price/Ct” x “Const $ Adj” (Col M x Col N) to find \"Constant Dollars /Ct\"",{"$nulls":12},"What is the mean?"]
[null,"Col P: Enter the per/carat reference price for Subject (from an industry matrix) in every row under Subj Ref",{"$nulls":12},"Which comps needed least adjustment? (1 -ratio closest to \"0\")"]
[null,"Col Q: Enter the per/carat reference price for each comp (from the same matrix) under Com Ref $/Ct",{"$nulls":12},"What is your opinion of value?"]
[null,"Col R: Calculate Ratio of subject/comp by dividing “Subject Reference $/Ct” by “Comp Reference $/Ct” (Col P/Col Q)",{"$nulls":12},"What is your degree of confidence? A reasonable  "]
[null,"Col S: Multiply each “Constant $/Ct” by “Ratio” (Col O x Col R) to get Adjusted $/Ct",{"$nulls":12},"      degree of jewelry appraisal probability or certainty?"]
[null,"Col T: Enter the Subject Carat Weight into every row"]
[null,"Col U: Multiply each “Adjusted $/Ct” times the “Subject Ct Weight” (Col S x Col T) to get each Adjusted Price—what"]
[null,"          that comp would have sold for had its value attributes been the same as the subject "]
[]
[null,"Analysis"]
[null,"Col U: SORT data by adjusted price, low to high"]
[null,"Col V: It may be necessary to round the adjusted prices to a significant figure to find the Mode"]
[null,"Col W: Subtract “1” from each Ratio (Col R). The results closest to “1”, either higher or lower, are more reliable."]
[]
[null,"Of the sample selected: "]
[null,"What is the range?"]
[null,"What is the mode?"]
[null,"What is the median?"]
[null,"What is the mean?"]
[null,"Analyze the amount of adjustment in \"Ratio\""]
[null,"The comps that needed least adjustment are the most reliable. (Ratios closest + or - \"1\")"]
[]
[null,"Opinion of value"]
[null,"Do not use the mean as the indicated value. (Rejected by IRS.) Use the least adjusted, (above), the mode if there is one, and/or median."]
[null,"Formula to round to a significant figure is \"  =ROUND(COLrow, -3) \" for nearest thousand; -2 for nearest hundred might help establish a mode"]
[null,"What is your opinion of value? (Rounded to a significant figure.)"]
[null,"What is your degree of confidence: a reasonable degree of jewelry appraisal probability or certainty?"]
[null,"State your opinion of the (kind of value), to a reasonable degree of jewelry appraisal probability (more than 50% probable) or certainty (more than 80% certain). "]
[null,"Never state the % on an appraisal or in testimony. Probability and Certainty are legally understood measures."]
[]
[null,"Write a narrative explaining the similarities and differences between each comp and the subject, how and why you adjusted each"]
[null,"transaction price, your analysis of the results, and how you formed your opinion of value. "]
[null,"This is best done by breaking it into 2 parts, first comparing each comp to the subject, second explaining your analysis & opinion."]
[null,"======================================"]
[null,"Comparison of comps to subject:"]
[null,"Comp 1 (compare and explain differences to subject, and explain significance) “Comp #1 weighed more than the subject but was lower in color."]
[null,"The larger size is more desirable, but the lower color is less desirable. I adjusted the transaction price down for weight and up for color to make "]
[null,"the comp equivalent to the subject”"]
[null,"Comp 2 (compare and explain differences to subject, and explain significance) etc. “Comp #2 weighed the same as the subject, but the color was "]
[null,"better while the clarity was lower. I adjusted the transaction price to compensate down for color and up for clarity.”"]
[]
[null,"Analysis of sales data:"]
[null,"In order to analyze the sales, I converted all transaction prices to $/carat"]
[null,"I adjusted the selling price of each comparator to 20xx dollars by . . . "]
[null,"Next, I adjusted the selling price of each comp for any differences in value attributes to make it equivalent to the subject"]
[null,"Etc, etc. . ."]

{"sheet":"Exercise 4","index":3,"n_rows":63,"n_cols":23,"rows_kept":63,"flags":["formulas_cached_only"]}
[" #","Lot #","Vendor","City","Date","$ Price","Type","Ct Wt","Cut","Color","Clarity","Cert","Price/Ct","Const $ Adj","Const $/Ct","Subj Ref /Ct","Comp Ref $/Ct","Ratio","Adj $/CT","Subj Ct Wt","Adjusted Price","Rounded","1 -Ratio"]
[8,340,"Christie's","NY","2004-10-13",{"v":4780,"d":"$4,780"},"Deco",{"v":9,"d":"9.00"},"OE, rose","H-M+","VS-SI",null,{"v":531.1111111111111,"d":"$531"},null,{"v":0,"d":"$0"},null,null,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},1.53,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"}]
[9,45,"Christies","NY","2004-09-14",{"v":2629,"d":"$2,629"},"Deco",{"v":3.5,"d":"3.50"},"OE ","K-M","VS-SI",null,{"v":751.1428571428571,"d":"$751"},null,{"v":0,"d":"$0"},null,null,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},1.53,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"}]
[11,263,"Christie's","NY","2004-09-14",{"v":1912,"d":"$1,912"},"Deco",{"v":1.6,"d":"1.60"},"OE & sapp","H-I","VVS-VS",null,{"v":1195,"d":"$1,195"},null,{"v":0,"d":"$0"},null,null,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},1.53,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"}]
[12,649,"Butterfield","SF","2004-06-29",{"v":2938,"d":"$2,938"},"Deco",{"v":2.5,"d":"2.50"},"Mq & em",{"$nulls":3},{"v":1175.2,"d":"$1,175"},null,{"v":0,"d":"$0"},null,null,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},1.53,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"}]
[18,513,"Skinner","Bost","2004-09-21",{"v":1763,"d":"$1,763"},"Deco",{"v":2,"d":"2.00"},"OE, s/c",{"$nulls":3},{"v":881.5,"d":"$882"},null,{"v":0,"d":"$0"},null,null,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},1.53,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"}]
[19,640,"Skinner","Bost","2004-06-15",{"v":9988,"d":"$9,988"},"Deco ++",18.65,"OE, s/c",{"$nulls":3},{"v":535.549597855228,"d":"$536"},null,{"v":0,"d":"$0"},null,null,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},1.53,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"}]
[21,228,"Weschlers","DC","2007-04-21",{"v":1400,"d":"$1,400"},"Deco",2.65,"OE & em",{"$nulls":3},{"v":528.3018867924528,"d":"$528"},null,{"v":0,"d":"$0"},null,null,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},1.53,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"}]
[{"$nulls":19},"Sum:",{"$error":"#DIV/0!"}]
[{"$nulls":4},"Subject:","Brooch","Deco","6.22+","OE","K-O","VVS2-VS2",null,null,"Reference Source: ",null,null,"(Rap, Guide, Palmieri, etc.)"]
[{"$nulls":7},"4 lg dias",null,"35 melee @ 3.22 t.w. I-K, VS"]
[null,null,"Photo of the subject brooch is on Page 21 of your manual. ",{"$nulls":6},"See the actual comps on my website at www.jewelry-appraisal.com/edu.html."]
[null,null,"The above figures are based on carat weight only and give a wide range of quality. See if you can refine the selection of comps and/or adjustments to get more reliable results."]
[null,null,"You might want to modify this spreadsheet by putting in a column for Melee."]
[null,"Col A-L: Enter data for each comparable sale",{"$nulls":13},"What is the range?",{"$nulls":4},{"$error":"#DIV/0!"}]
[null,"Col M: Divide “$ Price” by “Ct Wt” (Col F/Col H) to convert transaction prices to Price/ct",{"$nulls":13},"What is the mode?"]
[null,"Col N: Enter the adjustment to convert to constant dollars in Const $ Adj (US BLS Inflation Calculator)  ",{"$nulls":13},"What is the median?"]
[null,"Col 0: Multiply “Price/Ct” x “Const $ Adj” (Col M x Col N) to find \"Constant Dollars /Ct\"",{"$nulls":13},"What is the mean?",{"$nulls":4},{"$error":"#DIV/0!"}]
[null,"Col P: Enter the per/carat reference price for Subject (from an industry matrix) in every row under Subj Ref",{"$nulls":13},"Which comps needed least adjustment? (1 -ratio closest to \"0\")"]
[null,"Col Q: Enter the per/carat reference price for each comp (from the same matrix) under Com Ref $/Ct",{"$nulls":13},"What is your opinion of value?"]
[null,"Col R: Calculate Ratio of subject/comp by dividing “Subject Reference $/Ct” by “Comp Reference $/Ct” (Col P/Col Q)",{"$nulls":13},"What is your degree of confidence? A reasonable  "]
[null,"Col S: Multiply each “Constant $/Ct” by “Ratio” (Col O x Col R) to get Adjusted $/Ct",{"$nulls":13},"      degree of jewelry appraisal probability or certainty?"]
[null,"Col T: Enter the Subject Carat Weight into every row"]
[null,"Col U: Multiply each “Adjusted $/Ct” times the “Subject Ct Weight” (Col S x Col T) to get each Adjusted Price—what"]
[null,"          that comp would have sold for had its value attributes been the same as the subject "]
[]
[null,"Analysis"]
[null,"Col U: SORT data by adjusted price, low to high"]
[null,"Col V: It may be necessary to round the adjusted prices to a significant figure to find the Mode"]
[null,"Col W: Subtract “1” from each Ratio (Col R). The results closest to “1”, either higher or lower, are more reliable."]
[]
[null,"Of the sample selected: "]
[null,"What is the range?"]
[null,"What is the mode?"]
[null,"What is the median?"]
[null,"What is the mean?"]
[null,"Analyze the amount of adjustment in \"Ratio\""]
[null,"The comps that needed least adjustment are the most reliable. (Ratios closest + or - \"1\")"]
[]
[null,"Opinion of value"]
[null,"Do not use the mean as the indicated value. (Rejected by IRS.) Use the least adjusted, (above), the mode if there is one, and/or median."]
[null,"Formula to round to a significant figure is \"  =ROUND(COLrow, -3) \" for nearest thousand; -2 for nearest hundred might help establish a mode"]
[null,"What is your opinion of value? (Rounded to a significant figure.)"]
[null,"What is your degree of confidence: a reasonable degree of jewelry appraisal probability or certainty?"]
[null,"State your opinion of the (kind of value), to a reasonable degree of jewelry appraisal probability (more than 50% probable) or certainty (more than 80% certain). "]
[null,"Never state the % on an appraisal or in testimony. Probability and Certainty are legally understood measures."]
[]
[null,"Write a narrative explaining the similarities and differences between each comp and the subject, how and why you adjusted each"]
[null,"transaction price, your analysis of the results, and how you formed your opinion of value. "]
[null,"This is best done by breaking it into 2 parts, first comparing each comp to the subject, second explaining your analysis & opinion."]
[null,"======================================"]
[null,"Comparison of comps to subject:"]
[null,"Comp 1 (compare and explain differences to subject, and explain significance) “Comp #1 weighed more than the subject but was lower in color."]
[null,"The larger size is more desirable, but the lower color is less desirable. I adjusted the transaction price down for weight and up for color to make "]
[null,"the comp equivalent to the subject”"]
[null,"Comp 2 (compare and explain differences to subject, and explain significance) etc. “Comp #2 weighed the same as the subject, but the color was "]
[null,"better while the clarity was lower. I adjusted the transaction price to compensate down for color and up for clarity.”"]
[]
[null,"Analysis of sales data:"]
[null,"In order to analyze the sales, I converted all transaction prices to $/carat"]
[null,"I adjusted the selling price of each comparator to 20xx dollars by . . . "]
[null,"Next, I adjusted the selling price of each comp for any differences in value attributes to make it equivalent to the subject"]
[null,"Etc, etc. . ."]

{"sheet":"Analysis 1","index":4,"n_rows":46,"n_cols":23,"rows_kept":46,"flags":["formulas_cached_only"]}
[" #","Lot #","Vendor","City","Date","$ Price","Type","Ct Wt","Cut","Color","Clarity","Cert","Price  /Ct","Const $ Adj *","Const $/CT","Subj Ref $/Ct  ","Comp Ref $/Ct"," Ratio","Adj Price/CT","Subj  Ct Wt","Adj Price","Rounded","1 -Ratio"]
[3,1226,"Doyle","NY","2006-04-12",{"v":1700,"d":"$1,700"},"Ring",0.71,"RB","F","VS2","GIA",{"v":2394.366197183099,"d":"$2,394"},1.02,{"v":2442.253521126761,"d":"$2,442"},{"v":8300,"d":"$8,300"},{"v":3900,"d":"$3,900"},{"v":2.128205128205128,"d":"2.13"},{"v":5197.616468039004,"d":"$5,198"},1.53,{"v":7952.353196099676,"d":"$7,952"},{"v":8000,"d":"$8,000"},{"v":-1.1282051282051282,"d":"-1.13"}]
[6,147,"Sotheby's","Amsterdam","2006-06-27",{"v":5060,"d":"$5,060"},"Ring",1.01,"RB","G","VS1","GIA",{"v":5009.9009900990095,"d":"$5,010"},1.02,{"v":5110.09900990099,"d":"$5,110"},{"v":8300,"d":"$8,300"},{"v":6500,"d":"$6,500"},{"v":1.2769230769230768,"d":"1.28"},{"v":6525.20335110434,"d":"$6,525"},1.53,{"v":9983.56112718964,"d":"$9,984"},{"v":10000,"d":"$10,000"},{"v":-0.2769230769230768,"d":"-0.28"}]
[14,152,"Bonham's","London","2005-04-07",{"v":27273,"d":"$27,273"},"Ring",2.04,"RB","F","VVS1","GIA",{"v":13369.117647058823,"d":"$13,369"},1.05,{"v":14037.573529411766,"d":"$14,038"},{"v":8300,"d":"$8,300"},{"v":17600,"d":"$17,600"},{"v":0.4715909090909091,"d":"0.47"},{"v":6619.992062165777,"d":"$6,620"},1.53,{"v":10128.587855113637,"d":"$10,129"},{"v":10000,"d":"$10,000"},{"v":0.5284090909090908,"d":"0.53"}]
[8,607,"Skinner","Boston","2004-06-15",{"v":5581,"d":"$5,581"},"Ring",1.14,"RB","G","VS1","GIA",{"v":4895.6140350877195,"d":"$4,896"},1.09,{"v":5336.2192982456145,"d":"$5,336"},{"v":8300,"d":"$8,300"},{"v":6500,"d":"$6,500"},{"v":1.2769230769230768,"d":"1.28"},{"v":6813.941565452092,"d":"$6,814"},1.53,{"v":10425.3305951417,"d":"$10,425"},{"v":10000,"d":"$10,000"},{"v":0.2769230769230768,"d":"0.28"}]
[11,443,"Sotheby's","NY","2006-10-24",{"v":6300,"d":"$6,300"},"Ring",1.53,"OE","I","SI1","GIA",{"v":4117.64705882353,"d":"$4,118"},1.02,{"v":4200,"d":"$4,200"},{"v":8300,"d":"$8,300"},{"v":5000,"d":"$5,000"},1.66,{"v":6972,"d":"$6,972"},1.53,{"v":10667.16,"d":"$10,667"},{"v":11000,"d":"$11,000"},{"v":0.6599999999999999,"d":"0.66"}]
[4,37,"Bonham's","London","2004-04-21",{"v":3260,"d":"$3,260"},"Ring",0.91,"RB","G","SI1","GIA",{"v":3582.4175824175823,"d":"$3,582"},1.09,{"v":3904.835164835165,"d":"$3,905"},{"v":8300,"d":"$8,300"},{"v":4000,"d":"$4,000"},{"v":2.075,"d":"2.08"},{"v":8102.532967032968,"d":"$8,103"},1.53,{"v":12396.875439560441,"d":"$12,397"},{"v":12000,"d":"$12,000"},{"v":1.0750000000000002,"d":"1.08"}]
[null,null,"Subject",{"$nulls":3},"Ring",1.53,"RB","G","VS1","GIA",null,"Price reference:",null,"Guide  ","Rap, Guide, Palmieri, etc.",null,null,"Total",{"v":61553.868213105095,"d":"$61,554"}]
[]
[{"$nulls":15},"What is the range?",{"$nulls":4},{"v":4444.522243460765,"d":"$4,445"}]
["Col A-L: Enter data for each comparable sale",{"$nulls":14},"What is the mode?",{"$nulls":4},{"v":10000,"d":"$10,000"}]
["Col M: Divide “$ Price” by “Ct Wt” (Col F/Col H) to convert transaction prices to Price/ct",{"$nulls":14},"What is the median?",{"$nulls":4},{"v":10276.95922512767,"d":"$10,277"}]
["Col N: Enter the adjustment to convert to constant dollars in Const $ Adj (US BLS Inflation Calculator)  ",{"$nulls":14},"What is the mean?",{"$nulls":4},{"v":10258.978035517515,"d":"$10,259"}]
["Col 0: Multiply “Price/Ct” x “Const $ Adj” (Col M x Col N) to find \"Constant Dollars /Ct\"",{"$nulls":14},"Least adjusted? (1 -ratio closest to \"0\")",{"$nulls":4},"6, 8"]
["Col P: Enter the per/carat reference price for Subject (from an industry matrix) in every row under Subj Ref",{"$nulls":14},"What is your opinion of value?",{"$nulls":4},{"v":10000,"d":"$10,000"}]
["Col Q: Enter the per/carat reference price for each comp (from the same matrix) under Com Ref $/Ct",{"$nulls":14},"What is your degree of confidence? A reasonable  "]
["Col R: Calculate Ratio of subject/comp by dividing “Subject Reference $/Ct” by “Comp Reference $/Ct” (Col P/Col Q)",{"$nulls":14},"      degree of jewelry appraisal probability or certainty?",{"$nulls":5},"Certainty"]
["Col S: Multiply each “Constant $/Ct” by “Ratio” (Col O x Col R) to get Adjusted $/Ct"]
["Col T: Enter the Subject Carat Weight into every row"]
["Col U: Multiply each “Adjusted $/Ct” times the “Subject Ct Weight” (Col S x Col T) to get each Adjusted Price—what"]
["          that comp would have sold for had its value attributes been the same as the subject "]
[]
[null,"Analysis"]
[null,"Col U: SORT data by adjusted price, low to high"]
[null,"Col V: It may be necessary to round the adjusted prices to a significant figure to find the Mode"]
[null,"Col W: Subtract “1” from each Ratio (Col R). The results closest to “1”, either higher or lower, are more reliable."]
[]
[null,"Of the sample selected: "]
[null,"What is the range?"]
[null,"What is the mode?"]
[null,"What is the median?"]
[null,"What is the mean?"]
[null,"Analyze the amount of adjustment in \"Ratio\""]
[null,"The comps that needed least adjustment are the most reliable. (Ratios closest + or - \"1\")"]
[]
[null,"Opinion of value"]
[null,"Do not use the mean as the indicated value. (Rejected by IRS.) Use the least adjusted, (above), the mode if there is one, and/or median."]
[null,"Formula to round to a significant figure is \"  =ROUND(COLrow, -3) \" for nearest thousand; -2 for nearest hundred might help establish a mode"]
[null,"What is your opinion of value? (Rounded to a significant figure.)"]
[null,"What is your degree of confidence: a reasonable degree of jewelry appraisal probability or certainty?"]
[null,"State your opinion of the (kind of value), to a reasonable degree of jewelry appraisal probability (more than 50% probable) or certainty (more than 80% certain). "]
[null,"Never state the % on an appraisal or in testimony. Probability and Certainty are legally understood measures."]
[]
[null,"Write a narrative explaining the similarities and differences between each comp and the subject, how and why you adjusted each"]
[null,"transaction price, your analysis of the results, and how you formed your opinion of value. "]
[null,"This is best done by breaking it into 2 parts, first comparing each comp to the subject, second explaining your analysis & opinion."]

{"sheet":"Analysis 2","index":5,"n_rows":57,"n_cols":23,"rows_kept":57,"flags":["formulas_cached_only"]}
[" #","Lot #","Vendor","City","Date","$ Price","Type","Ct Wt","Cut","Color","Clarity","Cert","Price  /Ct","Const $ Adj *","Const $/CT","Subj Ref $/Ct","Comp Ref $/Ct "," Ratio","Adj Price/CT","Subj  Ct Wt","Adj Price","Rounded","1 -Ratio"]
[2,632,"Hindman","Illinois","2006-05-21",{"v":1440,"d":"$1,440"},"Ring",{"v":1.2,"d":"1.20"},"OE","K/L","VS2 - 2nks",null,{"v":1200,"d":"$1,200"},1.02,{"v":1224,"d":"$1,224"},{"v":2480,"d":"$2,480"},{"v":2370,"d":"$2,370"},{"v":1.0464135021097047,"d":"1.05"},{"v":1280.8101265822786,"d":"$1,281"},{"v":1.8,"d":"1.80"},{"v":2305.4582278481016,"d":"$2,305"},{"v":2500,"d":"$2,500"},{"v":-0.04641350210970474,"d":"-0.05"}]
[5,640,"Christie's","NY","2006-07-11",{"v":4560,"d":"$4,560"},"Ring",1.95,"OE","I/J","SI",null,{"v":2338.4615384615386,"d":"$2,338"},1.02,{"v":2385.2307692307695,"d":"$2,385"},{"v":2480,"d":"$2,480"},{"v":3080,"d":"$3,080"},{"v":0.8051948051948052,"d":"0.81"},{"v":1920.575424575425,"d":"$1,921"},{"v":1.8,"d":"1.80"},{"v":3457.035764235765,"d":"$3,457"},{"v":3500,"d":"$3,500"},{"v":0.19480519480519476,"d":"0.19"}]
[3,46,"Christie's","NY","2004-09-14",{"v":3107,"d":"$3,107"},"Ring",{"v":1.8,"d":"1.80"},"OE","M+","VVS-VS",null,{"v":1726.111111111111,"d":"$1,726"},1.09,{"v":1881.4611111111112,"d":"$1,881"},{"v":2480,"d":"$2,480"},{"v":2050,"d":"$2,050"},{"v":1.2097560975609756,"d":"1.21"},{"v":2276.109051490515,"d":"$2,276"},{"v":1.8,"d":"1.80"},{"v":4096.996292682927,"d":"$4,097"},{"v":4000,"d":"$4,000"},{"v":-0.20975609756097557,"d":"-0.21"}]
[8,330,"Christie's","NY","2004-02-11",{"v":8963,"d":"$8,963"},"Ring",2.45,"OE","I/J/K","VVS-VS",null,{"v":3658.3673469387754,"d":"$3,658"},1.09,{"v":3987.6204081632654,"d":"$3,988"},{"v":2480,"d":"$2,480"},{"v":3650,"d":"$3,650"},{"v":0.6794520547945205,"d":"0.68"},{"v":2709.3968800670955,"d":"$2,709"},{"v":1.8,"d":"1.80"},{"v":4876.914384120772,"d":"$4,877"},{"v":5000,"d":"$5,000"},{"v":0.32054794520547947,"d":"0.32"}]
[1,57,"Christie's","NY","2004-09-14",{"v":3646,"d":"$3,646"},"Ring",{"v":1.2,"d":"1.20"},"OE","J/K/L","VVS-VS",null,{"v":3038.3333333333335,"d":"$3,038"},1.02,{"v":3099.1000000000004,"d":"$3,099"},{"v":2480,"d":"$2,480"},{"v":2370,"d":"$2,370"},{"v":1.0464135021097047,"d":"1.05"},{"v":3242.9400843881863,"d":"$3,243"},{"v":1.8,"d":"1.80"},{"v":5837.292151898735,"d":"$5,837"},{"v":6000,"d":"$6,000"},{"v":-0.04641350210970474,"d":"-0.05"}]
[{"$nulls":19},"Sum:",{"v":20573.6968207863,"d":"$20,574"}]
[{"$nulls":5},"Subject:","Ring",1.8,"OE","K","VS-2","-",null,"  Price Reference:",null,null,"Guide","Rap, Guide, Palmieri, etc."]
[null,"Col A-L: Enter data for each comparable sale",{"$nulls":12},"Of the comps selected: "]
[null,"Col M: Divide “$ Price” by “Ct Wt” (Col F/Col H) to convert transaction prices to Price/ct",{"$nulls":12},"What is the range?",{"$nulls":5},{"v":3531.8339240506334,"d":"$3,532"}]
[null,"Col N: Enter the adjustment to convert to constant dollars in Const $ Adj (US BLS Inflation Calculator)  ",{"$nulls":12},"What is the mode?",{"$nulls":5},"none"]
[null,"Col 0: Multiply “Price/Ct” x “Const $ Adj” (Col M x Col N) to find \"Constant Dollars /Ct\"",{"$nulls":12},"What is the median?",{"$nulls":5},{"v":4096.996292682927,"d":"$4,097"}]
[null,"Col P: Enter the per/carat reference price for Subject (from an industry matrix) in every row under Subj Ref",{"$nulls":12}," What is the mean?",{"$nulls":5},{"v":4114.73936415726,"d":"$4,115"}]
[null,"Col Q: Enter the per/carat reference price for each comp (from the same matrix) under Com Ref $/Ct",{"$nulls":12},"Which comps needed least adjustment? (1 -ratio closest to \"0\")",{"$nulls":5},"2,1"]
[null,"Col R: Calculate Ratio of subject/comp by dividing “Subject Reference $/Ct” by “Comp Reference $/Ct” (Col P/Col Q)",{"$nulls":12},"To what do you attribute the weak central tendency?",{"$nulls":5},"Unreliable grading of comps"]
[null,"Col S: Multiply each “Constant $/Ct” by “Ratio” (Col O x Col R) to get Adjusted $/Ct",{"$nulls":12}," What is your opinion of value?",{"$nulls":5},4000]
[null,"Col T: Enter the Subject Carat Weight into every row",{"$nulls":12},"What is your degree of confidence? A reasonable degree of jewelry ",{"$nulls":6},"Probability"]
[null,"Col U: Multiply each “Adjusted $/Ct” times the “Subject Ct Weight” (Col S x Col T) to get each Adjusted Price—what",{"$nulls":12},"    appraisal probability or certainty?"]
[null,"          that comp would have sold for had its value attributes been the same as the subject "]
[]
[null,"Analysis"]
[null,"Col U: SORT data by adjusted price, low to high"]
[null,"Col V: It may be necessary to round the adjusted prices to a significant figure to find the Mode"]
[null,"Col W: Subtract “1” from each Ratio (Col R). The results closest to “1”, either higher or lower, are more reliable."]
[]
[null,"Of the sample selected: "]
[null,"What is the range?"]
[null,"What is the mode?"]
[null,"What is the median?"]
[null,"What is the mean?"]
[null,"Analyze the amount of adjustment in \"Ratio\""]
[null,"The comps that needed least adjustment are the most reliable. (Ratios closest + or - \"1\")"]
[]
[null,"Opinion of value"]
[null,"Do not use the mean as the indicated value. (Rejected by IRS.) Use the least adjusted, (above), the mode if there is one, and/or median."]
[null,"Formula to round to a significant figure is \"  =ROUND(COLrow, -3) \" for nearest thousand; -2 for nearest hundred might help establish a mode"]
[null,"What is your opinion of value? (Rounded to a significant figure.)"]
[null,"What is your degree of confidence: a reasonable degree of jewelry appraisal probability or certainty?"]
[null,"State your opinion of the (kind of value), to a reasonable degree of jewelry appraisal probability (more than 50% probable) or certainty (more than 80% certain). "]
[null,"Never state the % on an appraisal or in testimony. Probability and Certainty are legally understood measures."]
[]
[null,"Write a narrative explaining the similarities and differences between each comp and the subject, how and why you adjusted each"]
[null,"transaction price, your analysis of the results, and how you formed your opinion of value. "]
[null,"This is best done by breaking it into 2 parts, first comparing each comp to the subject, second explaining your analysis & opinion."]
[null,"======================================"]
[null,"Comparison of comps to subject:"]
[null,"Comp 1 (compare and explain differences to subject, and explain significance) “Comp #1 weighed more than the subject but was lower in color."]
[null,"The larger size is more desirable, but the lower color is less desirable. I adjusted the transaction price down for weight and up for color to make "]
[null,"the comp equivalent to the subject”"]
[null,"Comp 2 (compare and explain differences to subject, and explain significance) etc. “Comp #2 weighed the same as the subject, but the color was "]
[null,"better while the clarity was lower. I adjusted the transaction price to compensate down for color and up for clarity.”"]
[]
[null,"Analysis of sales data:"]
[null,"In order to analyze the sales, I converted all transaction prices to $/carat"]
[null,"I adjusted the selling price of each comparator to 20xx dollars by . . . "]
[null,"Next, I adjusted the selling price of each comp for any differences in value attributes to make it equivalent to the subject"]
[null,"Etc, etc. . ."]

{"sheet":"Analysis 3","index":6,"n_rows":64,"n_cols":23,"rows_kept":64,"flags":["formulas_cached_only"]}
["#","Lot #","Vendor","City","Date","$ Price","TYPE","Ct Wt","Cut","Color","Clarity","Cert","Price/Ct","Const $ Adj","Const $/Ct","Subj Ref $/Ct","Comp Ref $/Ct","Ratio","Adj $/Ct","Subj Ct Wt","Adjusted Price","Rounded","1 -Ratio"]
[13,247,"Bonham's","London","2004-12-09",{"v":22727,"d":"$22,727"},"Ring T&Co",1.92,"RB","D","IF","GIA",{"v":11836.979166666668,"d":"$11,837"},1.09,{"v":12902.30729166667,"d":"$12,902"},{"v":13800,"d":"$13,800"},{"v":21120,"d":"$21,120"},{"v":0.6534090909090909,"d":"0.65"},{"v":8430.484878077654,"d":"$8,430"},2.02,{"v":17029.579453716862,"d":"$17,030"},{"v":17000,"d":"$17,000"},{"v":0.34659090909090906,"d":"0.35"}]
[6,147,"Sotheby's","Amsterdam","2006-06-27",{"v":5060,"d":"$5,060"},"Ring",1.01,"RB","G","VS1","GIA",{"v":5009.9009900990095,"d":"$5,010"},1.02,{"v":5110.09900990099,"d":"$5,110"},{"v":13800,"d":"$13,800"},{"v":6500,"d":"$6,500"},{"v":2.123076923076923,"d":"2.12"},{"v":10849.133282559023,"d":"$10,849"},2.02,{"v":21915.249230769226,"d":"$21,915"},{"v":22000,"d":"$22,000"},{"v":-1.123076923076923,"d":"-1.12"}]
[2,235,"Bonham's","London","2005-09-28",{"v":1636,"d":"$1,636"},"Ring-EF",0.62,"RB","D","VS2","GIA",{"v":2638.7096774193546,"d":"$2,639"},1.05,{"v":2770.6451612903224,"d":"$2,771"},{"v":13800,"d":"$13,800"},{"v":3500,"d":"$3,500"},{"v":3.942857142857143,"d":"3.94"},{"v":10924.258064516129,"d":"$10,924"},2.02,{"v":22067.00129032258,"d":"$22,067"},{"v":22000,"d":"$22,000"},{"v":-2.942857142857143,"d":"-2.94"}]
[14,152,"Bonham's","London","2005-04-07",{"v":27273,"d":"$27,273"},"Ring",2.04,"RB","F","VVS1","GIA",{"v":13369.117647058823,"d":"$13,369"},1.05,{"v":14037.573529411766,"d":"$14,038"},{"v":13800,"d":"$13,800"},{"v":17600,"d":"$17,600"},{"v":0.7840909090909091,"d":"0.78"},{"v":11006.733790106953,"d":"$11,007"},2.02,{"v":22233.602256016045,"d":"$22,234"},{"v":22000,"d":"$22,000"},{"v":0.21590909090909094,"d":"0.22"}]
[17,570,"Skinner","Boston","2004-12-14",{"v":28200,"d":"$28,200"},"Ring +2 3.18 tw",2.52,"RB","F","VS1","GIA",{"v":11190.47619047619,"d":"$11,190"},1.09,{"v":12197.61904761905,"d":"$12,198"},{"v":13800,"d":"$13,800"},{"v":15225,"d":"$15,225"},{"v":0.9064039408866995,"d":"0.91"},{"v":11055.969974196578,"d":"$11,056"},2.02,{"v":22333.059347877086,"d":"$22,333"},{"v":22000,"d":"$22,000"},{"v":0.09359605911330049,"d":"0.09"}]
[8,607,"Skinner","Boston","2004-06-15",{"v":5581,"d":"$5,581"},"Ring",1.14,"RB","G","VS1","GIA",{"v":4895.6140350877195,"d":"$4,896"},1.09,{"v":5336.2192982456145,"d":"$5,336"},{"v":13800,"d":"$13,800"},{"v":6500,"d":"$6,500"},{"v":2.123076923076923,"d":"2.12"},{"v":11329.204048582997,"d":"$11,329"},2.02,{"v":22884.992178137654,"d":"$22,885"},{"v":23000,"d":"$23,000"},{"v":-1.123076923076923,"d":"-1.12"}]
[11,443,"Sotheby's","NY","2006-10-24",{"v":6300,"d":"$6,300"},"Ring",1.53,"OE","I","SI1","GIA",{"v":4117.64705882353,"d":"$4,118"},1.02,{"v":4200,"d":"$4,200"},{"v":13800,"d":"$13,800"},{"v":5000,"d":"$5,000"},2.76,{"v":11592,"d":"$11,592"},2.02,{"v":23415.84,"d":"$23,416"},{"v":23000,"d":"$23,000"},{"v":-1.7599999999999998,"d":"-1.76"}]
[9,211,"Sotheby's","Amsterdam","2006-06-27",{"v":7952,"d":"$7,952"},"Ring Fr.",1.24,"RB","G","VVS2","GIA",{"v":6412.903225806452,"d":"$6,413"},1.02,{"v":6541.1612903225805,"d":"$6,541"},{"v":13800,"d":"$13,800"},{"v":7300,"d":"$7,300"},{"v":1.8904109589041096,"d":"1.89"},{"v":12365.482987185153,"d":"$12,365"},2.02,{"v":24978.27563411401,"d":"$24,978"},{"v":25000,"d":"$25,000"},{"v":-0.8904109589041096,"d":"-0.89"}]
[7,164,"Bonham's","London","2003-10-02",{"v":6953,"d":"$6,953"},"Ring & sap",1.02,"RB","E","VVS2","GIA",{"v":6816.666666666667,"d":"$6,817"},1.12,{"v":7634.666666666668,"d":"$7,635"},{"v":13800,"d":"$13,800"},{"v":8200,"d":"$8,200"},{"v":1.6829268292682926,"d":"1.68"},{"v":12848.58536585366,"d":"$12,849"},2.02,{"v":25954.142439024392,"d":"$25,954"},{"v":26000,"d":"$26,000"},{"v":-0.6829268292682926,"d":"-0.68"}]
[5,16,"Sotheby's","NY","2007-10-24",{"v":7200,"d":"$7,200"},"Ring T&Co",1.01,"RB","G","IF","GIA",{"v":7128.712871287129,"d":"$7,129"},1.02,{"v":7271.287128712872,"d":"$7,271"},{"v":13800,"d":"$13,800"},{"v":7600,"d":"$7,600"},{"v":1.8157894736842106,"d":"1.82"},{"v":13203.12662845232,"d":"$13,203"},2.02,{"v":26670.315789473687,"d":"$26,670"},{"v":27000,"d":"$27,000"},{"v":-0.8157894736842106,"d":"-0.82"}]
[19,149,"Dawson/Nye","MorrisPlns","2007-01-24",{"v":112100,"d":"$112,100"},"Ring & bag .80",9.85,"RB","K","SI1","GIA",{"v":11380.710659898477,"d":"$11,381"},1,{"v":11380.710659898477,"d":"$11,381"},{"v":13800,"d":"$13,800"},{"v":11280,"d":"$11,280"},{"v":1.2234042553191489,"d":"1.22"},{"v":13923.209849875797,"d":"$13,923"},2.02,{"v":28124.88389674911,"d":"$28,125"},{"v":28000,"d":"$28,000"},{"v":-0.22340425531914887,"d":"-0.22"}]
[{"$nulls":19},"Sum:",{"v":257606.94151620063,"d":"$257,607"}]
[null,null,"Subject",{"$nulls":3},"Ring",2.02,"RB","G","VS1","GIA",null,"Data Source:",null,"Guide","Rap, Guide, Palmieri, etc."]
[]
[null,"Col A-L: Enter data for each comparable sale",{"$nulls":12},"What is the range?",{"$nulls":5},{"v":11095.304443032248,"d":"$11,095"}]
[null,"Col M: Divide “$ Price” by “Ct Wt” (Col F/Col H) to convert transaction prices to Price/ct",{"$nulls":12},"What is the mode?",{"$nulls":5},{"v":22000,"d":"$22,000"}]
[null,"Col N: Enter the adjustment to convert to constant dollars in Const $ Adj (US BLS Inflation Calculator)  ",{"$nulls":12},"What is the median?",{"$nulls":5},{"v":22885,"d":"$22,885"}]
[null,"Col 0: Multiply “Price/Ct” x “Const $ Adj” (Col M x Col N) to find \"Constant Dollars /Ct\"",{"$nulls":12},"What is the mean?",{"$nulls":5},{"v":23418.81286510915,"d":"$23,419"}]
[null,"Col P: Enter the per/carat reference price for Subject (from an industry matrix) in every row under Subj Ref",{"$nulls":12},"Which comps needed least adjustment? (1 -ratio closest to \"1\")",{"$nulls":5},{"v":22333,"d":"$22,333"},17]
[null,"Col Q: Enter the per/carat reference price for each comp (from the same matrix) under Com Ref $/Ct",{"$nulls":12},"What is your opinion of value?",{"$nulls":5},{"v":22000,"d":"$22,000"}]
[null,"Col R: Calculate Ratio of subject/comp by dividing “Subject Reference $/Ct” by “Comp Reference $/Ct” (Col P/Col Q)",{"$nulls":12},"What is your degree of confidence? A reasonable  "]
[null,"Col S: Multiply each “Constant $/Ct” by “Ratio” (Col O x Col R) to get Adjusted $/Ct",{"$nulls":12},"      degree of jewelry appraisal probability or certainty?",{"$nulls":5},"Certainty"]
[null,"Col T: Enter the Subject Carat Weight into every row"]
[null,"Col U: Multiply each “Adjusted $/Ct” times the “Subject Ct Weight” (Col S x Col T) to get each Adjusted Price—what"]
[null,"          that comp would have sold for had its value attributes been the same as the subject "]
[]
[null,"Analysis"]
[null,"Col U: SORT data by adjusted price, low to high"]
[null,"Col V: It may be necessary to round the adjusted prices to a significant figure to find the Mode"]
[null,"Col W: Subtract “1” from each Ratio (Col R). The results closest to “1”, either higher or lower, are more reliable."]
[]
[null,"Of the sample selected: "]
[null,"What is the range?"]
[null,"What is the mode?"]
[null,"What is the median?"]
[null,"What is the mean?"]
[null,"Analyze the amount of adjustment in \"Ratio\""]
[null,"The comps that needed least adjustment are the most reliable. (Ratios closest + or - \"1\")"]
[]
[null,"Opinion of value"]
[null,"Do not use the mean as the indicated value. (Rejected by IRS.) Use the least adjusted, (above), the mode if there is one, and/or median."]
[null,"Formula to round to a significant figure is \"  =ROUND(COLrow, -3) \" for nearest thousand; -2 for nearest hundred might help establish a mode"]
[null,"What is your opinion of value? (Rounded to a significant figure.)"]
[null,"What is your degree of confidence: a reasonable degree of jewelry appraisal probability or certainty?"]
[null,"State your opinion of the (kind of value), to a reasonable degree of jewelry appraisal probability (more than 50% probable) or certainty (more than 80% certain). "]
[null,"Never state the % on an appraisal or in testimony. Probability and Certainty are legally understood measures."]
[]
[null,"Write a narrative explaining the similarities and differences between each comp and the subject, how and why you adjusted each"]
[null,"transaction price, your analysis of the results, and how you formed your opinion of value. "]
[null,"This is best done by breaking it into 2 parts, first comparing each comp to the subject, second explaining your analysis & opinion."]
[null,"======================================"]
[null,"Comparison of comps to subject:"]
[null,"Comp 1 (compare and explain differences to subject, and explain significance) “Comp #1 weighed more than the subject but was lower in color."]
[null,"The larger size is more desirable, but the lower color is less desirable. I adjusted the transaction price down for weight and up for color to make "]
[null,"the comp equivalent to the subject”"]
[null,"Comp 2 (compare and explain differences to subject, and explain significance) etc. “Comp #2 weighed the same as the subject, but the color was "]
[null,"better while the clarity was lower. I adjusted the transaction price to compensate down for color and up for clarity.”"]
[]
[null,"Analysis of sales data:"]
[null,"In order to analyze the sales, I converted all transaction prices to $/carat"]
[null,"I adjusted the selling price of each comparator to 20xx dollars by . . . "]
[null,"Next, I adjusted the selling price of each comp for any differences in value attributes to make it equivalent to the subject"]
[null,"Etc, etc. . ."]

{"sheet":"Analysis 4","index":7,"n_rows":63,"n_cols":23,"rows_kept":63,"flags":["formulas_cached_only"]}
[" #","Lot #","Vendor","City","Date","$ Price","Type","Ct Wt","Cut","Color","Clarity","Cert","Price/Ct","Const $ Adj","Const $/Ct","Subj Ref /Ct","Comp Ref $/Ct","Ratio","Adj $/CT","Subj Ct Wt","Adjusted Price","Rounded","1 -Ratio"]
[8,340,"Christie's","NY","2004-10-13",{"v":4780,"d":"$4,780"},"Deco",{"v":9,"d":"9.00"},"OE, rose","H-M+","VS-SI",null,{"v":531.1111111111111,"d":"$531"},1.09,{"v":578.9111111111112,"d":"$579"},null,null,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},1.53,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"}]
[9,45,"Christies","NY","2004-09-14",{"v":2629,"d":"$2,629"},"Deco",{"v":3.5,"d":"3.50"},"OE ","K-M","VS-SI",null,{"v":751.1428571428571,"d":"$751"},1.09,{"v":818.7457142857143,"d":"$819"},null,null,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},1.53,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"}]
[11,263,"Christie's","NY","2004-09-14",{"v":1912,"d":"$1,912"},"Deco",{"v":1.6,"d":"1.60"},"OE & sapp","H-I","VVS-VS",null,{"v":1195,"d":"$1,195"},1.09,{"v":1302.5500000000002,"d":"$1,303"},null,null,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},1.53,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"}]
[12,649,"Butterfield","SF","2004-06-29",{"v":2938,"d":"$2,938"},"Deco",{"v":2.5,"d":"2.50"},"Mq & em",{"$nulls":3},{"v":1175.2,"d":"$1,175"},1.09,{"v":1280.968,"d":"$1,281"},null,null,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},1.53,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"}]
[18,513,"Skinner","Bost","2004-09-21",{"v":1763,"d":"$1,763"},"Deco",{"v":2,"d":"2.00"},"OE, s/c",{"$nulls":3},{"v":881.5,"d":"$882"},1.09,{"v":960.835,"d":"$961"},null,null,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},1.53,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"}]
[19,640,"Skinner","Bost","2004-06-15",{"v":9988,"d":"$9,988"},"Deco ++",18.65,"OE, s/c",{"$nulls":3},{"v":535.549597855228,"d":"$536"},1.09,{"v":583.7490616621985,"d":"$584"},null,null,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},1.53,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"}]
[21,228,"Weschlers","DC","2007-04-21",{"v":1400,"d":"$1,400"},"Deco",2.65,"OE & em",{"$nulls":3},{"v":528.3018867924528,"d":"$528"},1,{"v":528.3018867924528,"d":"$528"},null,null,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},1.53,{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"}]
[{"$nulls":19},"Sum:",{"$error":"#DIV/0!"}]
[{"$nulls":4},"Subject:","Brooch","Deco","6.22+","OE","K-O","VVS2-VS2",null,null,"Reference Source: ",null,null,"(Rap, Guide, Palmieri, etc.)"]
[{"$nulls":7},"4 lg dias",null,"35 melee @ 3.22 t.w. I-K, VS"]
[null,null,"Photo of the subject brooch is on Page 21 of your manual. ",{"$nulls":6},"See the actual comps on my website at www.jewelry-appraisal.com/edu.html."]
[null,null,"The above figures are based on carat weight only and give a wide range of quality. See if you can refine the selection of comps and/or adjustments to get more reliable results."]
[null,null,"You might want to modify this spreadsheet by putting in a column for Melee."]
[null,"Col A-L: Enter data for each comparable sale",{"$nulls":13},"What is the range?",{"$nulls":4},{"$error":"#DIV/0!"}]
[null,"Col M: Divide “$ Price” by “Ct Wt” (Col F/Col H) to convert transaction prices to Price/ct",{"$nulls":13},"What is the mode?"]
[null,"Col N: Enter the adjustment to convert to constant dollars in Const $ Adj (US BLS Inflation Calculator)  ",{"$nulls":13},"What is the median?"]
[null,"Col 0: Multiply “Price/Ct” x “Const $ Adj” (Col M x Col N) to find \"Constant Dollars /Ct\"",{"$nulls":13},"What is the mean?",{"$nulls":4},{"$error":"#DIV/0!"}]
[null,"Col P: Enter the per/carat reference price for Subject (from an industry matrix) in every row under Subj Ref",{"$nulls":13},"Which comps needed least adjustment? (1 -ratio closest to \"0\")"]
[null,"Col Q: Enter the per/carat reference price for each comp (from the same matrix) under Com Ref $/Ct",{"$nulls":13},"What is your opinion of value?"]
[null,"Col R: Calculate Ratio of subject/comp by dividing “Subject Reference $/Ct” by “Comp Reference $/Ct” (Col P/Col Q)",{"$nulls":13},"What is your degree of confidence? A reasonable  "]
[null,"Col S: Multiply each “Constant $/Ct” by “Ratio” (Col O x Col R) to get Adjusted $/Ct",{"$nulls":13},"      degree of jewelry appraisal probability or certainty?"]
[null,"Col T: Enter the Subject Carat Weight into every row"]
[null,"Col U: Multiply each “Adjusted $/Ct” times the “Subject Ct Weight” (Col S x Col T) to get each Adjusted Price—what"]
[null,"          that comp would have sold for had its value attributes been the same as the subject "]
[]
[null,"Analysis"]
[null,"Col U: SORT data by adjusted price, low to high"]
[null,"Col V: It may be necessary to round the adjusted prices to a significant figure to find the Mode"]
[null,"Col W: Subtract “1” from each Ratio (Col R). The results closest to “1”, either higher or lower, are more reliable."]
[]
[null,"Of the sample selected: "]
[null,"What is the range?"]
[null,"What is the mode?"]
[null,"What is the median?"]
[null,"What is the mean?"]
[null,"Analyze the amount of adjustment in \"Ratio\""]
[null,"The comps that needed least adjustment are the most reliable. (Ratios closest + or - \"1\")"]
[]
[null,"Opinion of value"]
[null,"Do not use the mean as the indicated value. (Rejected by IRS.) Use the least adjusted, (above), the mode if there is one, and/or median."]
[null,"Formula to round to a significant figure is \"  =ROUND(COLrow, -3) \" for nearest thousand; -2 for nearest hundred might help establish a mode"]
[null,"What is your opinion of value? (Rounded to a significant figure.)"]
[null,"What is your degree of confidence: a reasonable degree of jewelry appraisal probability or certainty?"]
[null,"State your opinion of the (kind of value), to a reasonable degree of jewelry appraisal probability (more than 50% probable) or certainty (more than 80% certain). "]
[null,"Never state the % on an appraisal or in testimony. Probability and Certainty are legally understood measures."]
[]
[null,"Write a narrative explaining the similarities and differences between each comp and the subject, how and why you adjusted each"]
[null,"transaction price, your analysis of the results, and how you formed your opinion of value. "]
[null,"This is best done by breaking it into 2 parts, first comparing each comp to the subject, second explaining your analysis & opinion."]
[null,"======================================"]
[null,"Comparison of comps to subject:"]
[null,"Comp 1 (compare and explain differences to subject, and explain significance) “Comp #1 weighed more than the subject but was lower in color."]
[null,"The larger size is more desirable, but the lower color is less desirable. I adjusted the transaction price down for weight and up for color to make "]
[null,"the comp equivalent to the subject”"]
[null,"Comp 2 (compare and explain differences to subject, and explain significance) etc. “Comp #2 weighed the same as the subject, but the color was "]
[null,"better while the clarity was lower. I adjusted the transaction price to compensate down for color and up for clarity.”"]
[]
[null,"Analysis of sales data:"]
[null,"In order to analyze the sales, I converted all transaction prices to $/carat"]
[null,"I adjusted the selling price of each comparator to 20xx dollars by . . . "]
[null,"Next, I adjusted the selling price of each comp for any differences in value attributes to make it equivalent to the subject"]
[null,"Etc, etc. . ."]