Wow Professions - Excel Spreadsheet Guide

Written by Scantron of the JMTC Forums

Why use Excel?

Your gold making venture is a business, whether it is a small Mom and Pop operation that specializes in flipping Titansteel Bars on the weekends or a multi-factional, multi-professional corporation that keeps half a dozen Chinese gold farmers in the saronite business. What better way to organize yourself than Microsoft Excel, software of choice for small businesses everywhere? In this article I am going to assume that you have at least rudimentary Excel-fu. (If not the internet has many excellent tutorials to get you started).

Set-up

Your sheets can be as complicated or simple as your heart desires. At a minimum, to use my techniques you need:
• Excel 2007 (I believe these instructions can also be followed for 2010)

• A list of your materials and their costs

• Formulas for the items you intend to craft

• A log of sales (date and price)
First let’s set up the materials list on Sheet1 of my spreadsheet. I need columns for material name and the maximum price I’ll pay for that item on my snatch list. That is all you NEED to have on this spreadsheet. However you can include a column for inventory. Personally I use my third column for the item type (as that is how I like to keep my list sorted), categories include “Elementals” like Eternal Fire and “Vendor” like Fine Thread. Now I’m going to populate this list with whatever items I purchase for crafting. The order doesn’t matter. I happen to keep my list organized but as long as the material is on the list once with a price in the column next to it, our formulas will work. For this example we are doing some Wrath blacksmithing, so I’ll populate my page with some bar and eternal prices.






Before we move on to formulas take a moment to affix your headers to the top of your material list. You can do this by selecting View  > Freeze Panes > Freeze Top Row. I suggest ALWAYS doing this, as your spreadsheets can get many screens long and it looks tidier if you can see what each column is without guessing or scrolling to the top of the page.

Formulas

Now we’re ready to enter our formulas. Move to Sheet 2. A Saronite Protector (level 78 blue healing shield) is made from six cobalt bars and four saronite bars. If you’ve used Excel before your instinct is probably to make a formula something like this:



Technically that works just fine, but we want to be advanced Excel users! This equation makes very little sense at a glance and it doesn’t help you duplicate it if you want to add a similar formula (like the Brilliant Saronite Belt, which is made from six cobalt bars and five saronite bars). Instead of one equation in one little cell, let’s use the rows of our spreadsheet to make a formula we can instantly understand.



Rather than putting all of our information in one cell, now the “cost” cell is the sum of each ingredient cost multiplied by the number of that ingredient that you need (the two yellow cells). Now things get more complicated!



G3 COULD have something like:

=F3*Materials!B2

That would give us the correct number. But using the reference “Materials!B2” means that you have to figure out where Cobalt is in your materials spreadsheet and if your spreadsheet is 200 items long that could be tedious. Instead we are going to use a handy little function called VLOOKUP. VLOOKUP takes three arguments we care about. The first is the value we are searching for, in this case “Cobalt Bar”, which is written in cell E3. The second value is itself a table that contains the values we care about, in this case this table is made up from columns A and B of our Materials sheet. The third value is what number we want to return, here that would be the value in column B (which is the second of the two columns in our table) of the materials table. The final argument we just use 0. Multiply this value times F4, the number of cobalt bars our recipe requires, and you have this:


In order to fix the absolute values columns A and B, add a “$” before each of them in your equation. Do not do this with any other value in the equation, as it will ruin copying it and using it in new places!

=F4*VLOOKUP(E4, Materials!$A:$B, 2, 0)

Now for the really beautiful part! In order to get the value for the saronite bars (cell J4), you just copy and paste your original formula. This formula will ALWAYS take the cost of the item mentioned in the cell two to its left and multiply it by the number in the cell directly to the left. You can also copy the set of three boxes that represent each component and paste them wherever you want and they will still work. To add the recipe for Brilliant Saronite Belt, for example, I can simply directly copy row four, change the item and number of saronite bars to five and I’m set!



Log

Having a formula list like the one above is an excellent way to ensure that you understand the cost of each item before you sell it. However, in order to get the most out of your crafting you need to know not only that you CAN make a profit, but also how much you’ve actually made! That’s when you need VLOOKUP to help you make your sales log. My sales log for each profession is very simple. You need columns for the item name, its cost, the amount you sold it for, profit, and date. My real blacksmithing log has more than 400 entries, (they fill up quickly) but all we need for this example is a few entries. I’ve added a few more simple formulas to the formula sheet (the cobalt tanking gear that Markco featured in a recent article).


You only need to use two functions on your log page. Use a Vlookup function to find your cost for each item and Sales Price * .95 – Cost for your profit column. (When you get money from the auction house, make sure you write down the actual sales price rather than the amount you got, because getting back your deposit is not money you earned!). The rest of the information you enter by hand. You must type the item names EXACTLY as they appear on your formula page or this will not work!



