Matrix Games Forums

UPDATE 3/14/2017: The revised AE-Allies Setup Spreadsheet has been uploaded (previous version "D" was released on 12/28/2009 and had over 2700 downloads). It contains 1586 changes (from a total line count of 4653) For those who have used (or are using) the previous version, just go to column "AA" and filter on "1". This will show ONLY the elements which have changed, making it much easier to edit your starting setup. For a more detailed list of the changes and improvements, see the "changelog" in Post #5 below. (The link in this post has been updated so it contains the new spreadsheet)
--------------------------------------------------------------------------------------------------------------------------------------

The AE-Allies Setup Spreadsheet (like it's Japanese equivalent), is designed to give the player a prescriptive list of actions to take on "Day 2", following the December 7th series of historical moves. There are 4653 individual Bases (878), LCUs (1194), Ships (1427), Air Groups (424), and Industry elements (730), all of which can potentially be adjusted in the course of the Day 2 turn. Needless to say, that is a daunting prospect. So what this spreadsheet offers is a set of instructions for EVERY one of those 4653 individual elements. I don't pretend to be a WitP Master, and my choices are certainly open to debate. Nevertheless, I hope this will help folks get past those opening turn jitters and give you a solid base upon which to build in the days and months to come.

You also have a variety of "game play" options to consider. In particular whether one is playing PBEM or versus the AI, with "Player Defined Upgrades" on or off, and "Realistic R&D" on or off. Personally I prefer games that impose a more historical set of constraints upon the player, plus my games are played against the AI, and thus I tend to shun settings which provide additional advantages against an already crippled opponent. So that means this spreadsheet primarily is intended for those who:

1) Play with PDU "off" (this doesn't matter much to the Allies, especially in the early game)
2) Play with Realistic R&D "on" (likewise not that important in the early game)
3) Play against the AI (although most of the recommendations - China excepted - would still be pertinent against an inexperienced PBEM opponent).
4) Game setting at Hard or Very Hard: This is required in order for the AI to function adequately. The AI is not able to supply the many island outposts and only settings of Hard and above will provide a small amount of supply to ALL of the AI locations.
5) Also worth noting that this spreadsheet is designed for Scenarios 1 or 2. It will probably work fine with the new test AI Scenarios 100 and 102, and possibly even the "Japanese Ironman" scenarios, although it's never been tested with those.

Strategically, all unit movements and/or adjustments were made to support a fairly conservative Allied approach. The top level objectives are as follows:

1) Hold Burma
2) Build up bases in Eastern India
3) Holding action in China (limited offensives, but mostly moving troops into defensible positions)
4) US Carriers positioned to assault either Wake or the Marshalls
5) Limited "Sir Robin" in DEI and Phillipines combined with aggressive SCTF activity
6) Detailed network of Search & ASW planes at key locations (interlocking verified and tweaked)
7) Almost all subs sent on patrol (with patrol zones detailed)
8) Get convoys moving between the US and Pearl Harbor/Australia & Abadan/Aden/Capetown and India
9) Begin to establish a line of bases between the US and Australia
10) Preparations for building out the Aleutians

If this sounds like your cup of tea, here's the plan to get you started:

(Note: The attachment below will always be updated to contain the most recent spreadsheet revision)

A picture is worth a thousand words, so here's what the AE-Allies Setup Spreadsheet looks like. Each column has a story:

