Salary Sheet With Attendance Template is a ready-to-use in Excel, Google Sheets, and Open Office Calcpayroll sheet to maintain records of attendance and calculate the salaries of 40 employees.
Table of Contents
What is Salary Sheet?
Salary Sheet Excel Template is a payroll document in which you can record payroll data for multiple employees along with Salary slip.
Components of Salary
There are three major components of the Salary: Basic Pay, Allowances, and Deductions.
Basic Salary
Basic Salary is fix amount that an employer pays in addition to allowances and subtraction of any deductions. It does not include bonuses, overtime, dearness allowance, etc. Usually, the basic salary is 40% or 50% of the CTC. Basic Salary is proportionate to the working days.
Allowances
Allowances are the financial benefits an employer provides above the Basic Salary to his employees. These allowances differ based on company policy, industry, job profile, and department.
- Dearness Allowance
- House Rent Allowance
- Conveyance Allowance
- Medical Allowance
- Special Allowance
- Bonus Pay
- TA – Travel Allowance
- Child Education Allowance
Deductions
Salary deductions are the amounts that an employer subtracts from an employee’s gross pay to reach net pay. These deductions include Employee Provident fund, Salary Advance, professional tax, TDS, etc.
What is the Attendance Sheet?
Employee Attendance Sheet is a document that records the presence, absence, sick leave, etc of employees for payroll or salary purposes.
Types of Leaves
Generally, there are 8 types of leaves as follows:
Holidays
Holidays are the leaves for which an employer doesn’t deduct any amount from his/her salary. It includes national holidays, weekly day-offs, festive holidays.
Earned Leave or Privilege Leave
Earned leaves are mandatory yearly leave than an employee earns while working. A specific number of days are allowed depending on the state and act the companies follow.
Employee avails such leave without any salary deductions. An employee has to obtain prior approval from the employer. In some companies as per the employment contract, employees can accumulate and encash money against accumulated/unused Earned Leaves.
Casual Leave
An employee can avail Casual leave or CL for short durations with prior intimation to the employer. Similar to the earned leave, these leaves also differ according to the industry and state.
Sick Leave or Medical Leave
An employer allows a stipulated number of Sick leaves per year. Usually, it is 12 days per year that is one SL per month. In sick leave, it is not mandatory for an employee without any prior intimation. It is advisable to inform the employer.
Maternity Leave
Maternity leaves are especially available for those women employees who plan to deliver a baby. Usually, maternity leave is 12-26 weeks. Moreover, a female employee can extend an additional unpaid leave of 16 months.
Half Pay Leave
Half Pay leaves are usually available only to government employees who completed one year of service. An employee avails half day salary during such leaves.
Quarantine Leave
Quarantine Leaves are those an employer grants to an employee only if the employee or his family member suffers a hazardous infectious disease.
Study Leave or Sabbatical Leave
: An employer grants study or sabbatical leave to the employee to update his/her knowledge and experience. Such an employee needs to rejoin the company and use learned skills.
Salary Sheet With Attendance Template
After the success of our previous templates viz: salary sheet and attendance sheet, we received ample of requests from our readers to combine both in one sheet.
This sheet will ease your payroll calculation and make it a simple and quick process. It is now just 3 step process: update attendance regularly, enter Salary Calculations and print Salary Slips. That’s It. Your work is all done. All the files are interlinked with each other. No repetitive tasks.
We have created a Salary Sheet With Attendance Register Template with predefined formulas for easy calculations.
Excel Google Sheets Open Office Calc
Click here to Download All HR & Payroll Excel Templates for ₹299.
Important Note: To edit and customize the Google Sheet, save the file on your Google Drive by using the “Make a Copy” option from the File menu.
You can download other HR and Payroll Templates like Employee Evaluation Template, Weekly Timesheet, Job Candidate Tracker, and much more.
Let us discuss how to efficiently and easily use this template.
Note: After you download the template, don’t forget to enable the content in the security warning pop-up below the ribbon as shown in the images below:
How to use Salary Sheet With Attendance Template?
This template consists of 4 sheets: Employee Attendance Sheet, Database Sheet, Consolidation Sheet, and Salary Slip.
Employee Attendance Sheet
In the header section, Enter the month for which you want to prepare the Attendance. You have to enter only the first date in the sheet and it will automatically display dates of the rest of the month.
Similarly, when you will enter the date, it will also automatically display the name of the day of the week using Text Function. Then you need to record the names of your employees with their employee id and their designation.
Secondly, select presence(P), leave(L) or holiday(H) from the drop-down list as shown in the figure below:
If you select P the cell will be filled with green color and when you select L it will fill Red color. For H it will fill a dark blue color.
Usually, Sunday is a Holiday. Some companies also mark Saturdays and Sundays as holidays. So need can choose H for those days.
If there are other National, Company, or Religious Holidays you can also mark them with H. For example, I have marked 25th May as an extra Holiday. At the end of the sheet, you can see the total number of employees present on that particular day.
Lastly, an attendance summary has been given on the right. It will display the Total number of days in the month, Total Presence, Total Leaves, Total Working Days, Total Worked Days, Total Allowed Leaves, and Toal Day off in the month.
Database Sheet
All the data entered in the Attendance sheet will automatically display like Names of employees, designation, and attendance summary. Here you will have to select month and year from the drop-down list. All the cells below will display the same.
Then comes the Salary Calculations. In this section, you need to enter the CTC amount of the employee and it will automatically calculate the salary as per the given formula. If you have different calculations you can either change the formulas or you can directly enter the amounts as required.
Both Gross Salary and Deductions will display the total and finally the take-home amount that is the Net Payable Salary.
Furthermore, you can provide additional information to be displayed in Salary slips at the end like gender, prefix, PF applicability, medical, signatory, etc.
Consolidation Sheet
The consolidation sheet will display the data payroll that you have entered in the database sheet. This sheet is only for approval from higher authorities. Just the replica of your payroll calculations with the approval section.
Salary Slip
Payslips or Salary slips are an important part of the Payroll process. Salary slip consists of all earnings (fixed and variable) and deductions (fixed and variable) along with the mandatory deductions.
On the right-hand side just select the name of the desired employee and it will automatically display the detail of that employee. Vlookup has been used here to fetch data from the database sheet.
Once you are done with that just click on the Print button to print the Salary Slip of your employees. The salary slip will be displayed as shown below:
That’s all you need to do to prepare the salary of your colleagues or employees. Enjoy using this simple template to manage the attendance and prepare the salary.
We have tried our best to avoid any mistakes in calculations. If you find any just let us know so that it can be rectified.
In addition to the above, if you want another salary slip formats you can check out the below article:
9 Ready-To-Use Salary Slip Templates In Excel
We thank our readers for liking, sharing, and following us on different social media platforms.
If you have any queries please share in the comment section below. I will be more than happy to assist you.
Frequently Asked Questions
How many sick leaves can an employee avail in a month?
Usually, an employee can take 12 sick leave in a calendar as per the shops and establishment act. Moreover, such leaves rules are regulated as per the concerned State’s Shops and Establishment Act or Factories Act.
How to calculate annual leave as per Indian Labour Law?
An employee has a minimum annual leave of three times his working days. For example, if an employee works 6 days a week he is entitled to annual leave of 18 days each year. That is 6 x 3 = 18.
How to calculate leave pay?
Leave encashment regulations are different from company to company. Generally, the amount is calculated on Basic Pay. Divide the monthly Basic day to per day basic amount and then multiply it by the number of paid days to be encashed.
How many days of leave can be availed for marriage in India?
In general, full-time employees are entitled to 3 days of leave for marriage. It is only applicable for the first legal marriage and mandatory to take within the first six months from the date of marriage.
How many hours an employer can make an employee work continuously?
According to the Minimum Wages Act, for an adult worker, the working day must be set in such a way that it should not exceed 12 hours inclusive of 1-hour of rest.
How to calculate OT Pay?
According to the Minimum Wages Act, divide the wage rate by 26 to calculate per day salary that is for 8 hours. Furthermore, divide it by 8 to calculate the per hour salary. Multiply it with 2 or 1.5 and then multiply it with the number of overtime hours worked.
What is the rule of notice period?
The minimum notice period as per the law is one week for every year worked. The rule for notice is one pay period. Thus, one week if the employee is paid weekly and one month if paid monthly.
What type of deductions can an employer make your salary?
An employer can deduct the following:
- Company fines as per the company rules and regulations.
- Pay for leave of absence.
- Deduction against damage or loss of goods entrusted.
- House Rent in case the accommodation is provided by the company.
- Deduction against additional amenities and service provided by the employer or contract.
- Deduction for recovery of advances and interest, and adjustment of overpayment;
- Recovery of loans lent by the company.
- Income tax deduction in the form of TDS.
- Deduction against orders of a court or other legal authority.
- Repayment of advance from PF.
- Payments to outer cooperative societies if it is agreed between the employer and the employee.
- LIC premium against authorization by the employee.
- Post Office Saving Schemes against authorization.
