open All Channels
seplocked Science and Industry
blankseplocked Blueprint Profit Calculator - v1.0.3
 
This thread is older than 90 days and has been locked due to inactivity.


 
Pages: [1] 2 3

Author Topic

Azia Burgi
Caldari
Science and Trade Institute
Posted - 2007.08.26 02:09:00 - [1]
 

Edited by: Azia Burgi on 25/09/2007 09:54:35
Hello everyone

Over the past few months I have been working on a calculator that lets me work out the cost of producing any given item. This is the result. Feel free to use this how you see fit and as always I am open to suggestions/ideas/comments on future improvements. You will require MS Office 2000 or later to open the spreadsheet or the latest version of OpenOffice.org.

Blueprint Profit Calculator (v1.0.3)

Features
  • All Tech 1 Blueprints

  • Total Margin Per Unit

  • Estimated Completion Time

  • Recommended ME Level

  • Recommended PE Level

  • Stocklevel Calculator

  • Accurate Required Materials Listings (based on the inputed skill levels)


Jei'son Bladesmith
The Storm Knights
The Cool Kids Club
Posted - 2007.08.27 00:03:00 - [2]
 

niiiiiiice, obviously put alotta work into it, looks very handy Smile

question....I've played around with a few Excel spreadsheets for EVE so I know the basics but not the advanced stuff...how do you lock the top section so its stays put while you scroll through the materials list? Thats a handy lil trick, makes it look all uber and professional Very Happy

Auralis
Caldari
School of Applied Knowledge
Posted - 2007.08.27 02:53:00 - [3]
 

Nice indeed.

No more scribbling on small notes to calculate cost and profit. Yeah, too lazy to make an excel sheet of my own and wouldn't even come close to this one.

Must've taken a lot of effort.

*bows*

Ishina Fel
Caldari
Terra Incognita
Intrepid Crossing
Posted - 2007.08.27 06:58:00 - [4]
 

Originally by: Jei'son Bladesmith

question....I've played around with a few Excel spreadsheets for EVE so I know the basics but not the advanced stuff...how do you lock the top section so its stays put while you scroll through the materials list? Thats a handy lil trick, makes it look all uber and professional Very Happy



Click on the line below the line you want to fixate -> menu Window -> fixate window

or thereabouts. My Excel is German :)

Kazuo Ishiguro
House of Marbles
Posted - 2007.08.27 10:45:00 - [5]
 

The cells used to calculate the build cost of items only seem to take into account the costs of basic minerals. The costs of Morphite, T2 components and other stuff are not included (yes, there are some T1 items that cannot be built without them).

Shinhan
Dreddit
Test Alliance Please Ignore
Posted - 2007.08.27 10:53:00 - [6]
 

Originally by: Ishina Fel
Originally by: Jei'son Bladesmith

question....I've played around with a few Excel spreadsheets for EVE so I know the basics but not the advanced stuff...how do you lock the top section so its stays put while you scroll through the materials list? Thats a handy lil trick, makes it look all uber and professional Very Happy



Click on the line below the line you want to fixate -> menu Window -> fixate window

or thereabouts. My Excel is German :)


In English Excel its "Freeze Panes".

Azia Burgi
Caldari
Science and Trade Institute
Posted - 2007.08.27 11:49:00 - [7]
 

Originally by: Kazuo Ishiguro
The cells used to calculate the build cost of items only seem to take into account the costs of basic minerals. The costs of Morphite, T2 components and other stuff are not included (yes, there are some T1 items that cannot be built without them).


can you give an example please and i'll try and look into it.

Thanks for all your comments, please keep them coming. Its taken me roughly 4 months to get it into the state its in now. i haven't worked on it solidly for all that time, few hours here, a few hours there...

Azia

Azia Burgi
Caldari
Science and Trade Institute
Posted - 2007.08.27 11:54:00 - [8]
 

Originally by: Azia Burgi
Originally by: Kazuo Ishiguro
The cells used to calculate the build cost of items only seem to take into account the costs of basic minerals. The costs of Morphite, T2 components and other stuff are not included (yes, there are some T1 items that cannot be built without them).


can you give an example please and i'll try and look into it.



fixed it Very Happy

new version is now uploaded. i still have a lot of work to do on it.

Azia

Jon Asus
Posted - 2007.08.27 16:47:00 - [9]
 

