Nowadays, I plan my wargame armies using Excel spreadsheets. I started doing this as I expanded my Napoleonic Waterloo set-up into the Peninsula, carried it on as I planned a Roman set-up (not yet built) and again as I planned, and actually started, an 18th Century set-up.
My starting point is Orbats for the various battles of the period. For the 18th Century I have separate Orbat spreadsheets for the Jacobite Rebellion, War of Austrian Succession and French & Indian War with the information taken from various books and on-line.
The Jacobite Rebellion spreadsheet has sheets within it for the various main battles, and some minor ones and sieges, plus some “what if” scenarios. The tabs across the bottom give the idea. The figures in red are to be made, whilst those in black are completed. Many figures and units will appear on more than one sheet, since they were at several battles.
The next stage is to select suitable figures for all of my units. I do this by building up charts of them, using the pictures on Plastic Soldier Review (which I have copied and pasted into the spreadsheet using Paint), and assigning each figure a number. The numbers J-A5, therefore indicates Jacobite Rebellion, Redbox British Infantry, figure number 5.
I then create a spreadsheet of Figures to Units. The first sheet within this is a list of the number of figures which I have, and how many I need.
The “Have Boxed Figures” column calculates automatically on a formula. as does the “Need Boxed Figures” column and the “Grand Totals” column. The column for loose figures shows any from other sets, like Napoleonic French Artillery, left over from before I started this system. There are probably 50 sets listed, many with 10 or more different figures, not only 18th Century, but any Napoleonic ones which I intend using some figures from. The ones you can see are all infantry, but lower down, where there are cavalry sets, I would use colours to indicate riders and horses as below:
This is showing that I need one more set of Swedish Dragoons of Charles XII, which I use for my British Cavalry (and plan to do so for Hanoverian Cavalry in the WAS expansion). The riders are grey and the horses pink on my colour system. I use other colours for artillery equipment, draught horses, drivers etc.
The other sheets in the spreadsheet are used to allocate figures to units. My 18th Century one is relatively simple, with sheets for British Army & Allies, Jacobite Army and French Army. It also has an Artillery Reconciliation because the Jacobites use artillery models and train horses from the British and French Armies. My Napoleonic spreadsheet is much more complex, with sheets for Staff & Infantry, Cavalry and Artillery, Engineers & Logistic within each of British & Allies, French & Allies and Prussian. It also has sheets for Austrian and American.
An extract from the Jacobite Army sheet is shown here:
The different figures are listed by their codes on the left of the sheet with the Commanders, Staff and Units across the top. The sheet has had its frames frozen so that columns A, B & C are always visible, as are Rows 1-7.
The number of figures available (column C), which includes those planned to be purchased, is pulled automatically by formula from the Figure Summary sheet. The columns with the titles highlighted in red are already completed, the next ones to be tackled are orange, then yellow, green, pale blue, mid blue and dark blue (the latter not shown), in other words a rainbow system. As figures get completed, then I move the colours on. The columns highlighted in pale pink have been sorted, but not completed, probably because those units were involved in headswaps, so are now in a box of “sorted figures” in separate plastic bags. The bottom row of the coloured heading shows the number of figures planned to be in that unit. The sheet above shows some of the allocation of RedBox Loyalist & Militia figures to Jacobite Lowland units. The individual figure highlighted brown is because he is destined to become a drummer (pipers would be mauve) and officers (off the bottom of that page) are orange. Many of my command figures are conversions, so this helps to remind me which ones I plan for which role.
The bottom of that same page shows totals, in a number of categories by type of figure.
By comparing the planned number (row 7) against the actual numbers (rows 456-459), it is possible to check that enough figures have been allocated to make units the right size. The totals are being automatically added by formula, and the various row colours (eg grey for mounted and pink for ridden horses) assists in that. Every time there is a mounted figure (row 457) there should be a horse for him to ride (row 459). I do mix up horses and riders a lot, so that is necessary.
The right hand side of the sheet has a number of totals columns.
Column CB shows the total number of figures in each row used by the Jacobite Army. Column CC shows how many of those same figures are used by the British Army (and those figures are pulled automatically from the British sheet). The French and Napoleonic columns are blank, since none of those particular figures are used by those armies, but for some rows there would be figures in there, again automatically pulled from their respective sheets. Column CF gives the Grand Total of figures used for each row, and Column CG gives any spare (or deficit in brackets) by subtracting the number used (Column CF) from the number available (Column C). If there are any deficits, then I need to make adjustments by moving figures around, or plan to buy another box (which would change the Figure Summary figures). As I start to plan these armies there are quite a lot of adjustments to get it right.
The British Army & Allies sheet has a lot of columns, since I expanded the original British Army for Prestonpans, Falkirk and Culloden, to also include plans for the British Army units defending England, plus Hessians, and then into their Hanoverian, Dutch and Austrian allies for the War of Austrian Succession, and finally into units for the French & Indian War.
Finally, I plan a schedule of when I will model and paint these figures. A typical one is below:
October is blank, because I am still finishing off my Siege Works until the end of that month. As I complete each batch, I will change the bar from blue to red. I have found from experience that it is best not to plan more than six months ahead, otherwise any slippage means more work to change a lot of entries. I have allowed 3 weeks for each batch, which is normally about right. This shows my Jacobite Army being finished by March 2017.
It may seem like a lot of extra work, but it keeps my computer skills alive and keeps my brain busy, quite apart from ensuring that my purchases of figures are very cost effective.
Rod's Wargaming 