Author Topic: Multiple Engine Design Spreadsheet - Find the lowest weight necessary  (Read 6740 times)

0 Members and 1 Guest are viewing this topic.

Offline Zenrer (OP)

  • Leading Rate
  • *
  • Z
  • Posts: 11
  • Thanked: 1 times
Being someone who isn't very good at finalizing a ships design very well, I decided to try and find something to make life easier on this forum.     The search inevitably led me to this:

hxxp: aurora2.    pentarch.    org/index.    php/topic,5482.    msg56842.    html#msg56842

However, there was a problem with the spreadsheet that Prince of Space quickly devised; and that was that finding an efficient multiple engine setup wasn't really possible.     So, me having nothing to do, apart from playing Aurora, decided to make a new spreadsheet for multiple engine desig, heavily based off Prince of Space's, and come up with a way that anybody can find the most efficient engine + fuel setup they want in their ship.     The only variables you need to input are Engine Tech Level, Fuel Consumption Tech Level, and the desired ship Tonnage, Speed and Range.     

Hopefully everyone can make good use of this spreadsheet.   

Link: https://www. dropbox. com/s/hf5dabi1mkwllko/AuroraEnginePlanner. xlsm?dl=0
« Last Edit: January 06, 2015, 08:20:03 AM by Zenrer »
 

Offline 83athom

  • Big Ship Commander
  • Vice Admiral
  • **********
  • Posts: 1261
  • Thanked: 86 times
But efficiency isn't always a concern. Also, this isn't about finding the most efficient design, because the most efficient design is always with max size (50 HS) with the very minimum EP (x0.1 power) (this topic was about finding the most efficient engine design after all, word it better next time). This spreadsheet is about finding a desired amount of engines and fuel for what you want (size/speed/range). But that said, well done.
Give a man a fire and he's warm for a day, but set fire to him and he's warm for the rest of his life.
 

Offline Rich.h

  • Captain
  • **********
  • R
  • Posts: 555
  • Thanked: 55 times
I might be missing the idea on how these are used but both this and thr original sheet suffer from the same problem. The three sections in this sheet ask for desired speed, range, and total ship size in tons. However as you will see from the results this total ship size is wrong. What you are doing is designing a ship that is nothing but engines and fuel for this size.

A quick example, I have ion tech, 0.5 fuel efficiency to start. I say I want a 130kton ship, with a speed of 3000km/s and a range of 80bkm. The sheet then tells me the following

Quote
29x 50-HS engines with Power Modifier x0.45, plus 57121196L of fuel, totaling 129622 tons of engine and fuel space.

As you can see the engines and fuel alone now total up to nearly 130ktons. This now leaves no room for anything else, once you start adding other things in the design efficiency created becomes lost. Or have I just completely missed the point on how these sheets are meant to be used?
 

Offline Zenrer (OP)

  • Leading Rate
  • *
  • Z
  • Posts: 11
  • Thanked: 1 times
Quote from: 83athom link=topic=7647.  msg77575#msg77575 date=1420515020
But efficiency isn't always a concern.   Also, this isn't about finding the most efficient design, because the most efficient design is always with max size (50 HS) with the very minimum EP (x0.  1 power) (this topic was about finding the most efficient engine design after all, word it better next time).   This spreadsheet is about finding a desired amount of engines and fuel for what you want (size/speed/range).   But that said, well done. 

I see what you're saying.   Would probably be better to describe it as 'Find the lowest combined weight of fuel + engines for a given ship size, speed and range'. 

Quote from: Rich.  h link=topic=7647.  msg77576#msg77576 date=1420547065
I might be missing the idea on how these are used but both this and thr original sheet suffer from the same problem.   The three sections in this sheet ask for desired speed, range, and total ship size in tons.   However as you will see from the results this total ship size is wrong.   What you are doing is designing a ship that is nothing but engines and fuel for this size. 

A quick example, I have ion tech, 0.  5 fuel efficiency to start.   I say I want a 130kton ship, with a speed of 3000km/s and a range of 80bkm.   The sheet then tells me the following

As you can see the engines and fuel alone now total up to nearly 130ktons.   This now leaves no room for anything else, once you start adding other things in the design efficiency created becomes lost.   Or have I just completely missed the point on how these sheets are meant to be used?

The result you have is actually wrong.   Realised about an hour after I uploaded I the file that I was calculating the fuel use per hour by doing Fuel EPH * Total Power * Engines instead of Fuel EPH * Total Power.   In this case, because you need 29 engines per the sheet, the resultant fuel value was 29 times greater than the actual necessary value, which is why the combined weight is so high.   I've just reuploaded the sheet, and it is now fixed. 

