OK, so here are the numbers I usually compute when comparing vehicles:

(1) Operating profit for hauling one unit one km on the target run.

For passengers with full loads, this is [(running cost) / (capacity)] - [revenue per unit] , This works because revenue and running cost are both per km. (It doesn't take into account indirect routing, which is relevant in experimental, but that's OK.)

For freight with full loads, this is [2 * (running cost) / (capacity)] - [revenue per unit], since the freight routes *usually* run empty half the time (and if you can get a two-way run it *should* be super profitable).

For balancing, you have to leave a little leeway on the passengers and assume less than 100% loading both ways. I'm not sure what's good to assume, probably somewhere in the region of 90% much of the time, and probably depends on equipment. With big trains you want to leave some leeway for a route with a central trunk section which is 100% full and outer "branches" which are maybe only 75% full, so probably less than 90%. With equipment designed for "point-to-point" traffic you can keep the percentage higher but you have to account for asymmetrical trips, so still not 100%.

So much for per-unit-hauled costs.

-- Note that revenue per unit can simply be looked up on a chart in standard, but is based on the *actual travel speed* in experimental. So in experimental one needs to figure out what the average speed will be on an "appropriate route". An "appropriate route" for a mainline train is one which is pretty straight and pretty flat, typically two curves between stations (= shortest possible route in simutrans) plus some serious slowdowns in the station areas. (And with station spacing appropriate to the chosen train type.) Similarly for lorries. An "appropriate route" for a city bus or tram is full of sharp curves and short distances between stations. An "appropriate route" for a ship is probably terribly indirect. An "appropriate route" for a plane is a straight shot.

(2) Max haulable volume per month on the track.

This is how I figure out how many convois it takes to service a factory. It can also be used to decide what factory production volumes should be! Assume freight here:

[length of route in tiles] * 2 is the number of tiles it takes for a convoi to traverse a full round trip.

So [length of route in tiles] * 2 / [convoi actual speed in tiles/month] is the number of months it takes a convoi to traverse a full round trip.

[factory units per month] / [units per convoi] is the number of convois which need to arrive at the factory per month.

So [factory units per month] * [length of route in tiles] * 2 / ([units per convoi] * [convoi speed in tiles per month] is the number of convois needed for the route.

(3) Congestion check.

The number from (2) can be too many to actually support. This allows me to figure out whether the tracks or roads or stations will get overcrowded, and should be used as a sanity check when balancing.

[length of track in tiles] / [tiles per convoi -- 1 for trucks, lots for trains] = maximum number of convoys on a two-way route (you can really only manage a lorry every other tile or a train every other block before everything slows to a crawl).

(4) For locomotives only -- cost per kw.

[running cost per km] / [kw] gives a good sense of which locomotives are the best deals for hauling a bunch of waggons. In pak64, one locomotive is head and shoulders cheaper than the rest and ends up being the best choice nearly all the time. (This is not terribly desirable). If a locomotive has a lower top speed, it should have a cheaper cost per kw, otherwise it will never be appropriate to use. (If it's supposed to be obsolete, of course, this is just fine, but if it's supposed to be competitive, it matters.) If it has a higher top speed it can be more expensive per kw, because higher top speeds lead to higher revenue.

(I'm not sure what the effect of "gear" is because I haven't figured out how it works. It *should* give higher accelerations. In experimental, but not in 'standard', higher accelerations would lead to higher average speeds on runs with many stops and higher revenue, too; so locomotives with higher acceleration could also be allowed to be more expensive per kw.)

(5) Figuring monthly maintenance costs into the entire scheme.

OK, so I have two hypothetical schemes, one with rails and one with roads (for instance), or one with fast trains on fast track and one with slow trains on slow track.

I can figure costs per month on each one pretty easily; x units of track * cost of track + x units of station * cost of station. In experimental, also have to add the monthly convoi costs.

Now, in *standard* you can totally cheat by buying very fast vehicles and getting the speed bonus for them and then running them on dirt roads! I do this *all the time*. The only reason you'd need to upgrade the track is if you needed greater volume per month. So to balance it you need *two* speed columns in your spreadsheet, one for revenue and one for volume. :-P

In "experimental" this is more realistic, and easier to work out in a spreadsheet, because the "average speed" for purposes of tiles per month is the same speed used to determine the revenue.

Note that the length of the track figures into the computation up in (2) and (3) as well. The cost of the track is connected to the average speed of the vehicles (used in 2, 3, and in 1 for experimental), and in fact is largely determined by it (except for bridges and tunnels, and you should assume none for balancing purposes). The track speed should of course be equal to or higher than vehicle maximum speed. In experimental the "weight limits" also determine the choice of track, but again the choice of track is driven by the choice of vehicle. Expected average speed should be a bit lower than track/vehicle maximum speed. The percentage to use should be dependent on the station spacing and percentage of "in-city" running mostly, since those are the main slowdowns, so it should be different for short-distance and long-distance vehicles. (Assume minimal curves outside cities, because we want to make the route profitable *if it's built efficiently*, not if it's a "bad route".) However, note that in-city running for road vehicles is "free".

The length of the station is simply determined by the length of the train, ideally, and you need only one station at each end for a freight run (that's good enough for balancing purposes; complex stations are used generally when multiple lines interact, otherwise a "staging line" of track or road is used). Passenger runs also need a station large enough to hold slightly over the number of passengers in a single convoi -- at each end. So the station costs are determined by the vehicles, largely.

OK, so how do I put the numbers together?

[operating profit for hauling one unit one km (from 1)] * [length of route in km] = operating profit for hauling one unit entire route (and returning empty, if freight, and with allowance for non-full passenger services)

[operating profit for hauling one unit entire route] * [factory units per month] = operating profit per month

[operating profit per month] - [maintenance cost per month] = true monthly profit (not counting depreciation).

[true monthly profit]/[number of convois (from 2)] = profit per convoi

This last one is the number which should be compared to the capital costs of the vehicles. (Treating track and station costs as "input" numbers; whenever they change, you have to recompute the lot.)

Finally, if you do make a spreadsheet, I'd suggest putting it in SVN so other people can poke around at it. :-)