Col A: Counter - This is the "row count". When using a filter button (lets say you wanted to work on all the "Alaska" region bases and units first), the counter in Cell A6 tells you how many rows of data you are working on (Alaska = 93).
Col B: Region - Every location on the map falls into one of 49 regions. It's one way to work on your Turn 2 selections in "bite size chunks". The Base & Industry data came from the WitP AE Tracker dataset (of which some was edited), and was manually extended to units.
Col C: Location - The hex location of every base, unit, and industry element. Modified WitP AE Tracker data.
Col D: Location Name - If the hex has a name (i.e. it's a Base of some sort), here it is. Modified WitP AE Tracker data.
Col E: Type - There are five row "types": Bases, Air Groups, LCUs, Ships, and Industry. New field, not from Tracker. Some of these cells are color coded:
- Green: Garrison Requirement is met
- Red: Garrison Requirement is NOT met
- Light Blue: Identifies all rows in which follow-on actions are required. You can filter this column by color in the "Day 3 and beyond" turns to identify tasks that still need to be performed (for example, LCUs that travel to one location by rail and then move somewhere else by road)
Col F: Unit Name - The name of each unit. Straight from the WitP AE Tracker datasets.
Col G: Unit Model/Type - Applies only to units. Airframe Model Names in Light Grey cells do not get upgrades. Unmodified data from WitP AE Tracker.
Col H: HQ - Applies only to Bases, Air Groups, and LCUs. Unmodified data from WitP AE Tracker.
Col I: Nation - Applies only to Bases, Air Groups, and LCUs. Heavily modified data from WitP AE Tracker.
Col J: ID - Unit ID. Of limited use, but insofar as it was provided by WitP AE Tracker, it's loaded here.
Col K: Code - Indicates the restriction status of units, and is also used to determine whether a base has a Garrison requirement:
1: Unrestricted units. Can be moved to any base.
2: Restricted units. Can spend PP to switch to an unrestricted HQ (thus allowing moves to any base)
3: Restricted units. Cannot spend PP to change the HQ. Some "static" units allow HQ changes but they are marked as "3" because they can't move anyway.
G: Base has a Garrison Requirement (see Comments in Column L for details). Certain types of damage can occur when requirement is not met (see Manual for details)
Col L: Comments - The meat of the spreadsheet. You can either follow my recommendations or tweak them to apply to your own strategy. Rather obviously, this is a new field, not from Tracker. Here's the meaning of the colored fonts used in the Comments field:
- Black: Take action as indicated
- Red: Either have to make a decision or ensure that indicated action takes place at a later date
- Green: Useful Information ("why" or "what", not "go do")
- Blue: Search coordinates
- Purple: Change made to a pre-existing TF (i.e. on-map on Turn 1)

Here's a screenshot of the second half of the spreadsheet. You may have been wondering about those descriptions in rows 1-5 of column D. They exist because I did something in this spreadsheet which is normally a bad idea - the data in columns M thru AH varies depending upon the "Type" (column E). I did this for a few reasons:
- First of all, this data is interesting but most people won't use it when making their Turn 2 selections.
- Second, it allowed me to shrink the width of the spreadsheet by a factor of three, which greatly enhances "usability".
- Third, all of this data was taken directly from the WitP AE Tracker datasets, and is largely unmodified.

So how do you interpret this? It's actually pretty easy:

- If you are viewing a row in which the "type" is "Industry", then use the column headings in row 1 (applies to columns M-P)
- If you are viewing a row in which the "type" is "Base", then use the column headings in row 2 (applies to columns M-Z)
- If you are viewing a row in which the "type" is "LCU", then use the column headings in row 3 (applies to columns M-AE)
- If you are viewing a row in which the "type" is "Ship", then use the column headings in row 4 (applies to columns M-AH)
- If you are viewing a row in which the "type" is "Air Group", then use the column headings in row 5 (applies to columns M-AH)

There are eight tabs in this workbook (used to be four), all serving a different purpose:

1) Getting Started (New): Primarily focused on teaching you to how to use and interpret the "Allied Set-up" worksheet, but also provides a synopsis of the other tabs.
2) Allies Set-up (Revised): Discussed extensively above.
3) 12-41 Arrivals (New): This tab contains all the units scheduled to arrive on-map during December 1941. This is not just a list of units, but has the same format as the "Allies Set-up" spreadsheet, to include a full set of comments for each unit! This takes the "hold your hand" ethos one step further, by providing suggestions on what to do with all these near-term new units (182 arrive in this 3-week period).
4) Unit Arrival Dates (New): This is something I had planned to do for a long time, but it took a while to achieve - a single document listing the arrival dates and locations for ALL 8318 units - Land, Air and Sea. Much of this was pulled from Tracker, but it's nice to have all of it in one location. For the most part, use of this worksheet is self-explanatory, but please take note that it follows the same approach as the "Allies Set-up" worksheet, with different column headings in rows 2-4 (applies to data in columns N thru AC). It can help to filter by unit "Type" (column C) in order to see the various units aggregated. The headings for all other columns (A thru M) apply equally to all units, regardless of type. (Screen shot below)
5) Transports for Key LCUs (New): Details (by name) the ships needed to perform three Land Unit transport missions and one Air Unit transport mission. To some extent this is redundant with information in the "Comments" column in the "Allied Set-up" worksheet, but it can be helpful to see it centralized in one place.
6) Region-Base list (New): Information pulled from Tracker, listing all Bases (including the Japanese) and their associated regions.
7) Devices (New): Information pulled from Tracker, listing all the devices (including those shared with or exclusive to Japan) . Can be very helpful when trying to determine the importance of any given device upgrade, especially for LCUs. Redundant with reports available in game, but the sorting here is a bit easier, especially if you have this workbook open during your game.
8) Changelog (Revised): The number of changes between Rev "D" and "E" (this version) takes this update far beyond the 1586 new or revised comments. Cumulatively they also reflect a complexity reduction philosophy more in keeping with a "beginner's how to" as well as a subtle change in the unit movement methodology, particularly as it affects certain theatres in the first month or so of the war. Accordingly, I highly recommend that you review the 7 items listed in the Rev E changelog description. It won't take long to read, and will give you a better understanding of the overall approach.

