Spreadsheet that compares occupation productivity

E

Elliot

Warlord
Joined
Jul 30, 2008
Messages
267
I am building a spreadsheet to compare the profitability of different means of producing additional colonists. Should I use my colonists as farmers to create additional colonists via accumulating 200 food or should I use my colonists to produce goods and raw materials to be sold in Europe and purchase new colonists? How does a tax rate of 50% affect this? This is the kind of question I want to answer in my spreadsheet.

The spreadsheet will be able to compare different occupations by converting every colonist's productivity into gold produced per turn. Raw materials(cotton, tobacco, fur, sugar) and goods producing occupations(cigars, wool, etc.) are easy(at first glance) to convert into this figure. Food, on the other hand, seems different. But food can be converted into gold by seeing that 200 food is needed to create a free colonist. In AODII a free colonist can be bought in Europe for 750 gold. Therefore each food is worth 3.75 gold at game start.


Calculations in the spoiler
Spoiler :
I said that raw materials production and goods producing occupations' profitability were easy to calculate at first glance. However, goods production profitability can get complicated.

First, my formula for determining raw materials profitability is:

Sale Price of Raw Material in Europe * Quantity being produced by unit[1-Tax rate] - food upkeep of unit

Example: A free colonist working a farmed plains tile produces 4 tobacco per turn. The price of tobacco in Europe is 5 and the tax rate is 25%. The game is played on normal speed and a free colonist costs 750 in Europe so the upkeep cost of a unit is 7.5. The colonist will produce a total of 7.5 gold in profit per turn. (4*5)(1-.25)-7.5


My formula for determining goods production profitability is:

Sale price of finished good in Europe*Quantity of finished good [1-Tax rate] - food upkeep of unit - opportunity cost of selling the raw material

How do I model the opportunity cost of selling the raw material?

Price Raw Material* Quantity needed of Raw Material for goods production[1-tax] - average food upkeep for raw materials gatherers to gather enough raw material to produce good

Wow thats a mouthfull! Let met talk about that last item in more detail.

Go to the f5 screen in civ4Col and see how many people you have harvesting tobacco. Then goto the f1 screen and look at the production tab. Add up the green numbers and look at the red number colonies to see if there is any production happening there. Now you know how much tobacco you produce per turn and how many people you have harvesting tobacco. Your tobacco production per turn divided by the number of tobacco harvesters is "average food upkeep per tobacco harvester". Now we can calculate the mouthfull from above.

Average food upkeep for raw materials gatherers to gather enough raw material to produce good = cost of colonist in Europe/ food needed to grow colonist * how many cigars your production building makes per colonist / Average food upkeep per tobacco harvester

For example: I'm playing a game of 1.10 AODII on normal speed(food upkeep of a unit is 7.5). On the f5 screen I have 3 units working tobacco. I produce a total of 60 tobacco per turn. (Note: I don't know how rebel sentiment affects the analysis yet.)

For a cigar producing building that makes 6 cigars a turn, the "average food upkeep for raw materials gatherers to gather enough raw material to produce good" is:

(7.5 * 6/ 20) =2.25

Thats a lot of work for such a small number! Well, those 3 workers were expert tobacco planters working specials and I had 4-5 cities settled on tobacco bringing in 3-4 tobacco a turn each! If I only had free colonists working farmed grassland tiles for tobacco the figure would be 7.5 * 6/4 or 11.25.

Now I'm going to do an example of how profitable cigar production is:

7.5 is the food upkeep cost of a colonist
Europe price of cigars is 10
Europe price of tobacco is 4
tax rate is 25%
You have a normal citizen working a building that produces 6 cigars per turn
The average production of a tobacco farmer is 7

10*6[1-.25] - 7.5 - 4*6[1-.25] - 7.5*(6/7) =13.07

A free citizen would make 13.07 gold per turn making cigars above his alternative of selling raw tobacco.

Now for some comparisons

The same colonist could work producing 4 raw tobacco a turn and make:

4*6[1-.25] - 7.5 = 4.5 gold per turn

If you were to farm a 4 food square:

3.75 per food * 4 - 7.5 = 7.5 gold per turn.

You're better off farming a 4 food tile than producing raw tobacco and selling it in Europe but manufacturing it into cigars is most productive.

Factories, production buildings that give you 50% more production without costing raw materials, are amazingly profitable!

Our colonist from above working in a factory instead would earn:

10*6[1-.25] - 7.5 - 4*6[1-.25] - 7.5*(6/7) + 10*3[1-.25] = 35.57

