The "Japan Airframe and Engine Planner" spreadsheet

Share your gameplay tips, secret tactics and fabulous strategies with fellow gamers here.

Moderators: wdolson, MOD_War-in-the-Pacific-Admirals-Edition

User avatar
Kull
Posts: 2645
Joined: Tue Jul 03, 2007 3:43 am
Location: El Paso, TX

The "Japan Airframe and Engine Planner" spreadsheet

Post by Kull »

For the last month, I've been developing the Japanese counterpart to the "AE-Allied Setup Spreadsheet". The degree of difficulty when moving from the Allies to Japan can be counted in orders of magnitude, and one of the most important involves managing airframe and engine production. As many of you know from hard experience, working through the multitude of options, all inter-related, is almost enough to make your head explode. To avert that, I began to develop a spreadsheet that would help me evaluate the various production options off-line, and was also flexible enough to run every what-if scenario imaginable AND could be easily tailored for any starting scenario or modification. The result is something I find to be extremely useful, and hopefully you will find it so as well. From the Instructions section:

The "Japan Airframe and Engine Planner" spreadsheet is designed to be VERY modifiable:

1) You can add new rows, but they must be inserted in the correct range: Airframe factories (rows 3 thru 98), Engine factories (rows 99 thru 118), and Engine pools (rows 119 thru 136). Do not "insert row" when highlighting the top row of each section as the formulas will not expand to include this new row - highlight anywhere else in the section and you'll be fine. Each section is color coded to assist in differentiation.

2) If you want more data columns, feel free to add as many as you like. You could even delete or change any of those included in this file, with the exception of Column K (Engine Name), Column L (Engine Count) and Column N (Starting Engine Pool). If you are inserting columns, do not add any between "Starting Engine Pool" (N) and "12/41" (O). That would make the formula in Column O different from all the other data formulas, which is a bad idea.

3) The key section is column K, "Engine Name". The formula reference is performed in the "engine pool" range, so if you change a name here (to include creating a brand new one) the aircraft & engine factory sections will "look for it" automatically. It's that simple.

4) You can change data in any of the existing rows and the spreadsheet won't care. If row 3 changes from an airframe requiring the Nakajima Ha-35 to one requiring the Mitsubishi Ha-31 (or anything else), the pool calculation for that engine will now "see" it and account for it.

5) The important numbers for calculating engine pools are; A) Engine Numbers per airframe (Column L), B) Starting Pool (Column N), and C) Airframe & Engine Production per month (Columns O-BN).

6) Airframe & Engine Factory monthly production (rows 3 thru 118) have formulas that use the number from the previous cell. You can override the formulas with manual entries and the range is conditionally formatted to show non-formulas as red cells (so you can easily see where you made changes).

7) Engine pool monthly counts (rows 119 thru 136) are conditionally formatted to change the font and the cell color whenever the pool dips into negative numbers.

8) The only thing you cannot do (without editing formulas) is add an airframe that needs other than 1, 2, or 4 engines - fortunately the odds of that are pretty slim (not a lot of 3 or 6 engine planes in this theatre).

NOTE: The conditional formatting described in item #6 above is based on an Excel Macro, so you will have to "enable macros" in order for it to work. This is HIGHLY recommended, otherwise all data in the Range O3:BN118 will be a garish red. If for some reason you prefer not to use macros (or cannot), then just highlight that whole range and set the color to "no fill". Again, this is not recommended since you lose the visual indicator which shows which cells have had formulas overwritten. (Engine Pool conditional formatting operates with or without macros enabled.)
Attachments
JapanAirf..Planner.zip
(78.34 KiB) Downloaded 1768 times
User avatar
Kull
Posts: 2645
Joined: Tue Jul 03, 2007 3:43 am
Location: El Paso, TX

RE: The "Japan Airframe and Engine Planner" spreadsheet

Post by Kull »

Here's what the "Japan Airframe & Engine Planner" spreadsheet looks like, and this is what each column tells you:

