Cash Flow Forecasting

The Mapper - Step 1

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

This is The Mapper - Step 1 of the 3 of creating the Cash Flow Forecasting spreadsheet Here we deep dive into the first step of creating an AI agent that maps the inputs and outputs of your Cash Flow Forecast.

The Mapping Problem Is Harder Than It Sounds

The raw data is a patchwork of translations. Every Monday, the data arrives with field names that rarely match the cash flow model's line items. For example:

An AR aging column like “Next 30” must be manually mapped to the cash flow row "Collections from Open A/R," with timing rules based on average DSO.
A cryptic balance sheet account name, such as "22222 - Checking - Jackson - x5645" is the same as the model's "Beginning Cash Book" and a human is the only one that knows that.
Dozens of payroll columns (“Gross Wages,” “401K,” “OASDI,” etc.) must be aggregated and timed according to deposit schedules to feed one or two simple model rows like "Salaries/ Payroll” and “Bonus/Commissions.”

A seasoned CFO carries these critical translations in their head, built over months with the client. This tribal knowledge lives nowhere else, creating an effective single point of failure. When a fractional CFO walks in, they have to replicate this complex mapping process from scratch on day one.

So what does a Mapper do?

When a fractional CFO onboards a new client they have to understand the client's systems of record, including the balance sheet, AR aging, AP aging, annual budget, payroll/bonus file, and the CapEx/OpEx schedule.

This is where the Mapper does the job for the CFO, its job is to read the structure of each file, the column headers and row labels without processing the dollar amounts. Its core instruction is: "Here are all the fields from the files(Inputs). Here are all the line items in the 13-week cash flow model(Output). Map Inputs to Output."

The Mapper returns a complete, proposed mapping for all fields. It matches every source field to the correct Cash Flow line item and, crucially, provides an explanation for the match:

"Mapped '22222 - Checking - Jackson - x5645’ → 'Beginning Cash Book.' This is a checking account balance on the balance sheet and the standard seed value for opening cash position."
"Mapped 'Jackson Site Operations' → 'Non-Recurring Expenses - South.’ This appears to be specific to a location, and we need to see if this is mapped to the correct region for the company’s cash flow model."

This initial mapping must be reviewed by the finance team and approved by the CFO. This setup should take about 30 minutes of review, not hours of manual work. Once approved, the mapping configuration is saved in the database.

The mapping only needs to be re-run when there are changes to the system of record data extraction or when new columns are added for company initiatives. The general rule of thumb is to run a full review every quarter to catch any differences. Every subsequent Monday, the Data Operator and Cash Flow Forecaster utilize the saved mapping to generate the Cash Flow Report.

Breaking down silos, making people advisors

When we test this with customers, the interesting part has nothing to do with the software—it’s about people, process, and organization.

The accounting and operations teams can now view the complete mapping, a document that clearly and completely shows how their line items connect to the cash flow model. This visibility was previously impossible because data in various source files could not be shared across the organization. The Mapper provides transparency, allowing everyone to understand their impact on cash flow and, ultimately, on driving company revenue.

Furthermore, teams were able to make corrections to the mapping based on their historical knowledge, which improved the accuracy of a forecast that had been running on flawed data for years.

That's what happens when you break the silo. It’s not achieved by holding a cross-functional meeting, hiring a data analyst, or buying a new tool. It is achieved by making the implicit explicit: taking the critical mapping that lives solely in one CFO's head and putting it somewhere everyone can see, react to, and improve.

The teams didn't need access to every system of record owned by different departments; they simply had a shared artifact to examine together for the very first time.

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

What's Next

Part 3 covers The Data Operator, the Monday morning workhorse. Its job is to read the fresh data files, apply the saved mapping, handle weekly vendor classification questions, and populate the full 13-week model.