open All Channels
seplocked Science and Industry
blankseplocked Invention and Manufacturing Spreadsheets v1.2
 
This thread is older than 90 days and has been locked due to inactivity.


 
Pages: [1] 2 3 4

Author Topic

Lore Varan
Caltech Shipyards
Posted - 2010.04.30 02:54:00 - [1]
 

Edited by: Lore Varan on 12/03/2011 20:17:09
Edited by: Lore Varan on 26/02/2011 05:12:43

Caltech Shipyards

Invention and Manufacturing Mastersheets v1.2


Invention and Manufacturing Mastersheets v1.1


Screenshot

Requirements


Open Office 3.2 ( Earlier Versions may work , but have not been tested )

350Mb Free memory recommended ( The "Everything" Sheet is a bit of a beast )

Eve Skill Production Efficiency level 5
Sorry this sheet is not suitable for new industrialist who have not trained lvl 5 PE yet.

Security


Macro execution permissions are required for some features

Open office Macro security has an option to allow macro's in spreadsheets that are located in a certain folder ( Very High security ).

If you decide to allow macro execution I strongly recommend that you use this option rather than enabling macro's globally ( Low Security )

This is located in
Open Office > Tools > Options > OpenOffice.org > Security > Macro Security > Trusted Sources > Trusted File Locations > Add Folder
( Create a folder if neccessay first and move the spreadsheet to that folder )

Basic spreadsheet functions do not require macro's so you can still use this sheet even if you do not wish to grant macro permissions

Purpose


  • Calculate the best way to Invent any item.

  • Calculate list of required purchases for invention attempts + Cost

  • Calculate list of required purchases for T1/T2/T3 manufacturing + Cost

  • Calculate total build cost of items T1/T2/T3* ( bpc costs for T3 not included )


Features


  • Quick List T2 Items by Invention+Build Profitability or Profit per Item

  • Quick List all purchases | subcomponent builds required

  • Download Prices from Eve-Central

  • Download Prices from Eve-Metrics

  • Customisable Look


Usage


The sheet is primarily designed to work out the best ( cheapest invent + build cost ) method of inventing T2 items.

It also includes functionality to enable you to quickly work out what you need to purchase to make these invention attempts as well as make T1 items , T2 items ( bpo or invention ) and T3 items.

You will need to spend a little time setting up some values in the sheet before you will get accurate results.

Market price information has been pre-loaded for each item where possible - Data supplied by Eve-Central and Eve-Metrics.

BPC price information has been entered by me and in each instance is a guestimate.

The manufacturing parts of the sheet are designed and are easiest to use if you are building subcomponents yourself.

You will need to enter the following information before you will get accurate results.

M.e. level of any blueprints you are building from
--> For Item Build Cost Calculations as well as material purchase requirements.

P.e. level of any blueprints you are building from
--> For build times and profits per day

Your Invention skill levels in the Invents Sheet
--> For all calculations involving invention.


A quick note on price evaluation.

Invention & Manufacturing formulas in these sheets take the higher of ( market price or build cost ) for subcomponents when calculating build cost for items that require these subcomponents.

Customising the sheet.

It is not possible to remove unwanted item rows from the Make or Buy sheets, this will break some calculations.

It is possible to hide unwanted rows and should also be ok to move rows up or down.
Adding columns for your own calculations should also be fine.

Do NOT rename the subsheets if you are using the macros, they will break.

Lore Varan
Caltech Shipyards
Posted - 2010.04.30 03:00:00 - [2]
 

Edited by: Lore Varan on 08/11/2010 21:14:21

The Make Sheet


This sheet is for manufacturing.
Items are grouped in tables based on there market group.

1st Column
This shows the item name - Table headers show Market Group

Profit/Day ( Formula Cell )
Shows you how much isk you will make per production line per day making this item.

To Build ( Formula Cell )
Shows the number of production runs you need to start ( taking into account Batch size ) to produce the amount of item requested in the Quantity Field + any sub component build requirements.

Quantity ( Input Cell )
Specify here the number of final items you want to build.
You do not need to specify subcomponents needed for other items, this is done automatically.

M.E. ( Input Cell )
Specify in here the M.E. level of the blueprint you are building off.

Sorry it is not possible to specify different me's for different blueprints of the same type.
IF you are in this situation then use the me of the least researched blueprint to ensure you always purchase enough materials

