Payroll is the biggest cost in most budgets, and FP&A plans it person by person: one row per role, one column per month. Each cell is the monthly salary when the person is on the payroll, and 0 when not:
=IF(D$4>=$B5,$C5/12,0)
The month-end dates sit in row 4 and the start dates in column B, so one formula with mixed references fills the whole grid.
Three edge cases separate a rough plan from a good one:
- Mid-month starts. Someone who starts on February 16 is paid for 13 of 28 days. Scale the month by
(month end - start + 1) / DAY(month end), kept between 0 and 1 with MIN and MAX.
- Pay rises from a date. A factor row, 1.00 before the date and 1.04 after, multiplies the grid.
- Payroll tax caps. Employer tax often stops once a person's pay for the year passes a cap. Tax only the part of this month's pay that's still under the cap.
Start Feb 16, salary 125,000:
Jan =125000/12*MIN(1,MAX(0,(Jan 31-Feb 16+1)/31)) 0
Feb =125000/12*MIN(1,MAX(0,(Feb 28-Feb 16+1)/28)) 4,836
Mar ... MIN(1, 44/31) = 1 10,417
13 of 28 days in February, then full months.