Manufacturing Recipe Spreadsheets

eel

Because it was pretty hard for me to parse what raw materials make what, and ultimately how much of any given material is needed in what volumes, I gathered that info from WHATMAKES and put it into a table.

And then because I hate myself was bored, I did a bunch of other stuff.

https://docs.google.com/spreadsheets/d/10YpNAP7YLlGRVr5yItpcAZRtvrv0PjvUxGeUjHdE05M/copy?usp=sharing

Probably won't stay current for long, but I think it's correct right now? And if the formulas are all correct, it should be easy to update with any changes to manufacturing recipes. Could use another set of eyes to look over it all.

Obviously, the actual refinery and factory prices currently listed aren't correct. I didn't look too deeply at prices in general. That's something you should fill in yourself. I don't really know how costing works with specializations.

Mostly I'm using it as a guideline for figuring out if X commodity is useful in large quantities, and what I can make with the stuff I do have. Let me know if you find anything that needs correcting in the recipe formulas.

RocketCat

Fine work! Thanks for sharing!

RocketCat

Thanks again for your work on these spreadsheets. I've used them as a basis for some stuff IC.

That said, a word of warning: The 'Materials (do not edit)' tab has some slight accounting errors. Namely with respect to finished goods which include intermediate commodities. The numbers currently assume that the batch size of each intermediate commodity is 1, when in fact they vary.

eel

@RocketCat

Thanks for catching that! I believe I have now updated the spreadsheets to account for batching. It has, as expected, generally lowered the number of materials and final price per 1 amount for certain items which used arrays, sensors, qpcs, and umms.

But please do check my numbers.

Matlkael

Ahh, I did notice some discrepancies but I couldn't identify it myself. Good job both of you! 

RocketCat

Nice update!

I see one final issue, and it's a fun one: BY-PRODUCTS.

Certain commodities will always give you by-products if you're always just starting from raw resources. I dare you to manufacture a batch of probes with only raw resources and end up with only probes. Seems my little puzzle was actually an error in logic. But the notion of by-products still stands: at certain batch quantities you will end up with by-products.

--backs away slowly--

eel

Hmmmm.....I do see what you mean.

Probes in particular are a nice example because of qpcs being used both in the main recipe and in the recipe for sensors.

I'll have to think about this. I foresee the end point here being a master table that will tell you, given the desired batch number, all the materials you need to collect, how many of what you then need to make, and finally if you'll have any leftovers.

Thanks for the input!

RocketCat

Soo this has inspired me to fiddle with the problem in JavaScript.

Here we can see the by-products if we're starting with just raw resources.

1 batch of astromech:

{"in":{"marks":5046,"vandium":16,"ultarine":12,"tritium":18,"diamene":4,"astrium":2,"iriil":10,"titanium":8,"duramine":4,"helium11":18},"out":{"sensors":1,"umm":1,"astromech":5},"batchCount":12,"resourceCost":1846,"totalCost":6892}

2 batches of astromech:

{"in":{"marks":7773,"vandium":21,"ultarine":14,"tritium":27,"diamene":8,"astrium":4,"iriil":20,"titanium":8,"duramine":4,"helium11":18},"out":{"array":1,"qpc":1,"astromech":10},"batchCount":17,"resourceCost":2512,"totalCost":10285}

3 batches of astromech:

{"in":{"marks":12819,"vandium":37,"ultarine":26,"tritium":45,"diamene":12,"astrium":6,"iriil":30,"titanium":16,"duramine":8,"helium11":36},"out":{"array":1,"qpc":1,"sensors":1,"umm":1,"astromech":15},"batchCount":29,"resourceCost":4358,"totalCost":17177}

4 batches of astromech:

{"in":{"marks":14713,"vandium":37,"ultarine":26,"tritium":45,"diamene":12,"astrium":6,"iriil":30,"titanium":16,"duramine":8,"helium11":36},"out":{"astromech":20},"batchCount":30,"resourceCost":4358,"totalCost":19071}

As you can see, autofactory costs and resource amounts aren't a simple 1:1 relationship!

And the next layer of the puzzle: production bonuses...

eel

Oof. Okay, I THINK I have it. At the very least, using astromech batching as a the yardstick, it checks out.

Please check my numbers, I went through excel hell fiddling and tuning everything, and I'm sure there's an error somewhere.

...I'm going to take a break before tackling production bonuses, though it shouldn't be too tough since it's just % stuff. I haven't been around in game too much recently, due to playing Fire Emblem: Three Houses, another game that is only made better with spreadsheets.

eel

