Sports Card Inventory In Excel

[BALxORIOLESx]

Hello,

I am calling on the helpful Blowout community again in hopes that some of you can help me with ideas on keep track of inventory, sales, profit, etc. of your sports cards.

Currently I keep up with my inventory in Excel.

I use the following column headers:

1. Card Name
2. Team
3. Sport
4. Auto or Non-Auto
5. Year
6. Numbered To
7. Currently For Sale? Y or N
8. Purchase Price
9. Sold For Price
10. eBay Fee (If Sold on eBay)
11. Total Profit (Sold For Price - Bought For Price - eBay Fee)
12. % Profit

I would like to turn this spreadsheet into some sort of dashboard with charts, graphs, etc.

I am pretty advanced with Excel as I know pretty much all formulas and just want to see if any of you all have any type of dashboard already laid out?

Thanks Blowout!

[rngrdanny22]

I do something similar. Word of advice, avoid Excel. Had mine in one, computer crashed, and had to start over.

I use Google Sheets now.

[HOFAutoRookies]

I basically do the same, except I keep track of the seller name or eBay id incase anything goes wrong.

Also, I haven't had an issue with excel. I recommend using macros when entering your data and to always have backup copies. My inventory list is on iCloud, One Drive, Drop Box and my computer itself lol

[Skipscards]

Love Excel and your list looks good. Maybe add company (Topps, Donruss, etc).

[BALxORIOLESx]

Do any of you use the data from those columns to make a dashboard type look?

[BALxORIOLESx]

Originally Posted by rngrdanny22

I do something similar. Word of advice, avoid Excel. Had mine in one, computer crashed, and had to start over.

I use Google Sheets now.

I had planned on transferring my Excel Spreadsheet into Google Sheets this weekend.

[Keyser Soze]

I don't have a massive collection, so I make my rows tall enough to have a column for scans of each card. Also, if anything ever happened to me, my wife knows where to find that spreadsheet and the scans would help her determine which cards were which so that she could value them accordingly.

[Keyser Soze]

I much prefer Excel. I just save the file on my computer AND on a thumb drive. I may lose the thumb drive, and my computer may crash, but it's pretty unlikely that both would happen

[imbluestreak23]

Example Spreadsheet Columns and descriptions.

I set it up this way so that when grades pop, I can copy and paste into Excel (temporary add a column for "card"). Grading is an overall value at the end of the order for the cost of the order + shipping to PSA. I eventually average this on a per card basis to better reflect accurate results in my ROI pivot table.


Line Item QTY Serial # Grade Card Cost Grading Sale Expected
23 1 42953693 GEM MINT 10 2011 Bowman's Best Prospects BBP9 Mike Trout $40.51 $121.88
24 1 42953694 GEM MINT 10 2011 Bowman's Best Prospects BBP9 Mike Trout $33.50 $107.80
25 1 42953695 GEM MINT 10 2011 Bowman's Best Prospects BBP9 Mike Trout $33.50 $102.08
26 1 42953696 GEM MINT 10 2011 Bowman's Best Prospects BBP9 Mike Trout $36.99 $95.92
27 1 42953697 GEM MINT 10 2011 Bowman's Best Prospects BBP9 Mike Trout $36.50 $92.84
28 1 42953698 GEM MINT 10 2011 Bowman's Best Prospects BBP9 Mike Trout $36.75 $91.08
29 1 42953699 MINT 9 2017 Panini Prizm 269 Patrick Mahomes II Disco Prizm $107.15 $119.75
30 1 42953700 GEM MINT 10 2017 Panini Prizm 269 Patrick Mahomes II Disco Prizm $95.00 $224.40
31 1 42953701 GEM MINT 10 2017 Panini Prizm 269 Patrick Mahomes II Disco Prizm $85.66 $189.20
32 1 42953702 GEM MINT 10 2017 Panini Prizm 269 Patrick Mahomes II Light Blue Prizm $127.88 $550.00
33 1 42953703 GEM MINT 10 2017 Panini Donruss Optic the Rookies 7 Patrick Mahomes II $34.50 $90.20
34 1 42953704 GEM MINT 10 2017 Panini Donruss Optic the Rookies 7 Patrick Mahomes II $24.50 $90.20
35 1 42953705 GEM MINT 10 2017 Panini Donruss Optic the Rookies 7 Patrick Mahomes II $35.49 $64.72
36 1 42953706 GEM MINT 10 2017 Panini Donruss Optic 177 Patrick Mahomes II Blue $82.27 $221.77
37 1 42953707 GEM MINT 10 2017 Panini Donruss Optic 177 Patrick Mahomes II Pink $42.94 $99.00
38 1 42953708 GEM MINT 10 2017 Panini Donruss Optic 177 Patrick Mahomes II Pink $43.75 $98.98
39 1 42953709 MINT 9 2017 Panini Donruss Optic 177 Patrick Mahomes II Pink $40.00 $67.86

Example Financial Calculations Conducted:


COGS Grading Expense Sales Receivables
$xx,xxx.xx $xx,xxx.xx $xx,xxx.xx $xx,xxx.xx

Assets $xxx,xxx.xx
Liabilities $xxx,xxx.xx
Profit w/Fees $xxx,xxx.xx
ROI xx.xx%


GEM 10 Rate xxxx 67.07%
MINT 9 Rate xxx 29.34%
NEAR MINT-MINT xx 36 3.59%


Folder Structure:
1) Excel Spreadsheet for each PSA order
2) Master Spreadsheet for all graded orders
---Tabs for each year to track and trend and a comprehensive running tab to show overall value add over time

Definitely a good way to keep organized. Would love to make this more "mineable" to do research based on product type, player, etc, but this operationally is the most feasible way without it being too labor intensive.

[Hollywood42]

I use a cloud SQL database for my PC, not Excel, but same concept. I have columns for year, set, subset, first name, last name, all players names (if card has more than 1 player on it), card number prefix, card number, serial number, grader, grade, date added, "display name", and flags for auto/relic/patch/manufactured relic/RC/numbered/HOF/one of one/SP or variation/graded. I like to think I have info pretty well covered But I plan to add a note section to store stories/things I'd like to remember about each card, maybe a field for purchase price

I also store references to pictures of each card. Currently have scans of fronts only, but long term would like to add the backs

I built my own website for all of these things to work with each other to display my cards in a filterable way, complete with all the info you could want to know about it. I've been working on this for years, combo of development of the site itself, as well as getting all of my PC cards scanned and entered into the database. It's been a real long project and something I've wanted to reveal the finished product of for a long time. Haven't done so yet, but I am planning to do so in early January

[ALBASKETBALL]

I keep track of BGS and PSA grades and cost associated with my submissions - this allows me to track my submission success and add that cost to total invested - or determine the monetary difference between buying graded vs raw + my own submission

I also track promos on Ebay (Ebay Bucks and discounts)

[BALxORIOLESx]

I just had an idea!

I am going to work on something for the next couple days or so and let you know what I come up with.

[powejr01]

I do something like this as well. My two pieces of advice are:

1. Use Google Sheets if you want to be able to edit anywhere. I like it because I use some computers that don't have Excel and if I want to update my data I can do so with only an internet connection. Excel has more functionality if you want to get fancy.

2. Instead of tracking the eBay and shipping cost for each individual sale, just use an assumption for what your net revenue is after all selling fees (Paypal, shipping, eBay) as a percentage of eBay sale price. If you're a high volume seller, this will save you hours. If you only sell a few things here and there, it's less important.

[imbluestreak23]

Dropbox!!

Pay for the 2GB. I put everything there, even things I probably shouldn’t.

After 3 MacBook crashes, I needed to level up my game

[chiefsncards275]

Originally Posted by rngrdanny22

I do something similar. Word of advice, avoid Excel. Had mine in one, computer crashed, and had to start over.



