Jump to content
  • Sign in to follow this  

    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

    Date Set Transaction Total Profit
    01/01/2013 Fire Brigade Buy 2@100 -200
    02/01/2013 Fire Brigade Sell 1 @150 -50(-200+150)
    03/01/2013 Fire Brigade Sell 1@ 200 150(-50+200)

     

    Business Accounting-double sided bookkeeping

      Transaction 1 -Buy  2 Fire Brigades at $100 on 01/01/2013 Transaction 2-Sell 1 Fire Brigade at  $150 Transaction 3-Sell 1 Fire Brigade at $200

    Result of Transaction

    Accounts Payable(or Cash out the door) ($200) Accounts Receivable(or Cash into your bank account) $150 Accounts Receivable(or Cash into your bank account) $200
    Inventory $200 Inventory 100 I sold a FB that was originally bought for a 100 I now only have one set left in inventory valued at 100 Inventory 0, nothing is left.
    Revenue 0 Revenue 150 Revenue 200
      Cost of Goods Sold 100 Cost of Goods Sold 100
      Profit 50 Profit 100

     

    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. Excel Titles Once you’re done with your headers go ahead and click this  Format as table button 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.  

    Set# Name Market Price
    7753 Pirate Tank $ 26.73
    8036 Separatist Shuttle $ 17.47
    8633 Speedboat Rescue $ 30.87
    8016 Hyena Droid Bomber $ 12.73
    8037 Anakin's Y-wing Star fighter $ 37.07
    7676 Republic Attack Gunship $ 123.60
     

    Total

    248.47

      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.

    2 2
    3 3

    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

    Excel 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

    1  

    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.

    2

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

    3  

    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. 4  

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

    5  

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

    6  

    Name your account and click next

    7  

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

    8  

    I suggest you skip this screen and start fresh.

    9  

    Select No on this screen

    10  

    Done! Click Finish

    11

      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

    12  

      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

    13  

    My ticker is my set number. Hit Next

    14

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

    15  

    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

    . 16  

    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.

    17

    Click on holdings button

    18

    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.

    19  

    And That is all folks!!!.    

    Sign in to follow this  


    User Feedback

    Recommended Comments

    Nice article, thanks. Is it possible to somehow add "Theme" to the lego set. So one would be able to sort or analyze cost and gain total on specific themes ex. Star Wars. I would be very interested in using Quicken if one can use it as a stock list and sort by Lego Theme. Do you know if that is possible? As you stated, Excel can be frustrating when it get more advanced. I use a more simple approach, where I use an excel page per Theme and then have a Summarizing page. That gives me a fairly good overview of each theme with all the sets a have within that theme, stocks, buying prices, selling prices, other cost, total invested money, total returns etc, alot of data. The Summarizing page in excel just draws some of the totals from each of the theme pages. So I have a fairly simpel overview on investment and returns in each Lego theme. /Lennart

    Share this comment


    Link to comment
    Share on other sites
    Easiest way to do it in quicken is to add theme abbreviation to either a name or a set number so it'll look like SW-10178 or MD-Fire Brigade, then you can customize your reporting to run a report by theme basically filter the report to show you security name contains or begins with abbreviation. I am not big fan of adding tabs in excel so if I was doing long term investment tracking in Excel I would just add theme column and let the pivot do the rest. Problem with excel for long term investment is that you need to account for blending of the different lots of the same item purchased over time to get an accurate cost base and CAGR, it is doable, but given Quicken alternative I figured I would not waste too much time explaining how to do it.

    Share this comment


    Link to comment
    Share on other sites
    Thanks for the reply. I have never heard of Quicken, but I am also fairly new to the lego investment. So I have never had the need for that kind of software. Looking up reviews for Quicken software I quickly find a lot of critics about the product, mostly about every version being buggy and UI bugs. But I also seeing many that have used it for many years, which gives in very positive reviews. Can you guys that uses Quicken comment on the "buggyness" of the software. And to all others, what are the alternatives. What are people here on Brickpicker using of software to keep track of stockroom, cost, revenue etc?

    Share this comment


    Link to comment
    Share on other sites
    In US quicken is pretty much the only game in town when it comes to full blown desktop personal finance management software. Microsoft used to make similar product called Money but it's been dead for years now. I've used quicken for the past decade usually there is always a problem with a new release related to online banking that they resolve within a month or two so if you can restrain yourself and upgrade 6-8 months after first release you'd get good stable software. Now quicken is not solely for investment tracking this is a full blown personal finance software: banking, investment, budgeting so if you are not going to be using those other features I am not sure if it's worth the money. I can probably figure something out in Excel and post another blog article about it :).

    Share this comment


    Link to comment
    Share on other sites
    Feeling super inferior....you don't happen to live in Seattle and want to teach Quickbooks Pro do you?! Is there a difference between Quickbooks Online Simple Start and Quickbooks Pro other than the $100 pricetag difference?

    Share this comment


    Link to comment
    Share on other sites
    betsy sorry I live on east coast :). Djim spreadsheets will only get you so far, databases are far superior in that respect and those software packages really have nominal cost if your volume is large enough. You have to have the right tools for the job at some point. :)

    Share this comment


    Link to comment
    Share on other sites
    You can build databases using Google Apps Script that also uses Google Spreadsheets to display the data (although Google Sheets can handle all this data without so much as a blink). You can also pull in information from across the web into spreadsheets (like current ebay/amazon price information, perhaps), plus, this information/application will be available to you on any device, even mobile. I really like what you have done here, and am thinking of setting up something similar in Google Spreadsheets.

    Share this comment


    Link to comment
    Share on other sites
    I'm more fan of MS products myself SQL access excel. Not very big fan of cloud anything still prefer an old school hard drive and a dedicated offsite backup service :). My thinking is that an average business owner would not have skills or time to do that from scratch. I have good experience with databases VBA and scripting and my guestimate that it will be easily two to four hundred dollars of my time tinkering with this stuff and I will still not get it completely right. Spreadsheet doesn't not handle natively double sided accounting and inventory tracking that business owner really needs. My thinking is that if you own quicken already for your personal finances it is easily adapted to handle lego investment, Spreadsheet with simple formulas can handle small volume shop anything larger than this you'd be actually saving money by buying a package unless of course you consider database design, setup, scripting your play time that you'd do anyway. :)

    Share this comment


    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
    Add a comment...

    ×   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.

    Loading...

×
×
  • Create New...