For me, a lowly PE 0 manufacturer it seems to be displaying the minerals needed as far too low.

BPO calc pic

Jei'son Bladesmith
The Storm Knights
The Cool Kids Club
Posted - 2007.08.27 17:15:00 - [10]
 

Originally by: Shinhan
Originally by: Ishina Fel
Originally by: Jei'son Bladesmith

question....I've played around with a few Excel spreadsheets for EVE so I know the basics but not the advanced stuff...how do you lock the top section so its stays put while you scroll through the materials list? Thats a handy lil trick, makes it look all uber and professional Very Happy



Click on the line below the line you want to fixate -> menu Window -> fixate window

or thereabouts. My Excel is German :)


In English Excel its "Freeze Panes".


Kewl thanx guys....may your expensive implants be safely stowed in a jumpclone when you next are podded...

Zakgram
POW ZAP THWAPP
Posted - 2007.08.27 20:22:00 - [11]
 

Edited by: Zakgram on 27/08/2007 20:22:07
I don't see items like Esoteric Data Interface. It's not T2 but I guess it's Cosmos, so I'll pwn myself with a "doh...".

Azia Burgi
Caldari
Science and Trade Institute
Posted - 2007.08.27 20:58:00 - [12]
 

Originally by: Jon Asus
For me, a lowly PE 0 manufacturer it seems to be displaying the minerals needed as far too low.

BPO calc pic


thats just plain weird. the data is straight from the REV2 data export. i will look into it for you. thanks for spotting it!

Azia

Azia Burgi
Caldari
Science and Trade Institute
Posted - 2007.08.27 23:09:00 - [13]
 

Edited by: Azia Burgi on 27/08/2007 23:10:17
ok its fixed now, with thanks to Corwin Aren for his uber maths skills and not having a hangover.

i still have to go through it and reimport the production times that got missed in the original transfer.

Azia

edit: spelling

Azia Burgi
Caldari
Science and Trade Institute
Posted - 2007.08.28 02:13:00 - [14]
 

I've just uploaded new version with the correct production times loaded in. I also tidied up a few other things, added in the Ideal ME calculation. i'm not 100% happy with the formula but its the best i can get it unless someone else has any ideas.

as always, comments are welcome.

Azia

Artemdorius
Gallente
Puzzleball Industries
Posted - 2007.08.28 12:56:00 - [15]
 

Nice looking spreadsheet. I love how you've tracked even rigs and probes, something I haven't been able to find elsewhere! Very Happy

A noticable problem when opening it in OpenOffice 2.2. Namely that if there is no value for a given component in the Lookup sheet, Calc throws an Error 502 instead of assuming a value of zero. I don't know if you typed in that lookup sheet by hand Shocked or imported it from the SQL exports. If the latter, it might not be difficult to massage the SQL export slightly to put zeros in for null values.

