Directorate of Online Education
SRM Institute of Science and
Technology SRM Nagar, Kattankulathur-
603203
LAB MANUAL
Course: MBA
SEMESTER I
III
SUBJECT TITLE
HUMAN RESOURCE METRICS
AND ANALYTICS
SUBJECT
CODE
Prepared By
Dr. Cristina Mary Alexander
Assistant Professor, Directorate of Online Education,
SRM IST.
APPENDIX-I
LIST OF EXPERIMENTS
Experiment Human Resource Analytics and Metrics
No
Creating HR Dashboard
1 Creating a Dynamic Table and Pivot Table in Excel
2 Add the Tenure column using the formula in the dynamic table, Create a
pivot table and create a grouping in the pivot table.
3 Creating a slicer option for the pivot table
4 Creating a Report connection option in excel
5 Creating charts for the pivot tables created for the visual representation of
the data.
6 Create a timeline on pivot tables.
7 How to get an instant pivot chart from the dataset
Data Analysis :
8 Demonstrate how to perform descriptive statistics for the data using the Data
Analysis option in excel.
9
Calculating and displaying correlation analysis in Excel
10
Calculating and displaying regression statistics in Excel
Experiment – 1:
1. Creating a Dynamic Table and Pivot Table in Excel
AIM: To create Dynamic Table and Pivot Table in Excel
Steps
i.Use the worksheet(HR data) attached and make the table dynamic
using ctrl+t or
using the table icon in the menu.
ii. Go to insert>Pivot Table and create a pivot worksheet.
iii. Create Pivot Table for Gender, Department, Designation, and
Nationality.
iv.Calculate the percentage of Gender
ii.Create a pivot table using the dynamic table.
iii)Example for pivot table for gender
iv) Calculating percentage for Gender Column in the pivot table
Experiment – 2
2. Add the Tenure column using the formula in the dynamic
table, Create a pivot table and create a grouping in the pivot
table.
Aim: To create an excel formula for the tenure column and a pivot table with groups.
Steps
i.Create a new column in excel and use the following formula in hire to create a new
column“
=(DATEDIF([@[Hire Date]],TODAY(),"Y"))
ii. Go to the number formatting option in the menu and make the Tenure column
general.
iii. Create a pivot table for tenure and get the top 10 in the list.Create top 10 list for
location.
iv.Grouping the pivot table into classes.
Experiment – 3:
3. Creating a slicer option for the pivot table
AIM: To create a slicer option for the pivot table.
Steps:
1. Create a pivot table for the department>go to the slicer option and click on
to Nationality for making a dynamic pivot table that shows the department
with location as a slicer.
2.Create pivot table for the Department,Click on to slicer.
2. Create pivot tables for five columns of your choice and create a slicer
of your choice for all the pivot tables created.
Experiment – 4
4. Creating a Report connection option in excel
AIM: To create a report connection option for all the pivot table slicers so that a
dynamic dashboard can be created.
Steps :
1.Create a slicer for pivot table and change the name of the table from default
name for all the tables created.
2.Go to the options menu>Click on Report Connections to connect all the pivot
tables and connectors.
Experiment- 5
5.Creating charts for the pivot tables created for the visual
representation of the data.
AIM: To Create visualization charts and graphs for the pivot table and make it
dynamic.
Steps: -
1.Create charts or graphs for pivot table created.
2.Create slicer for the charts and graphs
3.Create report connections for dynamic visualization.
All the steps are similar to that we have done for creating pivot table ,here we select the pivot
table,cick insert ,select the appropriate charts and graphs for the pivot table .copy it to a new
page ,paste it ,create slicer and report connections for the charts .Final output is given .
Experiment - 6
6.Create a timeline on pivot tables.
AIM: To create a dynamic timeline for the dates and years in the data.
Steps 1.create a pivot table.Go to insert timeline option and choose the
time line you need.
Step 2: Create timeline for department pivot table for hiredate.
Experiment- 7
7.How to get an instant pivot chart from the dataset
AIM: To Create pivot chart.
Steps: -Go to insert>Pivot chart>select the range and choose the worksheet
and click on ok.
Data Analysis :
Experiment- 8
8. Demonstrate how to perform descriptive statistics for the data using
the Data Analysis option in excel.
AIM: To create an Excel program to Demonstrate descriptive
statistics.(use salary data)
Steps
Activate Analysis Tool pack in excel.
1.Note: the Analysis TookPak is no longer included in Excel for the Mac. You need to
download a third party analysis program to perform some statistical tests. These instructions
apply to Excel for Windows.
2.You will need to have the Data Analysis add-in installed to your version of Excel
to run statistical tests. If you click on the “Data” menu tab and see the “Data
Analysis” option as below, then the add-in is already installed.
3. If you do not see the “Data Analysis” option, you will need to install the add-
in. Depending on the version of Excel you are using, you do this by clicking on
the Office button in the top left corner, and selecting the “Excel Options” button
(below left), or clicking on the “File” tab and then the “options” button (below
right).
You will then see the
Excel Options menu
(left): click on the “Add-
Ins” button and select the
“Analysis ToolPak” and
click the “Go” button to
install. The “Data
Analysis” tab should then
appear in the
“Data” menu as shown
above
Step 2.Go to data>Data Analysis option in excel
Step 3:Click on to data analysis option,select descriprive statistics.
Obtain the output and interpret the result.
Note: Output will be generated only for the Numerical variables.
Experiment- 9
9. Calculating and displaying correlation analysis in
Excel(use diabetes data)
Aim: Create a correlation table using the Data Analysis function in excel.
Steps:Go to Data in menu bar choose data analysis>correlation
Format the cells using conditional formatting.
Interpret the correlation result.
A correlation coefficient of +1 indicates a “perfect positive correlation”, which means
that as variable X increases, variable Y increases at the same rate. A correlation value
of -1, meanwhile, is a “perfect negative correlation”, which means that as variable X
increases, variable Y decreases at the same rate. Correlation analysis may also return
results anywhere between -1 and +1, which indicates that variables change at similar
but not identical rates.
10.Calculating and displaying regression statistics in Excel
AIM: To create an Excel program for calculating and displaying
regression statistics(use salary data)
1.To run the regression, arrange your data in columns as seen below. Click on the
“Data” menu, and then choose the “Data Analysis” tab. You will now see a
window listing the various statistical tests that Excel can perform. Scroll down to
find the regression option and click “OK”.
Now input the cells
containing your data. In
the menu box, click in the
“Input Y Range” box and
then use the cursor to
select the column
containing the data for the
dependent variable. As
you select the cells in your
spreadsheet, the range
should also appear in the
menu box window. Repeat
the process for “Input X
Range” and your
independent variable data.
When everything looks
good, click “OK”. You
will now see the results of
your statistical test (unless
you selected otherwise, by
default the results will
open in a new worksheet).
Among the variables that appear
in the results sheet (left),
depending on your experiment
the most important result is the
R square value, highlighted at
left in the pink cell. You then
report the R2 value in your text
when you describe your
statistical results. If you include
a figure showing your regression
analysis, you should also
include this value in the figure.
Steps for doing this appear
below.
You can also see the p-value (in
red box) indicating whether or
not the test is statistically
significant (i.e. if p < 0.05). In
this example, the p-value is
0.00018.
2.Create your regression curve by making a scatter plot. Add the regression
line by choosing the “Layout” tab in the “Chart Tools” menu. Then select
“Trendline” and choose the “Linear Trendline” option, and the line will
appear as shown above. To add the line equation and the R 2 value to your
figure, under the “Trendline” menu select “More Trendline Options” to see
the “Format Trendline” window shown below. Select the boxes next to
“Display equation on chart” and “Display R-squared value on chart” and you
are all set. (Note that you do not need to go through the “Data Analysis”
steps above to calculate your R2 value if you use this method – Excel will do
that automatically).
