Name: Cheryl Erickson
Title: Sr. Manager, Finance
Company: Thomson Reuters
Collaborators: Harsha G, Quadeer Syed, Anirudh Avantsa, Trisha Zimmerman, Matt Dybing, Mary Devaney, Patrick Fitzgerald, Christopher Martin, Cheryl Erickson
Overview of Use Case
As part of Controllership’s month-end cost assurance process, the team would run GL line item queries for six Corporate organizations from our reporting system, map in master data information, segregate the data by expense category, and create pivot tables in order to review financially material postings for accuracy. The process was very time-consuming and we developed a use case for leveraging Alteryx to automate the process and remove this effort from the hands of the end-user.
This was our first application of Alteryx within Finance and Accounting, and as a result of this use case, we have collaborated across the company in three key ways:
- Sharing learnings about Alteryx’ powerful functionality and processing speed with others in the company who are also using Alteryx and brainstorming the myriad of possible uses
- Facilitating earlier adoption within the company by demonstrating our workflow to several different teams thereby exposing them to Alteryx so that they are able to visualize the exciting possibilities within their organizations
- More broadly increasing efficiency and productivity by distributing and utilizing the output of this one workflow for other processes that use the same data
Describe the business challenge or problem you needed to solve
Our month-end close process consists of a cost assurance review which is a part of our control environment requirements (Sarbanes-Oxley). All publicly-held corporations are required to perform certain financial reviews to ensure financial statement accuracy.
The cost assurance process is run twice a day for six days during month-end close (3 days pre-close and 3 days during close) and would take a total of 24-30 hours to complete each month-end. Because month-end close is a critical time for Controllership where the focus is completing analysis in a short period of time while maintaining accuracy, the goal was to automate the process as much as possible as well as to remove the pre-analysis work from the hands of the end user allowing their time to be better spent on analysis. Though we were new to Alteryx functionality after having first heard about Alteryx in a Tableau User Group meeting in which Jonathan Drummey presented, it was strongly believed that the cost assurance process could be automated via a combination of scheduled Oracle reports, Alteryx workflow, Excel macros, and eventually Alteryx scheduling and publishing functionality.
Describe your working solution
We are using Alteryx workflow - specifically Filter, Union, Join, Unique, Select, and Comment tools.
We are using a repeatable workflow consisting of six – seven individual workflows. The solution begins by scheduling and importing Oracle data source files in a .xlsx format. This data is modified using the ‘Filter – Change data type’ and ‘Select’ tools.
In addition to loading the GL line item data into the Alteryx workflow, three source files containing master data hierarchy information (cost center, profit center, and location) are also loaded and we use Alteryx to map to the GL line item data using the ‘Join’ tool. The ‘Unique’ tool is then used to join the primary key to only unique values of the foreign key from the secondary data sources.
The ‘Union’ tool is used after each join to ensure all the records from the original data set are carried forward as opposed to only matching records.
Multiple ‘Join’/’Union’ tool combinations are used to bring in additional data points for the cost assurance review.
‘Comments’ are used to document each step in the process.
The final output is written into macro enabled excel worksheets using a ‘Overwrite sheet/drop’ method.
We are not currently using Alteryx scheduling functionality although it is the intent to implement this functionality soon.
Describe the benefits you have achieved
- Allows Controllership team to focus on more value-add month-end analysis by leveraging:
- Oracle report scheduling so the data is available and ready to load into Alteryx when needed eliminating the impact to the end user of report/query wait times
- Alteryx capability to load and map multiple source files together and generate needed data views
- The power of Alteryx workflow to complete the post-query process on many thousands of month and YTD GL line items in 24 minutes per month
- Excel macros to generate all data pivots by expense category
- Eliminates an overall end user impact of 24-30 hours per month.
- Built an initial knowledge base of Alteryx within our Center of Excellence team who supports Corporate Controllership and Corporate Finance. This has enabled us to begin building several additional Alteryx use cases for various processes that will have even greater productivity impacts for our end users because they can be leveraged across the enterprise.
- We have identified employees in other parts of the company who are using Alteryx and have collaborated with them brainstorming and designing workflow solutions.
- Conducted several demonstration sessions of the use case to internal teams including Controllership, Finance, Cash Flow, Master Data, Treasury, Tax, and HR teams exposing them to Alteryx functionality so that they are able to visualize the impact and possibilities within their organizations.
- Leveraging the workflow output of this use case to distribute the results to the Corporate Finance organization avoiding their need to run ad-hoc queries of the same line item data for their month-end and forecast variance analyses.
- Exploring leveraging the workflow output to save the Center of Excellence team time in creating financial reports that include line item detail as supporting documentation.
- Customer satisfaction – We rolled out the workflow process February month-end 2018, and the Controllership team has commented a number of times how much time-savings this use case has provided to them during their most critical time of the month.
- What we have learned about Alteryx thus far has resulted in us being very excited to explore Alteryx further including powerful features such as predictive analysis.