📌 Stage 4 · Overhead & WIP · Job Costing · Allocation · 30–40 min

One rate, applied to direct cost, makes jobs carry the office

Learning goals

  • Compute a predetermined overhead rate as pool over direct cost
  • Apply the rate to each job's actual direct costs
  • Explain why unallocated overhead flatters every job's profit

Concepts

One rate, computed not negotiated

The rate cell on Overhead!H14 is one division wrapped in a guard: pool over direct costs incurred. It lands at 16.7% for the case period - call it a predetermined overhead rate, the way a cost accountant would.

The base is direct cost (labor plus materials plus subs), not labor hours and not revenue. Direct cost is the least gameable base available to a small contractor: it is already captured, it is hard to shift between jobs, and it scales with the work that actually consumes office attention.

=IFERROR($H$12/$H$13,0)

H12 is the $18,110.00 pool, H13 the $108,655.00 of direct costs to date: $18,110.00 / $108,655.00 = 16.7% once formatted as a percentage. IFERROR guards the empty-workbook case where direct cost is zero and the division would return #DIV/0!.

Every job carries its share

On the Job P&L sheet, column H applies the rate to each job's actual direct cost. The kitchen has spent $66,490.00, so it carries $11,082.18 of overhead; the deck $12,990.00 and $2,165.10; the bath $29,175.00 and $4,862.72. The three allocations sum back to the pool - $18,110.00 - by construction, so the office is fully paid for exactly once.

=$G5*'Overhead'!$H$14

G5 is the kitchen's actual direct cost from WIP and H14 the rate cell. Because H14 is anchored once and referenced everywhere, repricing the overhead story is a one-cell edit on the pool sheet. The sheet name 'Overhead' is quoted in the reference - harmless here, mandatory when a name contains a space.

💡 Tips:

  • Other bases exist - percent of labor cost, per field hour - and contractors argue them like sports teams. What matters is consistency: pick a base, keep it, and compare jobs under the same rule.

Why allocate at all

Without allocation, every job looks about 17% more profitable than it is, and the office shows up only as a mysterious gap at the bottom of the year. With allocation, the question changes shape: not is the company profitable, but which jobs are - after carrying their share of the shop.

Look ahead at the arithmetic you have now assembled: estimates were priced at 19.5% to 20.0% gross margin (Lesson 6), and overhead consumes 16.7 points of direct cost. The margin left after overhead is thin - around 3% of cost - and Lesson 14 will show exactly that in the Job P&L. That finding, not any single formula, is the reason this stage exists.

Practice

Fixed case (Mar 1 – Jun 30, 2026). Pale-green cells are formulas. Cell H5 on Job P&L has been cleared - you will retype it.

  1. Read the rate cell — The exercise opens on Job P&L. First click the Overhead tab and confirm H14 shows 16.7% - pool over direct cost from Lesson 11.
  2. Retype the allocation in H5 — Back on Job P&L, cell H5 is blank. Type =$G5*'Overhead'!$H$14 and press Enter. The kitchen's allocation returns $11,082.18.
  3. Check the other jobs — H6 should read $2,165.10 for the deck and H7 $4,862.72 for the bath. Column I adds direct plus allocated: the kitchen's total cost to date is $77,572.18.
  4. Tie out to the pool — Row 8 totals column H at $18,110.00 - the whole pool, distributed. If it ever disagrees with Overhead!H12, a job is missing from the P&L list.

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 allocation into H5 and got $11,082.18 for the kitchen
  • ☐ Confirmed the deck allocates $2,165.10 and the bath $4,862.72
  • ☐ Verified the overhead rate reads 16.7% and total allocations tie back to the $18,110.00 pool