Matrix Games Forums

New information and screenshots for Pike & ShotDeal of the Week Pride of NationsTo End All Wars Releasing on Steam! Slitherine is recruiting: Programmers requiredPandora: Eclipse of Nashira gets release dateCommunity impressions of To End All WarsAgeod's To End All Wars is now availableTo End All Wars is now available!Deal of the Week: Field of GloryTo End All Wars: Video, AAR and Interview!
Forums  Register  Login  Photo Gallery  Member List  Search  Calendars  FAQ 

My Profile  Inbox  Address Book  My Subscription  My Forums  Log Out

The "Japan Airframe and Engine Planner" spreadsheet

 
View related threads: (in this forum | in all forums)

Logged in as: Guest
Users viewing this topic: none
  Printable Version
All Forums >> [New Releases from Matrix Games] >> War in the Pacific: Admiral's Edition >> The War Room >> The "Japan Airframe and Engine Planner" spreadsheet Page: [1]
Login
Message << Older Topic   Newer Topic >>
The "Japan Airframe and Engine Planner" sprea... - 10/21/2012 9:59:50 PM   
Kull


Posts: 1057
Joined: 7/3/2007
From: El Paso, TX
Status: offline
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.)

Attachment (1)

_____________________________

AE-Japan Setup

AE-Allied Setup

Japan Air-Engine Plan
Post #: 1
RE: The "Japan Airframe and Engine Planner" s... - 10/21/2012 10:00:31 PM   
Kull


Posts: 1057
Joined: 7/3/2007
From: El Paso, TX
Status: offline
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?




Attachment (1)

< Message edited by Kull -- 10/21/2012 11:03:58 PM >


_____________________________

AE-Japan Setup

AE-Allied Setup

Japan Air-Engine Plan

(in reply to Kull)
Post #: 2
RE: The "Japan Airframe and Engine Planner" s... - 10/21/2012 10:02:39 PM   
Kull


