📌 Stage 3 · Capturing Actuals · Job Costing · Timesheets · 35–45 min
Employee x job x cost code - the grain that makes labor costable
Learning goals
- Enter daily crew time as one row per person, job, and cost code
- Route office time to the OH job so it never contaminates job costs
- Pull employee names and loaded rates with VLOOKUP
Concepts
The grain that changes everything
A weekly timesheet that says Marcus worked 40 hours is payroll data. A timesheet that says Marcus worked 8 hours on J-2601 code 2200 is job-cost data. The row grain here is employee x job x cost code, and it is the single most valuable data-capture habit in this course.
Ridgeline captures a week per row per job: on Mar 5, 2026 Marcus books 8 hours to the kitchen's framing code. Dana's supervision hours go to code 1000 General Conditions on whatever job she watched that week. Priya's office day goes to job OH with code 0000 - visible, costed, but never on a job.
💡 Tips:
- Enter time weekly per person, not monthly: memory degrades fast and code 2200 versus 2100 is exactly what memory gets wrong.
- Agencies and consultants: this sheet is your timesheet against client engagements and service lines - billable utilization falls straight out of it in Lesson 16.
Lookups fill the row
The employee code in column D is all you type about who worked; the name in column E and the loaded rate in column I are pulled from the Employees sheet you built in Lesson 3.
=VLOOKUP($D5,Employees!$B$5:$C$104,2,FALSE)D5 holds the employee code (E02). VLOOKUP finds it in the first column of the Employees range and returns column 2, the name Marcus Reed. FALSE forces an exact match - a wrong code must fail loudly, not match something close.
=VLOOKUP($D5,Employees!$B$5:$G$104,6,FALSE)The same lookup widened to column G: with the range starting at B, column 6 is the loaded rate built in Lesson 3. Marcus's row prices his hours at $45.00, not his $36.00 wage.
Dates are real dates
Column C holds true date values displayed as Mar 5, 2026. That matters because Lesson 11 will filter these rows by month with SUMIFS date criteria, and text masquerading as dates breaks every one of those formulas.
A quick test you can use anywhere in the workbook: a real date right-aligns by default and responds to date arithmetic like +30; text sits left and does not.
Practice
Fixed case (Mar 1 – Jun 30, 2026). Pale-green cells are formulas. Cell I5 on Timesheets has been cleared - you will retype it.
- Read the first week — The exercise opens on Timesheets. Rows 5 through 9 are March weeks on the kitchen: two carpenters and the apprentice framing, Dana supervising, Sofia and Marcus back on framing later in the month.
- Retype the rate lookup in I5 — Cell I5 is blank. Type =VLOOKUP($D5,Employees!$B$5:$G$104,6,FALSE) and press Enter. Marcus Reed's loaded rate returns $45.00.
- Check the name lookup — E5 should read Marcus Reed. Scan the column: every row shows the person behind the code, pulled from Employees.
- Find the office time — Scroll to rows 24 through 27 (TS-1020 to TS-1023): Priya Patel, 8 hours each month-end, Job # OH and code 0000. Four rows, $240.00 each at her $30.00 loaded rate.
- Count the journal — Twenty-three rows cover four months of selected weekly entries. Real volume is higher; the case keeps the journal small enough to audit by eye.
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 VLOOKUP into I5 and got $45.00 for Marcus Reed
- ☐ Confirmed E5 reads Marcus Reed and the office rows carry job OH with code 0000
- ☐ Verified the journal holds 23 entries across the kitchen, deck, bath, and office