|
Article Excerpt
This paper describes our experience constructing a spreadsheet implementation of a dynamic ammunition requirements model for Canadian Army training. Each year, the Canadian Army offers a variety of training courses and expends approximately $40 million in ammunition costs to run these courses. It was budgeting over $60 million, and this created a number of organizational problems. The Canadian Army needed a planning tool to bring budgeted expenditures into line with actual expenditures. Our 14 MB spreadsheet implementation accomplished this objective, and the Canadian Army is very pleased with the result.
Key words: military; logistics; software; system design/operation. History: This paper was refereed.
**********
This paper documents our experience in building a spreadsheet implementation of a dynamic ammunition requirements model for the Canadian Army's individual training program. Each year, over three semesters, the Army offers a variety of individual training courses, and expends approximately $40 million in ammunition costs to run these courses. It was budgeting over $60 million, and this created a number of organizational problems that we will document herein. The Army needed a planning tool to bring budgeted expenditures into line with actual expenditures.
The Army currently has approximately 250 different types of ammunition (or in Army parlance, "natures") and runs more than 120 different training courses. During the year, it runs many sections of the same course; most use a variety of ammunition types. Given that the Army could not forecast the ammunition usage in these courses accurately, and given the dynamic nature of the problem, estimating the requirement for each ammunition type is a significant calculation. Consequently, we believed that the size of the problem would dictate a stand-alone application. However, because Army planners prefer spreadsheets, we decided to explore an Excel implementation. We quickly coded a prototype spreadsheet of approximately the size we would ultimately need, and found that there was no discernible recalculation delay. At the end of the project, we delivered a 14 MB spreadsheet application, the Ammunition Requirements Calculator (ARC). To date, the Army has been very satisfied with the application.
We wrote this paper in three sections. We first describe the nature of the problem, emphasizing the peculiarities of Canadian federal government accounting and finance. Next, we provide details about the model and its spreadsheet implementation. Finally, we comment on the success of the application.
The Problem and Its Origin
In the summer of 2002, Colonel Mike Ward, Commander of the Combat Training Center (CTC) at Camp Gagetown, a major Canadian Army training base, invited us to visit the base to look at several problems. One was ammunition requirements for individual training courses. He had been in his position for about a year, and wanted to address the discrepancy between budgeted and expended amounts of ammunition.
The CTC is responsible for all Army individual training (AIT) conducted at Camp Gagetown. The CTC headquarters staff is responsible for the planning and administration of the AIT courses. It divides the teaching year into three semesters, which it labels summer, fall, and winter. During the 2001-2002 training year, the CTC ran 97 course sections for 2,008 students.
The decision on which sections to offer was difficult and took weeks of staff time. Planning started about a year and a half before the beginning of the fiscal year. The first task was to estimate the course training requirement--that is, the number of sections of each course to be run. This required the staff to do an exhaustive check of a number of Canadian Forces personnel databases. Ultimately, it produced a prioritized list of sections that the CTC could offer. It classified each section into one of 12 priority classes; class 1 was designated as the most important and class 12 as the least important. The assignment of sections to priority classes was based on written guidance from the Chief of the Land Staff.
The budgetary process for AIT ammunition worked like this. Each year, the CTC forwarded an ammunition expenditure request to the Director of Army Training (DAT). Invariably DAT would authorize expenditure levels that were less than what had been requested. Moreover, one of the peculiarities of the system was that the DAT authorization levels were hard constraints. That is, DAT approved a maximum expenditure level, in units, for each ammunition nature, and the CTC could not expend a single round more than what had been authorized. Doing so carried significant risk for the Commander. Hence, given approved expenditure limits for each type of ammunition, the problem was to determine how many courses could be offered without running out of ammunition. The CTC staff referred to this as the problem of "where to draw the line."
For a variety of reasons, CTC staff could not forecast accurately the ammunition requirement for each...
|
