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

jbjohnson93

New Member
Joined
Jul 14, 2020
Messages
1
Office Version
  1. 2019
Platform
  1. Windows
  2. MacOS
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 NumberNameRole TypeTeamEmployee TypeAugust allocationSeptember allocationOctober allocationNovember allocation
420420TomDeveloperRedFTEProject 1Project 1Project 2Project 2
696969SamQARedContractorProject 1Project 1Project 2Project 2
123123MarkQABlueFTEProject 2Project 1Project 1Project 3
456456CarlDeveloperBlueContractorProject 3Project 2Project 1Project 2
789789BobManagerRedFTEProject 1Project 1Project 1Project 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.
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
1,056
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
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
ABCDEFGHI
1Employee ID NumberNameRole TypeTeamEmployee TypeAugust allocationSeptember allocationOctober allocationNovember allocation
2420420TomDeveloperRedFTEProject 1Project 1Project 2Project 2
3696969SamQARedContractorProject 1
4123123MarkQABlueFTEProject 2Project 1Project 1Project 3
5456456CarlDeveloperBlueContractorProject 3Project 2Project 1Project 2
6789789BobManagerRedFTEProject 1Project 1Project 1Project 1
7696969SamQARedFTEProject 1Project 2Project 2
Sheet1

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

Watch MrExcel Video

Forum statistics

Threads
1,127,596
Messages
5,625,720
Members
416,130
Latest member
galgozzi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top