​Three Steps to Building Heat Maps

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

Comments Views
Building heat maps

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.

Restructure data 

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).

Build pivot table 

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).

Pivot table 

The resulting pivot table should resemble the illustration below.

Pivot table 

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).

Heat map   

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:

Heat map 

The completed heat map should resemble the illustration below.

Heat map 

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.​



Comment on this article

comments powered by Disqus
  • MNP_Natonal Can Conf_Sept2017_Premium 1
  • SCCE_Aug2017_Prem 2
  • IIA FallTraining_Sept2017_Prem3