Optimize your City

/optimize Hash Tags

Optimize your City
Executive Summary
Elven Architect
Modeling a City
Artificial Buildings
Collaboration
Blue Plate Special

Optimize your City Spreadsheet
(Collaborative Editing)

Saved Filter Views,
one for each Model City
(Views can be used concurrently)
01 Elven Neighborhoods
01 Human Neighborhoods
….
10 us4 Elven Katwijk Balanced 10
15 us4 Human Katwick Balanced 15
….

22 Elven Harbours
22 Human Harbours

Optimize your City

We’re putting together a very large Google Sheets application that includes all of the Levels for each of the Researched Buildings, for both Elves and Humans, plus several artificial buildings to model conversion activities such as Trading, Research, Tournament runs, Spire runs, and Inventory Setasides.

We’ll include some of the more significant Event Building, but what we’re after is various model cities that can be easily understood, and easily improved by the judicious use of the buffs and benefits that are available from Neighborly Visits, Seasonal Events, Special Events, Provinces, Tournaments, the Spire of Eternity, and Ancient Wonders.

City Dynamics

The Barracks, Training Center, and Mercenary Camp are each SINGLE INSTANCE buildings, so we can use Military Production Capacity as a design restraint.

By contrast, available space is the only limit on the number of Manufacturing Buildings that you can have in your city.

Workers are needed for both Military and Manufacturing Buildings, which allows us to optimize our models for the EQUAL consumption of Supplies by Military Activities and Negotiating Activities, or for some other preferred ratio.

We can then back into the number of Workshops that are needed for supplies, and the Residences that are are needed to provide those workers. Also, the model assumes that all of the building will have been leveled to the maximum allowed by the current chapter Research Tree.

The number of squares in a city is a fundamental restraint that limits how hard you can push the development of your city. The Military Buildings are single instance buildings, but you can build several of most other buildings. How well you MANAGE the available space is a matter of skill and good judgement. We’ll allow up to 20% Magic Residences and 20% Magic Workshops, but won’t be using any premium 5x5 city sectors.

Timing is a tricky subject. The production rates in Elvenar are polynomial, which means that the production curve flattens out as the production runs get longer. A bit of Math shows that:

  • 2 * sqrt(3 hours) = 3.46 hourly production units
    which is just a bit more than

  • 1 * sqrt(9 hours) = 3.00 hourly production units

The various attribute tables in the Elvenar Wiki indicate the production yield for a 3 hour run (or less). We want to accommodate the folks who only stop by once per day, so it’s very convenient that two 3 hour cycles yield just a bit more than one 9 hour cycle, so we can have it both ways.

  1. For most buildings the production model is All or Nothing. You can collect at the end of the production cycle, or you can cancel and recover whatever you paid to start the cycle.

  2. Residential Production is stated as Per Hour for hh:mm, with partial hourly collections allowed. We’ll model residential production as an early withdrawal after 3 hours, with a restart to get a full capacity run during the second 3 hour cycle.

  3. Military Production is stated as Total Weight (a heavy unit has the same weight as 6 light units) that will be available in hh:mm, with the allowed withdrawal of slots that have been completed. We’ll model that as the withdrawal of the slots will be finished after 3 hours, with a restart for a full capacity run during the second 3 hours.

# Optimize your City

# Executive Summary

Executive Summary

Google Sheets has EXCELLENT support for COLLABORATIVE endeavors, and allows SEVERAL folks to use the same spreadsheet, concurrently. The suggested workflow is to use filtering and sorting to see what other folks have been doing, including the Blue Plate Special for each chapter, select the buildings that are close to what you need, Copy/Paste those buildings, and then identify those Pasted Buildings as your own. You can then use your own Named Filter View as your entry point whenever you revisit the Optimize Your City spreadsheet.

Once Off activities, mostly Research, Construction, and Upgrading, are easy to manage; all you need is a HUGE pile of Commodities. For Recurring Activities, mostly Production and Trading, a HUGE inventory isn't a feasible solution. Your recurring activites need to be optimized so that your city will have a smooth growth curve. Balancing your recurring Active Hours is the focus of this Optimize Your City spreadsheet.

Hourly rates are the actual metric for Residences and Troop Production, and for most of the other buildings the hourly rate is shown on the Upgrade Tab. Multiplying the Hourly Rate by the Number of Active Hours, and the Number of Identical Buildings, gives us the Daily Production (+) or Consumption (-) rates for each Commodity. Where an hourly rate is not indicated, we've used the Indicated Rate / Sqrt(Indicated Hours) to derive the Hourly Rate. To restate the consequences, the total production quantity of a long run = Hourly Rate * Sqrt(Number of Hours). A nine hour run only provides 3 times the hourly amount.

A Monte Carlo simulation is available, which allows you to programmatically vary an input over a specified range. You'll be able to see the Min, Mean, & Max for up to four different outputs. It's a LOT more satisfying than just wondering why you're not getting the results that you expected.

For the template, the Active Hours assumption is TWO sessions per day, at 6am and 6pm.

* Two 9 hour runs would be 18 Active Hours,