As for your ideal ME formula, I personally use one I found over a year ago (don't remember where) that is as follows (I have changed it slightly to fit your sheet):

ROUNDDOWN(MAX(B19:B157)*BPWasteFactor*2;0)

where BPWasteFactor is the waste of the BPO at 0 ME. This is .1 for most BPOs, but some start at 0.05.

This will give you the "perfect" ME. That is, the point where any more ME research will have no effect on the build requirements.

Thanks again for all your hard work!

Azia Burgi
Caldari
Science and Trade Institute
Posted - 2007.08.28 14:02:00 - [16]
 

Edited by: Azia Burgi on 28/08/2007 14:03:11
I've now put in the missing zeros. it was a simple matter of highlighting the data and doing find [ blank ] replace with 0. i personally don't use openoffice so i wasn't aware of that problem.

thanks for the ME formula. i've set it to be 0.1 by default. when they do the next major database change (rev3) i'll include wastage factor in my queries. i am sick of the sight of that damned database Very Happy. hopefully by then i'll have figured out a way to include COSMOS and T2 without running out of cells.

please keep the comments and suggestions coming!

Azia

edit: spelling

Therese Law
Gallente
Posted - 2007.08.28 15:37:00 - [17]
 

Very nice tool
Good Job!

But to be a bit greedy and tell you what i miss.
Option to t2 material construction, t2 modules and ships.

Then it would be nice to se what a total batch profit will be both % and numbers. You can now se it per unit only.

Giving some kind of "print/copy" section where a overview of the info that you have gatherd. Like you have a sum up in a diffrent sheet and you can just select and copy and then paste it into forums etc.


Azia Burgi
Caldari
Science and Trade Institute
Posted - 2007.08.28 16:23:00 - [18]
 

Originally by: Therese Law
Very nice tool
Good Job!

But to be a bit greedy and tell you what i miss.
Option to t2 material construction, t2 modules and ships.

Then it would be nice to se what a total batch profit will be both % and numbers. You can now se it per unit only.

Giving some kind of "print/copy" section where a overview of the info that you have gatherd. Like you have a sum up in a diffrent sheet and you can just select and copy and then paste it into forums etc.


i'm not sure what you mean about a "print/copy" sheet, you can copy and paste the data already. can you explain what you'd like to see with regards to this in a little more detail, please?

the t2 items aren't listed due to physical limitations within excel. all the solutions i can currently think of are very messy and require the user to do a bit of their own manipulation. i want to keep it simple as possible.

Azia

Therese Law
Gallente
Posted - 2007.08.28 20:09:00 - [19]
 

Originally by: Azia Burgi
Originally by: Therese Law
Very nice tool
Good Job!

But to be a bit greedy and tell you what i miss.
Option to t2 material construction, t2 modules and ships.

Then it would be nice to se what a total batch profit will be both % and numbers. You can now se it per unit only.

Giving some kind of "print/copy" section where a overview of the info that you have gatherd. Like you have a sum up in a diffrent sheet and you can just select and copy and then paste it into forums etc.


i'm not sure what you mean about a "print/copy" sheet, you can copy and paste the data already. can you explain what you'd like to see with regards to this in a little more detail, please?

the t2 items aren't listed due to physical limitations within excel. all the solutions i can currently think of are very messy and require the user to do a bit of their own manipulation. i want to keep it simple as possible.

Azia


Yes i can understand that t2 stuff make things a bit more complicated.

About the "print/copy" option that i had in mind is...
You set up a bpo and all the info that is specefic to you. Then you want to share this info with someone els, on a forum or in ingame chat channel.
Insteed of writing down all numbers and info it would be easier if you could mark it and copy it and paste it and still get a good overview. When you do it now it looks like this...

Quote:

BPO/BPC Information Your Skills
BP Name: Dominix Industry: 0
BP Cost: Production Eff: 5
Number Of Runs:
Current ME: 65 Your BPO should be researched to ME783156 to be fully effective.
Current PE:

Manufacturing Information Costing Information
Time per run (seconds): 18000 5 Cost For 1 Unit: 46 350 012
Installation Cost: 1000 Cost Per Hour: #N/A
Cost Per Hour: 333 Cost Using Current Stock: 1 000
No. Runs/Hour: 0 0,2 Batch Cost: 1 000
Ideal ME: 783156 Sale Price: 50 000 000
Ideal PE: Margin Per Unit: 3 649 988
Required Runs: Margin %: 8%

Material Information
Bill Of Materials Units Cost/Unit Cost of 1 Item Material Req Total Cost
Trit 4307356 2,33 10036139,48 0 0
Pyerite 1077524 7,88 8490889,12 0 0
Mexallon 271315 24,35 6606520,25 0 0
Isogen 67501 83,47 5634308,47 0 0
Nocxium 16815 223,84 3763869,6 0 0
Zydrine 3850 1600,15 6160577,5 0 0
Megacyte 1409 4014,46 5656374,14 0 0



A bit to mutch info and a bit messy to really make something out of it fast and get the fast overview over a bpo.
So some kind of copy friendly sum up on the info that is calculated after your settings... But i dont know if this can be done or how :P
That is what i meant.

But now when writing this i se that it might not be that needed. Must have been a impulse idea i hade when i tested your sheet out.


But anyway on a diffrent thing the total profit that can be made on x amount of built units would be nice. And tak into account if you use bpc and need to buy them for a price and how that effect the price etc.





Azia Burgi
Caldari
Science and Trade Institute
Posted - 2007.08.30 01:38:00 - [20]
 

i can't do anything about how it pastes into chat, unfortunately. you'll just have to paste it in little sections. i've added something close to what you asked for, tell me if its not quite what you want.

i also fixed the way it works out ProdTime. i had typo'd the formula.

Azia

Reviera
Posted - 2007.08.30 15:46:00 - [21]
 

bump for a good thing!
more needs to se this and hopefully more updates to improve even more :)

