What it's for
This cash flow projection template lays one job out month by month: the work you plan to do, what it bills, how much is held back as retainage, when the payment arrives and what you have spent by then. The result is a cash position for each month and the lowest point it reaches. Fill it in before a job starts to see the month it sits furthest below zero.
It is arithmetic on the figures you type, and nothing in it predicts when an owner will pay. Your planned percent complete also builds a running total in WORK TO DATE, the numbers behind a construction S-curve. The file has no chart, so plot that column yourself if you want the curve.
The retainage calculator works out retainage on a single bill, and our guide to retainage in construction explains how it is held and released. Take the monthly percentages from your construction schedule and bill them on a pay application. Once a job is under way, the WIP schedule compares billing with work done.
What's inside
Two sheets, both protected with no password. Pale yellow cells are yours to fill in; all other cells are locked.
Cash flow is an internal sheet, so it carries our logo with no spot for yours. Above the table are PROJECT, CONTRACT SUM, ESTIMATED COST, RETAINAGE HELD (%), MONTHS FROM BILLING TO PAYMENT and RETAINAGE RELEASED IN MONTH. Under MONTH BY MONTH come 18 rows, numbered 1 to 18, with WORK THIS MONTH, WORK TO DATE, BILLED, RETAINAGE HELD, NET BILLED, CASH IN, COSTS PAID, NET CASH and CASH POSITION. In the table, only WORK THIS MONTH is yours. Then come a Totals row, a "Work entered" line, and a LOWEST POINT block with the Lowest cash position and the Month of the lowest position. The month table, the totals and the lowest point show whole dollars so a contract of nearly ten million dollars fits, while the formulas keep the cents; the contract sum and estimated cost show the cents you type. It prints portrait on one page.
About this template holds three steps, the example figures, the method, the version, the license and links.
How to fill it in
- Type PROJECT, CONTRACT SUM and ESTIMATED COST. The two money cells take 0 or more.
- Type RETAINAGE HELD (%) with the percent sign, such as 10%. The cell takes 0% to 100%. The file does not decide what your contract or state allows: see retainage laws by state.
- Set MONTHS FROM BILLING TO PAYMENT, a whole number from 0 to 3. A 1 means a bill is paid a month after billing, as the example note says; a 0 means it is paid in the month billed.
- Set RETAINAGE RELEASED IN MONTH, a whole month from 1 to 18. Leave it blank and no retainage is paid back.
- Type your planned percent complete for each month in WORK THIS MONTH, with the percent sign. Each cell takes 0% to 100%, and a blank month counts as 0.
- Check the "Work entered" line. Nothing else flags a plan that adds up to something other than 100%.
- Read the highlighted CASH POSITION column, then the LOWEST POINT block. If two months tie, the earlier one shows.
BILLED is the contract sum times that month's work percent. RETAINAGE HELD is the bill times the retainage percent, and NET BILLED is the rest. COSTS PAID is the estimated cost times the same work percent, in the same month. CASH IN is the net billed from the number of months earlier you set, plus, in the release month only, the retainage held across all 18 months. CASH POSITION is the running total of cash in less costs paid.
Two things to watch. A release month before your last month of work pays back retainage not yet held. And a bill whose payment falls after month 18 never shows as cash in, because the table stops there.
The worked example
The file opens on a $480,000.00 contract with an estimated cost of $408,000.00, 10% retainage, one month from billing to payment and retainage released in month 10. Work is 5, 10, 15, 20, 20, 15, 10 and 5 percent in months 1 to 8. The About tab says to replace these figures with your own. The table reads as the sheet shows it, in whole dollars.
| Month | Billed | Cash in | Costs paid | Cash position |
|---|---|---|---|---|
| 1 | $24,000 | $0 | $20,400 | -$20,400 |
| 2 | $48,000 | $21,600 | $40,800 | -$39,600 |
| 3 | $72,000 | $43,200 | $61,200 | -$57,600 |
| 4 | $96,000 | $64,800 | $81,600 | -$74,400 |
| 5 | $96,000 | $86,400 | $81,600 | -$69,600 |
| 6 | $72,000 | $86,400 | $61,200 | -$44,400 |
| 7 | $48,000 | $64,800 | $40,800 | -$20,400 |
| 8 | $24,000 | $43,200 | $20,400 | $2,400 |
| 9 | $0 | $21,600 | $0 | $24,000 |
| 10 | $0 | $48,000 | $0 | $72,000 |
Month 1 bills $24,000 and holds $2,400, so $21,600 arrives in month 2. Month 10 brings $48,000, the retainage held over months 1 to 8. Months 11 to 18 add nothing and stay at $72,000. The totals row reads $480,000 billed, $48,000 retainage held, $432,000 net billed, $480,000 cash in, $408,000 costs paid and $72,000 net cash. The lowest cash position is -$74,400, in month 4.
Frequently asked questions
Does it calculate anything?
Yes. Every column after WORK THIS MONTH, the totals row and the lowest point are live formulas. The inputs above the table and the work percents are yours to type.
Does it work in Google Sheets or Numbers?
Both open .xlsx files. This one has no macros, and its formulas use only IF, ROUND, SUM, INDEX, MATCH and MIN.
Does it draw an S-curve?
No. The file has no chart. The WORK TO DATE column holds the running total of your planned percent complete, which you can plot in a chart of your own.
Can I add months?
No. The sheet has 18 month rows, and its formulas and the release-month cell are written for 18.
Can I put my logo on it?
It carries our logo, with no spot for yours. With the protection off (there's no password), you can swap in your own.
Is it accounting or financial advice?
No. The file is arithmetic on the figures you type. It makes no loan, tax, accounting, lender or surety claim and does not check your figures against your books. It does not check your percent complete, payment timing or retainage rate against a contract or a law.