* For T2 items this field can be populated automatically from the Invent Sheet by pressing the "Copy best to make sheet" button.

Misc Costs ( Input Cell )
you can specify here any other costs you incure per item build.
This field is used for example to put in the npc charge for a manufacturing job.
* For T2 invented items the Invention cost is put in here
* This field is also automatically populated from the Invent Sheet by pressing the "Copy best to make sheet" button.

Value ( Input Cell )
The Market Value of an Item.
This can be populated automatically from the Eve-Metrics and Eve-Central subsheets by pressing the "Apply Prices" buttons in those sheets.

Build Cost ( Formula Cell )
The Calculated cost for constructing this item from subcomponents or raw materials

When calculating the invent costs or build costs of an item that requires subcomponents to build
The highest of ( Value and Build Cost ) is used as the value of the item.

Required ( Formula Cell )
When building an item that requires subcomponents , the total number of subcomponents required for all builds appears in this column.

In Stock ( Input Cell )
If you allready have an amount of this item, specifying it here will remove the specified amount from the build reqs and purchase requirements.

Purchase ( Input Cell )
If you want to purchase a subcomponent rather than build it, put the amount you are going to purchase in here.

#BPO ( Input Cell )
The number of Blueprint you have for this item.

per BPO ( Formual Cell )
The number of build runs per BPO.

P.E. ( Input Cell )
Specify the p.e. of your blueprint here.

Days ( Formula Cell )
The Required number of days to build all the items you need or want.

TypeID ( Data Cell )
The TypeID of the Item.
This value is used to fetch and apply the prices ( Eve-Cental and Eve-Metrics ) Sheet
If you do not want a price to update automatically , remove this value or comment it out with a # in front of the number.
You will need to unprotect the sheet to do this.



In the top right of the sheet you will see an input cell where you can input a build time modifier.
Use this cell to adjust the build time of items.

Your build time modifier depends on you build skill ( industry iirc ) and an implant you have for build time improvement , plus any bonus you get from the location you are building in ( POSes )

The easiest way to work it out is to divide the base build time on a particular bpo by the actual build time you get when installing a job.

You only need to specify this value once.

Lore Varan
Caltech Shipyards
Posted - 2010.04.30 03:19:00 - [3]
 

Edited by: Lore Varan on 08/11/2010 21:14:49

The Buy Sheet


Here you update prices / stock levels of input materials and can see what you need to purchase.


First 2 Columns
Specifies [ type of input or item market group ] and item name.

Quantity ( Formula Cell )
The amount of the material you need to Purchase ( Requirements - Stock Level )

Price ( Input Cell )
The market value of a material.
This can be populated automatically from the Eve-Metrics and Eve-Central subsheets by pressing the "Apply Prices" buttons in those sheets.

Total Cost ( Formula Cell )
Cost for purchasing the amount of the material you need as specified in Quantity

Required ( Formula Cell )
The amount of the Item you need to do all the specified Builds / Invents

Stock ( Input Cell )
If you allready have an amount of this item, specifying it here will remove the specified amount from the purchase requirements.

TypeID ( Data Cell )
The TypeID of the Item.
This value is used to fetch and apply the prices ( Eve-Cental and Eve-Metrics ) Sheet
If you do not want a price to update automatically , remove this value or comment it out with a # in front of the number.
You will need to unprotect the sheet to do this.


Lore Varan
Caltech Shipyards
Posted - 2010.04.30 03:37:00 - [4]
 

Edited by: Lore Varan on 08/11/2010 21:15:15

The Invent Sheet


This sheet contains 3 Tables

The 1st table enables you to enter the level of your invention related skills.

For operation ease , I recommend that once you have entered your values in here , you unprotect the sheet and hide the rows of this table.
Then unfreeze the sheet ( Menu Bar > Windows > untick freeze )
and refreeze the sheet with the cell cursor in the first cell in the "Cost per run" column.
This will freeze the table header of the 2nd table at the top of the sheet.


2nd Table contains the best invention method for each item.
3rd Table contains all invention methods for each item ( from which the best is selected and highlighted )

Fields ( all fields here are formula fields with 1 exception )


First Field - Contains Item Name

Line ( Best Methods Table ) / Attempt ( All Methods Table ) ( Input Cell )

In the best methods table this field shows you on which row of the spread sheet you can find the best method for inventing this item.

In the All methods Table you will find an Input Cell where you can input the number of invention attempts you want to make using this method.



