Starting from Zero - Audit

1. Audit Schedule template

The audit schedule template is designed to give a starting point in organising your audits. There is a template and worked example included in this pack.

When setting up your schedule you will need to decide what sort of cycle your audits will follow – will it be yearly, and if so calendar or financial?

The template follows a schedule aligned to the financial year.

Columns A-F contain the key details of your audit, such as the title, area of the service that is audited, the type of audit, frequency, the senior colleague responsible for the audit and the member of staff who carries it out.

The schedule part of the template is designed to calculate whether your audits are running on time or not.

The months with an audit taking place can be noted with an ‘X’ in the respective ‘Due’ line.

The outcome – whether it happened on time or not, or if it’s not applicable (i.e. the audit didn’t happen due to extenuation circumstances) can be noted in the respective ‘on time?’ row.

From this the sheet will calculate the % on time for each audit. Above 95% shows green, 80% to 95% is orange and below 80% is red.

You can add further rows by copying the last one and pasting new ones below.

2. Audit tool

The audit tool contains a direct guide on its set-up and use within it. This is found on the ‘Quick guide’ tab.

Please note when first opening the audit tool you will need to enable macros:

The audit tools contained within this resource pack is designed to help standardise how your audits are carried out. It follows the principle of ‘compliance’, meaning any questions created for it should have a ‘Yes’ or ‘No’ answer. The only exception to this is if a response is non complaint but there is a justified reason e.g. it may be a certain test was not performed because a piece of equipment malfunctioned. It is important to make it clear what can be considered as a justified break in compliance to whoever is carrying out your audit.

Using these responses the audit tool will provide an average result for each month the audit was completed and an overall ‘average of averages’ for the year.

It is set up to follow a financial year – starting in April and ending March the following year. The sheet itself contains a how to for its use. The version provided is in template format, giving a blank version each time it is opened, when saving it, it must be saved as a macro enabled workbook to use all of its features.

Each month of the financial year has its own tab with its respective results for review. You can also view the ‘raw’ data for each month and make changes to it if needed – this can be accessed through the audit menu.

Reports for each month can be created to support in sharing results. This can function can also be accessed through the audit menu.

The audit tool contains a question bank for up to 40 audits. The idea being you can populate a template with all your compliance audits then create a new copy for each audit as required.

Questions, along with the audit title and any specific targets per question should be added to this tab before implementing the audit tool.

Please note that whilst the audit tool may contain questions for multiple audits, it is not intended that you run multiple audits from one copy. In short, you must create a new copy of the tool for each audit.

A worked version of this sheet is included within the resource pack.

The tool is offered in template and non-template versions. You may find it easier to populate the questions of the template and create copies from there, or copy a blank version and add questions audit by audit.

The RAG rating can be altered in the ‘year’ tab, changing the green and red values will change everything throughout the sheet.

Please note: the sheet is presented as ‘open’ to allow for changes you wish to make, however, changes to the layout of ‘Raw’ and ‘data’ tabs will most likely result in the sheet becoming unusable.

Altering the template version:

if you wish to alter the original you will need to open the file directly.

When saving a copy created from the template ensure you save it as a ‘macro enabled’ workbook.

The audit tool is set to auto save itself after use.