Data Analytics - An Introduction to Application
June 21, 2019
When I was a junior in college, I had to take a finance class as part of my degree requirements. If you have a business degree, you probably know the class to which I am referring. Each test contained a handful of questions that were lengthy and each answer required identifying the correct formula to use to find the answer. When it came time for the final, I did an analysis, like we all have done, to determine what I needed to make in order to get the grade I wanted at the end of the semester. Realizing how little I had to study, since I had an A already, I decided to attend a tacky Christmas sweater party rather than pull an all-nighter. It was at this party that I met my wife, who recently gave birth to our first baby boy.
So what is the moral of the story? Data analytics can land you a gorgeous wife and a cute kid! Data analytics can also help you stay ahead of the regulators, such as with grant effort reporting.
Although the process of building an analytics program involves a step-by-step framework, it is nearly impossible to execute without realizing at some point that a detail has been missed along the way. This missing information needs to be taken into consideration and included back into the analysis. This is important to keep in mind, especially during the kick-off meeting and subsequent meetings, and throughout the project to meet expectations of management.
Step One: Understand the Business
This is Audit 101—what is the risk? By understanding the risks, we can identify the specific area we want to build our analytics project around. The crux of the audit is the identification of high-risk areas. This involves identifying which controls need to be tested to determine whether the mitigation of risk is sufficient.
Say the high-risk area is grant expenditures and we need to understand the burn rate for all grants. Now that we understand the business process and the associated risks, it is time to understand the data.
Step Two: Understand the Data
We need to understand where the data is coming from, where in the data flow it can be modified, who has access, and who can make modifications.
We need to understand where the data is coming from, where in the data flow it can be modified, who has access, and who can make modifications. Typically, the data is from a system that is part of the financial statement audit, so we can place reliance on testing already performed. However, we also need to perform data validation procedures to determine if our date parameters match our scope.
Are there blank fields? Are there duplicate records? Are the calculated fields in the database layer accurate? There is a multitude of other procedures to determine whether the dataset is accurate and complete. If the data is not complete and accurate then neither are the results.
We also need to understand which fields are required. This drives home the point that the entire analytics process is cyclical. Finally, we must also understand the business process.
Step Three: Data Preparation
We determined the data is complete and accurate, and now it is time to clean and merge the data to yield meaningful results. It is unlikely that all the data we will need will be in one perfectly neat table in the database. To use our previous example, the grant data (e.g., award number, award start, award end, and award close date) is typically stored in a table separate from the expenditure data. Those tables will need to be joined together.
Perhaps award number and organization are in a single field, and for reporting purposes, the business wants a breakdown based on the organization. In this case, we will need to create a field just for the award number and just for the organization. Depending on the complexity of the dataset and how disparate the data sources are, this is often the most labor-intensive aspect of the process.
Step Four: Modeling
Data preparation and modeling go hand-in-hand, similar to the other steps. Modeling is about applying the business rules and the requirements developed with management. Say management wants to be able to identify all awards in which 25% of the budget was spent and 80% of the award days have been spent. These would be high-risk since they might be tempted to spend the remainder of their budget before the grant ends.
For example, a $1-million grant lasting one year, with a start date of 1/1/2018, that has spent 25% of its budget and 80% of its time, would have $750,000 left as of 11/1/2018 (if rounded up to the next month). By applying the model and adding a field that would indicate a simple “Yes” or ”No,” management can quickly identify the highest risk awards rather than scrolling through each award individually every month.
Over time, management might want to modify the percentages if they are not getting the expected results (in this case, the model would require updating). When possible, though, we should allow management to make these modifications themselves through an interface developed as part of the platform. This not only best serves management, but it also relieves the burden of having to maintain the model. At this point, it should be no surprise that when management has a better understanding of how data can be used to monitor and drive decision-making they will ask for additional functionality. They may ask for a separate analysis all together, in addition to the initial analyses.
Scope creep can be difficult to manage. Therefore, it is best to revert to step one and understand the risk that would be mitigated through the additional functionality or through the separate analysis. These conversations can be best handled by agreeing to take a look at what data we currently have versus what would be needed, plus the additional time it would take to increase the scope. It can also be helpful to keep the additional project in a follow-up file to revisit once the initial project is complete.
Step five: Evaluation
The data is cleansed and the model is built, so now it is time to hand the results over to management, right? Not quite. Inevitably, the business process was not fully understood. There are times where the process works a different way and that data needs to be included in the analysis. Or, perhaps a mistake was made in the data preparation or data modeling phase. Invariably, this will not be realized until after management has inquired about suspicious activity from award winners.
Step Six: Deployment
With management acceptance of the platform, it is time to automate the process to allow for continuous monitoring. Keep in mind that automation should be planned for throughout each step of the engagement. When working with the data owner, be aware of how the data will be provided on a continuous basis so there is no need to manually obtain and clean the data each week, month, or quarter.
The time spent manually processing data when multiple platforms are deployed does not allow for taking on new projects based on newly identified risks. Of course, maintenance of the platform is expected as table names change, the model is refined, database upgrades are performed, and developments in the business take hold. With management able to monitor expenditure burn rate continuously, we can now deliver the final report.
About the Author
Trent Russell
Trent Russell is the CEO of Greenskies Analytics where he develops value-add and risk-based analytics platforms for Internal Audit departments.
Read Full Author Bio