Form unNumbered UCR Summary Excel Workbook (Cargo Theft Only)

---

Overview

Instructions
Agency Information
Cargo Theft

---

Sheet 1: Instructions

UCR SUMMARY EXCEL WORKBOOK

The FBI UCR Program is providing this UCR Summary Excel Workbook to law enforcement agencies who are otherwise unable to submit monthly UCR data via electronic means.

This workbook is not intended to serve as a replacement for any agency's record management system. It should be used in place of the standard, OMB-approved UCR crime-reporting forms and submitted to the UCR Program on a monthly basis.

Once the workbook is filled out for a given month, please email it to your UCR Program liaison. Should you need to submit a later update, please make the appropriate change in your existing file and re-submit the entire workbook.

Usage Information

The UCR Summary Excel Workbook is a standard Microsoft Excel spreadsheet, consisting of several individual worksheets (tabs).

The first tab in the spreadsheet, titled Agency Information, should be filled out prior to the UCR form tabs but can be returned to at any time. The fields are broken down into two categories: Required and Optional. The three required fields (ORI, Month of Report, and Year of Report) are mandatory – the FBI cannot compile your data without these fields. The required fields will automatically be populated throughout the UCR form tabs. The optional fields do not affect data processing, but like the required fields, they will automatically be populated throughout the UCR form tabs.

Also on the Agency Information tab is the UCR Workbook Options Box. This box is meant to be a replacement for the "nothing to report" checkboxes present on the Return A form. For each form in the UCR Summary Excel Workbook, the UCR Workbook Options Box contains three options:

1.     Original Report / Zero Report

a.       Select this option if this is the first time you are submitting data for the month or if you wish to report 0 incidents for the month.

b.      Example: For January 2014, you are submitting Return A, Return A Supplement, LEOKA, ASR, Arson, and SHR. You had no LEOKA, Arson, or SHR data. You would select "Original Values/Zero Report" for every form, and simply leave the LEOKA, Arson, and SHR forms blank. Also, you are not reporting Police Employees, Hate Crime, Human Trafficking, or Cargo Theft so you would select "No Change/No Report".

2.       Adjustment

a.      Select this option if you are resubmitting data for any form in a given month.

b.      Example: You notice that some of the January 2014 numbers were off, requiring updates of Return A and ASR for that month. You would select "Adjustment" for Return A and ASR and set everything else to "No Change/No Report" since the other reports have been previously submitted.

a.       This option is used to let the FBI know that, even though you are resubmitting the workbook for the month, certain forms did not change from the original submission. Alternately, you can use this option to let us know that you do not wish to submit a particular form.

b.      Example: With the adjustment example from above, you would select "No Change/No Report" for every form except for the forms being adjusted.

c.       Example: Your agency does not submit Hate Crime or Cargo Theft data to the FBI, so every month you check "Original Values/Zero Report" for every form except Hate Crime or Cargo Theft, and select "No Change/No Report" for the Hate Crime or Cargo Theft forms.

The remaining tabs in the UCR Summary Excel Workbook represent the standard OMB-approved UCR paper forms and have been formatted to look as familiar as possible. These forms should be filled out exactly as you would fill out the paper forms, and they may be filled out in any order.

On every tab in the workbook, certain fields are highlighted light yellow. These fields are the only fields where data can be entered and, in most cases, should all contain data. The exception is the Optional fields on the Agency Information Tab.

All fields that are not highlighted light yellow are locked; that is, unable to be clicked in or to have data entered into them. To reduce data entry time and increase accuracy, all totals on every form are computed via formulas and may not be manually entered.

All yellow cells on every form must be whole numbers with no decimals or special characters ($, !, @, etc.) In most cases these numbers must be between 0 and 99999, though in some cases (arson values, stolen property, population) larger numbers are allowed as well as negative numbers.

Any cells which are highlighted in a light red/pink color indicates an invalid value and therefore should be corrected before saving the UCR Workbook. In some instances, you will be required to make the change before saving is allowed to occur while in other instances the highlighted cells are simply a warning that data values do not match.

Please note that the majority of the fields on the SHR forms will open up an option box when clicked. We ask that you please choose one of the available options and do not enter any other data in those fields.

In order to preserve the visual integrity of the OMB-approved forms, some cells are merged together. Note that if you are using the Tab key to move between cells on a form, these merged cells may require you to hit Tab several times before the cursor will move past the merged cell.

Additional Considerations

If you need to update numbers on any given form, you must re-send the entire workbook as it was originally presented, with the exception of changed numbers. Do not empty out the other forms. Use the Options Box on the Agency Information tab to indicate which form(s) have changed (click the Adjustment button for that form or forms) and which have not (click the "No Change/No Report" button for those forms). With this in mind, we suggest that you save a copy of each month's completed workbook for your records, in the event that you need to re-submit a form later on.

The UCR form worksheets were modeled after the paper UCR forms such that when the worksheets are printed, they are nearly identical to their official paper versions. No information has been added to or removed from the forms with the exception of the yellow highlighting to increase visibility of editable fields.

The UCR Summary Excel Workbook makes heavy use of Excel Macros. You may be required to allow these macros to run each time you open the UCR Summary Excel Workbook. It is also possible to set Excel to always accept macros, but doing so could lead to accidental infection by malicious macros in the future and should be discussed with your technical staff.

---

Sheet 2: Agency Information

Required Fields			Application Version	1.7
Agency Identifier (7 or 9 digit ORI)	WV0030000		WV0030000 (BOONE)
Month of Report	January
Year of Report (YYYY)	2015
Optional Fields
Agency
City
County
State
Population
Prepared By *
Title of Preparer
Telephone Number *
Email Address *
Chief, Sheriff, Superintendent, Commanding Officer, or Supervisor
Date Prepared
*Note: Please provide your contact information

---

Sheet 3: Cargo Theft

Cargo Theft Incident Reports
Below is a listing of all of the completed Cargo Theft incidents. Click the "Add New Incident" button to complete a new Cargo Theft Incident Report. You may view or edit any completed report by clicking the "View/Edit Report" button. An incident may be marked as an adjustment or deletion if it has been previously submitted to the FBI UCR program. If an incident has not yet been submitted to the FBI, you may delete it using the "Delete Report" button.
INCIDENT INFORMATION
INCIDENT NUMBER	INCIDENT DATE (YYYMMDD)	SUBMISSION TYPE