Archives

Dashboard Series – Actual Vs Budget Chart

In our example we have the actual and budget values for a year. A positive scenario is when we are within budget and a negative scenario is when we are above budget.

  • Positive scenario: Green
  • Negative scenario: Red
  • Budget values : Light blue.

This chart can be used for situations like sales vs target, last month values vs current month values.

Download Chart_Actual_Budget

Dashboard Series – Attrition – Using Gauge Chart

In our example, we have the data for attrition percent for certain years.

The chart has a combo box which has all our available years in a drop down.

In our Gauge chart we have three sections denoting three levels of attrition, Low, Moderate, and High.

Upon selecting the required year, the attrition % for that year is denoted by an indicator. The indicator points to the appropriate section of the Gauge Chart.

 

 

 

 

 

 

 

 

 

 

Download Chart Attrition_Gauge Chart

 

Excel Macros Series 3: Create Pivot Charts to get employee count across locations via department.

In our below example we have a data of employee data dashboard across locations.

Here instead of using the conventional method for creating a Pivot Table, we use Excel VBA macro instead to represent our required dashboard.

This code can be used in combination with our previous excel macro series example.

Eg: Extract active employee data onto a separate sheet and then create a pivot table based on the results.

Also we have created a code which will delete the Pivot charts in the workbook at the click of a command button.

Download Code Macro_Pivot Table

Download-Excel-Macro-pivot


Sub Create_PivotTable()

    Dim pc As PivotCache
    Dim PT As PivotTable
    Dim pf As PivotField
        
        'wsdata is the name of the worksheet
    If ThisWorkbook.PivotCaches.Count = 0 Then
        Set pc = ThisWorkbook.PivotCaches.Create( _
            SourceType:=xlDatabase, _
            SourceData:=wsdata.Name & "!" & wsdata.Range("A1").CurrentRegion.Address, _
            Version:=xlPivotTableVersion15)
    Else
        Set pc = ThisWorkbook.PivotCaches(1)
    End If
    
    Worksheets.Add
    Range("A3").Select
    
    Set PT = pc.CreatePivotTable( _
        Tabledestination:=ActiveCell, _
        TableName:="dataPivot")
        
    ' Row parameter for pivot table
    Set pf = PT.PivotFields("Location")
    pf.Orientation = xlRowField
    
    ' Column parameter for pivot table

    Set pf = PT.PivotFields("Department")
    pf.Orientation = xlColumnField

    ' Sum /Average /COunt etc parameter for pivot table
    Set pf = PT.PivotFields("Name")
    pf.Orientation = xlDataField
    ' you can use functions like xlsum, xlaverage
    pf.Function = xlCount

End Sub

Sub DeleteAllPivotTablesInWorkbook()
Dim WS as Worksheet
        On Error Resume Next
    For Each WS In ActiveWorkbook.Worksheets
        For Each PT In WS.PivotTables
            WS.Range(PT.TableRange2.Address).Delete Shift:=xlUp
        Next PT
    Next WS
End Sub

 

 

Excel Macros Series 2: Calculate the total paid days from the attendance roster for a particular month

In our below example we have a data of attendance roster from 01 Jan 2018 to 31 Jan 2018.

The attendance roster has the below legends.

The macro first calculates the total number of P, A, WO, OD .

To arrive at paid days the macro uses the below formula.

E.g  For the month of April ( Total Month Days are 30)

Hence the sum of Present Days , Week Off, Outdoor and Absent should be equal to 30. If it is not equal to 30 then some value has been missed.

Some organisations have salary cuts for late marks if the employee does not have leave balance. In such case an additional parameter for late mark should be included in addition to ( P , WO , OD, A).

The macro further uses conditional formatting to highlight all the absent in the attendance roster in red color.

Some additional formatting like font, border, and interior color are applied to various cell ranges.

Download Code_Attendence Macro

Download Excel Macro Attendance


Sub calculate_attendance()
wsroster.Activate
Dim p_range As Range
Dim a_range As Range
Dim wo_range As Range
Dim od_range As Range
Dim cr_range As Range
Dim countrows As Integer
Dim first_counterp As Integer
Dim paid_range As Range

'' Count no of employee
countrows = Application.WorksheetFunction.CountA _
        (Range("A5", Range("A" & Rows.Count).End(xlUp)))

