Math for Business Operations

Applied Accounting with Excel

Lesson ProgressPhase 3 of 6
Phase 3Guided Practice
Guided Practice: Pay Stub Studio: Taxes, Net Pay, and Proof

Model FIT/FICA/state tax formulas and connect them to a pay stub template

🧪 Phase 3: Guided Practice

Build the Tax Math & Pay Stub Template

Follow these cards with your teacher. Pause to implement each step in Excel, then resume once your numbers match.

Step 1 — Link Inputs to TaxMath
  • Create SelectedID cell with data validation (list of IDs).
  • Use XLOOKUP(SelectedID, tblPayrollInputs[EmployeeID], tblPayrollInputs[EmployeeName]) to pull info into TaxMath.
  • Store PayPeriod label (Bi-weekly #) so it prints on the stub.
Step 2 — Implement Formulas

Taxable Income Formula

=LET(g,GrossPay, pre,PreTaxBenefits, std,StandardDeduction/26, MAX(0, g - pre - std))

Divide the annual standard deduction by the number of pay periods. Use MAX to prevent negative taxable income.

FIT (Bi-weekly Table)

=LET(inc,TaxableIncome, row,XMATCH(inc, TableFIT[UpperBound],1), base,INDEX(TableFIT[BaseTax],row), rate,INDEX(TableFIT[Rate],row), lower,INDEX(TableFIT[LowerBound],row), base + (inc - lower)*rate)

The table must match the employee’s filing status. Consider naming each table (FIT_Single, FIT_Married) and switching with XLOOKUP on filing status.

FICA

=ROUND(TaxableIncome*0.062,2) =ROUND(TaxableIncome*0.0145,2)

Social Security (6.2%) stops after $172,800 annual wages. Medicare (1.45%) never stops at this level.

State Tax

=ROUND(TaxableIncome * StateRate, 2)

Use the State column to pull the correct starter rate.

Net Pay

=GrossPay - PreTaxBenefits - FIT - SocialSecurity - Medicare - StateTax - AdditionalWithholding

Display net pay prominently on the pay stub alongside year-to-date totals.

Step 3 — Design the Pay Stub
  • Use a two-column layout: left for employer/employee details, right for earnings and deductions.
  • Add section headers (EARNINGS, TAXES, SUMMARY) with subtle background fills (e.g., #FDF2F8).
  • Insert shapes/lines to mimic professional stubs. Include Net Pay in a bold box.
  • Use TEXT and FORMAT functions for dates ("Mar 14, 2025"), currency, and percentage formats.
Teacher Checkpoints
  • SelectedID drives every field on the stub.
  • FIT changes when filing status changes.
  • Changing State updates the state tax line.
  • Net Pay equals Gross – deductions (verify with calculator).

Show your teacher when all checkpoints clear before moving to independent practice.

Previous: Introduction
Lesson OverviewNext: Independent Practice
Phase Navigation
HookIntroductionGuided PracticeIndependent PracticeAssessmentClosing