Production Cost Analysis

[Monthar]

This spreadsheet is a tool to help keep track the expenses incurred while producing whatever items you choose to produce. It is not intended to be used as a buyer's guide. The only help if provides to buyers is to show that bidding below the bare minimum production costs on the AH is probably not going to complete the sell.

I've finish updating and revamping the spreadsheet for build 0.25.1.07

New Format of Prices Sheet
Now, you can enter a mark-up rate for Society, Local AH and Regional AH. I even color coded the columns for each sell price and for the manually entered tax rates.

All calculations are now dynamic. So when you change wages or enter a price paid all effected recipes production costs will be automatically updated.

I also changed the wages calculation for medium and large shipyards to be multiplied by the number of lots they use.

From dpi209 here's the link to Open Office.
OpenOffice.org

Those seem to be the most popular choices to deal with Excel spreadsheets without Microsoft Office.

To extract the file from the .zip file here's a link to WinRAR they have a free trial version to download. I've been using a trial version from them for years.

WinRAR Downloads Page

Update 1/26/08
Cleared all previous updates from this page.
Organized Ship Outfittings into Colossal, General, Huge, Large, Medium and Small to match in game categorization.
Moved Bandages and Smelling salts to Consumable Items General tab to match in game categorization.
Rearranged the tab order to match the Prices Page order. With Euro-Trades then Structures right after the Prices tab
Added the Settings tab to better organize for all the new features I've added.

New Features:
Selecting owned structures - This effects how upkeep, wages and labor are calculated if you are using a larger shipyard than the minimum required, or using an advanced structure. Added a second column/row for Structures and for Labor on the recipes pages to support this feature.
If you select yes to owning a structure better than the minimum require the recipe will calculate based on the better structure, i.e.. If you select Master Shipyard, Large ALL recipes using any shipyard or the Fishing Lodge (except Net Fish) will calculate based on the Master Shipyard, Large. This may result in a higher cost per item.
Combined the Shipwright Materials (Basic) and (Advanced) tabs into one.
Added a choice to use or not use Advanced Recipes
Added a choice to calculate structure upkeep or not.
Added a choice to set taxes globally or per structure.
Added a choice to set wages globally or per structure.
Added a quick sales calculator to the prices page with two totals. One rounded to the next whole doubloons and one using the rounding scheme found in the two blue mark-up columns.
Added grouping to the sections on the Prices tab to reduce the amount of scrolling needed.
Added conditional formatting to the Yes/No choices on the Settings tab so Yes is gold background No is grey.

Edit 1/27/08 a: Fixed a minor text issue on the settings page. Apparently Open Office has an issue with blank cells in a vlookup table. So, I've entered a 0 in all the purchase prices on the prices page to solve this issue. Of you're using Open Office, grab the file with Open Office fix in the name. Anyone using a regular version of Excel/ MS Office should be able to use either file just fine.

Edit 1/28/08: A guild mate told me the Cannon Ammo prices were showing as 5 db instead of the normal prices. So I checked and found a vlookup error in the Ammo section of the prices page that was calling for the taxes instead of cost per column on their recipes. I checked rest of the sections and found the Structure Deeds section of the prices page was also effected the same way. I only made the corrections to the version that works in both Open Office and Excel, removed the other files and uploaded this one file.

Update 1/29/07
Corrected some spelling errors
Added another new feature
Corrected the materials quantities for Huge Cannon and Advanced Huge Cannon recipes.
New Features:
Selection for European Trader Factions
Euro-Trades tab now gives the adjusted turn in quantities based on faction standing selection on the Settings tab

Update 2/1/08
Corrected studding sails and smelling salts recipes. These should both be giving the correct prices now.
Added a 2nd drop down menu to the quick sales calculator to make it easier to find the item you're looking for.

Update 2/15/08
Added Recipes for Bermuda Trader's Sloop, Exceptional and First Rate Hull Patch and Structure Brace
Corrected the name of the Fortified Wine Rations, Exceptional and First Rate recipe output names to Keg of Fortified Wine, Exception and First Rate to match its in game name.
Corrected all the Upkeep formulas on every page to match those on the Raw Materials page, so choosing not to include the structure upkeep costs will actually work as expected for all recipes. OOPS, sorry for missing this when I added that feature.

