profitable jobs can still run you out of money

Why a profitable business runs out of money

Think about a single job. In month one you pay your crew. In month two you pay for materials you ordered in month one. In month three the subcontractor's invoice falls due.

Meanwhile you invoice the customer at the end of month one. They pay in month two, or month three. And they keep 10% back as retainage, which you might see six months after the job finishes.

So the money goes out first and comes back later. Always. On one job that is a manageable dip. Now start a second job before the first finishes, and a third, and a fourth — which is what growing looks like — and the dips stack on top of each other.

This is why contractors fail in good years. Every job is profitable and the business still cannot make payroll, because profit is a fact about the whole job and payroll is a fact about Friday.

The sample scenario, and where it nearly breaks

Eight staggered jobs, $27.56M of contracts, $3.57M of gross profit. Every job profitable. Here is what the cash position does anyway.

Eleven percent headroom on a facility, in a scenario where nothing has gone wrong yet. One job slipping a month, or one owner paying late, and the line is exhausted. That is the number you want to know before you sign the fifth job, and it is why the Stress Test tab exists.

The timing details that decide the answer

A forecast that averages everything into one lag gives a smooth, comfortable and useless curve. The trough forms in the details.

Costs are paid on three different timings. Labour in the month it happens — payroll does not wait. Materials a month later. Subcontractors two months later. Average those into one number and you flatten out exactly the part that hurts.

Revenue follows an S-curve, not a straight line. Jobs start slowly, run hard through the middle and taper. A straight line understates the middle of a job, which is precisely where the cash trough forms.

Retainage comes back as a lump, months later. Not as part of ordinary collections. Treating it as a normal receivable makes the forecast look far healthier than it is.

How to forecast construction cash flow

progress(t) = t^n / (t^n + (1-t)^n)

What is inside the file

Eight tabs across a 30-month horizon. The sample is an $11M-a-year general contractor with eight staggered jobs, sized so the credit line is genuinely tight.

Opening it in Excel, Google Sheets or Numbers

It is one .xlsx file. There are no macros, no add-ins and nothing to install, which is what makes it portable — a macro-driven template would be Excel-only.

Every formula in this workbook uses ordinary functions — SUM, IF, INDEX, MATCH and their relatives. Nothing here is Excel-only.

The arithmetic, written out

The cash flow itself is a roll-forward, and the identities are what make it trustworthy.

progress(t)  = t^n / (t^n + (1-t)^n)          # the S-curve
closing(m)   = opening(m) + net(m) + draw(m)
opening(m+1) = closing(m)
draw(m)      = enough to hold the cash floor, capped at the facility
peak need    = MAX(line of credit balance)

One subtlety worth naming: peak funding need is the peak drawn balance, not the lowest cash balance. They are different numbers, because the model keeps cash at the floor and borrows the difference — which is what an operator actually does.

How I know the numbers are right

The whole forecast is reimplemented in Python and diffed against the recalculated workbook, and then the identities are asserted — a forecast that loses or invents money is worse than no forecast. Last run: 0 numeric mismatches, 0 property failures, 0 formula errors across all 30 months.

• Closing equals opening plus net movement plus draws, every month
• Next month's opening equals this month's closing, every month — no gaps in the chain
• Collections plus retainage released equals total billed, to $0.00
• The credit line balance never goes negative and never exceeds the facility
• Draws happen only when the month would otherwise close below the floor — no spurious borrowing
• All 8 jobs complete inside the horizon
• Peak funding need equals the maximum drawn balance: $2,218,817 in month 19

Worth noting how the workbook differs from the Python prototype: the prototype said $2,032,409. The workbook says $2,218,817 because it charges interest on the drawn balance and the prototype did not. The workbook is the more complete model.

Compared with the alternatives

Questions people ask before buying

Related spreadsheets