Aurora 4x
VB6 Aurora => Bureau of Ship Design => Topic started by: bean on February 14, 2016, 11:23:41 AM
-
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.
-
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.
-
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.
-
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.
-
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.
-
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.
-
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.
-
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.
-
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.
-
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.
-
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.
-
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. :)
-
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.
-
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.
-
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.
-
I'll check this when I get a chance. If it's true, then I'll file a bug report.
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.
But... But... how can you build ships with an exact size of 79.8? You've got to put that small tank in there to get all that free extra range!
-
But... But... how can you build ships with an exact size of 79.8? You've got to put that small tank in there to get all that free extra range!
I don't. Like you, I do fill those spaces with trim fuel. But it didn't seem worth worrying about, because it's a rounding error on the level of design the sheet is intended for.
-
I don't. Like you, I do fill those spaces with trim fuel. But it didn't seem worth worrying about, because it's a rounding error on the level of design the sheet is intended for.
:)
I added support for fuel tankers by calculating the amount of fuel delivered divided by the fuel used. Still need to fix the crew quarters though.
-
I checked. My copy won't let you lock the design without an engineering space on your freighter. It will throw an error in the lower right, instead of revoking commercial status.
-
I checked. My copy won't let you lock the design without an engineering space on your freighter. It will throw an error in the lower right, instead of revoking commercial status.
Ah, I see what you mean; it does indeed disable the lock button when I remove the engineering space. However in practice I always just go straight to the population window to retool to the class. Apparently it doesn't have the same restriction.
-
BTW, I just used an IPython notebook to interactively optimize a missile
-
Ah, I see what you mean; it does indeed disable the lock button when I remove the engineering space. However in practice I always just go straight to the population window to retool to the class. Apparently it doesn't have the same restriction.
That shouldn't be allowed.
BTW, I just used an IPython notebook to interactively optimize a missile
Sounds neat. I'll see what I can make of it. (I was the first to find the engine optimization stuff when 6.0 came out, but I'm an aerospace engineer, not a computer person. I usually use MATLAB.)
-
I was playing around with the solver on this sheet to optimize a design. Optimizing for Colonists/BP/hr/L results in a ship with one cryo module, one engine, and one tiny fuel tank. Optimizing for (Colonists/BP/hr)*(Colonists/L) gets me a more reasonable design with 35 cryo modules, 2 advanced CHS, 23 engines, and one standard fuel tank.
Maybe I'm just too tired at this hour, but I really expected the former to be the right optimization target. As expected, both designs are worse in Colonists/BP/hr than a design optimized just for Colonists/BP/hr, and both are worse on Colonists/L than a design optimized for Colonists/L, but the first design is much worse. Can you think of a reason for this to be a better optimization target?
BTW, the solver in LibreOffice works once you set up the right constraints (setting a constraint so that every variable is >= 0, is an integer, and so that it doesn't put a million tiny fuel tanks, etc) but the UI is annoying. To solve again in a different column you have to edit all of the constraints individually so that they point to the correct cells. I also had to restrict it to solving with only one type of engine, as it could never find its way out of the trap of putting in multiple types (which makes the design invalid). How does Excel compare?
-
I'm not sure that either of those make numerical sense, which is why you're getting weird results. I think the proper way to do this is to establish a figure of merit for the design, some function of Colonist/BP/hr (I'll call that B below) and Colonist/L (L), and then optimize that. This is technically what you did for the second, but you need to be more explicit about the weights you use.
The simplest FOM would be F=X*B+Y*L where X and Y are weighting factors. You can set X to 1, and then Y becomes how many colonists/L you're willing to trade for 1 Colonist/BP/hr. I'd have to play with the exact numbers before I'd want to guess at reasonable weights.
You can of course make more complicated weighting functions, if you want one variable to stay relatively close to a specific value while the other one fluctuates a lot. Say you want to cut L approximately in half because you're low on fuel, but don't want to make that a hard value. So your function might be F=X*B-Y*(L-Lt)^2 where Lt is the target L. (The minus is because you want the value to decrease as you depart from Lt, not increase.)
Excel's solver is pretty similar. My first thought is to use a macro and set all the constraints in there, which makes them easier to edit and much easier to re-use.
Edit:
My second example doesn't make much sense, as there's no reason to want to penalize designs for being more fuel-efficient than the target. I can't think of a single function which does what I want there, which is to give small bonuses for exceeding the fuel economy target, but large penalties for being below it.
-
Can a cargo ship load more than one automated mine?
-
Edit:
My second example doesn't make much sense, as there's no reason to want to penalize designs for being more fuel-efficient than the target. I can't think of a single function which does what I want there, which is to give small bonuses for exceeding the fuel economy target, but large penalties for being below it.
Start from something like (1.1 * ( L - Lt ) - abs( L - Lt ) ), perhaps?
Can a cargo ship load more than one automated mine?
Yes, provided it has the cargo space available.
-
Start from something like (1.1 * ( L - Lt ) - abs( L - Lt ) ), perhaps?
That might work. You'd have to play with the weights, but it does give more or less the desired result.