Cash Forecasting:
Divergent Views on Necessary Tools & Tech

This article was originally posted in the Treasury Update Newsletter (Quarter 2, Summer 2017). You can see the full newsletter here. 

Corporate Treasury’s Forecasting Woes

Cash forecasting is a vital, core functionality for treasury operations. In fact, in a recent survey conducted by Strategic Treasurer, 79% of corporate respondents indicated that forecasting functionality was regularly used or needed as part of their ongoing operations. However, of those that were using a treasury management system (TMS), a full 14% were either dissatisfied or very dissatisfied with the forecasting functionality it had to offer. An additional 26% of firms were no longer using the forecasting module of their TMS because it was not working properly or was ineffective. As part of the same survey, 31% of practitioners stated that their organization used only Excel for managing treasury-related tasks, including  forecasting.

Forecasting Crossroads: Growing Pains

At an organization’s inception, it usually takes nothing more than a few Excel worksheets to keep track of company finances. At the start, there are a minimal number of transactions occurring and only a handful of accounts that need to be managed. However, as a company grows, the number of items that must be accounted for also grows. New banks and bank accounts are added. Growth means more transactions are being conducted across more business units and locations and with more clients. Ultimately, it is only a matter of time before most companies arrive at the same crossroads and ask the same question: “How can we update our technology infrastructure to meet the evolving needs of the organization?”

Ultimately it is only a matter of time before most companies arrive at the same crossroads and ask the same question: “How can we update our technology infrastructure to meet the evolving needs of the organization?”

For treasury, the answer to this question commonly involves leaving their primarily Excel-based processes to make way for a solution such as a TMS. However, for companies that have come to rely on Excel for virtually all their treasury operations, this is an incredibly daunting task. What’s more, the skepticism and hesitancy many treasurers have regarding the use of treasury management systems, especially for processes such as forecasting, can cause some companies to consider alternative approaches and solutions to fill their technology void.

Chick-fil-A’s Forecasting Conundrum

The dilemma posed above is a reality that the corporate treasury department at Chick-fil-A, an America quick service restaurant chain, has recently faced as they look to advance their Excel-based forecasting operations. Until recently, Steven Peterson, Senior Manager of Treasury at Chick-fil-A, was content with handling the bulk of forecasting operations through Excel. Using Excel, historical data was exported from Chick-fil-A’s ERP system and banking portals, and forecasts were created for the company as a whole based primarily on percentage of sales. This forecast was constantly refined as fresh sales data became available throughout the course of the year. Using this method, Peterson’s team was able to achieve, on average, less than 10% variability overall between forecast-to-actual for short-term and medium-term ranges.

Until recently, Steven Peterson, Senior Manager of Treasury at Chick-fil-A, was content with handling the bulk of forecasting operations through Excel.

This forecasting workflow is the one that Chick-fil-A has utilized for years. However, as Chick-fil-A grows, treasury has been confronted with the need to find a solution that can automate and streamline this increasingly complex process. With annual sales from 2,000+ storefront locations now exceeding $7 billion dollars, 10% variability on forecasts no longer makes the cut. Ideally, Peterson would like to see variability on forecast-to-actuals come within 5%. At the same time, the reporting tools available through Excel have outlived their usefulness and Peterson would like to implement a solution that allows for efficient generation of automated forecasting reports based on specific queries. In order to accomplish these objectives, Peterson realizes that Chick-fil-A needs to find a tool that can easily incorporate a wide range of data points into the forecasts to improve accuracy and also generate automated reports based on the data for informative visualization and effective analysis.

Chick-fil-A needs to find a tool that can easily incorporate a wide range of data points into the forecasts to improve accuracy and also generate automated reports based on the data for informative visualization and effective analysis.

Chick-fil-A’s TMS Skepticism

At first glance, it would seem the predicament that Peterson and Chick-fil-A face is one that could be easily solved through the implementation of a TMS with enhanced forecasting capabilities. Furthermore, Chick-fil-A represents what most treasury technology vendors would find to be an ideal potential client as they prepare to make the jump from Excel to a more sophisticated forecasting solution. However, Peterson is admittedly skeptical on the benefits that a TMS solution would provide. Having seen numerous demonstrations in the past, he remains unconvinced that today’s TMS offerings have the capabilities he seeks.

At first glance, it would seem the predicament that Peterson and Chick-fil-A face is one that could be easily solved through the implementation of a TMS with enhanced forecasting capabilities.

However Peterson is admittedly skeptical on the benefits that a TMS solution would provide.

According to Peterson, the treasury products he has seen that offer forecasting provide little more than what Chick-fil-A currently utilizes via Excel, but are far more expensive. And while Peterson acknowledges that progress along that front has been made, he is still skeptical that the added functionality is worth the investment.

An Alternative Forecasting Approach