* If a run is longer than 24 hours, the model uses (Amount / N) as the average hourly rate, for 24 Active Hours

* Houses and Military Slots, in particular, allow early withdrawals. When a partial run exceeds 12 hours the model caps at 24 Active Hours.

Consumption (-) rates are never buffed, but Mana, Divine Seeds, Sentient Goods, Ascended Goods, etc are subject to Decay (10% is lost or reverts to Standard Goods) overnight.

For the Template Buildings we're using the most likely buffed production (+) rates, as follows:

* Main Hall: Chapter/2 for Effect Visits, =(Chapter * 2) for Reciprocal Visits with 100% Benefit Coins but only 8% Supplies to allow for the expired visits

* Builder's Hut will always get a 10% time reduction in each slot, from Helping Hands

* Residences: 170% buff for Excess Culture (Full Sunshine) from visits and spells

* Workshops: 170% buff for Excess Culture (Full Sunshine) from visits and spells

* Military Troop buildings don't have an inherent buff, but we're assuming that Armories and other buffs will double the Slot sizes.

* Manufacturing will be buffed at 700% from Relics (NOT Culture) and unbuffed Goods will be obtained from Trading (or the Warehouse). Sentient and Ascended Production is debuffed by 10% to account for Decay.

* There are several ARTIFICIAL buildings (zero footprint), mostly so that you can account for Negotiation, Combat, and Trading activities.

* Buffs from Ancient Wonders, Event Buildings, Spells, and other discretionary activities are indicated in the "Source of Benefits" Template numbers. The benefits that we assume are middle-of-the-road, because a LOT of the benefits depend on how interested you might be in Events and other discretionary activities. You'll definely want to adjust your numbers in accordance with your personal playing style, usually by just plugging in the numbers the game gives you, without much concern for where the numbers came from, unless you're in a bind.

# Elven Architect

Elven Architect

Elven Architect is a fabulous fan site that supports a wealth of building comparisons, including the Chapter by Chapter Resource calculator that we’ve linked, and the site also has a neat grid that allows you to fit everything together. But it DOES NOT tell you how everything interacts with everything, nor how many of each building will be needed to balance Production and Consumption.

Finding out that your design doesn’t work well, and rebuilding it over and over again, is a brutal way to manage a city. That’s where Spreadsheets and Optimization Models earn their keep. There’s nothing wrong with getting it right on the first try.

# Modeling a City

Modeling a City

The spreadsheet embedded in this page is live, and it’s automatically updated every 5 minutes, for all current users. You can download the full model if you want to dig into the details. And yes, we have a backup copy that we can quickly drop into place if somebody inadvertently destroys the shared instance.

