Security Manpower Planning Model Instructions

Printer Friendly Version

Review these instructions before downloading the file(s) download excel document



          Form Approved

     OMB No. 0704-0188

Public reporting burden for this collection of information is estimated to average one hour per response, including the time for reviewing instructions, searching existing data sources, gathering and maintaining the data needed, and completing and reviewing the collection of information.  Send comments regarding this burden estimate or any other aspect of this collection of information, including suggestions for reducing this burden, to Washington Headquarters Services, Directorate for Information Operations and Reports, 1215 Jefferson Davis Highway, Suite 1204, Arlington, VA 22202-4302, and to the Office of Management and Budget, Paperwork Reduction Project (0704-0188), Washington, DC 20503.

1. AGENCY USE ONLY        (Leave blank)



May 2008


Final Report, May 2008


Security Manpower Planning Model Instruction Manual





Rick Blake and Mark Uccardi


Booz Allen Hamilton, 8283 Greensboro Drive, McLean, VA  22102




Office of Safety and Security

Federal Transit Administration, 1200 New Jersey Ave, S.E., Washington, D.C. 20590




No Restrictions.  Available From:  National Technical Information Service/NTIS, Springfield, Virginia, 22161Phone 703.605.6000, Fax 703.605.6900, Email  []



13. ABSTRACT (Maximum 200 words)

This document includes resource information for identification and handling of information pertaining to transit systems and transit operations whose dissemination should be controlled and protected for security reasons through the use of designating this information as Sensitive Security Information (SSI).


Security Manpower Planning Model, Transit Security, Transit Police















This Security Manpower Planning Model and its instruction manual were developed by Booz Allen Hamilton as part of the FTA’s Office of Safety and Security Strategic Planning Program.  The development team acknowledges the input and leadership from Rick Gerhart from FTA’s Office of Safety and Security. 

The development team would also like to acknowledge the Transportation Security Administration (TSA), the American Public Transportation Association (APTA),  Dallas Area Rapid Transit, VIA Metropolitan Transit (San Antonio), Bay Area Rapid Transit District, Massachusetts Bay Transportation Authority and Golden Gate Bridge, Highway and Transportation District for their input in the development of this model and accompanying instruction manual. 


This product and its supporting documentation are disseminated under the sponsorship of the United Sated Department of Transportation.  The United States government assumes no liability for its contents or use thereof.

1.0   Security Manpower Planning Model Description

This Security Manpower Planning Model (SMPM) has been developed by the Federal Transit Administration, with initial draft versions reviewed by  the Transportation Security Administration (TSA) and American Public Transportation Association (APTA). 

The SMPM supports and closely aligns with FTA and TSA transit security fundamentals (including random and unpredictable roving patrols/inspections) and transit security program priorities (such as targeted training for transit security personnel).

The Security Manpower Planning Model is a flexible decision support tool created to enable transit security planners the ability to assess impacts of strategic decisions on resources and staffing.  Based on the data inputted, the model identifies staffing levels and budgeting.  The SMPM is flexible in the sense that it can be used by any transit agency with existing or planned security resources, regardless of operating mode(s) or size.  Further, the model can assist security planners in assessing impacts of various scenarios on resource and deployment strategies including:

  • Changes in revenue service operations (e.g., adding a new rail line, re-structuring existing routes or special event service planning)
  • Changes in ridership patterns, crime/incident rates and threat information
  • Changes in security personnel configurations (e.g., alternative mixes of internal/external security resources)
  • Changes in how security forces are deployed
  • Adjustments to security coverage levels
  • Implementation of proof-of-payment fare enforcement or other related security duties

Figure 1 – SMPM Overview



·       Tool that requires a number of specific parameters aimed at understanding how a transit agency deploys or plans to deploy its security resources

·       Tool that processes all user inputs to determine how much staff would be required to provide the specified coverage and at what cost

·       Tool that allows "what-if" determinations of alternative scenarios for deploying security manpower

·       Tool that accurately and consistently quantifies the data provided

·       Tool that does NOT make staffing and hiring recommendations

·       Tool that does NOT make deployment recommendations

