As the resident member of the spreadsheet brigade I have learned the following things from this thread:
1) The port capacity number produced by TCS is a theoretical maximum. For wargame scenarios, like the Islands campaigns, you will have your starports working at maximum theoretical capacity for obvious reasons. For a place like the Imperium the reality of economics and lack of obvious threats means the port build capacity will be smaller, in some cases much smaller, than the theoretical. To be useful the spreadsheet should generate a more realistic number based upon economics, not just theoretical maximums.
2) It is only implied, and stated partly in GT:Starports, that Imperial naval bases are the place where the Imperial navy ships are build, maintained, and repaired. This has several implications:
A) The collection of Naval bases should have a "Port capacity" large enough to support the Imperial navy in the area. Generally this is a subsector. Based upon calculations done by Chris Thrash and Hans the Imperial subsector navy (Numbered Fleet) is between 10 and 30 million Dtons.
B) The colonial/reserve fleet is about the same size, but must be serviced at the existing ports in the subsector.
C) The merchant fleet, based upon the volume of existing trade in the Subsector, must also be serviced in the existing ports in the Subsector.
D) B + C have two outcomes. The Port capacity of the A ports in the subsector need to support the replacement building of the merchant and colonial fleets. The Port Capacity of the A, B, and C ports needs to support the maintenance and repair capacity of the fleets. These are probably distributed according to population and or trade flow. The economic breakdown for each sector (e.g.
Daibei Sector) has a "Port Size" calculated based upon the cargo and passenger traffic. If you want to know where the merchant are going, start with the big ports.
3) The gap between actual and theoretical can not be filled in short periods of time. TCS states it takes 548 weeks (10.5 years) for a captured starport to begin building ships. Pocket Empires gives port upgrade times of 10 to 30 years. Meaning the port build capacity can't be brought up to maximums before the war is over.
4) The number of Imperial nobility is under 6,000. These people are who decides where the naval bases are built and what size. This number is too small to apply a statistical generalization. There are also enough exceptions (e.g. Corridor Sector fleets) is the best the spreadsheet can do is provide a generalization at the subsector level: The <N> naval bases support a fleet of <X> MdTons, requiring <Xc> dTons of build capacity. The Colonial fleet is <Y> MdTons, requiring <Yc> dTons of capacity. And the Merchant fleet is <Z> MdTons requiring <Zc> dTons of capacity.