ORdinary Spreadsheets and ORdnances

The July-August 2008 Interfaces Journal ran a theme of "The Use of Spreadsheet Software in the Application of Management Science and Operations Research". In their article from that issue, "A Spreadsheet Implementation of an Ammunition Requirements Planning Model for the Canadian Army", Hurley and Balez describe a successful spreadsheet model for planning training ammunition expenditures.

Ammunition is expended in training courses in a highly uncertain environment. Course registration rates, failure rates (before completing the entire course), and other uncertainties make it difficult to accurately forecast ammunition consumption. Planners must choose a course portfolio that will not result in an ammunition shortage. Of course, to minimize the chances of running out of ammo, planners to date had been planning to the maximum expenditure per course. The consequence of this was that the program came repeatedly under budget, 38.7% in 2002-03. This is far from ideal when attempting to allocate scare resources. Naturally this was an opportunity to apply risk management principles in order to either request a smaller budget to accomplish the same goals or to do more with the same budget.

The solution took the form of a spreadsheet tool. The Excel spreadsheet combined with Visual Basic for Applications (VBA) provided an easy and inuitive interface for planners to interact with the risk model. As a result, in 2004-05 the program was only 3.1% under budget. I will not go into too many more details as they are available in the article, but I had two interesting thoughts:

[1] A big advantage of using spreadsheets is the familiarity most managers have with it. Leveraging this, the team built a simple spreadsheet simulation to demonstrate the portfolio effect of running several courses. With repeated "F9-Simulations" (my term) they were able to demonstrate that while 10 course sections will never use 10 times the maximum (as presently budgeted), it is actually reliably much less than this. Moving up a level and using @Risk to run 10,000 simulations they were able to convincingly demonstrate the concept.

We cannot overemphasize the value of this type of spreadsheet demonstration in selling the potential of an OR model.

Interestingly enough their experience differs from my own. I tried to convince an utlrasound department supervisor that if average-45-minute appointments of uncertain lengths are booked every 45 minutes, her technologists would reliably work overtime. To do this I built a simple spreadsheet simulation, but it was totally lost on her. This is not meant as a knock against this approach, but rather to emphasize the importance of manager familiarity with spreadsheets. My ultrasound supervisor as a senior medical radiation technologist thinks differently from a rising Canadian Colonel.

[2] When selecting a portfolio of courses to fit the approved budget (less than requested), the Army chose to manually optimize using the tool rather than accept a priority-optimized result from a linear program. This perplexed the authours and I think they wrongly blamed themselves for a failure to achieve buy-in. It is my experience that when dealing with problems of a magnitude that an individual can wrap their heads around, clients prefer to leverage their intuition and optimize by hand. As OR practitioners we may not trust the client to acheive a truly optimal result, but as a client they do not trust a model to capture all of the nuances they know inuitively and the answer, of course, is somewhere in between.

The idea of doing OR with Excel probably wasn't what got you started in the field, but if you like seeing results it might just keep you in it.

Hurley, W.J., Balez, Mathieu. 2008. A Spreadsheet Implementation of an Ammunition Requirements Planning Model for the Canadian Army. Interfaces 38(4) 271-280