·       Tool that does NOT identify transit industry best practices

·       Tool that does NOT contain any optimization inside the model

·       Tool that does NOT come up with “one right answer” – transit agencies must determine for themselves the coverage levels that are appropriate for their operating environments

·       Tool that does NOT factor indirect costs into the model such as police vehicles, firearms, uniforms, etc.


The SMPM is a Microsoft Excel file that was devel­oped using Excel 2003.  All display and program menu items in this manual were written based on the behavior of the model under Excel 2003.  Menus could vary under different versions, and the model’s behavior with other programs and other versions of Excel may also vary, as follows:


·         Microsoft Excel 1997:  SMPM will not work properly due to missing Visual Basic (VBA) features.

·         Microsoft Excel 2000:  Users are not prevented from selecting non-data-entry cells, but cannot make entries in those cells.

·         Microsoft Excel 2007:  Changes to program functionality causes minor formatting differences.  A separate SMPM version for Excel 2007 will be produced.

·         Microsoft Excel for Mac 2004:  SMPM should function correctly, but it has not been tested.

·         Microsoft Excel for Mac 2008:  SMPM will likely not work due to the removal of VBA from this version of Excel for Mac.

·         Spreadsheet programs other than Microsoft Excel:  Proper functioning of SMPM with “clone” programs such as OpenOffice has not been tested and is not guaranteed.

1.1    SMPM Benefits

The SMPM is designed to provide multiple benefits to transit security managers and planners.  The figure below highlights the main benefits of the manpower model.

Figure 2 – SMPM Benefits


·       Provides accurate security staff labor costs

·       Quantifies security deployment statistics 

·       Validates business case for security staffing changes

·       Projects security force deployment costs associated with new revenue service initiatives

·       Tracks increased threat level security deployment costs (for transit agencies eligible to receive DHS transit security grant funds)

·       Links crime analysis statistics with deployment strategies

·       Assists in security force seniority and succession planning


1.2    Sensitive Security Information (SSI)

The information required by the model is considered sensitive and therefore must be appropriately protected.  As a result, once data is added to the model, it should be considered Sensitive Security Information (SSI).  For more information on what is SSI and how it should be protected, please refer to the Code of Federal Regulation at 49 CFR Parts 15 and 1520

Checking the checkbox labeled “Contains Sensitive Security Information” on the right side of the Home sheet adds the appropriate SSI labelling at the top and bottom of each page of the SMPM all displayed and printed pages.   It is strongly recommended that this box be checked once real data is entered into the model. 

SSI versions of the SMPM must be password protected when transmitted electronically (including as attachments in e-mails to colleagues).  To password protect the file, select File | Save As | Tools | General Options…, then enter a password to open the document.  Another password may also be added for modifying the document.

2.0   Running the Program

2.1    Getting Started

No software other than Microsoft Excel (preferably 2003 or 2007) is required to run SMPM.  The model is optimized for a display resolution of 1024 by 768 pixels.

To start the model, copy the original SMPM.xls file onto either the desktop or into a known folder, then either double-click on the model’s icon or open it via File | Open from within Excel.

Excel must allow the programming within the model to function, which requires the use of macros.  There are two potential issues to be resolved before using Excel macros:

·         When the model attempts to load, Excel may display a security warning that the file contains macros.  The macros in the model are safe, and you must select “Enable Macros” for the model to operate properly.

·         When the model is loaded, if a message is displayed saying “Macros are disabled…,” select Tools | Options | Security | Macro Security, click on Medium, close the window, and then open the model again to enable the program to operate normally.  (This technique is appropriate for Excel 2003; other methods may be required for a different version of Excel.)


The first time the program runs, a message box will display privacy and disclaimer information.  Clicking the red box on the Home sheet will also display this box at any time.

2.1    Selectable Factors

The manpower model enables the user to select which of the following factors are relevant for that particular transit agency:

·         Rail (including commuter rail,  heavy rail, and light rail)

·         Bus

·         Proof-of-Payment (POP) Enforcement

·         Ferry

·         Facilities (both passenger and operations/maintenance)

·         High Occupancy Vehicle (HOV) Lane Enforcement

