(HR) Headcount Planning - how to account for contractor-to-fulltime conversions

[jbjohnson93]

Hi there, first time posting. Let me know if there's anything else I can include here to help explain the problem.

I am in the process of documenting planned headcount and project allocations in my department for the rest of 2020, where each row represents how a given employee is going to be used for the remainder of the year by month. The end goal is to be able to determine things like how many developers, QA, project managers, etc. we have assigned to any project, and furthermore how many contractors vs. full-time employees we have. Here is a sample of my table setup:

Employee ID Number	Name	Role Type	Team	Employee Type	August allocation	September allocation	October allocation	November allocation
420420	Tom	Developer	Red	FTE	Project 1	Project 1	Project 2	Project 2
696969	Sam	QA	Red	Contractor	Project 1	Project 1	Project 2	Project 2
123123	Mark	QA	Blue	FTE	Project 2	Project 1	Project 1	Project 3
456456	Carl	Developer	Blue	Contractor	Project 3	Project 2	Project 1	Project 2
789789	Bob	Manager	Red	FTE	Project 1	Project 1	Project 1	Project 1

Let's say Sam has been doing a great job and we have decided to convert him from contract to full-time in September. Is there a way to account for this while not changing overall headcount numbers for Red team or Project 1? Up until now we have just been adding a new row for people undergoing conversions, which lets us capture the allocations Sam had or has while he's still a contractor, and it lets us know when he starts working as a full-timer, but it comes with one major caveat: it adds one to our headcount, which would be erroneous since it is not technically a new person to the department. Looking at our ultimate goal of knowing how many people of which type we have on each project, this would falsely tell us that we have, for example, 2 QAs on Red Team, and 3 QAs on Project 1 in September.

One possibility I'm brainstorming is to retain one row per person and to create another column letting me know if that person is being converted. But when it comes time to summarize the numbers, I don't know how I could selectively exclude or include Sam when getting my total of FTEs/Contractors on Project 1 during September, for example. The problem is hairier especially if people get converted in the middle of the month such that they are a contractor for the first half and an FTE for the second half.

 

[jmacleary]

Hello and welcome to the forum. One idea that comes to me is to add the new changed role, and blank out the allocations in months where that role doesn't occur. So, for your example, the table would look like this:

Book2
	A	B	C	D	E	F	G	H	I
1	Employee ID Number	Name	Role Type	Team	Employee Type	August allocation	September allocation	October allocation	November allocation
2	420420	Tom	Developer	Red	FTE	Project 1	Project 1	Project 2	Project 2
3	696969	Sam	QA	Red	Contractor	Project 1
4	123123	Mark	QA	Blue	FTE	Project 2	Project 1	Project 1	Project 3
5	456456	Carl	Developer	Blue	Contractor	Project 3	Project 2	Project 1	Project 2
6	789789	Bob	Manager	Red	FTE	Project 1	Project 1	Project 1	Project 1
7	696969	Sam	QA	Red	FTE		Project 1	Project 2	Project 2
Sheet1

To address the 'hairier' bit I think you are going to have to do a radical redesign.