Hkust Saves 15 Hours/Month with Code Free Expense Allocation Model

[ThalitaC]

Author:

Scorpio Wong

Role:

System Analyst, Information Systems Office

Company:

Hong Kong University of Science and Technology

Overview of Use Case

The Hong Kong University of Science and Technology (HKUST) was founded in 1989 as a research-focused university with the mission to advance learning and knowledge through teaching and research and assist in the economic and social development of Hong Kong. Today, HKUST has risen through the ranks and become a global powerhouse in tertiary education as a result of its continuous pursuit of excellence, leaving its mark in life science, engineering, business education, humanities and social science, and much more. HKUST has 16,054 student enrolments, 690+ faculty members and teaching staff, and 52 research centres. Scorpio Wong is part of the Information Systems Office (ISO) that supports faculties and departments with their technological needs. Scorpio supports the finance function in his role, taking charge of the Alteryx implementation, Qlik reporting design, and ERP systems.

Describe the business challenge or problem you needed to solve

The Finance Office (FO) at HKUST had an Expense Allocation model built with complicated formulas across 20-30 worksheets on Excel. The University Grants Committee (UGC) of Hong Kong requires for UGC-funded universities to submit the expenses report, sub-categorized by each department. The original solution Scorpio built on Excel contained over one million complex formulas that span across 20-30 worksheets which would take about an hour to run. He later made some enhancements to the performance with VBA to bring the runtime down to three minutes, but whenever finance needed to make a calculation change, especially when it involves more than one dimension, it will take him one to two man days to make the amendments. Furthermore, as Excel is not able to hold all of their 50,000 – 60,000 rows of data, these changes are needed to be applied and managed across all the spreadsheets that were being extracted from their Financial Management System (FMS) and Oracle databases. This report is required to be submitted monthly, taking up to at least 16 hours a month to prepare the report.  

Describe your working solution

When Scorpio’s manager, Derry Fong, Head of Information Systems, suggested for him to take a look if Alteryx could solve his problems, he downloaded the Alteryx Designer 14-day trial, spent two days on the Interactive lessons on the Community, and five to six days to build his first workflow by transferring all the Excel calculations to Alteryx Designer.

The workflow consisted of the following key functions:

1. Pulling data from the FMS 
2. Referencing data from other databases to be mapped to financial data for costing calculations
3. Data preparation and calculation of costing allocations
4. Blending the results together and summarizing for reporting output
5. Output into Excel to be reviewed and submitted to UGC 

The following features were extremely helpful in managing this workflow:

1. The ability to directly connect to the Oracle databases with the ODBC connector
2. Join, Union and Append tools were exceptionally useful in making references to multiple tables and keys for each department
3. Summarize tool helps with grouping results, which would have been hard to manage future changes in Excel with the SumIfs function for multiple conditions

After completing the first workflow, more time was spent on tweaking the conditions for changes that the Finance Office required and formatting the output for UGC’s requirement. In about half a year when the project was completed, the workflow was then handed over to the Finance team to be maintained. Due to the ease of use of Alteryx, non-coders in the finance team are also able to make changes to the conditions themselves and no longer having to rely on the ISO.

Describe the benefits you have achieved

The ISO and Finance teams both saw significant benefits since they moved their expense allocation calculations onto Alteryx:

1. Saved 15 hours monthly when making changes to calculations and conditions
2. Tools are easy for non-technical colleagues in the finance team to understand and be able to manage their own calculations without the reliance on the ISO team for coding, resulting in faster time to delivery by managing their business process themselves
3. Workflows are repeatable and reusable
4. Unlike SQL scripting or VBA (Visual Basic Administrator), a user can get intermediate results at each tool to understand the flow of data and identify areas that require changes; allowing for data transparency along the data flow without having to wait for a script to be completed

Now that the ISO has freed up some time, focus can be put on helping colleagues from other departments to transform their heavy excel processes. Alteryx is the choice of platform for future projects that will deliver faster time to deliver with its powerful calculation management and power with performance. For Scorpio, this new skillset with his experience in finance will benefit his future career development since many companies in the financial sector are also using Alteryx.

The current costing model can be applied to budget expenses to forecast next year’s budget. With the power of predictive analytics, the Finance Office is looking into creating budgeting simulations to anticipate future expenses. As more projects are being built out with Alteryx that have higher frequency of having to be executed, the Finance Office can benefit from Alteryx Server by automating these processes to be implemented in the near future.

It took Scorpio two days to learn Alteryx through the online Interactive Lessons on the Alteryx Community. Users from the Finance Office who are familiar with their own data and the calculations for the business pick up Alteryx very quickly as the application is quite similar to Excel. So, it is advised that understanding Excel formulas is a minimal requirement for the costing allocation project. With the help of our Hong Kong local partner, Velocity Business Solutions, introduction sessions and workshop that was run for other department users, they also feedback that Alteryx will be very useful in their own projects that can get them FTE and time-savings. Now, the ISO manages 13 other projects in 6 offices within HKUST and is looking forward to having Alteryx implemented as the enterprise solution for data prep and analytics.