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.