MgT2 Ship Design Speadsheet Walk-through

Discuss the Traveller RPG and its many settings
AnotherDilbert
Cosmic Mongoose
Posts: 3104
Joined: Wed Dec 23, 2015 2:49 pm
Location: Sweden

MgT2 Ship Design Speadsheet Walk-through

Postby AnotherDilbert » Fri Aug 24, 2018 4:37 pm

Spreadsheet: https://www.dropbox.com/s/tidv4xqelrw2b ... .xlsx?dl=1
or: http://www.travellerrpg.com/CotI/Discus ... hp?t=38848


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


The spreadsheet starts with a default ship:
Image

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:
Image

Specify a positive number in the "Arm Bulk" column to protect the system with Armoured Bulkheads.
Last edited by AnotherDilbert on Sun Sep 09, 2018 8:58 am, edited 1 time in total.
AnotherDilbert
Cosmic Mongoose
Posts: 3104
Joined: Wed Dec 23, 2015 2:49 pm
Location: Sweden

Re: MgT2 Ship Design Speadsheet Walk-through

Postby AnotherDilbert » Fri Aug 24, 2018 5:10 pm

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.

Image


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.

Image


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:

Image
AnotherDilbert
Cosmic Mongoose
Posts: 3104
Joined: Wed Dec 23, 2015 2:49 pm
Location: Sweden

Re: MgT2 Ship Design Speadsheet Walk-through

Postby AnotherDilbert » Fri Aug 24, 2018 5:18 pm

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.:

Image


We specify neither modules nor pods in this example build.
AnotherDilbert
Cosmic Mongoose
Posts: 3104
Joined: Wed Dec 23, 2015 2:49 pm
Location: Sweden

Re: MgT2 Ship Design Speadsheet Walk-through

Postby AnotherDilbert » Fri Aug 24, 2018 5:28 pm

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.:

Image

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
Cosmic Mongoose
Posts: 3104
Joined: Wed Dec 23, 2015 2:49 pm
Location: Sweden

Re: MgT2 Ship Design Speadsheet Walk-through

Postby AnotherDilbert » Fri Aug 24, 2018 5:46 pm

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:

Image

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
Cosmic Mongoose
Posts: 3104
Joined: Wed Dec 23, 2015 2:49 pm
Location: Sweden

Re: MgT2 Ship Design Speadsheet Walk-through

Postby AnotherDilbert » Fri Aug 24, 2018 6:14 pm

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:
Image
AnotherDilbert
Cosmic Mongoose
Posts: 3104
Joined: Wed Dec 23, 2015 2:49 pm
Location: Sweden

Re: MgT2 Ship Design Speadsheet Walk-through

Postby AnotherDilbert » Fri Aug 24, 2018 6:19 pm

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


In this example we specify no Reaction Drive.
AnotherDilbert
Cosmic Mongoose
Posts: 3104
Joined: Wed Dec 23, 2015 2:49 pm
Location: Sweden

Re: MgT2 Ship Design Speadsheet Walk-through

Postby AnotherDilbert » Fri Aug 24, 2018 6:38 pm

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:
Image
AnotherDilbert
Cosmic Mongoose
Posts: 3104
Joined: Wed Dec 23, 2015 2:49 pm
Location: Sweden

Re: MgT2 Ship Design Speadsheet Walk-through

Postby AnotherDilbert » Fri Aug 24, 2018 6:55 pm

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):
Image

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.

Image
AnotherDilbert
Cosmic Mongoose
Posts: 3104
Joined: Wed Dec 23, 2015 2:49 pm
Location: Sweden

Re: MgT2 Ship Design Speadsheet Walk-through

Postby AnotherDilbert » Fri Aug 24, 2018 7:22 pm

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.

Image
AnotherDilbert
Cosmic Mongoose
Posts: 3104
Joined: Wed Dec 23, 2015 2:49 pm
Location: Sweden

Re: MgT2 Ship Design Speadsheet Walk-through

Postby AnotherDilbert » Fri Aug 24, 2018 7:32 pm

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:

Image
AnotherDilbert
Cosmic Mongoose
Posts: 3104
Joined: Wed Dec 23, 2015 2:49 pm
Location: Sweden

Re: MgT2 Ship Design Speadsheet Walk-through

Postby AnotherDilbert » Fri Aug 24, 2018 8:09 pm

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):

Image

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
Cosmic Mongoose
Posts: 3104
Joined: Wed Dec 23, 2015 2:49 pm
Location: Sweden

Re: MgT2 Ship Design Speadsheet Walk-through

Postby AnotherDilbert » Fri Aug 24, 2018 8:21 pm

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:

Image
AnotherDilbert
Cosmic Mongoose
Posts: 3104
Joined: Wed Dec 23, 2015 2:49 pm
Location: Sweden

Re: MgT2 Ship Design Speadsheet Walk-through

Postby AnotherDilbert » Fri Aug 24, 2018 8:27 pm

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:

Image
AnotherDilbert
Cosmic Mongoose
Posts: 3104
Joined: Wed Dec 23, 2015 2:49 pm
Location: Sweden

Re: MgT2 Ship Design Speadsheet Walk-through

Postby AnotherDilbert » Fri Aug 24, 2018 8:31 pm

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:

Image
AnotherDilbert
Cosmic Mongoose
Posts: 3104
Joined: Wed Dec 23, 2015 2:49 pm
Location: Sweden

Re: MgT2 Ship Design Speadsheet Walk-through

Postby AnotherDilbert » Fri Aug 24, 2018 8:37 pm

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:

Image

Since the ship is not a freighter it would not be economical as a freighter...
AnotherDilbert
Cosmic Mongoose
Posts: 3104
Joined: Wed Dec 23, 2015 2:49 pm
Location: Sweden

Re: MgT2 Ship Design Speadsheet Walk-through

Postby AnotherDilbert » Fri Aug 24, 2018 8:41 pm

And we have a ship:

Image

Image
steve98052
Greater Spotted Mongoose
Posts: 831
Joined: Tue Oct 01, 2013 6:13 am
Location: near Seattle

Re: MgT2 Ship Design Speadsheet Walk-through

Postby steve98052 » Sat Aug 25, 2018 7:36 pm

AnotherDilbert wrote:
Fri Aug 24, 2018 5:10 pm
. . . 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
Cosmic Mongoose
Posts: 3104
Joined: Wed Dec 23, 2015 2:49 pm
Location: Sweden

Re: MgT2 Ship Design Speadsheet Walk-through

Postby AnotherDilbert » Sat Aug 25, 2018 7:52 pm

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
Weasel
Posts: 46
Joined: Wed Jan 10, 2018 3:42 pm

Re: MgT2 Ship Design Speadsheet Walk-through

Postby HalC » Sat Aug 25, 2018 11:48 pm

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!)

Who is online

Users browsing this forum: No registered users and 22 guests