CT HG'80 Ship Design Sheet Walk-through

[AnotherDilbert]

Spreadsheet: http://www.travellerrpg.com/CotI/Discuss/showpost.php?p=591754&postcount=7


[To Admin: Sorry, lots of external pictures linked, but I don't really see how I could make this otherwise. At least the spreadsheet contains an BBCode table so that finished ships can be posted.]


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" columns, we add number of systems in the "#" column and the size or rating of the desired system in the "Desired" column.

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.

Errors are marked with red colour, example:

This hull size requires a bigger computer than is available at the selected TL.

 

[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 select ship type codes in the tables in cells A4 & A5, in this case Corvette and Escort. The USP is updated automatically.

We specify hull size in cell D7. Basically any hull size of 5 Dt and up is supported. If the hull is less than 100 Dt jump drive and staterooms are deselected.

Configuration is specified as a number between 1 - 9, corresponding to the list of configurations, 1 is Standard, 9 is Buffered Planetoid, or in the table in cell B8. Cell B8 overrides the number.

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, Needle configuration, and Armour 4. The armour we actually get is shown in the "USP" column.

 

[AnotherDilbert]

Drive sizes are based on the total size of the ship, both the hull and drop tanks. E.g.:

To get a drop tank with enough fuel for a J-2 (keeping the tank) specify 20%.

In this example we will not use any drop tanks.


We specify drives with the desired rating, and the desired Agility. The power plant is dimensioned automatically. Specify negative numbers in the "#" column to get LBB2 lettered drives.

We specify J-2, M-5, and Agility-4, and LBB2 drives for M-drive and P-plant:

Minimum fuel is automatically added. Jump fuel is added as demountable tanks in the cargo hold, that can be turned off in row 31.

A fuel purifier is automatically added, that can be turned off in cell C21.

 

[AnotherDilbert]

A bridge and a computer is included automatically. A civilian ship includes the smallest computer possible, a military ship includes the maximum computer available.

The desired size of the computer is specified in cell D24, and bis/fib in cell E24, -1 = bis, 0 = normal, +1 = fib.

An appropriate computer is selected based on other systems. Selected computer is shown in cell B24.

We select a fib computer by specifying 1 in cell E24.

Accommodations are sized for the crew and passengers (specified below). Minimum number of staterooms can be specified in cells D26 to D28.

 

[AnotherDilbert]

Remaining space is added to the cargo hold.

Demountable tanks are in addition to the shown cargo hold, so currently we have 172 Dt cargo = 80 Dt tanks + 92 Dt free.

 

[AnotherDilbert]

Weapons:

A spinal can be specified in the table in cell D35.

Bays are specified by how many and type (+size).

Turret batteries are specified by type of weapon, number of batteries, weapons per turret, and turrets per batteries. Factor is calculated.

Example:

This shows 2 batteries of triple turrets, 4 turrets in each battery, giving factor-4. This is too many turrets for this ship so the Hardpoint column header shows red. We will not use those batteries in this ship.


Small ships can use mixed turrets, with several different weapon types in the same turret. Mixed turrets are specified by number of turrets and three rows of weapons where we can specify type of weapons and number of weapons in each turret. Number of weapons in each turret is shown in cell B46 as number of open slots, full, or red for too many weapons. The sheet keeps track of maximum size turret for selected weapon types.

Example, two Missile, Laser, and Sand turrets:

Fixed Mounts (Solomani) are specified like turrets.


Empty hardpoints with space for fire control are reserved, half available for civilians, all available for military. They can be removed by specifying 0 in cell C53.

If you need more weapon rows, copy an existing row and insert a new row. Copy the entire row, not just a few cells. Even if you want a new empty row, copy an existing row.

If you want a new mixed turrets, copy a block of four entire rows and insert four new rows:

Copy, then Insert:

In LibreOffice you have to Paste the four rows manually.


You can delete excess weapon rows (not in LibreOffice 6.0).


If batteries of the same type with different factors are selected everything but the highest factor is marked in red and not included in the USP. Example:

We add four mixed turrets to the ship, two Missile-Missile-Sand, one Missile-Pulse-Sand, and one Fusion-Missile:

Note that number of batteries and factor is calculated and shown in the USP in the header.
Note that we use all the power available from the PP-6, the maximum possible with LBB2 drives.

 

[AnotherDilbert]

Screens:

Screens are specified by number of systems and desired factor. The maximum factor is preselected.

Example:

We don't include any screens in this small, power-starved ship:

 

[AnotherDilbert]

Carried smallcraft and vehicles are specified by standard type (or custom size and cost) and number of craft. Size and Cost are given automatically for standard craft, if custom size and cost are specified, standard craft can no longer be specified.

More rows can be inserted as needed.

We specify a Launch and a GCarrier:

 

[AnotherDilbert]

We can specify passengers, extra staterooms, and troops below the calculated crew.

We specify a single Mid passenger, 3 low (for medical use), and 8 troops:

 

[AnotherDilbert]

Just above the Crew block the ship's systems are added together:

Nominal Cost is all raw sum of the cost of the included systems.
Class Cost is 20% for the first ship plus 1% architect's fee.
Ship Cost is the rebated 80% cost for subsequent ships.

Cost of carried craft is not rebated, nor included in the architect's fee.

 

[AnotherDilbert]

At the bottom of the sheet an economic estimate is calculated:

In cell D86 we can select "Custom" to use the Nominal cost, or "Standard" to use the rebated Ship Cost, as a basis for Down Payment and Mortgage.

In cell I88 we can select the average percent of cargo and passengers to use for the estimate.


Since this is a military ship with very little cargo capacity it is obviously not profitable as a freighter.

 

[AnotherDilbert]

The last rows of the sheet contains a BBCode table representing the ship:

The BBCode is a little too large to post on CotI, so we have to delete a few rows. Do not delete rows above Cargo (row 30 or less). E.g. we can delete the rows for spinals and bays:

Do not delete rows in LibreOffice 6.0 (too many bugs). You can erase cells in column S instead, each row is summarised as a BBCode row in column S, so each cell erased deletes one row from the BBCode. Do not touch column T.


The BBCode is now small enough to post:

Just copy the framed cell marked BBCode and paste in a new post. Unfortunately Excel adds quotation marks around the BBCode, you may want to delete the first and last character in the new post.

 

[AnotherDilbert]

LE-41256F2-430000-14001-0        MCr 375         400 Dton
bear        3     11  6                           Crew=18
batt        3     11  6                             TL=12
  Pass=1 Troops=8 Low=3 Cargo=25 Fuel=140 EP=24 Agility=4

TL=12						25,5	374,95	0
Dual		#	Desired		USP	Dton	Cost	EP
Hull	Streamlined		400		4	400
Configuration	Needle/Wedge		1		1		48
Scoops			1				0,4
Armour	4		4		4	40	28
Drop Tanks	0		0
Total tonnage	400
Jump drive		1	2		2	12	48
Manœuvre D	K	-1	5		5	19	40
Agility	4		4					-16
Power Plant	M	-1	6		6	37	96	24
Capacitor		0	0
Fuel, #J, #weeks		2	4			60
Purifier		1				6	0,03
Bridge		1				20	2
Computer	m/6fib	1	9	1	F	14	83	-5
Staterooms		7	0			28	3,5
Staterooms, Half		12	0			24	3
Low Berths		3	0			1,5	0,15
Cargo						25,5
Demountable Tanks		1	2			80	0,08
Collapsible Tanks		0	0
	Wpn Type	#	Wpn/Tu	Tu/B
Turret	Missile	0	3	4
Turret	Beam	0	3	0
Turret	Sand	0	3	0
Fixed Mount	Fusion	0	2	0
Mixed Turret	0	2				2
____Weapon	Missile	2			1		3
____Weapon	Pulse	0
____Weapon	Sand	1			3		0,5
Mixed Turret	0	1				1
____Weapon	Missile	1			1		0,75
____Weapon	Pulse	1			1		0,5	-1
____Weapon	Sand	1			3		0,25
Mixed Turret	0	1				2
____Weapon	Missile	1			1		0,75
____Weapon	Fusion	1			4		2	-2
____Weapon	Sand	0
Fixed Mount	2	0	DM -2
____Weapon	Missile	0
____Weapon	Sand	0
Empty hardpoint		0
Nuclear Damper		0	1		0
Meson Screen		0	1		0
Black Globe		0	0		0
	Type	#	Size	Cost
Carried Craft	Launch	1	20	14		20	14,04
		0	0	0
Launch Tube		0	0
Vehicles	GCarrier	1	8	1		8	1
		0	0	0
Nominal Cost	374,95				Sum:	25,5	374,95	0
Class Cost	75,59				Valid	≥0	≥0	≥0
Ship Cost	302,96
Crew			Bridge	Eng	Gunn	Flight	Service	Marine
18			2	2	4	1	1	8
						Extra
Passengers			High	Mid	Low	Staterooms	Marines	#FrozenW
			0	1	3	0	8	0
Estimated Economy of Ship			Custom
Ship price			Down Payment			Monthly Mortgage		Avg Filled Capacity
375		kCr	78739,5		kCr	1562,3		80%
Expenses per jump						Revenue
Bank		Cr	749900			High	0
Fuel		Cr	14000			Middle	6400
Life Support		Cr	25900			Low	2400
Salaries		Cr	23520			Cargo	20000
Maintenance		Cr	14998
Berthing		Cr	400
Summa		kCr	829			kCr	28,8
					Income	kCr -800
					Yield	-25,4%

 

[AnotherDilbert]

And the spreadsheet should look like:

 

[AnotherDilbert]

Unless we have added or deleted too many rows, the spreadsheet should print decently on one A4 page with the economic estimate on another page. Just print the first page to get the design on one page: