MgT2 Ship Design Speadsheet Walk-through

[Linwood]

I’m playing w/ building a space station this AM - nothing fancy, just a small orbital facility for traffic control and monitoring nearby space. I’m trying to add some docking clamps to store extra cutter modules but the spreadsheet doesn’t seem to account for the tonnage correctly - it seems to be subtracting the tonnage for the module as a whole (which makes sense for sizing jump drives and such) instead of just the tonnage for the clamps themselves.

Otherwise it’s pretty handy - nice job!

[AnotherDilbert]

I think it is correct...


In clamp: Only the clamp is deducted from interior space.
In hangar: lots of space deducted from interior space.
Cost is added either way.


Vessels held in clamps are not inside the hull, they are outside the hull and hence does not consume space inside the hull.

On the other hand drives have to be bigger.

Docking Clamp
A docking clamp allows a spacecraft to carry another vessel on the exterior of its hull, typically a small craft such as a ship’s boat or fighter. This permits a ship to carry auxiliary craft without consuming valuable interior space.

A ship’s Thrust and jump capability must be recalculated when another ship occupies its docking clamp, using the combined tonnage of both ships. This will likely mean the manoeuvre drive will be operating at a lower Thrust and the jump capability may be reduced.

[HalC]

I just downloaded the spreadsheet - and learned a few tricks that I'd not bothered to learn or lacked exposure to. The "custom" formating that you used in Cell A2 was an eye opener for me. Took a bit for me to realize that it was a less than zero, greater than zero, and equal to zero formating structure, but it works. NICE!

the other stuff with boolean logic, that was also nice. Multiplying by use of "If" statements where something had to be >= a value, and less than or equal to another (ie a range of numbers between low and high value) was also spiffy. A false result on either of the conditions results in a zero. Anything multiplied by zero is zero - which is a nice way around the =and(condition 1, condition 2) function in excel.

NICELY done.

[AnotherDilbert]

Thanks!

I generally find it easier to multiply by condition, rather than insert functions into the middle of formulas.

[Jackstar]

New version:

Spreadsheet (same URL): https://www.dropbox.com/s/tidv4xqelrw2b ... .xlsx?dl=1


