Tutorial: T5.10 ShipMaker Spreadsheet

[AnotherDilbert]

Spreadsheet can be found here:
http://www.travellerrpg.com/CotI/Discuss/showthread.php?t=40404


Let's build something like a Patrol Corvette yo see how it works.

We start with the optional simplified design step:

We select type: Armed Civilian,
50 Dt cargo,
a Ship's Boat,
TL 14,
Airframe,
J-4,
M-4:

A 400 Dt ship is suggested and designed.

 

[AnotherDilbert]

Move to the next tab with ctrl-PgDn.

We find a description of the ship:

"Using a 400 Dton streamlined hull, it has J Drive H, M Drive D2, and P Plant H giving performance of jump-4 and 4 g acceleration. There is fuel tankage of 168,56 Dton, enough for 4 weeks and 1 jump-4.
The ship has 10 control consoles, 9 operating consoles, and a m/3/bis/phot computer.
There are five sensors: a Mod AR Surf Commu-10 +9, a Mod AR Surf Radar-11 +9, a Mod AR Surf Scope-11 +9, a Mod Vd Surf Jamme-10 +4, and a Mod AR Surf Steal-14 +9.
There are nine staterooms for the crew with a Comfort of 3.
There are six weapons: three Mod DS T4 Miss-11 +3, a Mod D T4 Beam-11 +3, a Mod D T4 Sand-10 +3, and a Mod D T3 SLM -11 +2.
There is a carried craft: a Ship's Boat in a Grapple.
Cargo capacity is 52 Dton. The ship requires a crew of 17: two pilots, two navigators, two sensor operators, three engineers, a medic, and six gunners. The ship costs MCr 182,23298. "


On the third tab "Ship" we find the detailed design.

A code block to post the ship on forums can be found near the bottom:

Copy the code block and paste:

TL-14 C-DA44                   Ergo 2   Comfort 3    Demand 0
                                     0,440130000000011   182,23298
System                              #        Dton        Cost
Hull                                          400            
Airframe                                                   28
Plate     AV=14 ( 140 vs Blast, 2800 vs Heat/Beam, 140 vs Pres, 1400 vs Nuke, 1400 vs Rad, 0 vs EMP )
Ablat     AV= 0 ( 1400 vs Heat/Beam  )
Armour (Outmost layer) Std Anti-Rad          14                          AV=14 ( 140 vs Blast, 1400 vs H/B, 1400 vs Nuke, 1400 vs Rad )
Landing Wheels Wilderness                       0           0
Wings Incr Perform                              0           0
Fins Incr Agility                               0           0
Lifters Installed                                           2
                                                             
External Craft                     30                        
Total Drive Capacity              430                        
                                                0            
Jump Bubble                                                 0
Imp J Drive H                       1          45          45
Adv M Drive D2                      1     4,66662    18,66648
Adv P Plant H                       1     8,33325     16,6665
                                                             
Fuel, Jump                                  154,8            
Fuel, Power                                  13,8            
Purifier                            1           1         0,5
Scoops                              1           1        0,05
                                                             
    Control Console                10          20           1
    Operating Console               9          18        0,45
    Computer m/3/bis/phot           1           3           7
                                                             
Sensors                                                      
Mod AR Surf Commu-10 +9             1           0         1,5
Mod AR Surf Radar-11 +9             1           0         1,5
Mod AR Surf Scope-11 +9             1           0         1,5
Mod Vd Surf Jamme-10 +4             1           0         1,5
Mod AR Surf Steal-14 +9             1           0         1,5
                                                             
Crew:                       Comfort 3                        
Staterooms                          9          18         0,9
Freshers                            7         3,5         3,5
Common Areas                     29,5        29,5           0
Life Support:                                                
Med Console                         1         0,5         0,5
Auto-counsellor                     1           1         0,2
Clinic                              1           2           1
Life Support, Long Term             4           8          16
Escape Capsules                     4           4           4
                                                             
Standard Air Lock                   4           0           0
                                                             
Cargo                                          52            
                                                             
