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.

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).

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.)

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?

(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.

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.

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.

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

quote:

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!

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.

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.