Late Game Rocket Planning Spreadsheet

[ZorangeZorange]

Hi friends! I'm new here (to the forum) so I hope I'm putting this in an appropriate place, but I've been having fun making a spreadsheet for late game planning and wanted to share.

This started because I couldn't find a Factorio calculator that would include modules, which obviously have a giant impact on number of assembly machines, energy use etc. Instead of winging my first megabase I wanted to be able to play around and see what combination of modules on which parts of the production chain I wanted to go with. And here we are! I tried to make it easy to use and understand, but the game is pretty complex and the math behind the modules is a little unfortunate, so ask for clarification on anything and I'll do my best to explain or fix whatever it is.

If anyone is trying the 1 rocket per minute challenge and wants to test this out for me I'd love to know if it actually works

Please please let me know any mistakes you find, math, excel, or otherwise! Also anything you think might be a good addition!

EDIT: Added screenshots

http://i.imgur.com/DZjL9fH.jpg

http://i.imgur.com/cGMKgDe.jpg

EDIT: Fixed module cost to include the ones used in the Rocket Silo, added coal to the Raw Resources per second needed to maintain, added resource cost per rocket (all on Dashboard)

[ssilk]

Really nice work. You should add a screenshot.

Moved from General to Cheatsheets.

[ZorangeZorange]

Great idea! I didn't even see the Cheatsheet section, guess I have to explore a little bit more.

[Tnarg]

Good work, looks like a handy tool.

Few questions:

Would it be possible to show how much oil/copper/iron and coal I would need per second to run and how much per rocket.
Also I not sure it correctly works out the Module build cost for the rocket silo.

[Hannu]

I have 2 silos with 4 level 3 productivity modules. The silo sheet works as I expect but if I increase number of the productivity modules in the oil or circuits sheet inputs needed per second increases instead of decreasing. Is it a bug in the spreadsheet, my error or incompatibility issue with OpenOffice (3.4.1)?

[ZorangeZorange]

@Tnarg Thanks for catching that module cost, should be fixed. The oil/copper/iron/coal needed per second is in the bottom left of the dashboard, I titled it "Raw Resources to Maintain (s)" but that's basically what it is. Forgot to put the coal there, so added that in! Also right under that I put resources per rocket. Not sure if (resources needed per second)x(time per rocket launch) is 100% accurate, but it will give you a reasonable estimate I hope.

@Hannu I noticed that too when I was making this, I managed to justify it to myself with the following but I might be totally wrong about it. So from what I can tell if you add productivity modules without adding speed modules you actually do need more resources per second. Productivity modules decrease speed more than they add productivity, so you'll notice that the number of assembly machines needed jumps up. The increase in resources per second is because you have to feed a lot more assembly machines, but you are still saving resources.

So with your rocket set-up i just went to circuits, for green circuits with no modules there you need 41.92 inputs/s for 21 assembly machines, or 1.996 input per machine per second. If I throw in 4 productivity modules it says that now I need 74.85 inputs/s for 38 assembly machines, which is 1.969 inputs per machine per second (if I did the math right). So you do need more per second, but each assembly machine is using less.

[Hannu]

Productivity modules always decrease amount of raw materials needed to produce given amount of products. I take an example of green circuit production with level 3 assembler. If I have no modules, it produces 2.5 circuits per second and uses 3 copper wires and 1 iron per produced circuit. So I need 7.5 wires and 2 iron plates per second.

If I put one level 3 productivity module in the assembler, its speed decreases to 0.85 * 1.25 = 1.0625. Production cycle takes now 0.47 s, and it need same inputs, 3 wires and 1 iron per cycle. Now production bonus gives us 10 % extra (one free circuit after every tents production cycle). We get 1.1 / 0.47 s = 2.34 circuits/s and we need 3 / 0.47 = 6.4 wires and 1 / 0.47 = 2.1 irons per second. Production rate decreases but total amount per used raw materials increase by factor 1.1.

If we have 4 productivity modules, production cycle takes 0.5 s /(1.25 * 0.4) = 1 s and raw materials are needed 3 wires and 1 iron per second. Now we get 40 % free extra circuits (one after 2.5 production cycles) and production rate is 1.4 circuits per second. Again we get 40 % more per used material but production rate is low and we need much more assemblers to keep given production rate and energy consumption and pollution are insane.

Practically it is easy to decrease iron and copper consumption to about 30 k per rocket with productivity modules because bonuses of the subsequent assemblers in the production chain is multiplied by each other. Even larger savings are possible if much productivity modules are used in miners and electric furnaces. It is not best strategy in most gamestyles, because modules are expensive, but it is economic if you want to play hundreds of hours and send hundreds of rockets.

[Xe138]

@Hannu I noticed that too when I was making this, I managed to justify it to myself with the following but I might be totally wrong about it. So from what I can tell if you add productivity modules without adding speed modules you actually do need more resources per second. Productivity modules decrease speed more than they add productivity, so you'll notice that the number of assembly machines needed jumps up. The increase in resources per second is because you have to feed a lot more assembly machines, but you are still saving resources.

It looks like the productivity module behavior has been fixed, but I'm still noticing a problem with the speed module behavior. Adding speed modules to the assembly machines is causing the required resources to decrease along with the required number of assembly machines. I believe adding speed modules should have no impact on the input or output resources required, but should only affect the number of assembly machines needed to meet the throughput requirement.

I've adjusted the calculator to, hopefully, fix the issue. I removed the speed effect from the "Input Need /s" columns and added it to the "Assembly Machines" column. This seems to have corrected the problem, but I'd appreciate somebody else verifying the math.

[ccik]

This is brilliant and exactly what i was looking for. Thank you.

However, just one question. What does output need/s actually mean. How many items per second i will produce with the suggested setup?

I the current setup, the spreadsheet tells me i need 2426 Iron Ore per second to maintain.. This figure is also listed under input needed for Iron Mining. However, the number for output/s mining is 485, which is exactly what Input need for the furnaces is. This all seems a little confusing. Should the total need to maintain not be qual to the output of the mining drill? Or is there something i'm not getting?

[SkullTitsGaming]

This is absolutely lovely, but for some reason (see: i have issues with wanting to do things no computer could likely handle) it has problems about, say, 100 silos. While i'm sure i'm wayyyy overestimating my UPS/FPS, i'd love to see, just for fun's sake, what i would need in order to do quicker launches- say, 5 per second or something ridiculous. IS there a way to fix the DIV/0 errors?

[SkullTitsGaming]

Deciding it would be of more help to decipher where the issue was myself, rather than laying it upon someone else's mercy to do so, i discovered that most of the errors were coming from the math being done for solar panels and accumulators- the equations taking "(number of items needed for sattelite) / ((rockets/second) -3) results in negative numbers come division, meaning a need for negative assembly machines, costing negative amounts, etc.

Could someone explain the subtraction of three in this equation? Doing so would help me to find a way to fix this spreadsheet so it works up to large, supercomputer-like factories.