MS Excel is great at assisting with planning and one of the key planning areas is how many resources you may need given a specific workload, and more specifically how many ‘full time equivalent’ staff you may need. In order to do full time equivalent calculations in Excel there are some functions that would be useful to master.
If you get the calculation correct, it should be easy to create an FTE matrix as per below that shows how many people we need based on the number of jobs and how long each job takes.
The issue with FTE calculations
The issue with full time equivalents (FTE), is that we are assuming you need to hire a whole person (can’t have a quarter of a person for example). This means that even if you only have 1 job, you will need a full time employee. If a full time employee can handle 30 jobs a month, then when you have 31 jobs you need a whole second person.
Another issue may be that you need a group of people. So perhaps to handle 1 job you need a team of 4 people. At 31 jobs you would need to hire another team, which means 8 people in total. So we need to be able to calculate the number of people or teams needs but it will need to round the number in a set way depending on your business.
Rounding to Full Time Equivalents
MS Excel has a number of rounding functions. ROUND is fairly well known in that it takes a number with a decimal, and rounds it up if above 0.5 and down if below 0.5 .
However, for FTE we may decide that no matter what the decimal it must always round up. We would rather have excess capacity.
As shown below in B3, we have calculated how many hours a FTE can work. In B5 and B6 we have determined how much work we have in hours. In B5 (formula shown in C5) we have used a ROUNDUP to show how many people we need to complete this case load. The first part is just the calculation (hours per job x number of jobs / 1 FTE’s available hours). Normally in Excel this would give an answer below of 4.57 . The ROUNDUP function means that answer will be 5 even if the result was say 4.1.
If we have determined that we will always hire just below capacity (so everyone will need to work a bit harder), would could use the ROUNDDOWN function. In the example above, the answer would then be 4 instead of the 5.
Excel also has an MROUND function that allows you to round (using traditional rounding rules) in multiples. If you wanted to have rounding in multiples, but it must always go up, then there is a function called CEILING. If you want down, there is one called FLOOR.
Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.
Related
Rounding to numbers other than 1, 10, 100 etc