March 2014

Three Steps to Building Heat Maps

Heat maps are a useful and easy-to-make tool for visualizing risk data.

Larry C. Herzog Butler

We have all heard about the hot topics and tools in internal audit — data analytics, big data, heat maps, data visualization, etc. But how many practitioners have implemented these tools to build a graphical representation of data? Heat maps can be a valuable tool for communicating risks, trends, and opportunities to an organization’s stakeholders such as audit committees, executives, and process owners. Heat maps facilitate discussions by conveying information in a form that is more appealing than row upon row of data.

In three easy steps using familiar software, Microsoft Excel, auditors can build their first heat map. Note that some steps or menus may differ, depending on the version of Excel.

1. OBTAIN AND RESTRUCTURE DATA

Begin by obtaining the raw data. In this example, I obtained new hire and termination data from a medium-size retail company. Once auditors have the data, restructure the data by filtering it to only include the last 12 months. Then, add the “day” and “month” columns. Use the formula “=text(b2,”dddd”)” to yield the day of the week. Use the formula “=text(b2,”mmmm”)” to yield the month. Note that only the first 10 employees are shown in the illustration below. The actual data has 405 new hires.

 

Image1

 

2. BUILD PIVOT TABLE

A pivot table is a function within Excel that enables a user to efficiently summarize large amounts of data for analysis. Build a pivot table by selecting the “Day” and “Month” data in columns C and D (see next image).

 

Image2 

 

Under the “Insert” tab, select “Pivot Table” from the “Tables” options. Excel will automatically generate the pivot table in a new worksheet. When the pivot table has been created, drag the “Days” field into the “Values” area of the Pivot Table Builder (see next image). 

 

Image3

 

The resulting pivot table should resemble the illustration below.

 

Image4

 

 3. BUILD HEAT MAP

Paste data into a new sheet, adjust the row heights and column widths, and add month and day headings. Under the “Home” tab, highlight data and select “Conditional Formatting” (see next image).

 

Image5

 

Select “New Rule…” from the “Conditional Formatting” drop-down menu. Select the “Rule Type” and “Rule Description” options shown in the next image and click OK:  

 

Image6 

 

The completed heat map should resemble the illustration below.

 

Image7

 

THE COMPLETED MAP

With the heat map created, it is now time for analysis. Internal auditors can use heat maps in a variety of areas, including risk assessment, fraud detection, and audit reporting. What does the map depicted above tell you? What if I said this data comes from a medium-sized retailer whose biggest day of sales occurs during Black Friday each year?

Auditors can make many changes to their heat map, such as adjusting the percentiles or colors to better display the data to their audience. Play with it and have fun, but always remember to accurately reflect the underlying data.

Larry C. Herzog Butler, CIA, CPA, CRMA, CGMA, is managing director of Pharos Consulting Group in Los Angeles.

To comment on this article, email the author at larry.herzog-butler@theiia.org.


Share This Article:    

 

Subscribe_June 2014 

IIA_AllStar_July2014

 IIA_AllStar_July2014

IIA Academic_Nov 2013

IIA SmartBrief

 IIA Vision University

 

 Twitter

facebook IAO 

IA APP