Posts

Treasury Analyst @ HMSHost International

Schiphol-Rijk – Full-time Read more

Treasury Manager @ EVBox

Amsterdam – Full-time Read more

7 steps on how to make Cash Flow forecast a success

| 15-02-2021 | Bas Kolenburg

Last year was a good example to remind organizations that cash flow forecasting is important, although, very little were prepared for the unprecedented, sharp and abrupt changes in turnover and cash flow due to the Covid-19 pandemic.

CFO’s have been asking:

  • Where is the cash?
  • Are we prepared for all the contingencies?
  • Do we know how our cash flow will hold up for the rest of the year?
  • Will we meet the covenants set in our credit facilities?

In many treasuries, cash flow forecasting is a well-established basic core process, but from my experience it is often a “struggle” where the results do not always outweigh the efforts. Why is this process so difficult and more importantly: how can you make the cash flow forecast process a success?

Here are 7 steps that will help your organization:

1. Set your purpose and the horizon

Allow yourself to describe what the purpose of the cash flow forecast is as this will define also the horizon and the data that you need to build your forecast. The purpose will also be the guiding framework what level of tolerances you are prepared to accept.
Setting up a cash flow forecasting for quarterly reporting of covenants or to prepare for short term liquidity shortfalls means a different horizon and sometimes also a different set of data. Horizons can vary as much from the ‘standard’ 13-weeks to monthly or quarterly to even years. With a longer horizon, the level of accuracy will diminish.

2. Identify the cash flow drivers

This is the most essential and valuable step in the process as the right identification will largely determine the success of your forecasting.

    1. Where and when do we receive cash inflows and what will be our expected cash outflows?”
    2. From what sources can we derive the data, how predictable are they, in what currencies?
    3. And in which entities or what bank accounts will these cash flows occur?

Prepare a list of all (forecasted) cash in- and outflows and label them with priority, currency, predictability and identify in what entity and from what source you will be able to find actual and forecasted data.

3. Collect systematic and consistent data from all cash flow drivers

As you have, in the previous step, identified what will drive your cash flow, then we reach the really difficult part and that is obtaining reliable data on actuals and forecasts on these drivers.
You often hear : “I do not know when our clients will pay our invoices” and “If we win the tender then contract turnover will be X, however timing of the tender and outcome is unsure” and “Forecasted volumes of our product, I can give you but prices will be determined at the sale on spot basis”.
Don’t confuse sales and profit with cash. Most organizations seem very well equipped and organized to close each accounting period their books and forecast somehow the main profit and loss items going forward, however translating that into cash items, in the right currency with the right timing is not always easy.

My experience is that the process of obtaining these data gives you great insights on how cash driven the company really is and what role cash is playing in the KPI and rewards throughout the organization. You will often find that cash is, except for the treasury responsible, not on top of each minds.
Find also the right balance in detail of the data you want to forecast, as you can define a lot of cash flow categories, but that also means that you will need to label your actuals for all these categories. Manual labelling is often undoable (unless you have unlimited resources) and automating this labelling with tools is often easier said than done.

4. Focus on cash balance visibility

Your starting point for your cash flow forecast is the cash balance you have today and without adequate cash balance visibility on your today’s cash balance you will not be able to project future cash balances. Cash visibility means that you have access to – real time- information of all cash balances in your organization. When you have 1 or 2 banks, the Electronic Banking tools of these 1 or 2 banks will provide you all the information that you need. However, often certain bank accounts are managed on a decentralized level and information on these accounts are provided only at the close of the reporting period. Multi-banking tools that function as an information overlay can help you to overcome these kind of situations but you can also set up you own cash balance reporting consolidation.

5. Include analysis for variances

Analyzing the actuals versus your forecasts gives you a better insight how well the predictions have been and which data were reliable in the previous forecasting period and which were not. The sources that provided these data need to receive feedback on the variances from you to understand what was causing this difference so that their data can be improved going forward. Otherwise, it is only your problem. Sometimes a sort of “carrot and stick” feedback can be used to strengthen the reliability of the data collecting and create co-ownership for the process.

6. Prepare for scenarios