Col A: Counter - This is the "row count". When using a filter button. Let’s say you wanted to work with rows that use the "Mitsubishi Ha-31" engine - the counter in Cell A2 tells you how many rows of data you are working on (Mitsubishi Ha-31 = 6).
Col B: Type - The type of factory at game start (either Production or Research).
Col C: Role - The Combat Role of each aircraft (F=Fighter, FF=Float Fighter, NF=Night Fighter, FB=Fighter Bomber, DB=Dive Bomber, TB=Torpedo Bomber, LB=Level Bomber, T=Transport, S=Search/Recon)
Col D: Carrier Capable - Yes or No
Col E: Military Branch - IJA=Japanese Army, IJN=Japanese Navy
Col F: Unit Name - The name of each air unit.
Col G: Airframe - The Model/Type of each air unit.
Col H: Location - Location of the Airframe or Engine factory.
Col I: Factory Production - amount of Repaired Production capacity.
Col J: Factory Production - amount of Unrepaired Production capacity.
Col K: Engine Name - Self Explanatory (Perhaps the most important field in the spreadsheet.)
Col L: Engine Number - The number of engines required for each airframe of this type.
Col M: Start - Date on which the factory can begin producing the indicated item.
Col N: Pool - Quantity of engines in the pool at game start. (Also has starting aircraft pools, but those are purely informational.)
Col O-BN: Production Months

In the example shown below, I've filtered the spreadsheet so we are looking only at data for two engine types, the "Hitachi (Early)" and the "Mitsubishi Ha-32". As you can see from the engine pool calculations (bottom two rows), we have a sizable pool of Hitachis (and really don't need any more), but we are about to go negative on the Mitsubishi. The latter is a real problem, since it's a critical component of the G4M1 Betty, an important long range bomber with torpedo capability. Even worse, there is only 1 Betty in the starting aircraft pool! You need to do something, and fast, but what?

Image
Attachments
JapanPlanner1.jpg
JapanPlanner1.jpg (130.32 KiB) Viewed 947 times
User avatar
Kull
Posts: 2645
Joined: Tue Jul 03, 2007 3:43 am
Location: El Paso, TX

RE: The "Japan Airframe and Engine Planner" spreadsheet

Post by Kull »

(Edit: As Seille notes in Post #8 below, the previous example assumed that factories repair at 1 point per month instead of 1 point per day - quite the "duh" moment by yours truly. Anyway the text and accompanying graphic have been completely revised.)

**********************************************************************************************************************

In the example below, I've made several easy changes to the engine and airframe production, and the crisis is averted. Best of all, the spreadsheet EASILY lets you try out plenty of "what-if" alternatives. Let's see what I did (the numbers listed below in the post text are included in the spreadsheet graphic so you can see specifically where I am changing or referring to data):

1) As noted in Post #2 above, the "Hitachi (Early)" engine factory isn't required any longer (the pool of engines is huge and it's not producing anything of real value) so we'll convert the Tokyo factory to "Mitsubishi Ha-32" engine production. Key Point: You do not have to change any formulas in the pool section. Just change the engine name (at any aircraft or engine factory), and the formulas adjust automatically. Easy, yes?

2) With the factory gone, there's no sense wasting the Hitachi engines in the pool, so we'll also halt production of the Ida, an obsolete single engine bomber.

3) Assuming that factories repair at the rate of roughly one point per day, I have manually changed the engine forecast for the converted Tokyo plant to 23 for the first month (the number of days left in December), and 40 for the second month (the factory will be repaired and at full production by mid-January). Note: Whenever a formula is overwritten with a number, the cell color changes to red, which is why the December and January production numbers for this factory appear in that color. This is useful if you are trying a series of “what-if” scenarios since it shows which cells need to have the formulas replaced, should you decide to test a different approach.

4) The result (see second row from the bottom) is that we now have plenty of engines to keep Betty production humming along at the existing level of 25 per month. And by testing the "what-if" capability, you'll see we can even boost it a little (Not shown - try this at home and you'll see that a small boost to 27 airframes per month keeps production "in the black" until September 1943).

