To help you conduct a simple evaluation, this page contains an Excel spreadsheet that will assist with evaluating the outcome of crime reduction operations that take place in a geographic area. The spreadsheet is optimized to work with operations that are focused in areas such as neighborhoods, housing projects, or crime hot spots.
The spreadsheet requires before and after counts of crime in both an activity area (where your operation is targeted) and a control area (which represents the normal change in crime in your broader region over time). You also have the option to include crime counts in a buffer area so that you can check for displacement or a diffusion of benefits. Note that while the spreadsheet says "before" and "after", if your operation is current and ongoing, the "after" values can represent during the operational period.
ABC is a macro-enabled spreadsheet
Depending on your security settings, you will need to enable macro content in Excel for the spreadsheet to function. When you see a yellow message bar at the top of the spreadsheet, you will need to click "Enable editing" or "enable content". If your workplace has more rigid rules about macro-enabled spreadsheets, you should contact your system administrator.
Wheeler and Ratcliffe's Weighted Displacement Difference
Version 1.4 added the calculations for the Weighted Displacement Difference. If you wish to learn more about this technique, you can download the relevant research paper.
This spreadsheet is for illustrative purposes only, and is provided 'as-is' without warranty or support. Jerry Ratcliffe and Reducing Crime LLC assumes no responsibility or liability for the use of the software and makes no representation or warranty that such application will be suitable for the specified use without further testing or modification. On entering data the user assumes all liability regarding usage and interpreting of results.
If you acknowledge the disclaimers and limitations, click here -->
Ratcliffe, Jerry H. (2019) ABC spreadsheet calculator (version 1.4) [Computer Software], reducingcrime.com.
Version 1.4 added the calculations for the Wheeler and Ratcliffe Weighted Displacement Difference.
Version 1.3 was the first publicly available iteration.
Version 1.2 was a development version during drafting of the book "Reducing Crime: A Companion for Police Leaders"
Version 1.1 fixed a couple of bugs.
Version 1.0 was a refresh of the old SEPTIC spreadsheet constructed by Clarissa Breen and myself a long time ago.