Construction Profit and Loss Template Excel: How to Use

How-to basics · Updated November 22, 2026

A construction profit and loss template in Excel summarizes earned revenue, direct job costs, gross profit, overhead, and operating profit for a chosen period or project. Build one with a summary sheet and a detailed cost log; formulas can total the figures, but the template only works if revenue and costs are classified consistently.

What a construction P&L template shows

A profit and loss statement, also called an income statement, reports income and expenses over a defined period. A construction business can use it to review company-wide results, while a project-level version helps compare the financial performance of individual jobs. These views answer different questions: a profitable company-wide month can still contain a project that is losing money.

A practical construction P&L usually separates revenue from direct job costs and business overhead. Direct costs are attributable to delivering a project; overhead supports the business generally. Gross profit is revenue less direct costs. Operating profit is gross profit less overhead included in the statement. A P&L is not the same as a cash-flow report: invoices, payments, retainage, and the timing of earned revenue may fall in different periods.

Use the template as a management and record-organizing tool, not as a substitute for the accounting method required for your business. Revenue recognition, retainage, work in progress, and cost treatment can depend on contracts and applicable accounting rules. Ask a qualified accountant how those items should be handled in your circumstances.

Set up the workbook and summary

Create a workbook with a P&L Summary sheet and a Job Cost Log sheet. On the summary sheet, use column A for account names and column B for amounts for one reporting period. Put the period dates near the top, and show whether the report covers the whole company or one project. A useful basic layout is:

  • Revenue: earned contract revenue and approved change-order revenue, as applicable.
  • Direct costs: materials, field labor, subcontractors, equipment, permits, and other job costs.
  • Gross profit and gross margin.
  • Operating expenses: items such as office payroll, insurance, rent, and professional fees, according to your chart of accounts.
  • Operating profit and operating margin.

Keep account labels specific enough to support decisions, but do not create dozens of categories that nobody will use consistently. For example, separate equipment costs if you need to distinguish rentals from owned-equipment charges; otherwise, a simpler equipment line may be more practical. Apply the same categories across projects and reporting periods.

Add formulas for totals and margins

Enter actual amounts in the input rows, then use formulas for calculated rows rather than typing totals by hand. In the following example, column A contains labels and column B contains amounts: B2 is contract revenue of $125,000, B3 is change-order revenue of $8,500, B7:B11 are direct costs of $36,500, $42,000, $28,000, $6,500, and $3,000, and B16 is overhead of $10,000. Put the total revenue formula in B4; it returns $133,500:

=SUM(B2:B3)

With the direct-cost amounts in B7:B11 as listed above, put this formula in B12. It returns $116,000:

=SUM(B7:B11)

With total revenue in B4 and total direct costs in B12, put this in B13 for gross profit. The result is $17,500:

=B4-B12

With gross profit in B13 and revenue in B4, put this in B14 for gross margin. It returns about 13.1%:

=IFERROR(B13/B4,0)

Format B14 as a percentage. With gross profit in B13 and overhead in B16, put this in B17 for operating profit. It returns $7,500:

=B13-B16

With operating profit in B17 and revenue in B4, put this in B18 for operating margin. It returns about 5.6%:

=IFERROR(B17/B4,0)

These figures are illustrative, not a forecast or a target margin. The example assumes the revenue and expense figures are appropriate for the same reporting period and that the overhead line includes the expenses you intend to deduct.

Track job costs in a detail log

A single summary is easy to read but does not explain where costs came from. On the Job Cost Log sheet, create columns for project ID, transaction date, cost category, cost code, vendor or employee reference, description, and amount. Enter one transaction per row, and keep the project ID and category values consistent. This makes it possible to filter the log and summarize costs by job, category, or period.

For an Excel table, select the log range and choose Insert > Table, or use Ctrl+T on Windows; on Mac, Command+T is the usual shortcut. Confirm that the range includes headers. A table expands as new rows are added, and its filters make it easier to review transactions. Avoid combining several costs into one row if they belong to different projects or categories.

For a simple project-and-category total, assume project IDs are in A2:A100, categories in B2:B100, amounts in C2:C100, the chosen project ID is in E2, and the chosen category is in E3. If the matching rows are Job-101 / Labor / $12,000, Job-101 / Materials / $8,000, Job-101 / Labor / $3,500, and Job-102 / Labor / $6,000, enter this formula to total Job-101 labor. The result is $15,500:

=SUMIFS(C2:C100,A2:A100,E2,B2:B100,E3)

Check that the ranges cover the same number of rows and that amounts are numeric. In Microsoft 365 for Windows, the formula uses commas as argument separators under the standard regional settings; Excel may display a different separator if Windows regional settings require it.

Choose the revenue basis and reporting period carefully

One of the most important decisions is what the revenue line represents. Cash received is not automatically the same as revenue earned, and billings are not necessarily the same as earned revenue. Construction projects can involve progress billing, retainage, deposits, change orders, and work that spans reporting periods. Entering deposits or invoices as revenue without considering the business’s accounting method can make one month look unusually strong and another unusually weak.

