A while back, I mentioned that some of the people I worked with had mad excel skills, and some people wrote in wanting to see examples. I finally found something I could post here that doesn’t violate any NDAs or contain sensitive company information: a teammate’s personal Asscher cut diamond pricing model (right click to download and open or save). [Updated June 28, 2007: unlocked version of spreadsheet now available.]
I realize this is a bit out of left field, so here’s a little background. Someone recently joined our group who’d built an Excel-based model to price out how much an Asscher cut diamond should cost, based on several factors. He did this so that he could get the right engagement ring for his fiancée, where “right” was defined as the biggest bang for the buck (though I have to say it’s a gorgeous ring, and I’m no diamond afficionado). After putting the model to good use, he posted publicly on a diamond forum, and I got his permission to post it here as well.
This guy knew nothing at all about diamonds before he started, but he visited all the diamond stores — Tiffany, Cartier, etc. — to gather data and learn about diamonds, and in particular, the Asscher cut. (This cut has apparently gained a lot of popularity in recent years due to its appearance in celebrities’ engagements and the media.) After getting all the data, he used Excel to run a multivariate regression to find the most statistically significant determinants of price, and out of that, he created a pricing model.
The model uses 10 variable as inputs: cut, color, clarity, carat, polish, symmetry, debth [sic], table, florescence, and L/W (length-to-width ratio). You can see the impact of changing each variable on the price by using the dropdown menu items.
I’m posting his model here in case it’s useful to other people, and as an example of the power of Excel in the hands of an expert. Actually, pricing models such as this one can be used in all sorts of business applications, from pricing products to creating should-cost models (e.g. how much a product or service from a supplier should cost, based on various inputs).
Pricescope.com, the forum where he posted his model has received many views, but only one comment of appreciation, so if you have feedback, I’m sure he’d be interested. But beware that I am absolutely not a diamond expert in any way (I don’t even own one), so if you have questions about them sparklies, you’ll be better off visiting a diamond forum like Pricescope than posting your question here :)
Enjoy!
***************************************************
Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss
***************************************************
Reader
Password? (Doesn’t really help as a learning tool if it’s locked).
Ricemutt
Sorry – I didn’t realize the sheet was protected. I meant to show only the front portion, not the actual data sources. I’ll see if I can get the password.
paul in 02144
not as fascinating as I had hoped, but maybe that’s because diamonds don’t excite me much.
my project deals with analyzing a lot of back-end data (who’s project doesn’t?) and I’ve found excel Pivot Tables to be quite useful. Also, Advanced Filtering is everything you do by hand, almost ;-)
dong
I’m a bit of an excel junkie (though not quite as much as I’ve gotten older). I think in the end to really unlock the power in Excel, it’s about using Visual Basic. In a lot of ways, Excel is just interface for the VBA backend connections into databases.
Anyhow, it looks like your friend got quite a Rock….
Personal Finance Guide 101
Oh yeah 503 views and one comment on your friend Dale’s post. Well I am not an expert in excel but I could say he did a great job and it a lot of work…
Pilot
You’re right. It is gorgeous. When my daughter was in High School, I taught her how to do her first simple regression (over the dinner dishes) by using her own assessments of the quality of colleges she was considering and regressing them against cost.
Our deal emerged from that conversation and model. So long as she chose a college with quality above the CI of the regression line for a given cost, I’d pay for it all. She chose an excellent public college way above the line. I saved a bundle and she graduated with no debt — and even got to spend a semester abroad.
Asscher Cut Diamond
Wow! Pretty amazing spreadsheet. It is one of those thing that you ask why there would be such a need but once you see it it makes complete sense.
Dale
Glad people liked the model and my good friend Leslie posted it. I have since build one for Radiant & princess cuts for friends. These are super helpful when you are not a diamond expert, but are looking to make your commitment “official.” I didn’t know anything and didn’t want to take a dealer at his word. Numbers/ data levels the playing field a bit.
Enjoy!
Dale