5) Speaking of "what-if" capability, you can easily run another test from the same setup, and it shows that you don't have to convert the Hitachi factory after all! One can get much the same result (with far fewer changes to the production structure by doing the following:
- The Sally is still a useful Level Bomber at this point in the game, but it has 20 planes in the pool, so you can afford to slow production. Turn the Sally factory off and on for several months (December=0, January=23, February=0, March=23, and finally April=0), ultimately leaving it off altogether as the Helens begin to arrive.
- Increase production at the Nagoya plant by 3 (December production increases from 60 to 63)
- And that's it. The data in the last row proves that these simple changes ensure enough engines are available to keep the Betty in full production, and even begin building an engine surplus from March 1942 onward.

Hopefully this little tour gives you some sense of the features and perhaps it can assist you in making good aircraft and engine production decisions going forward. Always keep in mind that increases in factory capacity are not "free", and wholesale changes can be quite dangerous to the Japanese economy. After testing out options using the spreadsheet and then making those changes in-game, be sure to use the WitP Tracker program and see what the long term impacts are with respect to HI needs as well as fuel & supply consumption.

Image
Attachments
JapanPlanner3a.jpg
JapanPlanner3a.jpg (139.73 KiB) Viewed 863 times
User avatar
Kull
Posts: 2645
Joined: Tue Jul 03, 2007 3:43 am
Location: El Paso, TX

RE: The "Japan Airframe and Engine Planner" spreadsheet

Post by Kull »

Reserve for version change descriptions
User avatar
PaxMondo
Posts: 9749
Joined: Fri Jun 06, 2008 3:23 pm

RE: The "Japan Airframe and Engine Planner" spreadsheet

Post by PaxMondo »

Wow.  Gotta look and play with this!
 
[8D]
Pax
User avatar
Quixote
Posts: 773
Joined: Thu Aug 14, 2008 5:34 pm
Location: Maryland

RE: The "Japan Airframe and Engine Planner" spreadsheet

Post by Quixote »

Thanks Kull - after playing with it for (admittedly only) a short period of time, this looks ideal for Japanese planning. Thanks for sharing!
User avatar
Kull
Posts: 2645
Joined: Tue Jul 03, 2007 3:43 am
Location: El Paso, TX

RE: The "Japan Airframe and Engine Planner" spreadsheet

Post by Kull »

I realize there's a LOT of words on those three posts, but this thing is REALLY easy to use:

- Want to see what will happen if three engine factories switch from type X to type Y AND several Airframe factories are changed at the same time? Easy.
- Curious about the impact on engine pools if you turn production of various airframes on and off over a series of months? Piece of cake.
- Need a customized tool for any mod or scenario in order to add new airframes or engines into the mix? You can add, change, or delete rows to your hearts content, and the formulas will still work perfectly.

The most time consuming task is typing in forecast numbers which show the steady increase in output as repairs take place after a change - and that is seconds or minutes at the most. And easy to undo, since manual entries are blindingly obvious and you can replace them with any one of the formulas in the O3:BN118 range.
seille
Posts: 2048
Joined: Tue Jun 19, 2007 8:25 am
Location: Germany

RE: The "Japan Airframe and Engine Planner" spreadsheet

Post by seille »

@Kull

I don´t think your point 4 is right. Factories (especially engine facs) don´t repair one point per month. It is one point per day as long as enough supply is there.
Wondered about the HA-32 facs at Nagoya with 60 working facs and 6 repairing. Engine output on your screen is increased by one per month.
The 6 damaged facs would be repaired in december already.

Or do i just not understand how this excel sheet works ?
User avatar
Kull
Posts: 2645
Joined: Tue Jul 03, 2007 3:43 am
Location: El Paso, TX

RE: The "Japan Airframe and Engine Planner" spreadsheet

Post by Kull »

You are quite correct. Fortunately that is a manual entry, so it's not a problem with the spreadsheet, just the creator!

