An Ode to Spreadsheets

This scene came to mind when writing this.

That's right, WORLD (of Warcraft) DOMINATION!

I'm talking today to the people out there that like to think for themselves, experiment, and don't just want to compete but to crucify the auctionhouse without camping.  Back before such great addons like TSM and Auctionator (or even the introduction of TSM's predecessor Quick Auctions), making gold required you to figure a lot of things out on your own.  Since gold making became as simple as scrolling a mousewheel for the masses, or downloading the new shuffle spreadsheet, or visiting some blog/forum and reading what everyone else was doing the lost art of developing a spreadsheet and putting pencil to paper has been tossed aside.

This is, at least, for the vast majority of people working the AH today.  I would take a wild guess and estimate that 0.0001% of the people actively working the auctionhouse today are using spreadsheets they developed and maintain.  I happen to know of 4.

Of course, I'm not talking about using spreadsheets developed for shuffling purposes.  Those spreadsheets are widely available and used by everyone camping gems/glyphs/enchants today.  The people that put those together did a great job, sort of like Walmart bringing big city deals to rural communities.  This is a different form of spreadsheet development, as making your own will enhance your efforts further as you try to discover new markets, tricks, and paths of least resistance.

I came into WoW developing my first spreadsheet before I even hit level 60.  In a previous game I developed my own spreadsheets, and the trek to 60 back then was a 2 month ordeal so you had lots of time.  When I started playing Rift, it was the first thing I did.

We didn't have sites tracking prices very effectively (Thott and later Wowhead) so you had to do it in game and live to look up everything to plug and chug.  Appending to what I went over last time in JIT vs. Stockpiling, this was JIT in it's worst form.  You had to track those prices yourself, typically with Auctioneer data.  It was usually bad data because you were only seeing prices from the times when you scanned so you couldn't see that prices dipped overnight because the CGFs were out in force.  Thanks to API data and the efforts of Erorus who pioneered the science of logging in and checking every single hour of the day on most every server, Blizzard relented and started releasing the data.  Today's information is now good enough to be able to track market prices within a very tight margin of error, typically within 5%.

To develop a spreadsheet, you simply plugged in all the materials need to make the various crafts, and then referenced their construction to their material prices.  You then added them up, and could manipulate the data however you wished.  People using TSM today will scream "TSM does this!"  Yes, in a very basic format.  It culls upon the AuctionDB information, and then the addon determines profitability and you go buy the materials and make whatever percentage TSM tells you - provided material costs don't change rapidly.  Again, this is still JIT manufacturing and not long term planning.

Remember what I said in JIT manufacturing, it's great if the product is high turn.  You make your item and if it sells before pricing deflates or someone undercuts because they're using cheaper mats you didn't acquire then you win.  Stockpiling is projecting your profit consistently, JIT in WoW is arriving at a profit or a loss.  Spreadsheeting is used heavily in concert with stockpiling because you will determine your costs and profit of a product before you ever make that product in an effort to make a repeatable process.

Here is a sample from my own spreadsheet (part of it at least).  My spreadsheet today utilizes TUJ's data and puts everything into a neat and organized format.  My costs and prices are directly culled from the price data, and I then calculate profit based on:

(Sales Price x 0.95%) - Cost of Goods Sold =  Profit

My Return on Investment (ROI) is calculated by my profit over cost.  I also have a function that determines if the item is something worth making if it meets my ROI requirement.

Of course this is basic MoP information above, my spreadsheet includes every single recipe in the game that can create a predictable item, I don't include randomized items unless I am specifically analyzing them elsewhere within my spreadsheet.  I deal in consistent and expected results, not random chance.

If my spreadsheet tells me I should make something, I will then explore the materials behind it to insure that they are within good tolerance.  Let's take a Contender's piece for example.  I want to know WHERE those numbers are coming from, not simply that it sees an opportunity for profit.  I have each individual material set up in a separate part of the spreadsheet for my reference as such:

From this data, I'm able to determine that Ghost Iron Ore was probably used in the cost calculation since it's cheaper, but GIO is also much higher than it's historical price.  I know I've seen it cheaper, let's confirm with a quick visit to TUJ.

And there it is, the most recent low is showing 1.74g Market Price.  Since I want to make Contender's gear, I will now begin stockpiling all Ghost Iron Ore, whether I need it or not, below about 1.75g and crafting my Contender's gear with it.  I will develop purchase history and determine if that market price is accurate, since my server has been known to be far lower at times and TUJ uses algorithms to determine Market Price which have a margin of error of about 5% either way.  If I am acquiring too much ore, I will reduce my acquisition price by 5-10 silver, and fine tune it from there until I am able to hit my requirement of a 30 day supply for this material.  If I am acquiring too little ore, then I will increase my tolerance 5-10s per ore.

Because I also use GIO for engineering and jewelcrafting, it's important to closely monitor the amount I have on hand so as not to get into hoarding.  If I am overstocked, I will reduce my price further so as to use up some stock, but then increase it later when I'm needing more supply.  I will then make a habit of revisiting my spreadsheet about once a month, or whenever I see a discrepancy in the market.


This is not the same as if you simply go through TSM to make things.  While TSM does a great job for high turn items, actually planning your profit out via spreadsheet will give you longer term consistency and profit.  Remember, this is the reason you stockpile as opposed to engaging in JIT production.



Holes in the Market

Spreadsheeting isn't just used for determining profitability, that's just one aspect of it.  I've got every single recipe that is reasonably predictable in my spreadsheet.  If you were to try this same tactic in TSM, you would have a ton of things on your screen, and it would make it more difficult to plan accordingly since doing this same thing would make your UI a mess.

The reason I work so many markets is that I have poured over my spreadsheet looking for new opportunities.  I set my ROI to 20% minimum, and see what lights on the Christmas tree light up with MAKE.  Prices change all the time because the data used is only as good as that minute's pricing.  Sometimes things get overlooked, and maybe there is a niche I'm missing out on.

I'm able to sandbox any product simply by adding it to my sheet.  I can calculate the cost based on TUJ data, or if I am currently busting ore for my enchanting materials and can produce at a lower price, I can calculate my actual costs, which gives me a clear advantage over those merely looking at the basic materials on the auction house.  If I need to adjust the price of Spirits of Harmony, or if I run across a path to a product not captured by TUJ (there are a few), I can verify.

A good example of this was discovering that LW bags were still profitable on the server.  For a long time these items never lit up.  It wasn't until about 3 months ago that they finally began appearing on my radar.  I developed some trial stock, posted, and was able to determine that their prices were coming in exceptionally well and they were moving.  I then went through my standard process and now stockpile the materials and have a 30 day stockpile.  Materials today are 4x my price, so I get to cash in because I did proper planning and thankfully there's a market shortage.  JIT crafting loses again!

In a future post I'll go over another application of one of my spreadsheet efforts and show you my technique for cashing in on raid days.  Because spreadsheets allow you to manipulate and play the "what-if" game, I was able to find certain tricks to make one profession even more profitable.


Conclusion


It's not exactly rocket science, but it is an art.  Utilizing your spreadsheet is purely for long term planning, and not being a person merely working for the day. 

If you've seen my sales volume, and compared it to my purchases, you know how much gold I'm making.  I've got each market fine tuned to be consistently profitable using my method, and I don't have to post but 2-3 times a day to see very aggressive revenues.  There's no camping here, I'm not fighting 10 others to race to the bottom.  If anything my prices persuade people to keep camping and grinding glyphs, gems, and enchants.  I can compete and do what I want to do in the game, and my friend list is full of actual friends, not competitors I am having to watch because I engage in poor planning.

Applying a spreadsheet distances you far ahead of your competition and allows you to reason why to do things.  And that leads me to this week's tip:

Tip of the Week:  Nobody ever got rich doing the things everyone else does.

Margin Call Week of 2/10/2013

I hope your week was better than mine.  It's my fault, I had other things to do this weekend, one of them being to get a monitor to the shop in between everything else going on.  I'm not that thrilled about the results for the week given last week, but I didn't post but once on Saturday and Sunday, and the entire weekend was apparently slow anyhow.  Oh well, maybe next week will be better because this week was bordering on awful.

Thanks for stopping in!