Cost per run - The invention costs per successful output run received ( takes into consideration - cost of inputs / invention chance / number of runs )

Item Build Cost - The final cost of building this item using this method , taking into account invention and build costs.
( Whichever method has the lowest overall item build cost is selected as the best method )

Invention Cost - The ammount of isk required to make an invention attempt using this method.

Decryptor - Decryptor of the method

Meta Item - Meta item of the method

Input BPC - Input Bpc of the method ( either single run or max runs )

M.E. - The output m.e. of the bpc using this method

Chance - The chance of success using this method with your skills

Runs - The number of output runs received with this method

P.E. - The output p.e. of the bpc using this method

Profit per Item - The value of the item ( as specified in the "make" sheet ) - the invention and build costs per run

Profit per Day - The ( profit per item ) / The amount of time to manufacture the item
This is the profitability of manufacturing the item , does not take invention time into account.
It is also assumed that it is possible to sell all the items constructed as the current market price.
Take this value with a big pinch of salt and research the sales volume of an item before creating 100 runs of the golden looking bpc's


Ghanid
Posted - 2010.04.30 08:32:00 - [5]
 

Errr, do you really mean OpenOffice 3.3? I have the latest version which is only 3.2

Dav Varan
Posted - 2010.04.30 13:09:00 - [6]
 

Originally by: Ghanid
Errr, do you really mean OpenOffice 3.3? I have the latest version which is only 3.2


Fixed typo , thanks.

Lore Varan
Caltech Shipyards
Posted - 2010.04.30 20:48:00 - [7]
 

Edited by: Lore Varan on 08/11/2010 21:25:00

The Summary Sheet & Macro Functionality


The summary sheet



The four buttons on the left.


a.k.a. Show me the money buttons.
Lists all the inventions jobs in the invent sheet and sorts them by Profit or Profitability

Non Macro Workaround.

1.Copy the entire Invention sheet.
2.Paste into a new spreedsheet or subsheet.
3.Sort away to your hearts content.

Do not sort the invention sheet itself , this will almost certainly stop the sheet from working.

Either way remember that the profitability column assumes that you can sell all the items you build.
For low sales volume items - this value is missleading.

Purchase and build summary Button


Simply lists all the materials you need to buy.
Followed by the amount of each subcomponent you need to build.
Followed by the amount of each finished product you need to build.

Includes the build cost - for subcomponents and finished products.

Copy and paste this into eve notepad.

Non Macro Workaround.

Copy and paste into a new sheet , the "make" and "buy" sheets
deleted columns / rows as neccesaary.

Purchase and Build HTML


This generates a Purchase and Build Summary that can be viewed in the EVE In Game Brower.

The summary is generated as a HTML file on your local hard drive.
The link to use to view the file in the IGB is displayed in the second column in the output area of the Summary sheet along with some basic instructions.

An Index HTML file off all purchase and build summaries is also constructed and the link to it displayed.

Viewing the HTML file in the IGB enables you to open up the market screen at the correct item by clicking on an item in the list.


Apply Button


This repaints the spreadsheet with the colours / borders / background colours / fonts and number formats you specify.

The 7 boxes to the right of the button are where you specify fonts/colors/borders etc.
Just use the standard spreadsheet format cell options to alter these cells.

The 4 boxes to the left of the button are where you specify number formats.

Iskcolumns > the number format of all *(except buysheet.value) columns that contain isk values
These isk values are usually quite large and dont need isk cents by default and negative values in red.

Intcolumns > the number format of most non isk columns , no decimal places by default and negative values in black.

Floating point columns > isk cents included in format , only used in the buy sheet value column and the download sheets.

Percentage columns > How to display a percentage , only applied to chance column in Invent sheet.

Non Macro Workaround.

Apply any formats you want manually.

Other Macro Buttons


"Optimise columns" - Makes all the columns on the sheet the optimal width.
This is a shortcut for - unprotect sheet > apply optimal column width > reprotect sheet.

"Copy best to make sheet" on the Invention Sheet.

This goes through each best method in the sheet and copys the values for ( m.e. , p.e. and Invention costs ) to the make sheet.
These values can of course be copied manually if you are just interested in a few items.

Note.

If you want to build some t2 items from a BPO you have and you do not want your values for m.e. , p.e. , misc cost overwritten for that item do the following.

unprotect the make sheet.
Place a #Mark or "." in front of the items name in the first column
reprotect the make sheet.