Rather than adopt a TMS, Chick-fil-A’s treasury department is instead considering the adoption of two software solutions that are currently being utilized in other areas of the organization. These are Alteryx, a self-service data analytics platform, and Tableau, a provider of business intelligence software with a focus on visual data. While treasury is already using these solutions in other areas, they have yet to fully analyze how forecasting could be enhanced through the predictive functionality within these tools. However, Peterson asserts that further integrating treasury operations with Alteryx and Tableau to include forecasting will not only be less expensive than the adoption of a TMS, but will provide a greater level of benefit to the firm. Having already witnessed the capabilities that Alteryx and Tableau provide for other treasury-related functions, he is confident that expanding treasury’s use of the solutions to include forecasting will provide his team with the level of automation and accuracy that they are looking for.

Kyriba’s Counterargument: The Benefits of a TMS

The viewpoint held by Peterson is not an uncommon stance for corporate treasurers to take. However, there is an alternative viewpoint held by numerous others in the industry who see the upgrades and enhancements that have occurred in the TMS realm as holding significant value. As a means of highlighting both viewpoints, we allowed Kyriba, a leading provider of treasury software, to offer a counterargument on the benefits of a modern-day TMS. Greg Person, VP of Global Presales and Strategic Value at Kyriba, shared the following insights.

Intro to Cash Forecasting

Accurate cash flow forecasting continues to challenge global treasurers, which is why it’s been a topic of debate in treasury thought leadership forums for years. The complexities of cash flow forecasting are evident when one considers analyzing thousands of cash flow inputs: AR, AP, payroll, tax, legal, global subsidiaries, etc., not to mention the timing of these cash flows. In many ways, cash flow forecasting requires a special artistry and not simply a scientific approach. During my years in corporate treasury, where I managed a complex free cash flow, we referred to this artistry as the treasury touch. Thus, when we consider the right tool for our craft, what could be more appropriate than the mighty and dynamic Excel spreadsheet?

In many ways, cash flow forecasting requires a special artistry and not simply a scientific approach.

Excel: Pros & Cons

There is no dispute that Excel offers a unique level of individualized flexibility. However, this often results in complex models that may sacrifice accuracy and workflow efficiency, a particular issue when multinationals share data across email. The flexibility of Excel is often its Achilles heel, because the inputs and processes can become muddled from user to user and are highly error prone.

The flexibility of Excel is often its Achilles heel, because the inputs and processes can become muddled from user to user and are highly error prone.

Similar challenges occur when managing a complex cash flow forecasting program within Excel across global teams. As previously mentioned, a comprehensive cash flow forecast has a number of dependencies. Therefore, extracting the required forecast inputs, ensuring the related formulas, pivot tables, and vlookups are functioning accurately, and making sure the spreadsheet is understood by everyone, not just Excel gurus, is a fundamental concern. Additionally, there will be Excel inputs and submissions by various departments outside of treasury; thus, ensuring the integrity of these Excel templates and roll-up exercises are equally not to be taken lightly.
Finally, there is the topic of variance analysis and measurement of the forecast. Often, treasurers spend excessive amounts of time trying to uncover the reasons for forecast error, scouring through bank statements and accounting entries to determine timing and classification of receipts and disbursements – an exercise that is extremely challenging within a spreadsheet module. Still, despite these inefficiencies, Excel offers the unbridled ability to customize formulas and format graphs, footnotes, comments and fonts, all of which are critical when it comes to executive and board level presentation.

Often treasurers spend excessive amounts of time trying to uncover the reasons for forecast error, scouring through bank statements and accounting entries to determine timing and classification of receipts and disbursements – an exercise that is extremely challenging within a spreadsheet module.

Advantages of a TMS Over BI Solutions & Excel

So then, how does a TMS compare, and how feasible is it to expect a TMS to offer the same level of reporting flexibility and data visualization that other non-TMS solutions offer? For example, business intelligence (BI) tools are increasingly common among corporate finance departments to aggregate and transform large data sets to meaningful analytics. Still, without timely bank data and insight to forecast performance, the dynamic reporting layer won’t yield the optimal business insight.

To look at the superior value a TMS offers, it is important to note that cash flow forecasting is an iterative process; inputs are received, trends are discovered, and assumptions are made.

One certainty with cash flow forecasting is that there will be variances. Based on month-to-date actuals, or updates to cash flow timings, the initial cash flow forecast will evolve into a new version. This is where the TMS provides unique value compared to Excel or other reporting and data visualization tools, given that the bank statement activity is seamlessly integrated within the TMS.

However, one certainty with cash flow forecasting is that there will be variances. Based on month-to-date actuals, or updates to cash flow timings, the initial cash flow forecast will evolve into a new version. This is where the TMS provides unique value compared to Excel or other reporting and data visualization tools, given that the bank statement connectivity is seamlessly integrated within the TMS.

