Production Calculator Google Sheet [0.16]

Calculate optimal ratios for feeding recipes, search through the research-tree, specialized tools to view game-information.

Production Calculator Google Sheet [0.16]

Postby ignatio » Wed Jun 29, 2016 5:09 am

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).

Sheets: normal cost recipes, expensive recipes

Quick instructions:

  1. Make your own copy of the spreadsheet (File -> Make a copy...).
  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, but it only requests access to things in the same spreadsheet.)
  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 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).

Image

Image
Last edited by ignatio on Tue May 01, 2018 10:09 pm, edited 7 times in total.
ignatio
Inserter
Inserter
 
Posts: 43
Joined: Mon Jun 27, 2016 3:59 pm

Re: Production Calculator Google Sheet [0.13]

Postby ignatio » Wed Jul 06, 2016 12:52 am

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
Inserter
Inserter
 
Posts: 43
Joined: Mon Jun 27, 2016 3:59 pm

Re: Production Calculator Google Sheet [0.13 & 0.14]

Postby ignatio » Wed Sep 07, 2016 1:29 am

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:

Image

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.
ignatio
Inserter
Inserter
 
Posts: 43
Joined: Mon Jun 27, 2016 3:59 pm

Re: Production Calculator Google Sheet [0.13 & 0.14]

Postby Helfima » Wed Sep 07, 2016 1:51 am

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
2016-09-07_035558.png (979.52 KiB) Viewed 9810 times

Production block
2016-09-07_035703.png
2016-09-07_035703.png (1.32 MiB) Viewed 9810 times
Helfima
Fast Inserter
Fast Inserter
 
Posts: 134
Joined: Tue Jun 28, 2016 11:40 am

Re: Production Calculator Google Sheet [0.15]

Postby ignatio » Sun Apr 30, 2017 5:08 am

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.
ignatio
Inserter
Inserter
 
Posts: 43
Joined: Mon Jun 27, 2016 3:59 pm

Re: Production Calculator Google Sheet [0.15]

Postby PetWolverine » Fri Jun 02, 2017 2:13 pm

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.
PetWolverine
Burner Inserter
Burner Inserter
 
Posts: 18
Joined: Wed May 10, 2017 11:20 pm

Re: Production Calculator Google Sheet [0.15]

Postby mtcairneyleeming » Fri Jun 02, 2017 7:02 pm

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
mtcairneyleeming
Manual Inserter
Manual Inserter
 
Posts: 1
Joined: Mon May 22, 2017 6:25 pm

Re: Production Calculator Google Sheet [0.15]

Postby ignatio » Sat Jun 03, 2017 2:07 am

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
Inserter
Inserter
 
Posts: 43
Joined: Mon Jun 27, 2016 3:59 pm

Re: Production Calculator Google Sheet [0.15]

Postby ignatio » Sun Jun 04, 2017 2:58 pm

For now I've added coal liquefaction as just another balancing alternative next to basic and advanced processing. Better than nothing.
ignatio
Inserter
Inserter
 
Posts: 43
Joined: Mon Jun 27, 2016 3:59 pm

Re: Production Calculator Google Sheet [0.15]

Postby Sactorio » Sun Jul 02, 2017 10:05 am

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).
Sactorio
Burner Inserter
Burner Inserter
 
Posts: 8
Joined: Tue Jun 13, 2017 6:42 am

Re: Production Calculator Google Sheet [0.15]

Postby ignatio » Sun Jul 02, 2017 1:47 pm

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.
ignatio
Inserter
Inserter
 
Posts: 43
Joined: Mon Jun 27, 2016 3:59 pm

Re: Production Calculator Google Sheet [0.15]

Postby DaveMcW » Tue Jul 18, 2017 9:26 am

Offshore pump needs to be updated, it now produces 1200 water per second.
User avatar
DaveMcW
Smart Inserter
Smart Inserter
 
Posts: 2388
Joined: Tue May 13, 2014 11:06 am

Re: Production Calculator Google Sheet [0.15]

Postby ignatio » Tue Jul 18, 2017 11:10 am

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.
ignatio
Inserter
Inserter
 
Posts: 43
Joined: Mon Jun 27, 2016 3:59 pm

Re: Production Calculator Google Sheet [0.15 & 0.16]

Postby Bomaz » Mon Dec 25, 2017 2:55 pm

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
Bomaz
Burner Inserter
Burner Inserter
 
Posts: 13
Joined: Sun Jul 24, 2016 10:02 am

Re: Production Calculator Google Sheet [0.15 & 0.16]

Postby ignatio » Mon Dec 25, 2017 5:37 pm

Bomaz wrote:This is an awesome sheet.


Thanks! :)

Bomaz wrote: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).
ignatio
Inserter
Inserter
 
Posts: 43
Joined: Mon Jun 27, 2016 3:59 pm

Re: Production Calculator Google Sheet [0.15 & 0.16]

Postby Bomaz » Mon Dec 25, 2017 5:55 pm

ignatio wrote: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
Burner Inserter
Burner Inserter
 
Posts: 13
Joined: Sun Jul 24, 2016 10:02 am

Re: Production Calculator Google Sheet [0.15 & 0.16]

Postby Bomaz » Tue Dec 26, 2017 3:50 pm

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?
Bomaz
Burner Inserter
Burner Inserter
 
Posts: 13
Joined: Sun Jul 24, 2016 10:02 am

Re: Production Calculator Google Sheet [0.15 & 0.16]

Postby ignatio » Tue Dec 26, 2017 5:34 pm

Bomaz wrote: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.

Bomaz wrote: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
Inserter
Inserter
 
Posts: 43
Joined: Mon Jun 27, 2016 3:59 pm

Re: Production Calculator Google Sheet [0.15 & 0.16]

Postby ignatio » Sun Feb 04, 2018 5:45 pm

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.
ignatio
Inserter
Inserter
 
Posts: 43
Joined: Mon Jun 27, 2016 3:59 pm

Re: Production Calculator Google Sheet [0.15 & 0.16]

Postby irbork » Wed Apr 18, 2018 10:25 am

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.
User avatar
irbork
Long Handed Inserter
Long Handed Inserter
 
Posts: 83
Joined: Fri Jul 04, 2014 1:17 pm

Next

Return to Cheatsheets / Calculators / Viewers

Who is online

Users browsing this forum: No registered users and 1 guest