FTE Costs Spread Over Months

[Excel-Access]

Dear Excel Community

This is probably not difficult but I am struggling and need a formula to apply please.

I am trying to build an Excel formula to factor FTEs leaving and thus realising the benefits

In the attachment example 11 FTEs will leave by December, so 3 in April another 1 in June and 1 in Jul etc.

If FTEs leave in April you may not realise the benefit until say 1 or 2 months later

The formula should apportion the costs by FTE leaving in the month.

Example 2 FTEs leave in April, benefit not realised until 1 month later so May

In May no additional FTE so earlier months benefit stays the same

In June 1 FTE leaves, thus there is an additional saving now of 3 FTEs

I hope the attachment helps explains this better

Thanks

 

[Excel-Access]

I have provided clarification in the yellow sections

 

[vletm]

Excel-Access
If FTE is as written below
https://en.wikipedia.org/wiki/Full-time_equivalent
then
what would be Yours FTE?

 

[Excel-Access]

Hi

Not sure I understand your question?

 

[vletm]

Excel-Access
Okay ...
You use term FTE -- okay?
I 'googled' about FTE -- okay?
Full-time equivalent or whole time equivalent is a unit that indicates the workload of an employed person in a way that makes workloads or class loads comparable across various contexts. FTE is often used to measure a worker's or student's involvement in a project, or to track cost reductions in an organization.
Is this FTE same as Your FTE?

Cost per FTE.
Costs per FTE calculates the proportion of costs to FTE on costcenter or cost center group level. FTE or Full Time Equivalent is a unit of measure of an employee's or group's productivity. An FTE of 1.0 means that the person, or group of people, is equivalent to a full-time worker.

Other sample, 'everyone knows' OMG or how?:
What does OMG really mean? eg Outlaw Motorcycle Gangs
What does OMG mean in food? Genetically Modified Organisms
What does OMG mean in medical terms? Ocular Myasthenia Gravis
... and so on!

Explain, what would be Yours FTE?

 

[bosco_yip]

Try..........................

1] I open a new sheet named "Formula" as the output sheet

2] In D6, formula :

=COUNTA(F3:N3)

3] In E6 >> Data >> Data Validation>>

>> Allow : List

>> Source : 1,2,3,4,5,6,7,8,9

>> OK

4] In F6, formula copied across :

=F5-N(E5)

5] In F9, formula copied across :

=IF(COUNTA($F$3:F$3)>$E$6,IF(OFFSET(F6,,-$E$6)=0,E9,$C$9/$D$6*OFFSET(F6,,-$E$6)+E9),0)

5] Click E6 dropdown List and choose the number of month for testing/ checking purpose

Regards
Bosco