hi guys and welcome to the hr metrics template in this short video i want to walk you through
the single steps that you need to take in order to customize the template to suit your needs so
let's take a quick look at the sheets that we have in this template we're starting off with
a mapping table this is always very common in all the templates that you will find
we have an employee database headcount overview sales revenue by employee and then last but not
least the payroll now let's take a quick look at the different sheets now the mapping is quite
easy first you want to make sure that the months align with the regional language settings of your
microsoft excel and windows computer as far as the years are concerned update them for your relevant
years because this is going to be very important later on as we will be using drop-down lists and
we want to ensure that we're pulling the right data for the right years in the employee database
in the employee database sheet you see the names of your employees the group that they belong to
the date of entry date of exit if applicable then the number of salary payments per year
and then also their base salary assuming a 100 workload if that were to change let's say for
some ftes or some employees just want to work 80 of the time then we can specify this in column g
now based on these inputs we will have column h which is going to have the monthly cross salary
considering the workload excluding commission and bonus speaking of commission and bonus you can
set an annual bonus target for these employees let's say six percent of the annual revenue now
why is this important later on the in the payroll calculation we will see that even though the bonus
is only paid out once per year we still need to accrue for this bonus target each and every month
but we'll see that in detail in just a second then last but not least for those associates
who work out in the field a sales commission may be applicable now let's move on to the next sheet
headcount overview essentially gives us an overview as to how many people work in the company
now let's take the first two people in here for each and every month that they have been working
with a company we just need to put in a one this is going to be super important later on because
as we calculate the monthly salary it'll actually check if this particular person was working for
the company during that time using a complex index function so you want to make sure that
this is always maintained but don't worry most of this is automated all you need to make sure
is that you enter the right numbers down here now the template comes with pre-populated data you see
that these all refer to the previous sheet and if you were to update one of these let's say
in here just putting test then you'll find that this will automatically translate
into the sheets afterwards now let's eliminate this this was just to show you that this works
once we've clarified the headcount overview we can move over to the sales revenue by employee now
some of the associates that we're tracking in this file are actually field based associates meaning
they're out there selling our products and because of that they are also entitled to a commission
fee now this commission fee is essentially dependent upon the sales that they generate
and you can use this template to track the revenue that each employee generates per month again this
will be important later on as we calculate the monthly salary once you have maintained
this sheet we can just move over to the next one called payroll and this is where it's now getting
interesting because on the one hand side we have the full breakdown of the monthly salary in here
and essentially the way these formulas are built now there's nothing you need to change about
them because once you've maintained the previous sheets this will all just come automatically but
the way these are built is that they will check for the employee database what's the base salary
they will check if this person has worked in the month for that
you know particular month that we want to look at it'll also take into consideration
the sales revenue by employee and it will use this as a calculation to
bring you the actual charge that you will encounter the actual salary that needs to be paid
on the payroll sheet we can have the full overview month by month or if we're more
interested in the details say we want to know what is the base salary what's the bonus accrual
and what's the sales commission that the person is entitled to we can just use these drop-down lists
right here let's say we want to look at august and then you'll see that these numbers will change
now of course the base salary and the bonus accrual will not change because these are always
fixed right however the commission is always dependent upon the sales revenue that is generated
each month now let's say if we were to change this to 2019 this would of course also work differently
good guys so this is it for the human resource metrics template i hope that you'll find this
useful it's extremely helpful whenever you want to calculate the salary of a person