·         Safety/Security Management and Administration

·         Additional Officer Positions and Tasks (e.g., K-9 teams, Special Operations)

2.2    Data Requirements of the Model

The data necessary to populate the manpower model varies depending on which of the above factors are applicable to the transit agency.  There is no single “right answer” for the data; the model is designed to assist agencies in testing various security manpower deployment scenarios and make informed choices based in part on the model’s results.

2.3    The Home Sheet

The first time the model is run, the Home sheet will be displayed.  (The model may open on other sheets in the future depending on what sheet was visible the last time the model was saved.)  This sheet gives you the ability to specify which particular modules and factors are relevant for your transit agency.  To select or de-select different modules, click the checkbox to the left of the description.  If the box contains a checkmark, that module is active and a yellow tab to access a data-entry sheet for that module will be visible on the bottom of the screen.  The Home sheet itself is always available by clicking on the green Home tab at the bottom-left corner of the screen.  Figure 3 below is a screen shot of the Home sheet.

Figure 3 – Home Sheet

Figure 3 - Home sheet (displays applicable modules that can be selected)


On every sheet within the model, a box with a light-green background desig­nates those locations where data can be entered.  The cursor can be located only in those green boxes and no other entries can be made to the model.  There is a data entry box on the Home sheet where a description of various scenarios can be typed.  The description typed into this box will appear on every sheet of the model, which is useful when scenarios are printed.

If various sheets are selected and de-selected during the what-if process by clicking the checkboxes on the Home sheet, any data that is entered on a sheet remains on that sheet when it is deselected and will reappear when the sheet is again selected.  However, nothing on a deselected sheet will be factored into the manpower and cost figures that are presented on the Results sheet.

Checking the checkbox on the right side of the screen labeled “Contains Sensitive Security Information” adds the appropriate SSI labeling to the top and bottom of all displayed and printed pages.  It is recommended that this box be checked once data has been entered in the model.  In addition, once the file is designated as SSI, it must be password protected when transmitted electronically, such as via e-mail.  Passwords can be applied by selecting File | Save As to open the save window, then clicking on Tools | General Options.  Remember that it is not possible to recover a file if its pass­word is lost or forgotten.

Clicking the blue button will present a pop-up box that displays information on getting help with the model.  The red button will present a box with SSI and general disclaimer information.  As a reminder, once data is added to the model, it should be designated as SSI.  Once it is SSI, it now must be password protected.  See Section 1.2 for more information.

The model can be customized by placing a logo or similar photograph of the transit agency into the white box at the upper-right corner of the Home sheet.  To do this, click on the box and select the desired file.  The file should be in JPG, GIF, or BMP format and for best results should be square with dimensions of at least 125 by 125 pixels.  A picture can be deleted or replaced by clicking on it at any time.  (Note that Excel occasionally has trouble properly rendering some graphics files.)

Once the relevant modules have been selected, it is time to enter data on the various module worksheets.

2.4    The Data Entry Sheets

Data can and should be entered in all of the relevant light-green boxes on all of the yellow-tabbed sheets that appear at the bottom of the screen.  It is always possible to select or deselect which sheets are active (via the Home sheet), and Yes/No answers can be changed as necessary throughout the what-if process.  The General sheet cannot be deselected and is always active.

On all of the sheets, data can be entered only in the light-green boxes.  The required data is generally self-explanatory.

Following are some helpful hints:

·         Virtually all data cells initially have no data in them, eliminating the danger of pre-existing data corrupting the model for an agency.  Only cells that are relevant for an agency need to have appropriate data.

·         The text in some rows is grayed out if their parameters are not relevant (generally due to user Yes/No or quantity selections).  Although data can usually be entered in cells that are gray, any data in those cells is not considered in the calculations.  Remember that cells that are grayed out may become active if some other parameters—especially Yes/No boxes or certain quantities—are changed.  If you believe a specific row should not be gray, check elsewhere in the model to ensure that appropriate Yes/No cells and relevant quantities are correct.

·         Responses to Yes/No questions can be entered either by typing “Y” or “N” (in either upper or lower case) or via each cell’s pull-down menu.