The first portion of the model is a list of ALL of the Researchable Buildings for both Humans and Elves, for each Chapter and Level, plus selected Settlement Buildings.

  • To provide a middle-of-the-road city that’s easy to understand, we have assumed that a user will make TWO visits each day, at 6am and 6pm, and will choose a 3 hour cycle wherever possible. If someone makes more than two visits per day there’s no harm done, they’ll just have some extra inventory. BUT :-)

    • The Wiki provides 3hr production rates for the most part, and we want our spreadsheet numbers to be readily verifiable.

    • Residence Workers and Military Weights both allow EARLY withdraws, each hour for Residences and each completed slot for the Military.

      • If a full run takes LESS than 12 hour, we indicate 2 (3 hour + cycles.

      • If a full run takes between 12 and 24 hours, we model it by capping the runs at 12 hours, with 2 cycles per day. You can’t double dip anyway.

      • If a full run is already 24 hours we simply show 1 cycle per day, but you can gather the coins and troops at any time. Collecting coins automatically resets the timer, but you have to refill training slots.

    • Two 3 hour runs have pretty much the same yield as One 9 hour run, so the model is useful for the folks who only drop by once per day.

  • To optimize a city we adjust the The NUMBER OF BUILDINGS, not the cycle times. More precisely, the daily production and consumption PER DAY PER SQUARE is what we optimize. The model doesn’t push you to level your buildings, although the need will hopefully be obvious when the Optimizer suggests more Upgraded Buildings and Fewer Building that could be upgraded.

  • If a research level range spans more than one chapter, the last level in the initial chapter, and each subsequent chapters are shown as 10_15 (for example) to indicate that you can get a bit ahead of your current chapter if you have enough resources.

  • As indicated earlier, we’re optimizing for two 3 hour cycles per day, and the amounts are indicated as attributes for each building.

  • (-) Consumed (note the MINUS)

  • (+) Produced
    LOSS or GAIN PER DAY PER SQUARE
    is the signature variable that we’re using for the optimization model. You’ll need to average out your weekly requirements for Tournaments, Spire of Eternity, and the various Special Events.

  • Culture is a bit peculiar in that it’s just THERE. You need enough spare culture to build things in the first place but, once something is built your collection of Relics (NOT your Culture) can buff your Manufacturing Capabilities, by a factor of up to 700%, which is the buff that we’ve modeled, and you can go even higher if you’re using Spells, Event Buildings, and the various Ancient Wonders.

# Artificial Buildings

Artificial Buildings

We’ve define a few Artificial Buildings so that we can model discretionary activities such as Neighborly Visits and Tournament Runs. They provide stuff, just like the real buildings, but the numbers will vary (a lot) from user to user.

  1. For real buildings it’s usually
    (+) Production and
    (-)Consumption, but for Artificial Buildings it’s usually the other way round.

  2. A fake building, that aggregates quantities and footprints, is an effective way to deal with the whole zoo of Special Event buildings.

  3. The point is that we’re using the spreadsheet to add everything together, for each commodity, so that we can then OPTIMIZE for a slightly positive balance.

  4. An elaborate structure of IF statements. below the data portion of the spreadsheet, provides us with a cross-total for the daily use of each type of commodity in your city.

  5. Your progress will stall if you don’t generate enough extra Workers or Culture so, as a Sanity Check, the Current Workforce and the Current Culture Bonus are tracked redundantly. Also, we redundantly track burn rate for Military and NON-Military activities.

  6. In a Perfectly Optimized City, ALL of the cross-totals would individually sum to zero, rather than being slightly positive, but in a functional city quick and convenient is much more important than perfect, so not bad at all is good enough.

  7. It would be lovely to have a nice list of the buildings is your CURRENT city, and their status. We’ve been asking for that list for 10 years now, so don’t hold your breath. The Move View, on a mobile, pops up the Building Name and Level, and that’s the best we can do at the moment.

The Blue Plate Special

is a view only snapshot of various cities, something hypothetical for each chapter plus Actual Cities as they become available. The /optimize Hash Tags menu will show a Named Filter View for each of the past Blue Plate Specials, so that you can jump to an excellent starting point for your next chapter.

Optimize your City is the Real Deal.

Optimize your City is a fully interactive Google Sheet Published Web Page, with the entire Smorgasbord of sorting and filtering tools needed to support collaborative City Design. The data itself is shared, but the Sorts and Filtered Views are yours alone. You will be able to use Pick Me to select a decent starting point for your City Design, then Copy Paste your selection and name it as your own entry point, using “Data > Saved as filter view” followed by
”Data>View Options>Get link to view,” for creating a Desktop Icon.

The suggested Filter naming convention is “Human Katwick Balanced 15,” so that other folks will have a convenient shopping list. We’re overloading the Race column with Race+Username, so you’ll need to include Race as a prefix, and you’ll probably want to include Chapter as a suffix so that you’ll be able to use pretty much the same name for your Filter Views in each Chapter. City parameters don’t vary from World to World, but it’s a Data Field. Folks will be able to take a look at your actual city, using Elven Architect if they’re curious, or an Alt if they have some Why and What If questions for you.

  • The first tab, “ActiveHours,” is pretty much raw data, except for Validation Warnings on data that need to be reliable for sorting and summation.
    The bottom half of the tab is is very geeky. At one point we had the Geeky stuff in a separate tab, but the Monte Carlo simulator balked.

  • The third tab, “Charts,” will help you visualize stuff like the Production Rates for various Cycle Times.

  • The fourth tab, “Instructions.” recommends an efficient workflow, and details how the spreadsheet operates.

  • The fifth tab is probably temporary. It’s a list of AW Benefits, sorted by Category

  • The sixth tab, “Sandbox,” allows you to try things out. It’s explicitly designed for a Copy/Past of your selected Buildings, into the Sandbox, then tuning your building attributes and quantities until they’re “close,”™ with a final Copy/Paste back into the “buildingCycles” tab and the “Optimize” analysis.

  • AW Benefit Categories is a live Saved Filter View link to the Optimize Your City spreadsheet that we’re developing.

  • The website software only allows us to display a live read-only image of the spreadsheet. You can actually see what’s happening at the moment. You can scroll around and change tabs, but you can’t use the outline groups, nor any of the filters, nor any of the sorting capabilities. For those capabilites you’ll need to click on one of the various Saved Filter Views, and once you’re in you’ll have full access to all of the Bells and Whistles.

  • Yeah - we KNOW that somebody is going to mess up sooner or later, but “excellent collaboration tools” includes excellent reversion capabilities including nameable checkpoints, the ability to share comments, and Owner access to the identity of recent editors. If we have to, we can restrict editing privileges to selected accounts, but we’d RATHER use the spreadsheet to share ideas, or even help with “Why isn’t my city working??”

# Collaboration

Collaboration

The Blue Plate Special, at the bottom of this page, is a view only Blue Plate Special that we’ll change frequently, and include in the /optimize Hash Tags menu, so you can see what’s possible.

Optimize My City is our collaborative Google Sheet. It contains all of the levels for Researched Buildings, and quite a bit of other stuff as well, including access to OpenSolver to help you tune your City Design.

The collaborative website is HUGE, but you can link a Saved Filter View that will take you directly to your own city. The important point is that you’ll have the benefit of good ideas and actual working data from other Cities, and they from yours.

We’re still pouring data into the development spreadsheet, but it’s fully interactive and quite interesting.

# The Blue Plate Special

with Monte Carlo