MgT2 Ship Design Speadsheet Walk-through

[AnotherDilbert]

Spreadsheet: https://www.dropbox.com/s/tidv4xqelrw2byv/ MgT2 Ship Template.xlsx?dl=1
or: http://www.travellerrpg.com/CotI/Discuss/showthread.php?t=38848


The spreadsheet is Excel without any macros, it should work fine in LibreOffice.


The spreadsheet starts with a default ship:

Only change values in grey cells. Mainly we add values in the "Desired" column, we add the size or rating of the desired system, or if appropriate the number of desired fixed systems.

Tech advantages are selected in the "∆TL" column, if appropriate. Specify a numerical value between -2 to +3 (-0,5 for Budget). You will get as good as the TL will allow, so you can specify a large number to get as good as possible. Only a few advantages or disadvantaged are supported, they are applied automatically.

Generally what you get is limited by the selected TL, e.g if you ask for a J-6 drive at TL-12 you will get a J-3 drive. You can get a bigger drive by selecting a Prototype tech advantage. Example:

Specify a positive number in the "Arm Bulk" column to protect the system with Armoured Bulkheads.

 

[AnotherDilbert]

Let's build a small patrol ship:

We specify TL-12 in cell A1 and a military ship by specifying a positive number in cell A2. Military ships automatically use double occupancy, but that can be overridden in cell A3, 0 = Single occupance, 1 = Double occupancy.

We specify hull size in cell D4. Basically any hull size of 10 Dt and up is supported. If the hull is less than 100 Dt jump drive and staterooms are deselected, but that can be overridden.

Configuration is specified as a number between 1 - 7, corresponding to the list of configurations, 1 is Standard, 7 is Buffered Planetoid.

Hull strength is specified as a number: 1 = Light, 2 = Standard, 3 = Reinforced.

Armour is specified as a number, what you get is limited by TL. Specify e.g. 99 to get as good as possible.

We specify 400 Dt, Close Structure configuration, Standard hull strength, and maximum armour. The armour we actually get is shown in the "Rat" (for Rating) column.

Hull options are selected by specifying a positive number in the "Desired" column. Radiation Shielding is pre-selected for military ships. Stealth specified by 1 = Stealth, 2 = Superior Stealth.

We select Reflec coating and Repair Drones:

 

[AnotherDilbert]

Modules and Pods are selected by specifying a size and a how many we want. Modules are internal so consume space inside the ship, pods are external so require bigger drives. E.g.:

We specify neither modules nor pods in this example build.

 

[AnotherDilbert]

Drive sizes are based on the total size of the ship, both the hull and any externally carried pods, craft, cargo, or drop tanks. E.g.:

In this example the drives will be based on 420 Dt to suffice for the hull and a carried craft.

We will specify the carried craft later.

 

[AnotherDilbert]

We specify drives with a desired rating and number of desired tech advantages. Reduced Size or Energy Inefficient are applied. Specify a positive number in cell N32 to switch to Fuel Efficient jump drive. Specify "Stealth" in cell M32 to get a Stealth jump drive, if two tech advantages are available.

We specify J-3, M-6, Emergency Power system and two Dt battery to power the jump drive, all with maximum tech advantage:

The power plant is dimensioned to power all needed systems. Military ships do not provide full power to basic ship systems while in combat.

If tech advantages are specified for the power plant a Size Reduced plant of lower TL is used, since it is probably cheaper.

 

[AnotherDilbert]

Fuel capacity and purifier is preselected to provide one jump and four weeks endurance.

Jump fuel is by default in Fuel/Cargo containers. Fuel in Fuel/Cargo is deducted from needed fuel in regular tanks (row 40), so specify a lower number in cell D41 to get regular tanks instead.

Specify the purifier by the number of hours to refine jump fuel, or 0 for no purifier.

Drop tanks are specified by the number of J-1 to be supported in cell D39.

In cell E39 we specify how the tanks should be calculated, -1 we jump with the tanks, +1 we jump without the tanks, and 0 to specify partial tanks e.g. to be able to do J-2 on internal tankage and J-4 while keeping external tanks (thanks Chas for the idea).

Drop tank collars are included as needed, we can add collars for more tanks in cell D38.

Drop tanks will be included in Total Drive Capacity or not as appropriate.


In this example we keep default fuel:

 

[AnotherDilbert]

High Burn Thrusters or Reaction drives are specified by desired performance and hours of fuel. The maximum fuel efficiency is automatically applied. E.g.:

In this example we specify no Reaction Drive.

 

[AnotherDilbert]

The biggest appropriate bridge is included. Specify 0 in the "Desired" column to exclude that type of bridge. Specify a bigger number to get more bridges.

Detachable bridges and Dual cockpits can be selected in the B column.

If Holographic controls are selected (as it is by default) it is applied to all bridges.

A number of Sub-Command Centres and extra Sensor Workstations can be specified.

The minimum computer to manage the jump is selected, and a one size smaller backup. A different size computer can be specified in cell D56 in increments of 5 Bandwidth, e.g. specifying 7 would give you a Bandwidth 35 computer, TL permitting.


In this example we specify a detachable standard bridge with armoured bulkheads and the maximum computer available:

 

[AnotherDilbert]

Sensors are specified by a positive number in the "Desired" column. Bigger number gets you better systems, limited by TL.

We can get several identical systems by specifying a bigger number in the "#" column.


We specify the best sensors we can get and a backup sensor (without array):

Total sensor DM and ECM DM is calculated and shown in cells L1 and L2 (top right).