'' Main Range
Set cr_range = Range("g5", Range("g5").End(xlToRight))

' Criteria Count

Set p_range = Range("b5")
Set a_range = Range("c5")
Set wo_range = Range("d5")
Set od_range = Range("e5")
Set paid_range = Range("f5")

'COUNT PRESENT DAYS
first_counterp = 0

Do

'countif the criteria and publish results
p_range = Application.WorksheetFunction.CountIf(cr_range, "P")
a_range = Application.WorksheetFunction.CountIf(cr_range, "a")
wo_range = Application.WorksheetFunction.CountIf(cr_range, "wo")
od_range = Application.WorksheetFunction.CountIf(cr_range, "od")
paid_range.Value = (p_range.Value + a_range.Value + wo_range.Value + od_range.Value) - a_range.Value
paid_range.Interior.ColorIndex = 36
'increase the counter

first_counterp = first_counterp + 1

''set focus cell on the next criteria answer
Set p_range = p_range.Offset(1, 0)
Set a_range = a_range.Offset(1, 0)
Set wo_range = wo_range.Offset(1, 0)
Set od_range = od_range.Offset(1, 0)
Set paid_range = paid_range.Offset(1, 0)

'move to next record
Set cr_range = cr_range.Offset(1, 0)
Set cr_range = Range(cr_range, cr_range.End(xlToRight))
Loop While first_counterp <> countrows

'' format cells
Dim frm_range1 As Range
Dim frm_range2 As Range
Dim frm_range3 As Range
Dim cf_range1 As Range

Set frm_range1 = Range("A5", Range("A5").End(xlDown).End(xlToRight))

frm_range1.Borders(xlEdgeLeft).LineStyle = xlcontinous
frm_range1.Borders(xlEdgeRight).LineStyle = xlcontinous
frm_range1.Borders(xlEdgeTop).LineStyle = xlcontinous
frm_range1.Borders(xlEdgeBottom).LineStyle = xlcontinous
frm_range1.Borders(xlInsideHorizontal).LineStyle = xlDot
frm_range1.Borders(xlInsideVertical).LineStyle = xlDot

frm_range1.Borders(xlEdgeLeft).Weight = xlThick
frm_range1.Borders(xlEdgeRight).Weight = xlThick
frm_range1.Borders(xlEdgeTop).Weight = xlThick
frm_range1.Borders(xlEdgeBottom).Weight = xlThick
frm_range1.Borders(xlInsideHorizontal).Weight = xlThin
frm_range1.Borders(xlInsideVertical).Weight = xlThin

Set frm_range2 = Range("B4:E4")
frm_range2.Font.Bold = True
frm_range2.Borders.LineStyle = xlcontinous
frm_range2.Interior.ColorIndex = 40

Set frm_range3 = Range("G4", Range("g4").End(xlToRight))
frm_range3.Borders.LineStyle = xlcontinous
frm_range3.Interior.ColorIndex = 22
frm_range3.Font.Bold = True

Range("a4").Interior.ColorIndex = 50
Range("a4").Font.Bold = True

Range("f4").Interior.ColorIndex = 44
Range("f4").Font.Bold = True

Range("A1:B1").Font.Bold = True
Range("A1:B1").Borders.LineStyle = xlcontinous
Range("A1:B1").Borders.Weight = xlThick
Range("A1:B1").Interior.ColorIndex = 24

' Set conditional formatting

' Select Range
Set cf_range1 = Range("G5", Range("G5").End(xlDown).End(xlToRight))
cf_range1.Select