(But when I get some time later, I'll go back and revise the examples so they aren't misleading, thanks!)

Edit: Post #3 fixed to show correct repair rates.
User avatar
SuluSea
Posts: 2359
Joined: Fri Nov 17, 2006 2:13 pm

RE: The "Japan Airframe and Engine Planner" spreadsheet

Post by SuluSea »

Super job on this Kull. Thanks for sharing it with us! [:)] [:)]
"There’s no such thing as a bitter person who keeps the bitterness to himself.” ~ Erwin Lutzer
User avatar
fodder
Posts: 2159
Joined: Sat Apr 10, 2010 9:27 pm
Location: Daytona Beach

RE: The "Japan Airframe and Engine Planner" spreadsheet

Post by fodder »

Thanks Kull, this will be a great help. [:)]
Image
User avatar
Kull
Posts: 2645
Joined: Tue Jul 03, 2007 3:43 am
Location: El Paso, TX

RE: The "Japan Airframe and Engine Planner" spreadsheet

Post by Kull »

ORIGINAL: PaxMondo

Wow.  Gotta look and play with this!

[8D]
ORIGINAL: Quixote

Thanks Kull - after playing with it for (admittedly only) a short period of time, this looks ideal for Japanese planning. Thanks for sharing!
ORIGINAL: SuluSea

Super job on this Kull. Thanks for sharing it with us! [:)] [:)]
ORIGINAL: fodder

Thanks Kull, this will be a great help. [:)]