For treasurers, being prepared for the unknown is part of their DNA. So setting up scenario’s next to a base case in the cash flow forecast is essential to understand the headroom and even more important, what are the main drivers affecting the headroom. Because one thing is certain: Covid-19 will not be the last crisis they we will face.

7. Let systems work for you

There is no one-size-fits-all solution. Each process and tool must be tailored to the needs and objectives of each specific business. Many organizations work with Excel sheets because of the flexibility, it’s easy to use, the low costs and because it can manage massive amounts of data. Basically there is no problem with that, except when you would like to follow the steps, I described above, in more complex and multi-currency environment, then Excel will fall short to “let systems work for you”.
Nowadays there are multiple solutions (in various price ranges) for tools that can support your cash flow forecasting process from dedicated cash flow forecasting tools to more generic treasury systems and also payment hubs and banks provide (parts of) the solutions to support the cash flow forecasting process. Sometimes the tools include also artificial intelligence features that use actual company data to determine and support the forecasts. But often the tool is just a blank template sheet that needs to be filled with the actual and forecasted data. Then the added value is limited as “garbage in” means often also “garbage out” .

Conclusion

My advice is to revisit the cash flow forecast process in your own organization with the above mentioned 7 steps. If not ideal, there might be a strong business case to change (parts) of the process to be better prepared for the future.

 

 

Bas Kolenburg

View my profile

 

 

 

CASH FORECASTING SURVEY RESULTS 2019 TOO MUCH PROCESSING, NOT ENOUGH FORECASTING? – 5 KEY INSIGHTS

| 26-03-2019 | treasuryXL | Cashforce | Nicolas Christiaen

Cash is often labeled as the lifeblood of an organization as it enables a company to function. However, when it comes to forecasting cash, most companies face various challenges. Afterall, it starts with the uncertainty of the future, but more factors are at play. Decisions about risk vs reward, the essence of business, can be difficult. To learn more about the complexity of the topic from first-hand data, we initiated the first Cash Forecasting Survey together with the Financial Executives Consulting Group. This survey was focused on senior financial and treasury executives. The 225 respondents are active across a variety of industries (manufacturing, energy, retail, telecommunications, health care, …) that range from smaller businesses (35% under $50 million revenue) to big corporations (28% over $1 billion revenue).

This survey aims to dig a little bit deeper into the challenges that companies are currently facing, the underlying causes of these difficulties, as well as possible future trends and technologies that could ease these challenges. The results of the survey will be discussed in-depth during a webinar on the 27st of March, 2019 (11am EST / 4pm CEST). To lift the veil slightly we’ve distilled our top 5 key insights below.

1. CASH FORECASTING REMAINS THE TOP ISSUE

A company’s ability to forecast the future with any degree of certainty is determined by external factors (i.e. competitor’s actions, market conditions such as interest rates and local tax rates) or internal factors (availability of funds, staff available, technology in use, access to data etc.). Regardless of whether one is a pessimist or an optimist there should be very little to argue about the fact that cash forecasting is one of the central pillars of treasury, and therefore has been on the radar for improvement for many companies for a decade. The graph below hints at the current focus: Given the opportunity to rank the most important issues over the next year, “cash forecasting” comes out on top (34%). Furthermore, we see in the answers that there is a big focus on the outcome as such, as well as on improving the current processes that are at play (such as management reporting, upgrading financial systems).

WHAT IS THE MOST IMPORTANT ISSUE FOR YOUR COMPANY OVER THE NEXT YEAR?

2. TIME IS MONEY

For the past decades, Excel has been integral to businesses everywhere. It’s embedded in countless processes throughout the company, including within finance and treasury departments. While spreadsheets remain popular as a tool to accomplish many tasks, even their users admit that they are error prone. Moreover, maintaining data integrity within spreadsheets can be a cumbersome and manual task since this type of technology was never meant to be an integrated solution to business issues (i.e. data from one spreadsheet almost never flows seamlessly into another spreadsheet).

Nonetheless, as the graphic below lays out, more than 9 out of 10 respondents continue to use spreadsheets for collecting/creating cash forecasts & making comparisons to actuals. 69% of respondents however find the process too time consuming. On top of that, the graph clearly shows that this is the case in all surveyed treasury/finance areas. Overall, we can state there is still a great over reliance on spreadsheets, from cash forecast generation to debt management. Combined, these insights indicate a dire need for a better solution in the area of cash forecasting and treasury management.

