New workbook I would like someone to look at. Opinions welcomed.

mrpaseo

So I am putting together a workbook for my parents. They (Like me) are new to PMs and I have a better understanding of excel. I am trying to make this user friendly to those with little excel experience. Here are some screen shots.



This image shows the first tab. This tab is where we will build our stack. The shaded blocks have formulas and we are expected to fill in only the white blocks (Throughout the workbook). Hopefully, the while blocks are self explanatory though I do have a few notes throughout the workbook to help the user understand.

The second tab will be used when preparing to buy bullion (This workbook is made for Silver).



The top automatically updates each time the workbook is opened or when (ctrl + alt + f5) are pressed at the same time.

Scrolling down you will find this.



and this.



In Tennessee we pay taxes on any bullion/coin purchase, the local shop told me they charge 3% over spot plus taxes for certain items.


I would appreciate any input or changes you think would be helpful. I can send this to anyone for your evaluation and use. If you want a copy, please send your e-mail address to me in a private message.

Thanks for looking,
Ray


NOTE: I already changed GOIN to GOING, that said, if you see any miss spelled words please point them out.

mrpaseo

Took me three times to get the pics right... I am getting rusty...lol.

Weiss

That's awfully detailed. "Going rate" is something that's going to fluctuate rapidly and has to be entered manually. I wouldn't have that unless you really like data entry.

Total bullion might be better titled "weight" in either grams or troy ounce.

Consequently, I wouldn't have a column for "current spot", either. I just have it in a field off to the side (P2, for example). Then reference it in the equations of the individual cells (P2*D3). That way you just change one field and the entire column (or any other column you reference) is instantly updated based on that one field change.

I like the "item" field, but I'd add "misc info" or something like that. So you can individualize the items with serial numbers, slab numbers, etc.

Finally, I keep duplicate tabs for each PM. Just cut and paste that whole page (without data added) and save it as a different tab, name each tab by the metal (silver, gold, platinum). That way I don't have to worry about different metal spot values on the same page.

mrpaseo

<< <i>That's awfully detailed. "Going rate" is something that's going to fluctuate rapidly and has to be entered manually. I wouldn't have that unless you really like data entry. >>



I will add a note that this changes daily, I put this in because I did not want people (My parents) thinking they were so deep underwater and could put the going rate in to see the current value of their Silver.



<< <i>Total bullion might be better titled "weight" in either grams or troy ounce. >>



I like this and will make the changes



<< <i>Consequently, I wouldn't have a column for "current spot", either. I just have it in a field off to the side (P2, for example). Then reference it in the equations of the individual cells (P2*D3). That way you just change one field and the entire column (or any other column you reference) is instantly updated based on that one field change. >>



Anywhere the current spot is, there is a formula bringing it there, there is also a third tab that automatically downloads the current spot price ever time the document is opened so these are all done automatically



<< <i>I like the "item" field, but I'd add "misc info" or something like that. So you can individualize the items with serial numbers, slab numbers, etc. >>



I like this and will make the changes



<< <i>Finally, I keep duplicate tabs for each PM. Just cut and paste that whole page (without data added) and save it as a different tab, name each tab by the metal (silver, gold, platinum). That way I don't have to worry about different metal spot values on the same page. >>



Currently I am building this for Silver, I will update to also track Gold and Platinum (Maybe Palladium and Rhodium) once Silver is solidified

Thank you very much for the input,
Ray

mrpaseo

Updates completed as noted above.

Ray

jmski52

I have a format that keeps track of the necessary information for tax purposes.

Once I got that part refined and working, I added some other features that allow me to get a snapshot of the total value based on ounces so that when I update the daily spot price, the total reflects everything I own.

Keep in mind that when a piece is worth more than its "bullion value", I don't really update those changes much because it would be too time-consuming. I only do that when I get curious about a real total. This means that my instant total is almost always less than the real value, but that's ok for me for my tax purposes.

The cost basis doesn't change, so once an item is added I don't have to worry until I sell it. Then I complete the rest of the additional cost items and calculate the net proceeds. After I do that, I cut 'n paste that row to a different spreadsheet for that year's sales. This way, I have an ongoing tax record and the end of the year is less hassle.

derryb

I don't see a need for spot, premiums or going rates in the spreadsheet I use.

Might as well make a spreadsheet that can be used at tax time. I make two identical spreadsheets, one for current inventory and one for current year sales. Here are my column headers:
date (of item), description, date purchased, purchase price, date sold, sell price, PP fee, shipping fee, ebay fee, grading fee, total fees, net income, profit

I have excel formulas keep totals at the bottom of applicable columns and use formulas to calculate the total fees, net income and profit. Whenever I add a new purchase I just insert a new line and keep everything listed by date or in the case of non-coins by bar/round size (weight). With over 12 pages of line items I can go right to an item by keeping them organized in this order. If I buy multiples (500 coin monster box or 20 coin tube) I list it as the single item I purchased. If I break it up to sell, I just create more line items at that time and adjust purchase price for each line item. For example if I sell a tube from the box I turn a single line item for the box into 25 line items for each of the tubes. If i sell five coins from a tube, i create one line item for five coins and 15 line items for individual coins. This way I can cut and paste a line item of what was sold and leave the remainder in the inventory.

Whenever i have all the numbers plugged in after a sale, I just cut and paste the line item into an identical "current year sales sheet." Nice thing about formulas is that they adjust totals automatically after doing this. The sales sheet tells me everything I need to know at tax time for that year.

at the bottom of my inventory spread sheet i keep a running total of silver, gold and platinum ounces. I add to or subtract from this running total manually whenever I buy or sell. To know the current spot price value of my inventory i just multiply these numbers by current spot.

For graded coins my description includes cert numbers. This will come in hand in case of theft. I keep a digital photo file of all items and file the pics by last five digits of the cert number.

I keep all of my coin data and files on a flash/thumb USB drive that can go with me when I travel and it also protects me in case of a hard drive crash or computer failure. Excel is unforgiving with mistakes, best to save often and if you find your self in a mistake you just can't fix, exit the program at re-enter data from where you last saved.