Building an Engine That Updates Itself

The customer data from Parts 1 and 2 had to become a real 13-week forecast. The first version needed every number typed in by hand, every single week. That was slow, and it caused mistakes. Here is what I built instead: change one date, and the whole forecast updates. Add one row of new data, and it appears in the correct week with no typing required.

Part 1 and Part 2 covered the customer data — country, industry, payment terms, and the revenue findings. This part covers a different problem: how to turn that data into an actual 13-week forecast, without typing every number in by hand every week.

Where this went wrong the first time

The first version of this forecast required a lot of manual work every week. I started with an accounts receivable report showing only the customer name and total outstanding balance. Then I examined the individual invoices that made up each balance. For every customer, I created a table listing each invoice amount together with its expected payment date. Finally, I manually entered each amount into the correct week of the cash flow forecast.

This process was slow and produced a dataset that was difficult to read, verify, and maintain. Every weekly update required reviewing the same information again instead of simply adding new data.

It also introduced unnecessary risk. A mistyped date, a misplaced decimal point, or one skipped invoice could make an entire week's forecast incorrect. These errors often remained unnoticed until the forecast no longer matched reality.

Here is an example of what the source data actually looked like for one customer:

Extracting four amounts and four dates from a single text field every week is exactly the kind of repetitive task that leads to mistakes. It also made maintaining the forecast unnecessarily time-consuming. Advancing the forecast by one week meant reviewing and re-entering much of the existing data instead of updating only the new information.

What I built instead

The rebuild happened in stages. Each one fixed a specific problem with the version before it.

• Attempt 1 — typing by hand
  Every payment amount and date got typed into the forecast directly, read from the source data each week. Slow, and prone to mistakes, as shown above.
• Fix 1 — one table for every payment
  Every payment — confirmed invoices, confirmed open orders — now goes into one table first. One row per expected payment: customer, amount, and date. The 13-week forecast itself never gets typed into directly. Formulas read this table and place each payment into the correct week automatically, based on its date. Add a new row to the table, and it appears in the right week with no manual placement.
• Fix 2 — one date controls the whole forecast
  One date controls the entire 13-week forecast: the date Week 1 ends. Change that one date, and every week column, every total, and every formula updates to match it, automatically. A dropdown list of valid dates is placed next to that cell, so there is no need to search for the right Sunday, and no risk of typing the date in the wrong format.
• Fix 3 — a record that does not make the forecast longer
  Every forecast that updates each week has the same problem. Keep every old week, and the file keeps growing larger. Remove old weeks to stay short, and there is no record left to check later. The fix: keep two separate things. A 13-week forecast that always shows the next 13 weeks from today. And a separate list that adds one new row every week, recording what was forecast, what actually came in, and the difference between the two numbers.

Here is a closer look at the real sheets — the same four sheets used to build this forecast, with real numbers pulled directly from the file.

How the Forecast Is Built

The forecast is built in four steps. Each sheet prepares the data for the next one. All numbers below are real, taken directly from the actual file, with cents removed for easier reading.

Why this matters to a client

The real value here is not the spreadsheet mechanics by themselves. The real value is what they make possible. A forecast that takes ten minutes to update each week actually gets kept current. A forecast that takes half a day is far more likely to be skipped. After being skipped a few times, people stop using it. By the time a real cash problem happens, nobody trusts the forecast anymore.

This part of the work is not something a business owner sees directly. But it is the difference between a forecast that keeps getting used, and a report that was accurate once and became wrong within a month.

This is Part 3 of a 5-part case study. Part 4 — forecasting the cash that is not from customers — publishes soon.