DO YOU USE EXCEL SPREADSHEETS FOR THE FOLLOWING TASKS?

WHAT ARE THE LIMITATIONS OF USING SPREADSHEETS TO PERFORM?

3. PROCESSING AGAINST THE CLOCK

The following graph further illustrates how frustratingly long any spreadsheet driven forecast process can be. If we look at the distribution of respondents that spent more than 10% of their resources on creating/updating cash forecasts, we can see that 43% of respondents spend more than 2 hours a day on average on creating/updating cash forecasts. Naturally, with only limited resources available for processing, forecasts will be less than “perfect” until conditions are changed.  Investing in “better” technology has been shown to help the forecasting process, but technology alone (i.e. working faster) is not a substitute for working smarter (i.e. more strategically) as the following survey observation makes clear.

WHAT PERCENTAGE OF STAFF RESOURCES ARE SPENT ON CREATING/UPDATING CASH FORECASTS?

4. A TMS IS NOT MADE FOR CASH FORECASTING

It cannot be said with certainty what the “perfect” allocation of resources devoted to processing vs planning / analysis should be in the forecasting process. As mentioned earlier there is a continued reliance on a disparate set of 1980s technology (i.e. spreadsheets) perhaps because they are easy to use and offer a certain degree of flexibility. However, basing your forecasting on technology that offers little in the way of scenario planning, ability to sum up / drill down through an organization or track change by date, user, etc.  may constrain a company’s ability to measure its success.

In response to the weaknesses of spreadsheet driven forecasts many companies over the last decade have implemented a large number of Treasury Management Systems (TMS). Yet, the adoption of a TMS for forecasting purposes has been slow to occur. As the survey results show, more than 9 out of 10 respondents still use spreadsheets for cash forecasting, even when using a TMS. It is a surprising statistic and may indicate that a TMS maybe better at processing (e.g. cash positions, payment execution, cash accounting, etc.)  than at planning / forecasting.

WHAT FINANCIAL SYSTEMS ARE USED WHEN PERFORMING THE FOLLOWING FUNCTIONS?

5. NO PROPER REWARD STRUCTURE BASED ON CASH FLOW

As seen in previous paragraphs, the processes involved in cash forecasting are mostly done manually and with error-prone tools and processes. As a result, companies frequently struggle to foresee unexpected occurrences that impact the livelihood of their business. Bridging these gaps can pose considerable risks and can be very costly to overcome. Consequentially, one would expect companies to put rewards in place for their business units in order to draw out valuable insights when it comes to cash flow. Yet, 3 out of 4 companies don’t reward their business units based on cash flow. Even among the large companies (i.e. revenue > 500MM) only 33% of companies reward their business units based on cash flows. It prompts the question: If cash forecasting ranks as the number one issue, how come it is rewarded as a by-product rather than a focal point?

WHICH METRICS BELOW DOES YOUR COMPANY USE AND WHICH ONES ARE USED TO REWARD ITS BUSINESS UNITS?

CONCLUSION

As the survey shows, cash flow forecasting is an important tool used by businesses to peer into the future and work towards accomplishing previously set profitability, liquidity and risk goals.

Unfortunately, all forecasts will be “wrong” as the future is uncertain and forces companies to navigate through a seemingly infinite sea of scattered data that only increases over time and becomes prone to human error as it accumulates up and down the organization.

Though no forecast will be perfect, any forecast requires a certain amount of dedicated resources (e.g. staff time throughout the company, integrated sets of data and the technology to turn data into information). If too many resources are devoted to processing then there will be less time remaining, possibly reducing the quality of actual decisions made. Few companies should base their strategic decisions based mostly on time left over as it may harm, not benefit the company. Depending on the size of the company, the following trends can be seen:

  • At many large companies treasury departments have taken the lead on cash forecasting by beginning to replace their spreadsheets with more purpose-built applications such as a TMS, but even this type of system appears to have limitations, perhaps because it had its origins in processing transactions rather than comparing alternative futures and lacks real “what if” features.
  • At small to medium sized companies FP & A areas are often responsible for cash forecasting, but, as the survey shows, spreadsheets are not the best forecasting tool and ERP systems contain only historical data that is difficult to extract and project into the future.

