Cargo Fleet Optimization Spreadsheet

[bean (OP)]

I've recently developed a spreadsheet for finding the most efficient freighter designs.  You input the range of a typical mission leg, and the spaceport facilities on each side, and then the specifics of your engine design(s) on a separate sheet.  Then, you can add components, and the sheet calculates expected crew quarters and armor size.  It ends by telling you the cargo tons/BP/hr and colonists/BP/hr on the route in question.  You can then try multiple designs, and pick the one that gives you the highest values.  It also tells you things like range which may be important for design work.
I think I've gotten all of the bugs worked out, and it shouldn't do silly things like give negative crew quarters.  Also, the 'load both ways' value needs some explaining.  If set to 1, it applies load times twice, which slightly changes the optimum.  Don't put more than one engine type on a design, as it breaks several bits of math. 
I built this after one of the players in my multiplayer game designed a freighter (nuclear pulse engines) that did 183 km/s.  I decided to see how badly he'd messed up, and ended up finding out that his cargo/BP/hr was about one-fourth of the optimum.  I also ended up with a spreadsheet that seems very, very useful, and didn't see that anyone had posted anything similar.
Enjoy.

[db48x]

Neat. I do see one bug though; it doesn't check that the ship range is adequate for the trip, so removing tanks always increases the efficiency. This makes it want to go to a negative infinite number of tanks when you use the solver.

Also, you should give your fields names rather than always referring to them by row/column addresses; it makes the formulas easier to read.

[bean (OP)]

Neat. I do see one bug though; it doesn't check that the ship range is adequate for the trip, so removing tanks always increases the efficiency. This makes it want to go to a negative infinite number of tanks when you use the solver.

That's not a bug, so much as it is that I didn't design it for use with the solver.  I probably should have, but I wasn't thinking about that.  Yes, I know that the solver is somewhat faster, but besides a few layout issues I haven't quite solved, it goes pretty quickly anyway.  Also, I tend to fuel for several times the typical trip, so I don't have to mess with logistics if I need to run longer legs.

Also, you should give your fields names rather than always referring to them by row/column addresses; it makes the formulas easier to read.

That was me being forgetful.  Well, some of it.  I'm not sure exactly how that interacts with doing multiple columns. 

[db48x]

That was me being forgetful.  Well, some of it.  I'm not sure exactly how that interacts with doing multiple columns. 

In Excel you can select a range and make it a Table. Tables have a name much like a range would, but the columns within the table also have names (taken from the column headers if there are any), and you can reference things inside the table using those names. I think you'll want to transpose the ship designs so that each design is a row; that'll work better once it's a table. Probably not as easy to read though.

Unfortunately this is one area where LibreOffice falls down a bit; you can auto-format a table, or filter it, but it doesn't give you the super-handy formula references.

[bean (OP)]

In Excel you can select a range and make it a Table. Tables have a name much like a range would, but the columns within the table also have names (taken from the column headers if there are any), and you can reference things inside the table using those names. I think you'll want to transpose the ship designs so that each design is a row; that'll work better once it's a table. Probably not as easy to read though.

Unfortunately this is one area where LibreOffice falls down a bit; you can auto-format a table, or filter it, but it doesn't give you the super-handy formula references.

I'm not planning to revise the sheet at the moment (it's something I built for my own use, then decided to share), although I might get bored and play around with tables.
I did initially try to put the ships as a line each, but the formatting was horrible because of how wide it was.

[db48x]

I'm not planning to revise the sheet at the moment (it's something I built for my own use, then decided to share), although I might get bored and play around with tables.

Indeed. In fact, I've been optimizing my designs; thanks!

I did initially try to put the ships as a line each, but the formatting was horrible because of how wide it was.

Yea, I tried it and it just doesn't work.

[bean (OP)]

Indeed. In fact, I've been optimizing my designs; thanks!

You're very welcome.  I'd be interested to see your modifications, which I assume include letting it design engines. 
I'm not actually sure how much optimization my next set of ships will have.  There are logistical reasons to base colony ships and freighters off the same design, and to keep all of the various sizes at the same speed.  I'm still kicking it around.  The one change that will happen is that some of the cargo handling systems will be reduced in level. 

[db48x]

Yea, I found that it would be beneficial to put more cargo handling systems on my colony ships than my cargo ships, but didn't because it's handy to be able to build both of them in the same yard.

I've pretty much rewritten all of the formulas, but the armor calculation is slightly off; I think I messed it up somehow.