@RocketCat (since you've been very helpful so far!) but also to anyone else paying attention...

I've updated the spreadsheet that the original post links to, so a new copy will need to be made.

Since my last post, I fixed an error with double-counting batch amounts, which made costs very off, so things should look better now.

I'm going to need some help (or time poking around in game) understanding how production bonuses work, because I only own a couple factories in centralized locations, meaning they are not there for bonus purposes. Also I don't really make a lot of stuff in general.

RocketCat

Nice work on this latest one, once again. I went through and plugged in my prices for resources/production to the spreadsheet and compared this with what I wrote in JS. Our numbers match.

I love that there's a sheet called 'SensorBS'

Poet

OMG thank you for this. Trying to figure guard costs was draining my sanity.

eel

Including anyone who has commented here and/or asked about it.
@RocketCat, @Poet, @Matlkael, @Rylek

I've made some changes to the link in the original post, so go ahead and make more copies.

• Found an error where I had batch amounts swapped for probes and scoops or something dumb like that. Fixed.
• In the 'Prices (Edit this)' tab, added a reference table to the right for general refining prices, and switched the table to the left to be buy prices (pay your miners good money!).
• Added tab 'Economies' which is just a reference that I constantly need to use. Stuff that's in stronger highlights (not the lightest yellow, which is banding for readability) calls out the highest bonus for that comm.
• Added tab 'Navigation' because I'm constantly forgetting where everything is.

Changes/Notes specifically for 'MatsNeeded' tab

• Column added to select factory location and calculate bonuses accordingly - the gray cells autopopulate to the location selected in the uncolored cell, but can also be manually changed if needed.
• If your autofactory is located in a factional or non-specializing area, pick NONE, and you will get no bonus.
• % bonus cell will change color to light blue if you select the highest yield subsec for production of that type.
  
• Added tables to the right of the Leftovers columns showing actuals needed to load into the stations/planets to make the stuff. Obviously, hide or show as you prefer.
• Also added colors back because colors are cool.

Pretty much the only stuff you should be changing in the 'MatsNeeded' tab is the # of batches you want to make of a thing, and the location of your autofactory.

So yeah, take a look, let me know if you see any errors.

RocketCat

Nice work, for sure! This iteration probably gets everything one would practically use.

Just one point of order:

- Production bonuses are a little deceiving. A 33% production bonus ACTUALLY means a 1/3 (i.e. 33.33 repeating) production bonus. So running 3 batches of kinetic_battery will net you 40 kinetic_battery, not 39. I haven't tested but I assume 14% production bonus is actually 1/7 and 12% is actually 1/8.

And another thing, just to keep you awake at night (this is where the problem gets real complicated): What happens to the 'Total Raw Materials' column if you're using production bonuses for the intermediate commodities?

eel

RocketCat said:

Just one point of order:

- Production bonuses are a little deceiving. A 33% production bonus ACTUALLY means a 1/3 (i.e. 33.33 repeating) production bonus. So running 3 batches of kinetic_battery will net you 40 kinetic_battery, not 39. I haven't tested but I assume 14% production bonus is actually 1/7 and 12% is actually 1/8.

Oh. They lied to us!   But okay, that makes more sense actually, since the 14% bonus on repairkits didn't seem like it would ever hit a sweet spot, but a 1/7 bonus might (idk math). Hm. Perhaps I'll chart it out.

Anyhow, thanks for pointing that out, I'll poke around and make the changes at some point.

RocketCat said:

And another thing, just to keep you awake at night (this is where the problem gets real complicated): What happens to the 'Total Raw Materials' column if you're using production bonuses for the intermediate commodities?

Oooh, you mean like, if you select a bonus for the intermediates involved in making your final product, that the sheet should take that into account IF it happens to affect the number of raw materials needed? Hrm. That's really interesting. Perhaps if I get bored or more organized in my production methods I'll tackle it.

RocketCat

eel said:

Oooh, you mean like, if you select a bonus for the intermediates involved in making your final product, that the sheet should take that into account IF it happens to affect the number of raw materials needed? Hrm. That's really interesting. Perhaps if I get bored or more organized in my production methods I'll tackle it.

I mean, it could... I don't think it necessarily should (unless you're really, really intent on mapping all the deepest corners of simulating this). The sheets as they are are immensely useful, just knowing ahead of time what raw materials you need to make x batches.

But yeah, it actually becomes a fairly bewildering maximization problem to ask: Using maximum production bonuses for all intermediate comms and ending up with no by-products, what is the lowest possible price per unit for a given commodity?

eel

Hello friends. I return with an update to the spreadsheet linked in the original post.

• It includes all the new ammo recipes (and harvestdrones, figured I might as well).

• The Prices tab got a big overhaul and is now a lot more involved. You can do things like specify where you're refining that raw iriil, and it calculates the cost based on the production bonus of that location! Same deal for intermediates.
  You'll notice it reduces the cost based on the location bonus, so it's an average cost per 1 of the item being made. This should work fine for raw materials, but for intermediates it likely isn't perfect for the ones that get made in batches of 2. But I'm calling it close enough.
  (And it doesn't affect anything in the Total Raw Materials. From a practical perspective, I'm more likely to just make a large pile of intermediates without any specific goal for them, so this fits best with my personal use case. Especially since we now have a lot more mats coming in! )

So please take it for a test run and let me know if you see any issues!

eel

Updated with the new electronic warfare goodies!