Another fix I've also implemented was a problem that if your minimum engines was greater than a specific HS needed for a specific power modifier, the sheet threw up errors everywhere, which meant the result was always #VALUE.   I've fixed this by forcing the amount of engines to 1,000,000 in these cases, basically ruling out that specific power modifier ever being the final result, because it's not possible.   
« Last Edit: January 06, 2015, 07:34:08 AM by Zenrer »
 

Offline Rich.h

  • Captain
  • **********
  • R
  • Posts: 555
  • Thanked: 55 times
Re: Multiple Engine Design Spreadsheet - Find the lowest weight necessary
« Reply #4 on: January 06, 2015, 07:42:42 AM »
Works much better now thank you.
 

Offline Zenrer (OP)

  • Leading Rate
  • *
  • Z
  • Posts: 11
  • Thanked: 1 times
Re: Multiple Engine Design Spreadsheet - Find the lowest weight necessary
« Reply #5 on: January 06, 2015, 08:19:02 AM »
Quote from: Rich. h link=topic=7647. msg77578#msg77578 date=1420551762
Works much better now thank you.

No problem :)

Also, updated the sheet once more.  Can now input your minimum and maximum power modifiers. 
 

Offline Prince of Space

  • Lieutenant
  • *******
  • Posts: 182
  • Thanked: 5 times
  • We like it very much.
Re: Multiple Engine Design Spreadsheet - Find the lowest weight necessary
« Reply #6 on: January 06, 2015, 08:39:18 AM »
A few weeks ago, I think, someone pointed out an error in my posted spreadsheet. It may have been double counting the number of engines, so the error Zenrer is pointing out here may have propogated from my sloppy math back in the day. Thanks to the holidays I haven't had a chance to track that down.

In the 2+ years since I posted that spreadsheet I have been tinkering with the engine design tool and I know for certain that I included multiple engine capbaility at one point, but I don't think I ever followed up by posting the more refined versions on the forums. I'm glad someone has picked up my scribblings and shared their thoughts.

The "lowest combined weight of fuel and engines" was indeed the definition of efficiency that I had in mind. Think of it as the efficiency of the ship design as compared to the fuel efficiency of the engines, though I admit that the use of the term in two different contexts can be confusing.

Having used some version of this approach for a couple of years now, I can report two issues.

First, the engine designs that get spit out can sometimes have ludicrously high power modifiers, which when combined with the tendency to assign larger engines for the sake of reducing fuel consumption, can result in very high Max Repair values, sometimes higher than the total Maintenance Supply Points for the ship. I put so much faith in my own design tool I ended up neglecting to double check the design the way I would when critiquing a stranger's ship posted to the forums. I put the damn thing into production and its engine conked out before it finished its first round of task force training. It was a disheartening day.

Second, I now think the whole approach still involves too much trial and error. I always had to spend too much time fiddling with the speed and the range to get a ship with enough remaining tonnage to fit enough useful components, like missile launchers or active sensors. I'm working on a new paradigm where rather than specifying a desired ship tonnage, I specify a desired mission package tonnage, and the new design spreadsheet spits out a chassis that can haul that much bulk as far and as fast as I need. I'm still working the kinks out of it, however.
 

Offline Ninetails

  • Petty Officer
  • **
  • N
  • Posts: 19
  • Thanked: 1 times
Re: Multiple Engine Design Spreadsheet - Find the lowest weight necessary
« Reply #7 on: January 06, 2015, 09:49:50 AM »
First of all, I must commend you on solving this problem, especially in such a relatively simple way - atleast more simple than I would use for this class of problem.
For those of you how do not know this yet: The optimal amount of fuel to engine proportions when one wishes to have the most range and speed pr tonnage is a ratio of fuel tanks 40% the size of your combined engines. This is constant when single engine size does not change (meaning each engine is not changed in size, just in numbers), and it comes from equating the differential size inreases of fuel tanks and engines over changes in engine power/fuell efficiency - where the power of 2.5 is the cause.

The problem with using this type of optimization, is that engine design is not typically governed by only those values, and technically speaking it is optimization of many target functions, without any apriori good combinations of those into 1 target function. Here I am speaking of other problems, such as cost of deployment, redundancy, HTK, engineering max repair, and so on. Cost of deployment is actually a big logistic concern, since it can be much easier to sustain fleets where each ship has a lower cost of deployment (including the fuel cost, and possibly missile usage). This is part of the reason I typically design my fleets to have a lower cost of deployment, which can be done by increasing the size of the enginees and lowering the power/fuel modifier.