Lore Varan
Caltech Shipyards
Posted - 2010.04.30 22:25:00 - [8]
 

Edited by: Lore Varan on 08/11/2010 21:15:59

The Download Sheets


Eve-Metrics and Eve-Central download sheets both work the same way.
Eve-Central has a volumes column whereas Eve-Metrics doesnt but it does have a simulated value , which is imo the besst value to take.

Reset Button
Clears the sheet and builds a fresh list of every item that a price can be obtained for.

Drop down
Select the region you want prices for.

Eve-Metrics / Eve-Central Button
This fetches the pricing data from the relevant websites.
This will cause the sheet to lag out while the prices are downloaded.

Apply Prices
Applys the value in the formula column for each item to the rest of the spreadsheet.

Formula to Apply
Enter into the box the formula you would like to apply to the download data or leave blank for the default formula.
Any cells selected in the formula should be on the line above the header.
This gets altered as the formula is copied down the sheet in the same manner as regular copy and paste of spreadsheet formula's

By default Eve-Central sheet uses the highest value of Average Buy orders or minimum sell orders.
By default Eve-Metrics sheet uses the highest of the Simulated prices ( buy or cell )

Clicking reset copys your new formula into the formula box for each item.


Optimising

Its not neccessary to download a full list each time.
Once a full download has been performed and those prices applied you can cut out rows you dont want to upate each time you download.

Maybe just have the minerals and moon goo as regular downloads.
You can alter the formula for individual items if you wish.
Beware that pressing reset will remove these customisations.

Copy and paste your optimised download list into a spare sheet.
That way when you do need to do a full download refresh , you can paste you optimised list back in afterwards.


Questions or bug reports



Please post any queries about the sheet in here rather than in-game mail.

Regards
Lore Varan.

Stafen
The Workers Union
Posted - 2010.05.01 19:13:00 - [9]
 

This topic deserves a reply, especially as I have been thinking of making similar spreadsheets myself. They look very complete, with a nice at a glance list of the most profitable items.

Making the spreadsheets in openoffice is nice as it's free, no need to acquire a version of excel.
(even though using excel's xml import functions makes importing data trivial)

The macros are visual basic, and you can inspect the source code, so someone can easily verify nothing is amiss. It takes a bit of time to import the prices though, CPU is not at 100% and it does fetch them in batches of 19, so the bottle neck is probably the server.




Emporer Norton
Posted - 2010.05.01 20:47:00 - [10]
 

Nice set of sheets thank you

Lore Varan
Caltech Shipyards
Posted - 2010.05.02 16:11:00 - [11]
 

Originally by: Stafen
This topic deserves a reply, especially as I have been thinking of making similar spreadsheets myself. They look very complete, with a nice at a glance list of the most profitable items.

Making the spreadsheets in openoffice is nice as it's free, no need to acquire a version of excel.
(even though using excel's xml import functions makes importing data trivial)

The macros are visual basic, and you can inspect the source code, so someone can easily verify nothing is amiss. It takes a bit of time to import the prices though, CPU is not at 100% and it does fetch them in batches of 19, so the bottle neck is probably the server.





Yeah the download is not great.
On my quad core cpu runs at 25% while downloading which leads me to believe that OO only supports single core operation.

I decided to use OO basic ( very similar to VB :) ) to do the macros as the macro code can travel with the spreadsheets.
Anyone with OO Basic or Visual basic experiance will find it easy to check out the code before running it.

The other way to do XML imports in OO is to use XML Filters, but they need to be installed independantly by the user afaik.

Basically what happens is the XML returned by the server is opened as a new CSV spreadsheet ( invisible ) then the macro code parses the the spreadsheet to extract the required info.

Not the most efficient way to do it , But it works.

There is a 3rd method of getting XML into OO using a component known as the SAX parser.
I am going to have a play around with it these next couple of weeks to see if I can get the download working more smoothly.

Lore Varan
Caltech Shipyards
Posted - 2010.05.07 15:48:00 - [12]
 

Bumpage

Lore Varan
Caltech Shipyards
Posted - 2010.05.17 13:10:00 - [13]
 

Bumpage

Jobby
Minmatar
PURPLE.
Posted - 2010.05.17 14:33:00 - [14]
 

Looks quite useful, will certainly be checking this out. :-)

Thanks.

Kordel Trask
Posted - 2010.05.17 23:51:00 - [15]
 