Weapons                                                      
Mod DS T4 Miss-11 +3                3           9        25,5
Mod D T4 Beam-11 +3                 1         0,5           1
Mod D T4 Sand-10 +3                 1         0,5         0,8
Mod D T3 SLM -11 +2                 1         0,5           1
Mod D T1 Sand-10 ±0                 1           0           0
Mod D T1 Beam-11 ±0                 1           0           0
Mod SR T1 Miss-8 ±0                 1           0           0
                                                             
Carried Craft                                                
Grapple Ship's Boat                 1           1           1
                                                             
                                Crew     Consoles    Salaries
Crew                          17               19       56000
Command                        1                1        5000
Bridge                         6                             
    Pilot                           2           2       12000
    Astrogator                      2           1       10000
    Sensor & EW                     2           2        6000
Engineer                       3                         9000
    Engineer                        2           3            
    Maintenance                     1           1            
Service                        1                1        2000
    Medic                           1                        
    Steward                         0           1            
Gunner                         6                        12000
    Gunner                          6           6            
Flight                         0                1           0

The code block is started and ended with quotes, remove them for best results.

 

[AnotherDilbert]

We can make a detailed design on the third tab "Ship".

If we did the simplified design in post 1, most things are already filled in:

Change values in grey boxes. Only type numbers, any text input can be selected from lists.

Start with TL in the top left corner. In cell A2 we can set defaults for civilian or military ships, specify 0 for a civilian ship, 1 for a lightly armed ship, and 2 for a military ship. Armed ships get more and better weapons and sensors, with a larger crew with worse crew comfort.

Generally we specify potential, size, or number of systems in the "Desired" column. Some parameters can be selected in the left-most columns.

Desired ship size is specified in cell G4. The spreadsheet can make any size ship from 100 Dt to tens of thousands of Dt. Uneven tonnage, e.g. 175 Dt, is ok, it is considered a ship with a pod of the appropriate size.

Configuration is selected in the list in cell C5, and armour structure in cell C6. Only appropriate structures for the selected configurations are shown, and a reasonable structure is defaulted.

Armour layer are specified by selecting an anti-layer in column D or the number 1 in column G:

Armour is calculated:

If you want more rows for armour layers, copy an entire row and insert new rows: Be careful to copy and insert entire rows, not just a few cells.

Copy

Select a few rows:

Insert:

We can now specify more armour layers.

In the same way we can get more sensor and weapon rows.


Hull fittings can be selected in the appropriate row. Only fittings possible for this configuration are shown. Fittings that are included in the configuration are pre-selected. Example, we select a Submersible hull:

 

[AnotherDilbert]

Select pods and subhulls in cells C26-C27. A default size is shown in cells D26-D27, adjust to your preference:

Select a 10 Dt pod:

Small pods are added to overtonnage, larger pods are added to Total Drive Capacity instead.

This can be adjusted by specifying a 1 or a 0 in cell G29.

Carried craft are specified below in the spreadsheet and are handled similarly.

This way a small ship can add a small pod or craft without needing larger drives, and a larger ship can add several pods or craft compensate with larger drives to avoid overtonnage.

In a similar manner optional wings gives more space, that can either be added as overtonnage, or increase the hull and hence drives.

It is perhaps not by the letter of the rules, but I believe it is about as the overtonnage mechanism is intended.

 

[AnotherDilbert]

Drives are selected by specifying a desired potential in cells G35-G36. An appropriate power plant is defaulted.

Desired max tech stage is specified in column H. You get as good a stage as can be managed at the specified TL.

The drives are dimensioned for the Total Drive Capacity calculated in cell C32, not just the hull. The Potential to Energy Point formula (p114) is used to select a lettered drive. The spreadsheet makes a reasonable choice for drive nexus, but you can override that in cells E35-F37.

Fuel is calculated automatically, also by Total Drive Capacity, not just hull size.
We can change the fuel allocation by specifying jump fuel in J-1 units and operating fuel in weeks in cells G39-G40.


The spreadsheet takes a vague stab at defaulting fuel fittings such as scoops and purifiers, change in cells G42-G45. Since they have a specified TL I assume a tech stage can be applied, in general "Generic" to get a uniform QREBS.

 