' Specify Parameter
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""A"""

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
  With Selection.FormatConditions(1).Font
        .Color = -16383844
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13551615
        .TintAndShade = 0
    End With
 Selection.FormatConditions(1).StopIfTrue = False

Range("A1").Select
End Sub

Sub clearatt()
[att_cal].ClearContents
End Sub



 

Excel Macros Series 1: Extract Active Employee Data & conditionally format extracted records

In our below example we have a data of employee records.

Our requirement.

From our worksheet emp_master datadet  we first find out the active employee data and then copy the active employee records onto worksheet display.

Then in our extracted records below conditional formatting is set.

  • In column DOJ all employee whose have joined in 2008 are highlighted.
  • In column Salary all employee whose have salary less than Rs 33000/- are highlighted.

Note : How to use Macros (Know More)

 

Download_Code_ExtractData_1

Download_Excel Macro extract data

 


Sub extract_emp_data()
Dim counttotrows As Integer
Dim status As String
Dim loopcounter As Integer
Dim actempcode As String

wsdisplay.Activate
Dim del_old_records As Range

Set del_old_records = Range("A2", Range("A2").End(xlDown).End(xlToRight))
del_old_records.Delete
Range("A2").Select

wsemp.Activate
counttotrows = Application.WorksheetFunction.CountA(Range("B3", Range("B" & Rows.Count).End(xlUp)))
Range("B3").Select
loopcounter = 0

Do
actempcode = Selection.Value
status = Selection.Offset(0, 1).Value

 If status = "Active" Then
 loopcounter = loopcounter + 1
 wsdisplay.Activate
    ActiveCell.Value = actempcode
    ActiveCell.Offset(0, 1).Value = Application.WorksheetFunction.VLookup(ActiveCell.Value, [empmaster], 3, False)
    ActiveCell.Offset(0, 2).Value = Application.WorksheetFunction.VLookup(ActiveCell.Value, [empmaster], 4, False)
    ActiveCell.Offset(0, 3).Value = Application.WorksheetFunction.VLookup(ActiveCell.Value, [empmaster], 5, False)
    ActiveCell.Offset(1, 0).Select
    wsemp.Activate
    ActiveCell.Offset(1, 0).Select
 Else
    loopcounter = loopcounter + 1
    wsemp.Activate
    ActiveCell.Offset(1, 0).Select
 End If
Loop While loopcounter <> counttotrows

   
' Set conditional formatting
wsdisplay.Activate
Dim cf_range As Range
Set cf_range = Range("D2", Range("D2").End(xlDown))
cf_range.Select

'' Specify Parameter
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="=33000"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    
    With Selection.FormatConditions(1).Font
        .Color = -16383844
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13551615
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
   
Dim cf_range2 As Range
Set cf_range2 = Range("C2", Range("C2").End(xlDown))
cf_range2.Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=YEAR($C2)=2008"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 5296274
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False

    Range("A1:D1").Interior.Color = vbCyan
    Range("A1:D1").Font.Bold = True
    Range("A2").CurrentRegion.EntireColumn.AutoFit
    Range("A2", Range("A2").End(xlDown).End(xlToRight)).HorizontalAlignment = xlCenter
    Range("A2", Range("A2").End(xlDown).End(xlToRight)).Borders.LineStyle = xlDash
    Range("A2", Range("A2").End(xlDown).End(xlToRight)).Borders.Weight = xlThin
    Range("C2", Range("C2").End(xlDown)).NumberFormat = "dd-mmm-yy;@"
    Range("D2", Range("D2").End(xlDown)).NumberFormat = "0"
    
wsemp.Activate
Range("B3").Select
wsdisplay.Activate
Range("A2").Select

MsgBox ("Active employee data extraction completed")

End Sub

 

Diversity and Inclusion

Most of us spend most of our daily time in our offices. Hence it is essential that the climate of the organization is such that an employee feels enriched. The organizational culture, the energy an employee feels makes him/her engaged with the organization. Hence an atmosphere where the employee feels unvalued, misunderstood, unsafe would result in dissatisfaction and unproductivity.

A diverse workplace is one which employs individuals irrespective of their age, gender, religion, ethnicity, national origin, socio-economic class etc. Every individuals has their own set of strengths, competencies and experiences.

Everyone needs and deserved a respectful, collaborative and encouraging workplace.

An inclusive organizations is the one which respects each individual’s diversity and leverages those strengths and unique traits to create an enabling and enriching work environment. Inclusion means not only respecting and valuing individuals but also involving them in decision making and key projects. Effective inclusion will bring a sense of belonginess among employees for the organization.

The benefits of diversity can be achieved only when we respect and value each individual irrespective of their background.

Diversity and Inclusion are unique and inter-related concepts.

 

Diversity will enable you to have workforce numbers who are diverse in race, ethnicity, religion, age, gender, culture, disability, etc. However the true value to those numbers is enabled by inclusion.

In conclusion ,diversity is the mix of people you have and inclusion is what you do with the people you have. So diversity is futile without inclusion. 

Introduction – Variance Analysis

Variance analysis refers to a difference between two values. The deviation may favorable or unfavorable as under:

Variance is generally calculated as per below parameters

The time period for measuring deviation can be as under:

  • Current Month vs Previous Month
  • Current Year vs Previous Year
  • First Quarter vs Second Quarter

Variance Reports are made to find out the % increase or decrease for Profit, Revenue, Cost Expenses, Attrition, Manpower Count, Salary, Recruitment Cost etc.

Examples of Variance Analysis charts

Statutory Bonus

Applicability of Bonus Act

  1. The company has been in operations for 5 years and employs at least 20 employees
  2. The employee’s wages are not more than 21,000 every month.
  3. The employee must have worked for minimum 30 days in the calendar year.

Salary and wages

Wages as per bonus act means Basic + DA

It does not include:

  • Allowances
  • Commission
  • Travelling concession
  • Ex-gratia payment
  • Retrenchment compensation, gratuity or other retirement benefits.
  • Any amenity, service, or concessional supply of food grains or other articles
  • Employer’s contribution to PF or other pension fund
  • Remuneration in respect of overtime work.

Time Limit for payment

The payment of Bonus for a financial year has to be made within 8 months of closing the book of accounts.  As financial year ends in March, payments are made by the end of October. Most companies generally pay the statutory bonus during Diwali.

Bonus needs to be paid even for exited employees. The pending bonus for exited employees is done in their F&F settlement.

Bonus Limits

  • Maximum Limit: 20%
  • Minimum Limit: 8.33%

 Bonus Wage Ceiling

  • Where the salary or wage of an employee exceeds seven thousand rupees or the minimum wage per month(whichever is higher), the bonus payable to such employee shall be calculated as if his salary or wage were seven thousand rupees or the minimum wage (whichever is higher)
  • So If basic and DA is more than 7000 or minimum wages (whichever is higher), then the bonus will be paid on that ceiling amount and not on actual Basic and Da.
  • Where the salary or wage of an employee is less than seven thousand rupees or the minimum wage per month (whichever is higher), the bonus payable to such employee, shall be calculated on his actual wages.

There has been a lot of confusion with regards to the calculation method based on this amendment made. But note that the amendment of Rs 7000/- or minimum wages is made in Section 12 of bonus act.

Section 12 of bonus act pertains to a salary (basic +da) more than the prescribed limit( which in our case is Rs 7000/- or Minimum wages – whichever is higher)

Disqualification of Bonus

  • An employee who is dismissed from service on the grounds of fraud/theft, riotous or violent behavior at the premises of the establishment or for misappropriation or sabotage of any of the property of the establishment can lead for disqualification of bonus.

 

KRA and KPI

KEY RESULT AREA

Key result area (KRA) are strategic factors where in positive results must be realized in order to achieve the organizations strategic goals. They are used to denote the assignments of task and duties to employees.

Each employee is given a set of KRA (generally 4-5). KRA gives an individual a clarity to his role. The employee is hence aware of what are the expectations of the superior has towards him/her.

KRA is a strategic area where excellent performance is required. Key result areas are also referred to “critical success factors”.

The various KRA are given weightage. Weight of each KRA is determined based on the scale of priority. Total of all the weights should be equal to 100%.

KEY PERFORMANCE INDICATORS

Key Performance Indicators (KPIs) are quantifiable metrics, which provides an indication as to whether the organization is making progress towards achieving that KRA.

For each KRA there are dedicated KPI(s). These are set at the beginning of the assessment period. At the end of the performance period the employees’ achievement towards that objective is measured.

The weight of each KRA is divided among its KPI.

Each KPI will have a target score.

KPIs are subsets of KRAs. Once you have a KRA in place, you can get a definitive and measurable KPI.

EXAMPLE

KRA: Recruitment ( if weightage is 45 %)

KPI:

  • Time to fill (15%)
  • Qualified Candidate per hire (10%)
  • Quality of Hire (10%)
  • First year retention rate (10%)

For each KPI we have a target and we measure the employee performance against that target.

e.g Time to fill we have a target that a TAT achievement above 90 % will give a 5 rating. So 90 % = 4.5 rating as 100 % = 5 rating.

So our target is 4.5. We then calculate the recruiter TAT % and find out his/ her rating.

So a TAT% of 70% will give us a rating of 3.5.

  • Target: 4.5
  • Achievement: 3.5
  • Achievement percent: 3.5/ 4.5 = 78%

The weightage for time to fill is 15%. So the weighted score is 12% (78% * 15%)

So all other KPI(s) in recruitment will be calculated to get their individual weighted score.

Summation of all the KPI weighted scores will give us the score for recruitment KRA.

Maternity Act

Introduction

Applicable to establishments employing 10 or more employees.

A woman who has worked for at least 80 days in the 12 months immediately preceding for expected date of delivery will be eligible to avail the Maternity benefits.

It is applicable to all the women who are employed in any capacity directly or through any contractual agency.

 

Leave Allowed

  • Maternity leave available to the working women is increased from 12 weeks to 26 weeks for the first two children. This benefit could be availed by women for a period extending up to 8 weeks before the expected delivery date and remaining 18 weeks can be availed post childbirth.
  • A woman with two or more children will be entitled to only 12 weeks of maternity leave.
  • Maternity leave up to 12 weeks for a woman who adopts a child below the age of three months, and for commissioning mothers (who use a surrogate to have a child). The period of maternity leave will be calculated from the date the child is handed over to the adoptive or commissioning mother.

Note

  • It is mandatory on the part of employers to extend the benefit of enhanced maternity leave to those women workers who were already on maternity leave on the date of enforcement of the Maternity Benefit (Amendment) Act,2017 i.e. as on 01.04.2017.
  • Those women employees who has already availed 12 weeks of maternity leave before enforcement of the Maternity Benefits (amendment) act 2017 i.e., 1st April, 2017 shall not be entitled to avail the extended benefit of the 26 weeks leave.

Creche Facility

Every establishment with 50 or more employees needs to mandatorily provide a creche facility within a prescribed distance. The woman will be allowed to visit the crèche four times a day.

Work from Home

An employer may permit a woman to work from home, if the nature of work assigned permits her to do so. This may be mutually agreed upon by the employer and the woman. This may be exercised after the expiry of the 26 weeks’ leave period.

The above amendments are effective from 1st April,2017, except those relating to creche facility which would come into force from 01.07.2017.

Maternity Leave Salary

Under the Maternity Benefits Act of 1961, the maternity leave salary is calculated based on the average daily wage for the three month period immediately before maternity leave starts. It includes Basic, DA, HRA, Conveyance, Medical Allowance, Other Allowance etc.

Leave for miscarriage

In case of miscarriage or medical termination of pregnancy, a woman shall, on production of such proof as may be prescribed, be entitled to leave with wages at the rate of maternity benefit, for a period of six weeks immediately following the day of her miscarriage or, as the case may be, her medical termination of pregnancy.

Leave for tubectomy operation

In case of tubectomy operation, a woman shall, on production of such proof as may be prescribed, be entitled to leave with wages at the rate of maternity benefit for a period of two weeks immediately following the day of her tubectomy operation.

Leave for illness arising out of pregnancy

A woman suffering from illness arising out of pregnancy, delivery; premature birth of child miscarriage, medical termination of pregnancy or tubectomy operation shall, on production of such proof as may be prescribed, be entitled, in addition to the period of absence allowed to her under respective provisions of maternity act is allowed leave with wages at the rate of maternity benefit for a maximum period of one month.

Payment of maternity benefit in case of death of a woman

  • Before Child Delivery: When a woman dies during this period, before delivering the child, the maternity benefit shall be payable only for the days up to and including the day of her death.( 8 weeks leave can be taken before the date of delivery)
  • After Child Delivery: When a woman, dies during her delivery or during the period immediately following the date of her delivery for which she is entitled for the maternity benefit, leaving behind in either case the child, the employer shall be liable for the maternity benefit for that entire period. If the child also dies during the said period, then, for the days up to and including the date of the death of the child

Important

  • The law also states that from 10 weeks before women’s employee due date, she will not have to do arduous work, stand for long hours or be asked to do any other work that may cause problems. A working woman also has right to ask for light jobs during pregnancy for eg., jobs that does not require long standing hours or work in section where hazardous substances are not used as per maternity benefit act.
  • There is no number or limit mentioned in the Maternity Benefit act, as to how many times can one take Maternity Leave.
  • Dismissal or discharge of a women during the pregnancy is unlawful and such employer can be punished under Section 12 of the Act
  • If any woman employee, who is on maternity leave is found to be working in any other establishment for any period during the leave, then her claim to maternity benefit for such period is liable to be forfeited.