Retention calculator

An email I received from our RRRRRRRRRRRRRRRRRRWESO last night made me think. He asked for retention ideas and stressed: "All comments must be cost neutral".

So, setting aside my belief that what he meant was 'free', I started wondering. I have come up with the attached model. It is a model and I am not interested in comments like 'the daily rate of pay is wrong'. I know - it is just a model. The question is, are the figures it provides believable?

I have made a number of assumptions which can easily be changed (they are in red) around the cost of your training each year to the army, the number of days, the number of Op Tours and the amount of experience they are worth.

Ladies and Gentlemen, your thoughts.

msr
v.2 added - corrected bug with selecting rank as 'Maj'

 

Minor display error with column B4 when 'Major' is selected as rank, to correct just change the range_lookup in the formula to TRUE rather than FALSE. Don't think there's any negative reprecusions from doing so.
Edit: oh look you beat me to it using an IF function, that I couldn't get to work. Bah. Two ways to skin a cat etc.

Function looking at the Bounty paid appears a bit messed up too, look at a gunner in bounty year 2 - should only be a grand paid so far. I'll take a look at the formula and drop a PM.

Edit again: Look, just stop it, as soon as I find a problem you go and fix it before I even finish the post.

Interesting stuff though, it appears we're worth a pretty penny. So how much should be spent on retention? Whatever figure that is it would also increase the cost to replace.

As you pointed out, it would be good to better model the pay fields, taking into account levels and things, pretty complicated though and could always just ask the user to manually input the daily rate though.

 

Thanks for the feedback - do you think the figures it spits out are in the right ball park?

Sorted in v.2

Set Op Tours to zero and you will get his payment. I have estimated that one Op Tour is worth 3 years TA training (so you will have to have someone in for 5 years to replace him) - again, I am prepared to be challenged over this.

msr

 

You could also argue that a Major should have 3 yrs as Lt, 5 years as Capt and 2 as Maj, but given that it is v. difficult to replace a Maj with a Maj, I reckon they are worth more. Plus the model becomes ridiculously complex and I don't think that work will let me spend that sort of time on it.

msr

 

Very interesting, msr!! It looks simple enough, and easy to amend the figures. However, from a bean-counters perspective (I'm not a bean-counter) it doesn't take into account training costs such as provision of instructors (salary and experience), life support, ammunition, provision of unit transport or the individuals MMA/RPOD, etc etc etc. The cited figure in your spreadsheet which is based only on salary and bounty expenditure will always be only a fraction of the true cost of training the individual to their current level of attainment; it's worth keeping that in the bank if you're considering using it as ammo to promote spending on retention.

I've always looked at volunteers in two ways: they either contribute value by being eligible for mobilisation, or they contribute value by training (or recruiting) others to be eligible for mobilisation. (There is a third category, which is having potential to be one of the other two, which obviously applies to recruits and the inexperienced, but also to those who for whatever reason have asked to be counted out for mobilisation purposes, or those who are long-term injured/ill etc.) I've often used the second 'training others' category to lobby for extensions for certain old soldiers who are (self-confessed) 'past-their-prime' when it comes to mobilisation, but who through a combination of dedication, experience, and where necessary up-to-date qualifications, bring huge value to training of others. The problem is that their value in this regard is difficult to quantify, and the 'cost to replace' model is not compelling when you have young talented and enthusiastic guys/girls fresh back from operations with gleaming op inserts, who you and your boss were keeping an eye on with a view to promotion long before they got mobilised.

Not wishing to get sucked into a discussion about the constrained options for how to physically retain such experience within the unit (limitations on slots, option of voluntary reduction in rank, options to transfer to DSTT/BSTT etc etc), it would be interesting to see if someone brighter than me could find a way of quantifying the value of these 'old and bold...but still very bright and bloody useful'. I am firmly of the belief that if they make a positive contribution to the level of training within the unit then they are well worth retaining.

Actually....I had never realised how much it has cost HMG to get me to my current elevated position! Even more scary is the figure it produces for the Troop Staffy I'm just about to lose and the Troop Sgt I've just lost (both on retirement). Add the cost of them two up and you could buy a very nice house in a decent part of London!!