For designing ships such that they have a good amount of military hardware and at the same time have speed, range and so on without a lot of trial and error, I can recommand designing ships based on dedicating percentages of the total tonnage/HS to certain types of systems. This works well for 2 reasons, the first is that you can easily work out the math beforehand and find your errors before designing your first component, and secondly because it allows you to very easily copy it over to all the other ship classes it will be used together with, even if they have different sizes. Oh, and there is ofcause the point about most of the effects of the important stats like speed, range and such are generally determined by the proportion of your ship they take up together with some design variables (like fuel efficiency tech). In terms of range it can quickly be calculated by using that 10% of the total tonnage dedicated to fuel tanks on a 100% fuel efficiency (ingame named) gives a range of 18 billion km. For suggested values, I recommend using about 20-40% of tonnage on engines (larger the mission space will be very small very quickly), no more than 40% of the engine tonnage as fuel tanks, about 5-20% for armor and reserve atleast another 5% for crew. When you have fitted those numbers together, say 25% engines, 7% fuel, 8% armor and 5% crew = 45% package usage, you will see how much the package size is, and large a ratio of the size is left for military goodies. Note that there will usually be something more needed, say extra crew and maintance, maybe some extra shields/armor, so I would suggest to initially plan to only use the majority of the military military tonnage, and then use the last 5-15% to squishe something extra in you might thing of (and if nothing else just an extra cannon/launcher/magasin).
If you want a specific suggestion, I have had good experiances with using 1/3 (say 50HS on a 7500 ton ship) for engines with a multiplyer of 0.75 (this gives the stadard speed of 25% of engine techs power/HS), with ranges about 40-100 billion km (depending on tech). It can get a bit hard to manage the repairs on higher tech levels (antimatter), but at those times there will usually be plenty of ways to deal with it.
 

Offline Vandermeer

  • Rear Admiral
  • **********
  • Posts: 961
  • Thanked: 128 times
Re: Multiple Engine Design Spreadsheet - Find the lowest weight necessary
« Reply #8 on: January 06, 2015, 12:17:56 PM »
Really nice and cleanly made, Zenrar. I especially like how the result is easily discernible by this one concluding sentence. I think I will steal that technique for some my future projects.