Very Nice,

I too appreciate the open office format and the open visual basic.

Thanks for the open office docs on behalf of those who don't have MS office. (I have both installed)

The Visual Basic.
That's a big benefit for those of us trying to figure out how everything works for ourselves.

Thanks for all of the work and sharing it.

Lore Varan
Caltech Shipyards
Posted - 2010.07.21 13:55:00 - [16]
 

Bump before the lock

Bad Princess
Minmatar
Posted - 2010.08.05 13:26:00 - [17]
 

Black on dark blue is impossible to read.


Lore Varan
Caltech Shipyards
Posted - 2010.08.05 13:36:00 - [18]
 

Edited by: Lore Varan on 05/08/2010 13:52:01

Originally by: Bad Princess
Black on dark blue is impossible to read.




There should be no black on dark blue in the sheet by default!

The sheet should look like this

Are you using Open Office 3.2 or another version ?

That said you can apply whatever colours you like by pressing the apply button on the summary sheet.
Apply your own colours to the input cells next to that button.

If that does not work then you can allways apply colours with the standard format tools of the spreed sheet.

The headers are dark blue and the text color in them is automatic.
If this is not working properly then try making the text color white and hitting the apply button.


Lore Varan
Caltech Shipyards
Posted - 2010.08.25 15:17:00 - [19]
 

Bumpage

Jeddeita
Minmatar
Lead Farmers
Kill It With Fire
Posted - 2010.08.26 09:13:00 - [20]
 

Many thanks for the work you have put into this. Just one question - does the invention profit/day take into account the % chance of a invention job working? Just need to know so I don't mess up my maths :)

Cerrano
Caldari
Posted - 2010.08.26 09:24:00 - [21]
 

This sheet looks promising, I'll take a closer look when I'm home from work. But it looks like it covers what I've been looking for. Thanks.

Lore Varan
Caltech Shipyards
Posted - 2010.08.26 19:09:00 - [22]
 

Edited by: Lore Varan on 26/08/2010 19:57:27

Originally by: Jeddeita
Many thanks for the work you have put into this. Just one question - does the invention profit/day take into account the % chance of a invention job working? Just need to know so I don't mess up my maths :)


Yes percentage chance is taken into account.

Of course this makes it an averaged value that can be expected over many runs.

Specifically.

Invention Attempt Cost = ( Number of Datacores * Cost of Datacore type 1 ) + ( Number of Datacores * Cost of Datacore type 2 ) + Decryptor Value ( If used ) + Meta Item Value ( If Used ) + Input Bpc Value

Chance = MIN(1;Item Base Chance*(1+(0.01*Encryption Skill))*(1+((Datacore 1 Skill + Datacore 2 Skill)*(0.1/(5 - Meta Item Meta Level))))*Decryptor Bonus))

Then

Invention cost per output BPc run = Invention Attempt Cost / Chance / output BPc runs
Manufacturing cost is calculated using the me of the output BPc

Then

Total build cost per finished item = Invention cost per output BPc run + Manufacturing cost.

Profitability = ( Product Value - Total build cost per finished item ) / Manufacturing time.

So the profitability relates to the time taken up in the manufacturing slots.
This beacause invention is a much quicker process than manufacturing and is rarelly the bottleneck.








Jason W0rthing
Posted - 2010.08.26 21:24:00 - [23]
 

Hi,

I just had a question about the cost calculations that you are using. You said you use the higher of the values for calculating build cost. I find this to be somewhat inaccurate. When using invention programs and such with market aggregating tools such EVE-central or EVE Metrics I find the best way to approximate the profitability of an item at a glance is to use minimum sell price, for both buying and selling items. This way you are essentially calculating revenue for an item off what you would sell it for 0.01 isking, and what you would buy materials for from the cheapest jita-market supplier. I have found these calculations to be reasonably reliable in determining profitability.

I just wanted to know your rationale behind the method you chose for cost calculations.

Lore Varan
Caltech Shipyards
Posted - 2010.08.26 23:32:00 - [24]
 

Edited by: Lore Varan on 27/08/2010 02:22:20
Originally by: JASON W0RTHING
Hi,

I just had a question about the cost calculations that you are using. You said you use the higher of the values for calculating build cost. I find this to be somewhat inaccurate. When using invention programs and such with market aggregating tools such EVE-central or EVE Metrics I find the best way to approximate the profitability of an item at a glance is to use minimum sell price, for both buying and selling items. This way you are essentially calculating revenue for an item off what you would sell it for 0.01 isking, and what you would buy materials for from the cheapest jita-market supplier. I have found these calculations to be reasonably reliable in determining profitability.

