Excel Spreadsheet template

[iahawks550]

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.

[eklein03]

I didn't know you could export data from the site into excel.  My excel spreadsheet is a simple 4 part income statement, balance sheet, retained earnings, and journal entry done by month.  Let me know if it sounds helpful to you.

[iahawks550]

http://www.brickpicker.com/bpms/brickfolio_data.cfm

 

That's the link to export your brickfolio info. I love how it exports the pricing data.

 

It would be great to see anyone's spreadsheet. I don't know if this site allows for attachment of such files to a post or not.

 

Edit: It appears attaching Excel files aren't allowed.

[eklein03]

Cool.  Thanks.  Just pm me your email and ill send you my format.

[Ed Mack]

If some generous members would donate their LEGO excel spreadsheets, maybe Jeff can get a better idea what members are looking for when he updates the current one later this year.

[iahawks550]

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.

[Miami Bomb Squad]

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.

[iahawks550]

My inventory sheet shows

 

Set #

Set Name

Quantity

Date bought

Date sold

Price paid

Price sold

Fees for selling

Net profit/loss

[Brickbuilder.g]

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.

[Miami Bomb Squad]

That was one of the issues i had with the current BP portfolio, Avg Pricing.
I have to do this manually every time. 

[exciter1]

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.

[LegoManiacc]

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.

[Jeff Mack]

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.

[imirish11]

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.

[surfin.ei]

Very Useful Stuff Ty

[imirish11]

[Edit: Removed. Editor keeps converting text to hyperlink even after I unselect the Unlink Toggle Button]

[No More Monkeys]

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)

[fuzzy_bricks]

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.

[No More Monkeys]

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)

[DoNotInsertIntoMouth]

I wouldn't ever use Google sheets for this guys. Big reason why - Security.

 

I don't want my records for anything being pulled. I use Excel, I encrypt and password protect them and I use dropbox so I can access them from any PC.

[No More Monkeys]

If it hit the wire, NSA most likely has it already. If it was encrypted, then NSA has it for sure...

 

But yeah, I see your point, once your volume is high enough, it becomes similar to any business - you don't want to provide an easy way to access your business data

[iguana]

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!!

[Ciglione]

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.

[glucapg]

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

[jaisonline]

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