I'm trying to get a better handle on our runway and downloaded a popular excel cash flow forecast template. It looks clean, but I'm struggling to adapt it to our weird, project-based revenue where invoices get paid months after the work is done. My spreadsheet is becoming a mess of manual date adjustments. Is there a smarter way to model this kind of lag, or do I just need a different starting point?
Here's a pragmatic approach that keeps your existing template intact. Treat revenue from each project as separate lines with fields like project name, milestone or completion date, invoiced date, payment terms, and amount. Then model a lag by moving the revenue from the completion month to the month you expect payment based on terms and history. For example, if a milestone finishes in March but customers pay in 30 days, the cash inflow shows up in April. Do you have a standard term you’d anchor to?
Rather than fighting the template, try a two-layer forecast: revenue by month from work completed, and cash receipts by month from invoices paid. If you see gaps between when work is done and when cash lands, that’s a payment delay; you can incorporate a 'days to payment' parameter and test how it shifts the cash flow.
One simple trick is to map each milestone to an expected payment month with a small lookup table. Keep a tiny 'invoices' sheet: milestone, amount, expected month, actual payment month. Then use SUMIFS to aggregate by month for your forecast.
I’d pull a quick history-based assumption: calculate the average days from completion to payment on your past projects and apply that as the default delay. Then run a couple scenarios with longer or shorter delays to see how your runway shifts.
Consider adjusting your terms or project milestones to bring invoicing earlier, like milestone partial invoicing or progress billings. A cleaner ladder often reduces the guesswork in the forecast.
If you’re using Excel, you could also try a separate cash-flow sheet that links to the project sheet; use a VLOOKUP or XLOOKUP to pull the payment month and then sum by month.
If you want, share a screenshot or a sample layout of your sheet and the months you’re forecasting, and we’ll sketch a minimal model that fits your revenue reality without tearing apart the template.