Jump to content

Excel Spreadsheet template


Recommended Posts

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........
:twitch:

 

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.

  • Like 1
Link to comment
Share on other sites

I like the exported spreadsheet, at least as a master inventory list. The only thing  I (my opinion) would change is eliminate the internal ID number column (don't know what that is) and would re-arrange the columns slightly, with more important data on the left.

 

Of course, it's all stuff I can manually do, but then every time I download a new list, I lose what I have already done.

 

.......In no way am I trying to sound ungrateful for what is offered here. I'm not. It's a pretty awesome tool to have.

Link to comment
Share on other sites

An excel spreadsheet is an awesome way to keep track of your inventory and everyone's thinking process is different. 
I think we should all share some pointers and pick and choose the ones that makes sense to you.

From there you can create your own personal spreadsheet.

 

One thing that is important for me is the purchase price.
I have several columns that helps me determine the correct purchase price, such as, tax, shipping if any, vip points, gc, coupons, etc.... 
Without the correct purchase price, i can not determine exact profits.

Link to comment
Share on other sites

My inventory sheet shows

Set #

Set Name

Quantity

Date bought

Date sold

Price paid

Price sold

Fees for selling

Net profit/loss

So you have a row for each purchase? I was thinking of having one row per set and a column that averages the purchase price. This would be to keep the sheet shorter so I have one row for 20 sets I've bought rather than 20 rows. And for selling...having a separate sheet also have a column for eBay/bl/amazon/other and then it just find the net price from locating the avg purchase price on the buy sheet.

Link to comment
Share on other sites

I only keep a spreadsheet for sales, and my columns pretty much look like this:

 

Item : Purchase Price : Shipping : Sale Price : Date of Sale : Calculated EBay Fees : Calculated Paypal Fees : Total Cost : Total Profit : Percentage Profit

 

I might create new columns for EBay Item Listing Number and Paypal Transaction Number.

 

You can average or sum any of these at the bottom.

 

I'd like to be able to track Sale Price and Profit in the Brickfolio and then have a view for "Active Inventory" or "Sold Inventory".  This might come along with one of the Brickfolio enhancements.

Link to comment
Share on other sites

Here's what I use for my stuff. Any column in red needs the information added manually. Any column header in black has a formula that does the calculation:

 

https://www.dropbox.com/s/f7k5czfn704cuu8/LEGO%20Template.xlsx

 

Couple things...far to the right hand side is where I keep track of individual sales (I have mine broken up 2012, 2013 and 2014). It's very important to enter each sale individually because every time you add a sale to a set's row, the "count" function is used to deduct  exactly 1 from remaining inventory. So if you sell 2 same sets in one transaction and try to enter it that way, your final numbers will be off because the spreadsheet thinks you only sold 1 item, not 2. Hope that makes sense.

 

After you've entered in sales data, the profit, etc will also be updated automatically in the middle columns.

 

The tabs near the bottom are used for:

  • ROI
  • keeping track of  $ amount spent to purchase sets on a specific date (I used to just update the Main tab's cost column, but it's easier to track this way)
  • quantity of sets purchased on a specific date (again, same as above...Main tab's columns will adjust based on info entered here)
  • supply and operating costs
  • storage unit info (my way of knowing what I have on hand when selling)
  • and finally Brickpicker data which can be manually copied and pasted into that tab from the export of your brickfolio. The only thing you have to do is take out the "-1" after each set number to allow the brickpicker columns on the Main tab to find the info under the Brickpicker tab and calculate them correctly  (unrealized gain, etc)....or if you don't want to do that each time, you can add a -1 (or -2, etc...whatever the number is for the brickfolio export) to set #'s in the rest of the spreadsheet...that way would make more sense since it results in no extra work like I currently have to do everytime I update the brickpicker tab to the newest month's numbers

Again, LEGO Set # is the ID that connects all the information across the different tabs, so they all need to be exactly  identical (whether that's with a -1 or without) for the formulas to work correctly.

  • Like 4
Link to comment
Share on other sites

I like the exported spreadsheet, at least as a master inventory list. The only thing  I (my opinion) would change is eliminate the internal ID number column (don't know what that is) and would re-arrange the columns slightly, with more important data on the left.

 

Of course, it's all stuff I can manually do, but then every time I download a new list, I lose what I have already done.

 

.......In no way am I trying to sound ungrateful for what is offered here. I'm not. It's a pretty awesome tool to have.

 

There is a reason that is there for future features, but you can delete if it bothers you that much.

Link to comment
Share on other sites

Paypal -> History -> Download History -> Select Date Range -> Output (Comma Delimited - All Activity)

 

Boom. Sales Data. Copy/Paste into Worksheet1.

 

From that point, if you write the proper formulas, the rest of your financial documents, and pretty much any other document, can be automatically updated with the most current info each time new Raw Data is pasted into Worksheet1. The results can easily be double checked with your Financial Summary within Paypal -> History -> Reports. It's a large amount of work at first, but saves a lot of work in the long run...and TBQH, at some point, you should really be selling so much that it is not worth your time to manually key in your data :( Sorry, we can't post our spreadsheets for proprietary reasons :/

 

My other business involves generating Market Research Reports...and we constantly import raw data into Excel in order to prepare it for analysis. Hence, the need to create efficient ways to read and interpret your data.

Link to comment
Share on other sites

So, you guys are not using Google Sheets? as long as you keep it simple, it has all the functionality you need, with an added plus that you can access it (from smartphone) while staring at all those 50%+ off deals in target trying to remember how many of the set you have already...

 

(I know Excel spreadsheets could be pushed to Microsoft's cloud, but it is more cumbersome I think to access from some of the phone platforms)

  • Like 2
Link to comment
Share on other sites

So, you guys are not using Google Sheets? as long as you keep it simple, it has all the functionality you need, with an added plus that you can access it (from smartphone) while staring at all those 50%+ off deals in target trying to remember how many of the set you have already...

 

(I know Excel spreadsheets could be pushed to Microsoft's cloud, but it is more cumbersome I think to access from some of the phone platforms)

I had a Windows phone for a while and the Office integration on that thing was pretty good.  One of the few redeeming features, and then one day it started to randomly tell me it couldn't access my Skydrive.

Link to comment
Share on other sites

I had a Windows phone for a while and the Office integration on that thing was pretty good.  One of the few redeeming features, and then one day it started to randomly tell me it couldn't access my Skydrive.

That's why I mentioned Google Sheets first. You would be able to access them from Windows Phone as well (Google seems to have better handle on Cloud so far, and if Google has outage, then it becomes too well known for Google to just ingnore)

Link to comment
Share on other sites

  • 5 months later...

Hey guys, I thought i would share my investment tracker spreadsheet, which i think is pretty nifty if i don't say so myself :)

 

https://drive.google.com/file/d/0B-FkThHORDEicDBRMWkyNU54Vmc/edit?usp=sharing

 

Basically, I love the Brickfolio, but it was just bothering me that some of the new sets were showing up as -100%, as I couldn't view the data accurately, I decided to make my own tracker in excel using the exported CSV file from Brickfolio.

 

The formulas are all set up, so if you change the the numbers in the quantity, purchase price, or MSRRP columns, everything else including all the percentages cascades down accordingly.

 

Every fortnight, I scour eBay and update the average market prices per set, this provides me with really accurate indication on the growth % of my inventory.

 

The second sheet added is just full inventory list, which i'll update once i start selling (i haven't done so yet).

 

I hope this helps others too here.

 

Thanks!!

  • Like 1
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

The same for me....I sell before the highest and I remain with the lowest...so I'm not overestimating the gain

Link to comment
Share on other sites

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.

I always enter the exact # of sets in the brickfolio and my spreadsheet (eg 5 10220s = 5 diff entries).

When it comes updating the spreadsheet for sets sold, I always update the rows that were entered first (those sets purchased the longest time ago). So I use FIFO for accounting purposes.

If the boxes are similar like my 9465 The Zombies, I just ship the 1st one my hands grab. If I'm shipping sets where box condition varies (e.g. 7888s), I always pick the box that resembles the actual box condition.

Sent from an iPhone using the Brickpicker app

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.


×
×
  • Create New...