I just wanted to know your rationale behind the method you chose for cost calculations.


Subcomponents

Subcomponent price evaluation works as follows.

In the make sheet.

A subcomponents value is the higher of market value or build cost.
This evaluation is used as the Value of the item for all cost calculations for items that require the subcomponent for there construction.

On the subsomponents actual row , the value of the subcomponent is taken to be just market price.

So on the subcomponents row ( profit = market value - build cost )



It is done this way so that it is possible to do either
1) see the profit per slot ( subs and final build )
or
2) see all the profit just on the final build.

Normally what you want to see is the 1) Profit per slot

unfortunettelly this doesnt work for some items.
Jump freighter for example.

There is no market ( check jita ) for Capital T2 components, Therefore there market value is extremmely low.

T2 Cap build lines show a massive loss and the JF build shows a massive profit.

This is not helpful.

In these instances it is more helpful to 0 out the T2 CAP components market price and see the overall profit on the JF slot.

Profit per item is accurate.
Unfortunelltley profitablity is not.

It is correct for the final build but does not take into account the subcomponent build times.

Please be aware of this when looking at profitabiity on JF builds or any other item that you have had to zero out the market values for to get useful information.


In the invention sheet

Subcomponent prices here are evaluated as the higher of build cost or market value.

In some cases where the subs can be brought off the market cheaper than can they can be constructed. then the profit per item shown in the invention sheet will be slightly lower than what it would be in reallity.

So by buying cheap subs you make a bit more than what the sheet shows you.

But have you made these profit from the build or from trading ???

Not sure on that so I like to be on the safe side and never show profits which arnt due to manufacturing.

I originally used a different formula.
basically use market price if available else use build cost.

However I did not like the results.
By using default prices from Eve-Central Eve-metrics massive huge humongous profits were shown on JF's and some other items where there was no market for the subs.

This was extremley dangerous and missleading.


Lore Varan
Caltech Shipyards
Posted - 2010.08.26 23:51:00 - [25]
 

Originally by: JASON W0RTHING
Hi,

I just had a question about the cost calculations that you are using. You said you use the higher of the values for calculating build cost. I find this to be somewhat inaccurate. When using invention programs and such with market aggregating tools such EVE-central or EVE Metrics I find the best way to approximate the profitability of an item at a glance is to use minimum sell price, for both buying and selling items. This way you are essentially calculating revenue for an item off what you would sell it for 0.01 isking, and what you would buy materials for from the cheapest jita-market supplier. I have found these calculations to be reasonably reliable in determining profitability.

I just wanted to know your rationale behind the method you chose for cost calculations.



Market prices from download sheets.

These are customisable.

I take simulated from eve-metrics ( the price required to sell/buy 5% of the entire market iirc ) and higer of ( average buy | minimum sell ) from eve-central.

The prices downloaded are for a region.
minimum sell can be misleading on a region level.
e.g.
The forge.
There may be a sell order for 27 trit in Friggi @1.9isk.
Whereas buyers are buying in jita for 2.3isk.

What is the value of trit ?

I tried to find a formula that is a good fit for a lot of different products.

raw materials , subs , finished products.

Its perfectly possible to set up the sheet to always take minimum sell price.

In the download formula box create a formula that simple links to the cell on the same row as itself in the minumum sell column.

Press reset.
Press download.

You may find minimum sell works well for some items.
For other items it wont be accurate.

Each item can have its own different formula if you want to make the sheet really accurate.

e.g.
For trit you could use average buy price.
And for Absolution you could use minimum sell price.

just change the formula in the yellow box in each items row.

These customised formula stay in place when you next download as long as you dont press reset.

pressing reset will use the formula in the default formula box for each row.
If there is no formula there . ie it has been cleared by teh user then the sheet substitutes my default formula.




Lore Varan
Caltech Shipyards
Posted - 2010.08.27 01:50:00 - [26]
 

Originally by: JASON W0RTHING
Hi,