Thats 2.7x as profitable as the 6 production building!


Constructive criticism is very welcome!
 

Attachments

  • Civ4ColAODII Analysis.zip
    10.2 KB · Views: 110
Sounds like an awesome idea! I'll have to check this out tonight at home. :)
 
I worked on it a little more and added Ranching, Guns production, changed some formatting and added some other stuff.

Short answer, don't ranch horses to sell in Europe!
 

Attachments

  • Civ4ColAODII Analysis 1.01.zip
    14.1 KB · Views: 107
This is a cool idea... I haven't had the time to check it out yet, but I'm going to assume that at a moderately high tax rate, nothing has a better yield than a farmer. This is how the game play goes.

This is a big thing that the game lost when they took out the customs house. Right now, the only feasible strategy is just developing an economy based on food and guns (and horses) and pumping out soldiers. After a while it seems there's almost no point in producing any commodities. Your first 4 cities or so should be trade based, after that they either make food or they make tools and guns. The customs house let you build a real economy as a viable strategy for winning on the harder levels.
 
craigdh said:
This is a cool idea... I haven't had the time to check it out yet, but I'm going to assume that at a moderately high tax rate, nothing has a better yield than a farmer. This is how the game play goes.
Click to expand...

This is true.

At a tax rate of 50%, normal speed, 750 colonist cost, manufacturing cigars with your average tobacco harvester producing 10 tobacco (price of tobacco set to zero to make comparison to farming clear), a free colonist working as a cigar maker in a 6 production building makes ~12 gold per turn when the price of cigars is 8(add 3 or subtract 3 for each change in price). A free colonist farming working a 4 food tile makes 7.5 gold per turn.

For specialists, a master tobacconist under the same circumstances as above makes 31.50 gold per turn while the expert farmer working a 4 food tile makes 18.75 per turn.


If the tax rate is 75% instead:

Free colonist making tobacco in a 6 production building: 0 gold per turn!(Factory: 6 gpt)
Master tobacconist making tobacco in 6 production building: 7.5 gold per turn(factory: 19.5 gpt)

Free colonist farming a 4 production tile: 7.5
Expert Farmer farming a 4 production tile: 18.75

~~

If your goal is additional colonists once the tax rate hits ~75%, go for farmers even if you have factories to work.

This does make the game play bland, but who needs more colonists at this point in the game? You should be making guns and horses(well, buying them) so that you can prepare for independence.
 
Hmmm I'm not sure if I agree with your analysis entirely. Farmer is far more productive at less than a 75% tax rate. I think an error is with the output of a free colonist on a 4 yield farm patch... you're putting it at 7.5, which means you're applying the 50% tax rate... the tax rate doesn't apply to the farmer because tax doesn't effect food production. The farmers output is 15g, not 7.5g. 4 food is 1/50th of a free colonist, and 1/50th of 750 is 15.

So take the scenario where you have 2 free colonists and you have 2 options: both are farming, or 1 is growing tobacco and the other is making cigars. Farmers generate a total of 8 food, or 30g. Free colonist growing tobacco will make 4 tobacco lets say, which will turn into 4 cigars. At 12g each (which is a high price), this is 48g and is superior. At a 50% tax rate the farming guys still make 30g worth of output vs the now 24g worth of the cigars, making the farming better. This doesn't take into effect the time differential of taking goods to europe, selling them, and shipping a free colonist back (when you get 200 food they appear nicely in your town).

