Editor’s note: NeuGroup’s online communities provide members a forum to pose questions and give answers. Talking Shop shares valuable insights from these exchanges, anonymously. Send us your responses: [email protected].
Context: Cash forecasting is a perennial challenge for both treasury and FP&A teams, but the two functions tend to approach the problem from different perspectives. Treasury, responsible for short-term liquidity and cash management, depends more on bottom-up, direct forecasts. FP&A, concerned with planning and budgeting for the longer term, typically uses a top-down, indirect method. But treasury at some companies employs both approaches.
- “It’s important for us to keep both,” said one member who participated in NeuGroup’s Cash Forecasting Survey last year. “At the end of the quarter, we run both models, using the indirect forecast from the income statement and direct forecast from the cash flow. This is great because we can reconcile the models.”
- The problem for many treasuries is access to data, often a manual process. While a TMS automates some of the work, participants in post-survey focus groups had not yet found a system offering comprehensive cash forecasting functionality. That’s why 93% of treasuries are still using Excel, often in combination with the TMS. Only 53% said they are using their TMS for cash forecasting. Just 18% are using ML/AI tools.
Member question: “I am trying to get to a nice 13-week cash forecast and currently have a messy one that is taking me far too much time. I’d like to know how others handle short-term forecasting.”
Peer answer: “Our process is at a relatively early stage and not perfect; but that may make it more applicable if you’re starting from scratch. It’s not a rolling forecast; we only forecast the current quarter in our direct cash flow forecast. We have a longer, indirect cash flow forecast which is rolling, but that is mostly driven by FP&A inputs.
“Our one quarter direct cash flow forecast consists of the items below. Periodically, we update it using quarter-to-date actuals from our TMS system (previously we used bank portals).
- “Collections. Based on AR and forecasted billings.
- “Employee spend (salary and other). Forecasted by our payroll team but mostly driven by historical data and growth.
- “Vendor and T&E spend. Forecasted by treasury using historical run rate and FP&A expense growth assumptions.
- “Corporate taxes. Forecasted by the tax team.
- “Interest income. Forecasted by treasury.
- “Capex. Forecasted by FP&A
- “Debt/equity cash flows. Forecasted by treasury.”
NeuGroup Insights reached out to the member who posed the question to find out what he learned from a follow-up conversation with the peer who provided the written answer. The questioner began by describing his forecasting journey so far:
- “Previously we just had a budget that was done quarterly. I began doing a monthly cash forecast, as I oversee all cash items for AP release and collecting AR and oversight on employee expenses; I was able to get a more accurate month-end cash number.
- “However, it was a messy Excel sheet where I was continuously moving actuals around; it was very inefficient and I was not displaying my forecast, actuals and variance numbers well.”
Here are the member’s key learnings from the conversation with his peer.
- Clean presentation. “Using hypothetical figures, he showed me how they use their cash flow to show forecast, actual and variance sections on a weekly basis. This was probably a light bulb moment for me. Looking online for templates, I did not like what I saw; what he showed me was clean and straightforward.
- “It was an Excel workbook. It had several tabs of reports and data that were pulled from Kyriba. It had a summary tab, cash waterfall, forecast, actual and variance sheet and several others. I liked seeing a bit of a summary tab showing who is responsible for getting numbers.
- Clean data. “Knowing the source of your data is critical. The quote we have all heard is ‘garbage in, garbage out.’ I was pondering if I could use our accounting system for some tools and data but knew that things were not being reconciled in a timely manner. The peer member syncs bank data to Kyriba; using bank feeds to a TMS is the go-to route to get actuals in a timely basis.
- “Then, however, they export from Kyriba to an Excel workbook. As many people know, the visualization ability is not there yet.
- Clear metrics. “Know what metrics are you trying to measure, i.e., free cash flow, change in cash. I began the process for doing cash forecasting to get a more accurate number than what our budget was spitting out, but there are more metrics I can incorporate into my reporting that can give me value.
- Rules rule. “Try to set up as many rules as possible for your bank feed. I do not have a Kyriba or a TMS but believe I can roll up my Excel sleeves to make some rules work.
- “I will try to enhance the efficiency of my reporting by using formulas in Excel to allocate the transactions better to the forecasted line items. It is currently a manual task of adding and subtracting line items—that can be streamlined.”