Changes in Version "E" (Various strategic/tactical improvements & formats revised to match "Japan Set-up")

1) Format & Terminology changes:
A lot of changes were made to format in order to fully align the "Allies Set-up" worksheet with it's Japan counterpart. You'll also notice that the color coding is identical, as are the expanded data fields (columns M-AH) imported from Tracker. Much of this isn't immediately apparent, but users of both spreadsheets will now find them to be almost fully in synch.

The comments section was extensively revised in order to use terminology "shortcuts" that are identical to those in the Japanese version. For example:
Original ASW Patrol Settings: ASW Patrol, Search: 310-40, Level: 50, Alt: 5K, Train: 20 (all else no change)
New "Japan-style" Description: ASW Patrol (50): 310-40, Alt: 5K, Train: 20

2) Naval Air Units:
Air units aboard ship now have their own "data row" (same as the Japan spreadsheet)

3) Veteran Pilot Reallocation Process:
The previous version of the spreadsheet contained detailed recommendations on how many Veteran Pilots should be harvested and moved from one unit to another, literally noting how many pilots should be pulled from unit "X" and reassigned to units "Y & Z". On further consideration, that's too much micro-management for a "getting started" spreadsheet. All that is gone, replaced by an occasional notation for any unit that might benefit from adding replacements or even swapping in some Veteran Pilots (in general, that would be any pilot with 60+ experience).

4) Allied Pilot Training:
A major change in the new version is a much larger number of air units assigned to Training missions. Most Allied pilots begin the game with very low experience and their across-the-board specialized skill numbers are frequently atrocious. Accordingly, the number of air units flying missions (Naval Search and ASW Patrols in particular) have been greatly reduced so you can get an early start on building up those important skill sets.

5) Submarine Patrol Zones:
I have provided specific Patrol Zones for most submarines, but it can be very time consuming to match them exactly. Accordingly, you should feel free to set your own zones, or simply note the designated area for each zone - always specified in the Comments - and then use the "Patrol Around Target" button. The resulting computer choices are often less than optimal, but are easy to alter in the Patrol Zone selection screen. Tip: When using the "Patrol Around Target" button, do not select any land hex. For some reason that sets only two patrol zone points, whereas selecting an ocean hex gives three.

The AI doesn't deal with enemy subs as well as a Human player, so to reduce their impact a bit, Submarine Reaction limits are always kept at "1"

6) Actions/Descriptions for Damaged Units:
The previous version provided comments based on the "after Day 1" state of all units, to include those damaged in the various Japanese attacks. However, even with the "Historical First Turn" toggled on, the results of these attacks are highly variable - especially the list of ships that are sunk, and even the damage levels on those which survive. Some air units can also be damaged so severely as to have few flying airframes. Pearl Harbor is the primary example, but similar issues affect the Philippines, Burma, & Malaya.

Accordingly, the new spreadsheet assumes all units are present and undamaged, but does try to minimize the assignment of actions to those likely to be impacted. You will just have to go through each port and airfield and make adjustments according to the true state of affairs in your game.

7) Historical Game Play Considerations:
WitP-AE is not a simulation, and there is no requirement for the human player to try and "replicate history". However, there are certain areas where I find the game is improved if you impose limitations on what the engine otherwise allows. Specifically:

7a) Several changes to the China Theatre:
Certain Chinese units are no longer recommended to move outside of China (except for possible offensive action against Japanese units in Border zones) unless PPs are paid to release them. Basically it's a gameplay/historical improvement - in particular to avoid the temptation of building up a non-historical defense force in Burma. There is no code which would prevent you from just marching them over the border (for free), but it's a better game if you don't.

