Payroll Excel Sheet Download ((LINK)) India
Fajar Roux
Running payroll is a lot of hassle. If you are just starting out and your small business has less than five people, then you can use excel for it. MS Excel is one of the most loved applications. It has a multitude of use cases that can be carried out using its formulae and functions.
..and then comes payroll in Excel to the rescue. As I said, data manipulation is quite simple with this tool. Even formulas can be used as inputs for spreadsheets. What this does is it makes it effortless to insert numbers into specific locations on the spreadsheet itself. In addition, Excel will perform the necessary calculations automatically. As a result, you will end up putting absolutely no effort in obtaining payroll results. So not only are you saving tons of your valuable business time but also reducing the work you put in for this monthly task.
The majority of the people believe in saving their file after completing work on it. While it is okay to do this for most file types, do not take the risk when it comes to payroll information. Do you want to spend hours creating a comprehensive sheet only to lose it at the very last minute? No.
Maintaining comprehensive excel sheets manually requires a lot of management. As a result, even a small adjustment in the formulas can cause serious problems if it is used to run Payroll or store employee data, attendance, leaves, investment declarations, deductions, etc. Using complex formulas can lead to incorrect calculations and payment errors. In general, the entire process can become repetitive, tedious, and unproductive.
The template consists of the following sheets:
Setup - all the business & payroll settings for the template needs to be included on this sheet. This includes the business details, tax year dates, income tax rates, medical tax credit rates, list of earnings, list of salary deductions, list of company contributions and the list of departments. A column & row matrix which highlights incorrect column or row counts is also included at the bottom of the sheet.
Emp - add a unique employee code for each employee and enter data into all the employee information columns (columns B to K). Each employee needs to be linked to an income tax table and income tax rebate code which is used in the automated income tax calculations. The number of medical aid members is used in the automated medical tax credit calculations. The basic monthly salaries, annual bonus and salary increase amounts are used to automate the earnings calculations on the Payroll sheet. The deduction rate columns on this sheet can be used to override the rates on the Setup sheet for a particular employee. There is no limit on the number of employees that can be added to the template but the template has been designed for businesses with 50 or less monthly paid employees and due to the complexity of the calculations, the calculation speed of the template could slow down considerably if more than 50 employees are added.
Payroll - all the calculations on this sheet are automated based on the data which is entered on the Setup, Emp and Override sheets. All you need to do is to ensure that the Excel table on this sheet contains sufficient rows to accommodate all the employees that you have added to the Emp sheet. The Sheet Status at the top of the sheet will be highlighted in red if you need to add additional rows to the table.
Override - override any of the automatically calculated earnings, income tax, medical tax credits, salary deductions or company contributions values for any employee by adding the appropriate values to this sheet. You can override values for a single month or you can set the override end date in order to override values for multiple months or until the end of the tax year.
PaySlip - this sheet contains an automated monthly pay slip. All the calculations on this sheet are automated and you only need to select the appropriate pay slip number in cell G3 in order to view the appropriate pay slip.
Summary - this sheet contains a summary of all the monthly payroll data on the Payroll sheet. The sheet requires no user input and the data can even be filtered by department or individual employee by selecting the appropriate entries from the yellow cells at the top of the sheet.
MonthEmp - this sheet contains a monthly summary of payroll data by employee. All the calculations are based on the Payroll sheet and the sheet requires no user input. The appropriate measurement on which the calculations should be based can be selected from the yellow cell at the top of the sheet. Available measurements include gross pay, income tax, total deductions, net pay, total company contributions, total deductions & company contributions and total cost to company.
MonthDept - this sheet contains a monthly summary of payroll data by department. All the calculations are based on the Payroll sheet and the sheet requires no user input. The appropriate measurement on which the calculations should be based can be selected from the yellow cell at the top of the sheet. Available measurements include gross pay, income tax, total deductions, net pay, total company contributions, total deductions & company contributions and total cost to company.
You also need to set the salary payment day, annual bonus month and salary increase month. The salary payment day is used as the payroll run date on the Payroll sheet and can be any day of the month. If pay runs are not always scheduled on the same day, you can set the day of the month which is used most frequently. If you select 31, the payment run date will be set to the last day of each calendar month.
Note: The tables and lists on the Setup sheet need to be customized based on your requirements (if necessary) before you start using the data on the Payroll sheet. Once the first pay period has been paid, you can still add new items to the lists but you should not change any of the rates that are included on the Setup sheet. If you do change any of the rates, it will also affect past pay periods which would then create differences between the payroll calculations and the amounts that have been paid for the elapsed months. If any of the rates on the Setup sheet need to be amended during the tax year, you need to use the Override feature to affect the required changes.
An earnings code needs to be created for each type of earning which is paid to employees. These earnings codes need to be maintained in the Earnings list on the Setup sheet. The earnings list includes 5 user input fields - the following information is required in each of these fields for each type of earning:
Code - you need to enter a code for each type of earning. We highly recommend sticking to the default template convention of using a single letter to represent each type of earning. Once you have linked an earning to a specific letter and you have completed your first payroll period, you should not change the letter.
Description - enter a description for each earning. The earning description will be included on all employee pay slips.
Short Name - enter a short name for the earning. The short name should not be more than 15 characters in length and is used as a column heading above the earnings columns that are included on the other sheets in the template. Use a short name that will make it easy to identify the type of earning that it refers to.
Taxable % - if the earning type is not fully taxable, enter the percentage of the earning which is taxable. The default taxable % is 100% - if the earning is fully taxable, you do not need to enter anything.
Basis - select whether the earning is payable on a monthly or annual basis. This selection is important especially for income tax calculation purposes. If no value is selected, the earning basis will be deemed to be monthly.
Employees can be linked to departments in order to calculate departmental payroll costs which can be used to allocate payroll entries for accounting purposes. The department code is linked to individual employees in column C on the Emp sheet and only the department codes which have been included in the Departments list on the Setup sheet will be available for selection. The following information is required in the department list:
Code - enter a unique code for each department which will make it easy to identify the appropriate department and to distinguish between different departments.
Description - enter a description for each department.
Cost Centre - enter the general ledger code which should be used for allocation of costs to departments.
Note: A summary of payroll costs by department is included on the MonthDept sheet. All the calculations that are included on this sheet are automated and the only user input which is required on this sheet is selecting the appropriate payroll cost measurement at the top of the sheet.
All employees need to be added to the Emp sheet by adding a unique employee number in column A and entering employee data into all the user input columns on this sheet. The following columns require user input data:
Employee Number - enter a unique employee number for each individual employee. We recommend using employee numbers which consist of a combination of letters and numbers.
Employee Name - enter the name of the employee. You can use full names or surname & initials. The name as entered in this field will be included on the other sheets in the template including on the pay slip.
Dept - select the appropriate department. Only departments which have been added to the department list on the Setup sheet will be available for selection.
Date Employed - enter the date on which the employee commenced employment.
1st Payment Date - enter the date of the first payroll run which the employee should be included in. If the employee is added to a payroll run in any month which falls before the first day of the month in which this date falls, all payroll amounts will be set to nil values. The number of pay periods is also determined based on this date and an incorrect date can therefore lead to calculation inaccuracies. It is therefore imperative that the first payment dates of all employees are recorded accurately.
Date Terminated - if the employee has left the employment of the company, enter the date of termination in this column. The employee will only be able to receive payment on a payroll run date up to the end of the month in which this date falls.
Pay Period Override - select the "Yes" option if the employee pay periods need to be replaced by the business pay periods. This setting only has an effect if an employee has joined the company during the current tax year. See below for an example.
Job Title - enter the employee job title (included on pay slip).
ID Number - enter the employee identification or ID number (included on pay slip).
Income Tax Number - enter the employee income tax number (included on pay slip).
Residential Address - enter the residential address of the employee (included on pay slip).
IT Rebate Code - enter the appropriate income tax rebate code for the employee. Refer to the income tax rebate list on the Setup sheet for the available rebate codes. It is important that the correct rebate codes are entered for each employee otherwise their income tax calculations may not be accurate.
Income Tax Rate - select the appropriate income tax table code for the employee. There are two income tax tables on the Setup sheet - select A for the first table and B for the second table. You can also enter a percentage in this field if the income tax calculations for the employee need to be based on a fixed income tax percentage.
Medical Aid Members - enter the number of medical aid members. The medical tax credits are based on the number of medical aid members and the rates that are specified in the Medical Tax Credits table on the Setup sheet.
Basic Monthly Salary - enter the monthly basic salary of each employee in this column. The values in this column are included in the basic salary column (earnings code A) on the Payroll sheet for all months in which the employee is active. If the employee's basic salary changes (aside from the one salary increase month which is provided for), you should not change the value in this column but add an override entry to the Override sheet for the remaining pay periods. If you change the amount in this column, it will also change payroll data for past pay periods.
Annual Bonus Amount - enter the annual bonus amount in this column. The amounts entered in this column are included in the annual bonus column (earnings code C) on the Payroll sheet based on the annual bonus month selection which is specified on the Setup sheet.
One Increase Amount - enter the adjusted monthly salary amounts after the annual salary increase in this column. Note that it is not the amount of the increase which needs to be entered, it is the full adjusted salary amount. The basic monthly salaries (earnings code A) in all remaining pay periods will be adjusted to the amounts specified in this column and the annual salary increase month is set on the Setup sheet. If no annual salary increases need to be applied during the course of the tax year, you do not need to enter any values in this column.
Deduction Rates - this section includes 4 columns, one for each of the 4 default items that are included in the Salary Deductions List on the Setup sheet. The rate field on the Setup sheet which is set up on a business level can be overridden by entering a rate for a particular employee in the appropriate salary deduction column in this section of the Emp sheet. The salary deduction code of each column is included above the column heading.
Period Count - this column contains a formula which displays the number of pay periods which have been added to the Payroll sheet for the particular employee. All of the values in this column should be 12 - if the value for any employee is less than 12, it indicates that you need to add additional rows to the table on the Payroll sheet.