Resource hauling spreadsheet

[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=&#2198983

[Blind Sniper]

Whoops - I can't upload a zip file? I thought I have done that before? Anyone have a trick to upload a zip and/or willing to host the file?

Try to rename file extension from .zip to .txt

[erstad]

Based on a question someone e-mailed me (not sure what his forum name is to give him credit), I've been looking a little closer at the load rates. In addition to the manual error about resource center effects I posted elsewhere, I realize there are three additional items where I'm not certain I'm reading the manual correctly and I'm not certain the manual is right. I can run some tests, but won't have time till the weekend so I thought I'd post and see if anyone has already checked these out.

1) 6.3.3.2.5 says a naval support squad adds 10 points to the "Troop and Cargo load ability". I've been assuming this includes resources; however, the table in 6.3.3.2.4 has resources in a separate column from cargo so I'm now wondering if it really only means cargo-cargo and not resources.

2) 6.3.3.2.5 doesn't say, but I've been assuming that since the table is "per phase" for the unit load rate, so is the +10 adder.

3) The biggie - 6.3.3.2.5 is clear that this is added to the load rate, but not the daily rate. The load rate is "per ship" (until you hit the daily rate). I had assumed that meant each ship got +10 (again, limited by the daily rate) but I'm now thinking that gets too big for normal levels of naval support. The naval support adder could be many times the base load rate; maybe reasonable for a ship but for a bunch of ships? As a pure speculation, I'm wondering if indeed the +10 is to the item load rate but instead of applying to each ship individually it is the total item rate boost that all ships divvy up.

Note that if you 0 the naval support in the spreadheet you bypass all these questions and get a somewhat conservative answer.

[Local Yokel]

I'm still coming to grips with the complexities of your spreadsheet, but thank you anyway for uploading it.

You raise an interesting question about how the benefits of naval support get applied. Not sure whether it adds anything, but have you taken into account 9.3.3.2? -

"The cargo handling and fueling limits are Daily values, the others limits are constant. Rearming and Loading Troops consumes cargo capacity, as does loading supplies, resources, fuel in barrels, and aircraft. Refueling and loading ships with oil or fuel (other than in barrels) consumes fueling capacity. Once the daily cargo/fuel limits are reached, no more cargo handling/fueling can take place that day. Cargo limit can be increased by Naval Support (extra stevedoring) but not fuel limits."

The distinction seems to between liquid and solid loads, on the basis that any number of stevedores cannot increase the flow rate through a loading hose. OTOH you rightly note the difference between cargo and resource loading rates (resource loading rate double that for cargo) - I think that was intended to reflect a difference between commodities capable of loading in bulk via conveyor or the like (e.g. iron ore, bauxite) and 'general' cargo loaded by slings, nets, pallets and the like. Skilled stevedores and extra labour could accelerate general cargo loading rates to a degree, but, as with loading liquids, they can't make conveyors go faster. So perhaps naval support doesn't assist loading of resources.

In any case I'm inclined to discount the presence of naval support in schemes for managing the shipping of Japan's resources, as I think the precious naval support units are probably better employed in forward areas where they will compensate for the absence of large ports - this certainly simplifies calculations.

I assume that naval support squads don't have a per-phase ration of ops points, and instead confer their benefit upon all base activities to which they can make a contribution. My wife would say they must consist of women - otherwise they couldn't multi-task in this way!