I had made an engine design spreadsheet myself some months ago, but that one solves for a given engine mass, and leaves the range open to maximization, whereas yours seeks to reduce mass assignment for an already known range.
( can be found here: http://aurora2.pentarch.org/index.php/topic,7314.msg74246.html#msg74246 )

Depending on your design situation, the one or the other will be more useful.
Now we only need another guy to make a sheet that solves for speed, then learn the fusion dance to throw it all together, and assemble the ultimate Aurora engine designer with drop-down of every possible situation and a firework.gif and such.
playing Aurora as swarm fleet: Zen Nomadic Hive Fantasy
 

Offline GreatTuna

  • Lt. Commander
  • ********
  • Posts: 203
  • Thanked: 1 times
Re: Multiple Engine Design Spreadsheet - Find the lowest weight necessary
« Reply #9 on: January 06, 2015, 02:33:13 PM »
Now we only need another guy to make a sheet that solves for speed,

Well, I made one. It doesn't work fully yet, but I think you should take a look.
https://drive.google.com/file/d/0B2qnK3jQHehebWN5Tmd4bjlMMFE/view?usp=sharing

I just checked it and it gave me approximately right answer.
EP\HS = 40 (Gas Core AM)
Fuel modifier = 0.2
Ship size = 3000
Fuel+engine size = 2200
Desired range = 12000 billions km

Result:
3866 km\s with 29 1HS engines (I have no idea on how to make it work with larger engines. Yet) and 14.6 HS (730000 litres) of fuel.

Code: [Select]
Testbed class Cruiser    3 000 tons     89 Crew     418 BP      TCS 60  TH 232  EM 0
3866 km/s     Armour 1-18     Shields 0-0     Sensors 1/1/0/0     Damage Control Rating 11     PPV 0
Maint Life 94.34 Years     MSP 958    AFR 6%    IFR 0.1%    1YR 0    5YR 3    Max Repair 18 MSP
Intended Deployment Time: 3 months    Spare Berths 1   

ExpAI TestBed Engine (29)    Power 8    Fuel Use 0.35%    Signature 8    Exp 2%
Fuel Capacity 730 000 Litres    Range 12512.1 billion km   (37458 days at full power)

This design is classed as a Military Vessel for maintenance purposes
 

Offline Ninetails

  • Petty Officer
  • **
  • N
  • Posts: 19
  • Thanked: 1 times
Re: Multiple Engine Design Spreadsheet - Find the lowest weight necessary
« Reply #10 on: January 06, 2015, 05:48:19 PM »
Solving for speed is trivial, and can be done using a single equation rather than numerical methods like spreadsheets. Here is the algorithm for how to generate the equation: First split the engine+fuel into 2 groups, the engine group containing 5/7 parts of it rounded up. For the fuel part, calculate how much fuel is in it (50,000 l. fuel = 50 tons = 1HS), and calculate the necessary fuel efficiency necessary to reach the desirred range. Then factor out the size of the engine (assuming a single engine for now) and the fuel efficiency tech. The rest supplies the maximum possible engine power/fuel efficiency coefficient that will work, which one rounds down and use, done.
 

Offline Vandermeer

  • Rear Admiral
  • **********
  • Posts: 961
  • Thanked: 128 times
Re: Multiple Engine Design Spreadsheet - Find the lowest weight necessary
« Reply #11 on: January 07, 2015, 12:55:04 AM »
Well, I made one. It doesn't work fully yet, but I think you should take a look.
https://drive.google.com/file/d/0B2qnK3jQHehebWN5Tmd4bjlMMFE/view?usp=sharing

I just checked it and it gave me approximately right answer.
EP\HS = 40 (Gas Core AM)
Fuel modifier = 0.2
Ship size = 3000
Fuel+engine size = 2200
Desired range = 12000 billions km

Result:
3866 km\s with 29 1HS engines (I have no idea on how to make it work with larger engines. Yet) and 14.6 HS (730000 litres) of fuel.
Hehe, well, the calculation of larger engines is indeed the tricky part, and I didn't know how to approach this at first too. The reason for this is that you have a two dimensional field of answers here, while classical formula calculations in excel only determine linear goals.
However, there is a trick, and that is realizing that having maximized single engine sizes are always working towards the optimum, so you can eliminate that factor and return to linear again if you somehow achieve to rule out what that maximum engine size is on every of the power factors. For example, there might be multiple solutions for that 29 1HS engine to exist and fulfill its purpose (4x7HS, 3x10, 2x14, etc. ...), yet the only one with maximum yield would be around a 1x27 design probably.
So what you need to do is to come up with a list that finds those maximum factors to all the power factors in advance, so the problem gets sorted prior to actual calculation. You do that by constructing a prime factor calculator into the sheet, which sounds lofty, but after I researched it, it turned out easy to manufacture. If you unhide the calculation columns right to my sheet, you will find the section where this was done, so you could reverse engineer and adapt it to fit the speed maximization instead. {...just looking over it again, it seems quite convoluted with all the specifications that came into the problem, so maybe it is better to just research how a prime number calculator is built, like I did}

Since the speed problem is a bit reverse, and single engine size actually influences in a more complex way compared to the power factor, maybe it could also be better to instead make a list of the possible sizes of 1-50, and then look to each of them for how much fuel the range requirement is at min. fulfilled, and figure out how many multipliers of that already given single engine size can now still fit into the remaining space (then fill the very last bit with fuel too).
Yes, I think that would actually be the best and most effortless approach here.


@Ninetails: I don't see what you are talking about. This 5/7 is a rule over the thumb, that is exactly that: quite correct to some degree of error, but never actually exact + very wrong even in extreme cases. These sheets are about exact calculations, so no place for country lore.
Solving for speed is not that trivial, nor are all the other cases. If you truly believe that, than you haven't understood the problems well enough. You can of course have adequate engine sets already if you know the game well enough, so all these sheets give you is probably only 5% to one or the other stat. However, you will never find the true optimum with bare finger counting, and especially in case of very tightly planned designs (fighters, missiles, certain jump or cloaked designs), there can be huge gain by having such tools.
playing Aurora as swarm fleet: Zen Nomadic Hive Fantasy
 

Offline Zenrer (OP)

  • Leading Rate
  • *
  • Z
  • Posts: 11
  • Thanked: 1 times
Re: Multiple Engine Design Spreadsheet - Find the lowest weight necessary
« Reply #12 on: January 07, 2015, 01:07:52 AM »
Quote from: Ninetails link=topic=7647.  msg77587#msg77587 date=1420588099
Solving for speed is trivial, and can be done using a single equation rather than numerical methods like spreadsheets.   Here is the algorithm for how to generate the equation: First split the engine+fuel into 2 groups, the engine group containing 5/7 parts of it rounded up.   For the fuel part, calculate how much fuel is in it (50,000 l.   fuel = 50 tons = 1HS), and calculate the necessary fuel efficiency necessary to reach the desirred range.   Then factor out the size of the engine (assuming a single engine for now) and the fuel efficiency tech.   The rest supplies the maximum possible engine power/fuel efficiency coefficient that will work, which one rounds down and use, done. 

As stated by Vandermeer, the 5/7 rule of really just something to go by to simplify things.   If we were to try and get an exact answer here, it would be tough, due to circular referencing. 

This happens because the total engine size is based on the total mass wanted minus the mass of the fuel.   To find the mass of the fuel, you need to find the EPH, and to find the EPH, you need a per engine size, which is based on the total engine size.   This just keeps looping on itself, which can cause calculation errors.   
 

Offline Vandermeer

  • Rear Admiral
  • **********
  • Posts: 961
  • Thanked: 128 times
Re: Multiple Engine Design Spreadsheet - Find the lowest weight necessary
« Reply #13 on: January 07, 2015, 02:14:11 AM »
I was thinking a bit (..I hate when that happens) to come up with special situations where definitely each of the 3 possible calculation routes would be practical.

Maximizing for speed:
- Fighters that operate in a mothership's active sensor coverage. Here you have a pretty set range (to the edge of the given sensor range, back, and then a tiny bit extra), and the propulsion mass assignment is likely predetermined already too, because the weapons take a fixed part. I would actually need that for my current game.
- In case of a game with a working interstellar fuel network or sizable oiler fleets: Military ships would need only need a very low fixed range here (much lower than than the usual 20-30% fuel in the propulsion part), so maximizing the speed with that low requirement will provide fruitful.(and is otherwise difficult to determine by hand - ..you end up wasting research into engines that weren't quite fitted)

Minimizing mass assignment(/maximizing mission tonnage):
- Ships that operate in a fleet. If you have to cross-sync different designs of cruisers, destroyers and frigates, it could be a waste to have them go at different speeds or ranges. So to give them equal capacities and then figure out how to maximize their mission component tonnage, this is very useful.
- Generally the way of choice if one has very organized fleets and likes round numbers.(my current capital{+survey} ships for example all aim for exactly 5kps speed and about 300b range, so here I would apply this calculation)

Maximizing for range:
- Small jump or stealth scouts whose size is predetermined through the jump engine or cloak size, and the mission component size is fixed as well. The remaining percentage of mass needs  to be ideally utilized in right fuel:engine ratios and power factors, so that you get the most out of the scouts.
- A speed goal comes up, because of observation on enemy designs. Ships get readjusted to have pretty much the same design in matters of total mass and engine to mission ratios, but you need to figure out how exactly to rework the already known propulsion part so that the speed goal is achieved.

There are likely more uses, and occasionally even normal design routines can use one or the other. Missiles specifically can easily fall in any of the three. LR Torpedoes might for example be more a case for the speed solver, because a range requirement and payload is likely already set, but generally every missile could be eligible for any case.(I assume most will do either speed or range optimization, because the payload needs to be determined so accurately {/squares} in most cases)

As stated by Vandermeer, the 5/7 rule of really just something to go by to simplify things.   If we were to try and get an exact answer here, it would be tough, due to circular referencing.  

This happens because the total engine size is based on the total mass wanted minus the mass of the fuel.   To find the mass of the fuel, you need to find the EPH, and to find the EPH, you need a per engine size, which is based on the total engine size.   This just keeps looping on itself, which can cause calculation errors.  
Oho, but wait, that was the point there in my answer from above: It is possible. The circular reference is often a nuisance, but in this case for once it can be bridged with the method I described above and also put to paper(?) in the other calculator. The reason this works is because maximized single-engine-sizes always lead to the better solution (since the fuel ratio is always better), so no need to worry about fields of answers or circular recalculation. You just make a list where that maximum possible single engine component is determined for every power factor, and then
« Last Edit: January 07, 2015, 02:17:48 AM by Vandermeer »
playing Aurora as swarm fleet: Zen Nomadic Hive Fantasy
 

Offline Vandermeer

  • Rear Admiral
  • **********
  • Posts: 961
  • Thanked: 128 times
Re: Multiple Engine Design Spreadsheet - Find the lowest weight necessary
« Reply #14 on: January 07, 2015, 02:20:28 AM »
Sorry, the forum is bugging out again. The last line here:
"... let excel select the one that gives you the best range or speed, depending on the goals of the sheet."
playing Aurora as swarm fleet: Zen Nomadic Hive Fantasy