Thanks guys! Any feedback would be much appreciated. (i.e. comments on ease of use, suggested changes, anything that's not clear, etc.)
User avatar
Lokasenna
Posts: 9300
Joined: Sat Mar 03, 2012 3:57 am
Location: Iowan in MD/DC

RE: The "Japan Airframe and Engine Planner" spreadsheet

Post by Lokasenna »

Finally getting around to checking this out for possible use (got some major production planning to do if I'm gonna start a new IJ game after I -hopefully- conclude my first), looking forward to it!

Also, this thread doesn't deserve to be anywhere but on the first page.
User avatar
inqistor
Posts: 1813
Joined: Wed May 12, 2010 1:19 pm

RE: The "Japan Airframe and Engine Planner" spreadsheet

Post by inqistor »

Heh, I am wondering how could I have missed that useful tool earlier... GOOD WORK!
ORIGINAL: Kull

3) Assuming that factories repair at the rate of roughly one point per day, I have manually changed the engine forecast for the converted Tokyo plant to 23 for the first month (the number of days left in December), and 40 for the second month (the factory will be repaired and at full production by mid-January). Note: Whenever a formula is overwritten with a number, the cell color changes to red, which is why the December and January production numbers for this factory appear in that color. This is useful if you are trying a series of “what-if” scenarios since it shows which cells need to have the formulas replaced, should you decide to test a different approach.
Indeed, they repair at 1 per day, but they also produce daily from current size. So it MAY BE 23 at END OF MONTH, but production will be much lower during month.

(This is for all fans of quick conversions from "obsolete" aircraft/engines):
Converted factory begins at 0, and it repairs at 1 per day. So at first day it will be only size 1, and have 1/30 chance to actually produce anything. Next day it will be size 2, and chance will be 2/30. On the last day of December it will be size 24 (31 days in month).

Adding lowest, and highest numbers: 25*12=300
Converting it to chances: 300/30=10 - this is average number produced during December, if factory begins at 0. ONLY 10 planes/engines!
User avatar
Empire101
Posts: 1950
Joined: Tue May 20, 2008 2:25 pm
Location: Coruscant

RE: The "Japan Airframe and Engine Planner" spreadsheet

Post by Empire101 »

ORIGINAL: inqistor

Heh, I am wondering how could I have missed that useful tool earlier... GOOD WORK!

+1

I missed it too.
Thanks Kull
[font="Tahoma"]Our lives may be more boring than those who lived in apocalyptic times,
but being bored is greatly preferable to being prematurely dead because of some ideological fantasy.
[/font] - Michael Burleigh

User avatar
Treetop64
Posts: 928
Joined: Tue Apr 12, 2005 4:20 am
Location: 519 Redwood City - BASE (Hex 218, 70)

RE: The "Japan Airframe and Engine Planner" spreadsheet

Post by Treetop64 »

Looks very handy! This is almost enough to make me wanna actually play the Japanese side. [:D]
Image
User avatar
bigred
Posts: 3710
Joined: Thu Dec 27, 2007 1:15 am

RE: The "Japan Airframe and Engine Planner" spreadsheet

Post by bigred »

I use this for RA70 mod.
photo_view.asp?file=0;2617283

I wanted to see exactly what planes need what engines. Or to be more precise, what engines go to which planes. I also wanted to define what I wanted to get rid of.
This chart was my beginning tool. My production strategy changed as I became more educated on this subject.
Damian and other more experienced players just look at the numbers on tracker and figure it out. I needed yellow color code to help me see the engine changes as each model upgrades.

Image


This is some of my changes as listed at post 255:
1. Ki45nick-increased frames from 13to15.
2. D4Y1 judy-R/D to 30
3. D4Y4 judy r/D to 30(look at the payload***)
4. Ki27 airframes turned on.
5. Ki49a Helen set to 30 per month. I may increase this later.
6. Ha31 engine increase to 70.(current 40 plus 30 per month for the Nick, may also increase this later but nick has only 5 sqns-3 in training).
7. Changed ha5 to Ha34 and expanded to 30.
8. Kotobuki change to Ha34 and expand to 25.
9. amakaze change to Ha34 and expand to 30.
10. Increased current Ha34 from 10 to 30.
11. Increased r/d on Tojo to 30.
12. Converted f1m2 pete to Ki44 tojo an increased r/d to 30.
13. Increased r/d on ki84 from 56 to 60
14. Increased r/d on ki43 to 30(probably a mistake)
15. Tojo plan starting for 60 and 30 helens(2x engines) means I will need 120 Ha34 per month. I may have to increase plant size later.


Attachments
a1.jpg
a1.jpg (152.98 KiB) Viewed 847 times
---bigred---

IJ Production mistakes--
tm.asp?m=2597400
User avatar
btd64
Posts: 11227
Joined: Sat Jan 23, 2010 12:48 am
Location: Mass. USA. now in Lancaster, OHIO

RE: The "Japan Airframe and Engine Planner" spreadsheet

Post by btd64 »

BUMP....GP
Intel i7 4.3GHz 10th Gen,16GB Ram,Nvidia GeForce MX330

AKA General Patton

DWU-Beta Tester
TOAW4-Alpha/Beta Tester
DW2-Alpha/Beta Tester

"Do everything you ask of those you command"....Gen. George S. Patton
User avatar
Jellicoe
Posts: 163
Joined: Wed Sep 26, 2012 5:00 pm
Location: Kent, UK

RE: The "Japan Airframe and Engine Planner" spreadsheet

Post by Jellicoe »

Thank you Kull

Your spreadsheets are a total boon to those of us who are still somewhat intimidated by Japanese set up and industry but would really like not to be.

Still learning this beast and will now feel able to have a stab at becoming a JFB on the back of these [&o]
User avatar
Kull
Posts: 2645
Joined: Tue Jul 03, 2007 3:43 am
Location: El Paso, TX

RE: The "Japan Airframe and Engine Planner" spreadsheet

Post by Kull »

Glad that it's helpful! I played the Allies for years, and going to the Dark Side is a huge challenge, but also a ton of fun. One area the Airframe/Engine Planner can really help with is the long range strategy to speed up research, get and maintain the 500 engine bonus, and just make sure you aren't overproducing airframes or engines of a particular type.

There are so many interlocking variables that it's hard to plan air and engine requirements over the long term, but the spreadsheet helps a lot. The easy "what-ifs" are also useful...much better to garner your "oh hell, that won't work" experiences virtually!
Post Reply

Return to “The War Room”