Ahh! Now we have so much pretty data to work with! Let’s figure out where we’re making our money! Our log book has 84 entries, but I want to know how many of each pattern I sold. Return to your formula page. Remember how column A and column B were left blank? That’s because I prefer to put my summary data on the left hand side and the actual recipe components on the right side. To count how many Cobalt Belts I sold, I place my cursor in cell A6 and type

=COUNTIF(Log!B:B, C6) Simple as that!



Now you can see what cobalt pieces sell the most often (the shield) and which you shouldn’t bother making if you’re short on cobalt (shoulders, bracers). However, this isn’t enough information for me, because it doesn’t tell me how much profit I am making on each item! I put all of my cobalt tanking pieces in the same Quick Auctions group, so I sell them all for the same price regardless of the fact that their material costs vary by 20%. Our final equation goes in column B. I am going to use the function SUMIF to add together all the profits I’ve made on each cobalt item! SUMIF works like COUNTIF, except it adds a third argument, the number to be added. For us and cobalt belts that is column E of the log file.



That concludes everything I wanted to cover in this article.

24 comments: on "Wow Professions - Excel Spreadsheet Guide"

  1. Too...... much....like....office.....life ::bangs head into table::
    Damn government price lists... concantonate.... vlookup... ACK wow now work!!

    On a side note if you do not own a copy of Microsoft office, May I recomend Openoffice? it's free and pretty much will serve the same function. Swing over to OpenOffice.org

  2. How far do you usually take your material list down? In the example you gave, you used Cobalt and Saronite bars. In practice, would you have listed copper and saronite ore instead?

    What about multi-profession crafts? For example, how would you list/log buying mats for an alchemist to transmute a gem (and maybe getting a proc) which you then sent to your JC to cut and sell?

    I've dabbled in logging before and always sucked at it. Your approach looks really good to me, I'm just trying to figure out how to iron out some of the details for use.

  3. Wow color me impressed. Your tips are getting better each day.

  4. My personal preference is to use Googledocs for spreadsheets.
    This allows me to access them from anywhere, and to share them with others also.

    Every formula you have listed works perfectly in googledocs spreadsheets also.

  5. Oh, that is such an excellent article, Scantron! I will be going through my spreadsheets tonight, looking for places to use VLOOKUP!

    The sales log you describe, though, is a pain to keep up-to-date manually. Moreover, one thing that impinges on my profits on some of the rarely sold items is the number of unsold returns I get that cost me my deposit.

    For instance, "Cherry Grog" rarely sells, so most of my postings just come back to me and cost me my deposit. Even some items that sell well have whopping big deposits that make a lost sale painful (I'm looking at you, Frozen Orb).

    I guess I could just modify your log and put in all the returns there; but frankly the log as you describe would already be a pain to keep up-to-date. There must be a better way that includes using Auctioneer's Beancounter data.

    I'm not smart enough to figure out how to do that, but I suspect you are!

  6. I think this is an excellent tip, but without any automated way to import and manage the AH data I don't think I will take the time to do it.

    There are a couple addons I've tried that attempt to do the same thing, but seem "buggy" in terms of how it manages the cost of raw materials.

    The instructions on how to manage the data makes perfect sense, and if I only played the AH it would work great. I would definitely do this if I had an import feature to get this raw data.

    Looks like you have a great handle on your AH activity though!

  7. Great stuff! For those that don't have access to Excel (for whatever reason) or want to be able to access their document online, all of this works in Google docs as well. I hadn't ever tried linking to other sheets, but I just tried it and it works the same way.
    -Calianna

  8. Thank you for tackling this guide, it's well done. VLOOKUP is the only thing mentioned I haven't tackled in Excel, and I'm looking forward to trying it out.

    Kammler's point is well taken, however. You import your sold prices into Excel manually? I thought there was at least a way to get beancounter data exported. Hundreds of auctions a day would make this a necessity. I'm planning on figuring it out when Auctioneer is updated, but I'd also like to see a guide for that!

  9. In excel, if you right click on a cell and got to Format Cells. Then down to Custom, you can create a custom format: 00"g "00"s "00"c"

    You then have to list everything in copper (ie 1g is 10000) and it will show up as 1g 00s 00c.

  10. I am working on a spreadsheet that is pre-loaded with data, all recipes and ingredients to make. I am hoping to release it within the next week.

    It works a lot better then this one.

  11. Looks good. How about incorporating something like Bolt of Frostweave, where you have a choice of buying it straight out or buying Frostweave and creating Bolts?

    Thanks for the tips though.

  12. How about you make a post about the cooldown on epic gem transmutes and how it did NOT get implemented.

  13. I'll clarify that I don't use a sales log for everything, mostly just things I really like making (lowbie tailoring patterns, blacksmithing as I was levelling it). For that stuff, entering things in the spreadsheet is fun for me, but I agree that it isn't practical to keep a sales log for your entire wow business. I do use MS Sales for glyphs (which I care about) and then manually enter that data once every week or two, but mostly.

    For stuff I care less about (titansteel, for example), I simply make sure my QA is set to a level that my spreadsheets tell me is a profit and let that all take care of itself, adjusting both the QA and the spreadsheet if my mats prices change.

    I'm a little baffled by the comment that claims their spreadsheet "works better than that one"... mine isn't set up to be a pre-made spreadsheet for people, its a tutorial designed to teach people how to use some of the more obscure functions so they can make their own spreadsheets.

    As to the question of ore versus bars, you can have one column for current bar prices, one column for current ore prices and then some math and the MIN function to figure out which price to use. I've found that I always buy saronite ore, so I just multiply that by 2 to get my bar price, and cobalt has 1.

    For constructed materials, such as bolts of cloth, my master materials spreadsheet (yeah I don't actually keep all my stuff in the same spreadsheet, how boring!) has a "constructed materials" sheet that Vlookups the current price on the correct profession sheet and then I make sure my vlookup refers to the crafted list rather than the normal list when I use it. I didn't want to add that level of complexity to this tutorial! (BTW that list doesn't only include things like bolts which are only used by one profession but also a few multi-use lowbie constructs like Frost Oil and Shadow Oil).

  14. Sadly, I still have Office 2000, so the formatting and coding does not work with my excel (I tride and was good up until the VLOOK coding, came back as a big (#N/A) in the cell I was working on. Any thoughts?

  15. I'm using OpenOffice and even though I'm using the VLOOKUP function exactly as it appears here, it's giving me an Error: in bracketing message (Err:508).

  16. Here's a tip that will help your formulas easier to read. On your Materials tab, click in the cell B3 (Saronite Ore Price). Then next to where the Formula is entered you will see B3. Click on this B3 and type SaroniteOrePrice (has to be one word). This is now a named cell reference. Now in your formula below where you state
    "G3 COULD have something like:" it would read:
    =F3*SaroniteOrePrice

    You can do this for all the cells whose name sticks, and you don't need to worry about the pesky $ because it will keep the correctly named cell. :)

    With this method you could remove the VLOOKUP, but then you'd have to manually type each formula. It's a decision to weigh, easy to read formula vs more initial work.

  17. Nice idea, but this only works on a VERY small scale. Once you start dealing in 5 figure deals and hundreds of auctions, I simply do not have time to enter everything into a spreadsheet.

    I DO use spreadsheets to do initial calculations, i.e., determine profitability, break even values, cheapest way to craft something, etc. But after that, I'm done.

  18. Thank you for the wonderful article. As someone who has -never- had much luck with making money (other than farming fires) I took this weekend to sit down and really soak in your advice. As a result I made an excel sheet for prebc enchants for heirlooms. I found a 200% profit margin just from buying the mats on the AH and re-listing the scrolls! 1,000g in one weekend. Thanks!

  19. UPDATE - 8,000G. WOW! <3

  20. Wow I learned a couple of Excel things! One question though.. How do you account for material price fluctuation over time? I imagine you would have to have a sheet (table) for each mat and correspond your item creation date with material price on that give date. So you'd have to record mat prices far more often. But you then could have a mat cost history that you could easily put into graph form and maybe see if there are price points that correspond to happenings in game (patches, new recipes).

  21. Would be great if there were a working add on or program that could decipher and input the beancounter data. It's all local, so it's definately doable, I just lack the coding experience/determination to make it happen.

  22. http://wow.curseforge.com/addons/beancounter-export/

    Could this perhaps be the help you're looking for? Exports all of your data from Bean Counter into an excel sheet.

  23. This is a very useful post and the guide you linked to fits me perfectly.
    Thanks to this post i'm finally getting into to making spreadsheets. Learning slowly, depending on motivation, but learning nonetheless.

  24. The problem I have is the spreadsheet assumes you're going to be paying the same price for mats every time. I don't have a better alternative, and this definitely does look good, but the profit numbers are going to be extremely skewed (likely for the worse).

Post a Comment

Insider Gold Strategies

Enter Your Name & Email Below to Receive My 7 Theories On Making Gold... Guaranteed to Put You Ahead of 99% of Players Out There

 

 

Recent Comments

Subscribe to recent comments

Labels

Blog Archive

Featured On: