Contractor Cash Conversion Cycle Calculator in Excel: Why Profitable Jobs Run You Out of Money

A commercial interiors contractor closed last year at $2.4 million in revenue and a 15 percent gross margin. In February he turned down a $480,000 tenant improvement because his line of credit was drawn to the ceiling and payroll cleared on Friday. Every job on his board was profitable. He was still out of money. That distance between a profitable job and a funded job is exactly what a contractor cash conversion cycle calculator in Excel measures, and the number it produces is the one that decides how much work you can actually take.

Here is the timing that creates the hole. You order material on day 5 and the supplier bills net 30. Your crew works all month and payroll clears every Friday. The billing period closes on day 30, you submit the pay application on day 35, the architect certifies it on day 42, and the owner pays 30 days after that. Cash arrives on day 72. You funded 72 days of production with money you did not have, and 10 percent of what you earned sits in a retainage account you will not touch for another six months.

Why the Textbook Formula Breaks on a Construction Job

The cash conversion cycle came out of manufacturing. It reads days inventory outstanding, plus days sales outstanding, minus days payable outstanding. Drop a construction job into that formula and three things go wrong at once.

There is no inventory. What sits between spending money and sending an invoice is work in progress, and the clock on it is not driven by how fast you build. It is driven by the billing period in your contract. You could finish a floor in nine days and still wait until the twenty-fifth of the month to bill it.

Days payable outstanding is not one number. It is four, and they are wildly different. Labor is paid in about 7 days because payroll runs weekly. Material sits at 30 days from the supplier invoice. Equipment rental runs monthly at net 30. Subcontractors, on a pay-when-paid clause, are not paid until after the owner pays you, which makes their days payable longer than your days sales outstanding. Blend those into a single company average and you erase the only distinction that matters.

And retainage does not appear anywhere. It is not a receivable that ages through a 30, 60, 90 bucket. It is a separate long-dated asset that leaves your accounts receivable report looking healthy while a third of your annual profit sits in someone else's bank account.

Build the Cycle Calculator in Excel

Start with one job, not the company. Company-level averages are the reason this metric gets ignored: they produce a number nobody can act on. A per-job model produces a dollar figure you can compare to your line of credit before you sign.

Lay out the contract terms in column B.

B18 is the input most contractors get wrong. It is not the gap between month end and the pay application date. It is the gap between the average day work is performed and the day you bill it. Work spread evenly across the month averages out to the fifteenth, and if the application goes out on day 35 then your average work-to-billing lag is 20 days, not 5.

Total days from work performed to cash in hand goes in B21:

=B18+B19+B20

That returns 57 days. Now the cost mix, with a payment lag for each line in column C.

The labor lag of 7 days is weekly payroll. The material lag of 20 days is a net 30 supplier invoice on material delivered about 10 days ahead of installation. The subcontractor lag of 64 days is your 57 days to cash plus the 7 days you have to pay them after you are paid, which is the federal standard under FAR 52.232-27 and the model most private subcontracts copy.

Float days in column D is how long each dollar is out the door before the matching dollar comes back:

=$B$21-C8

Cash tied up in column E converts float days into dollars at your monthly burn rate:

=B8/$B$3*D8/30

Note the subcontractor line runs negative. On a pay-when-paid clause your subs are lending you money, which is the single most important fact in this entire model and the one nobody puts in a spreadsheet.

Now the headline metric. Weighted days payable in B23 has to be weighted by dollars, never averaged across the four types:

=SUMPRODUCT(B8:B11,C8:C11)/SUM(B8:B11)

That returns 21.5 days. The cycle itself goes in B24:

=B21-B23

Thirty-five and a half days. That is how long the average dollar of cost is out of your account before the customer's dollar replaces it.

Two Jobs, Same Margin, One Needs Nearly Three Times the Cash

Here is where the model earns its keep. Take the $480,000 job above, which is labor heavy because the contractor self-performs. Now take a second $480,000 job at the identical 15 percent margin and the identical 5 month duration, built almost entirely with subcontractors.

Same revenue, same profit, same schedule. One job has a cycle five times longer than the other. Now run both through a month by month cash position, where cash from work performed in month one arrives in month three, and subcontractors billed in month one are paid in month three.

Job A needs $134,400 of working capital at its worst point. Job B needs $48,000. That is $86,400 of difference on two jobs a banker would call identical, and it is the reason the self-perform decision is a financing decision as much as a cost decision. If you are weighing that trade, the loaded cost comparison belongs in its own model: see self-perform vs subcontract cost analysis for the margin side of the same question, and make sure the labor number carries full burden, not base wage.

One check before you trust any version of this table. Both columns have to land on $72,000 at the end, because that is the gross margin. If your cumulative position does not close on gross margin after the last retainage check clears, you have double counted something. This is the cheapest error trap in the whole build.

