Planner

[Ledhead]

I did a quick excel sheet for housing/prosperity guestimations.

It will give you population, production (currently at optimum levels) and prosperity for housing. The second sheet includes a slightly modified version of the heaven building list with some buildings ranges added.

Color coded for labor and can help with prosperity and judging production, food produced and food consumed. At this time the food consumed is much more accurate then food produced. If you match these numbers up you will starve :p

No macros. Just a couple of conditional formattings (coloring a box if you are over or under for population vs. housing) but nothing intrusive. Enter numbers in grey boxes and sheet will handle the rest.

As I haven't gotten two seperate labs to give the exact same results I tend to figure a 10% loss from optimal production due to transportation. Never hurts you to slightly over produce. If you see that a map is gonna cost you more for transportation take a little more off.

www.swgclassic.com/downloads/planner/planner102706b.xlt = Office 2003 2K 2003
www.swgclassic.com/downloads/planner/planner102706b.xltx = Office 2007 beta


--update history--
102706 - corrected equation issue with production buildings.
102606b - spell check is your friend :o goods calc fix.

[Ledhead]

Included a more accurate estimation of food consumption for Patrician housing.

Corrected tax collectors using plebs for labor missed that at some point...

Same link.

[PhilL]

Thank you for your hard work. Very nice,

Are you sure food production/consumption is correct. I input some numbers from memory of a working city and found I only produced 25% the amount of food I would need.

[vic_4]

I am going to study it very accurately.

[Cartpusher]

Phil,
I think his food production numbers are correct, but I think the numbers of people per grain/veg field is about 175. (22.23 units per year/.128 units/person/year) and cattle pastures feed about 280 people per year. Double those numbers for farm amounts, assuming two fields/pastures per farm. Interestingly, the higher production in cattle pastures is exactly offset by the increased size. Each microtile produces the same amount of food per year.

My numbers were calculated in the demo with mimimum supply chain distances and only one operating farm on the map. As we have seen, the workers have travel time to and from the fields/pastures. and workers will sometimes walk across the city to an open field/pasture. I haven't seen any changes to these factors in the game.

So the theoretical maximum for a group of three farms, one of each type, is about 1250 people. When I plan a city, I ballpark 1000 people per city to allow for supply chain friction.

And once again, thanks to Grumpus, whose site "Which Way to Rome" introduced me to the concept of bean counting and pre-planning cities.

[ZiRo]

May I suggest you work some efficiency "warnings" or suggestions into the spreadsheet.

E.G. Point out the fact clay is being produced, but not used. and that there is more pottery being produced than is consumed in a year by the residents etc..

That'd make it extremely extremely useful..

Edit: Just checked out "Which Way to Rome" and I used to print Grumpus's blocks out when I was probably 13, I remember the drawings. (I'm now 18) - When I used to play C3 anyway..

[Ledhead]

The numbers should all be from the prima guide productions tables. So food and factories should all be "Optimum productions" it doesn't take travel time shortages in labor or any other factor into account.

Food consumed was based on my lab work from http://www.tiltedmill.com/forums/showthread.php?t=10511 the additional add for patrician houses came from actually looking at how much was eaten per cycle. I assumed they run on the same 16-17 cycles per year that the other houses use.

[eperdos]

Phil,
Each microtile produces the same amount of food per year.

