Alteryx is a powerful self-service software for data automation and insights-driven analytics that we have been implementing heavily to help with finance automation specifically. At Billigence, we have helped several organisations in the adoption of the Alteryx software to streamline and automate manual, labour-intensive data processes. We’ve seen analysts move away from static excel spreadsheets and manual reporting to making more informed data-driven decisions, as well as producing in-depth analysis and predictive insights with just a few clicks.
To showcase the benefits of Alteryx in finance, we will be sharing five different use cases specifically targeting finance automation, accounting tasks and managing payroll processes.
Use Case #1 – Reconciliations of Assets and Liabilities
One of the common use cases for finance is working on the reconciliations of financial statements such as the balance sheet. Reconciliation of financial statements contributes to a significant portion of the workload for most finance professionals, especially during peak audit season. Compiling data sources with varying formats just to conduct a simple check on balances can be time consuming and tedious. Furthermore, human error in repetitive tasks, data processing or manual data entry may also hinder the accuracy of the final output. With Alteryx Designer, a simple workflow can easily replace the repetitive and manual reconciliation efforts.
In the above example, we are joining three separate data sources together to understand whether our company assets and liabilities dataset matches to the audit dataset. The input tool at the beginning of the workflow can read in data from various data sources such as Excel, SQL, Oracle or Snowflake. Alteryx is then able to consolidate all the different sources with the join tools and match conditional unique identifiers for basic data preparation. Additional preparation tools, such as the formula tool, filter tool or summarize tool can be introduced to identify missing information, mismatched asset-liabilities balances or to flag out the missing IDs from the various inputs.
On top of these, advanced reporting tools in Alteryx can allows finance employees to generate a customized report, as shown below, of the reconciliation findings and output an automated report summary with the click of a button.
These visual reports can provide business owner with a quick overview of the results, identifying any discrepancies with the accounts. The templated report summary which is generated with each workflow run without the need for manual intervention, allow analysts to save time spend for monthly or periodic reporting processes. We have seen this approach bring huge time savings for analysts across the entire business operations with the additional benefits of generating accurate data.
Use Case #2 – Reformat Legacy System Reports
The second use case in this blog post illustrates the formatting of legacy system-generated reports, those which cannot be easily processed using Excel spreadsheets. Reports from legacy accounting systems can come in various file formats and contain inconsistent table alignments.
Usually, it will require the tedious tasks of writing complex scripts or manually copying and pasting each item into Excel spreadsheet for additional analysis. These methods are often quite complex, costly, and very time consuming.
With Alteryx, various tools can be used to completely replace these tedious tasks. Employees can simply read in the required files and Alteryx will be able to do its job in extracting required information from the reports and perform reformatting for further analysis. The result via Alteryx is an output of clean data and insights into an Excel file or database for storage.
To demonstrate the benefits of Alteryx in extracting required information from a legacy report, we have a sample text file shown below. Typically, an analyst will have to copy and paste each row into Excel and manually clean the values before analysis.
In Alteryx, you can simply build a workflow as shown, using data preparation and parsing tools to cleanse and extract the required information into an ideal format. Once this is done, the formula tool can conduct ageing calculation based on the invoice date, so the correct Net Invoice Amount is assigned to the corresponding ageing column.
And voila, this workflow converted the unfriendly text file into a readable Excel file, enhanced with ageing calculation of current, 30 days, 60 days, 90 days and 120+ days.
Use Case #3 – Inventory Valuation (FIFO or LIFO)
With the large and frequent movements of inventory, it can be challenging to keep track and manage the different inventory account at any point in time. Additionally, inventory usually make up a significant portion of the assets for business that sells physical items, so it is crucial to ensure that the inventory value is accurate and recorded in a consistent manner. Alteryx can consolidate the large volume of dataset within the transaction ledger with a designated workflow logic, ensuring consistency in the calculation for each run of inventory valuation.
To demonstrate the use of Alteryx in inventory valuation, we have a sample data set with various purchase and sale prices of Item 1.
With the workflow shown below, we can easily add up the total quantity sold and append it to the purchase table as the “Total Monthly Sales” column, which will be used for the subsequent inventory valuation calculations.
Using the two most popular valuation methods, First-in First Out (FIFO) and Last-in First-Out (LIFO) method respectively, a workflow can be created to generate the value of the remaining inventory. For FIFO method, the “Date” column will be sorted in the ascending order, such that we have the first purchase date at the top of the table. The monthly sales are then matched against the quantity for each purchase to identify the remaining quantity of inventory at each transaction.
The computation of LIFO, is similar to FIFO with the difference of sorting the date in the descending order, having the latest date at the top of the table for the subsequent computations.
As shown below, a table can be generated from the above workflow, showing the inventory valuation using both the LIFO and FIFO method.
With these workflows and tools in place, employees can easily automate the tracking and calculation of inventory costs, providing the management and business owners with a clear and up-to-date financial position of their company at any point in time.
Use Case #4 – Automation of Financial KPI and metrics
Another use case for Alteryx is the automation of financial KPI and metrics calculation. KPI metrics are important for business and are crucial for the success of many businesses, as it provides insights into the underlying financial and operational strength for future financial planning. Every organization must keep track of many varied financial KPIs, such as Gross Profit Margin, Return on Sales, Net Profit Margin, Operating Cash Flow Ratio etc., It can be daunting at times for financial professionals to manage large volume of data, applying suitable formulas and computations to derive the metrics, especially with tight timelines.
Alteryx can effectively automate the calculations of KPI metrics and match distinct accounts across many sources within a workflow. These automated workflows can then be used for all subsequent runs for such metric calculations, generating benefits for greater speed consistency and accuracy in deriving the metrics.
For illustration purposes, a simple workflow to derive Year to Date (YTD) and Year on Year (YoY) is included below. To create new metrics, users can simply add in new tools or edit the configuration of the existing tools so required changes can be made. After saving the workflow, all subsequent runs will reflect outputs with the new changes.
Use Case #5 – Payroll Calculations
The final use case in this post is using Alteryx for automating payroll management. This is crucial to every organization as payroll makes up the largest proportion of business expenses. When handled manually, commissions, taxes, wage calculations, and other payroll tasks can be time-consuming. Furthermore, the payroll processing requires great precision to ensure employees are paid accurately and that the company complies with the tax laws.
One aspect of payroll is employee benefits, which consist of insurance reimbursement, retirement benefits, paid leave, study allowance as well as commissions from sales or performances. In the sample workflow below, we will be focusing on the commission computations.
Using Alteryx, you can easily join multiple datasets together, apply suitable computations and filter out relevant payroll data for analysis. From the above workflow, tools are used to compare the sales target to determine if employee qualify for the commissions and the commissions amount are being mapped to each employee. At the end of the workflow, the commissions are aggregated by each employee, generating the total commission each employee will receive.
Workflows for the other payroll computations can be designed as such, where the monthly employee earnings could be attained with the summation of all the payroll workflows. This will enable the finance team to save significant amount of time, relieving them the time and effort required to work out every aspect of the payroll every month.
Conclusion
Over the years, Alteryx has been successful in helping organisations with auditing and finance automation of their processes for faster and more accurate results. It has many demonstrated benefits, one of note is its ability to work with large volume datasets from varied sources, transforming raw data into valuable insights for organisations. By deploying Alteryx for our customers, we have seen how Alteryx can greatly enhance organisational analytics efficiency by replacing repetitive manual processes while ensuring higher accuracy and precision. This is especially relevant for the finance industry where there is a lot of bulk data that needs to be processed. Our clients have found that financial automation processes built on Alteryx have significantly impacted their accuracy and speed of delivery.
If you are interested in finding out more about Alteryx and its potential, please feel free to submit an enquiry via the form below. With the assistance of our Alteryx certified consultants, we look forward to assisting your organisation on your analytics journey.
Interested in learning more about Alteryx in Finance?
Watch our FREE Alteryx workflow videos on reconciliation and reporting, two typical use-cases audit & accounting companies regularly deal with, and learn how Alteryx can help automate associated manual, labour intensive tasks.
