Cash Flow Forecasting for SMBs

Overview - The Monday Morning Blues

This is the first article in our second 4-part series on how to leverage AI to build a 13 week cash flow forecast for small and medium businesses.

Even before you have had your morning coffee.

There is a meeting on Thursday and The CFO report needs to be done by Wednesday. As a starting point you have an AR aging report that looks a little different from the week before because someone in operations added a column into the data.

You have payroll from HR that has the product/engineering’s CapEx tracker that is a tab in a sheet that multiple people have access to. And the AP aging has ten new vendors because the ops team signed two new field service contracts last month and the first invoices just landed.

This is just the start of the start of the week.

Secret of forecasting

Every CFO knows that the hardest part of forecasting isn't the math, it's the history.

The math is easy - net cash in, net cash out, beginning balance, ending balance. Analysts have been doing this in excel for years. The hard part is:

• Chasing down the payroll data
• Figuring out why this week's AR aging excel file has three new columns
• Messaging the ops lead to ask which CapEx projects are actually spending money this week
• Cross-referencing the AP aging against a vendor list that just got updated
• Manually mapping 140 balance sheet line items to the right rows in your forecast template

This mapping may not be needed every week, but when it happens it stops you from doing the strategic work that was planned for this week to fix and deliver that forecast report to the board on time. You are now playing the role of a data wrangler and mapper.

This is not a technology problem, it is siloed function problem

We tend to frame cash flow forecasting as a finance problem. Better templates, smarter formulas, fancier software. But when you peel the onion, it is more about how we operate as teams.

Finance & HR: Payroll is the single largest weekly cash outflow for most of the companies we work with. It lives in a system that HR owns and Finance generally does not have access to. Every week is a handoff. Every handoff is a potential delay, a new column added to track a new initiative.

Finance & Operations: CapEx spend is the other big spend; especially in manufacturing, distribution or construction company's 13-week model. It's also the number that operations teams think about in terms of projects and milestones, not in terms of weekly cash timing. This translation falls to the finance team and generally lands straight at CFO’s feet.

Finance & Partner Teams: Vendor relationships are driven by partner teams.When a new vendor shows up, when an aging bucket shifts, finance finds out when the next export looks different and something doesn't reconcile.

The weekly cash flow forecast is, in practice, a coordination exercise that spans at least 3 if not 4 departments and Finance is the one that needs to reconcile the numbers.

​
​If we had the ability to rethink it, how would we do it

The goal was not a dashboard or a fancier Excel template. A conversational assistant that reads the files, figures out the mappings, handles the translation work between how each department thinks about money and how a cash flow model needs to see it, and populates the forecast.

It really boils down to:

1. The Mapper: It reads every input file or data source and figures out how each line maps to the cash flow model template that your company uses, this is done once and it remembers it forever. When the data source changes may be once every six months, we run this step again. The human approves this step before running the Data Entry.
2. The Data Operator: runs every Monday, reads the fresh files, applies the mappings, and populates the 13-week model with calculated values for the actuals for current week and budgets for next 13 week
3. The Forecaster: It runs the populated model every week after the Data Operator has done its magic. This runs the math, generates Excel, writes the CFO narrative, and produces the insights.

In the next article we will delve into the making of “The Mapper.” Until then ponder over this most important thing we need to solve for when building these:

Are you looking to simplify your Finance and Operations process, we are here to help - email us at Team@excelinsight.io