New functionality:
Each component has its own TL (by default ship's TL) to support refitted ships and user upgrades.
Pop-up turrets.




TL 12 ship with a TL 15 computer:

Higher TLs marked in red to avoid mistakes...


Pop-up turrets are specified with a negative number in the #Wpn column, e.g. -2 for a pop-up double turret:

Works for turrets and fixed mounts.

Hi AnotherDilbert,
when you posted a design for the special-operations ship, I realised that I don't have the latest version (I have v1.19). Is the quoted link still good for the latest?

[AnotherDilbert]

Now it is...

There is no big difference, mostly bugfixes.

[Jackstar]

Now it is...

There is no big difference, mostly bugfixes.

Thanks. I find this extremely useful.

Do you have a guide to the advanced technolgy advantage options? For example, i am trying to get a Jump Drive with Stealth and Early Jump (for the Harrier from Pirates of Drinax). What numbers go in O33 and O44? (When I look at the formula, I can't see Early Jump anywhere.)

[AnotherDilbert]

Sorry, I haven't implemented Early Jump.

Change the formula in cell H33 to

= G33*MAX( 10*(F33>0); OM( $K33=Tables!$A$84; Hull; DriveCapacity )*X33+5*(F33>0) )*( 1 + AD33 - 0 * 10%*(MAX(0;AH33)*(O33=0)) )
[change underlined and in red].

Then the size of the jump drive will not be changed, so you can allocate the advantages to anything else. Cell N33 is just informative, so you can type in anything there.


Default advantage for jump drive is Reduced Size. A positive value in cell O33 changes the advantage to Decreased Fuel. A positive value in cell P33 adds Stealth Jump (if there are enough advantages available).

[Jackstar]

Sorry, I haven't implemented Early Jump.

Change the formula in cell H33 to

= G33*MAX( 10*(F33>0); OM( $K33=Tables!$A$84; Hull; DriveCapacity )*X33+5*(F33>0) )*( 1 + AD33 - 0 * 10%*(MAX(0;AH33)*(O33=0)) )
[change underlined and in red].

Then the size of the jump drive will not be changed, so you can allocate the advantages to anything else. Cell N33 is just informative, so you can type in anything there.


Default advantage for jump drive is Reduced Size. A positive value in cell O33 changes the advantage to Decreased Fuel. A positive value in cell P33 adds Stealth Jump (if there are enough advantages available).

Thanks, Ii is a bit clearer now - you have automated some of the options that affect other things but not the other ones, like Early Jump, that are just functional.

[ChalkLine]

May I post a suggestion?:

I design some ships for players on the basis that not all the features are fitted at sale, and thus the price is different.

If you wish to implement this I suggest you have a 'Retrofit?' button on item. The button only charges 5% of the item's price but allots its tonnage.

(PS: thank you for having the standard air-lock on the spreadsheet. So many systems forget to allot its tonnage.)

[AnotherDilbert]

Improved handling of Tech Advantages for drives and weapons:


Any repeatable advantage/disadvantage can be specified as default to be applied as many times as allowed by current tech stage. A single additional advantage or disadvantage can be specified in the "Once" column and will be applied first it the current tech stage allows.

If you specify a negative value in column E you can specify prototype drives, but if a lower tech drive is selected you get as good a tech stage as the TL allows.
Examples:

At TL-12 we have specified Tech Stage -9 and Jump-2. We get as good a Tech Stage as possible, which is Advanced with 1 advantage.

If we instead specify Jump-4 (which requires TL-13) we get stage Prototype with 1 disadvantage:


If we specify a positive number in column E no prototype drives are used, but only drives allowed at the current TL.

If 0 (as it is by default) is specified no tech stage is used.

[AnotherDilbert]

Weapons advantages are handled more simply:


By specifying a positive number in columns O or P that many advantages/disadvantages are applied as Increased/Reduced Size or Energy Efficient/Inefficient respectively.

By default advantages are unspecified, e.g. Long Range or Accurate. Disadvantages are taken as Energy Inefficient.

Missiles and Torpedo weapons use Reduced or Increased Size instead.


Download link as usual: https://www.dropbox.com/s/tidv4xqelrw2b ... .xlsx?dl=1

[Linwood]

Oh good! I’ave been waiting / hoping for this improvement....

[AnotherDilbert]

Bridges simplified; types selectable from menu, number of bridges as desired:

A reasonable bridge is defaulted. Copy and insert new rows if you need more types.


Residential and Commercial Zones removed as separate rows and added as generic accommodation options:



Concealed compartments removed as separate rows and added as cargo options:



By default a computer big enough for the most demanding software is dimensioned:
More software, bigger computer:



Description enhanced to handle options better (or at all...):

Using a 100 Dton partially streamlined hull, it has performance of jump-2 and 2 g acceleration. There is fuel tankage of 21 Dton, enough for 4 weeks and jump-2.
The ship has a bridge with holographic controls. Adjacent to the bridge are a m/15 and a m/10 computer. The sensor suite include a civilian sensor.
There are three staterooms and three tons of common areas. There are six escape capsules, two briefing rooms, three libraries, and four tons of commercial zone.
Cargo capacity is 10 Dton. The ship requires a crew of three: a pilot, an astrogator, and an engineer. The ship costs MCr 52,5.

Bugfixes.


Download link as usual: https://www.dropbox.com/s/tidv4xqelrw2b ... .xlsx?dl=1
or more permanently at CotI.

[FreeTrav]

Do me a favor, please... check out the short descriptions I use in Freelance Traveller's Computer Connection software lists. Write a similar short description of this spreadsheet, and email me (editor@freelancetraveller.com) the description and a permalink to the sheet. I'll add it to the software list...

[qstor]

have you done anymore ships? I'm looking for the 1000 Qasar scout ship from the t20 EPIC adventure 5?

[paltrysum]

New version:

Spreadsheet: https://www.dropbox.com/s/tidv4xqelrw2b ... .xlsx?dl=1

Thank you for sharing it, AnotherDilbert. This will be very useful!

[AnotherDilbert]

210102:
Added some components from the Companion and JTAS, e.g. carronades, laser barbettes, and burst lasers.
Minor bugfixes.

Carronades are defaulted to 40 Dt, since they seem to be some sort of minor bays. If you prefer you can specify some other tonnage in cell R126.

Missile Containers and Torpedo-interceptor Clusters added as turret weapons. Use a fixed mount for Torpedo-interceptor Clusters to get the correct size and price.



Also some new hull and stateroom options, look in the drop down menu in cells A18 and A101.

.
https://www.dropbox.com/s/tidv4xqelrw2b ... .xlsx?dl=1