Portfolio Management: Part 2

Yesterday, I showed how to setup a spreadsheet to track your portfolio of products that you are trading on the Trading Post. Today I will quickly go over some of the key things you should be tracking, and how to easily go about it.

Trading Post Fees

Trading Post fees are easily to calculate. The cost of posting something is five percent of the sale price. Thus, if you post something for one silver, the cost of making the post is five copper. If you cancel the sell order you do not get this fee back. The Trading Post also takes another ten percent when you make a sale, so for then item we posted at one silver, we will be charged another ten copper when the items sells. All-in-all the TP takes fifteen percent of the final sale price, with five percent being upfront. Now, that means that you will essentially take home eighty-five percent of what you post an item for. In other words/, to make a profit selling at one silver, you have to buy at lower than eighty-five copper, or in general, you need to sell for at least, 17.6% more than you bought for.

Trading Post Fees.

It is important to know this fact, because this is your only expense. This expense will determine whether you make money or loose money. A calculator is enough to figure out your break even points, and my spreadsheet factors this in automatically. Another handy tools is tpcalc.com. It simply does the calculation describe above showing you your costs and profits.

COGS and Inventory

When we talk about Cost of Goods Sold we must also talk about how you calculate your inventory. There are a lot of different ways you can do this, and a lot of different ways it is done in the real world. There are three major systems, First-in, first-out (FIFO), last-in first-out (LIFO), and average cost. My spreadsheet uses average cost, because it is the easiest to implement on a spreadsheet. I simply add up the amount you have paid for a certain product and divide by the number bought. This is the average price per unit used to calculate Cost of Goods Sold.

Average cost inventory.

Intuitively, most probably use a FIFO approach. This means that the first price you buy at is the cost you take into account when you go to sell. If you bought eggs on Monday for 20c, on Tuesday for 25c, and Wednesday for 30c then you started selling your eggs on Friday you would first consider the price you paid on Monday and would have to sell at 24c to make a profit. As you work through your inventory your costs would rise and you would have to raise your prices.

LIFO on the other hand would have you sell the eggs you bought most recently first. Thus you would sell those you bough on Wednesday first and work your way towards the cheaper eggs you bought on Monday. In general costs of goods rise as time goes on. Inflation, in general, is driven by an increase in the money supply. As there is more money available people are willing to pay more for things. Thus, in general, FIFO will have you selling your cheaper goods first, and LIFO will have you selling your more expensive goods first. This is a general statement though for the long term, and doesn't apply to the short term, nor necessarily to the long term either.

It does bring up an interesting thing to consider, though. Do you sell your more expensive inventory first? or the cheaper stuff? If, in both scenarios you sell for the same prices, than it has no affect what-so-ever on your profit. In the real world, companies use different methods to make their business looks more attractive to investors. In this game, it really only matters to you. If you prefer to get the expensive stuff out of the way first, then do that. If you prefer to see big profits quickly sell your cheaper stuff first.

In the end, use the inventory system that works for you. Then use this system to arrive at the Cost of the Goods you are selling. This way you can effectively evaluate your trading post fees and profits. Even if you do not apply this to your trading in game, I hope you learned something about real world accounting.

Percentage of Inventory

Another important statistic I like to track is the percentage of a certain good as it relates to my total inventory. For example if I have ten gold in eggs, how much as a percentage is this of my total investments of one hundred gold? In this case ten percent. This is a good thing to track, because it shows how diversified your investments are. Having 100% of your inventory in a single good means that you have not diversified and your future hinges on a single market. If, however, you are invested in twenty different commodities and each makes up roughly five percent of your total inventory you know that any markets that bottom out will be covered by your other investments. 

My current inventory, as a percent of the total.

This is not to say that there aren't markets you should invest heavily into. It is a judgement call you have to make. Charts and graphs can help you make these calls. They can also give you signals as to when you have bought enough, and when it is time to think about looking for opportunities to sell.

Profit

The final, and most obvious stat to track, are your profits. For starters it is a moral booster to see this number slowly growing and to know your efforts are paying off. Secondly, once you break profits down to margins and start examining them on the basis on each commodity you will see where your efforts are meeting the most reward.

Based on current (Dec. 11th) prices
 my expected ROI shown per commodity

Simply, profits equal revenue minus costs. Sometimes this is also referred to as gross profits and since we have no other costs this is also our net profit. Above we talked about how to track your costs. Your revenue is simply what you sell the good for. If your revenue is higher than your costs, you are in the black and making gold. Otherwise, you are in the red and taking a loss.

Next, you can calculate your return on investment (ROI) so that you know how well your money is working for you. The calculation for this is simple, take your net profit, and divide by your costs (Cost of Goods Sold, and TP fees). This is the percentage of what you invested that has been returned to you. The higher the ROI the more lucrative the trade. ROI is not the be all and end all though. Trades with a low ROI, but a high turn over may be more lucrative than a high ROI with slow turn over.

Conclusions

The point here is to give yourself the tools to analyze the strategies you are using going into the market. This way you can re-double the efforts that are proving successful, and abandon those that are not. You can also examine the numbers closely and see where you went wrong and how to avoid future disasters, and on the flip side you can see how you succeeded and apply those strategies in other areas. Not only that, but the morale boost given by seeing your numbers rising, bars on graphs growing, is quite telling. Instead of abstract numbers in your head, it starts to become a real thing you care about and want to grow. Either way, the point is to have fun. If this is not fun to you, than do not do it. Continue with whatever systems you have in place that are working for you.

Trading Post Portfolio Spreadsheet

My current market portfolio, complete with pie charts.

