Lesson ProgressPhase 2 of 6
Phase 2Introduction
Introduction: Pay Stub Studio: Taxes, Net Pay, and Proof
Data flow from payroll inputs → taxable income → tax components → printable stub
Blueprint: Inputs → Taxable Income → Pay Stub
This phase maps the workbook before you open Excel. Download the dataset, label the sheets, and understand the math every line on the pay stub represents.
Sheet 1 — PayrollInputs
- Import unit05-lesson06-paystub-practice.csv.
- Columns: EmployeeID, EmployeeName, FilingStatus, StandardDeduction, GrossPay, PreTaxBenefits, AdditionalWithholding, State, StateRate.
- Format as tblPayrollInputs so the selector can find people by ID.
Sheet 2 — TaxMath
- TaxableIncome = Gross – PreTaxBenefits – (StandardDeduction ÷ PayPeriodsPerYear).
- FIT = Use IRS table (bi-weekly) for the filing status. Provide columns: LowerBound, UpperBound, BaseTax, Rate over Lower.
- Social Security = TaxableIncome × 6.2%. Medicare = TaxableIncome × 1.45%.
- StateTax = TaxableIncome × StateRate (starter rate for CA, 0 for WA, etc.).
Sheet 3 — PayStub
- Drop in a clean layout: left column for employee info, right column for earnings/deductions summary.
- Add an Employee ID dropdown (Data Validation → list from tblPayrollInputs[EmployeeID]).
- Every field uses XLOOKUP to pull values from TaxMath or PayrollInputs, so the stub updates instantly.
- Use shapes, borders, and subtle color blocks for a printable, investor-ready finish.
Non-Negotiables
- The pay stub must show: Pay Period, Gross Pay, Taxable Income, FIT, Social Security, Medicare, State Tax, Additional Withholding, Net Pay.
- Include memo lines for YTD totals (you can copy formulas down as you add more pay periods later).
- Add a footer with Sarah’s business name, EIN placeholder, and a thank-you note for professionalism.
Planning Tip: Decide how many pay periods per year your stub represents (bi-weekly = 26). You will divide the standard deduction by that number when calculating taxable income.
Lock in the Vocabulary
Pay Stub Vocabulary
Complete each sentence before opening Excel.
Attempts: 0Score: 0%
📝 Fill in the Blanks
Complete each sentence by typing the missing word or phrase
📚 Word Bank
Available answers