[AnotherDilbert]

Consoles are summarised here:

You don't have to specify anything here, the allocated consoles are based on the allocated crew and Ergonomics. (Control Panels are calculated offscreen, column AH.) Change this when everything else is done.

Bridge and other control rooms can be specified when deck plans are drawn.

By default officers, pilots and gunners get Control Consoles, the rest of the crew get Operating Consoles.

Computers are specified here, a desired model number in cells G50-G51 (-1 for no computer). Ask for a bis computer with a 1 in column E and a fib (or photonic) computer with a 1 in column F. Ask for a tech stage in column H. You get what can be achieved at the selected TL.

We probably want a better computer (generic for cheapness) for our patrol ship and a few secondary computers for automation:

 

[AnotherDilbert]

Sensors are specified by selecting type, mount, range effect, extensible mount, deployable mount, number of identical sensors, and max desired tech stage.

If you start with selecting a sensor by specifying a 1 in column G, the rest is preselected to reasonable values, based on the cell A2 (civilian or military ship)

The sensor is calculated including the sensor range mod for active sensor.

Default range sensors are cheap and preselected except when cell A2 is 2 or higher.

If you want more sensor rows copy an entire row and insert as many rows as needed, as described above.


For a patrol ship we want better sensors:

 

[AnotherDilbert]

Crew and passenger accomodations are automatically allocated to a specified level of Comfort and Demand.

Specify number of crew and passengers below before you fine-tune the accommodations.

Specify desired crew comfort in cell G65 and passenger demand in cell G70. The resulting rating is shown in cells I65 and I70 respectively.

The crew is accommodated in 2 Dt staterooms, single to quad occupancy selected per Comfort level. Freshers can be specified in a percentage of needed capacity, by default 150% to allow squeezing in more people if needed.

Luxury staterooms can be specified manually, e.g. for the Captain or all officers, each Lux stateroom displaces one regular stateroom.

Common areas are added as needed to achieve the desired comfort and demand.

Total tonnage for each category is shown in column C to aid drawing deck plans.

Life support and Escape capsules are also specified as a percentage of nominal capacity, by default 200% for redundancy and to allow dual occupancy.

Barracks are specified by type and number of units. They are considered a special type of passengers and not included in Comfort or Demand calculations.


Specialised compartments such as Vaults, Labs, and Workshops can be specified by size and number in the Payload rows.

By default one airlock per 100 Dt is allocated without cost. More airlocks can be specified as desired.


We leave all this for later. We don't have much space left, so we might have to squeeze crew comfort a little.

 

[AnotherDilbert]

Cargo is specified relative available space or as an absolute number, by default all remaining space.

Cell G69 can specify an exact number of Dt or a negative number for all remaining space (default).

Cell D96 can specify a desired target overtonnage, or 0 to disregard. A higher number means a larger cargo hold and a larger overtonnage.

If no space is left for a cargo hold these calculations may be ignored.

 

[AnotherDilbert]

Weapons and Screens are specified by type, mount, extendable mount, deployable mount, number of mounts, max tech stage,

Some default weapons are defaulted, based on the value in cell A2 (higher means better weapons).

Specify a number of weapons first and a reasonable mount, range effect, and max tech stage is defaulted. Override as needed.

Small weapons (<1 Dt) are allocated to firmpoints, normal weapons to hardpoints.

Some normal weapons for attack and some small point defence weapons are allocated by default.

SLM Hybrids are allocated as a single mount with three individual weapons, all sharing the same Range Effect and tech stage.


We allocate some short-ranged, cheap screens:

 

[AnotherDilbert]

Carried craft are allocated by type and number of craft.

A reasonable size and mount type is defaulted, but can be overridden.

Craft carried externally are not deducted from hull space, but added as overtonnage or external load increasing Total Drive Capacity, see post #4 above.

Craft in Hangars are deducted from Hull space.

 

[AnotherDilbert]

Crew and passengers are specified near the bottom of the spreadsheet:

A crew is defaulted, vaguely based on CT and MgT2 thumb-rules. Military ships get larger crew. Override as desired.

