Tuesday, February 26, 2013

Further Application of Spreadsheets



Last Tuesday I went into why spreadsheets were an important vehicle in decision making and long term profit planning.  This week I'm going to gloss over something I developed using my spreadsheet several months ago and it's something I don't mind people seeing since it's just an example of critical thinking and what I promote here.  Since I wrote it a while ago, I figure this article flows well in my discussion.

Token Cost Normalization Method

I love cooking, and always dealt in this market from Classic WoW through present day.  Even in Cataclysm you could make a healthy profit just flipping fish.  I'm fairly certain a lot of people out there are on servers where cooking is flat out not profitable anymore, and it made me a bit upset after release to see my server seemed this way.  The only way many approach it is to fish some mats, kill lots of different mobs, or grow the veggies wasting valuable farm spots.  You could also potentially engage in the Ironpaw shuffle as presented by Elen, and here's my spin on it and my system.

This is a method for always making cooking high end recipes profitable.  As mentioned the vast majority of recipes are not profitable, so that means we need to establish a baseline cost.  The following is a snippet of my personal spreadsheet from its Cooking section showing a typical day using TUJ data on a Monday:


Anything above 20% ROI is flagged here, although it doesn't mean I'm going to necessarily make it.  My spreadsheet, for convenience sake, compares what I'm about to demonstrate against purchasing the materials outright.  That means I compare token pricing to buying materials for direct use, but we're talking strictly tokens here.