Today I would like to talk a little bit about portfolio management. This is something that I just started doing thanks to Wintersday. I wanted a way to quickly at a glance see how much my on hand inventory is worth, how much I can expect to make selling at current market values, and how much I have made from sales to date. 

To solve this problem, I did what anyone would, I made a spreadsheet. You too an make a spreadsheet or you can use mine if you would like. Either way having a spreadsheet like this is very helpful in making decisions on the fly. It also help you track your success in the market, such that you can determine where to reinvest your profits. You could probably even use accounting software such as Quickbooks or Simply Accounting.

Using my Spreadsheet

To access my portfolio spreadsheet click HERE. The copy I have shared is not editable, so you will need to make a copy for yourself. Select File > Make a copy.... This will save a copy into your Google Docs folder which you will be able to edit. If you prefer to work in Excel or a similar program offline than select File > Download As > Excel Spreadsheet.

Select File > Make a copy... to add a copy to your Google Drive.

My spreadsheet is laid out across multiple sheets. You can see along the bottom that there is one sheet called, "Portfolio," and then each product is on its own sheet. This way you can track everything to do with a specific product on one page, and your overall portfolio on another. There is also a sheet titled, 'Blank Product,' which I use as a template when adding new products.

This spreadsheet does require some manual work to link the portfolio sheet to the product sheets. To make linking the portfolio and products easy for you I have colour coded the cells that need to be linked together. Thus, a green cell on the portfolio page needs to be linked to the green cell on the product page. Similarly for the yellow cells and the blue cells. There are instructions in the spreadsheet on how to do this. Also, I have set up six examples to help you get started. Below, I also have instructions walking you through the process of adding a new product.

The pink cells need to be filled in manually by you the user. Pink cells are for the current market price of the product. Simply go to the Trading Post in game, determine the current value you could sell a good for, and enter that number into the pink cell.

Adding a new product

If you wish to add another product you simply start by creating a new sheet,

The new sheet button.

Next ensure you title the sheet with the name of the product by using the renaming option,

The rename option for a new sheet.

In this case, Sample Product 6,

The rename dialog box.

Next open the Blank Product sheet, and press CTRL-A to select the entire contents of the sheet then press CRTL-C to copy the selected contents.

Open the Blank Product sheet I have already made and copy the entirety of its contents to the clipboard.

Then navigate back to the new sheet you just made (I titled mine "Sample Product 6"), select the upper left cell (A1) and press CTRL-V to paste the contents of the "Blank Product" sheet into this new product sheet. Your new product page should now be filled in a ready to go. You should see a purchases section and a sales section now.

Return to the main portfolio sheet. Select the next available row (in my case row 7) and select the only green cell in that row (in my case cell A7) and enter the following formula without the quotation marks, 

='Product Name'!A1

Replace 'Product Name' with the title you gave the new sheet which in my case is 'Sample Product 6'. If you entered the formula correctly the cell should now say, "insert your products name here." If you go back to the sheet you just made you will notice it says the same thing in cell A1. Replace the contents of cell A1 on your new product sheet with the name of your product. Now when you go back to the portfolio sheet you should see your product name in the cell you just changed the formula on.

You should see this if you entered the formula correctly. Be sure to replace
 'Sample Product 6' in the formula with the title you gave your brand new sheet.

Next select the first yellow cell in the B column. Enter the following formula into that square,

='Product Name'!B3

Again, replace 'Product Name' with the title you gave the sheet which in my case is 'Sample Product 6'. Now to auto-fill the yellow cell to the right, simply select the yellow cell you just filled out, grab the square in the lower right of the cell, and drag it over one cell to the right,

Drag the highlighted square to the right one cell.

This should automatically put the following formula in the yellow square in the C column,

='Product Name'!C3

Finally, scroll to the right until you find the blue cells. Select the blue cell in your new products row under the K column and enter the following formula,

='Product Name'!G3

Again replace 'Product Name' with the titled of your new product sheet. Now use the same auto-fill technique described above to auto-fill the contents of this blue cell to the blue cell to the right. It should auto-fill the following formula into that cell,

='Product Name'!H3'

That's it! That is all you have to do to add a new product. 

Adding new rows to portfolio

If you run out of rows on the portfolio page follow these steps to add a new row. Start by right clicking on the bottom most row (row 15 by default) and select 'Insert 1 above'. 

Insert 1 above option.

A new blank row will then be inserted above row 15. It is important to ensure the new row is added between our upper most row (2 by default) and the lower most row (15 by default) to ensure that the formula are preserved. Next select the row above the new row you just inserted (in my case row 14). Select the entire row from column A to column O. For me these are cells A14 through O14.

Select the entire row above the one just inserted.

Next, grab hold of the square handle in the lower right of your selection and drag it down one row to auto-fill everything down one row.

Use auto-fill to pull the old row down into the new row.

That is it! The auto-fill automatically fills in all the formula used to calculate the non-user defined cells. Now you just need to follow the steps outlined above to create a new sheet and link the new sheet to the main portfolio page. You can continue adding as many rows as you require.

Day-to-day Use

I have described above the procedures you must follow to add a new product to the sheet. You will not need to do this very often, and once you have done it once you will be able to do it again easy enough. 

Day-to-day use of this sheet is even easier. On each product page all you need to do is enter the quantities you bought and the price you paid into the purchases section. When you make a sale, you do the same thing in the sales section. If you ever need to add more rows to either section simply use the exact same process used to add a new row to the portfolio page.

Other than that you just need to keep the pink cells on the portfolio page up-to-date with the data directly from the trading post. Using this the spreadsheet will predict your expected profits.  As for reading the spreadsheet and understanding all the columns, I will leave that for tomorrow. For now I hope you can find some use out of my spreadsheet, or at least I hope I have inspired you to create your own spreadsheet which will track similar things.