Retainage Is a Third of Your Cycle and It Is Not in the Formula

Everything above excludes retainage. Add it and the picture changes again. Retainage on this job is:

=B2*B4

That is $48,000, accumulated across five months and released roughly 90 days after substantial completion. Call it day 240 on a job that finished on day 150.

Spread across the contract, that $48,000 held for an extra 180 days adds 18 days to the cycle. The real number for Job A is not 35.5 days. It is 53.5 days, and the third of it that comes from retainage is the third that never shows up in a standard accounts receivable aging report.

Run the same arithmetic at company level. On $2.4 million of revenue, accounts receivable of $295,000 gives a days sales outstanding of 44.9 days, which any lender would call excellent. Add $138,000 of retainage receivable and the real figure is 65.9 days. The 21 day difference is the difference between comfortable and calling your banker on a Thursday.

The Lever That Moves the Peak Is Not the One You Negotiate

Most contractors attack this by pushing on payment terms. It is the obvious move and, on this job, it does almost nothing. Cutting owner terms from net 30 to net 21 pulls first cash from day 72 to day 63. Both are still inside month three, so the month two low point does not change at all. You negotiated hard and moved the peak by zero.

The peak is set by one thing: how long you fund production before the first dollar arrives. To move it you have to get a dollar in the door earlier, and the way to do that is to bill more often.

Switch to semi-monthly pay applications. The first period closes on day 15, the application goes out on day 20, certification lands on day 27, and the owner pays on day 57. That first receipt is:

=B2/B3/2*(1-B4)

It returns $43,200, and it arrives inside month two.

A mobilization or general conditions line item in the schedule of values, billed in the first application, is the cheapest working capital available to a contractor. It carries no interest, no personal guarantee, and no covenant. Load $40,000 of legitimate early-cost value into the first application and $36,000 net of retainage lands with the first check.

Together those two clauses cut the requirement from $134,400 to $67,200. Exactly half, from contract language that costs nothing. And $67,200 is the floor: it is one month of production cost, which you have to fund before you can bill anything at all under any billing arrangement.

When Making the Cycle Worse Makes You Money

A metric you can push on is a metric you can push the wrong way, and this one has an obvious trap. Paying suppliers later always improves the cycle. It is not always the right call.

Your material supplier offers 2/10 net 30. Taking the discount means paying on day 10 instead of day 30, which lengthens your float by 20 days and makes every number in this model worse. Price it anyway:

=B31/(1-B31)*(365/B32)

With 2 percent in B31 and 20 days in B32, that returns 37.2 percent annualized. On the $96,000 of material in Job A, the discount is worth $1,920 and the 20 days of extra borrowing on a 12 percent line costs $631. You clear $1,289 by making your cash conversion cycle worse.

The rule this produces is simple and worth writing on the wall: borrow on the line to take any discount above your line rate, and stretch every payable that carries no discount. The cycle is a diagnostic, not a target. Optimize the dollars, not the days.

Turn the Cycle Into a Bidding Rule

The output of this model is not a number of days. It is an answer to the only question that matters when the phone rings with another job: can I fund this one on top of what I am already running?

Put your available capital in B39 and the peak requirement per job in B38, and the capacity answer is:

=FLOOR(B39/B38,1)

The same $150,000 line supports $480,000 or $1,440,000 of simultaneous work depending on how the jobs are built and billed. Nothing in that table is about winning more bids. It is all cost mix and contract clauses, and it explains why contractors who chase growth through sales alone hit a wall they cannot see in their profit and loss statement.

Two rules fall out of it. First, when the bid schedule gets crowded, favor the sub-heavy job even at a point less margin, because it consumes a fraction of the capital. Second, if a job is going to eat more than about 60 percent of your available capital at its peak, either fix the billing terms before signing or do not sign. The pay application schedule is negotiable at bid time and unnegotiable the day after.

Run the Number Before You Sign, Not After

The cash conversion cycle is worth calculating once for the diagnosis and then never again on its own. What you keep is the peak working capital figure per job, because that is the number you compare to the line of credit before you commit a crew. Get it in front of the decision instead of behind it and the questions change. You stop asking whether a job is profitable, which it almost always is, and start asking whether you can afford to be right about it for 72 days.

Three inputs drive everything: the cost mix, the days from work performed to first cash, and the retainage percentage. Everything else is arithmetic. The failure mode is not bad math, it is never running the math until the job is underway and the terms are locked.

Building this from a blank workbook means wiring the job cost mix, the pay application calendar, the retainage schedule and the cash position table together and keeping them tied to actual job costs as they post. Our Construction Budget Tracker already carries the cost-code structure, the pay application schedule and the retainage tracking these formulas depend on, so you drop in the cost mix and the billing terms and get the peak working capital figure per job without rebuilding the plumbing. Run your next three bids through it before you price them, and you will turn down at least one job for the right reason.