·         In general, it is best to use weighted averages wherever appropriate.  For example, if four officers get paid $10 per hour and another officer gets paid $20 per hour, the weighted average would not be $15 per hour [(10+20)/2], but rather $12 per hour [((4*10)+(1*20))/5)].  This is especially true in wages or benefits cells if there is more than one rate for the same position.

·         The “Show Summary” button at the lower-right corner of the data entry sheets opens a pop-up window that will show total agency officer field hours and agency officer full-time employee equivalents (FTEs).  Click OK to close the pop-up window.

·         There is some error checking to ensure that entries are within logical bounds, and pop-up boxes may appear if some data is clearly inappropriate.  However, sanity checks should be used before entering data, and the results should be checked to ensure they are logical.

·         Each of the yellow-tabbed data entry sheets should be fully visible without the need to scroll through the sheet. (Only the Results sheet requires scrolling.)  If this is not the case, it may be necessary to disable unused Excel toolbars; only the Standard and Formatting toolbars should be displayed, although they are not needed to operate the model.

·         Large agencies with multiple transit modes may find the model easier to use by setting up different scenarios for each of the modes and then adding the totals together manually.  If this strategy is used, care must be taken to avoid duplication, especially for management and administration jobs.

The following sections describe some specific notes about each data entry sheet.

2.4.1   General

This sheet lays the groundwork for the staffing requirements, by determining whether coverage is provided by (in-house) transit agency-employed security personnel, (external) non-agency law enforcement officers and/or security guards.  For transit agency security personnel, it is possible to specify their daily shift lengths and how many days/week they work.  Rates of pay are set for each of these labor categories, and are broken down by wages and benefits.  If it is not necessary or possible to separate out the cost of benefits; the total hourly amount can be inserted in the wages column, and the benefits cells can be left blank.  (NOTE:  If only staffing level information is desired, it is not necessary to provide any cost data on the various sheets.)  The benefits cells usually contain the cost for transit agency-funded sick leave, retirement plans, disability insurance, workers’ compensation, etc.

Transit agency security forces often work 8-, 10-, or 12-hour days, frequently with a bi-weekly schedule that averages out to 40 hours per week, and the model accepts these scheduling arrangements.  Absences due to training (for security, firearms, driving, recertification, etc.), vacation, sick leave, court appearances, etc., and unavailability due to the time required to get into the field, as well as meals and breaks during a shift, affect the availability of transit agency security personnel to provide the required coverage.  Adjustments for these unavailability factors are entered on this sheet.

Some of the choices made on the General sheet impact what is displayed and available on this and other sheets.  Specifically, certain options will be grayed out if the transit agency does not have “Agency police officers,” “Non-agency police officers,” or “Security guards,” which are determined by the three Yes/No boxes on the General sheet.  Further, results will be unreliable—and the model itself will be irrelevant—if neither “Agency police officers” nor “Non-agency police officers” have a “Y” on the General sheet.  If a transit agency uses only security guards, they should be entered as “Non-agency police officers” rather than as “Security guards.”  However, it should be remembered that some law enforcement tasks can only be provided by sworn police officers.  Figure 4 below is a screen shot of the General Sheet.

Figure 4 – General Sheet

Figure 4 - General Sheet (displays general staffing parameters)

The services of non-agency police officers (or sheriff’s deputies) can be obtained in two basic ways—either via individual contracts with each officer, or via a contract with the officers’ employer, such as a local/city police department or county sheriff’s office.  If the latter scenario is used, and the transit agency is responsible for benefits and time-off of the officers provided under the contract, then it might be more appropriate to consider these officers as “Agency officers” for the purposes of the model.  Otherwise, it is assumed that the transit agency is not responsible for vacation, sick leave, training, or other forms of time-off for the non-agency officer and security guard positions.  Additionally, if the hourly rate of non-agency officers includes additional components such as police vehicles, then those costs should be separated out to ensure that only labor costs are included.  The only adjust­ment to the availability of non-agency officers and security guards is the number of hours they actually provide productive work based on an 8-hour shift.  (If security guards are on staff, additional time-off can be accommodated by pro-rating the additional absences into their daily availability figure.)

