Q&D T5 ShipMaker Spreadsheet Walk-through

[AnotherDilbert]

Let's see how this spreadsheet work:
http://www.travellerrpg.com/CotI/Discuss/showthread.php?p=590680#post590680

I have taken some liberties with the system for simplicity, mainly allowing hulls of any volume (without any pods).

Optionally you can also use the drive formulas to make drives, instead of the drive tables. Cell A3: 0 = Drive Table, 1 = Drive formula.


Let's build a small patrol corvette, we start with a blank sheet:

We keep TL12 (Cell A1) and Drive Table (Cell A3), but set Cell A2 to 1 = Military (this affects default crew and Comfort).

We set size to 400 Dt (cell G4) and select configuration "Airframe" in the list in cell C3:

A reasonable, but not necessarily cheap, Structure is pre-selected in cell C4. It can be changed, all possible Structures for this Configuration are shown in the list in cell C4.

Any included Legs, Wings, and/or Fins are automatically selected, but can be changed in the lists in cells C13 to C15:

Armour layers can be added by specifying "1" in cells G9 to G12. Anti-layers are selected in the lists in cells D8 to D12. Coating is selected in cell D7.

The stage of all armour layers is selected in cell H8. A high number is preselected, giving us the best stage possible at this TL.

If more layers are needed copy an entire row and insert a new row (after the first and before the last armour row).

We select four layers with one each of the anti-layers, AV for each layer is shown in column L and total AV is shown in cell E6:

We select water capability in the list in cell C16, we select "Submersible" to be able to refuel and hide everywhere.

If we want the craft to be carried in grapples by a larger craft specify "1" in cell G18, but we leave that empty:

 

[AnotherDilbert]

Drives are dimensioned for the hull and any externally carried craft and tanks, as shown in rows 20 to 22. Drives are dimensioned for the tonnage in cell C22.

If we later add external craft the drives and required fuel will be increased.

If you want several identical systems, e.g. for backup, specify the number you want in the "#" column.

We specify drives in cells G24 to G25 and stages in cells H24 to H26. We specify fairly small drives with max stage since we will be squeezed for space, Jump-2 and M-3:

Drives with stages Improved and Modified have efficiency 110% so can drive more tonnage than Standard drives. We see how many EPs are required to drive the current total tonnage in cells D24 to D26. Standard drives have an even hundreds of EPs, so we can see that we the drives are not fully utilised. We can specify 1 = Drive formula in cell A3 to get custom drives exactly for this ship, but that is not RAW.

Instead we go back and increase hull size to 440 Dt (cell G4). We can see that the drives are now full utilised, if we increased the hull any more we would need bigger drives.

Fuel, purifier, and usable scoops are pre-specified in cells G28 to G33, but can be overridden. Fuel is dimensioned in number of J-1 and weeks of PP fuel.

 

[AnotherDilbert]

Bridge and Controls are specified with an Ergonomics score. Ergonomics can be different for the bridge and other controls. Total resulting Ergonomics is shown up top in cell C1 (including +2 for a bridge).

Don't change the number of consoles here, they are based on the defaulted crew further down.

A computer enough to handle the jump drive (according to CT rules) is pre-selected. Override by selecting the numeric model in cell G38, specify 1 in cell F31 to get an fiberoptic computer and 1 in cell E38 to get a bis (if possible). An additional computer can be specified in row 43. A few extra m/0bis computers are a cheap way to get more automated tasks.

Stages for the consoles and computers can be specified in the "Stage" column, to modify C+S for automated tasks.

We specify max stage for the consoles and a few extra Generic m/0bis computers:

We leave the number of consoles and Ergonomics for later.

 

[AnotherDilbert]

Sensors:

Select the type of sensor in column A, type of mount in column C, range effects in column D, number of identical sensors in column G and stage in column H.

Turret mounts can be made Extendable by specifying 1 in column E and Deployable by specifying 1 in column F. For other mount that is ignored.

The resulting system is summarised in column Q.

The same list of Range effects are used for both Space and World systems but only valid effects are implemented for the given system, so both "Rng+2, TL+2" and "Rng+3, TL+3" give the same result (range DS) for Space range systems.

If you need more sensor rows, copy an entire sensor row and insert as many rows as needed. Copy the entire row, not just a few cells.

Copy

Insert

Result:

We select an Antenna mount (max stage) and a backup Surface (Generic) mount system for the major sensors, to get a good but perhaps unreliable system and a reliable backup system of each type, most with long range to maximise the sensor mod:

 

[AnotherDilbert]

We skip Crew, Passengers, and Life Support for now ang go down to weapons.

Weapons:

Select the type of weapon in column A, type of mount in column C, range effects in column D, number of identical weapons in column G and stage in column H.

