Production Calculator Google Sheet [Vanilla, Krastorio2, Space Exploration - 2024 REVAMP]

[ignatio]

[MAJOR UPDATE 2024] I've made a total revamp of my Google sheets to do production calculations. The features are largely the same as before, but they now support mods with complicated production trees much better, and besides vanilla I've created sheets for Krastorio 2 and Space Exploration (links below).

Features

• Shows requirements for all types of production facilities, from assemblers, chemical plants and drills to labs, belt lanes and inserters.
• You can set productivity modules. Particularly useful to see how much raw material and processing you save by adding some modules in the late stages.
• Speed modules: You'll see how many machines you need if you fill remaining slots (not taken by productivity modules) with speed modules. It shows counts for all module levels at once - no need for repeated calculations.
• Beacons: You can set the beacon influence on the machines for each recipe.
• Technology bonuses: You can specify the levels for any tech that affects machine counts: mining productivity, lab research speed and inserter capacity bonus.
• Optimise machine composition: Specify the number of machines you want to use and the script calculates the cheapest combo of machine types (e.g. assembly machine 1, 2 or 3) and speed modules that gives you the required throughput. (Currently not available for Space Exploration because the number of facility and module combinations is just too great.)
• Inserter throughput takes source and target into account (chest-to-belt, belt-to-chest, and chest-to-chest are all different), belt type and stack size.
• Alternative production methods: When there are several ways to produce a product, e.g. basic/advanced oil processing and coal liquefaction, you can choose the method to use, or even a mix of them.
• Production balancing: There are buttons to balance production, so you can calculate how much heavy and light oil cracking is necessary to get the exact amounts you need of each oil type, or the ratio between uranium processing and kovarex enrichment.
• Production graph: Get a graph showing how to wire things up. Edges show the amount of items between each production step.
• Notes as "tooltips" on each item shows the ingredients and result counts.
• Non-item production goals: Things like research on different levels are included in the item list to easily calculate combined requirements.
• Supports expensive recipes as well as the normal ones. I've provided one spreadsheet for each for vanilla.
• A mod that extracts recipe data from within the game, so you can get a spreadsheet that exactly corresponds to your combination of mods and settings. Instructions in the Docs tab in the sheet.

It's a Google spreadsheet, so no need to download anything. A spreadsheet also makes it flexible - you can add your own notes and highlights, any extra calculations you need, etc. You can even use several spreadsheets at once to calculate different parts of the production line and link them together with IMPORTRANGE formulas.

Links

• Vanilla 1.1, and a variant for expensive recipes
• Krastorio 2
• Space Exploration (big mod with 1500+ recipes, so this sheet is rather beefy)

Quick instructions

1. Make your own copy of the spreadsheet - go to File → Make a copy... This requires a Google account. Please don't request edit access to the master copies - get in touch with me instead if you want to improve them for everyone.
2. Put in the amounts for the things you need in the Want column.
3. If the production is using e.g. various oils you can try the Rebalance button to calculate the exact right ratios between advanced oil processing and cracking steps.
4. Select Data → Filter views... → Active items to narrow it down to the items in use.
5. If you like, press the Production graph button to get a nice graph showing which items need to go where.

For more details check out the Docs tab in the spreadsheet.

Authorisation popups

The spreadsheet doesn't require you to give any permissions for the basic calculations, but if you click any of the blue buttons it'll run scripts which do. The first time Google will ask for authorisation and show some rather scary looking dialogues - you'll have to click Advanced, then Go to Factorio Production Calculator (unsafe), and then Allow. I don't think there's anything I can do to "verify" the app with Google - you've made a copy and as you can see it's your own account that's listed as the "developer" you need to trust. The requested permissions are:

• "View and manage spreadsheets that this application has been installed in": As it says - the scripts can only access the sheet itself, nothing else. They cannot send anything off to me or anyone else.
• "Display and run third-party web content in prompts and sidebars inside Google applications": This is required to show the graph when you click Production graph. It uses a Javascript implementation of Graphviz to create the SVG graph directly in the browser. No requests are sent to 3rd party sites.

Example

The screenshots below show a solution for producing an atomic bomb in one minute, with kovarex enrichment to minimise uranium ore. You can see all raw resource costs, and exactly how many speed modules you need to stick into the centrifuges to make them keep up.

screenshot-fpc1.png (552.18 KiB) Viewed 1545 times

screenshot-fpc2.png (454.99 KiB) Viewed 1545 times

[ignatio]

A couple of updates:

• Now allows you to specify Productivity Modules per item. While they slow down the machines you put them in, they also decrease the amount of ingredients which might save quite a bit of capacity for lower level items.
• Show ingredients as "tooltips" for easy access.

[ignatio]

Not a lot feedback on this, but I have noticed a few visitors on the spreadsheet so I hope it's useful to some.

Anyway, I've extended the spreadsheet with a function to calculate the cheapest combination of factories and speed modules that gives you the production capacity you need:

First, make a calculation as usual. For any item where the sheet has calculated a production amount, you can enter the maximum number of machines you want to produce that item. There's a new column for that with an "Optimise for count" button on top of it. Press that button and you'll get a note for each number you've entered that shows the combination of machines and speed modules.

Here's an example that shows a mix of Assembly Machine 2 and 3 with some speed modules to meet the required 12 factories:



The "speed margin" means that combination gives you 0.45% more capacity than what you actually need.

I've used this to size my rocket part factories and found that it works quite well.

[Helfima]

i have made i MOD for that without cost but u can use with other MOD
https://mods.factorio.com/mods/Helfima/helmod

Rocket number

2016-09-07_035558.png (979.52 KiB) Viewed 47428 times

Production block

2016-09-07_035703.png (1.32 MiB) Viewed 47428 times