“Total Gross Work Hours in One Year” is generally 2,080 (8 hours per day times 5 days per week times 52 weeks per year); this number can be changed based on transit agency standards.  Note that this figure applies to all staff positions, not just officers.

The hourly cost of personnel can be separated into wages and benefits, although they can be combined if necessary or desired.  There generally should not be an hourly benefit cost for transit agency officers on overtime, and it is usually not possible to separate out the hourly benefits cost for non-agency personnel.

At some transit agencies, various personnel performing similar tasks may be paid at different rates due to their length of service or union/non-union status.  This can be accommodated in the model by calculating a weighted average of the different rates by factoring the percentage of personnel who earn each of the different rates.

The model does not specifically allow for shift differential, although the average cost per hour can be increased a certain amount based on the percentage of personnel who work shifts that receive a higher rate.

2.4.2   Rail

The Rail sheet provides the ability to apply either Proportional coverage, where the percentage of trains and stations the transit agency would like to patrol is specified, or Zone coverage, where the service area is broken into different security coverage zones with specific staffing levels.  Only the coverage mode that is selected (by clicking one of the buttons at the top of the sheet) is visible and will be used in the calculations, but no data is erased if the mode is changed.  The transit agency can test both coverage strategies in its what-if scenarios process.

The Proportional coverage mode asks for the number of passenger stations that require patrolling.  If the size of stations varies so widely that some stations require full-time security presence while others just get short visits a few times a day, the larger facilities can be entered on the Passenger Facilities section of the Facilities sheet, while the smaller stations can be entered on the Rail sheet.  The same technique can be used for the zone coverage as well, with the smaller stations being covered within the patrol zones.

The Zone coverage mode is similar to the data that is input on the Bus sheet, as is discussed more fully in the following section.

Other data, such as incident handling and right-of-way patrol, are applicable to both the Proportional and Zone coverage methods.  The incident handling section is used to compensate for the potential loss of patrol time due to the need for officers to spend a certain amount of time dealing with incidents.

2.4.3   Bus

Since bus operations are typically more geographically dispersed than the more linear rail systems, bus system security is generally accommodated by establishing a certain number of patrol zones to which security personnel are assigned.  This technique is what the model supports.  The model allows the number of patrol zones and personnel to be set for various shifts throughout the day.  No differentiation is made for weekends, but the data can be averaged over 7 days if there are daily variations.

It is assumed that all shifts have the same duration.  For example, if there were three shifts providing 18 total hours of coverage per day, all three shifts would be 6 hours in length.

If it is desired to ensure that the need for officers to handle incidents does not detract from the basic coverage, additional hours can be created by stating that dedicated time for incident handling is required and then entering the appropriate parameters.

If police officers report directly to their zones compared to reporting to a centralized facility, the “Number of hours in field…” on the General sheet can usually be higher.  This technique for increasing efficiency should be considered when establishing or updating security department logistics.

Coverage can also be more efficient if officers combine bus zone patrols with facility patrols, which are specified on the Facilities sheet.  The level of this diversion from the zones can be specified in the model, and the overall efficiency is shown on the Results sheet as a savings in task-hours and officer FTEs.

The line that asks about “additional hours/day to ride buses” refers to special targeted areas where coverage would be provided above the normal zone patrols.  This coverage could also be entered on the Other Factors sheet, although that sheet represents a permanent staff of officers dedicated to that purpose whereas on this sheet the coverage would be provided by officers already on zone patrol.

2.4.4   POP Enforcement

Traditionally, a Proof-Of-Payment (POP) fare collection policy has primarily been used for light rail and bus rapid transit services, but its use is becoming more prevalent for other transit modes.  To support a broader use of POP, the model enables five separate sets of parameters for deployment of POP enforcement.  They are labeled as Heavy Rail, Light Rail, Commuter Rail, Bus Rapid Transit, and Other, but the algorithms are identical for all five sets and these labels exist only for conveni­ence.  Data is required only for those transit modes that have POP enforcement; other columns can be left blank.

