***UPDATE!***
I've now made YouTube Videos entailing the entire process!
Making Gold With Alchemy - Potions
-Associated Blog Post
Making Gold With Alchemy - Flasks
-Associated Blog Post
***Back to Post***
I've used Excel in the past to aid me in keeping track of prices, etc.; but I've never been able to make someone quite as streamlined as the most recent project I made (which is deceptively simple).
Below is the basic look of the project.
(Unfortunately, buying and selling nothing has a Net Profit of 0 )
How it works is quite simple. All I have to do is search for the current AH prices for Cinderbloom, Azshara's Veil, Whiptail, Stormvine, Heartblossom, Volatile Life (The required materials to make 5 different types of Cataclysm Potions), Mythical Mana Potions, Golemblood Potions, Potions of the Tol'vir, Volcanic Potions, and Earthen Potions (The 5 Cataclysm Potions themselves) and enter those prices into their respective "Price Per Unit" categories.
I chose these 5 potions because I've seen them as the most popular and profitable on my server. I'm looking to add more to the list in time.
At this point, the "coding" (Yes, I'm using that term loosely - sorry CS majors!) takes over, taking the base pricing information and manipulating it to produce results such as Price Per Unit of each type of Potion, Price Per 40 Units of each type of potion (I create in lots of 40 as to sell them 1x20, 1x10, 1x5, and 5x1 - just a quirk about my selling habits), Net Profit / Loss Per Unit, etc.
(On a side-note: I've got an addon that allows "Shopping Lists" to be created (Auctionator) and I've got a shopping list for all the data needed for the spreadsheet. It takes a tedious process of repeatedly searching for the items and breaks it down to 11 mouse clicks.)
As you can see, even before creating the potions, you can tell that, at current market price, there is a net profit of over 1,400 gold (That goes for you non-potion spec Alchemists out there). This gives you a quick look how much gold you can make with a few mouse clicks without having to do all the time-consuming math time and time again. But, as a Potion-Specced Alchemist, the spreadsheet was incomplete. I needed to factor in procs.
One of the last features I created in this spreadsheet was the ability to take in account any procs as I am a Potion Master. As you can see below, all it takes is a simple entering of how many procs of each potion I received to re-calculate the subsequent results.
In short, if you like making gold and have a profession where something can be produced...USE EXCEL. It can not only help to make sure that any multi-process, multi-item creation you're working on is profitable across the board, but it can do it quickly and efficiently - removing the majority of the legwork. Not only that, but this only both took a very rudimentary understanding of Excel and roughly 30 minutes to complete - and I haven't used excel in ages.
With very simple math, I am able to calculate that an investment of 1280 gold will result in an income of 3,330 gold (with the added profit via procs), yielding 2,049 gold in Net Profit. That took about 2 minutes of data entry after the spreadsheet was complete. Not only that, but, say, hypothetically, the price of Stormvine (The sole material for the Earthen Potion) was out the roof, I would be able to detect that, while I do have a positive overall Net Profit, I would be losing money by creating and selling Earthen Potions. That is the reason for breaking down the Net Profit / Loss of each potion. It gives me the ability to detect where I can make profit, how much profit I can make, and what potions I should or should not make in order to raise my Net Profit.
One of the perks of using a system like this is that, once you've done it once too your liking, you can easily translate the same principal to any profession - not just Alchemy. It's a simple process of checking the prices of required materials verses the price of the end result on a bit of a larger scale as well as having a good knowledge of what is in demand and sells well on your server.
I just wanted to share my creation and encourage many of you out there to do the same!
-=EDIT=-
After a few PMs, etc., I decided to just upload the file and let you all have your way with it
(TAKE NOTE THIS IS AN UPDATED VERSION OF THE FILE THAT JUST HAPPENS TO BE A BIT CLEANER AND BETTER - EXPLANATION BELOW)
http://www.mediafire.com/?hvz0zauuux50qy9
This is how the new interface looks:
The rule of thumb with the new interface is very simple: DO NOT TOUCH WHAT IS IN COLOR. If you're trying to enter data into a colored slot, you're doing it wrong.
And after a few numbers being inserted...
As you can see, there is a new setup in this file. The purple cells are those that were in the previous version. Nothing terribly new. In the green, however, are the amount of herbs per potion that are required; and, in the light blue are the materials to be purchased. I'll explain the difference below.
And the third image, featuring the new feature...
When comparing the two most recent images, there is a difference in "Materials to buy" as well as "Investment", "Income", and "Net Profit". That was one of the main issues I had with the older version. It would tell me how many herbs I needed to buy to make 40 of each potion, but would not take into account any adjustments due to a potion being a net loss; therefore, not being created.
In the new version, it will auto-adjust the "Investment", "Income", and "Net Profit" as well as the "Materials to buy" if any items are in the negative and should not be created. This was done in order to not have anyone (well, more importantly, me ) accidentally purchase more herbs than I needed to create potions that were a net loss to create and sell.
And just a couple side notes...
1. The "Procs" category still works - though is not displayed in the new images.
2. As said earlier, only enter data into non-colored fields. This includes the "Market Price Per Unit" row (Row 2), the "Procs" row (Row 5), and the "Price Per Unit" sub-column (G12:G18). After entering data in any of those areas, use the rest of the spreadsheet to read data and act accordingly.
3. Investment, Income, and Net Profit all function the same as in the earlier version - fairly self-explanatory.
4. If the figures look a bit off to you, it's because they are - by 5%. The AH cut of 5% is removed in the appropriate places to re-adjust prices to the 95% of gold you will receive after the AH cut. This is to avoid a figure having a marginal profit - therefore inclining you to purchase and create the potions - without taking into consideration the AH cut, resulting in a net loss.
5. Again, the download link for the file is: http://www.mediafire.com/?hvz0zauuux50qy9
Happy AHing, and if you have any questions, feel free to PM me
~Warwithin
-=EDIT #2=-
This edit was useless and confusing. Editing out edit #2.
-=EDIT #3=-
Ignore the Rapidshare link, got one on Mediafire that should work.
New Mediafire Link: http://www.mediafire.com/?hvz0zauuux50qy9
And again, any questions, feel free to PM.
This link is for the Potions Excel Spreadsheet. For the Flasks one, see below.
~Warwithin
-=EDIT #4=-
Finally uploaded my spreadsheet for Flasks. Sorry for the wait! Not gonna lie, I kinda forgot =S
http://www.mediafire.com/?7eecz361lb3b1jz