Cash Flow Forecasting in a Global Media Company

  • By Siddhant Jain, CTP, Senior Implementation Analyst (Treasury), AFP APAC Treasury Advisory Council
  • Published: 4/26/2024
APAC Cash Flow Forecasting Case Study

The necessity for a centralized treasury, and subsequent centralized cash flow forecasting, at a prominent global media company operating across more than 150 countries had become critical. With its central treasury team based in the U.K., each of the 150 countries from which the company operates managed its own cash flow forecasting, leading to significant issues starting with funding and market liquidity risks and ending with financial market risk.

Collaborating with TIS to leverage their tool CashOptix, the treasury team went about creating and implementing a plan to centralize and automate cash flow projections, thereby optimizing accuracy and minimizing manual efforts throughout the organization.

The following case study documents the journey of achieving comprehensive and streamlined global cash flow forecasting within the company.

The AFP Treasury Case Study series is designed to help you build up key treasury capabilities and skills by sharing examples of how leading practitioners have tackled challenges in their work and the lessons learned.

This case study was presented to and shared with AFP’s APAC Treasury Advisory Council as part of its ongoing efforts to discuss best practices and common challenges and share ideas within the region.

Insight: Centralizing and automating cash flow projections can optimize accuracy and minimize manual efforts throughout the organization.

Company Size: Large
Industry: Media
Geography: Global
Topic: Cash Management and Forecasting

Challenge: The Work or Difficulty Treasury Had to Address

The company’s challenge encompassed four critical issues. First, they faced funding and market liquidity risks stemming from a decentralized treasury structure where cash was dispersed across various countries and entities within the group. This led to limited visibility and accessibility to cash when needed and hindered effective liquidity planning.

Second, credit risk loomed due to insufficient insight into intercompany loans across decentralized treasuries, posing potential repayment difficulties and exposing the company to financial instability. “If we don't know about the cash setting in each country and the decentralized treasuries don't have the picture of the intercompany loans that they can take from another country, then how will they repay loans if they don't have cash themselves?” said the Senior Implementation Analyst.

Third, manual errors resulting from isolated decision-making within decentralized units posed operational risks and threatened reputational damage.

Lastly, financial market risk persisted as the absence of a consolidated cash overview inhibited effective foreign exchange (FX) hedging strategies, leaving the company vulnerable to adverse impacts from market fluctuations on cash flows, profits and the balance sheet. “Since we don't have a centralized picture of our cash, we can't hedge our FX properly; we don't know how much we have of all the currencies, where they are sitting, with which account and in which country,” he said.

Prior to the implementation of CashOptix, each country produced an eight-week forecast every six months. Over the course of several days, they would each produce or update 12 spreadsheets linked together with formulas. “It took about 15 minutes just to open them because they were so data-heavy,” said the Senior Implementation Analyst. The report would be reviewed by central treasury, and then updated again in four weeks.

They also performed a significant number of tasks manually, including extracts from the ERPs, calculations to apply the A/P cycle, estimation of taxes and salaries, calculation of intercompany balances and more. They found themselves locked to a point in time; there was no scenario modeling taking place, there were errors happening as a result of eroding Excel formulas, the forecasts were inaccurate and not easy to analyze, it was not scalable with M&A, and the whole thing was a very time-consuming process.

Approach: How Treasury Addressed the Challenge

The company made significant strides in optimizing its cash forecasting processes through the adoption of the CashOptix tool, resulting in both immediate solutions and longer-term opportunities. In terms of opportunities, they gained enhanced global cash visibility by consolidating short- and medium-term cash flow forecasting onto a single automated platform, facilitating comprehensive oversight across local markets.

It also enabled the integration of FX considerations into cash forecasts, empowering the team to forecast future cash requirements with greater accuracy and efficiency. Moreover, they were able to facilitate more precise forecast reporting, enabling the identification of process improvements and behaviors that could enhance liquidity management.

The tool's working capital analytics capabilities further allowed treasury to gain insights into the impact of payment and collection behaviors on working capital dynamics. And the automation not only improved scalability but also reduced errors and eliminated the inefficiencies associated with manual Excel-based reporting processes, ultimately enhancing overall forecasting capabilities and operational efficiency.