The level of POP enforcement effort is generally determined by targeting a specific percentage of riders for inspection each day.  Therefore, the model asks for the number of riders (weekday, Saturday, and Sunday), and the target percentage of riders to be inspected.  Some transit agencies have dedicated fare enforcement officers (FEOs) on staff, some only use police officers, and some use both.  The model supports all three strategies, and the target inspection percentage can be different for FEOs (whose efficiency can be specified) and officers.

2.4.5   Ferry

The ferry coverage is based on patrols aboard a specified percentage of trips each day.  Patrols of ferry facilities can be accommodated on the Facilities sheet, although personnel assigned to ferry security can also patrol facilities during the layover periods between ferry trips.

2.4.6   Facilities

Coverage of facilities is broken into two categories—passenger facilities and operations & maintenance (O&M) facilities.  Administrative offices should be included in the latter category.  It is common for transit agencies to use Security Guards in lieu of Police Officers for some or all of this coverage; the percentage of coverage that can be provided by Security Guards can be specified.

For each category, the model asks for the number of facilities that require constant monitoring during the service day, as well as the number that only require periodic monitoring, and how much coverage each type requires.

This sheet is the appropriate place to enter the coverage requirements for large rail stations or transfer facilities that need more than the average amount of coverage, but care should be taken to prevent duplication of facility coverage with the zone patrol entries on the Bus and Rail sheets.

2.4.7   HOV Lane Enforcement

The required data on this sheet is straightforward, and is based on the number of highway miles each officer can cover and the number of hours of coverage.

Since HOV patrols frequently only occur for short periods during the rush hours, agencies may decide that zone coverage can be shortened while HOV coverage takes place.  The model allows for this up to a specified percentage.

2.4.8   Safety/Security Management and Administration

The sheets previously discussed identify the required coverage of officers, security guards, and fare enforcement officers in the field.  In addition to field personnel, security departments need management and administrative staff, the parameters for which are entered on this sheet.  It is up to thetransit agency to ascertain its level of management and administration ranks and their proportion to the total security staffing level.

The management and administration personnel can be either transit agency or non-agency staff.  Either way, the hourly costs can be broken down by wages and benefits, although they can be combined in the wages column if desired.  Additionally, the average amount of overtime pay can be entered.  It is assumed that all listed personnel are paid the hourly rate for the number of total annual hours (nominally 2,080) specified on the General sheet, and that there is no back-fill for these positions when they are absent due to vacation, sick leave, training, etc.

In addition to stating whether there is a Security/Police Chief on staff, it is possible to establish up to five other management ranks.  Specifying the number of management ranks activates that many rows immediately below.  For each rank, the staffing level and total annual cost for straight time, overtime, and benefits are to be entered.  (NOTE:  If police officers are used in management and administration, they should be entered on the Additional Officer Tasks sheet, instead of on this sheet.)

In addition to the management staff, there can be up to 10 user-specified administration classifications, each with its own number of positions and annual cost (per position) of wages, benefits, and overtime pay.

2.4.9   Additional Officer Positions and Tasks

This sheet has two sections.

First, up to 14 additional police officer position categories and staffing levels can be entered on this sheet.  The first 10 tasks are available via pull-down menus, and include such common tasks as detectives, undercover and plainclothes operations, intelligence officers, special operations, community and taskforce liaisons, etc.  The last four tasks can be typed in, for any positions that are unique to the transit agency.

The actual number of officer positions to be added (fractional is acceptable) should be entered on each row for which a job category has been entered.  It is assumed that each officer position is paid the regular-time officer rate for the total number of hours in one year (nominally 2,080) and that there is no backfill for these positions.

Second, the number of annual hours to perform additional field tasks, such as special event coverage, can be added.  The difference between this section and the additional positions discussed above is that these hours are added to the field task-hours required, rather than being included as individual officer positions.  Thus, officer availability impacts the number of officers required to provide this additional coverage.  If many of these tasks are covered by officer overtime, the average amount of overtime can be entered on the Results sheet.

2.5    The Results Sheet

The Results sheet, which is identified by its red tab, always displays calculations based on the current data that is entered in all of the active sheets.  Data on sheets that are not currently active (i.e., the tabs are not visible because they are not selected on the Home sheet) will not be factored into the calculations, and their lines on the Results sheet will be grayed out with zero shown for hours and FTEs.