in fact these are the ratios per microtile (assuming ledhead`s planner is right, wich seems to be, in my test of food consumption and grain producing):

every microtile produces 0.16 food unit/year.wich is 16 foods in the house(portions :p ), wich means every microtile produces 1 food portion every cycle.

[Estavius Maximus]

Thanks for going to the trouble to work this up. It's sure to be quite helpful.

[eperdos]

ledhead, there IS a problem with food consumption.if I set one large insulla and 3 large domus, so I have 390 population, I consume 51,01 food units/year, wich is correct. I set 1 more large villa and boom, I have 639,01 food units/year. thats imposible!!! would be as avery patrician consums 19.6 food units (1960 portions!!!!!!)/year. can`t be...

I know nothing about working in excel, but apparently, when slect patricians houses, your planing calculates annual food consumption corectly but places it in monthly cell.and in annualy cell, multiply corectlly the monthly cell by 12. so please, change it if you find I`m right.

[Ledhead]

Corrected now.

I had used food rather then food units. (forgot to devide the 100)


**edit**
Also had a hosed sum field. (43 instead of 34) thats changed now too.

[Kylestra]

Great work Ledhead, thanks this will be very useful.

One thing I noticed is that you left out the Education section in the Equite section of labor.

Kylestra

[Ledhead]

Correct...

In there now.

[doublenerds]

Very nice! I did something a bit different, but it seems to be working well for me.

The attached excel spreadsheet lets me put in my population goal, the total number of common and luxury good types available, and the number of products needed to meet my trade partners' needs.

From these figures, it estimates how many houses and industries of each type I will need to support my city and trade the max amount each year.

So far, my estimate of 60% pleb, 30% equite, and 10% patrician housing seems to be a decent ratio for a successful city. I'll probably tweak the proportions a bit after I've finished the whole game (currently working on Lugnudum).

[Ledhead]

updated to include eperdos' production info. Should give much more acurate food production. I wont be able to test till i get home... But if any one is able let me know if they are more accurate.

For those that have noticed a size change between posts, it's because I have 07beta at work and it bumps up the size.

[Ledhead]

New update.

1) What should be a very accurate estimate for food consumption. Re-worked the math for consumption based on total population rather then based on house usage. Also corrected an error of using the number of patrician houses rather then the patrician population. Seemed to be correct as I was evicting people.

2) Re-arranged the layout to match things up between sheets and make it a little easier to make changes and match up some of the equations.

3) Moved drill yard over to equite labor section.

4) Added property tax income for patrician housing

The production sheet is a work in progress and not functional at this point.

**update**
Production sheet is somewhat usable. It's fairly confusing and needs some work for the variables on production.
Multiple production for goods needed by houses. Until that break through I doubt much more will be added at this time.

[Cartpusher]

doublenerds:

It looks like you are underestimating your city's consumption. I agree with the .12 per period, but there are 16 consumption periods per year, not 12.

You need to have 1.92 in those formulae instead of 1.44.

Also, you might want to reconsider exporting your raw materials. When you consider the wages and food cost for the extra workers to produce the goods, it's just about a push. Also, as you add housing units and your city gets bigger to accomodate the extra plebs, you will incur additional indirect costs for services provided by equites. Not to mention that as you increase the number of insula and domus, you will need more patricians to achieve your prosperity goal. Since the population goals are so easy to meet, there is no need to employ those extra workers.

[Cartpusher]

Here's a shocker...for me anyway. I figure I LOSE 26 denari for every unit of weapons that I export. It costs over 130 dinari to make a unit of weapons. I considered the wages and food consumption of the workers producing iron and wood, and the wages and food consumption of the workers making the weapons.

Somebody tell me I'm wrong!!!! I have always opened the weapons trade first believing that it brought in the most money. No wonder I'm going broke in Narona. 2000 for a bridge and 1500 to open a trade route that doesn't produce a profit.

I think I'll open the olive oil trade first this time and see what happens. I'll do the bridge later when I can afford it:) , or Caesar makes it necessary:( .

[Marc5]

Here's a shocker...for me anyway. I figure I LOSE 26 denari for every unit of weapons that I export. It costs over 130 dinari to make a unit of weapons. I considered the wages and food consumption of the workers producing iron and wood, and the wages and food consumption of the workers making the weapons.

Somebody tell me I'm wrong!!!! I have always opened the weapons trade first believing that it brought in the most money. No wonder I'm going broke in Narona. 2000 for a bridge and 1500 to open a trade route that doesn't produce a profit.

I think I'll open the olive oil trade first this time and see what happens. I'll do the bridge later when I can afford it:) , or Caesar makes it necessary:( .

I don't know the exact figure but I believe you actually only pay about 10% of employees wages. Did you factor full wages into your calculation?

[Ledhead]

Wages are a bit wonky... take the total per year (calculated at start of month based on population) on the labor screen and devide that into the %labor for the production your looking at.

ex.
350 plebs = 700 in wages per year
timber = 40 labor and cost to build 20
iron = 42 labor and cost to build 20
weapons(x2) = 126 labor cost to build 76
sea trade = 30 labor and to build cost 38

238 pop = 32 weapons a wear
68% of the population
476 in labor
initial cost = 154
average price for weapon 100

Figure 1/2 production first year = 1600 at a cost of 630 first year
Then 3200 at a cost of 476 per year.

Not counting mars bonus to production or mercury bonuses to trade.

[eperdos]

the new planner is very good. one mistak I noticed so far: wine factory produce in fact 17, beeing a luxury good.

what are figures?

the new building sheet is great but I found theater range 97? it`s for sure? looks grater than coliseum...

oh and in population sheet, you missed the guilds.

it`s great the tax income for property. vry helpfull. could you add tax for selling too?

great work!!!you are a master

[Cartpusher]

Yes, I used the 20/year amount in my calculations. Who knew (MD?) that TM would purposely include a misleading number like that. My apologies for not verifying the data and my assumptions.

There is profit to be made exporting any and all goods, raw materials included.

Never mind.:o

But this goes a long way toward explaining another economic problem. While picking the demo apart, it bothered me that the governor was paying for the construction of the farms and paying the workers at the farms, but only collecting x% of the sales price at the food market in taxes. My question was "where is the rest of that money going?" I finally rationalized it by saying that the patricians were making profits from business in some way because they needed a source of income to pay their taxes. It now appears that they must also be paying the other 90% of the plebs wages.

[Jimaaten]

Somebody tell me I'm wrong!!!! I have always opened the weapons trade first believing that it brought in the most money. No wonder I'm going broke in Narona. 2000 for a bridge and 1500 to open a trade route that doesn't produce a profit.

I think I'll open the olive oil trade first this time and see what happens. I'll do the bridge later when I can afford it:) , or Caesar makes it necessary:( .My advice on Narona would be to go ahead and build the bridge in the beginning. But concentrate on jewelry and utensils first. (You also don't need to wait for that first olive crop.) Each only requires one raw materials and can be sold in greater quantities than weapons. Each factory also uses about 2/3 the labor of a weapons one. Also, those two trade routes combined are cheaper than the 1500 dn one! :eek:

[Ledhead]

@eperdos
Ya wine was off. When I rearanged the layout i missed the production value

figures was just a scratch pad. Now its a holding area for a grouped bit of info from the population page. Makes it easier for me to apply changes to equations on all goods.

Ya range on theaters was off i was going off a post it looked a bit odd but it also had the size as 1x1 bigger. set to 60

Guilds were there just not labled with guild. I added that and rearanged them so they are noticable.

I haven't gotten around to labin' out the sales tax yet. I don't think I've seen one yet either. If/when I/someone does it i'll add it.



@all
Played with the layout and in the process worked out the production page. Should be fairly easy to understand. It has a place to enter import and export ammounts and costs. It's only for basic goods, luxury goods and resources. no exotics (yet).

It will red out a field if you don't have enough structures or resources for your planned production. There is a section top right of population that should give you goods produced and goods consumed. The conditional formatting is a bit wonky. It only changes correctly after changing production not the housing numbers.

I moved tax section to population page. When I manage sales tax i'll figure that in for goods used.

Unless I dropped something else along the way should be good for a short while. God bonuses and sales tax are the two biggest things I can think of that are missing at this point. Those should be fairly easy to add when I get the figures.

Minor note, these figures are for a stable city. It wont take into account the big rush to fill the markets. For food I'd suggest a little over production. For goods it will just take a bit from your export income to cover the build up. Assuming you don't warehouse things before opening markets.

** edit **
While test numbers seemed to work, using it for game failed. Little things like doubling some buildings needed and not others and listing 12 times the goods needed.

**Planner links are in first post.**

[Ledhead]

** update**
The beta planner (link above) has (what I think to be) a working production section.
Added sales tax.
Calculates import and export profit/loss.
Production should account for both import and export values entered in.
Taxes and production for goods (top right) based on yearly production.

For production sheet.
If you are importing or exporting add the number and cost into the fields. It will base the rest of the figures from your population page settings.

Buildings needed: the total number of that building that you will need to reach the production you want.

Needed units: The amount of material needed by production or trade.

**edit**
corrected some building ranges after labing them out.

[eperdos]

I see you put property salex calculation. can you put sales tax calculation? or am I missing it? I see the cell for sales % but no calculation.
and is there a place whre to enter exotic goods import?

anyway, beta planner is so good.
bravo!!!

[neilm85uk]

This is a great tool. Now to get my Small Estates up to Large and win!

[Ledhead]

I see you put property salex calculation. can you put sales tax calculation? or am I missing it? I see the cell for sales % but no calculation.
and is there a place whre to enter exotic goods import?


The sales tax portion is right below the prosperity section and above the goods usage section. If you downloaded between some of my updates might not have been there yet.

I just need a list of exotics to add. That portion is independent since it isn't used for production and has a couple places where you import export it so it needs added at some point.

Thinking off adding the exotic requirements to grey out the patrician housing that wont be available without X# of goods.

Assuming I don't find any more blatant errors today i'll probably bump the beta version up to normal after I add the exotics. now to find the exotics...

[MrKris]

Thank you for your hard work in creating that spreadsheet. I have a small request for you. Would you be able to update first post in this thread with the new links? It will make your spreadsheet much more accesible for people opening this thread. Thank you.

[Ledhead]

Updated first link. (Should have thought of that.)

Added what exotics I could think of for import export.

Added a list in building info with MarkDuffy's evolution info.

Corrected tax info to be based on info from game file. Same solution different equations.

Added a section next to exotic goods that you can place a 1 in the field if patricians will have access to it. This will later be used to grey out what buildings aren't available without the goods to eveolve them.

This is all part of planner now. The plannerbeta will start on the greying out of things for evolution. I need more "testing" though been slacking off a bit :D

Let me know if you see an issue in the planner. Other then spelling samphorage... should be mostly good :)

**edit**
correction to planner.
Added remaining exotic goods.
Corrected Equite labor calculation.
Corrected the maximum for property taxes.

[Ledhead]

Updated entertainment info and trade cost/profit info off heaven.

[Unmasked]

One minor adjustment: Your planner gives weapon exports a price of 206 when it is 106. :)

[Ledhead]

Thanks fixed :)
Wouldn't have noticed that till after the weekend.

[Jimaaten]

Ledhead, I noticed in the plannerbeta that there seemed to be an error in how it calculates the Total labor under the Government header; at least the version I downloaded a week or so ago. For example, I had 132 engineer and 144 prefect labor, but the total showed 1124, almost a 1000 plebe difference. Changing labor under the Military one seemed to affect this. Needless to say, it throws the plebe total out of whack. I noticed the original planner has the correct total.

Hope this helps.

Thanks for your hard work. I use this before every city to plan out my attack! :)

[Ledhead]

Ya I had an issue with that total field. It was adding the columns above it plus its fields. It was either the storage or the markets (I don't recall which) that was getting added to it also. Fixed it the following day, you just caught it between updates. Broke it somewhere between re-arranging the layout and moving the tax collector or the drill yard to the Equite side.

As a side note I wont be able to red out unavailable housing based on goods availability. Excel (haven’t tested in a beta07 .xls) wont allow a cell to be formatted based on two other cells. Such is life :)

**edit**
Scratch that last part got it figured out making the change as I eat pizza :p wish me luck. Another update will be up soon.

OK should be all good reds out if you don't have enough exotics or if you don't have gatehouses (no gathouse no surrounded with wall)

Wonder if i can get it to lay out the city for me next :D

Added the xltx for any one with office beta07. It has less issues with conditional formating then previous versions. Links are all in first post.

[Zamolxes]

Great work Ledhead, no wonder people dont ask for numbers anymore, this excel sheet is a GOLDMINE. If u can make the thing lay the buildings automatically for u, in whatever pattern u want .....:D

[Ledhead]

Ya if I could make that program I'd be a happier man. I look forward to someone doing a drag and drop "map" that you can trully lay out a city without worrying about your population actually surviving it :p having it give you the overlays from outside the game. Handy thing to do at work. :D

At this point I'd be happy with a printable micro tile map for each map that has all the unbuildable areas shown. ;)

[eperdos]

I only post to keep this topic in front, as I believe is a "sticky" one.
sorry...;)

[Ledhead]

Last little fix for a while.

I added imported and exported food to the yearly totals for food collected/used.

Removed the plannerbeta as nothing needs tested and no one has mentioned issues lately.

There is an issue with the conditional formating in versions prior to excel07 in that it wont treat two rules seperatly. First one to change will cause the shift. Mansions will lose their red for exotic goods even without walls. In 07 all rules are applied so lifing one shading will leave others. It is a program issue nothing I can do for that.

If anyone finds something wrong or that needs added let me know.

[Marc5]

This is an amazing piece of software that I use before every mission. I am wondering what/where is the most up to date posting of the Planner? When I download the one from your very first post, I get a bunch of folders but not the spreadsheet.:(

I also noticed that whatever version I already have, in the Pleb worker calculation on the first page, when I add 1 tower, the Total Pleb Workers (near the top) actually counts it as 114 workers instead of 38. (3 x 38 = 114, any correlation to anything?) I can't see anything wrong with your formulas. Is this because of the conditional formatting you mentioned since I have Office 2003? If so, how can I remove the shading as the worker calculation is more important than the cell being shaded?

Thanks for the great spreadsheet!

[Ledhead]

The math issue was from fields getting counted multiple times. That was fixed a little while back. The conditional formating is just the reding out of the houses if you don't have enough exotic goods or walls and the reding or greening out low popuplaion or food type stuff. I use gatehouses to represent full walls as they are needed :) Thats all I use it for at this time.

I updated the links and the files with a date in the name. Will give me something to go off of for fixing issues. It let me download it and it seemed fine.

As long as your using the pre07 file you should be fine with office 2003. The .xltx file wont work in anything but beta07 without the little file converter form microsoft. the converter wont fix the contitional formating but will let you open it.

[Marc5]

My math issue is with the Towers, not Gate Houses. I am in Hippo Regius and am building walls for the first time, so I never used the towers, gatehouses on your spreadsheet before. When I enter “1” in the Towers section, next to it, it will say “38” employees but the top says “114”. Is this corrected in the PreBeta version?

I did download the Beta as I was confused by the file names. I thought that the “Beta” and “Prebeta” names referred to this file, not beta for MS Office.

[Ledhead]

The "beta" and "prebeta" refers to office 07 beta. Unless you have Office beta07 you'll want the pre beta version. Office changed its file type for all office files.

You can get a converter from microsoft that will allow (atleast) office 2003 to open the office07beta file types. It looses some of its features but the file itself still remains fully functional. (the limitations are in the office 2003 programs not the file).

On the version I have up now the tower thing shouldn't be happening. My references to the gatehouses were just for the conditional formating reding out the L. domus and mansions for not having city walls.


*edit*
The "betaplanner" was removed since there isn't anything that needs tested at this time. If anyone knows something they want added let me know I'll consider it.

[Marc5]

Thanks for changing the file names. I now know which one I need.

Again, an EXCELLENT Spreadsheet that I will continue to use!

[CarlyTuur]

I agree. Excellent sheet.

One remark. If you calculate the production needs for your raw materials you take into account what will be exported. I don't think it's correct since you have to produce it first (when not covered by import).

[Ledhead]

@CarlyTuur ah missed that.

I had corrected that on the armor and weapons section... I was doing the imports and exports twice. Negating them out for the buildings section.

Still don't like how the negative buildings display but at quick glance it looks right. I'll take a better look at it next week when I have more time to try some things.

Uploaded a corrected one.

[Marc5]

Couple of things I noticed:


Cell P53 does not have the correct formula. It has "=SUM(P44:P50)" and I believe it should be "=SUM(P44:P52)"



The red shading of your Patrician homes is only based on Exotic Goods available, not if a Gatehouse is being used (I think that is what you meant with this statement - "My references to the gatehouses were just for the conditional formatting reding out the L. domus and mansions for not having city walls."



A few spelling errors - Vegtable, Colliseum, Gadiator, Jewlery, Furnature, Resevoir, Govenor's


I only use the first sheet myself, and LOVE it. I always start by using it to determine how many Equites I will need, then determine the number of Plebs. After that I layout my entire city in the game. I have a Save file "0 Hippo Master Plan" for instance that I save and go back to as I am playing to make sure I don't forget anything. Another thing I really like about your planner is that after I have planned my entire city, I go back to a blank Planner and use it to determine how many houses of each type I will need in "Phase 1" - initial starting to get some things such as Basic goods, Food (it doesn't feel right to not feed the buggers!) and export up and running and "Phase 2" which is where things are really starting to move along and usually includes adding my initial Patrician homes to get the taxes coming in. I have never had a "Phase 3" as I am about 85%-90% done building after Phase 2 is complete.

[Ledhead]

Fixed. Thanks.

Ya not sure how I missed that one cell. It happened when I split it up for each house level (restricting evolution) but I usually drag all those things when I can...

Far as I know walls are only required for mansions. Both rules are in place for mansions just goods for the estates.

As I live by spell check no clue as to why I hadn't done that yet... :o

I generally use it to get my initial set up for food service and what trade I choose to open. Then come back to figure housing for prosperity later. I don't over build as much this way and found going back on hard level I'm beating levels without having to wall housing to get prosperity up. Lower population better culture too. A sure sign that I had really over built before, 3K vs. 5K population... I don't drop Patrician housing till everything else is stable. Their taxes just cover part of the cost for exotics. I'll already have the routes and depot/port open.

[Marc5]

Far as I know walls are only required for mansions. Both rules are in place for mansions just goods for the estates.



You are correct about walls only being needed for Mansions, but if you have 6 Exotic Goods and no gatehouse, the Mansions still turn blue.

[Ledhead]

That is the issue with the conditional formating. Unless opened in excel07 it blues out whichever req is met first. Both rules are there but there isn't an "and" option its all "or". So instead of "walls and goods" its "walls or goods". Nothing I can do about it other then pull both colorings for the xlt file.

Can't test it to check while at work. I have 07 here have 03 at home.

[Marc5]

Cool. I just wanted to let you know. I don't use that portion of the spreadsheet anyways...but the first page ROCKS!

I am a spreadsheet freak for every CB game since C3. Pages and pages of documentation and they all of their own file folder in my filing cabinet. Just pull the appropriate folder out when going back to another CB.:)

[Euroallain]

@Ledhead:The planner is a great tool. Thx and congrats with it!

I have a suggestion on something i added on the building info: i added a column "desirability" with the codes: 0,+ or -

My quotes (i tried them all out on a "lab") are if this can help you:

Housing: all 0
Food: all -
Raw Materials: all -
Industry: all -
Markets: Food,Basics and Luxury:0 ; Exotic:+
Storage: all -
Health and Hygiene: Barber,Bath and Clinic:+ ; Hospital:- (!)
Religion: all +
Education: all +
Entertainment: All the buildings:+ ; All the guilds:-
Water: Reservoir, Pump House and Aquaduct: all - ;Fountain:+;Well and bridges: all 0
Government:Prefect, Engineer and Tax office: all -; Governors place, Basilica and Forum: all +
Aesthetics: all + :)
Military: all - except Wall:0

Maybe this can be helpful and Thx again for your great planningtool

Allain

[eperdos]

nice list allain. nver thought hospital is with -. but seems logic.

just a thought: romans didn`t had cemeteries?

[Ledhead]

@Euroallain Good idea. The Prima guide has that info in it. If I can get that portion of it I'll put it in. The guide actually has teh + and - values.

[Euroallain]

That surprises me..because the 0's are the most important: to know that markets and walls have a neutral desirability seems to be important to me...

[Bathing]

Walls definitely have negative desirability. Place a wall next to a new home you've plunked down and see for yourself ;)

[Euroallain]

That's definetely not true..see the picture; a gatehouse has a -, a wall a neutral 0

[Bathing]

Very interesting .. good to know!

[Euroallain]

Thx...;) ..it's indeed interesting to let evolve domus...walls are okay..but take care where you put gatehouses..:)