Update 3/06/08
Made several recipe corrections as they were pointed out either in this thread of via a PM. I just forgot to add what was corrected to the Info page, so I don't recall all of them. In any case, here's the latest file.

[Monthar]

Here's a copy of what's on the Info page. For those that have complained about sorting issues, the first line as seen below has been on the Info page since the very first version. So yes, you were warned before you hit those errors.

Do Not change the sorting on any page or the calculations will be wrong! Raw Materials are those which only cost labor and doubloons. Manufactured Goods are those which requires one or more Raw Materials to create Shipwright Materials (Basic) are those made up of Raw Materials and/or Manufactured Goods Shipwright Materials (Advanced) are made up of Shipwright Materials (Basic) and/or Manufactured Goods and/or Raw Materials Recipes that list Fishing Lodge can be built in Small, Medium and Large Shipyards as well as the Fishing Lodge. All recipes requiring a Shipyard (Small) can be built in Small, Medium and Large Shipyards All recipes requiring a Shipyard (Medium) can be built in Medium and Large Shipyards All recipes that require Draughtsman Office can be made with a Master Draughtsman Office Some reformatting to support calculating production costs per item. Changed all labor to be displayed in hours and added a wages column which is labor hours times 10 doubloons. The wages are just to give a baseline estimate for figuring required labor into the equations. Items in blue on a recipe denotes an item that is required for the recipe but you get back/retain when producing that recipe's output. Example: Cheeses requires livestock that you get back/retain when making the cheese. Changed the wages formula for recipes requiring a medium or large shipyard to use a factor of the number of lots these structures require. Large shipyard now requires 3 lots so wages are 3 times the labor hours times 10 doubloons. Medium shipyard recipes have wages set at 2 times labor hours times 10 doubloons. Removed the Books tab since we can no longer research the books. All books in game are found as loot. All the new ships are on the New Ships tab for now so I can get this update published as soon as possible. Added Advanced Recipes to their own tab since that are only usable by Freetraders with the appropriate skill. Renamed tabs and sections on the prices tab to match the listings in the AH, Warehouse and Inventory screens. With the exception of the new ships. I gave them their own tab for now. Update 3/14/07 Taxes have been added. Update 4/15/07 Added a Lumbermill's Wood Kits sheet and section on the Prices sheet for hull and mast parts and a sheet for just these parts to get the numbers right. I also took out the rounding in the society prices column. Update 7/5/07 added new recipes and reformatted the prices page to be easier to read when printed. All Recipes requiring leather and cured meat now use the cheaper versions made from Livestock. I reversed the Local and Regional AH columns and made Local the higher price due to the higher listings fees in the Local AH. Added Structure Upkeep Update 7/28/07 Added new ships, new outfittings and updated output quantities on ships; sail, hull & cannon outfittings Update 8/12/07 All recipes with multiple outputs such as Granite/Gravel, Limestone/Gravel, Marble/Gravel, Beef/Hides, Refined Sugar/Molasses, no longer split the cost between both products. With the exception of Silver/Lead. Silver is still at 2/3 cost and lead at 1/3 cost to keep their prices in line with the other metals. Update 8/25/07 Added new Advanced Freetrader Recipes received from Career Missions. Added new Streamlined Structure 1-4, Exception & First rate Spare Guns recipes. Update 9/8/07 Added new ships - No recipe found for the new 'La Fayette' Frigate. Added new recipes for Huge & Colossal outfittings. Added a few more rare recipes. Update 9/14/07 Added Refit Ships' Deeds and Captured Deeds sections to the prices page. Captured deed's costs are the production costs divided by the durability points. Update 11/4/07 Added Pirate Destroyer Frigate, First Rate Mast Brace and updated all labor rates to match build 0.27.81.00 Update 1/13/08 Added Careening Camp recipes, new ships & Lineship Structural Materials. Removed refit packages since they can no longer be manufactured. Removed rounding everywhere except the mark-up columns on the prices page. Incorporated vlookup formulas to make future updates a bit easier. Update 1/16/08 Rearranged the order of sections on the Prices Page. Unrest Bundles have not yet been added. I'll get those added in the next update. Update 1/18/08 Added the Unrest Supplies recipes. Removed the lock from the Prices Page, due to issues with this page in older versions of Excel no accepting the user password. Corrected a few missed recipe updates that Jessamyn pointed out. Added the info for folks to send donations to my pay-pal account to to top of this page. Update 1/19/08 Corrected the recipe for Butcher Beef. Fixed a vlookup error for the cost of the teak logs which was causing recipes using them to be off. Adjusted the vlookup formulas for Crate of Hides, Leather and Meat, Cured to use the cheapest value between the from livestock and from game animals entries, unless an cost is entered in the purchase price column of either one. Added Recipes Draft Master Large Shipyard and Construct Refit 'Mystique' Poleacre. Creator: Monthar Contributors: Monthar, Valeroth and shadowsong42 shadowsong42 gave better, dynamic, rounding formulas for the prices page, which are now in place.