The top part of the Results sheet displays the number of task-hours required for police officers, security guards, and FEOs to provide the coverage specified. It also shows the number of FTEs for transit agency field officers (who will likely have a different availability rate than non-agency officers) and for the other two security jobs. The FTE numbers are essentially the number of task-hours divided by the number of effective hours per officer.  Since the task-hours only reflect the amount of labor required in the field, at this point there is no differentiation between the use of transit agency and/or non-agency officers in the number of task-hours required.

Management, administration, and additional officer positions are not included in this section because they are entered into the model strictly by the number of personnel, rather than by the number of hours required to perform their tasks.

By scrolling down the page, the cost data becomes visible. There are three data-entry cells in this section that show the impact of resource deployment on staffing levels and the total security manpower cost. These data-entry boxes were placed on this sheet to enable the user to immediately see the effect of the changes made.

·         The top box enables the user to specify a percentage of required officer field task-hours that will be covered. This can easily be used in conjunction with the number of required positions and cost totals to ascertain the amount of required coverage that can be provided at a specific cost.  The percentage can be changed either by typing in the number or by adjusting the slider; the latter technique is preferred because all the resultant numbers constantly update as the slider moves, making it easy to hit a “target” number such as the budget or the number of officers.

·         The second box also contains a slider, and provides the ability to specify the percentage of officer field task-hours that is covered by transit agency officers versus non-agency officers. Because of the different availability and hourly cost factors for each category, the total number of pay hours and total cost could vary significantly as this variable is changed. (This control is meaningful only if both transit agency police and non-agency police have been selected on the General sheet; otherwise, the entire coverage requirement will be allocated to whichever resource is used.)

·         Finally, the average amount of transit agency field officer overtime can be specified (assuming that “Agency police officer” has been selected on the General sheet); the required number of staff positions decreases as the number of overtime hours increases, which could actually result in a cost savings to the transit agency because (a) the overtime rate of pay is frequently less than the normal rate plus benefits, and (b) overtime availability is not diluted due to sick leave, vacation, training, etc.

The number of hours displayed in the cost section is pay-hours, which for field positions is generally greater than the number of task-hours due to officers not being able to provide coverage 100 percent of the time.  However, the pay-hours for management, administration, and other non-field officer positions are determined by the designated level of staff multiplied by the number of hours in a year (nominally 2,080), since availability is not a determinant of their staffing level.

NOTE:  The model presents the calculated number of FTEs that are necessary to provide the required coverage and by extension, the recommended staffing levels.  The transit agency must determine the most efficient work schedule that deploys the staff as required, and in certain cases (e.g., if a high percentage of coverage is required within a short period of time), it may not be possible to provide the coverage desired using the stated number of FTE staff.


Figure 5 – Results Sheet

Figure 5 - Results Sheet (shows final results)

A pie chart (not shown above) can be viewed by scrolling further down the Results sheet; it reflects the distribution of costs among the various categories of security staff.

2.5    Saving and Printing the Model

Once the transit agency’s basic parameters have been entered into the model, it is advisable to save that version with a distinct file name and use it as a template file on which subsequent what-if scenarios are based (it should also be saved under a different name in case the original template accidentally is overwritten).  Each subsequent iteration of the model can be saved as a different Excel file, using the “File | Save As…” command.  It is recommended that the file name match the scenario description entered on the Home sheet.  As stated earlier, the file should be password protected once sensitive data has been entered.

To print the entire model at any time and from any sheet, click “File | Print” and under “Print what” select “Entire Workbook.”  Only those sheets that are active will print.  Clicking on the Print icon on the Excel menu bar will generally print only the active sheet.  Each sheet is already formatted to print correctly, along with the scenario description entered on the Home sheet.

3.0   Getting Help

For help or assistance with the model, or to report flaws or provide suggestions, please contact Mark Uccardi at 973-456-5062 or e-mail at  Updates and other information about the SMPM will be posted on the FTA’s Office of Safety and Security website,

A A A    Bookmark and Share