Richard Day and Alannah Comerford explain how Chartered Accountants can enhance their organisations’ data transformation capabilities using Alteryx.
With the recent changes to the FAE syllabus, which now includes Tableau, Alteryx and UIPath, the new crop of qualified Chartered Accountants will bring these skills into the workplace. In this article, we will discuss the advantages of using a data processing tool such as Alteryx. The Institute has recognised the value that Alteryx provides, and the onus is now on all of us to leverage the skills and knowledge our bright new crop of young accountants will bring to the workplace.
Reflect on the tasks we are required to complete regularly as part of our role as a modern-day Chartered Accountant. Many of us would find that, despite not considering ourselves to be data experts, we cleanse, filter, summarise, append and cross-reference datasets – even if we don’t think of our actions in these terms. We often turn to spreadsheets to do these data-heavy tasks.
Many of us have picked up a spreadsheet which has multiple tabs, complex formulae, thousands of rows of data and found it challenging to figure out what is happening. Also, these complex transformations and calculations often have undocumented steps, can be slow to update, require manual effort to repeat, and generally could be better controlled.
Alteryx is a data processing tool that facilitates data transformations and calculations in a controlled and repeatable manner and can revolutionise how we process and analyse data.
Given the user-centric design and functionality, all accountants should be able to pick up Alteryx and get started. In Alteryx, steps in a process are represented graphically in a format called a “workflow”. It should, therefore, be far easier for a colleague to view such a workflow and figure out what is happening than if they were to pick up a spreadsheet, as described above.
Repeatable data transformation
Take the simple scenario where we need to carry out a task that requires information from two or more systems. We typically export information from each system into separate files and then transfer these files to tabs in a single spreadsheet to carry out the task by summarising information from one tab and looking it up in the other.
In an ideal world, with fully integrated systems perfectly tailored to all of our needs, this would be possible to do automatically on the systems themselves. However, this level of integration is not a reality for most of us and as a result, we regularly spend our time on these data transformation tasks. In many cases, data manipulation often represents a significant proportion of the time taken and does not leave much time for the accountant to review and consider the results.
Alteryx can help with the data transformation and processing elements of such tasks. It provides the accountant with a way to build a workflow to complete each of the required steps each time such analysis is performed. It would then be a matter of refreshing the input files as needed and running the workflow, eliminating almost all of the time associated with the transformation of the data (see Figure 1).
Similarly, Alteryx offers excellent value to an accountant by cleansing the data. In a world with imperfect and unintegrated systems, there may be data quality issues as well as inconsistent data across different systems. We have become used to removing leading zeros in an account or reference number, correcting misspelt names, or translating names of customers or products, so they match across systems. Alteryx allows us to build these data cleansing routines into a workflow to ensure that they are automatically performed the same way any time a file of this type is processed, unlocking real efficiencies.
Where we need to perform tasks such as sorting, manipulating or joining files of any reasonable scale, Alteryx comes into its own. Standard steps that are performed regularly are prime targets for Alteryx. This affords excellent opportunities for Chartered Accountants to begin using this tool, as they should have an exceptional understanding of the activity required and the associated inputs and outputs.
Robust data processing
While many of the functions discussed above would be possible with other tools, Alteryx also has the added advantage of allowing the user to make the data transformation process more robust. While at first, it may be slightly more challenging to use Alteryx rather than filtering, sorting and using copy and paste in a spreadsheet, a Chartered Accountant will quickly become familiar with the tool given its graphical nature.
Also, the rigour that is brought to a process by a user deliberately building specific steps into a workflow lends itself to robust processing. In Alteryx, it is also possible and recommended to build in controls to provide comfort over the completeness and accuracy of the information being manipulated at critical stages of the process, assuring that all required data is included and that the result is accurate.
The processing is a little more opaque since it generally sits in data files rather than yet another tab on a spreadsheet. You should, therefore, build in the ability to browse the interim data at various stages of your process so you can troubleshoot or review how it looks and check that the different steps are performing as expected.
When performing calculations or analysis in a spreadsheet, a small change can cause an error in a set of calculations, and it can be challenging to identify where the error is occurring. There may be hundreds or even thousands of iterations of a formula. As a result, we often see data anomalies fixed with hard-coded values. This is much better in Alteryx as good design will allow errors to be identified and a user cannot revert to hard-coding values, which may not be appropriate for future iterations of a calculation.
It is also easier with Alteryx to ensure that inputs are used as provided. It provides a mechanism to revert to the source data when required, which also contributes to the robustness of a process.
Processing at scale
In Alteryx, tasks can also be carried out using large volumes of data that would only have been attempted by the bravest of spreadsheet proponents, making tasks that were previously tricky (or in some cases, impossible) more feasible.
Some spreadsheets have an outer limit of up to one million lines, but in many cases, the practical limit is much lower since adding formulae to files with only thousands of records can cause them to slow down drastically. Alteryx can handle the bigger datasets we now encounter. In addition, making changes to calculations in spreadsheets can be time-consuming and many have encountered spreadsheet files crashing.
Alteryx generally allows changes to be made and re-run quickly. Many workflows will run in seconds while processing simple transformations for millions of records should only take a few minutes. This is a huge advantage when building a workflow, as it enables the user to experiment efficiently and add additional functionality with ease.
Documentation
The ability to review a workflow and the controls built into it affords management excellent oversight of calculations that may drive critical outputs. Detailed documentation of processes is something that is not always present, especially for data-heavy tasks that began as an ad hoc exercise but are now embedded in everyday activities.
Performing data transformation in a tool such as Alteryx and adding annotations to workflows has the added benefit of encouraging the user to define and document what is happening in a process.
Outputs
The outputs from Alteryx workflows can be produced in a range of formats. It may be that the most convenient output from your Alteryx workflow is a spreadsheet, such as debtors who are over their credit limit. It is also possible for visualisations, such as those covered in previous articles, to be refreshed automatically with data files produced from Alteryx. This can help Chartered Accountants provide significant value to their businesses.
Significant value
Alteryx may not be required when you are working with easy-to-manipulate data on a once-off basis. In an increasingly regulated and controlled business environment, however, the benefits associated with repeatable, efficient and documented data transformations are of significant value.
As we are supported by our soon to be qualified Chartered Accountants on our data analytics journey, we encourage you to share your experiences within your teams. Knowledge-sharing and an open attitude to the improvements technology can bring will breed success.
Richard Day FCA is Partner, Data Analytics & Assurance, at PwC Ireland.
Alannah Comerford ACA is Senior Manager, Data Analytics & Assurance, at PwC Ireland.
LOADING...