SD Military Recruitment Example
Page Summary
This page…. This page covers just the basics.
Related Pages

Overview

Stock and Flow models (related to a general area of modeling called Systems Dynamics models) are essentially sets of linked difference equations. We'll see how to build a simple stock and flow model (and to draw a diagram of it) using Excel.

Example One

In S&Z we read the example of an army trying to balance its use of training facilities and to achieve a particular size without extreme fluctuations over time. We'll start with a simple implementation of the model as described in the book.

  1. Tour of duty lasts 4 time periods
  2. Fifty percent reenlist for another tour at end of their fourth period.
  3. At time zero we have
(1)
\begin{equation} S_1(0) = 500,000 \end{equation}
(2)
\begin{equation} S_2(0) = 100,000 \end{equation}
(3)
\begin{equation} S_3(0) = 100,000 \end{equation}
(4)
\begin{equation} S_4(0) = 100,000 \end{equation}
  1. Assume that we have 100% retention from phase 1 to phase 2, 2 to 3, and 3 to 4.
  2. Some number, R(t), new trainees are recruited during any given time period '''t''' and they move into phase 1 during time period '''t+1'''.

Instructions

1. Open a new worksheet
1. Starting in row 5 (we'll leave some space above for parameters) enter column headings as in table 4-4 on page 70 of the text:

stock-and-flow-01.gif

1. We will have two parameters: # of traineesper period, rate of reenlisting. In cells B2 and B3 type names for these and then put initial values in C2 and C3

stock-and-flow-02.gif

1. Name these cell ranges. Right click on the numerical value and select the command "Name a Range…" Excel will use the text to the left of the cell as a default name. You can modify as you like.
1. Put 0, 1, 2 in the first few time periods in the first column and use autofill to complete the series down to, say, t=20.
1. Insert zero in the cells representing trainees and reenlists at t=0.
1. Insert initial values given above for $S_1$ through $S_4$.
1. Write a formula for Total in Army(0) that is the sum of Trainees(0), renlists(0), $S_1(0)$ through $S_4(0)$
1. Think for a second about where each of the following are going to come from
a. Trainees(1)
a. Reenlists(1)
a. $S_1(1)$
a. $S_2(1), S_3(1), S_4(1)$
Making sure your answers have made use of the two parameters and use their names rather than their row,column identity. As you type the formula, just type the first letter of the variable name and you should see it as an option in a drop down. Practice this until you feel confident about using this feature.
1. Fill in the rest of the table with autofill. Verify that your first few rows match the text:

stock-and-flow-03.gif

1. Now let's add a chart so we can see the size of the army over time. We mainly want to see a plot of Total Army Size by time. But easiest thing to do when creating a chart like this is to select all the data here and then throw away the data series we don't want. Thus, select all the data (including the column headers in row 5) and then Insert»Scatter>Scatter with Smooth Lines. Adjust size and insert a chart title . Then go to "Select Data" (right click the chart or under Chart Tools / Design it's in the upper left part of the ribbon). Delete all the data series except S1, S2, S3, S4, and Total.
1. Now add a spinner to control each of our variables. Note that to control the percentage who reenlist we'll have to let the spinner control another cell (the one right under the spinner is a good idea so we don't have to see that value. Let's assume it is D3.) and then put a formula ({=D3/100}) in the cell (C3) that now contains 0.5.
1. Verify that everything works. For the initial values given to the parameters in the problem how does the system behave? Does it reach a steady state? What size army? How long does it take to settle down?
1. Try several different combinations of recruitment size and reenlist rates. Fill in the following table with your observations (text, sentences, etc.).
stock-and-flow-04.gif

Example Two

Suppose you have been put in charge of establishing an "army" of community organizers as a part of a new Obamanization of American Communities. The goal is to have a steady state program size of 800,000 organizers. Our program involves a recruitment phase, a training phase, a placement phase and an active duty phase. Recruitment happens continuously with training academies starting every 6 months. The training itself lasts 6 months and is followed by a 6 month placement rotation, and then a year of active duty volunteer work.

We will assume that logistics and such are taken care of and we are only interested in building a model that will help us to see how recruitment and retention rates are related to the the number of volunteers on the street at any given point.

During training we expect 25% of recruits to drop out. A further 10% will leave during the placement phase. Once organizers get to active duty status we expect 100% retention for one year. At the end of one year, about 30% of organizers sign up for one more year of active duty.

First describe the situation in words. If the program begins with 100,000 recruits on 1 January 2011, what will the first few half-years look like in terms of activity?

Draw (on paper) a stock and flow diagram that represents this system.

stock-and-flow-05.gif

Build an Excel simulation that will let you vary the total number of recruits and the recruit retention rate and the re-join rate. Follow more or less the same steps you did above. When your model is complete, investigate combinations of recruitment rates and re-join rates as you did for the army model. For a given combination of recruitment and re-join rates, vary the recruit retention rate and report on your findings.

Write up your results in a short report. You can download a word document with the diagram already in it here: [[File(stock and flow diagram.docx)]]. Remember any document must be stand-alone. It should describe the problem, how you approached it, etc. not just give answers to unseen questions.

Files

[[File(stock and flow lab report.docx)]]
[[File(army-simulation.xlsx)]]