Of course this analysis only works if your goal is pumping out free colonists, which it should be after 150 turns or so (as they're your soldiers). In the beginning trade is much better because you want to buy the specialists (I buy boatloads of farmers) and the tax rates are low. I think the point where your economy should shift from trade to guns and food is around 35-40% tax, although you want to put the wheels much sooner.
 
craigdh said:
Hmmm I'm not sure if I agree with your analysis entirely. Farmer is far more productive at less than a 75% tax rate. I think an error is with the output of a free colonist on a 4 yield farm patch... you're putting it at 7.5, which means you're applying the 50% tax rate... the tax rate doesn't apply to the farmer because tax doesn't effect food production. The farmers output is 15g, not 7.5g. 4 food is 1/50th of a free colonist, and 1/50th of 750 is 15.
Click to expand...

The free colonist makes 15 gold per turn in "revenue" farming but he eats 2 food each turn(7.5 gold) so his profit is only 7.5. I'm not taxing food, although it looks like that with that example. The colonist who produces 4 food per turn really only produces 2.

My formula for farmer profitability is:

=Cost of a free colonist in Europe/(Food needed to “grow” a unit/(food production-2))


craigdh said:
So take the scenario where you have 2 free colonists and you have 2 options: both are farming, or 1 is growing tobacco and the other is making cigars. Farmers generate a total of 8 food, or 30g. Free colonist growing tobacco will make 4 tobacco lets say, which will turn into 4 cigars. At 12g each (which is a high price), this is 48g and is superior. At a 50% tax rate the farming guys still make 30g worth of output vs the now 24g worth of the cigars, making the farming better.
Click to expand...

I'm now thinking I'm doing the analysis wrong. For cigar making I can calculate the profitability but I can't calculate how profitable the tobacco harvesters were that allowed the cigar makers to operate. I'm taking the tobacco harvesters as a given and then calculating the profitability of cigar making. The cigar making may look profitable but the tobacco harvesting isn't and that could be throwing off the analysis.

I think the way to better model what is going on is to group production teams up like in your example. You'll need 3 expert tobacco harvesters working 4 production tiles to provide the tobacco for 2 Master Tobacconists. This team will generate 106 gold per turn at cigar price 12 with a 50% tax rate. For comparison, a 5 expert farmer team working 4 production tiles will produce 93.75 gpt. If the price of cigars is 11, farming and cigar production are about equal.

It looks like a 50% tax rate means stop working non factories and start farming. Factories look like they're better than farming until a tax rate of 70%. I need to look this over and update the spreadsheet to be sure.
 
Elliot said:
The free colonist makes 15 gold per turn in "revenue" farming but he eats 2 food each turn(7.5 gold) so his profit is only 7.5. I'm not taxing food, although it looks like that with that example. The colonist who produces 4 food per turn really only produces 2.
Click to expand...

I see where you're coming from.

But remember the cigar and tobacco guys also eat two food, so I've left that out of the calculations because it offsets itself on a relative basis. If you're going to subtract 7.5g for food consumed from farmers, you have to do so for tobacco and cigar guys too, which complicates things a bit so I just say forget about both of their food consumption as you get the same result either way.
 
Elliot said:
You'll need 3 expert tobacco harvesters working 4 production tiles to provide the tobacco for 2 Master Tobacconists.
Click to expand...

3 working 4 production tiles? I assume you're adding the city itself and assuming it's on a plot that generates tobacco? If so, then you have to add the tobacco output to the farmers equation because they could theoretically sell that tobacco. I say just leave out the city plot though, it complicates things and is equal between both scenarios (just like with food consumption).
 
Now, given that Dale's added all basic starting buildings to the first city in AOD2, the OVERWHELMING best option at the very start is a preacher generating crosses. Your first immigrant takes 3 crosses (I play the english usually), and a colonist working the church generates 2... and you will get units better than a free colonist... calculate that return ;)

You'll generate units far faster than your little caravel can carry them...

2nd best return is a Scout on auto explore... makes huge coin at the beginning, as well as the benefit of figuring out the map. Can you put this into the spreadsheet?
 
craigdh said:
3 working 4 production tiles? I assume you're adding the city itself and assuming it's on a plot that generates tobacco? If so, then you have to add the tobacco output to the farmers equation because they could theoretically sell that tobacco. I say just leave out the city plot though, it complicates things and is equal between both scenarios (just like with food consumption).
Click to expand...

Because they're expert tobacco planters, they each produce 8 tobacco when working a 4 production tile. The 3 planters make 24 tobacco, exactly enough to cover the 2 master tobacconists. I should have explained myself better, although I always feel so wordy.

craigdh said:
I see where you're coming from.

But remember the cigar and tobacco guys also eat two food, so I've left that out of the calculations because it offsets itself on a relative basis. If you're going to subtract 7.5g for food consumed from farmers, you have to do so for tobacco and cigar guys too, which complicates things a bit so I just say forget about both of their food consumption as you get the same result either way.
Click to expand...

I subtracted the 7.5 gold from all workers, farmers or not. Please download the spreadsheet and take a look at the formulas :) If they're too convoluted, tell me and I'll translate them.

craigdh said:
Now, given that Dale's added all basic starting buildings to the first city in AOD2, the OVERWHELMING best option at the very start is a preacher generating crosses. Your first immigrant takes 3 crosses (I play the english usually), and a colonist working the church generates 2... and you will get units better than a free colonist... calculate that return ;)

You'll generate units far faster than your little caravel can carry them...
Click to expand...

I build the chapel first too. I even build 3-4 settlements asap and build chapel's in all of them. Once it takes 50ish crosses to get a new immigrant, however, I stop producing crosses unless I received a Firebrand Preacher.