What I tend to hone in on are 300 stat recipes and other reagents used in cooking (which makes it very similar to Elen's shuffle).  I am specifically targeting the raiders on my server that understand having 25 more to a stat buffs their raid a healthy amount, provided most everyone is using 300 food.  There are quite a few of them to say the least that min/max.

Normalizing the cost of materials

What I am doing is guaranteeing my ROI on every food sold.  For certain a person could just buy random materials that go into the craft, or buy them up on raid days and make their own food (which can be expensive) but like I explained before, I buy supplies even if I don't need these supplies.  Using this system, it doesn't matter if I'm buying Jade Lungfish for 1g a piece or Raw Crab Meat for 2g each.  Each of the materials are collected and eventually turned into tokens and each of those tokens will have a fixed value of no more than 40g or 50g each, or whatever I want to make them.

For our purposes here, let's say I want to make each token worth 50g each, which means my raw materials (in stacks of 20) can be no more than 2.5g each, and this is demonstrated on my spreadsheet and returns over 20% ROI for the crafts I engage in.


I can then convert these tokens to anything I wish to make in cooking, so my spreadsheet accounts for the cost of the primary recipes that people will probably want to buy:

300 stat food - 4 tokens - 200g cost - 40g cost per food
275 10-man feast - 7 tokens - 350g cost - 70g cost per feast
275 25-man feast - 13 tokens - 650g cost - 130g cost per feast

So if I assign a fixed maximum cost of 40g per 300 food, I can buy endless materials of any fish or meat off the auction house converting them to tokens, and not have a worry about anything beyond the fact that my costs are always fixed to a maximum of a 40g cost.  In other words, if I have 1000 tokens on my turn-in character, I have 40000g in reagents available for 300 stat foods, powders, flours, or soy sauce.

Do these sell?  On my server they do.  Tuesday through Friday I commonly sell a healthy stock of food off, with Mogu Fish Stew being undoubtedly the most popular.  Other recipes sell sparingly, but given this was written in January and lots of people are phoning in the raids, it's just important to have the item available.


As you can see, my ROI is better than 20%, and I consider this a bonus.

Since meats only grant 5 per token, you can reduce the costs even further by keeping a few stacks in reserve to craft with since 15g is less than 40g.  This means if you can acquire cheap Tiger Gourami, Jewel Danio, Emperor Salmon, Giant Mantis Shrimp, and Redbelly Mandarin, Mushan Ribs, Raw Crocolisk Belly, Raw Turtle Meat, Raw Crab Meat, and Raw Tiger Steak, and you don't mind stockpiling them, by all means go for it.  You will need to balance what is an appropriate stockpile and what you will be converting.  I tend to keep enough of various meats in stock based on my own experience, allowing me to produce a week of inventory while trading for the missing veggies and reagents.  Either way, my costs are always below a fixed normalized amount of gold and I like having a revenue source that's on autopilot and doesn't require a lot of mental acrobatics to keep up with.

Can you have too many tokens in stock?

In the reagent market, this also works quite well for 100 Year Soy Sauce still, since these are undoubtedly the easiest items to trade up for besides Rice Flour and Black Powder (which also sell sparingly).  People buy them for leveling up cooking, making quick raid feasts, and making their own 300 foods since I know many people still grind/grow their own mats.  I keep about 100 Soy in stock for posting at any given time.


Conclusion

This is just one more application of where a spreadsheet will help you in critical thinking ability, as opposed to simply checking what to make via an addon.  Since my costs are locked in, I can easily set TSM up for any of these to only post when I'm going to see a decent profit, and I can restock quickly with a few minutes in Halfhill.  I tend to keep about 100 of the different foods in stock at any given time, and if they post, then they post.  Because I scour the AH for the best priced raw materials normally, I know point blank anyone posting under me is farming the materials themselves or flat out not thinking about their costs because I tend to work this market on a daily basis.  If someone posts below my own cost, I will potentially buy them out - although this rarely happens because then you know you have a real idiot on your hands.

Owner's Manual

Step 1:

Hit up TUJ or Wowuction and find out what your market prices are.  Determine your cost and ROI from this data because this will determine what your token prices will be fixed at.

Step 2:

Retain the recipes.  I have all recipes listed here that you should have access to once you have leveled all "Ways" to 600.  Specifically:

Sea Mist Rice Noodles
Steamed Crab Surprise
Mogu Fish Stew
Chun Tian Spring Rolls
Black Pepper Ribs and Shrimp

Each of these recipes will produce 5 of each food per craft.

Step 3:

TEST THE MARKET.  Don't just jump into this.  Make 1-4 batches of each, see how long it takes to move them.  If you see good results, then this may be a decent stream of revenue for you.  It's not get rich money, but just another avenue of revenue.

Step 4:

Acquire the materials off the auction house, send them to an alt parked out in Halfhill.  It's best to use an 85-89 you're still leveling since you'll receive the experience for turning in the baskets*.  If you don't care about that, you should send them to your cook.

Step 5: 

Stock up and sell.

*It should be noted that you can save yourself a lot of time setting up a macro for converting the baskets.  The addon Turn In will make the turn in of the baskets a joke.  I personally keep about 20 of each basket in my bank and just spam the macro, then turn them in when I'm done.  I also do it whenever the mailbox is packed with foods.  Another macro helps with the exchange.

Margin Call - Week of 2/17/2013

Decent week here for the week before the week before the patch.  I made a rather sizable investment ahead of this next week (as you can see) and I think I'll cash in after the release, we'll see.  If you haven't kept up with the week's drama, apparently Blizzard has been hammering the bot users causing a bit of a problem with the availability of herbs and ores.  Isn't it interesting that there was also a sale this month on new copies?  Coincidence?  Or just excellent quarterly planning?

Normally I see several hundred stacks of underpriced ores during the week, but this week I was only able to snag a handful.  This is why stockpiling works, it will prevent interruptions of supply and allow a person to be competitive in any market.  I'm also fairly certain they'll be back up and running within a few weeks, if they aren't at it already.


Tip of the week:  My numbers are always for example purposes, I could be using higher or lower numbers.


Thanks for stopping in!

2 comments:

  1. Nice, kind of the same way i do it actually but i keep it in my head instead of in Excell.
    I do (or more did) routinely scan for all different fish and meats to regularly buy everything below a certain value wich gives a kind of sense for it.

    One important difference though, that addon Turn it in. I have been doing the clicking manually and it always ends up with my hand and fingers burning of pain after a few houndreds of turnins. So i dont do it except for when they are to expensive to buy (usually so cheap that i dont have to bother since everything is overproduced).

    Thanks for the tip

    /Stash4Cash

    ReplyDelete
  2. You're welcome, but I want others to understand this is not so much a technique as a demonstration of utilizing spreadsheets to develop critical data to make a decision to process something or not to process something.

    ReplyDelete

Remember to keep your comments pithy, they will be posted after approval!