There is no definite answer to what mix of resources should be employed to achieve success, but relying on the current mix of disparate technologies doesn’t seem to be the answer. Fortunately, advances in new technology available to users through out a company are paving the way towards a clearer future, one driven by analytics, visualization, automation and transparency of data across an entire company.

Curious about additional findings from our survey? Be sure to register here for the webinar on the 27st of March, 2019 (11am EST / 4pm CEST) where we will discuss these and other findings and learn more about the challenges and solutions of Cash Forecasting.

[button url=”https://www.treasuryxl.com/contact/” text=”Contact us” size=”small” type=”primary” icon=”” external=”1″]

[separator type=”” size=”” icon=””]

 

Zero Coupon Yields and implied Forward Yields

| 13-06-2016 | Lionel Pavey |

 

Having constructed our 20 year yield curve with both observable data and discovered data in my previous article,we are now in possession of 3 sets of data:

  1. Spot par rates
  2. Spot zero coupon rates
  3. Discounted Cash Flow factors (DCF)

The most important of these, for calculation purposes, is DCF.

Present Value

The discounted present value of a future cash flow can be calculated by dividing the future value by the DCF. It therefore follows that a series of future cash flows can also be discounted to a single net present value.

Implied Forward Rates

The original yield curve showed annual spot rates for a period of 20 years. Using DCF it is possible to construct similar curves but with forward start dates – a curve starting in 1 year, 2 years, 3 years etc. When building these curves the “implied” forward rate will actually be a zero coupon rate and not a par rate. Converting the zero rates to par rates can be achieved by using Excel Solver – a very useful tool but great care must be taken as Solver gives an answer but shows no formula.

So, how do we calculate an implied forward rate?

Let us assume that we wished to find a rate with a duration of 4 years, starting 5 years forward.
To achieve that, we need both the 5 year DCF and the 9 year DCF

The previous constructed curve yields the following values –

5Y DCF                    =               0.9464924176

9Y DCF                    =               0.8508986778

((.9464924176/.8508986778)^(1/4)-1)*100     =  2.6975% implied 4 year rate starting in 5 years

As stated, this is the implied zero coupon rate – the implied par rate is 2.6887%

All forward rates are purely implied rates – a true quoted rate would always be different for various reasons –

  1. Spread between bid and offer
  2. Yield curve constructed with specific data
  3. Sentiment of the trader
  4. Possible exposure already in the banks’ books

Here is a small snapshot of both implied rates and par rates built with the original curve.

parrates complied rates

When I discussed building the original yield curve, different ways of interpolation were tried. I would now like to show how small differences in yields in a spot curve can lead to significant differences in a forward curve.

Let us look at a duration of 5 years starting 10 years in the future and compare the linear interpolation with the smooth adjusted curve. Assume that the instrument to be priced is a linear instrument – equal repayments of principal every year.

I have built both curves using the same layout and formulae throughout with the exception of the input rates in the missing periods.

The linear rate is 3.276%; the smooth adjusted rate is 3.416% – a difference of 14 basis points or about 4% of the smooth rate. In a market where the normal bid/offer spread is about 3 or 4 basis point, this represents a significant difference/anomaly.

I regularly hear people say that when they need to purchase a financial instrument that they get at least 2 quotes – this is all very well but does not stop a treasurer from first ascertaining what the correct price should be before getting a quote. If banks know that, as a treasurer, you can not calculate the theoretical price this allows them to move the price away from the implied to a price that is more advantageous to them and their trading book! A dedicated financial data vendor system can make life easier, but it is not impossible to calculate a price without these resources!

 

Next – Spreads; their use and the hidden extra costs

First two articles on building a yield curve:
1. Yield Curves (term structure of interest rates) – filling in the blanks
2. Yield Curves (term structure of interest rates) – filling in the blanks part II

 

Lionel Pavey

 

 

Lionel Pavey

Treasurer