Lesson ProgressPhase 2 of 6
Phase 2Introduction
Introduction: Schedule-to-Pay: Building the Weekly Labor Engine
Professional‑grade automation patterns, constraints, and gotchas for payroll
Blueprint: Roster → Schedule → Hours
Before you open Excel, lock the architecture. Every sheet you build today has a job description and feeds a question Sarah must answer for Harbor Market. Keep the writing at an 8th-grade reading level so your teammates can follow the plan while they code.
Sheet 1 — EmployeeRoster
- Columns: EmployeeID, Name, Department, Role, HourlyRate, Availability, PreferredDays.
- Format as table tblRoster. Structured references auto-expand.
- Store overtime eligibility and max hours so SUMIFS can respect individual caps.
Download the starter data here: unit05-lesson05-schedule-practice.csv
Sheet 2 — WeeklySchedule
- Grid layout: rows = time blocks (e.g., 6a–10a, 10a–2p, 2p–6p, 6p–close).
- Columns = Sun → Sat. Each cell has a data-validation dropdown referencing tblRoster[EmployeeID].
- Use conditional formatting to highlight double-booked employees or empty high-priority slots.
Sheet 3 — Hours & Gross
- Use SUMIFS to total hours by EmployeeID and by Department.
- Add helper columns: RegularHours, OvertimeHours, GrossPay.
- Formula idea: =LET(h,TotalHours, rate,XLOOKUP(ID, tblRoster[EmployeeID], tblRoster[HourlyRate]), reg, MIN(40,h), ot, MAX(0,h-40), reg*rate + ot*rate*1.5)
Questions Investors Will Ask
Build with these answers in mind:
- “What is the cost of fully staffing the bakery next week?” → Filter WeeklySchedule by department.
- “Who crosses 35 hours before Friday?” → Use conditional formatting plus the Hours sheet.
- “How much cash do I need on payday if we add a Sunday brunch shift?” → Hours & Gross updates instantly.
Pro Tip: Name your ranges now (dvEmployee, rngBlocks,tblHours). Lesson06 combines this workbook with the Payday Simulator, so clean names save hours later.
Lock In the Vocabulary
Schedule Blueprint Vocabulary
Complete each sentence before you start building your workbook.
Attempts: 0Score: 0%
📝 Fill in the Blanks
Complete each sentence by typing the missing word or phrase
📚 Word Bank
Available answers