Keeping Track of your Lego Investments

Tracking your Lego investments is one of the most important topics that are discussed on the BrickPicker forums.  Since I see quite a few questions popping up on the forums about how to keep track of your Lego investments, I figured I’ll write a blog article about it and maybe some of you will find it helpful.

Terminology

First we need to get our terminology straight as it will help us to pick out appropriate tools for the job.  There are few ways you can treat your Lego business.

1. Long term investment-You buy complete brand new sets hold them for a period of more than one year and sell them at a premium after those sets are discontinued.
2. Short terms investment- You buy new sets now on sale and at discount and hold them for a period of less than one year and sell them during holiday rush at a premium.  I would lump arbitrage investment here as well, where you buy sets in US for cheap and sell them on eBay international to take advantage of currency rate and pricing differentials.
3. Flipping Used Sets- You buy used sets, usually in lots, rehab them to completion put them together, (The fun part if you are an AFOL and/or have Little Legomaniacs around the house), take pictures and sell them on eBay.
4. Parting out, PAB, Lego pounds-Well... the title says it all.

Now the question is what can we use to keep financial record of our business?  We need to examine each individual point and see where it falls and what is important to us.

Long Term investment -What is important?  Our cost basis, our annualized return on investment, record of lots purchased and timing of those lot purchases.  It is quite likely that we will be purchasing same items over the course of year or two for different prices before we sell it so our calculations need to account for that blending as well as holding periods.

Short Term investment and flipping used sets - We can treat it one of two ways as a short term investment or as a retail business that carries inventory. For the purpose of this paragraph let’s treat it as a short term investment.  Annualized rate of return is no longer important we are holding it for less than a year.  We basically can resort to simple cost and profit calculation. Revenue-Cost=Profit.

Parting out, PAB, Lego pounds - In my humble opinion if you are heavily involved in this type of investment you are better off treating it as a retail business with inventory. So what is the difference?  In investment scenario you are concerned with cost basis and sale price. In a business scenario to be successful you need to be concerned with properly matching revenue and expenses.  Let me illustrate the difference.  I am simplifying some things from GAAP and tax accounting but the illustration will hold true.

Short term investment

Business Accounting-double sided bookkeeping

So if you are heavily involved in parting out and bulk bricks you might as well treat your flipping as a retail business model.  If you don’t do a lot of parting out then treating flipping as short term investment is easier.

Choosing appropriate tools for the task

Now we are ready to discuss the tools and I will go backwards on the list.   If you choose a retail model I strongly recommend investing into QuickBooks pro.  It natively supports inventory, double sided accounting, general ledgers profit and loss reports and much, much more.  At $250 retail and there are many coupons floating around, it is very affordable and keeps you sane.  Also comes tax time it can provide you with more opportunities for deductions. As a side note if you make significant amount of money from this activity and live in US. I suggest establishing LLC or Corp and a separate business checking account.  Cost for registering Corp. or LLC is nominal, it does not require an attorney can be done online and it protects you legally.  In the most basic sense Corp and you are two separate legal entities and you are generally not personally liable for misdeeds of the corp.  There are of course exceptions to this rule but they involve fraud, negligence and certain poorly executed contractual obligations. You don’t have to quit your day job to register LLC or Corp.

Short Term Investing - Excel and some intermediate formulas is all you need.  Let’s start with layout.  For my template I chose a logical left to right vs mathematical format.  I buy the set if it is used I rehab it and incur cost along the way I sell it and incur selling costs (eBay, PayPal commissions.)  For the sake of my blood pressure I chose not to track packing costs separately I do incorporate them in my sale’s price  or I inflate shipping a little bit to cover my supplies.  Trying to keep them separate in excel will very quickly turn into running your business as a business with inventory and I can live with slightly inflated performance view of my investments.  We also need at least two tabs on the spreadsheet one for tracking details one for summary reporting. Once you’re done with your headers go ahead and click this   button on your excel Home Tab toolbar.  This will add named ranges to your spreadsheet and will allow you to extend your ranges by Tabbing into the next row.  I’m assuming you are using 2007 and up excel version. First 6 columns are self-explanatory.  Purchase Price per 1 set is actually a weighted average formula so I must digress to explain what weighted average is and why I choose to use it.  

Digression begins:

I buy a used lot of six sets for a $100.  

  If I use simple average method to determine cost per each set it comes out to be $16.6 each.  Now let’s say I need to spend $3  on bricklink  to rehab 8016 I look at the market price of 12.73 and say to myself that I’m already in a hole for this set and I’ll just throw it in a trash and I’ll be wrong and lose profit in the process.    My cost per set should be allocated based on that set’s market value weight in my purchased lot.  Here’s how we do it.

1.  Determine Set’s percent contribution/weight to market value of the lot (Set Market value/Total market value of the lot)
2. Allocate Purchase cost to the set.  (Lot total cost X Set Weight from step one)

Based on that my 8036 cost is now only (17.47/248.47) X 100= $7.03.  Spending $3 to rehab it still leaves me with a healthy profit. 

Digression ends

