Building a Site Audit in an Excel Template
- Jing Tong (Unlicensed)
- Chellie Esters
- Natalia Larina (Unlicensed)
A site audit is an inspection with a targeted scope in line with your business needs: a semi-annual fire/life safety audit, a monthly loss prevention audit, an annual branding audit, or a quarterly facility operations audit, to name a few examples. While an auditor is at a location, (s)he will pull up the correct audit type through SC Mobile and conduct the appropriate audit. In Site Planning Manager - Admin, you may configure as many audit types as necessary.
There are four distinct parts that make up an audit type:
- Audit type name is a distinct name of the audit, for example, Semi-Annual Fire/Life Safety, Quarterly Loss Prevention, or Branding. A descriptive name helps an auditor use the correct audit while on-site and helps you manage an array of audits based on your business needs.
- Areas are a specific region at each location the auditor can inspect, for example, Exterior, Interior, Back of House, or Front of House. Areas are often the high-level parts of the location but could also be categories of an audit. For reporting purposes, audit results are categorized by areas.
- Checklists determine the auditor's focus and are often a specific section of an area, for example, Parking Lot, Kitchen, Office, Vestibule, or Restroom. Checklists are containers which house checklist items, or rather, the specific auditing tasks.
- Checklist items pinpoint exactly what to inspect and rate from each checklist, for example, Signage, Emergency Lighting, Security Cameras, Flooring, or Doors. Checklist items may be configured with instructions and guidance on how to effectively assess these items.
Configuring an audit type requires uploading a Microsoft Excel template with a mapping of areas, checklists, and checklist items. Afterward, you will upload the template into Site Planning Manager - Admin and give it an audit type name.
Below is the workflow for which you build your site audits:
In this section, we will discuss each part of the template so that you can configure it to your needs; later are the steps to upload the template into Site Planning Manager - Admin and append an audit type name.
Site Audit Excel Templates
Below are available templates to get you started building a site audit. You may use the standard template — which has items that may be applicable to an array of industries — or any of the industry-based templates that may better align with your operations.
- Site Audit Setup - Standard Template.xlsx
- Site Audit Setup - Convenience Store.xlsx
- Site Audit Setup - Bank.xlsx
- Site Audit Setup - Retail.xlsx
- Site Audit Setup - Supportive Housing.xlsx
- Site Audit Setup - Restaurant.xlsx
Configuring and Mapping Audit Data
There are five tabs in the template: Areas, Checklists (CL), Checklist Items (CI), Assign CL to Area, and Assign CLI to CL.
| Template Tab | Description |
|---|---|
Areas
| A list of the general, grand sections within your location. This list is usually the shortest in a site audit template. A few common areas are:
|
Checklists (CL)
| A list of individual rooms or spaces throughout each location. You can configure checklists for:
|
Checklist Items (CI)
| A list of items to inspect and rate. The Short Description column lists the actual items an auditor will check. The optional Detailed Instructions column contains further guidance for the auditor, detailing what to look for during the audit. This list is rather long, so it is best to take time to ensure you have captured your full list. Checklist items (with optional detailed instructions in parentheses) may include:
You do not need a detailed instruction for every checklist item. |
Assign CL to Area or Assign Checklist to Area
| The mapping of each checklist to an area. Given the following:
you may map:
Map one area to one checklist on a separate line, as depicted. Note that the areas repeat, but the checklists differ for each line item for a specific area. |
Assign CLI to CL or Assign Checklist Items to Checklists
| The mapping of each checklist item to a checklist. Similar to assigning checklists to areas, this tab allows you to map individual checklist items to checklists. As assets of the same type may be in multiple rooms in a location, it is normal to map the same checklist item to multiple checklists. Given the following:
you may map:
Map one checklist item to one checklist on a separate line, as depicted. Note that the checklists repeat, but the checklists items differ for each line item for a specific checklist. The optional CI Impact column specifies the importance of a checklist item relative to other items. Two possible values are Veto and Ignore. See Configuring Checklist Item Impact, below, for more details. The Rating System describes ratings — how the auditor will evaluate each checklist item. See Assigning Site Planning Rating Systems, below, for more information. |
Assigning Site Planning Rating Systems
When an auditor is on site, they inspect the location and rate checklist items. How items are evaluated depends on the rating system assigned to them. Using various rating systems allows you to get more fine-grained information on the location and related assets in site audit reports.
The following site planning rating systems are available:
- PF: Implies evaluating the condition of a checklist item as either Pass or Fail.
- Numeric: Permits selecting a score on a 0–10 scale for an item.
In the Range column, define a range that will be displayed to the user during the audit. Values must be separated by commas. You can enter any combination of values that are in the range from 0 to 10. Weighted: Allows rating the item as Great, Good, or Fail. Note that when working with the template you will see 3 columns — Good, Fair, and Failed. Specify the scores that should be assigned to each of these options (for example, 7 for Good, 5 for Fair, and 0 for Failed).
A word of caution on the Template columns
There is a slight difference between the columns in the Site Planning Template and the options available in the ServiceChannel Mobile app. The template lists these option ranges as Good/Fair/Failed in the configuration columns, while the mobile app's ranges are Great/Good/Fail. When using the template, make sure the column headers are Good/Fair/Failed.
- Percentage: Enables providing a percentage-based rating. A percentage value between 0 and 100% represents the item condition where 0% is the worst and 100% is best.
Pass Threshold determines the minimum rating value that is sufficient to pass the audit. This parameter is required in the Numeric, Weighted, and Percentage rating systems. If the auditor’s rating is equal to or greater than the specified value, the pass flag is attributed to the checklist item. For example, if the threshold is 70% in the Percentage rating system, any rating value from 70 to 100% indicates that the checklist item has successfully passed the audit.
One way to choose which rating system to use is to think of the level of acceptance of an item. You can assign the Pass or Fail system to items that are either acceptable or not, such as an audio system that either works or not. The Weighted rating system provides a greater level of detail. For items with varying degrees of acceptance, such as a carpet that can slowly show wear and tear, you can apply the Numeric system. The Percentage system is a good choice when you need to show the percentage of items that are in good condition, for example, the number of lights for which no issues were detected.
Here is an example of configuring the rating system options on the Assign CLI to CL tab in the site audit template.
The image below illustrates the rating options configured in the template. Whether the pass or fail flag will be assigned to an audited item depends on the Pass Threshold value.
Configuring Checklist Item Impact
Checklist items can differ in their impact on the audit score. Some checklist items can be so important that the audit is scored 1% when one of these items fails even if all other things are perfectly done. On the other hand, there can be less important things that may be inspected but will be ignored when the audit score is calculated.
In the Site Audit template, you can configure the importance of specific checklist items. This will ensure a more objective scoring as well as help auditors pay more attention to the critical issues and do not to waste time on less important things.
In the CI Impact column of the Assign CLI to CL tab, add a CI impact for required checklist items:
- Veto means that this item has the highest impact on the audit score. When at least one of the Veto checklist items is rated as Fail, the total score of the audit drops to 1%. However, when the same item gets Pass, it is counted as a regular checklist item.
- Ignore means that this item is ignored when the audit score is calculated.
- An empty cell means that this checklist item is counted as a regular item.
- Download the appropriate Site Audit Template.
- Areas tab, Name column. Enter the desired areas, starting on row 2. Once all areas are entered, copy them and paste into the Area column of the Assign CL to Area tab.
- Checklists (CL) tab, Name column. Enter the desired rooms or spaces, starting on row 2. Once all checklists are entered, copy them and paste into the Checklist column of the Assign CL to Area tab and into the Checklist column of the Assign CLI to CL tab.
- Checklist Items (CI) tab, Short Description column. Enter the desired items to check while conducting an audit. Once all checklist items are entered, copy them and paste into the Checklist Item column of the Assign CLI to CL tab.
- (Optional) Checklist Items (CI) tab, Detailed Description column. Enter brief instructions as guidance on what an auditor should look for while inspecting the checklist item.
- Assign CL to Area tab. Align each area to one appropriate checklist, and then fill in the appropriate areas for the remaining checklists. You may then sort the list so that all areas are together.
- Assign CLI to CL tab:
- Checklists and Checklist Item columns. Align each checklist to one appropriate checklist item, and then fill in the appropriate checklists for the remaining checklist items. You may then sort the list so that all checklists are together.
- Rating System: Enter a rating system for each checklist item: PF, Numeric, Weighted, or Percentage.
- Pass Threshold: For the Numeric, Weighted, and Percentage rating systems, specify the minimum value that is sufficient to pass the audit. Depending on the rating system, the threshold value is either a number or a percentage.
- Range: This parameter is required when the Numeric rating system is assigned. Provide a range of values separated by commas that the auditor will see when evaluating the item condition.
- Good, Fair, and Failed: For items with the Weighted rating system selected, enter numeric values that should be assigned to each of these options.
- (Optional) CI Impact column. Enter an impact for required checklist items, either Veto or Ignore.
- Save the template as either an XLS or XLSX file. You will then upload this template to Site Planning Manager - Admin.
It is recommended to save the file using the same name as for the audit type for better template management. For example, for the Loss Prevention audit type, name the file Loss Prevention.xls.
You must save the template as either an XLS or XLSX file. Site Planning Manager cannot accept any other file type, including CSV files.
Site Planning Manager Templates