Calculating Consistent Rate Spreads

When you calculate annual amounts, how can you spread those amounts over 12 months in a logical manner? For example, given monthly employee counts (FTEs), calendar days, and an annual total salary budget, how can you spread the salary dollars over the year in a consistent manner?

In the following screenshot an annual salary amount is presented in cell O4. An average employee FTE and average day per month have been calculated in column O based on a given number of employees each month and the days per month. The task is to spread the salary dollars using a consistent rate per month.

To do so, enter the following formula into cell C10. Copy that formula across the range C10:N10.

Calc 1

=(C6/$O$6)*(C8/$O$8)

Sum the results of the range C10:N10 into cell O11. The spreadsheet should look like this:

Next, enter the following formula into cell C12. Copy that formula across the range C12:N12.

Calc 2

=C10/$O$10

The spreadsheet should look like this:

Next, calculate the monthly salaries. Enter the following formula into cell C4 and copy it across the range C4:N4.

Calc 3

=$O$4*C12

The spreadsheet now looks like this:

Finally, calculate the monthly salary rate. Enter the following formula into cell C14 and copy it across the range C14:N14.

Calc 4

=C4/C6/C8

If all of the calculations worked, the final salary / FTE / calendar day should be exactly the same for all 12 months. In the screenshot below, "Calc 4" shows that it is.

To illustrate graphically, the final salary spread varies from month to month . . .

but the salary rate remains consistent . . .

This technique can be used to calculate a consistent monthly rate for one or more variables.