Although there's no way to visibly distinguish Red Chinese from Kuomintang forces (to say nothing of trying to separate Chiang Kai Shek loyalists from the Warlords leading their own private armies), I've tried to add some additional realism by not grouping the two together. In sum, the game has some Kuomintang armies in Yenan (the communist capital) while two Red Chinese units occupy Sian (a Kuomintang city). Units that are moving only for this reason will contain a simple notation in the comment column ("Communist" or "Kuomintang"), so players who do not care for this level of granularity can simply ignore the movement instructions given to those units. This was the result of a pretty detailed analysis on the affiliation of in-game units:

7b) ABDA Command:
Historically, the "American-British-Dutch-Australian" command system was not finalized until very late December. Accordingly, the new version of the spreadsheet actively avoids combinations of forces that would not have taken place until that time. So no mixed Naval, Ground, or Air Forces forces from the outset - in particular ANY combination involving the Dutch - until January 1st.

This does not apply to Cargo ships and escorts moving fuel or supply (but it does apply to Troop Transports), or to existing Allies like Britain-Commonwealth-Australia-NZ, nor also the Australian-US strategic cooperation that began very shortly after the Pacific War commenced. More problematic is British-US cooperation. Although already coordinating closely in the Atlantic, the US was very concerned about appearing to support British colonial activities in the Far East, and actively avoided doing so throughout the war. So aside from Naval Task force combinations, the spreadsheet deliberately avoids combined land and air operations involving the US and Britain (AVG being the sole exception, for historical reasons). The same "colonial conundrum" likewise bars the movement of US land forces into Dutch possessions, while British/Australian/NZ land forces never move to Dutch bases as politically those countries were far more concerned about homeland (or "own colony") defenses.

It should also be remembered that the purpose of ABDA was to defend the "Malay Barrier" (which extended through the southern islands of the Dutch East Indies), not facilitate it's evacuation. Accordingly, I've reduced (or added significant "degree of difficulty") to all "Brave Sir Robin" activities. This means:
- Evacuating units from the North Coast of Borneo (or from Hong Kong), now requires British-owned ships, bases, and air transports. No more pull-outs using Dutch or US air and sea units (nor into into Dutch or US bases)
- Likewise, no more buying out of Dutch units for use in Australia or other theatres. Moving them in limited numbers to Dutch-owned bases that are closer to Australia is OK, since that is in accordance with the ABDA charter.
- Although not strictly related to ABDA, the wholesale evacuation of US units from the Philippines has also been significantly curtailed. Gen. Douglas MacArthur commanded this theatre, and historically would NEVER have supported a major reduction in ground forces - quite the contrary, he was pushing for more. Air and Naval units are a different matter, as historically they were pulled out early in the campaign.

Obviously you can choose to ignore all these limitations, but please remember that it's very easy to break the AI early in the war, especially when the human player can use all sorts of historically impossible force combinations ("Fortress Palembang" and "Fortress Java" in particular). You want to have a fun game here, and the best way to do that is to impose the actual historical limits on your options, thereby giving the AI a decent chance to grab the bases it needs to support the Japanese economy in the years to come. Strategies that are totally "OK" against a human player (who can react to them) will strangle the script-driven AI before it even has a chance to get going.

7c) American Pilot Considerations (AVG & Philippines):
The AVG (literally the "All Volunteer Group") was composed of civilians operating under contract, and realistically the player should not treat these pilots like the rest of the American pilot pool. You start the war with 79 pilots assigned to the 3 AVG units in Burma, but since there were originally 99 under contract, it's historically reasonable to add 20 more into the AVG pool.....but no more until such time as the units withdraw. For similar reasons, these units should restrict their movement to bases in Burma and China, as was historically true.

During the early part of the war the US was not able to easily move pilots in or out of the Philippines theatre of operations. Certainly not as easily as one can by using the Pilot replacement buttons. To play as historically as possible, the human player should NOT move veteran pilots in (or out) of these units, at least until the end of December. That would reflect the early war chaos, after which the player should feel free to take whatever actions seem appropriate.

As always, these are only recommendations and the player can choose to operate differently.