craigdh said:
2nd best return is a Scout on auto explore... makes huge coin at the beginning, as well as the benefit of figuring out the map. Can you put this into the spreadsheet?
Click to expand...

That's waaaay too complicated to model. I am working on incorporating cross production into the spreadsheet. I'm getting discouraged because I can't calculate the immigration cost increase exactly.

I know immigration cost increases by 10% each time, but there is some rounding involved that throws my numbers off. Its not terrible(when it requires 199 crosses for an immigrant, my simple formula predicts the cost should be 205), but its really annoying. Dale, do you know how the game handles rounding in this instance?

In game observation of immigration costs in crosses

Spoiler :
5
5
6
6
7
8
8
9
10
11
12
14
15
16
18
20
22
24
27
29
32
36
39
43
47
52
57
63
70
77
84
93
102
112
123
136
149
164
181
199


Cross cost if formula is: previous cross requirement *1.1

Spoiler :
5.00
5.50
6.05
6.66
7.32
8.05
8.86
9.74
10.72
11.79
12.97
14.27
15.69
17.26
18.99
20.89
22.97
25.27
27.80
30.58
33.64
37.00
40.70
44.77
49.25
54.17
59.59
65.55
72.10
79.32
87.25
95.97
105.57
116.13
127.74
140.51
154.56
170.02
187.02
205.72
 
Ahhh... 3 guys working tiles that produce 4 tobacco, not 3 guys working 4 tiles... that's why I thought the 4th tile was the city itself :p
 
And expert farmers on a normal plot with farm generate 7 food per... so using your formulas, with 2 scenarios (5 expert farmers vs 3 expert tobacco planters and 2 expert tobacconists), at 0% tax rate the 5 farmers are worth 93.75g, whereas the tobacco dudes are worth 250.5g (12g per cigar). At a 50% tax rate it becomes 93.75 vs 106.5g, so tobacco is still slightly better. Although personally I think 12g each is a little high, considering cigars will range between 9g and 12g, so maybe 10.5g is a better mid-range amount. At 10.5g per, farmers are more productive at 50% tax rate (93.75g vs 88.5g).

Now another thing to take into consideration is farming doesn't lead to further tax increases or gain trade points for founding fathers. Given some of the founding fathers, a higher tax rate actually has some positive benefits, so I never ditch trade all together. Personally I just won't exceed more than 1 manufacturing hub, with 3 experts going full tilt. At that point in the game, it's just not worth developing a 2nd one because usually the tax rate is approaching 50%.
 
If it is ever more efficient to farm rather than trade to get new colonist, then the game needs to be rebalanced. Possibly with only free colonist (no specialist) and low level buildings it should be more efficient to farm, but once specialist and higher level buildings come into play, trade should be much more efficient than farming alone. At least 50% more efficient.

I likewise think that buying a specialist should not be more efficient than educating a free colonist. The wait time for education should be a factor.

I don't agree with the way prices of military hardware escallates in Europe. I think it should be a viable strategy to produce no weapons at all in your colony but buy them all. Producing your own should be substantially cheaper but you should still be able to purchase enough of them to get by before the price goes through the roof.

The price of a finished good (i.e. cigars) should never be so close in price to the corresponding raw material (i.e. tobacco) that it is more cost effective to stop producing the finished good and start selling the raw material.

Any feature in the game needs to offer a distinct advantage or it's a waste. The advantage may be situational though not so esoteric that it's usefulness is too rare a circumstance. The more situational the advantage, the bigger the advantage needs to be.

Any tax rate above 50% should produce an exponential rise in rebel sentiment. When you start taking more than half of a person's income, they get pretty pissed, pretty fast. In fact, IMHO, the closer to 50% the tax rate gets, the more rare a tax increase should get. Especially in circumstances where the taxed are getting NOTHING for the money paid. Even in the European countries were tax rates are at or above 50%, there are services provided for that money (i.e. free health care) Even a king as stupid as King George was would understand that dynamic.
A good formula for tax rate increases could be:
TaxIncrease% = Random(2 to 7) - (CurRate% / 10)
 
Aerion said:
If it is ever more efficient to farm rather than trade to get new colonist, then the game needs to be rebalanced. Possibly with only free colonist (no specialist) and low level buildings it should be more efficient to farm, but once specialist and higher level buildings come into play, trade should be much more efficient than farming alone. At least 50% more efficient.
Click to expand...

I agree with you. Trade is more efficient at a lower tax rate, but once the tax rate gets to 50%, if the goal is to get a lot of colonists (which you need to build a big army and defeat the king) then the best way to do it is to dedicate your economy to food production (and guns).

Your next point on buying military hardware from Europe is also 100% valid. It's not an option to buy an army right now... you have to literally grow it and build the guns yourself. Save buying a few cheap cannons early, there's no point buying anything from europe military-wise.

I know I'm like a broken record, but getting rid of the customs house has pretty much eliminated trading as a viable long term strategy in the game.

Your other point on education is good too... right now it does not make any sense to train your own specialists, with the exception of the unique ones that you can't buy in europe (ie expert sugar planters) and veteran soldiers. One could maybe make a case for the elder statesmen, but other than that, you're much better off buying your specialists.
 
craigdh said:
I know I'm like a broken record, but getting rid of the customs house has pretty much eliminated trading as a viable long term strategy in the game.
Click to expand...

Actually, with a little modding on the side, Dale's top level warehouse can function as a custom house. You just have to mod the controlling XML to reduce the penalties and increase the percentage.
 
Aerion said:
Actually, with a little modding on the side, Dale's top level warehouse can function as a custom house. You just have to mod the controlling XML to reduce the penalties and increase the percentage.
Click to expand...

This is true, however there are still a few things that differentiate it from the original customs house.

1. You can't trade in boycotted goods
2. Taxes still come off your sales
3. Sales will lead to higher tax rates
4. You can't set a level where they sell everything above... ie sell when a quantity of goods exceeds 100. With the current "customs house" it will only sell when you exceed 400 units.

To make a trade economy viable again, you need to have something like this around.
 
My wish list would be the possibility of trading with ANY European king - not just your own. Especially after WoI declared.
 
craigdh said:
This is true, however there are still a few things that differentiate it from the original customs house.

1. You can't trade in boycotted goods
2. Taxes still come off your sales
3. Sales will lead to higher tax rates
4. You can't set a level where they sell everything above... ie sell when a quantity of goods exceeds 100. With the current "customs house" it will only sell when you exceed 400 units.

To make a trade economy viable again, you need to have something like this around.
Click to expand...

You DO realise that what made this possible in Col1 is actually a bug right? ;)

One thing to point out, is that at DoI your tax rate is reset to 25%. This helps the sales-tax thing. Before DoI it is 100% believable that ALL trade goods will go to the mother country. I've also severely upped how much the warehouses sell off, thus stopping the ever-increasing warehouse amount. Also note that boycott items are re-enabled after DoI if you select the constitution option to "trade with Europe".

So basically I have tried to make trade easier with the old custom house, whilst not introducing the bugs of the past. :)
 
Dale said:
You DO realise that what made this possible in Col1 is actually a bug right? ;)

One thing to point out, is that at DoI your tax rate is reset to 25%. This helps the sales-tax thing. Before DoI it is 100% believable that ALL trade goods will go to the mother country. I've also severely upped how much the warehouses sell off, thus stopping the ever-increasing warehouse amount. Also note that boycott items are re-enabled after DoI if you select the constitution option to "trade with Europe".

So basically I have tried to make trade easier with the old custom house, whilst not introducing the bugs of the past. :)
Click to expand...

Doesn't much matter what happens to trade AFTER DoI. Trading is pointless after that anyway.

The way the old custom house worked might have been a bug, but apparently this bug was popular enough they never fixed it. But I do agree that trading through a custom house should not circumvent taxation. Though it might be appropriate to get a bit of discount on the tax rate or a slightly better price on the trade to represent the streamlining of trade.

In the case of England, all trade was FORCED with England through the East India Trading Company. But as tax rates increase, so does smuggling. The higher the cut the crown takes, the more incentive to game the system. At 5% savings, why bother. At 50% savings, you're talking REAL money. Thus the ability to sell to other European countries represents smuggling. But the custom house/warehouse only sells to the mother country. Any outside selling is via ship and the goods are sold at a reduced rate to represent the smuggler's cut.
 
You must log in or register to reply here.

Similar threads

W
Early- and Midgame Guide for We-The-People
Replies
0
Views
2K
Watermelon
W
W
General Feedback on Normal/Revolutionary
Replies
4
Views
2K
Cyberian
Cyberian
hokath
Hokath's Tweaks
3 4 5
Replies
80
Views
11K
hokath
hokath
PDX Firefly
Developer Diary | Economy Part Two
Replies
2
Views
1K
Boris Gudenuf
Boris Gudenuf
PDX Firefly
Developer Diary | Economy Part One
Replies
7
Views
2K
JNR13
JNR13
Top Bottom