Treasury Nightmares: Cash Forecasting with Spreadsheets

Treasury reporting can be a torture. Especially, when done in spreadsheets. For example, the CFO needs a cash forecast for the next year to understand how to fund an investment in Brazil. He asks his treasurer to put together a report until their next monthly meeting.

The treasurer has never done a global forecast and over the next 30 days, the treasurer and his cash manager are working night and day. Here's how they put together the cash forecast.

1.       Collect data

It is a massive effort to pull together the right data from all the different systems and sources. They need to collect bank balances different banking portals. Extract purchase orders and sales contracts from the ERP system. Dig through thousands of emails to find forecasts from subsidiaries and other departments.

2.       Set up master spreadsheet

They put together their cash forecast for the next 12 months in a huge spread sheet. After arguing a while, they agree on sales growth assumptions and exchange rates for converting EUR into USD and BRL into USD. The cash manager is quite tech-savvy and automates the FX calculations as well as simulations for missing data. Time goes by quickly.

3.       Fill data gaps

Two days before the deadline, the treasurer finds out that data from the sales team is still missing. As the deadline is approaching, a second wave of emails is sent out to follow up on pending numbers. Emails arrive from the subsidiaries and all of a sudden, the finance team has five versions of the spreadsheet and needs to find out which data has been added by whom.

4.       Create report

Finally, all financial data is copied and pasted thoroughly into the master spreadsheet. All calculations were run. Numbers are crosschecked for plausibility. Although the spreadsheet freezes every now and then, the treasurer finally holds a nicely formatted report in his hands. Luckily, as the deadline has already arrived.

In the next monthly catch up, the treasurer proudly presents the report to the CFO. After looking at the report for a few minutes, the CFO says, "Great job, the cash forecast gives me a great overview of forecasted liquidity." A smile flashes across the treasurer´s face. All the effort has paid off.

As the CFO continues, "Can you get me a weekly forecast for the first quarter? And what is the impact on liquidity if our receivables extend 45 days?", and 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?"

When the treasurer is back in his office, he calls his cash manager and asks him to start looking for a treasury system to automate data collection and report creation. There is no way the CFO´s questions can be answered in time and without errors, using spreadsheets.

Automate cash forecasting with cloud technology

Cash flow forecasting doesn’t need to be a nightmare. Best practice treasury systems allow treasury teams to gain control over all aspects of their financial planning through the insight, aggregation and analysis of all company cash flows. Graphical dashboards and data cubes are available for clear and concise data views that easily allow informed decision making.

With cloud technology, real time reporting is no longer a luxury only available to the biggest organisations. Today, cost-effective cloud treasury systems allow corporates to achieve best practice reporting within budget.

Read Reval´s eBook "How to Build an Accurate Cash Forecast" to learn how to automate liquidity planning in your company.