Consoles are defaulted based on functions and desired level of Ergonomics. Override as desired.


When you have specified the crew and passengers, go back and look over accommodations, see post #8 above.

 

[AnotherDilbert]

Once we have a design we can look at the economic estimate and draft Hit Location Table at the bottom of the spreadsheet:

It's not a merchant ship, so it makes no money, of course.

The Hit Location Table is based entirely on size of systems and will have to be adjusted, but gives a rough idea of what it should be.

 

[AnotherDilbert]

And we have a finished design:

Summarised in a code block:

TL-14 C-DA44                   Ergo 2   Comfort 3    Demand 0
                                     0,440129800000022 230,6079802
System                              #        Dton        Cost
Hull                                          400            
Airframe                                                   28
Plate     AV=28 ( 280 vs Blast, 4200 vs Heat/Beam, 560 vs Pres, 1540 vs Nuke, 1400 vs Rad, 140 vs EMP )
Ablat     AV= 0 ( 1400 vs Heat/Beam  )
Armour Outer Std Anti-Rad          14                          AV=14 ( 140 vs Blast, 1400 vs H/B, 1400 vs Nuke, 1400 vs Rad,   0 vs EMP )
Armour Std Anti-EMP                14          16              AV=14 ( 140 vs Blast, 1400 vs H/B,  140 vs Nuke,    0 vs Rad, 140 vs EMP )
Landing Wheels Wilderness                       0           0
Wings Incr Perform                              0           0
Fins Incr Agility                               0           0
Submersible Water Landing                       8           8
Lifters Installed                                           2
                                                             
External Craft                     30                        
Total Drive Capacity              430                        
                                                0            
Jump Bubble                                                 0
Imp J Drive H                       1          45          45
Adv M Drive D2                      1     4,66662    18,66648
Adv P Plant H                       1     8,33325     16,6665
                                                             
Fuel, Jump                                  154,8            
Fuel, Power                                  13,8            
Purifier                            1           1         0,5
Scoops                              1           1        0,05
Water Intake                        1           1        0,05
                                                             
    Control Console                12          24         1,2
    Operating Console               9          18        0,45
    Computer m/5                    1           5        13,5
    Computer                        5         2,5       0,625
                                                             
Sensors                                                      
Mod DS Surf Commu-12 +14            1           0         5,5
Mod DS Surf Radar-13 +14            1           0         5,5
Mod AR Surf EMS-14 +9               1           0         1,5
Mod DS Surf Scope-13 +14            1           0         5,5
AR Surf Visor-14 +7                 1           0           2
Mod AR Surf Neutr-12 +9             1           0         1,5
Imp AR Surf Grav -14 +8             1           0           2
Mod G Surf Jamme-13 +7              1           0         6,5
DS Surf Steal-14 +12                1           0           6
                                                             
Crew:                       Comfort 3                        
Staterooms                          9          18         0,9
Freshers                            7         3,5         3,5
Common Areas                     32,5        32,5           0
Life Support:                                                
Med Console                         1         0,5         0,5
Auto-counsellor                     1           1         0,2
Clinic                              1           2           1
Life Support, Long Term             4           8          16
Escape Capsules                     4           4           4
                                                             
Standard Air Lock                   4           0           0
                                                             
Cargo                                          13            
                                                             
Weapons                                                      
Mod DS T4 Miss-11 +3                3           9        25,5
Mod D T4 Beam-11 +3                 1         0,5           1
Mod D T4 Sand-10 +3                 1         0,5         0,8
Mod D T3 SLM -11 +2                 1         0,5           1
Mod D T1 Sand-10 ±0                 1           0           0
Mod D T1 Beam-11 ±0                 1           0           0
Mod SR T1 Miss-8 ±0                 1           0           0
                                                             
Screens                                                      
Mod D Bo Nucle-14 +5                1         1,5           2
Mod VL Bo Meson-14 +5               1   1,0000002   2,5000002
                                                             
Carried Craft                                                
Grapple Ship's Boat                 1           1           1
                                                             
                                Crew     Consoles    Salaries
