Treasury nightmares: cash forecasting with spreadsheets

Picture this: your CFO needs a cash forecast for next year; she wants to understand how to fund an investment in Brazil. So, she turns to you, her trusted treasurer, to put together a report ahead of the next monthly meeting.

But without a TMS, it is a quite lengthily task – here are the steps you end up taking:

1. Collect data

   It is a massive effort to pull together the correct data from different systems and sources. You’ll need to fetch bank balances from various banking portals and extract the ERP system’s purchase orders and sales contracts. You’ll dig through thousands of emails to find subsidiaries’ forecasts and other departments.

2. Set up a primary spreadsheet

   You pull together the forecast for the next 12 months in a vast spreadsheet. You’ll consider sales growth assumptions and exchange rates for converting EUR into USD and BRL into USD and use formulas for all the FX calculations. But in an excel spreadsheet, you’re unable to build simulations for missing data.

3. Fill data gaps

   What about missing data? Forecast submissions from the sales team are still missing. Waves of emails are sent to follow up on pending numbers. The sales team realizes they now have three versions of their forecast spreadsheet – but no way of knowing which data has been added by whom.

4. Create reporting

   Finally, you’ll copy all the financial data and paste it thoroughly into the primary spreadsheet. You run the calculations and crosscheck the numbers for plausibility. Your work is finally complete, albeit presented in a very slow and lagging spreadsheet.

When the next monthly catch up rolls around, you proudly present your report to the CFO.

But additional questions begin to ensue: “Can you get me a weekly forecast for the first quarter? And what is the impact on liquidity if our receivables extend 45 days?”  she continues, “How big is our BRL exposure today? How would this investment in Brazil in the third quarter impact our cash flows and counterparty credit risk? I think exchange rates will go down; how will that impact us?”

There is no way you can answer your CFO’s questions in time and without errors using spreadsheets. It is time to start looking for a treasury management system to automate data collection and report creation.

Automate cash forecasting with technology

Cash flow forecasting doesn’t need to be a nightmare. Treasury systems allow treasury teams to gain control over all aspects of their financial planning. With a treasury system, you can:

• Get insight, aggregation, and analysis of all company cash flows.
• View clear and concise data on the graphical dashboard that easily allows informed decision making.
• Get real-time cash forecasting and reporting.

Today, ION Treasury’s treasury systems allow corporates to implement cash, liquidity, treasury, and risk management best practices within budget. What’s more? ION is reimagining cash forecasting by using machine learning models. Machine learning can improve the cash forecasting process significantly by producing forecasts in seconds, free of any human biases, and driven entirely by the data.

ION’s Reval, a highly scalable, SaaS (Software as a Service) treasury and risk management solution, captures the historical data required to train our machine learning algorithms and create forecasts automatically. At any time, the forecasting parameters can be adjusted and forecasts can be rerun to reflect unforeseen market events or a change in business strategy.

More accurate cash forecasts free up working capital and lower the cost of funding ongoing operations. They help you better manage investments and fund business growth. Using a TMS helps you overcome the hurdles of spreadsheets and get yourself and your CFO the visibility into your company’s cashflow you need at any time.