Financial
Cost-to-cost project progress: a simplified revenue and billings worksheet
Practice cost-based progress and cumulative catch-up arithmetic. Determine applicable ASC 606 or IFRS 15 accounting separately before using a project model.
A simplified cost-based progress model
This worksheet assumes one performance obligation already qualifies for recognition over time and the chosen cost input method faithfully depicts progress. A long contract duration alone does not establish those conditions. Transaction price, variable consideration and allocation are supplied assumptions.
Not every incurred cost measures progress: inefficiencies and certain uninstalled materials may require adjustments. Use appropriately adjusted costs. Revenue less billing is a reconciliation figure here; a contract asset, receivable or liability classification depends on the rights and obligations, not only this difference.
| Illustrative project metric ($2.0M price) | Formula | Period Calculation |
|---|---|---|
| Percent Complete (Cost-to-Cost) | $720k / ($720k + $880k) | 45.0% ($720k / $1,600k est. cost) |
| Cumulative vs. Current Revenue | 45% * $2.0M - $500k prior | $900k cum | $400k current period |
| Over / (Under) Billing | $950k Billed - $900k Earned | +$50,000 billed above modeled revenue |
Current-period revenue in the simplified model
A2 is the allocated price, B2 adjusted cost to date, C2 adjusted estimated remaining cost, and D2 previously recognized cumulative revenue. All inputs must be numeric and nonnegative, with positive total estimated cost. A negative current-period catch-up is possible after estimates change; it is not forced to zero.
=IF(OR(COUNT(A2:D2)<>4,MIN(A2:D2)<0,B2+C2<=0),"Invalid inputs",LET(progress,B2/(B2+C2),ROUND(progress*A2-D2,2)))Billings minus modeled cumulative revenue
Positive means billing exceeds modeled revenue; negative means the reverse. This does not by itself classify the amount for financial reporting. E2 contains nonnegative cumulative billings.
=IF(OR(COUNT(A2:C2)<>3,MIN(A2:C2)<0,NOT(ISNUMBER(E2)),E2<0,B2+C2<=0),"Invalid inputs",LET(progress,B2/(B2+C2),ROUND(E2-progress*A2,2)))Try the example
Use the original values or change them to test your understanding.