📌 Stage 2 · Estimating · Job Costing · Jobs Master · 35–45 min
One row per job - and the margin math that guards pricing
Learning goals
- Structure the Jobs sheet as the spine every journal points at
- Use Status and Contract Type so reports can include or exclude work cleanly
- Compute estimated margin dollars and margin percentage with guarded formulas
Concepts
One row per job
The Jobs sheet is the spine of the workbook. Each row is one job: a number (J-2601), a name, a client, a status, a contract type, a start date, and the contract price. Every timesheet line, purchase, and invoice you will enter later points at one of these numbers.
Status drives behavior. Open means won and in flight - these are the jobs WIP and the Job P&L track. Complete means finished and closed. Bid means priced but not won: it stays visible for pipeline but is excluded from the reports until someone signs. Internal is used by the OH row, which is not a job at all but the bucket office time lands in.
💡 Tips:
- Never reuse a job number, even for a change order on the same house - open J-2605 instead so history stays comparable.
- The OH row is what makes the utilization split in Lesson 16 work; do not delete it.
Margin dollars and margin percent
Column I rolls the estimate up from the Estimates sheet with SUMIF (Lesson 6 types it). Columns J and K turn price and estimate into the two numbers an owner actually prices from: margin dollars and gross margin percentage.
Both formulas guard against jobs that have no price yet - a bid with a placeholder zero would otherwise show a misleading negative margin and a division error.
=IF($H5<=0,"",$H5-$I5)H5 is the contract price and I5 the rolled-up estimated cost. If the price is zero or missing (a pure placeholder), return an empty string; otherwise return price minus estimate. For the kitchen: $96,250.00 - $77,000.00 = $19,250.00.
=IFERROR(($H5-$I5)/$H5,"")Gross margin percent is margin dollars divided by price, not by cost - $19,250.00 over a $96,250.00 price is 20.0%. IFERROR catches the divide-by-zero when the price is blank and returns an empty string instead of #DIV/0!.
The 20% habit
Look down column K: the kitchen and bath sit at 20.0% and the deck at 19.5%. Ridgeline's habit is to price estimate divided by 0.8, which lands gross margin at 20% before overhead - a common small-contractor rule of thumb.
The garage bid (J-2604) prices at 16.7% margin on its $15,500 estimate. Whether that is a deliberate competitive bid or an accident is exactly the conversation this sheet exists to force. Keep this 20% in mind: by Lesson 14 you will see what overhead does to it.
Practice
Fixed case (Mar 1 – Jun 30, 2026). Pale-green cells are formulas. Cell J5 on Jobs has been cleared - you will retype it.
- Read the job list — The exercise opens on Jobs. Rows 5 through 8 are the three open jobs and the garage bid; row 9 is the OH office bucket with no price and blank margin cells.
- Retype the margin formula in J5 — Cell J5 is blank. Type =IF($H5<=0,"",$H5-$I5) and press Enter. The kitchen's estimated margin returns $19,250.00 - the $96,250.00 contract over its $77,000.00 estimate.
- Read the margin percents — Column K should show 20.0% for the kitchen, 19.5% for the deck, 20.0% for the bath, and 16.7% for the garage bid - the outlier you would challenge before signing.
- Notice what the OH row does not do — Row 9 has no price, so J9 and K9 are empty strings, not errors. Guarded formulas keep summary sheets clean when non-jobs share a table with real ones.
Online exercise
The spreadsheet below is editable — follow the practice steps right on this page. Steps that need ribbon menus should be done in your own Excel with the downloadable practice file.
Checklist
Work through each item; when every box passes, this lesson is done:
- ☐ Typed the IF guard into J5 and got $19,250.00 of estimated margin on the kitchen
- ☐ Confirmed K5 shows 20.0%, K6 19.5%, and K8 16.7% on the garage bid
- ☐ Verified the OH row stays blank instead of erroring