ORIGINAL: erstad
Here's a little tool I put together to help model the number of cargo ships and TFs I need servicing a location. There are quite a few variables...
Basically, you fill in the characteristics of the source port (size, nav spt, in-hex resources[IN CENTERS], in-hex LI, and non-inhex resources[IN TOTAL POINTS] being transhipped ) and destination port (same except for LI), and distance between the two (round trip). You also select the size cargo hold you wlll be using and the speed. Lastly, you tell it how many days/turn are being run. All of these are in light grey.
The spreadsheet then figures out how long each TF takes to load, move to the destination, unload, and return, snapped to the turn cycle. Based on that, you find how many ships are needed. Further, based on the port limits it determines the maximum number of ships that can be in one TF, and thus how many TFs are needed (ships need to be evenly split between the TFs)
It errs on the side of using the merchies efficiently versus transporting all resources, so over time some excess will accumulate. It also ignores things like refueling ops, but that doesn't interfere with keeping the transports busy.
Two limits: It doesn't account for possible other traffic at the destination; and it doesn't determine if the number of TFs is viable (no two TFs can be loading on the same day)
There are two examples in the file, assuming two day turns. One is a Toyohara to Ominato run. Here only the resources generated in Toyohara are being transmitted. You need three TFs with 12 total ships (using the Aden class for this exercise). Then, I did a Fusan to Shimoneski run, assuming I wanted to get 22,500 excess resources out of China through this one port. Here, you need 3 TFs of 28 total ships. (This is an example of where it may not be a practical number of TFs, the loading is around 3 days (of 6 total) so more than one would be trying to load at the same time - the spreadsheet doesn't understand this)
In addition to figuring out # of ships/TFs, one can also do some what-if analysis of different routes (is it better to go a little further to hit that big port?) and port/naval support expansions. There's also some trades in terms of ship class; port size, naval support, and presence of resource centers all affect the item load rate and daily load rate in non-proportional ways.
There is both an OpenOffice and Excel version, I only tested the OpenOffice version.
Feedback/bug reports welcomed
The formulas are documented on the spreadsheet
Spreadsheet is in this thread
tm.asp?m=2198983&mpage=1&key=?