Set a clear period, such as a calendar month, and use the same start and end dates for the related revenue and expense figures. If you compare months, keep the basis consistent. A project P&L may also need budget, actual, and committed-cost columns, but committed costs should not be presented as if they were already incurred expenses. Label each measure so readers know what they are comparing.

For a company-level statement, include revenue and costs from all relevant projects and business operations without counting the same transaction twice. For a project-level statement, allocate only the costs and revenue that belong to that project, and document how shared costs are assigned if you choose to allocate them. Your accountant can advise on treatment for work in progress and other accounting questions.

Build a repeatable monthly workflow

A spreadsheet becomes more useful when updating it follows a dependable routine. Before sharing a report, use steps like these:

  1. Set the reporting dates and identify whether the statement is for one project or the whole business.
  2. Export or collect the period’s transactions from the accounting system, payroll records, invoices, and other source documents.
  3. Assign each item a project ID and cost category. Investigate missing IDs, duplicate entries, and unclear descriptions instead of guessing.
  4. Reconcile the totals to the source records and confirm that the reporting basis is consistent with prior periods.
  5. Review unusual changes, such as a large subcontractor invoice or a revenue adjustment, and add a brief note where context is needed.
  6. Save a dated copy of the workbook and restrict edits to the cells intended for data entry.

In Excel for Microsoft 365 on Windows, use Home > Number Format to apply currency or percentage formats. Use Home > Conditional Formatting if you want to highlight negative values or unusually large variances, and keep the rule simple enough that users can understand it. Menu wording can differ slightly by platform or update; Excel for Mac has comparable formatting tools, though ribbon layout and some shortcuts differ.

Read margins and examples in context

Gross margin is gross profit divided by revenue; operating margin is operating profit divided by revenue. In the example above, $17,500 divided by $133,500 is approximately 13.1%, while $7,500 divided by $133,500 is approximately 5.6%. A margin percentage helps compare projects of different sizes, but it does not explain the reason for a change or establish whether a project is performing well.

For example, a project may show lower gross profit because material costs rose, labor hours exceeded the estimate, or revenue has not yet been recorded on the same basis as costs. Compare actuals with the original estimate, current commitments, and prior periods where those figures are available. Review the underlying transactions before drawing conclusions from a single percentage. The spreadsheet arithmetic is useful for analysis, but it does not determine how a transaction should be accounted for.

Common spreadsheet problems to prevent

  • Mixing cash and earned revenue: Label the revenue basis and use the method approved for the business rather than treating deposits, billings, and earned revenue as interchangeable.
  • Inconsistent cost coding: Agree on category and project names, and correct misspellings or duplicate labels so the same type of cost does not split across multiple totals.
  • Hard-coded totals: Use formulas for subtotals and margins, and check that formulas include newly added rows.
  • Double-counted costs: If an accounting export already includes a transaction, do not enter it again manually without a clear reason.
  • Hidden assumptions: State whether overhead is included and how shared costs are treated. A project report that omits these details can be mistaken for a full company P&L.
  • Unprotected formulas: Distinguish input cells from calculated cells with formatting or sheet protection, and keep an untouched backup before making major changes.

Excel template or accounting software?

Excel is flexible and familiar, and it can be a sensible starting point for a small operation, a one-off analysis, or a report built from verified accounting data. It is also easy to customize categories and compare scenarios. Its weakness is that users must manage imports, formulas, access, and version control. A copied workbook can quickly become inconsistent with the accounting records.

Accounting or construction-management software may provide integrated transaction records, project costing, permissions, and reports, depending on the product and setup. It can reduce manual re-entry, but it still needs accurate coding and appropriate configuration. A useful middle ground is to maintain the books in accounting software and export a controlled report to Excel for analysis, rather than treating a spreadsheet as the only record of transactions.

Frequently Asked Questions

What should a construction profit and loss statement include?
A typical statement includes revenue, direct project costs, gross profit, operating expenses, and operating profit. The exact categories depend on the business’s chart of accounts and accounting method. Keep project-level reporting distinct from company-wide results.

Can I make a construction P&L template in Excel for free?
You can build one in Excel using the layout and formulas above if you already have access to Excel. The formula examples do not require an add-in. Whether Excel itself is free depends on your Microsoft account, license, and the version you use.

How do I calculate construction gross profit in Excel?
Subtract total direct costs from revenue. If revenue is in B4 and direct costs are in B12, enter =B4-B12 in the gross-profit cell. The example in this guide returns $17,500 from $133,500 revenue and $116,000 in direct costs.

Is a project P&L the same as a job-cost report?
No. A job-cost report commonly organizes costs by project and cost code, and may compare actual costs with estimates or commitments. A project P&L presents revenue and expenses for that project over a period or across its life. A detailed job-cost log can supply cost totals for a P&L, but the reports serve different purposes.

Should retainage be listed as construction revenue?
Its presentation depends on the contract, the accounting method, and applicable reporting requirements. Do not assume that retainage is simply cash revenue when collected or that it should always be handled in one particular way. Confirm the treatment with a qualified accountant who can review the business’s records and terms.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.