One member, challenged to create a solution without external resources, automated FX processes through in-house ‘citizen developers.’
While a TMS can offer an integrated treasury technology solution, no single system can support the full range of automation requirements. In the case of one fast-growing, high-tech NeuGroup member company, the deficits surfaced in the area of FX transaction management and reporting. Faced with a need to improve the process, shorten cycle time and reduce cost with no additional resources, the company decided to build the automations in-house, leveraging its access to so-called citizen developers.
- The member who shared her team’s journey at a recent meeting of NeuGroup for Foreign Exchange 2 said her company’s revenue doubled in a short span of time, but her FX team remained the same size. On top of this, its TMS is only equipped to handle cash reporting, positioning and the majority of intercompany fundings, with all FX hedging handled separately.
- “Thus, we augmented our processes with automation tools to better communicate with our fellow treasury cash managers,” the member said.
System for the self-taught. Members on the team trained themselves and one another through online coding boot camps and internal training sessions. They ended up implementing three tools that require increasing degrees of expertise while functioning together.
- A license in the efficient (but relatively pricey) self-service data tool Alteryx Designer allows the team to access large datasets from its ERP, which are then pulled into the programming language Python to manipulate and analyze the data. Next, the data is exported into an Excel spreadsheet with a detailed set of macros scripted in VBA (visual basic for applications).
- While the flow may sound complicated, in the end it dramatically cuts the amount of time the FX team spends by eliminating manual processes.
Top of the line. Alteryx Designer, which allows users to create automations using flow charts and a simple, drag-and-drop user interface, is the first stop. The member calls the tool “coding for dummies,” as its capabilities aren’t quite as open as a programming language, but she said it is very helpful for cleaning up large datasets pulled from SAP S/4HANA, “digging into the details and finding a needle in the haystack in a fraction of the time.”
- The member was fortunate that treasury had a license to spare for Alteryx, as the tool requires a pretty steep resource commitment compared to free-to-use programming languages.
- The team uses Alteryx to do the first step in updating balance sheet exposure by pulling a report, scrubbing the data and exporting it into a clean an Excel spreadsheet. A process that once took 20 minutes each day now happens in 11 seconds at the push of a button.
The second stop: Python. The most powerful tool the member uses is Python, an open-source programming language. It takes time to learn, but the member’s team now uses Python code to model and forecast the impact of cash flow hedges.
- The company has millions of invoices per quarter for its four largest traded currencies, which she said caused it to “outgrow doing calculations in Excel, which was no longer an option. I’m sure everyone can understand how frustrating and impossible it is to manipulate massive amounts of data in Excel.”
- “From my experience, it’s actually not super difficult to get set up in Python,” she said. “I usually get a little intimidated trying to get set up with new technology, but this was actually pretty straightforward. It’s nice because there is no monetary cost to it: The world is your oyster.”
- Leveraging her new skill with the tool and assistance from Python-proficient team members, the company now uses a Python function that takes the code from Alteryx and applies forecasting and cash flow hedging models, which previously ran on a manual basis.
Virtual connectivity. The data ends up in Excel, which the member admitted is not the best tool for processing large data sets. But VBA, which she called “the granddaddy automation tool,” allows users to create macros, which are simple, repeatable tasks within Excel that can assist with reporting.
- At the member’s company, “FX produces a monthly forecast deliverable to FP&A surrounding cash flow hedging impact,” tracking the impact throughout the quarter, up to six times per quarter.
- The employee in charge of the company’s use of VBA explained that team members were once doing thousands of calculations per quarter that are now completed in an instant. “We hedge with options, about 60 options every quarter.” he said. The company amortizes its options out 12 months, “so if you take 300 options at minimum over 12 months, that’s 3,600 lines of information that needs to be accurate.”
- Though he admits the process is pretty complicated, the member said it “has been very beneficial to provide quick and accurate information,” and the team couldn’t have done it without the assistance of automated tools.