Posts: 1057
Joined: 7/3/2007
From: El Paso, TX
Status: offline
(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.




Attachment (1)

< Message edited by Kull -- 10/23/2012 6:59:49 AM >


_____________________________

AE-Japan Setup

AE-Allied Setup

Japan Air-Engine Plan

(in reply to Kull)
Post #: 3
RE: The "Japan Airframe and Engine Planner" s... - 10/21/2012 10:03:24 PM   
Kull


Posts: 1057
Joined: 7/3/2007
From: El Paso, TX
Status: offline
Reserve for version change descriptions

_____________________________

AE-Japan Setup

AE-Allied Setup

Japan Air-Engine Plan

(in reply to Kull)
Post #: 4
RE: The "Japan Airframe and Engine Planner" s... - 10/22/2012 4:45:15 AM   
PaxMondo


Posts: 5718
Joined: 6/6/2008
Status: online
Wow.  Gotta look and play with this!



_____________________________

Pax

(in reply to Kull)
Post #: 5
RE: The "Japan Airframe and Engine Planner" s... - 10/22/2012 5:50:03 AM   
Quixote


Posts: 736
Joined: 8/14/2008
From: Maryland
Status: offline
Thanks Kull - after playing with it for (admittedly only) a short period of time, this looks ideal for Japanese planning. Thanks for sharing!

(in reply to PaxMondo)
Post #: 6
RE: The "Japan Airframe and Engine Planner" s... - 10/22/2012 7:19:01 AM   
Kull


Posts: 1057
Joined: 7/3/2007
From: El Paso, TX
Status: offline
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.

< Message edited by Kull -- 10/22/2012 7:20:44 AM >


_____________________________

AE-Japan Setup

AE-Allied Setup

Japan Air-Engine Plan

(in reply to Quixote)
Post #: 7
RE: The "Japan Airframe and Engine Planner" s... - 10/22/2012 8:57:57 AM   
seille

 

Posts: 2123
Joined: 6/19/2007
From: Germany
Status: offline
@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 ?

(in reply to Kull)
Post #: 8
RE: The "Japan Airframe and Engine Planner" s... - 10/22/2012 3:27:44 PM   
Kull


Posts: 1057
Joined: 7/3/2007
From: El Paso, TX
Status: offline
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.

< Message edited by Kull -- 10/23/2012 2:55:17 AM >


_____________________________

AE-Japan Setup

AE-Allied Setup

Japan Air-Engine Plan

(in reply to seille)
Post #: 9
RE: The "Japan Airframe and Engine Planner" s... - 10/23/2012 12:35:39 AM   
SuluSea


Posts: 2071
Joined: 11/17/2006
Status: offline
Super job on this Kull. Thanks for sharing it with us!


_____________________________


”How strangely will the Tools of a Tyrant pervert the plain Meaning of Words!” ~ Samuel Adams

(in reply to Kull)
Post #: 10
RE: The "Japan Airframe and Engine Planner" s... - 10/23/2012 4:40:39 AM   
fodder


Posts: 1845
Joined: 4/11/2010
From: Daytona Beach
Status: offline
Thanks Kull, this will be a great help.

_____________________________


(in reply to SuluSea)
Post #: 11
RE: The "Japan Airframe and Engine Planner" s... - 10/23/2012 5:19:23 PM   
Kull


Posts: 1057
Joined: 7/3/2007
From: El Paso, TX
Status: offline
quote:

ORIGINAL: PaxMondo

Wow.  Gotta look and play with this!



quote:

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!

quote:

ORIGINAL: SuluSea

Super job on this Kull. Thanks for sharing it with us!


quote:

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

_____________________________

AE-Japan Setup

AE-Allied Setup

Japan Air-Engine Plan

(in reply to fodder)
Post #: 12
RE: The "Japan Airframe and Engine Planner" s... - 1/15/2013 11:40:10 PM   
Lokasenna


Posts: 2733
Joined: 3/3/2012
From: Iowan in MD/DC
Status: offline
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.

(in reply to Kull)
Post #: 13
RE: The "Japan Airframe and Engine Planner" s... - 1/19/2013 8:29:37 AM   
inqistor


Posts: 1332
Joined: 5/12/2010
Status: offline
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!

(in reply to Kull)
Post #: 14
RE: The "Japan Airframe and Engine Planner" s... - 1/19/2013 2:24:02 PM   
Empire101


Posts: 1957
Joined: 5/20/2008
From: Coruscant
Status: offline

quote:

ORIGINAL: inqistor

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



+1

I missed it too.
Thanks Kull


_____________________________

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.
- Michael Burleigh


(in reply to inqistor)
Post #: 15
RE: The "Japan Airframe and Engine Planner" s... - 1/19/2013 3:26:56 PM   
Treetop64


Posts: 798
Joined: 4/12/2005
From: 519 Redwood City, CA - BASE (Hex 218, 70)
Status: online
Looks very handy! This is almost enough to make me wanna actually play the Japanese side.

_____________________________



"Junk is something you've kept for years and throw away three weeks before you need it"

(in reply to Empire101)
Post #: 16
RE: The "Japan Airframe and Engine Planner" s... - 1/20/2013 11:08:58 PM   
bigred


Posts: 2881
Joined: 12/27/2007
Status: online
I use this for RA70 mod.
http://www.matrixgames.com/forums/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.





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.




Attachment (1)

< Message edited by bigred -- 1/20/2013 11:15:37 PM >


_____________________________

---bigred---

IJ Production mistakes--
http://www.matrixgames.com/forums/tm.asp?m=2597400

(in reply to Treetop64)
Post #: 17
Page:   [1]
All Forums >> [New Releases from Matrix Games] >> War in the Pacific: Admiral's Edition >> The War Room >> The "Japan Airframe and Engine Planner" spreadsheet Page: [1]
Jump to:





New Messages No New Messages
Hot Topic w/ New Messages Hot Topic w/o New Messages
Locked w/ New Messages Locked w/o New Messages
 Post New Thread
 Reply to Message
 Post New Poll
 Submit Vote
 Delete My Own Post
 Delete My Own Thread
 Rate Posts


Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI

0.094