NOTE: If you are interested in some eye candy, in April 2020 I completed a mod which provides historically accurate shapes, colors, and camouflage for 135 classes of Japanese ships, primarily Auxiliaries, Transports and Cargo ships. If you are interested in the historical background behind each and every one of these vessels, there's an interesting thread which takes you through the development process. For the mod itself, this link has the download and full installation instructions. As the Allies, you'll only see all the new shipsides when you are attacking a Japanese Task Force, but it does add some spice when your Submarines or Dive Bombers are taking aim at a beautifully camouflaged Japanese vessel!

Looks great, too bad i only play Japanese. I don't think a spreadsheet for that side is in order?
 
Keep it up,
 
d0mbo.
 
 

Outstanding! [&o]

This will be enormously helpful.

Kull wrote: One major element missing from this spreadsheet is the "Arrival Location". Unfortunately that's not captured in the WitP AE Tracker data dump, although it would be very useful!

Actually it is ... but it is in the LCU production area. [;)]

Wow!

Will be taking a close look at this one.

Thanks,

Very nice of you to go to the trouble Kull. Looks quite helpful.

ORIGINAL: Blackhorse

Outstanding! [&o]

This will be enormously helpful.

ORIGINAL: Knavey

Wow!

Will be taking a close look at this one.

Thanks,

ORIGINAL: stuman

Very nice of you to go to the trouble Kull. Looks quite helpful.

Thanks for the supportive comments, guys! [:)] And also to the 72 folks (and counting) who've already downloaded it! As I play further into the game, I'll come back and create revisions if it turns out that some of my force dispositions are....less than optimal! So far it's already obvious that AI-KB's tendency to loiter around Pearl Harbor means that most of my recommended TFs there are little more than suicide missions. Plus, I'm pretty sure that I can improve the West Coast Search & ASW net. Otherwise it seems to be playing out rather well.

Comments or questions are always welcome!

Err, where is the spreadsheet or it's URL?  I'd love to download & look at it, but can't seem to see the handle on any of the Forums quoted...

ORIGINAL: xj900uk

Err, where is the spreadsheet or it's URL?  I'd love to download & look at it, but can't seem to see the handle on any of the Forums quoted...

Check the first post in this thread - I believe the attachment is the file in Q.

Wow this is great thanks, I have spent the last 4-5 days on day 2, and I am only half done this will help immensely.

Cheers

ANy revisions since patch 2 Kull? very nice spreadsheet

Yes Kull, thank you for sharing this with us. It is one of the few things pertaining to AE that I can smuggle on to a government laptop to enjoy on a airline trip. [:)]

All the best!

Excellent tool Kull, thank you for the time and sweat, this spreadsheet will come in real handy.

Check the first post in this thread - I believe the attachment is the file in Q.

Thanx, found it now... [&o]

Kull,

Thanks!  This will be a huge help next time I start a game.

How long did you work on this? [&o]

Canoe "I won't have to do it on the fly now" Rebel

ORIGINAL: Menser

ANy revisions since patch 2 Kull? very nice spreadsheet

Actually, yes! I just finished off a MAJOR revision:

1) Every move was set-up and tested on a new Patch 2 (w/Hotfix) game.

2) Uses the Enhanced Japan Scenario 2 as the baseline instead of Ironman Scenario 9 (S-2 is more likely to be the scenario of choice for new players, which is probably the majority of folks who will use this)

3) Changes include:
- Improved strategy for Chinese LCUs.
- Greater use of Naval Search vs. ASW for "short-range" air units
- Pearl Harbor Naval Unit instructions are listed, but most of the TFs are not created until KB leaves the area - this averts a horrible slaughter on the 8th.
- No PPs are expended, but those which are recommended can be sorted on and counted up (the player can easily make his own changes and follow the count to ensure it's less than 150).
- Accounts for all the new Garrison requirement changes (mostly in China, Burma, & India).
- New strategies involving mines and Air-evac using Transports.
- A wholesales clarification of the recommendations involving "auto-disband" (which only works when the TF arrives at it's home port, NOT the target port!)
- Reinforcements are now "fixed". The previous spreadsheet used the +/-14 day setting, and that meant the spreadsheet was using units which most players won't have in hand for days or weeks, lus the "delay" tabs were completely unreliable.

I'm making a few minor adjustments to make it a bit more "user-friendly", and will try and post it later tonite.

ORIGINAL: Canoerebel

How long did you work on this? [&o]

The first version took over two months to build, and the Patch 2 revision took about another month to finish and test!