Friday, January 20, 2017

Spreadsheet Magic

One of the benefits of moving to manufacturing in an Engineering Complex (aka EngPlex or EC) is that there is a base decrease in material cost across all three sized structures: 1% reduction in manufacturing job required materials. For a few small items like modules or ships, that may see insignificant, but over the long run or for larger items the savings can be considerable.

When I move my structure manufacturing into a Raitaru I didn't bother updating the spreadsheets. I knew there was savings, but I let the extra materials accumulate and get used in the next build so I had to buy less materials every time. Transporting 1% extra materials even for building an Azbel is not a big deal in high sec. 

But in low sec, moving 1% worth of minerals for a capital ship could be the difference between an extra jump freighter jump or not. So I decided to fix my spreadsheets so I could calculate accurately the minerals required producing in the Azbel as opposed to an NPC station.

Note: No, I did not ever produce in a POS with the Thukker component array, I've used POS in the past and I didn't want that hassle by myself.

My spreadsheet started with three pages: one with the blueprint information about the required materials and amounts, one with the prices imported from Fuzzworks api, and one that looked up the information from the other two to give me the exact total minerals required across all the components per capitals and a price estimate. This tells me how much tritanium et al to buy and an idea for how low I can go to sell the capital and still make a profit.

I added a field where I could choose a multiplier, 1 for NPC station, and 0.99 for EC manufacturing, and then multiplied each of the resulting lookup fields by it to get the actual amount. Above you see the start of the Moros section and minerals required for an NPC station, and after choosing 0.99 you see this:

Not huge savings but it adds up.

However, when comparing my results with Fuzzwork's Blueprint Calculator I noticed that not only did the amount of minerals go down when using an EC, the number of some components also went down, specifically on the Moros the Capital Turret Hardpoint and Capital Siege Array requirements dropped from 34 to 33 and 26 to 25 respectively, but the Capital Armour Plates required stayed at 22.

That didn't make sense to me. One percent off of 34 is 33.66 and all rounding is up so it should still be 34! I asked on twitter to a dev who helped develop ECs:
@CCP_Fozzie I don't understand how an Azbel 1% material reduction takes a Moros build of 34 Cap Turret Hardpoints to 33. Special rounding?
Fortunately, an answer came quick from the creator of Fuzzworks, CSM member, and all around great guy:
Fuzzysteve @Fuzzysteve
@kirithkodachi @CCP_Fozzie ME 10? requires 33.30, which is taken up to 34. EC drops it to 32.97, or 33. Not rounded. Ceiling-ed
You see, there is some magic I forgot to take into account that involves the Material Efficiency of the Moros blueprint.

An unresearched Moros blueprint says that 37 Capital Turret Hardpoints are required, but each level of material efficiency research done on it reduces that amount by 1% rounded up. So my blueprint at ME 10 says 34 but the actual calculation is 37 * 0.9 = 33.3 rounded up to 34.

That's all I need for producing in a NPC station but the 1% bonus for an EC does not apply to the 34 amount, its applied to the hidden actual 33.3 amount, which brings it to 32.967 which rounds up to 33.

So I understood how it worked but was faced with the issue that my spreadsheet blueprint database didn't capture that interim number calculation so I couldn't apply the 1% properly to capital ship components requirements. Faced with inaccurate numbers that I couldn't abide, I spent half an hour last night updating my spreadsheet with the base ME 0 requirements for capital ships, and then applied the ME level to calculate the true values for the blueprints that my main sheet could lookup and apply the 1% discount on it as needed.

Fortunately I only needed that extra level of calculation for the capital ships. The mineral requirements of the components are accurate enough to not have any hidden rounding involved and the Astrahus, Raitaru, and Azbel blueprints have requirements too low to be affected. Well, the Azbel can benefit if you use a rigged EC with ME 10 Azbel blueprint... which might take you a while to research:

So, to sum up: I've fixed my spreadsheet to allow me to reflect the material requirements of building in an Engineering Complex or NPC station. This is absolutely the most EVE post I've ever made.

No comments:

Post a Comment