Tracking Incidents And Functional Changes In A System

Over the years, I have worked for quite a few organizations. In any organization, tracking incidents and functional changes in a system is a very challenging task. Unfortunately, most places don't do a good job in tracking these changes. Nevertheless, I found this Excel template to be a very cheap and effective tool for tracking incidents and functional changes in a system.

The tracking document has three main sections. The summary page, the open incidents page(s), and the closed incidents page. If needed, another section can be added for action items. These sections are discussed in detail below:

The Summary Page
The Summary Page has a simple layout that show the number of incidents that are open, and the number of incidents that have been closed. There are additional drill down sections that shows the open incidents for each department (i.e., dept 1, dept 2, etc.). The open incidents for each department is categorized by priority (i.e., High, Medium, Low). The priority count is calculated by a macro that inputs the value directly from the 'severity level' column from a particular department's open incidents page. the closed counts are directly input from the closed incidents page, using an Excel VBA macro.

The page also contains several buttons for ease of use. In the example below, the first two buttons opens the 'Open Incidents' page for their respective departments. The third button opens the 'Closed Incidents' page. The fourth button prints summary page. The fifth button prints out the the entire tracking work book in a predetermined print layout.


Here is the Excel VBA macro code for calculating incidents in the summary page.

Here is the Excel VBA macro code for the buttons.

Assigning the Excel VBA macro code to the buttons.


The Open Incidents Page
The Open incidents page contain the functional changes that are yet to be completed. Ideally, there should be one Open Incidents page per department.


The incidents are color coded to depict whether the changes are being made on time, or the deadlines are slipping. The color of the row is assigned by a VBA macro that calculates the difference between the expected date (i.e., ETA) and the current date. If the difference between the ETA and current date is seven days or less, the incident row is given a yellow color. If the ETA has been missed,i.e., the current date has passed the ETA, the incident row is given a red color. Looking at the page, an analyst can immediately ascertain which incidents need to be resolved within a week, as well as which incidents have missed their deadlines.

Here is the Excel VBA macro code to color code the incident entries:

The Open Incidents page has the following columns (The list below is just a suggestion. You can add/delete as many columns as you want):

Rank: Out of all the incidents, which incident should be tackled foremost. Also, provides a way to sort all the incidents as management may wish.

Priority: Based on three categories - High, medium and low - which group of incidents should be worked on first.

Ticket Number: Every incident should be tagged with a ticket number for tracking and audit purposes.

Date Opened: When the incident was first reported.

Description: A brief description of the incident. Try to make it as descriptive as possible.

Business Owner: The unit that owns the incident or is the most impacted by it.

Assigned to: The person(s) who will be responsible for making the functional change.

ETA: The expected date when the functional change will be made.

Updates: This is probably the most important and useful section. Put a concise status of the open incident preceded by a date.

Impact: What system(s) are impacted by the functional change.

Severity level: How severe the open incident is (i.e., high, medium, low). It is important to fill out this field, because the summary table calculates the incidents by category from this field.

Status: Whether the status of the incident is open or closed. If the incident is listed in this page, the status should always be 'Open'.

Resolution: How the incident was resolved. If the incident is listed in this page, this field should remain blank.


The Closed Incidents Page
Finally, when an incident has been resolved or a functional change made, move the entire row from the Open Incidents page and transfer it to the Closed Incidents page. Fill out the resolution field. It is also important to change the status field from 'open' to 'closed', because the summary page calculates the number of closed incidents from this value.


You can also create a page for action items, which are system improvements that come up during discussion. These changes do not need to be worked on right away, but can be implemented during a future release.