Pwett
QUANT Corp.
QUANT Hegemony
Posted - 2007.08.30 16:16:00 - [22]
 

Edited by: Pwett on 30/08/2007 16:16:26
Edited by: Pwett on 30/08/2007 16:16:13
I wholly endorse this spp. :)

Really great work - I don't know how excel deals with XML, but if you can figure that out, here's an option for importing current mineral / salvage / datacore / other random prices:

http://www.starvingpoet.net/feeds/mmi.xml

Azia Burgi
Caldari
Science and Trade Institute
Posted - 2007.09.03 17:07:00 - [23]
 

I'm still playing about with the XML feed, not sure excel can cope with it (if anyone has any experience with importing XML into excel please contact me ingame). In the meantime i've added a link to the MMI on the credits page.

not much done on this update, just tided up my formulas ready for when i do an IGB version.

Azia

Vrizuh
VTECHS
Posted - 2007.09.03 19:17:00 - [24]
 

Go ahead. Burn a few orphanages down. You've earned such good karma that nothing can keep you out of the happy bright places now.

Azia Burgi
Caldari
Science and Trade Institute
Posted - 2007.09.05 20:37:00 - [25]
 

I have added in the two implants that effect manufacturing time. These are the only ones that directly effect the manufacturing process all the other "industrial" implants refer to blueprint research.

I have also added in an estimated TTC section based on the required number of runs.

Azia

Brubeck
Posted - 2007.09.07 00:00:00 - [26]
 

Edited by: Brubeck on 07/09/2007 00:00:41
Impressive work!

I found an error in the "Cost for 1 Unit" cell though - the formula is:

Mineral Cost + Install Cost + Hourly Run Cost

For items such as ammo that produce multiple runs per hour, this generates a much higher production cost than it actually is.

Mineral Cost + Install Cost/Number of Runs + Hourly Run Cost/Runs per Hour

Should give the correct production cost.

Thanks for putting the spreadsheet together though - it's an extremely useful tool.

Azia Burgi
Caldari
Science and Trade Institute
Posted - 2007.09.07 02:19:00 - [27]
 

Edited by: Azia Burgi on 07/09/2007 02:21:13
Thanks for pointing this out, sometimes its handy to have a fresh set of eyes look over your work. you can become blind to its shortcoming after you've stared at it for several hoursRazz. i've fixed it now, i had to modify your formula otherwise it returned division errors for items with production times over 1 hour.

the formula i have used is as follows:
MaterialCost+installcost+(HourlyRunCost*((ProdTime/60)/60))

this converts the ProdTime from whole seconds to decimal hours (1.33333 using the example of a shuttle BPO) then multiplies it by the base HourlyRunCost before adding on the fixed costs.

i still have a few things i want to add to it:
* T2/COSMOS items
* base wastage factor - not appearing until the next major DB update
* proper ammo building calculations (currently it assumes that each run produces 1 unit of ammo where as its 100 units per run) - i have just thought of a solution to this which i might implemented tomorrow when its not 3am
* standings and their effects on fixed costs.
* ideal PE
* ideal ME that doesn't look like i used a random number generator to work it out

thanks for your comments and suggestions, please keep them coming!

Azia

Brubeck
Posted - 2007.09.15 02:55:00 - [28]
 

Thanks again for the work on the spreadsheet!

Shouldn't the install cost be divided by the total number of runs? As a one time cost per production run, the amount added to each unit would be just a fraction of the full installation cost (unless you were producing just one item).

I produce a lot of ammo and drones, so the difference is noticeable. With larger items, it would be minimal.

Azia Burgi
Caldari
Science and Trade Institute
Posted - 2007.09.15 18:16:00 - [29]
 

The install cost is fixed no matter now many runs you produce. if you do 700 runs the install cost is still 1000 isk.

i'm aware of the fact it isn't calculating ammo correctly, i'm working on a solution for this.

Azia

Azia Burgi
Caldari
Science and Trade Institute
Posted - 2007.09.15 22:22:00 - [30]
 

I have implemented the fix so it calculates ammo production correctly and included base wastage on the lookup table.

Please keep your comments coming!

Azia


Pages: [1] 2 3

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