📌 Stage 2 · People and Revenue · Staff & Payroll · fully loaded cost · 35–45 min

Salary plus employer taxes plus health, per person per month

Learning goals

  • Break a monthly payroll cost into salary, employer taxes, and health benefits
  • Write the monthly-salary, tax, and total-cost formulas from the case
  • Explain why the roster total is the largest fixed block in the plan
  • Read the fully loaded cost of any team member from the sheet

Concepts

Fully loaded cost

What an employee costs the studio each month is not their salary. It is salary, plus the employer's share of payroll taxes, plus the employer's share of benefits. For Maya Okafor (Owner & Creative Director, $102,000.00 a year) the three pieces are $8,500.00 + $807.50 + $560.00 = $9,867.50 a month — about 16% on top of base salary.

Planning with salary alone is the classic small-business budget error: the payroll tax and benefit lines arrive whether or not you planned them. The Staff & Payroll sheet makes all three visible per person.

Monthly salary

The roster stores annual salary because that is how offers are written; the model needs monthly. The conversion is a plain division:

=$E5/12

Staff & Payroll F5: Maya's $102,000.00 annual salary divided by 12 = $8,500.00 per month. Column F repeats it for every roster row.

Employer payroll taxes

Employer taxes are a percentage of wages — here a blended 9.5% planning rate covering the employer FICA share plus FUTA and SUTA, rounded to cents. The rate lives once on Assumptions (F23) so a statutory change is a one-cell edit:

=ROUND($F5*Assumptions!$F$23,2)

Staff & Payroll G5: 9.5% × $8,500.00 = $807.50 for Maya. Column G repeats it per person; the P&L later sums the column by month.

💡 Tips:

  • The blended rate is a planning figure: actual Social Security and Medicare shares have wage bases, and state unemployment rates vary by employer history. Confirm your own rates with your payroll provider before committing.

Health insurance and total cost

Health premiums are a flat monthly amount per enrolled employee, pulled from the Assumptions driver so scenario changes (Best $520.00, Base $560.00, Worst $620.00) flow everywhere at once:

=Assumptions!$F$24

Staff & Payroll H5: the Base health premium of $560.00 per employee per month.

=$F5+$G5+$H5

Staff & Payroll I5: total monthly cost = $8,500.00 + $807.50 + $560.00 = $9,867.50 for Maya. Row 10 sums the roster: $34,883.50 per month across all five people.

Practice

Fixed case: Harbor Creative Studio LLC, a four-person design studio in Austin, Texas, planning calendar year 2026. Cached numbers are the Base scenario. Pale-green cells are formulas; white cells are typed inputs. The exercise opens on the sheet this lesson teaches. Cell F5 (Maya's monthly salary) is blank: retype the division that produces it.

  1. Open Staff & Payroll — The exercise opens on Staff & Payroll. Row 4 headers run Employee through FY2026 Cost; the roster sits on rows 5 to 9 and the TOTAL row is row 10.
  2. Retype the monthly salary in F5 — Cell F5 (Maya's Monthly Salary) is empty. Type =$E5/12 and confirm: it should return $8,500.00 from the $102,000.00 annual salary in E5.
  3. Read the loaded cost — Check G5 = $807.50 (employer taxes at 9.5%), H5 = $560.00 (health premium), and I5 = $9,867.50 total monthly cost. Read down the roster: Luis $7,677.50, Priya $6,582.50, Dana $5,816.00, Ellis $4,940.00.
  4. Check the roster totals — Row 10 should read $29,300.00 monthly salaries (F10), $2,783.50 employer taxes (G10), $2,800.00 health (H10), and $34,883.50 total monthly cost (I10).
  5. Sanity-check the annual figure — The FY2026 Cost column (K) shows $388,962.00 in K10 — the payroll engine behind the whole plan. Lesson 5 explains why that is not simply 12 × $34,883.50.

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:

  • ☐ Retyped =$E5/12 in F5 and it returned $8,500.00
  • ☐ I5 total monthly cost for Maya reads $9,867.50 (salary + taxes + health)
  • ☐ I10 roster total reads $34,883.50 per month and K10 reads $388,962.00 for FY2026
  • ☐ Can name the three components of fully loaded cost and where the rates live