I just had a question about the cost calculations that you are using. You said you use the higher of the values for calculating build cost. I find this to be somewhat inaccurate. When using invention programs and such with market aggregating tools such EVE-central or EVE Metrics I find the best way to approximate the profitability of an item at a glance is to use minimum sell price, for both buying and selling items. This way you are essentially calculating revenue for an item off what you would sell it for 0.01 isking, and what you would buy materials for from the cheapest jita-market supplier. I have found these calculations to be reasonably reliable in determining profitability.

I just wanted to know your rationale behind the method you chose for cost calculations.



The sheet has 2 major functional area's

Calculating material requirements ( purchases ).

In this the sheet should be 100% spot on accurate at all times.

When you have run all your builds and inventions you should have a nice empty input hangar.



Profit Evaluation.

Is a somewhat greyer area.

This sheet is not a ledger system , it is not calculating profit after the fact with accurate inputs.

The purchases total should be fairly accurate to within a percent.
Assuming prices are not fluctuating wildly.

As far as the profit per item columns go in both the make sheet and inventions sheets.

These are predicted values based on the assumption that market prices are relativelly stable over the build time.

In making these predictions and setting aside market price fluctuations the rationale has allways been to make a cautous assesment.




Val Ashir
Posted - 2010.08.27 08:28:00 - [27]
 

Edited by: Val Ashir on 27/08/2010 08:35:30
Awesome spreadsheet

Ive been using the modules spreadsheet and i get macro errors when i changed the formula in the eve-central sheet to eliminate non-buyable items.
atm it sits as =IF(sellmin=0;999999999; IF(RIGHT(TRIM(item name);2)="II";MAX(buymax;sellmin);MAX(AVERAGE(buymax;MIN(sellmed;sellavg));AVERAGE(I18;MIN(K18;L18))))) which works
what i wanted to do was return it as N/A in the first IF condition if the item lacked sell orders.

I also added a new column in the make sheet. It shows the magnitude of the average of of sell/buy orders as a log function and the ratio between sell and buy orders.

Oh i also messed with your bpc names, i put the 1run/300run at the front instead of the back so its easier to see and allows me to shrink the column width.

Lore Varan
Caltech Shipyards
Posted - 2010.08.27 13:36:00 - [28]
 

Edited by: Lore Varan on 27/08/2010 13:45:14
Originally by: Val Ashir
Edited by: Val Ashir on 27/08/2010 08:35:30
Awesome spreadsheet

Ive been using the modules spreadsheet and i get macro errors when i changed the formula in the eve-central sheet to eliminate non-buyable items.
atm it sits as =IF(sellmin=0;999999999; IF(RIGHT(TRIM(item name);2)="II";MAX(buymax;sellmin);MAX(AVERAGE(buymax;MIN(sellmed;sellavg));AVERAGE(I18;MIN(K18;L18))))) which works
what i wanted to do was return it as N/A in the first IF condition if the item lacked sell orders.

I also added a new column in the make sheet. It shows the magnitude of the average of of sell/buy orders as a log function and the ratio between sell and buy orders.

Oh i also messed with your bpc names, i put the 1run/300run at the front instead of the back so its easier to see and allows me to shrink the column width.



Use

=IF(I2=0;NA(); IF(RIGHT(TRIM(A2);2)="II";MAX(E2;I2);MAX(AVERAGE(E2;MIN(K2;L2));AVERAGE(I2;MIN(K2;L2)))))


How did you get the volume information into the make sheet ?
LOOKUP are are you diddling with the macros as well ?? :)

Glad you getting good use out of it.

Good idea on the bpc names.
If I ever manage to get around to v1.1 I'll make that the default.

Val Ashir
Posted - 2010.08.27 17:02:00 - [29]
 

Edited by: Val Ashir on 27/08/2010 17:06:11
The volume column i made uses uses 2 additional columns to look everything up.
They fetch the buy/sell volumes using =VLOOKUP(TypeID;'Eve-Central'.$B$4:$M$1314;12) for the sell volume and =VLOOKUP(Typeid;'Eve-Central'.$B$4:$M$1314;7) for the buyvolume.
then its just =LEFT(LOG(AVERAGE(buyvol;sellvol));4)&", "&LEFT(sellvol/buyvol;3) in a visible column beside the other stuff

Lore Varan
Caltech Shipyards
Posted - 2010.09.14 16:34:00 - [30]
 

Bumpage


Pages: [1] 2 3 4

This thread is older than 90 days and has been locked due to inactivity.


 


The new forums are live

Please adjust your bookmarks to https://forums.eveonline.com

These forums are archived and read-only