Since we seem to have run out of space (cell H2) we go back and reduce the jump drive to J-2, giving us space for weapons.

 

[AnotherDilbert]

Necessary regular staterooms for the crew and passengers are defaulted, together with 25% common areas and two escape capsules per stateroom.

Larger staterooms and barracks can be specified in the "Desired" column, such added accommodations reduce the needed regular staterooms.

Facilities such as briefing rooms and medical bays can be added as desired. The Medical Bay is dimensioned to accommodate a specified percentage of the crew simultaneously. Each 4 Dt med bay requires 1 Power, but that is not added automatically to avoid a circular reference, add it manually in cell J76.


We specify a High stateroom for the Captain and a medical bay (with power). An armoury is defaulted for military ships.

 

[AnotherDilbert]

All free space is dedicated to the cargo hold.

Ancillary systems are specified by desired Dtons, or size and number for cargo cranes and airlocks.

The drives will be enlarged to handle any external cargo.


We specify a small cargo airlock and a sinlge Dt of UNREP equipment:

 

[AnotherDilbert]

Weapons are specified by the number of desired systems and tech advantages. Turrets are triple by default but that can be changed in column F (up to quad).

Spinal mounts are specified by type and dice of damage. TL size reduction is automatically applied. Other tech advantages can be specified, but decreases the TL size reduction.

Tech advantages are assumed to be Accurate, Long Range, or something like that, except for missile launchers that use Reduced Size.

Magazine space for missiles is calculated and can be added in battery-rounds, default is 12 battery-rounds.

If you need more rows copy a row and insert rows as needed. If you need more types of mixed turrets copy the four rows and insert four new rows.


We specify a couple of particle barbettes, a laser turret for point defence (and to scare civilians), and a mixed turret with a laser (PD), missile rack (missile drones), and a sandcaster (jamming):

Launched missiles and torpedoes are totalled in cell B118.
Estimated point defence capacity (with very good gunners) against missiles and torpedoes is shown in cell B119.

 

[AnotherDilbert]

Carried craft can be specified by size and number for hangars (internal) and clamps (external). Drives will be resized for external craft.

If you need more rows copy an entire row and insert rows as needed.


We specify a 20 Dt small craft carried externally in a docking clamp:

 

[AnotherDilbert]

Software is specified by level or a large number for the best possible at this TL.

Specify level 1 to get Virtual Crew/Gunner/0.

Needed software (Jump Control) is defaulted, unless we have a Core computer that does not need it.


We specify a reasonably good set of software at high cost:

 

[AnotherDilbert]

Crew is defaulted, but can be changed as desired. Accommodations is resized as the crew is changed.


We add a sensor operator to get the most of the sensors:

 

[AnotherDilbert]

Passengers can be added by type.

An estimated economical break-down is shown for the ship in freight traffic.


We have no space left for any passengers, but we add a few Low to get some medical low berths:

Since the ship is not a freighter it would not be economical as a freighter...

 

[AnotherDilbert]

And we have a ship:

 

[steve98052]

. . . Military ships automatically use double occupancy, but that can be overridden in cell A3, 0 = Single occupance, 1 = Double occupancy. . . .

My understanding is that military and commercial both normally use single occupancy for officers, double for crew, and single or double for passengers at their option. Is that aupported?

 

[AnotherDilbert]

Officers and passengers are always given full staterooms.

With dual occupancy crew are given half staterooms (rounded up).


I may lean too much on CT, but in the Imperium passengers are given full staterooms. I haven't really considered anything else...

 

[HalC]

Just thought I'd mention that you're doing a nice job documenting how to use the spreadsheet. Always a good thing in my opinion. By chance, have you included the instructions you provide here, on the spreadsheet itself? Simply create a new tab, and then enter the text in the new tab and name the tab "Instructions".

I can't help but wonder what something like this written in VB.NET would look like.

And while I'm bringing THAT topic up...

What I would suggest anyone do, is pick up "teach yourself VB.NET". From this foundation, you can then start to teach yourself how to use VBA (Visual Basic for applications. Each copy of Excel has the ability to run VBA as code (it is what we would call macros). You could for example, set up drop down menus to select TL, hull size, jump/manuever/power parameters, and then make it do what is laborious in Excel more of a breeze.

The best part is this...

If you know what you want to do in VB.NET, then google how that same thing would be done in VBA. That's how I taught myself how to use VBA in the first place. I developed a spreadsheet for work at one point in time, where the user would fill in one per line - name, address, letter type, issue, issue amount - etc so that all they had to do was enter in say, 30 customers onto the spreadsheet, click a button on the spreadsheet, and it would create the letters in Microsoft Word as form letters. It is sort of like a mail merge, but allows you to track what letters you sent out on what day, along with creating 30 pages worth of form letters in 5 minutes, what used to take 2 hours to create by opening form letters by hand, changing the data, printing the data, and then opening up the next form for the next customer etc.

In short - VBA for gaming applications can be a really NICE thing. Heck, you could create an app on one spreadsheet using VBA, and print the results to another spreadsheet, format it the way you want it with column widths, background color, etc - and save that to a new excel sheet that is macro free.

All it takes is the willingness to learn VB.NET first (with but a single book no less), and then using Google to help fill in your grey areas of lack of specific VBA knowledge.

And yes, if you have Outlook, you can even use VBA to create emails, fill in the recipient names, and actively send emails - all while using VBA with Excel (that's a bit tricky, but if you're stubborn about it, Google is your friend!)