[wcs7000]

I'm glad for the return of this sheet! Thank you!

[Edited question that was based on a misunderstanding.]

Thanks for clarifying the Wages.

Great, great job!

[Monthar]

Because the weekly upkeep is always the same for every structure. So it's already in all the calculations. Thus there's no need for you to figure them out manually. I already did it for every recipe. It's been in the calculations since last summer.

Update 7/5/07 added new recipes and reformatted the prices page to be easier to read when printed. All Recipes requiring leather and cured meat now use the cheaper versions made from Livestock. I reversed the Local and Regional AH columns and made Local the higher price due to the higher listings fees in the Local AH. Added Structure Upkeep

[Monthar]

I was replying to this.

fwiw, structure upkeep isnt the same for all structures

However it seems to have been deleted while I was typing the reply. I'll leave this answer here anyway to make sure everyone has a clear understanding of how I've had the structure upkeep calculations running.


Of course it's not the same for every structure. It's the exact same number of hours in a week. If you look at the structures tab, you'll see I entered all the actual upkeeps for every structure. Then look at the formula for any recipe's upkeep and you'll see it's calculating based on the minimum structure that can run that recipe, ie, the structure listed on that recipe.

So yes, if you're using a Large Shipyard to make items a Fishing Lodge can make the calculations are going to be low. Telling it which one you're actually using hasn't been a high priority on this sheet. Besides I haven't quite figured out how best to make this selection.

On top of this, I'd also have to work in how to change the labor usage if it's an advanced shipyard, master shipyard, etc.

I have thought about these and that's going to be a lot of work incorporating.

Oh and for those that do use the Labor/Wages block to add a global wages calculation. It does calculate based on the both the hours used in the recipe and the number of lots required by the minimum structure. So a medium shipyard at 10db/hr on a 2 hour recipe would calculate that as 10*2*2 since it takes 2 lots.

PS: I didn't make it real clear before now, because I was using this as a means of economic warfare. Since knowledge is power. The knowledge of how the upkeep was calculating gave my friends, society mates and I a slight advantage in undercutting others to get our sales.

[NancyJ]

The ambiguity of the english language I deleted the comment because I re-read your sentence with a different interpetation and it made more sense.

[Angafea]

Very, very, hugely, phantasmagorically useful!

Thanks so much! Rep for you!

[srmx2]

Silly question, but how do I figure out my hourly wage?

[Monthar]

Originally Posted by srmx2 Silly question, but how do I figure out my hourly wage?

First you need to ask yourself this question. "What is my time worth?" Then ask, "How much time do I spend shipping goods?" Next, "How much could Io make during other activities during that time?"

If you only play 2 hours a day and half that is spent shipping goods and operating your structures,.That might be worth mroe to you than someone else spending the same hour but playing for 8 hours a day.

In other words. You're the only one that can answer your question.

[srmx2]

Originally Posted by Monthar First you need to ask yourself this question. "What is my time worth?" Then ask, "How much time do I spend shipping goods?" Next, "How much could Io make during other activities during that time?" If you only play 2 hours a day and half that is spent shipping goods and operating your structures,.That might be worth mroe to you than someone else spending the same hour but playing for 8 hours a day. In other words. You're the only one that can answer your question.

Makes complete sense!

Thank you and excelent job!!