Wow Professions - Excel Spreadsheet Guide

Posted by Markco at 8:00 AM

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.