I've read some blogs and have some very basic Excel spreadsheet experience. But, my spreadsheet looks pretty awful and isn't very user friendly.

 

It would be awesome if someone with a really nice, Lego specific, Excel spreadsheet wanted to share their blank template for those on this site........

 

I really like the way you can export the brickfolio data to Excel, but I like to customize my spreadsheet and add sales data, which makes importing new data impossible.

Out of curiousity, how do you account the sets sold? I mean... if you have multiples of a set with different purchase prices... do you use FIFO or LIFO?

When I sell a set I have multiples of I always sell the less beautiful box and write off the set which has the highest bu-in price. So my remaining invested amount becomes lower. I hope you guys know what I mean.

Correct me if I'm wrong, does FIFO and LIFO mean, first-in and first-out and last in first out.

Also if you have 5 of the same sets in various conditions and different purchase prices, technically shouldn't you be averaging the purchase price out over the 5 sets because in the end really your ROI is over the lot of 5 sets? I'm thinking giving 1 specific set out of the lot of 5 a specific amount$, and you are defining an ROI for that specific set, the numbers are being skewed in your favour no matter what???

Correct me if I'm wrong, does FIFO and LIFO mean, first-in and first-out and last in first out.

Also if you have 5 of the same sets in various conditions and different purchase prices, technically shouldn't you be averaging the purchase price out over the 5 sets because in the end really your ROI is over the lot of 5 sets? I'm thinking giving 1 specific set out of the lot of 5 a specific amount$, and you are defining an ROI for that specific set, the numbers are being skewed in your favour no matter what???

 

It is in my favour yes. I would just call it creative accounting. I do not scam anything. The end result is always the same (profit wise). 

 

What I do is:

 

I have 5 pcs of the same set with different purchase prices and different box conditions. When I notice it is profitable to sell this particular set I will take the set with the least beautiful or perfect box condition. Make a picture of it and put it for sale with the actual original clear picture so people know what they buy. I keep the best ones for later. When I sell the set I will "write off" the most expensive set in my books. If I had to pay shipping costs with the purchases of my 5 sets I will write that off too from the profit. At the end I will for example have the two best sets left for the cheapest purchase price. So my investment money in this set is as low as possible. The profit I earned I can then use to reinvest in other sets.   

 

It is not FIFO or LIFO but a creative combination of both.

It is in my favour yes. I would just call it creative accounting. I do not scam anything. The end result is always the same (profit wise).

What I do is:

I have 5 pcs of the same set with different purchase prices and different box conditions. When I notice it is profitable to sell this particular set I will take the set with the least beautiful or perfect box condition. Make a picture of it and put it for sale with the actual original clear picture so people know what they buy. I keep the best ones for later. When I sell the set I will "write off" the most expensive set in my books. If I had to pay shipping costs with the purchases of my 5 sets I will write that off too from the profit. At the end I will for example have the two best sets left for the cheapest purchase price. So my investment money in this set is as low as possible. The profit I earned I can then use to reinvest in other sets.

It is not FIFO or LIFO but a creative combination of both.

I haven't sold anything yet and this was something that hadn't crossed my mind. I like your creativeness here and I just may apply it to my practices when I get there.

Here is a vanilla version of my Google spreadsheet.  Please feel free to borrow and use any ideas, and provide feedback if you have any.

Thanks!

https://docs.google.com/spreadsheets/d/11UTvui13wEzrEblD2B-EKgR9F-VF3f7ctT2VoxnbGzQ/edit?usp=sharing

That's slick, thanks for sharing!

I've added several new features to my Google Drive spreadsheet for tracking inventory and sales.

 

1.  Supplies.  Enter the type of box needed for each item.  Enter your beginning box inventory, and then any subsequent supply purchases.  The spreadsheet will then track how many are used and how many you need.  Never accidentally run out of boxes again!

 

2.  CAGR calculations.  By item and overall.

 

3.  Quick Item lookup.  Enter the set # for details on how many you have in inventory/sold/profit, etc.

 

4.  Goals vs. actual sales/income.

 

5.  Breakdown of Amazon vs. ebay sales.

 

6.  Piece count.

 

Let me know if you have questions/suggestions for the next version!

 

 

Enjoy!

 

https://docs.google.com/spreadsheets/d/1NCmgY9nNxF8bjzuMthZArR5JmySAaJtFbr7fAgI2egU/edit?usp=sharing

Edited March 5, 201510 yr by xeeeej

Sweet!  Thanks xeeeej.  I used the last one that you posted, and kinda did a merge between it and my own sheet.  I'll have to post it one of these days.

 

I greatly expanded your Stats page.  I now do stats by year, as well as overall.  I've expanded a couple of other things in there, as well.  But I did incorporate a lot of the stuff you put into yours to help expand mine.

 

Looking forward to taking a look at your sheets, to see how you've expanded on them.

You're welcome!

I would like to track items across multiple years, but honestly the spreadsheet gets bogged down once you put so many items on it with all the formulas, etc.  So I doubt it would ever be able to sustain a multi-year version.  I just save a new version for each quarter or year, and can go back to review those for historical purposes.  I'd be interested to see what you cooked up, though!

I'm up to four years in the spreadsheet.  And it is starting to get pretty easy.  But performance isn't too made, mostly because I keep it in Google Docs.  

 

I break out different segments, though, which makes it even "bigger."  Right now, I have tabs for Sets, Minifigs, Set Parting, Bulk Lots, and Bulk Parts.  I'm about to break out Polybags into their own tab.  I also double track Mixels (they are tracked on the Sets tab, but I also track their individual purchases on a separate tab so when I'm putting together sets from multiple buys, I have a source for their individual prices that then get combined into the entry on Sets page - e.***., if I buy two of one color one day, and the third another day, at a different location and price, I can combine those prices into one entry for that particular color).

Very impressive work! Certainly an asset for those that want a very realistic image of their 'business'.

I'm up to four years in the spreadsheet.  And it is starting to get pretty easy.  But performance isn't too made, mostly because I keep it in Google Docs.  

 

I break out different segments, though, which makes it even "bigger."  Right now, I have tabs for Sets, Minifigs, Set Parting, Bulk Lots, and Bulk Parts.  I'm about to break out Polybags into their own tab.  I also double track Mixels (they are tracked on the Sets tab, but I also track their individual purchases on a separate tab so when I'm putting together sets from multiple buys, I have a source for their individual prices that then get combined into the entry on Sets page - e.***., if I buy two of one color one day, and the third another day, at a different location and price, I can combine those prices into one entry for that particular color).

 

That sounds intense!  Looking forward to seeing a demo.

Time to start to create one excel sheet for myself too

I recently made an excel sheet to track all of this. Gives item irr, total portfolio irr, summary cashflows of revenues, inventories, etc, and realized profits by month. If there is an appetite for it I'll link it.

Sure!  I'm always looking for new ideas for mine.

I can always geek out over a good spreadsheet!

^ link away.