Production Calculator Google Sheet [1.0]

[ignatio]

Hi, I'd like to share a spreadsheet to do advanced and very accurate production calculations:

• 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.
• 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 options 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. Thicker lines means higher throughput.
• Notes as "tooltips" on each item shows the ingredients and result counts.
• Non-item production goals: Things like satellite launch and research on different levels are included in the item list to easily calculate combined requirements.
• Supports expensive recipes as well as the normal ones. There's one spreadsheet for each (links below).

It's a Google spreadsheet, so no need to download anything. A spreadsheet is also flexible - you can add your own notes and highlights, any extra calculations you need, etc. I find it useful to have several spreadsheets at once to calculate different parts of the production line; I e.g. used three to tune my rocket part production unit.

Last but not least there's a lua script to update the recipes directly from the game, so you don't have to wait for me to put out a new version after an update, and you should be able to adapt it to any mods you got installed (haven't tried that though, so the script might need a few tweaks). See the Docs sheet in each spreadsheet for more details and link to the script.

Sheets for 1.0: normal cost recipes, expensive recipes

Older versions (I won't be updating these):
0.16: normal cost recipes, expensive recipes
0.15: normal cost recipes, expensive recipes
0.14: normal cost recipes

Quick instructions:

1. Make your own copy of the spreadsheet - go to File -> Make a copy... (Please don't request edit access - get in touch with me instead if you want to improve it.)
2. Put in the amounts for the things you need in the Want column.
3. Click the Rebalance button to do oil and uranium balancing calculations. (First time you'll need to give the script permission to run. It only requests access to change things in the same spreadsheet, and to display the graph.)
4. Select Data -> Filter views... -> Active items, to narrow it down to the items you need.
5. If you like, press the "Production graph" button to get a nice graph showing which items need to go where.

The screenshot below shows a solution for producing an atom 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 (click for full size).

[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 41895 times

Production block

2016-09-07_035703.png (1.32 MiB) Viewed 41895 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.