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.
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).
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).
The resulting pivot table should resemble the illustration below.
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).
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:
The completed heat map should resemble the illustration below.
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.