Internal Audit – Interactive Report

First a Warning!!

To use this spreadsheet, you need excel 2013 or higher, due to the use of table slicers (not to be confused with pivot table slicers).

Internal Audit (IA) frequently reports to the board or supervisory committee. Typically, these groups are concerned with the areas of the firm that have the highest risk to the credit union. At the same time, they need to know the likelihood of these high-risk areas having a major problem. If the high-risk areas are well managed, the likelihood of a major problem is reduced. This spreadsheet can provide IA with an interactive dashboard, that can be used to display the degree of risk and at the same time, the likelihood of a problem developing for each department. There is also a third dimension that the board or committee might be concerned with, and that is how much time IA has allocated to review each department.

To accomplish this, the spreadsheet uses a 3D bubble, X,Y scatter chart. Risk is represented along the horizontal axis and the likelihood of a problem is along the vertical axis. The chart is divided into quarters. The upper right hand quarter contains the departments with the highest risk and the highest likelihoods of a problem existing. Likewise, the departments with the lowest risk and the lowest likelihood are in the lower left hand quarter. The size of the each bubble shows the relative time (in days) IA expects  spending on reviewing each area.

I assume that the user of this spreadsheet has some basic knowledge on how to use splicers. If not, do a Google search and watch some videos on splicers. There are five splicers on the Bubble Chart sheet. The risk is at the top of the chart (horizon)  with degrees of risk 1 through 10. How you determine the risk level for each department is up to you. Along the left hand side of the chart is the Likelihood splicer. This splicer is 10% to 90%. Over on the right hand side of the chart is the Days to Complete splicer (in blue). The more expected days to complete the work on a department, the larger the bubble. In this example chart, GL (general ledger) is the largest bubble with an estimated 7 days to complete. To the right of that are two more slicers for the abbreviations of each department that are shown on chart and the corresponding full name of the department.

If we want to show just the departments that are in the upper right hand quarter (highest risk and likelihood of a problem) click on the Risk splicer the number five and while holding the Shift key, click the number 10. Now on the Likelihood splicer click the .5 number and while holding down the Shift key, select the number .9.

The chart should like the above. Due to some large bubbles, it is difficult to see the abbreviations on each bubble. On the right hand side of the sheet however, you can see the abbreviations and names of the seven most risky departments with the highest Likelihoods of a problem.

To look at just Commercial Lending click the name on the far right hand side and the chart will look like this:

You see where Commercial Lending is on the chart and that it will take 5 days to complete the review. If the slicers get stuck, click the Rest Database button.

Other areas a chart like this might be useful would be in investments (risk vs return) or asset liability management.

 Download:  Audit_Bubble

Don Pistulka
Don Pistulka

Retired Credit Union CFO - Finance
Background: over 40 years in investments, asset/Liability management, banking, securities trader.
Worked for: California Credit Union, WesCorp, CalFed S&L, Crocker Bank, Carroll McEntee, Federal Home Loan Bank Board (D.C.), Western Asset Management, Security Pacific National Bank.

Leave a Reply

Your email address will not be published. Required fields are marked *