Excel Formula Sheet
I am attaching herewith the excel formula sheet. Please go through and let me know your valuable inputs.
Function keys in Microsoft Excel | ||||||
Function Key | SHIFT | CTRL | ALT | CTRL+SHIFT | ALT+SHIFT | |
F1 | Display Help or the Office Assistant | What’s This? | Insert a chart sheet | Insert a new worksheet | ||
F2 | Edit the active cell | Edit a cell comment | Save As command | Save command | ||
F3 | Paste a name into a formula | Paste a function into a formula | Define a name | Create names by using row and column labels | ||
F4 | Repeat the last action | Repeat the last Find (Find Next) | Close the window | Exit | ||
F5 | Go To | Display the Find dialog box | Restore the window size | |||
F6 | Move to the next pane | Move to the previous pane | Move to the next workbook window | Move to the previous workbook window | ||
F7 | Spelling command | Move the window | ||||
F8 | Extend a selection | Add to the selection | Resize the window | Display the Macro dialog box | ||
F9 | Calculate all sheets in all open workbooks | Calculate the active worksheet | Minimize the workbook | |||
F10 | Make the menu bar active | Display a shortcut menu (right click) | Maximize or restore the workbook window | |||
F11 | Create a chart | Insert a new worksheet | Insert a Microsoft Excel 4.0 macro sheet | Display Visual Basic Editor | ||
F12 | Save As command | Save command | Open command | Print command |
Payroll Spread Sheet
I have Attached G-Payroll Spreadh Sheet
Mango’s payroll tool |
|
Back to intro | |||||||||||||
Insert your tax band information in the yellow cells below | |||||||||||||||
(The example data entered is for Uganda) | |||||||||||||||
Bands | Rate | Bands | Rate | Additional | |||||||||||
From | To | % | – | % | |||||||||||
0 | A | 0% | A | 130,000 | 10% | W | |||||||||
A | B | W | B | 235,000 | 20% | X | 10,500 | ||||||||
B | C | X | C | 410,000 | 30% | Y | 45,500 | ||||||||
C | D | Y | D | Z | |||||||||||
D | Gross | X | |||||||||||||
Monthly gross pay | 1,500,000 | N | |||||||||||||
% | |||||||||||||||
Employees Social security | 5 | Ees | (If the Employees’ contribution is a fixed amount, write the figure in D14 and adjust the formula) | ||||||||||||
Employers Social security | 10 | Ers | (If the Employers’ contribution is a fixed amount, write the figure in D14 and adjust the formula) | ||||||||||||
PAYE Formula: | =(VLOOKUP(N,Tax,2)*(N-VLOOKUP(N,Tax,1)))+VLOOKUP(N,Tax,4) | (‘Tax’ in this formula refers to table E5:H10) | |||||||||||||
Net pay formula: | N-PAYE-(N*Ees%) | ||||||||||||||
Test calculation: | |||||||||||||||
1. Fill in all the yellow cells with sample figures | |||||||||||||||
2. Check if the blue figures below are as you expect | |||||||||||||||
PAYE: | 372,500 | ||||||||||||||
Net pay: | 1,052,500 | ||||||||||||||
3. If it is correct as you expect, you can proceed to use or adapt the following payroll page | |||||||||||||||
4. If it is not correct, you may need to amend the formula for your particular country. Kindly notify Mango. |
Calculating Employee Attrition
I have Attached Sample Attrition Calculation sheet
B | Apr-09 | May-09 | Jun-09 | Jul-09 | Aug-09 | Sep-09 | Oct-09 | Nov-09 | Dec-09 | Jan-10 | Feb-10 | Mar-10 | |
No. of Left | No. of Left | No. of Left | No. of Left | No. of Left | No. of Left | No. of Left | No. of Left | No. of Left | No. of Left | No. of Left | No. of Left | ||
Manager | 0 | 0 | 0 | 1 | 0 | 1 | 0 | 0 | 0 | 1 | 0 | 0 | |
Staff | 1 | 0 | 0 | 0 | 2 | 0 | 2 | 0 | 2 | 0 | 5 | 0 | |
Trainees | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | |
Total left | 1 | 0 | 1 | 1 | 2 | 1 | 2 | 0 | 2 | 1 | 5 | 1 | |
Average Headcount | 35 | 39 | 41 | 40 | 41 | 41 | 41 | 41 | 41 | 42 | 43 | 44 | |
ATTRITION (%) | 2.86% | 0.00% | 2.44% | 2.50% | 4.88% | 2.44% | 4.88% | 0.00% | 4.88% | 2.38% | 11.63% | 2.27% | |
Month wise Attrition Data- B | |||||||||||||
Particulars | Apr-09 | May-09 | Jun-09 | Jul-09 | Aug-09 | Sep-09 | Oct-09 | Nov-09 | Dec-09 | Jan-10 | Feb-10 | Mar-10 | Total |
Average Headcount | 35 | 39 | 41 | 40 | 41 | 41 | 41 | 41 | 41 | 42 | 43 | 44 | 41 |
Attrition (No) | 1 | 0 | 1 | 1 | 2 | 1 | 2 | 0 | 2 | 1 | 5 | 1 | 1 |
Attrition (%) | 2.86% | 0.00% | 2.44% | 2.50% | 4.88% | 2.44% | 4.88% | 0.00% | 4.88% | 2.38% | 11.63% | 2.27% | 2.4% |
Categories: HR Tags: Attrition, Calculation, Sample, Sheet
Employee Performance Evaluation Sheet
Herewith i have attach Employee Performance Evaluation sheet which may be helpful. Further you can add your points and criteria as per your companies requirement to evaluate employee performance.
Performance Measurement Sheet | |||
Job Title/Designation: | Administration and HR – Executive | Division/Department | Hr/Admin |
Salary Grade/Band: | G3 | Manager Name | UMR & JKS & SSG |
Name of Employee | Joining Date | 14/09/09 | |
Department | Review Period | 2009-10 | |
Purpose of Appraisal | Annual evaluation of current employees during the last 6 months | ||
Scoring System | |||
Attribute | Score | ||
Outstanding | 5 | ||
Exceeds Requirements | 4 | ||
Meets Requirements | 3 | ||
Need Improvement | 2 | ||
Unsatisfactory | 1 | ||
PART I | |||
FUNCTIONAL / JOB RELATED SKILLS | Max. Marks = 50 | ||
CRITERION | SCORE | SUB-TOTAL | |
Quality of Work | 11 | ||
Accuracy, neatness and timeliness of work | 4 | ||
Adherence to duties and procedures in Job Description and Work Instructions | 4 | ||
Synchronization with organizations/functional goals | 3 | ||
Work Habits | 17 | ||
Punctuality to workplace | 4 | ||
Attendance | 4 | ||
Doest the employee stay busy, look for things to do, takes initiatives at workplace | 5 | ||
Submits reports on time and meets deadlines | 4 | ||
Job Knowledge | 10 | ||
Skill and ability to perform job satisfactorily | 3 | ||
Shown interest in learning and improving | 4 | ||
Problem solving ability | 3 | ||
TOTAL | 38 | ||
Additional Comments | |||
PART II | |||
INTERPERSONAL SKILLS | Max. Marks = 25 | ||
CRITERION | SCORE | SUB-TOTAL | |
Interpersonal relations/ behaviour | 17 | ||
Responds and contributes to team efforts | 3 | ||
Responds positively to suggestions and instructions and criticism | 4 | ||
Keeps supervisor informed of all details | 3 | ||
Adapts well to changing circumstances | 3 | ||
Seeks feedback to improve | 4 | ||
TOTAL | 17 | ||
Additional Comments | |||
PART III | |||
LEADERSHIP | Max. Marks = 25 | ||
CRITERION | SCORE | SUB-TOTAL | |
Leadership | 19 | ||
Aspirant to climb up the ladder, accepts challenges, new responsibilities and roles | 8 | ||
Innovative thinking – contribution to organizations and functions and personal growth | 7 | ||
Work motivation | 4 | ||
TOTAL | 19 | ||
Additional Comments | |||
Total Overall Score : (Out of 100 points) | 74 | ||
OVERALL PROGRESS | |||
Employee performance and learning is unsatisfactory and is failing to improve at a satisfactory rate | |||
Employee performance and learning is acceptable and is improving at a satisfactory rate | |||
Employee has successfully demonstrated outstanding overall performance | |||
Additional Comments | |||
RECOMMENDATIONS | |||
Next Review Date | |||
FINAL COMMENTS | |||
Evaluator’s Name: | |||
Signature: | |||
Date: | |||
Director’s Name: | |||
Signature: | |||
Date: | |||
Click Here To Download Appraisal or Evaluation Sheet
Categories: HR Tags: Appraisal, Evaluation, or, Sheet
Employee Office in And Out Time Excel Sheet
I have Attached Employee Office in And Out Time Excel Sheet
Note – when employee come office, put cursor in related employee office in time cell and click on red “office in time button” when employee left office , put cursor in related employee office out time cell and click on blue “office out time button”
Sr No Name Of Employee Office In Time Office Out Time 1 PATEL JITENDRA 10:37:34 PM 10:39:09 PM 2 PATEL NIRAJ 10:43:39 PM 10:44:08 PM 3 RAVAL JAIVIK 10:47:56 PM 10:48:13 PM 4 SONI SACHIN 5 6 7
Click Here To Download Employee Office in And Out Time Excel Sheet
Candidate Short Listing Sample Sheet
I have attached sample format for selection panel members regarding the shortlisting exercise.
SHORTLISTING SHEET
Boutique De Emerald India limited |
||||||
POST APPLIED FOR |
||||||
Sr. No. | Date of receipt of application | Qualification of the candidate | Qualification demanded | Years of experience of the candidate in this field | Names | Marks (Basic=50,Additional Qualification=10, additional year of experience=10) |
Categories: HR Tags: Format, Sheet, Shortlisting
HR Audit Sheet or Audit Questions
Audits help determine the effectiveness of an HR department and/or HR systems. They are asystematic, objective tool to assess regulatory or policy compliance in the workplace.
HR Audit Questions
- What was total revenue for the business unit for the most recently completed fiscal year?
- What were total assets for the business unit for the most recently completed fiscal year?
- How many locations exist within the business unit? (Include all operating and administrative locations which perform HR activities.)
- Is the business unit a publicly-held or a privately-held entity?
- What was the pre-tax income for the business unit for the last fiscal year-end?
- What is the company business strategy?
- Does the company business strategy link to Human Resources (HR)? Please describe.
- What is your company’s HR Strategy? Is the HR strategy aligned with the company’s strategy?
- Who develop the HR Strategy? Do you assemble a cross-functional team to develop the HR strategy?
- What does HR do in supporting the implementation of organization’s strategy?
- Determine how the human resources department will support strategic goal and impact organization performance?
- Do you perform a gap analysis of current versus desired organizational behavior and performance, and develop strategy to close the gap?
- Outline the HR department’s present role, list its function and evaluate its effectiveness?
1. Please describe the Human Resources Policies of the company. If possible, please specify the policies for each HR function (recruitment & placement, training & development, performance appraisal, etc)
2. How and to what extent do the policies support the HR department in executing the strategy?
3. Please describe the Human Resources Program of your company.
4. Do these programs align with your Human Resources Policies?
1. Are your workforce formation fits the company’s business objectives?
2. Does the company have manpower plan?
3. How do you create a manpower plan?
4. Do you develop a competency-based approach to staffing?
5. Does every position have competency/requirement?
6. Do every employee understand competency/requirement needed to perform a job/position?
7. Does this company have a clear job description for each job/position? And does each have clear competency ‘map’?
8. What is the total number of all employees (headcount) at the business unit?
- managerial level :
- supervisory level :
- staff/operator level :
- total :
8. What is the total number of employees (headcount)for the human resources department?
number
- managerial
- supervisory
- staff
- total
1. Answer the following in relation to the job offer cycle:
a. What was the total number of offers made in most recent
complete fiscal year?
b. What was the total number of offers accepted in
most recent complete fiscal year?
c. What was the average time (in days) from
job requisition to extension of job offer?
2. Please complete the following question with the number of entry and non-entry level positions filled
internally and externally during the most recent complete fiscal year:
internal external
a. Entry
b. Non-entry
3. What were your total recruiting costs for internal external
the previous year?
4. How many positions (job titles) exist across the organization?
5. How is the recruitment process?
6. Does it eliminate unsuitable applicants early and focus on promising candidates ?
7. How is the recruitment methodology? Does it attract desirable workers? Does it match capabilities of candidates with competency requirement for positions?
8. Do you use executive search services to help you in the recruitment area?
9. How do you describe the qualification requirements for the vacant positions?
10. Who makes the hiring decision?
1. What kind of programs does exist for employee retention? Are they sufficient?
2. Does the company develop attractive benefit program that can retain employees?
3. Do you track your staff turnover? What is your turn over rate this fiscal year?
1. Does the company have a clear development or training plan/strategy ?
2. What is the current training and development system for employees?
3. What was the total number of training hours in the previous year for all employees?
Managerial :
Supervisory :
Staff :
4. Of the total amount of training hours offered, what percentage was:
Percentage
a. Formal classroom
b. Computer-based
c. On-the-job
d. Self-study
e. Off-site
5. What was the total cost for training in the previous year?
6. How do you conduct Training Need Analysis (TNA)?
7. How do you translate the TNA into a Training Program?
8. What kind of training programs offered to employees?
9. Do managers and employees find the training program relevant to their needs?
10. How do you evaluate training effectiveness?
11. Does each employees have Individual Development Plan(IDP)? How are individual development plans and needs identified?
12. Does IDP work effectively? If it doesn’t work effectively, what is the cause of it?
1. What types of performance management systems do you use? Please describe.
2. Does it work effectively? If not, why?
3. Do you give constructive feedback to increase employee’s performance?
4. Do you include a customer satisfaction element in performance evaluations?
1. What is the current compensation system in your company? Please describe
2. Do you offer compensation plans that provide a stable base with variables linked to performance?
3. Do you develop compensation plan that support strategic goals?
4. Who design the current compensation system? Does the BOD / HR Department involve in formulating pay strategy?
5. Is the compensation system reviewed periodically? How do you evaluate your pay structure and compensation policy at least annually and adjust it when needed?
6. Do you compare the company’s compensation programs with the market rates? Does the current compensation system competitive?
7. Do you set up procedures that ensure company compliance with all applicable payroll laws needed?
8. Please describe the current benefit and allowance system of your company? (describe in detail if there is differences among upper / middle / lower level of management / staff?)
9. For all employees in the organization (include both part-time and full-time employees in all departments), what is the:
a. Total base pay
b. Total overtime pay
c. Total variable pay
d. Total payroll cost
10. What is the total benefits cost (not including benefits administrations costs) for all employees in the organization? (Include both part- and full-time employees in all departments)?
1. What is the current system for Career Development in your company? Please describe.
2. Does it work based on meritocracy and fairness?
3. How does this current system apply to the employee individually?
4. What is the current system of succession planning?
5. Do succession plan exist for all critical positions?
6. What percentage of management positions at the following levels has succession plans in place?
percent
a. Executives
b. Senior mgt.
c. Middle mgt.
7. What are the criteria for the successors?
8. Does the succession planning system work effectively? If not, why?
1. Do you build a culture of inclusion that promotes labor and management as partner in business success? How?
2. What problems does the company encounter in labor relation issues? Do you have labor union? Does the labor union represent the employees well?
3. How do you solve labor relation (LR) issues?
4. Do employees give feedback to solve IR issues? Do you design grievance procedure for resolving employees problem?
5. Do the company actively communicate LR / IR procedure to all employees?
6. Do you encourage employees to play a proactive role in improving the labor-management relationship?
1. What IT systems and infrastructure do you use to support your HRIS? Does it work effectively?
2. How can the IT system be used to support the HR Strategy?
3. How many human resource information databases your company have?
1. What is the current retirement policy?
2. Does your company have Pre-Retirement Preparation Program for their employees?
3. Please indicate the number of employees participating in retirement plans.
Managerial :
Supervisory :
Staff :
4. Please indicate the total contribution (in terms of percentage of total compensation) into retirement plans as outlined below:
managerial supervisory staff
a. By employee
b. By the organization
1. Do you create an environment of open communication between employees and management.
2. How do managers and employees perceive the effectiveness and integrity of current communication culture?
3. Do you capture the creative insight of employees by soliciting their ideas for improvement?
4. Do you create environment to delegate decision making to the lowest level possible?
5. What kind of media your organization uses to communicate to the entire workforce on a regular basis?
6. What mechanisms do you have for obtaining employee feedback on a regular basis?
1. How much the cost for the following items:
- Human resources department direct labor cost (Wages, overtime, and benefits.)
- Human resources department operating expenses (Supplies, training, and other locally controllable expenses not including rent, depreciation, or allocated overhead expenses.)
- Outsourcing fees, if any (Contract fees including annual fees and monthly operating charges.)
- Contracted services cost (Temporary and contract labor).
- Data processing equipment and facilities to support the human resources department (Include the cost of providing computer processing, software, hardware, )
- Total human resources department cost (Total of questions 1 through 5)
Sample HR Audit Sheet
I have attached HR audit sheet in excel format.
HR AUDIT – Process Chart | |||||
Sr.No | Detail | Content | Obtain Marks | Total Marks | Remark |
1 | Appointment Letter | Designation | 2 | ||
Date of Joining | 2 | ||||
CTC | 2 | ||||
2 | Offer Letter | Date of Joining | 2 | ||
Offered Designation | 2 | ||||
3 | Interview Evaluation Sheet | Interview Panel | 2 | ||
Approval | 2 | ||||
CTC Offer | 2 | ||||
4 | Qualification certificate | Last Degree | 3 | ||
Certificate | 2 | ||||
Addi. Qualification | 2 | ||||
5 | Experience related certificate | Experience Letter | 2 | ||
Relieving Letter | 2 | ||||
Last Salary Slip | 3 | ||||
6 | Confirmation Letter(If Confirm Or Completed Six month) | Date of Confirmation | 5 | ||
7 | Salary Increment letter(If Applicable) | Approval for Increment | 5 | ||
8 | Office Order(If Applicable) | Office order | 2 | ||
9 | HRMS Entry | Personal Detail | 5 | ||
Professional Detail | 5 | ||||
Educational Detail | 5 | ||||
Leave Balance | 5 | ||||
10 | Attendance Process/ Register | Bio Matrix Attendance | 5 | ||
Register (On-Roll) | 2 | ||||
Register (Off-Roll) | 2 | ||||
11 | Leave Process & Record | Leave Form | 5 | ||
Approved Authority | 5 | ||||
Signature | 2 | ||||
Balance | 2 | ||||
12 | HR System At Unit Level | Actual HR System At Unit level | 3 | ||
Way of work for HR | 3 | ||||
Day to Day activity | 3 | ||||
13 | Salary Discrepancy | Last Salary Sheet | 2 | ||
Salary Discrepancy | 2 | ||||
Approval of HOD | 2 | ||||
Any other issue related to Salary | 2 | ||||
14 | Salary File | Month Wise | 5 | ||
All Documents as per check list | 5 | ||||
Company wise Salary File (i.e.NLL, CHHPL, TDPL) | 5 | ||||
15 | Meeting with HOD | Meeting with HOD during Morning Meeting in Unit. | 2 | ||
Take feedback and suggestion to improve HR System. | 2 | ||||
Give instruction to Follow HR Process. | 2 | ||||
Update HR System | 2 | ||||
16 | Observation | Work Process | 5 | ||
Work Environment | 5 | ||||
Attitude/Behavior of HOD | 5 | ||||
17 | MIS – HR | Employee Detail | 2 | ||
New Joinee Detail – Monthly | 5 | ||||
Left Emloyee Detail – Monthly | 5 | ||||
Full & Final Status of Left Employee | 5 | ||||
18 | Legal Compliance | ESIC Sub Code (If Applicable) | 5 | ||
ESIC No. of all employee | 3 | ||||
Form – 2 for PF Nomination | 3 | ||||
Form – F for Gratuity Nomination | 3 | ||||
Monthly ESIC Chalan Copy | 3 | ||||
Form – 5 & Form – 10 | 3 | ||||
All Required Register | 3 | ||||
Total |
Click Here To Download Hr Audit Sheet In Excel Format