[ignatio]

I've got a new sheet for all the goodness in 0.15, and I've also added beacon support. Actually it's two sheets now, since there are "normal" and "expensive" variants of some recipes. Updated links in the original post.

[PetWolverine]

Thanks for this, it's really handy! It's the only calculator I've found that supports modules/beacons and expensive recipes.

I've found a minor bug - Chemical Plants and Oil Refineries have 3 module slots, not 2. This changed in 0.15.0; the wiki is out of date.

[mtcairneyleeming]

I've found this spreadsheet really useful, but I was wondering how to get it to use coal liquefaction, as I'm loath to set up the ~1,000 pumpjacks it suggests without liquefaction

Thanks

[ignatio]

I'm glad you find it useful! My aim is to make it as accurate as possible, so thanks for reporting errors.

Chemical Plants and Oil Refineries have 3 module slots, not 2. This changed in 0.15.0; the wiki is out of date.

Fixed (in the wiki too), thanks.

I've found this spreadsheet really useful, but I was wondering how to get it to use coal liquefaction, as I'm loath to set up the ~1,000 pumpjacks it suggests without liquefaction

I haven't set up balancing for coal liquefaction since it hasn't been clear to me how one wants it to work (haven't found use for it myself on my main map). You can always manually fill in a number on the "Petroleum gas from coal liquefaction" row (or one of the other oil types).

In general, if you get a number in "Resources required" for some oil, you can copy that number into the "Want" cell for the production alternative you want to use. That's essentially what the balancing buttons do. (You can of course also split up the amount between different alternatives.)

Btw, I noticed that pumpjacks also benefit from mining productivity research. That can have a quite big impact in the late game. I've updated both spreadsheets to take this into account.

But back to coal liquefaction balancing, I guess what one would really want is to specify a max number of pumpjacks, so it gives you a solution with basic/advanced oil processing up to that number and then coal liquefaction for the rest. Does that sound useful? I can probably make the balancing more clever with a bit more scripting.

[ignatio]

For now I've added coal liquefaction as just another balancing alternative next to basic and advanced processing. Better than nothing.

[Sactorio]

This is superb! Does everything I could want. Except one thing: Allow up to 12 beacons (handy for assemblers at the end of a long production chain).

[ignatio]

Done. I weeded out some alternatives to keep the selection list from getting too long, and also added alternatives for 16 and 20 beacons (possible around larger buildings like refineries and rocket silos). Anyway, if you'd like a beacon configuration that isn't in the list you can just edit it in the Multipliers sheet.

[DaveMcW]

Offshore pump needs to be updated, it now produces 1200 water per second.

[ignatio]

Thanks for noticing. That's one of the hardcoded numbers I haven't been able to find in the data files. Spreadsheets and lua code updated.

[Bomaz]

This is an awesome sheet.

When I tried it I got some odd results however.

For crafting 100 accumulators it recommends 100/3 assembly 1 machines. If my calculations are correct it should take 2000 machines which is more than the 33 recommended.

An older version that I have been using recommends using 2000 as well so you probably have a bug

[ignatio]

This is an awesome sheet.

Thanks!

For crafting 100 accumulators it recommends 100/3 assembly 1 machines. If my calculations are correct it should take 2000 machines which is more than the 33 recommended.

An older version that I have been using recommends using 2000 as well so you probably have a bug

What manufacturing time do you have configured (cell AD5 or thereabouts)? It's 60 seconds by default which means 33.3 machines. If I set it to 1 sec I get 2000. I think those calculations are correct (10 s manufacturing time per item, and AM1 has a 0.5 base speed factor).

[Bomaz]

What manufacturing time do you have configured (cell AD5 or thereabouts)? It's 60 seconds by default which means 33.3 machines. If I set it to 1 sec I get 2000. I think those calculations are correct (10 s manufacturing time per item, and AM1 has a 0.5 base speed factor).

Ah, my bad you are absolutely right. There is no issue

[Bomaz]

Some questions,

Is there some easy way to use filter to reduce the amount of rows shown in the main view? It doesn't like merge cells so when I try to use it I end up getting the filter on the "accumulator" row,

Is there some way to see in the graph how much of a particular resource flows along a particular edge?

[ignatio]

Is there some easy way to use filter to reduce the amount of rows shown in the main view? It doesn't like merge cells so when I try to use it I end up getting the filter on the "accumulator" row,

The "Active items" filter does that (Data -> Filter views... -> Active items), except it never filters out the first row, which is the Accumulator line. That's as far as I can see how filters in Google Sheets work, so not much to do about it.

Is there some way to see in the graph how much of a particular resource flows along a particular edge?

Not currently, but I agree it'd be useful. It wouldn't be hard to add to the graph, but the problem is that whole graph is encoded in the URL and is limited to 2000 characters, so it runs out of space even for moderately small graphs (you might have noticed edge thicknesses disappear when graphs get a bit larger - that's also due to the space constraint).

I usually work around it by abundant use of the "Do not manufacture" option to disable other paths in the graph, but that is admittedly clunky. I think it has to be visible in the sheet itself, but I haven't figured out a way to expose it without things getting terribly complicated.

[ignatio]

Fixed a bug when using "Optimise for max count" with pumpjacks - it didn't take the yield into account so calculated too high counts. The values given in the pumpjack columns were correct though.

[irbork]

When I calculate Advanced Circuit, 40/s, Assemblers 3 with PM3, Beaconed x4x2xSM3,
the calculated amount of assemblers required for copper cable is about 2x larger then it is in game tested built.

EDIT
New copy of calculator.
Want - 40 - Advanced circuits.
Items to manufacture - 400 - Copper cable

Correct items to manufacture 160 copper cable.