Microsoft Excel is an underrated forensic and investigative tool that can help investigators sort, organize, filter, simplify, normalize and format data. It can also be used to create pivot tables and charts for a number of different research questions.
In this article I will walk you through a few law enforcement excel spreadsheets that can be used to streamline some of your agency’s operations. These tools can be useful for scheduling, dispatching, shift bids, vehicle inspections and more.
1. Training Log
A Training Log is a great way to track your progress and monitor the success of your training routine. It also helps you identify patterns in your training that can be used to improve future workouts or planning.
Choosing the right stationary is important to keep your training log organized and easy to reproduce. Blank notebooks are a great choice but you can also use other forms of paper and pens to record your training details.
The first set of trainings under each officer’s tab are the mandatory training hours that all officers must complete annually. These include Firearms Qualification, First Aid, and Domestic Violence.
The second set of trainings under each officer’s log are the additional hours that the officer completed outside of the mandatory training. These are typically other trainings that the officer completed through their agency.
2. Rotation Schedule
A rotation schedule is a great way to give employees the option of working different shifts every week, month or other time intervals. This scheduling method is most often used in customer service because it allows agents to plan for their personal events and still provide 24×7 support.
One common rotation schedule is the Pitman schedule, which is best for public safety departments with 15 to 125 people. This schedule is made up of three teams with a 24-hour shift each and 48 hours off.
The Pitman schedule can be customized to fit your department’s needs and is easy to plug into a law enforcement excel spreadsheet.
If your team is larger, you might want to create 3 squads with a day, mid and night shift each. You can then repeat this template for 2 more squads to cover 24×7 support.
3. Shift Bids
Shift bidding is a process where employees bid for shifts they want. These requests can be based on agency rules, seniority, safety regulations, compliance training and union agreements.
Automated shift bidding processes help public safety administrators collect and prioritize shift bids in a fair and transparent manner, in compliance with agency regulations and union rules. This is important for ensuring police officers and sheriffs get equal scheduling opportunities, which helps improve officer engagement and morale.
To create a bid process, select Create a new bid process from the Add bid process to my schedules menu. Then, select a date for the bid process, and select agents who will be included in the process.
Next, add criteria to rank the agents by, such as their employment start date or any optional columns you use for performance information (like agents’ gamification leaderboard position). You can also add random numeric values to rank the agents by.
Agents are then able to place bids on the shift patterns they want until the next bid process assigns new shift patterns, or you decide not to use them when scheduling.
4. Vehicle Inspections
Using an Excel spreadsheet to analyze inspection data is an effective way for law enforcement agencies to examine any safety problems. The information is easy to download and sort by driver, date, location of inspection, vehicle, vehicle type, and violation.
The data in these spreadsheets will help a carrier see which violations occur the most frequently and have the highest severity weights. This is an important data point to look at when determining next steps for safety improvements.
Another thing to keep in mind is that the severity points displayed in the download only count one time. If they are counting two or three times instead of one, then this is a serious problem.
In order to be able to assess a motor carrier’s safety performance, FMCSA evaluates a carrier’s on-road performance and roadside inspections over a 24-month period of time. These results are used to determine the Behavior Analysis and Safety Improvement Category (BASIC) status of each motor carrier.