Hello all!
I have been gone recently because I am in the middle of a home re-model… taking up lots of time ( and money )
I wanted to pitch a “new” idea in tracking collections.
I am currently using a nice spreadsheet I created in excel to keep track of my purchases and how my “investments” are doing. I also use the spreadsheet to track my percentage complete.
This is where I ran into a problem.
I have a mercury dime collection that is almost complete, I am only missing three coins ( can you guess what they are? )
1916-D
1921
1921-D
According to my excel spreadsheet I have 96% of the coins, but that is not altogether true. A standard calculation of percentage really is not applicable, as it would “value” a 1916-D and a 1945-S as the same 9 as far as percentage is concerned ) so I have devised a different way to calculate percentage of completion.
For this example I will use my commemorative type set collection, as it is the only one I have completed.
First I create a list of all of the coins in the collection, in this case the classic commemoratives are 50 coins.
I list each one out by name, in the next column I put an average market price and round up to the nearest $25.00 range. I am using grey sheet bid prices and make a price assumption based on what I am willing to pay for a coin ( some coins jump a tremendous amount between grades )
After I am done with the price I give the coin a “point” value by dividing the price by 25, basically a coin gets 1 point for every $25.00 it takes to purchase it.
In the next column I use to track what coins I have purchased, and enter the coins points I have earned.
Now I can take the points I have earned, divide it by the total points, and get a weighted percentage complete.
This gives a more accurate percentage complete, and fits into the old rule “buy the key coins first”
Any ways, I hope this makes sense and would be of help to some of you all. I have attached a zip file with an example spreadsheet.
Some notes on the example:
This is not my collection, just an example. Try playing around with the numbers!
It shows how if you purchase a Hawaiian Sesquicentennial first, you actually have 18% of the collection complete, which makes sense considering that it is the most expensive coin in a whole raft of expensive coins. This makes sense to me…
To better illustrate what coins are “owned” in the collection I have highlighted them.
Anywho, I am also considering working in a points system based on grade, it would make it more complex but would account for “filler” coins.
In my example a Grant memorial coin is worth 6 points for an ms-64 example, lets say I purchase an AU coin as a filler, it may only be worth 3 points. Even if my collection was complete as far as number of coins go it would not mark my collection as 100% complete because I have fillers…
I may be complicating the idea a little to much right now
I wanted to put this out there as an idea I had and to see what you guys and gals thought.
As a side not this just shows how powerful using excel is to track your collection, you can do so much more then the software you purchase. Plus you learn skills and tricks using excel that translate into the job market
Cheers!
Threaded Mode