Also, I deleted the crew quarters from the components list as a simplification, but I think that you probably had the right idea there. Having them in the list is redundant because we're already calculating the amount of space they will take up, but it takes away the option to override the automatic calculation and supply extra crew quarters for a better match between colony and cargo ships. If I add them back I'll do a hybrid setup, where we calculate the required HS of crew quarters as my version does, but also lets you add extras.

Also, you can add as many scenarios as you like just by copying the last column on the ship design page and pasting it back in one column over, because all of the formulas use named rows instead of named cells.

Ironically I've yet to use the solver at all; the solver in LibreOffice requires a JRE, and mine is apparently broken or misconfigured.

[bean (OP)]

Yea, I found that it would be beneficial to put more cargo handling systems on my colony ships than my cargo ships, but didn't because it's handy to be able to build both of them in the same yard.

That usually doesn't destroy interoperability.  I admit that I didn't check that, but it's a fairly small cost.  I was primarily referring to engines.

I've pretty much rewritten all of the formulas, but the armor calculation is slightly off; I think I messed it up somehow.

That was the hard one to get right.  I'll take a look.

Also, I deleted the crew quarters from the components list as a simplification, but I think that you probably had the right idea there. Having them in the list is redundant because we're already calculating the amount of space they will take up, but it takes away the option to override the automatic calculation and supply extra crew quarters for a better match between colony and cargo ships. If I add them back I'll do a hybrid setup, where we calculate the required HS of crew quarters as my version does, but also lets you add extras.

I was calculating the required space, I just hid that row.  The stuff down below was referencing that row, and auto-calculating.  It was to make sure that the cost of the crew quarters got into the system.  In some cases that was important.

Also, you can add as many scenarios as you like just by copying the last column on the ship design page and pasting it back in one column over, because all of the formulas use named rows instead of named cells.

You could do that anyway.  The references to fixed cells were using $, which meant they'd remain stable when you copied or dragged them.

Ironically I've yet to use the solver at all; the solver in LibreOffice requires a JRE, and mine is apparently broken or misconfigured.

I just use Excel.
Edit:
Which doesn't seem to like .ods files.  It changed everything to pure numbers, so I can't see much of what you did.  I'll have to try again later.

[db48x]

You could do that anyway.  The references to fixed cells were using $, which meant they'd remain stable when you copied or dragged them.

Granted

With names though the formulas are actually readable.

I just use Excel.
Edit:
Which doesn't seem to like .ods files.  It changed everything to pure numbers, so I can't see much of what you did.  I'll have to try again later.

Try this one, I saved it as an xslx this time.

[bean (OP)]

Try this one, I saved it as an xslx this time.

Couple things.  First, you need an engineering space in a freighter.  Second, my sheet's output doesn't match yours.  I validated mine against some of my existing freighters, but when I tried building one of yours, it didn't line up with what your sheet was telling me.  I'm not sure if this was an excel translation error (I'm still not sure how your selections are working) or if your sheet is broken.
Edit: And now it does, despite my not changing anything on either sheet.  I'm very confused.

[db48x]

you need an engineering space in a freighter.

It says that in the class designer, but leaving it out doesn't have any effect that I've noticed. For the purposes of optimization, then, I started leaving it out.

[bean (OP)]

It says that in the class designer, but leaving it out doesn't have any effect that I've noticed. For the purposes of optimization, then, I started leaving it out.

And you're able to lock the design without one?  That's a bug.

[db48x]

And you're able to lock the design without one?  That's a bug.

Quite possibly!

It was to make sure that the cost of the crew quarters got into the system.  In some cases that was important.

Ah, I'd forgotten about that. Ships with more engines will lose a little bit of the penalty of the extra crew, because the BP will be lower than it should be.

I think the best way to do it would be to have two fields for each size of crew quarters, one which is calculated from the crew requirements and another for extra berths. I just don't like overwriting a formula with a number; it's too easy to forget that you've done so.

[bean (OP)]

Quite possibly!

I'll check this when I get a chance.  If it's true, then I'll file a bug report.

Ah, I'd forgotten about that. Ships with more engines will lose a little bit of the penalty of the extra crew, because the BP will be lower than it should be.

I think the best way to do it would be to have two fields for each size of crew quarters, one which is calculated from the crew requirements and another for extra berths. I just don't like overwriting a formula with a number; it's too easy to forget that you've done so.

Fair enough.  I usually don't go around hanging extra crew quarters on my commercial ships, so that wasn't a major design driver.  This wasn't intended to be a comprehensive system for designing commercial ships, which is why I also left out things like small fuel tanks.