The minimum mount for the weapon system is automatically selected, but can be overridden as usual. Invalid mounts are noted in columns O and P.

Turret mounts can be made Extendable by specifying 1 in column E and Deployable by specifying 1 in column F. For other mount that is ignored.

Weapon systems smaller than 1 Dt (e.g. turrets with decreased range) are assigned to firmpoints and weapons of 1 Dt or bigger are assigned to hardpoints. Hardpoints are exchanged for firmpoints automatically. Available hardpoints are shown up top in columns M and N, red negative numbers means you have used too many hardpoints.

The resulting system is summarised in column Q.

The same list of Range effects are used for both Space and World systems but only valid effects are implemented for the given system, so both "Rng+2, TL+2" and "Rng+3, TL+3" give the same result (range DS) for Space range systems.

If you need more weapon rows, copy an entire weapon row and insert as many rows as needed. Copy the entire row, not just a few cells.


We select couple of long range Particle barbettes, a long range CommCaster, a short range CommCaster, and fill the rest of the firmpoints with point defence systems:

(The small CommCaster is limited to stage Modified to save money.)

 

[AnotherDilbert]

Screens:

Screens are specified in the same way as weapons.

We specify a Damper and Meson screen, with backups:

 

[AnotherDilbert]

Carried Craft:

Carried craft are specified by selecting hangar/mount type, size, and number of craft.

Craft in brackets, niches, and grapples are carried externally so increase total tonnage. The size of the external craft are automatically added to total tonnage in row 20, and drives are resized.

We specify hangar for a 20 Dt craft (to make sure it is protected by armour). Vehicles can be carried in the cargo hold.

 

[AnotherDilbert]

Crew:

Crew and Consoles are defaulted, vaguely based on CT and MgT2:

We override it by adding some troops and a few extra maintenance crew (since we have several systems with questionable reliablity, e.g. with stages Early and Improved).

We decrease the number of gunners to one petty officer, one for each barbette, one for each other weapon type, one for the screens, and let the consoles remain so the rest of the systems can be automated. Total six gunners.

We remove the flight crew, since the craft is only a utility craft, not intended for combat.

Add a Steward as a cook/supply clerk. (This takes us to a nominal crew of an even 30.)

Decrease the Screen consoles to 2, the other screens are only backups.

We need no Passengers.

 

[AnotherDilbert]

Now we can go back to Crew quarters and Life Support.

We add control the quarters by specifying Comfort in cell G57 and Demand in cell G62, Common Areas are added as needed.

We can add Luxury staterooms for some crew in cell G58, those are deducted from the needed regular staterooms.

By default 150% of needed freshers and 200% of needed life support is allocated to allow double occupancy to carry supernumeraries.

Long term life support can be specified by inserting 1 in cell D77. Long term life support is just twice as large, both regular and luxury life support. Life support of each type is rounded up to even Dtons.


We accept the default Comfort -2 for Military ships. We add a luxury stateroom for the Captain in cell G58. We add a 4 Dt Workshop (for the maintenance crew) in cell G81.

Specifying actual rooms is done when deck plans are drawn. Crew, Passenger, Steerage, and Life Support tonnage are summed separately to aid drawing deck plans.

 

[AnotherDilbert]

Finally, we can go back to Bridge and Consoles to adjust Ergonomics.

We accept the number of consoles specified with the crew before.

We specify Ergonomics -1 for the Bridge and Ergonomics -2.5 for other consoles, giving us a total Ergonomics ±0.

The remaining 32 Dtons are allocated to the cargo hold.

 

[AnotherDilbert]

And we have a finished design:

 

[AnotherDilbert]

Updated spreadsheet:

Drive Nexus:

Specify Pre-nexus in column E and post-nexus in column F. This is a minimum, the spreadsheet may choose a higher number of drives if necessary to achieve desired potential.

 

[AnotherDilbert]

Jump Grid:

Default is Jump Bubble, specify a positive number in cell G24 to switch to Jump Grid. Note that armour is decreased.

 

[AnotherDilbert]

Draft Hit Location Table:

A draft hit location table is automatically generated at the bottom of the spreadsheet. The span is increased by one to give empty hit locations for wings and extended hardpoints.

The hit locations are based solely on size, systems are added up in categories, and individual systems are selected within each category based on total tonnage. If a category only has one hit location, no specific system is selected but the category is used for the hit location, e.g. the first hit location "Sensors".

The bridge and sensors are placed first, then accommodations, then cargo hold and fuel, and last drives. The jump drive is placed in the middle for Jump Bubble and at the end (before the PP) for Jump Grid.

The categories are:

Divided into a specific hit location table:

Not all systems are guaranteed to be included in the hit location table, just the biggest systems. You will probably have to adjust the table to make sure all important systems are included.