A well-deployed TMS will achieve 90+% global cash visibility on a daily basis. By having the first insight to global bank balance and transaction activity, treasurers that leverage a TMS have the distinct advantage of analyzing their company’s global cash flow performance in near real-time, ahead of the corporate accounting team or any other corporate finance organizations. Today’s TMS solutions offer intelligent and intuitive categorization rules, so cash flow reported on the bank statements can be analyzed against existing cash flow forecast line items. The TMS can also automatically ‘snapshot’ forecast versions, so treasurers can compare actuals to prior forecast assumptions, or analyze multiple forecast versions alongside one another with powerful analytics and variance commentary.

A well-deployed TMS will achieve 90+% global cash visibility on a daily basis. By having the first insight to global bank balance and transaction activity, treasurers that leverage a TMS have the distinct advantage of analyzing their company’s global cash flow performance in near real-time, ahead of the corporate accounting team or any other corporate finance organizations.

This opportunity to perform frequent and insightful variance analytics aligns with the natural and iterative process of cash flow forecasting, as it enables the treasurer to update assumptions and cash flow timings, and even commence working capital strategies, to ensure cash flow targets are met. The demand for variance analytics is increasing in importance for treasurers and CFOs who are analyzing free cash flow performance compared to initial strategy plans and budgets, or when comparing the quarter-to-date treasury direct method versus the FP&A indirect method that was communicated to Wall Street investors.

Treasury management solutions are specialized with robust databases that are designed to communicate with other technologies, seamlessly and securely. Thus, integrating cash flow forecast data from ERP systems, FP&A systems or internal data warehouses is drastically simplified, and often automated. A comprehensive TMS will also house all capital market activity, which means that investment, debt, and currency derivative flows — both principal and interest payments — are automatically included in the forecast.

Integrating cash flow forecast data from ERP systems, FP&A systems, or internal data warehouses with a TMS is drastically simplified, and often automated. A comprehensive TMS will also house all capital market activity, which means that investment, debt, and currency derivative flows – both principal and interest payments – are automatically included in the forecast.

Additionally, modern TMS solutions include forecast predicative analytics that leverage historical actuals and existing budget plans to produce intelligent, data-driven cash flow forecast versions. For global teams, a TMS introduces a new standard of discipline, scalability and workflow that provide global subsidiaries a simple tool to submit their forecast, and even include approval steps as required. A TMS also provides measurements of KPIs that allow regional and central treasury to monitor forecast accuracy and institute treasury scorecards to improve accuracy and accountability.

Despite all these efficiencies, TMS solutions have been criticized for the all-important publishing and formal presentation of the forecast, falling short when it comes to formats, graphs and executive output. It is rare but not uncommon that visual data reporting is prioritized over integrated global insights. In some cases, the treasury team is out gunned by the IT department who have reviewed and adopted new software solutions for other departments and that have some applicability as a general data analytics or reporting solution. These tools don’t solve the core work of a TMS: aggregating financial data from dozens or hundreds of bank accounts worldwide in a timely, efficient manner that does not involve logging into individual bank portals. To do this at scale, you need seamless connectivity, security, and a robust infrastructure. Any business intelligence tool can add value from a data analytics and visualization perspective, but it cannot replicate the core tasks of a TMS. Having said that, with the adoption of modern UIs and custom dashboards, treasury management solutions’ data visualization now rival even the most dynamic spreadsheets.

BI tools don’t solve the core work of a TMS: aggregating financial data from dozens or hundreds of bank accounts worldwide in a timely, efficient manner that does not involve logging into individual bank portals. To do this at scale, you
need seamless connectivity, security, and a robust infrastructure. Any business intelligence tool can add value from a data analytics and visualization perspective, but it cannot replicate the core tasks of a TMS.

Closing Thoughts

In closing, it is clear Excel and other BI tools can offer tremendous value to treasurers for a variety of analytical needs. However, when considering a scalable process to manage a global cash flow that automates a variety of inputs, drives predictive results, and provides structure to measure variances that hold participants accountable, the modern TMS has evolved to encapsulate these treasury needs in a superior, secure, and compliant framework.

Editor’s Note: Many thanks to Steven Peterson and the treasury staff at Chick-fil-A, as well as Greg Person and the staff at Kyriba, for their willingness to participate in this piece. It is this openness that allows us to generate meaningful content that covers real and current industry challenges, viewpoints, and concerns.

-Isaac Zaubi, Publications Manager, Strategic Treasurer

Isaac Zaubi

Publications Manager, Treasury Analyst
Isaac Zaubi has been with Strategic Treasurer for over 2 years as a treasury analyst before coming into his current role as Publications Manager. Isaac’s contributions center primarily around the development and management of publications, including fintech analyst reports, survey results reports, e-books, and whitepapers.