馃攳
馃敶How to make Payroll in Excel for beginners | Payroll Calculation as per Labour Laws - YouTube
Channel: unknown
[0]
Friends, Excel is a very powerful tool.
[2]
You can achieve a lot of tasks
through Excel, including payroll.
[5]
If you wish to make such complex payroll
via Excel, then keep on watching this video
[10]
In the next 10 minutes, I will show you that
[13]
How to make a payroll using excel
effortlessly that too in 5-10 minutes.
[17]
My name is Rishabh Jain. I鈥檓 a
third-generation labour law consultant.
[21]
We are working in this field for the past 42 years
[24]
We have delivered labor law and payroll
consulting services to more than 500 companies.
[28]
So let鈥檚 start without any delay
[30]
and learn how to prepare a basic payroll
using excel in just a few minutes.
[34]
[Intro music]
[42]
Make a basic payroll. First,
see fields in a basic payroll.
[45]
These are the basic fields in a basic payroll
[51]
You can write the name of the company,
address along with some other details,
[53]
for example, if you are consulting you can
write your name, the name of your company,
[57]
and your company鈥檚 website, etc.
The month also can be mentioned here.
[61]
Let's start with the first column. First,
you have to mention the serial number
[64]
If you want then you can also write
the code number of the employee here
[69]
Then You need to write the employee鈥檚 name
and employee鈥檚 father's name.
[72]
It is important to mention both because
[74]
If a company has multiple employees
sharing a name,
[76]
then they can be easily differentiated.
[78]
Then you need to mention
the rate of the salary.
[80]
Considering that a company has 3 bifurcations
Basic, DA, and HRA.
[84]
First, you need to mention
the Basic salary,
[87]
then you want to calculate
DA based on Basic.
[90]
Consider that it鈥檚 10% in your company,
then you need to multiply it by 10%
[94]
and you will get the amount of DA.
[97]
Similarly, if you want to calculate HRA,
for example, if HRA in your company is
[101]
30% of the sum Basic+ DA,
first, we need to add Basic and DA
[106]
Basic+ DA and
then multiply it by 30%
[113]
Here we have calculated
Basic and DA
[116]
Using the double click,
I have dragged the cursor,
[121]
by doing this we will get
all the amounts (Basic, DA, and HRA)
[123]
For calculating the total,
I have used the sum formula.
[126]
I鈥檒l show the formula again.
[128]
You have to use the sum formula,
the sum of all three components.
[133]
This will give us the total rate.
We can drag the cursor
[138]
and apply it to all employees.
[140]
Now we have the attendance
where we have present days, paid leaves,
[143]
weekly off, festivals and we can
calculate the total paydays
[148]
We need to pay attention
the month of march has 31 days
[152]
the paydays can鈥檛 be
more than 31 days.
[155]
Sometimes we do commit this
mistake in basics
[158]
We will add all the components present
days, paid leaves, weekly offs, and festivals
[161]
using the sum formula, here
we have calculated the paydays
[166]
Once we get paydays from
the HR Department or attendance machine
[170]
then we can easily calculate
Basic, DA, and HRA
[172]
first, we will calculate Basic
based on days.
[175]
We have to multiple Basic by
18, number of days
[184]
divided 31 because
March has 31 days.
[187]
This calculation gives us
the Basic of the employee.
[189]
Similarly, we will calculate DA by
multiplying DA rate by 18 divided by 31
[197]
By using the same formula,
we can also calculate HRA very easily.
[200]
HRA multiplied by the total
days divided by 31.
[206]
We have calculated Basic, DA
and HRA using this formula.
[209]
Now we will drag the cursor so that the
the same formula can be applied to each employee.
[215]
if there are some incentives this month
from sales, I will manually add them.
[220]
After manually adding the Incentives,
I will calculate the total earned salary.
[225]
I will use the sum formula. Write sum
and add these 4 columns, this gives the sum.
[234]
Here I have calculated the salary
of this particular employee
[236]
who has earned 22,000 and the
another employee has earned 34,000.
[240]
By following this formula we
can easily calculate the
[243]
salaries of all the employees.
[244]
now we will focus on deductions.
As of now, we know
[246]
Deductions on ESI is 0.75%
[249]
ESI is deducted on all the components
Basic, HRA, DA, and incentive.
[253]
So, I will calculate it
on the total amount.
[256]
We have to multiply the total by 0.75%. With this we have
[259]
calculated the ESI amount.
[263]
We have to take care that ESI
is not deducted for all employees.
[266]
It is only deducted for those employees
whose salaries are less than 21,000.
[269]
To find the employees who have
salary less than 21,000 we will
[271]
hide the middle columns so that
it is more clear for you.
[275]
we won鈥檛 deduct ESI for those
Employees whose total salaries are
[278]
more than 21,000 if it has
never been deducted before.
[281]
Considering that for these employees
ESI has never been deducted,
[283]
so I will delete their amounts. I am also
deleting the amount for these three employees
[286]
because ESI shouldn鈥檛 be deducted.
Here I have corrected.
[288]
So after calculating the ESI,
we will calculate the deduction for PF
[293]
I will first unhide the columns
that I had hidden.
[295]
Now we know that PF
is deducted on Basic and DA.
[300]
We will not deduct PF
on HRA and Incentive
[303]
First, we will add Basic
and DA, Basic+ DA
[309]
Then I will multiply
it by 12%
[315]
because 12% is
PF鈥檚 deduction
[317]
With this we have
calculated PF鈥檚 deduction
[321]
If an employee has taken some
advance, his TDS will be deducted
[325]
let鈥檚 assume it鈥檚 labor welfare fund,
So we can fill its value here
[328]
professional tax can also
be entered here
[330]
once after calculating all deductions,
we will calculate the total deduction
[335]
Here I will again use
the sum formula
[337]
We will add all the columns,
starting from R to W
[343]
after this calculation, we have
to drag the cursor
[346]
to get the calculation for all employees
[349]
After getting the total earned
salary and total deduction
[353]
With this information, we can
easily calculate net salary
[356]
We will subtract total deductions
from total earned salary
[362]
With this calculation, we will get
the net salary in hand for all employees
[369]
By watching this video, you will
not understand the details of payroll
[373]
you will only understand
the basics
[375]
To understand payroll in detail,
it will need handwork on your behalf
[377]
It took us 42 years
to master payroll
[380]
All the knowledge that we
have gained in 42 years,
[383]
I have summarised all that
in one course for you
[385]
if you are in the field of payroll
or want to make a career in payroll
[388]
or as a payroll consultant want to
generate monthly recurring income
[394]
I suggest you enroll in
A-Z payroll course
[398]
I will teach you complete
details of payroll in this course
[401]
Overall 45 lectures and
more than 100 modules
[404]
I will teach you all the details,
from basic to advance
[408]
even if you belong from any background
or profession be it science, commerce, or arts
[413]
you can easily learn this
because it doesn鈥檛 have complex mathematics
[416]
and only requires basic aptitude.
In this, I will teach you
[419]
what is payroll?
how to see allowances
[423]
what are different
formulas of payroll,
[425]
We will learn about all the
act calculations and its exceptions
[427]
and were will make mistakes
[429]
like ESI, PF, Bonus, Gratuity, labour
welfare fund, professional tax, TDS, etc
[435]
The most important part, of this
course how to compile practical payroll
[440]
and calculate it in excel,
with actual examples from companies
[444]
I will show how to calculate
payroll for the entire year
[446]
I will give some homework as well
and excel sheets for calculation
[450]
After this course, if you are
working in the payroll field
[453]
then you will be entitled
to promotion
[456]
if you are planning to start payroll
consulting you can easily start it
[460]
if you are a business owner then keeping
in mind complete government compliances
[463]
you calculate your payroll
[464]
if you are using a software
or making it on excel
[468]
After doing this course you
will develop a complete understanding
[472]
This course has one more USP,
it has a discussion forum
[476]
People have posted zillion doubts
and I have answered each one of them
[479]
if you have any doubt, post it
here, I will answer it immediately
[485]
You can also check other people鈥檚
queries and increase your knowledge
[491]
The link to this course is
mentioned in the description box
[494]
If you are anxious
because of excel, don鈥檛 worry
[497]
We have course names
introduction to excel
[500]
wherein 18 modules we have covered
all the basics in the Hindi language
[504]
after completing this course
[506]
your payroll course and
job life鈥檚 excel requirements
[510]
will be completed
[512]
where basic functions
and formulas
[516]
and pivot table details
will be taught
[517]
we will also provide practical
examples, worksheets, and homework
[521]
I hope you guys
liked this episode
[524]
Stay connected to our channel
[526]
where we will bring informatively
content related to labor law and finance
[530]
That鈥檚 all for this video
[531]
Give me permission
to take your leave
[532]
Jai Hind! Jai Bharat!
Most Recent Videos:
You can go back to the homepage right here: Homepage