In terms of immediate benefits, the treasury team experienced a marked improvement in the accuracy of cash flow forecasts, facilitated by on-system variance analysis capabilities that enhanced visibility and precision in financial projections. It also streamlined forecasting processes by enabling automatic synchronization with the company's ERP system and fostering live collaboration among team members, boosting overall process efficiency.

Furthermore, the tool's integrated working capital dashboard provided deeper insights into working capital dynamics, empowering the treasury team to make informed decisions regarding payment and collection strategies. In addition to operational enhancements, the company realized substantial cost savings, including reduced bank fees, diminished reliance on overdrafts and minimized FX exposures.

The two biggest factors that drove the company to CashOptix were its ability to offer them global cash visibility and automation. “We don’t want people wasting their time creating Excel reports and downloading data,” said the Senior Implementation Analyst. “We have a lot of ERP systems and billing systems in each country, which meant the data wasn’t consolidated into one particular system. We wanted that collaboration and consolidation.”

Implementing the solution also required a high-level design where they linked D365/BizTalk, which is an ERP that holds daily datafiles on customers, customer invoices (open/closed), vendors and vendor invoices (open/closed) to CashOptix and uploaded the datafiles on a daily basis. There was also a part two to this design where all of the entities that are not part of D365 are integrated, e.g., Paprika in Spain. This included accounting and sales platforms from non-D365 entities; all of this data was downloaded through SnapLogic.

Outcome: What Came of Treasury's Efforts and What Was Learned

Following the implementation of CashOptix, a streamlined forecasting process was established. Each country that has been processed (Spain and Singapore thus far) now submits a 12-week forecast on a monthly basis, complemented by automatically generated daily forecasts. This is facilitated by an automated daily synchronization of actuals, invoices and purchase orders sourced from Coupa, ERPs and media systems. Mid-month, the brand finance teams review the numbers and make minimal adjustments directly on screen to dates and figures as needed, ensuring accuracy and timeliness in financial forecasting and reporting.

Additionally, a comprehensive suite of tasks was automated within the treasury system, including daily forecasting based on integrated systems, automated rules for accounts payable cycles, and automatic determination of customer payment dates using D365 or historical data. The system also computed purchase order cash flows, taxes, salaries and intercompany balances automatically, reducing manual effort and potential errors. Additionally, it facilitated the automatic upload of starting balances and actuals, while offering automated variance analysis at any level and at any time for enhanced financial insights and forecasting accuracy.

The team also now enjoys dynamic and daily forecasting, ensuring up-to-date cash flow insights. Scenario modeling for receivables financing, intercompany transactions and BMG movements, enhancing strategic decision-making. Accuracy analysis supports the continuous improvement of rules and assumptions, and CashOptix has proved to be easily scalable, accommodating changes from mergers and acquisitions seamlessly.

The treasury team has gone through the entire process with two countries so far, Spain and Singapore, and they are in talks with Canada, China, India and others to bring them on board in 2024. Based on their implementation efforts thus far, the team learned some key lessons, including:

  1. Utilize the approved architecture. In the case of this global company, this meant using BizTalk to move D365 master data and A/R and A/P invoice data to TIS and SnapLogic to move all other source system master invoice data to TIS. They also learned that unattended UiPath bots can extract and transform system reports to avoid vendor development costs (e.g., MediaOcean) and that Excel provided a simple, manual way to upload new acquisitions or brands.
  2. Define market prerequisites. Avoid markets where ERP/media system migration is underway and evaluate the quality of ERP data in relation to local billing issues (e.g., billing backlogs and dunning letters).
  3. Understand market data. The market finance team must sign off on market statement data categorization. Be sure to scope market systems and analyze sample system data to determine data transformation efforts and design. An iterative approach to forecasting allows for an agile approach to deployment. And the forecast rule automation must be agreed upon, checked and signed off on, including how to convert purchase orders into cashflows and the cutoff time for uncleared invoices to be carried forward.
  4. Outline the monthly forecast process. Understand the current incumbent market forecast process, and review the local purchase order and billing processes to understand how system data is updated. Finally, the manual adjustment of invoice dates and amounts on TIS must be minimal — prioritize forecast rule automation.

Copyright © 2024 Association for Financial Professionals, Inc.
All rights reserved.