Thomas Slade

The Resource War: Spreadsheet Work

The Resource War: Spreadsheet Work

Perhaps more than any other game, Strategy games are about numbers. Lots and lots of numbers. System design and balance is integral to the genre (which is part of the reason why I like working in it so much), and the primary tool of the system designer (aside from maybe playtesting) is Excel. For The Resource War, I wanted to get some real practice in on spreadsheet work, so I went all out.

To summarise the mod so far; I've added 5 new types of equipment. These are rations (used in most infantry), light munitions (Bullets and stuff. Again: infantry), heavy munitions, anti-tank munitions (which require tungsten), and gasoline. All five are "depletable" - or rather, I've decreased their reliability to make them tick down faster and require some upkeep. They're also "essential" to unit performance; if you have 500/500 guns, but only 5/500 light munitions, your unit is going to function at 1% strength. The 5 depletables are built using two modded resources -grain, copper - and the vanilla tungsten.

While implementing and balancing these new features, I have to answer a lot of questions. How much grain and copper should be on the map? How many factories does it take to produce 1 unit of gasoline in a day? How much copper do munitions use up per factory, per day? How many units of rations are in each battalion of; Infantry? Marines? Motorized Infantry? Mountaineers? ...

Keep in mind the general aims of my mod. Hearts of Iron IV already has some elements of resource dependence, blockading, and shortages, but I want to make it urgent. U-Boating Britain's convoys currently seems to serve as a minor debuff and an annoyance, but I want it to be crushing. Starvation should bring a country to its knees. Additionally, the world's resources should be in truly high demand; currently, I feel that the game has too many resources to go around, meaning that the player/AI almost always has import options. There should be just enough grain on the map to support army sizes in the late game, and that grain certainly shouldn't be within reach of the Axis.

Unit Values

My first step was to arbitrarily set out values for producing rations (how many rations are produced per day by 1 factory?). This was mostly just a guess, since everything else would depend on these values, but I did have one important criterion: I wanted the player to need roughly 1/3rd of the factories on gun production for ration production. That is, I didn't want hundreds of factories producing rations.

UnitSS1.png

Hearts of Iron works with Battalions, which you use to build custom Divisions. Battalions have a pre-defined equipment usage. An Infantry Battalion will use 100 Infantry Equipment. Super Heavy Armour only uses 15 (expensive) Super Heavy Tanks. If you want the actual production cost of a battalion, or even a division, you need to multiply the equipment quantity by its production cost. This is sometimes super important, as you can see in the "Tank BC ic" (Tank Build Cost), where I've determined that even though SH Armours require only 15 tanks, while L Armours require 60, SH battalions cost over 3x as many factories to produce.

Anyway, my main concern for rations was Infantry and Support equipment. I figured these equipment units represent soldiers and officers, all of whom need feeding. So I defined a ratio of guns and medical boxes to rations (red).

UnitSS2.png

Then I calculated how many units of various equipments would be produced per day using 1 factory, 5 factories, and 15 factories. When the rations per day (blue) was roughly 3 times the guns per day (green), that's when my values would produce the factory usage that I was aiming for; for every 3 factories producing guns, you'll want 1 factory on rations.

Resource Usage

Rations require grain. Exactly how much grain, I hadn't figured out yet. What I knew for certain was that there should be just enough grain on the map to sustain the entire world's armies. The USA and USSR should start with a huge surplus of grain (in the corn-belt and the Ukraine, respectively), while the UK, Japan, and especially Germany should be lacking the grain needed to sustain their typical army sizes. But what are the typical army sizes in Hearts of Iron IV?

To find out, I fired up the game and played a quick match as Germany from 1936 to 1940. I tagged between nations (using the console command) counting up the number of divisions (and for good measure, navy and air-force sizes, in case I needed those values later) in two-year intervals. I always took note of the army values for the eight majors - Britain, USA, France, Germany, Italy, Japan, USSR, and China - but took an average out of a sample of minors (Spain, Hungary, Canada, Ethiopia ...) and a sample of minies (any country starting with less than 5 divisions). With this data, I was able to produce an estimate of total army sizes in 1936, '38, and '40.

 

UnitSS3.png
UnitSS4.png

By putting together a typical army breakdown (20% tanks? 10% marines?), I was able to estimate the average number of ration equipments in a division.

UnitSS5.png

Multiply that by typical army sizes each year, add some amount of equipment attrition, and it's easy to determine how much grain will be needed in a given match.

UnitSS6.png

As you can see, it's around 1000 for both Grain and Copper. But I want to explain how unreliable this process is. On my first attempt, my formula for Grain Needed (yellow) was a little less accurate than it should have been. After adding 1000 grain to the map and running through a quick game, I found that there was far, far too much grain available. Shortages simply weren't an issue. So I multiplied the grain usage of Rations by 8, and tweaked my math. On second inspection, it seems to be more accurate (though perhaps now there's too little grain). All the spreadsheets in the world won't replace some playtesting, unfortunately, but my workbook did allow me to quickly fix my mistake and maintain a useful tool for predicting resource consumption. I've yet to give the mod another run-through, but hopefully my copper estimates are pretty good.

It's also pretty easy to determine how much grain each country is going to need, giving me an idea of just how much grain should be in Germany at the start of the game.

UnitSS7.png

As for distributing this grain and copper: map editing is always the fun part! Internet sources on where copper and grain were coming from in WW2 isn't exactly abundant, but before beginning this mod I'd been gathering notes on any tidbits regarding distribution. Aside from the obvious ones (Grain in Ukraine, Ilinois, and the Ganges valley) I found a bunch of good spots from the Wikipedia list on "breadbaskets", and a few important copper mines (the Bor in Serbia, giving Germany some real incentive to invade Yugoslavia).

Resources1.png

So with all this in place, I'm ready to give the mod a proper test-drive (while also getting to work on my horrible placeholder art).

Silkroads: Introducing the Honours Project

Silkroads: Introducing the Honours Project

Hearts of Iron IV Mod: The Resource War

Hearts of Iron IV Mod: The Resource War