Crew                          18               21       58000
Command                        1                1        5000
Bridge                         6                             
    Pilot                           2           2       12000
    Astrogator                      2           1       10000
    Sensor & EW                     2           2        6000
Engineer                       3                         9000
    Engineer                        2           3            
    Maintenance                     1           1            
Service                        1                1        2000
    Medic                           1                        
    Steward                         0           1            
Gunner                         7                        14000
    Gunner                          6           6            
    Screen                          1           2            
Flight                         0                1           0

 

[Spartan159]

Thanks for this, I am going over it now.

One thing I caught, the comment on the tables page about jump plate cost, T5.00 page 369 listed Jump Plate requirements as 1 plate per 10 tons of ship, and each plate cost 10KCr.

So Jump Bubbles have no cost, Jump Plates are 0.1 MCr / 100 tons, and a Jump Grid is 1.0 MCr / 100 tons if I understand correctly.

Something else, is it possible to design a ship with backup drives? I have one I am working on that has Thruster as a backup/alternate drive.

P.S. How do you allow for firmpoints?

 

[AnotherDilbert]

So Jump Bubbles have no cost, Jump Plates are 0.1 MCr / 100 tons, and a Jump Grid is 1.0 MCr / 100 tons if I understand correctly.

Thanks, I have to admit I picked the cost of Jump Plates at random. It will be fixed.

Something else, is it possible to design a ship with backup drives? I have one I am working on that has Thruster as a backup/alternate drive.

I haven't implemented Thrusters (Dean Drives), but they have the same size as the M-drives, so we can cheat...

Copy the entire M-drive row:

Insert a new row:

The new row can be used for the thrusters.
Change cell A31 to "Thrusters".
Change cell L31 from "=K31*2*AQ31" to "=K31*0.5*AQ31"

We get something like this:

Backup drives are on my to-do list, but not a very high priority...

P.S. How do you allow for firmpoints?

Hardpoints are converted to three firmpoints each as needed.

So, if you have a 400 Dt ship it has four hardpoints. If you allocate a small (range-reduced) turret it takes a firmpoint, so one hardpoint is converted to three firmpoints. The ship now has three hardpoints and two firmpoints unallocated.

 

[AnotherDilbert]

New version:
http://www.travellerrpg.com/CotI/Discuss/showpost.php?p=607383

We now have three rows for drives and two rows for power plants, each row can change drive type:

We can add more rows by copying an existing row and inserting a new row.


For power plants we can give a negative number in the "Desired" column to get an automatically dimensioned plant. Example:

With a jump-2 drive and a M-3 drive is automatically powered by a Fusion-3 plant and a Collector-2.

Since we have a Collector to power the Jump drive, no jump fuel is allocated by default. Additional jump fuel can be added manually.

 

[AnotherDilbert]

Something else, is it possible to design a ship with backup drives? I have one I am working on that has Thruster as a backup/alternate drive.

Implemented, both backup drives and Thrusters (Dean Drive).

 

[Spartan159]

Implemented, both backup drives and Thrusters (Dean Drive).

Nice, thanks.

A couple of errors in the new sheet, on the tables page:

Mounts: Surface and Bolt-in need separate lines, Surface is Size 0 Mod 0 1 MCr where as Bolt-in is Size 3 Mod 3 3MCr

Range codes: R=12 name says Atttack3

Would it be possible to add version numbers to the file name, or a date or something to differentiate the updates?

 

[AnotherDilbert]

A couple of errors in the new sheet, on the tables page:

Thanks, I appreciate bug rapports.

Mounts: Surface and Bolt-in need separate lines, Surface is Size 0 Mod 0 1 MCr where as Bolt-in is Size 3 Mod 3 3MCr

I cheat a bit and fix it with logic in the spreadsheet. It would be inconvenient with several lines that are only valid for some systems.

Range codes: R=12 name says Atttack3

Thanks, it will be fixed in the next version.

Would it be possible to add version numbers to the file name, or a date or something to differentiate the updates?

Sorry, I'm a bit lazy and only use the file system date code of the file. I keep the file write protected to avoid accidental changes. I want to avoid keeping a separate table or number that I will forget to update half the time.