I use Google Sheets now.

What I do is use a flash drive and make sure all my scans/excel sheets are saved on that. If something happens to the computer then you’re still good


Sent from my iPhone using Tapatalk

[Whodey14]

I use google sheets, have the app and can access it easily on the go. Might be possible with excel also but I have had no reason to check.

[coltsfan23]

I've a lot of different data broken down in a variety of ways for mining/research purposes, but my two main spreadsheets are 1) yearly sales/expenses/P&L broken down by each specific transaction, and 2) current inventory by sport with costs & current market value.

FYI: Don't go around saying that 'you're pretty advanced with Excel and know pretty much all the formulas'. Do you utilize macros consistently? Are you able to use Excel without ever utilizing a mouse or clicking? Just a few random things that don't even cover the surface. Not even someone who uses Excel on a daily basis for 30+ years should be stating that. You're not even close.

[BALxORIOLESx]

Originally Posted by coltsfan23

I've a lot of different data broken down in a variety of ways for mining/research purposes, but my two main spreadsheets are 1) yearly sales/expenses/P&L broken down by each specific transaction, and 2) current inventory by sport with costs & current market value.

FYI: Don't go around saying that 'you're pretty advanced with Excel and know pretty much all the formulas'. Do you utilize macros consistently? Are you able to use Excel without ever utilizing a mouse or clicking? Just a few random things that don't even cover the surface. Not even someone who uses Excel on a daily basis for 30+ years should be stating that. You're not even close.

Lmao. Get off my thread with that negativity. People are searching for advice on something not looking to be criticized. I actually have several spreadsheets that have macros set up that I use for all sorts of reports for work that take a single click of a button and it saves over 100 different customer based quarterly reports in PDF format in a specified file path. I am seeking out advice on a layout of a dashboard; not your shenanigans. Get out of my thread with this non-sense.

I did not say I was an expert. I said I was advanced (as in I am not a beginner), but am not an expert. Beginners know how to do basic vlookups, countifs, sumifs, etc. Advanced users would be able to do the aforementioned skillset with other things thrown in such as macros, dashboards, drop downs, index/match functions to return images off a certain text criteria, etc.

Goodnight.

[Y2Hood]

I might have to look into Google sheets. Excel keeps trying to divide all serial #'d cards when I put x/y in that column. Finally figured out I should just write out "x of y."

[IgnatiusJReilly]

I like excel and love using pivot tables for various analysis. For example, I can use my table of 1956 Topps cards to get quick distributions of grades, analyze completion status, etc.


Sent from my iPhone using Tapatalk

[Keyser Soze]

Originally Posted by Y2Hood

I might have to look into Google sheets. Excel keeps trying to divide all serial #'d cards when I put x/y in that column. Finally figured out I should just write out "x of y."

Reformat that entire column to be "text" and it will leave your "divided by" commands alone.

[bloomie6]

I use excel but then I’ll email it to myself in gmail. This way I have a web based backup

[BALxORIOLESx]

Wanted to get some of your opinions on something that I made.... This is in the early stages and I wanted to get some advice on possible column titles for this specific sheet only. It is a "Team Dashboard".

I have all of my data in the background (such as: product, year, player, color, team, auto?, #'d to, bought for, sold for, profit ($), and profit (%).

So this team dashboard that I built I have 6 columns that I wanted to know what you think would be valuable data for this dashboard. I will use lookups based on the team data.

I know this is not going to show me much but I do like a cool looking visual

[BALxORIOLESx]

I also have this excel "dashboard" made for me to use a dropdown list of product, players, lots, etc. That I have and by selecting that item from the drop down it will tell me what box and row I have it located in. ?

Does anyone have any ideas of anything else I can do to make this look cooler and prettier? as I said before I am all about a visual!!!

[mainerunr]

Originally Posted by rngrdanny22

I do something similar. Word of advice, avoid Excel. Had mine in one, computer crashed, and had to start over.

I use Google Sheets now.

or, back your stuff up?