Back to purchase price per 1 set formula it looks like this rewritten with column headers vs cell references. ((Set Market price*Quantity)/SUMPRODUCT((Entire column where my lot numbers are="Lot 1")*(Set market price entire column)*(Lot Cost entire column)))*(Lot cost cell/Quantity cell). Sumproduct formula explanation is in order.  In its very basic form sumproduct multiplies ranges of columns and delivers sum of multiplications.

Sumproduct of these four cells is 13  2*2+3*3.  

I use it here to test conditions.  SUMPRODUCT((Entire column where my lot numbers are="Lot 1") will either return 1 or 0 for each row because it’s either my lot 1 or not from there the rest of the formula will either be 0 or the value since 0 multiplied by any value is 0.  With this formula all you have to do is populate your total lot cost for every row in that lot and adjust the formula slightly for each lot to have excel automatically calculate weighted average price per set in a lot.

Total purchase price per quantity =Quantity*Purchase price per 1 set

Lot number -we need to populate for every row to have the sumproduct formula work for us.  Simple copy paste does the trick.

Lot cost -we need that to have our weighted average price per set formula to work

Set Market price - comes straight from BP Maybe premium members will be allowed to connect via ODBC to it.  Hint Hint.

Bricklink cost=Cost to rehab the set.

Sale price- Well… your sales price

Qty sold is your qty sold

Gross Revenue- IFERROR(Sale price*Qty Sold,0)+Shipping charged-Shipping Paid. I put error handling in here because I use Sale price column to write in status of my sets i.e. Listed, waiting for parts etc.  With this iferror formula it will return zero if I don’t have a valid sales price in that row.  I also have shipping spread added to my gross revenue because I’m still experimenting with pricing to cover my packaging costs.

Shipping charged and Paid were already explained.

EBay insertion fee- you can use that to keep track of those.

eBay FV fee -IFERROR(0.1*(Sale Price*Qty sold+Shipping Charged),0) again error handling here and your standard 10 percent eBay commission on sales+shipping.

PayPal Fee - FERROR(0.029*(Sale Price*Qty Sold+shipping charged)+0.3*Q,0) very straightforward  PayPal fee of 2.9% + 30cents per transaction. Shipping in both eBay FV formulas and PayPal Fee formulas are user populated.  I am assuming that shipping could be different for any two items sold from the same lot and you can use simple arithmetic to add to the shipping.

Net Revenue- IFERROR(Sales Price*Quantity+Shipping Charged-Shipping Paid-eBay Insertion Fee-eBay FV fee-PayPal Fee,0).  I incorporated this column to have a little fun with analytics when it is time for me to think about opening eBay store.

Total Cost- SUM(Total purchase price for qty,Bricklink cost, EBay FV fee, PayPal Fee)

Profit is your total gross revenue-Total cost. Profit Margin or more accurately net profit margin is IFERROR(Profit/(Purchase price for qty+Bricklink cost),0).   With all this out of the way  here’s a screen shot of the populated details

Entirely useless and confusing for analysis.  That’s why we need a summary tab and a pivot table in it. To create a pivot table  go to your insert tab and click pivot table button

You will see popup like the one below, Click on the button on the right and select the range where your detail data is.  And just follow the prompts from there.

You will end up with a blank pivot table and list of available fields

At this point you can start to experiment by dragging fields from the top right section of the minipivot into the different areas of the bottom right section. The populated pivot table  in the screenshot summarizes my performance per lot purchased. I can get as detailed or as high level as I want and have multiple pivots side by side for quick dash boarding analysis with just a few clicks. Ok we are done with short term investment tracking.  

Long Term investing

Using Excel for long term investment tracking becomes an exercise in advanced excel and quite frankly is not worth the effort.  Most of us already have personal accounting software like Quicken Premiere that supports investment tracking natively.

Go to your tools menu and then select add account option.  You will see this screen.  

Click on Brokerage under Investing and retirement. Once the next screen comes up click Advanced setup link at the bottom of the screen.

Select  I want to enter my transactions manually on the screen that appears and click next.

Name your account and click next

Next screen you can skip or enter information on it if you have startup cash that you are allocating towards your purchases

I suggest you skip this screen and start fresh.

Select No on this screen

Done! Click Finish

  Now find your newly created account it should be in investing section of the left sidebar and click on it. We have a clean slate time to enter some transactions

  Double click on the first line and you’ll see the popup below. Change Enter Transaction drop down list to Buy-Shares Bought And populate your information.  You also have an option here to select your cash account or credit card as source of funds for this purchase. 

And then hit enter/new button

My ticker is my set number. Hit Next

  Quicken will protest and we’ll have to tell it to shut up and hit next

Here you can actually assign asset class to your Lego portfolio.

Fire brigade belongs to Large Cap in my opinion, Dolphin cruiser would probably be Mid Cap. And hit Done

.  

Rinse and repeat until you’re done with your transactions.  Of course you can type this info in directly into ledger screen if you wish.

Pretty soon your ledger screen will look busy.

Click on holdings button

Now right click on your investment  and select price history from submenu, click on New and enter new price.  I suggest doing it once a month to have a good historical representation.

And That is all folks!!!.