RNSIT – MBA

HR ANALYTICS (20MBA HR304)

Module -3 Applications of HR Metrics and Creating HR Dashboards
Contents of the Module:
HR Metrics, Types of HR Metrics, Staffing Metrics, Training and Development Metrics,
Application-oriented Exercises : Dashboards: Few Key Excel Add-ins/Functions to Help
Create Dashboards, Name Range, The Developer Tab, Form Controls, Important Excel
Formulas Useful for Creating Dashboards, VLOOKUP, INDEX, SUMIF, AVERAGEIF and
COUNTIF, Application of Excel Functions in Creating HR Dashboards, Storyboarding:
Connecting the Dots and Integrating the Findings.

DEFINING HR METRICS

HR metrics refers to a system of data that is used to measure HR and its relationship and the
effect on an organization. They can focus on a variety of specifics and can be used to help an
organization uncover not only the areas of excellence but also areas of improvement.

The TDRP (Talent Development Reporting Principles) classifies the HR metrics majorly into
six metrics:

i. Capability Management Measure

ii. Leadership Development Measure
iii. Talent Acquisition Measure
iv. Learning and Development Measure
v. Performance Management Measure
vi. Total Rewards Measure

The CIPD (Chartered Institute of Personnel and Development) HR measures are classified as
per the different stages of typical HR process.

a) Recruitment and Selection: Stage of HR process, quantity recruited, number of

people interviewed, and full-time /part-time jobs recruited.
b) Retention and Separation: Turnover of high performers, resignations by the length of
service, resignation rate and employee retention index.
c) Learning and Development: Training initiatives for priority skills gap spent on
training as a proportion of profit and L&D costs as a percentage of labour costs.

This document is intended for circulation in Dept of MBA – RNSIT only. Page 1
RNSIT – MBA

d) Remuneration: Labour costs per full-time equivalent (FTE) pay differentials,

effectiveness of remuneration process and pay competitiveness.
e) Talent Management and Development: Turnover of high-performers, promotion
speed ratio, effectiveness of talent management and development process and asset
values of key employees.
f) Performance/capability management: Average time to promotion, new hire
performance satisfaction, effectiveness of feedback and revenue/operating cost per
employee.
g) Workforce planning and optimization: Job heat map, timely publication of schedules
and workforce stability.
h) Engagement/Culture: Attendance/absence rate, supportiveness of workers to each
other, staff satisfaction index and impact of intrinsic reward elements on engagement.
i) Diversity Management: Pay differentials by diversity criteria, age staffing breakdown
and participation in diversity and ethics training.
j) People Leadership/Leadership Development: Ratio of HR staff to all employees,
reduction of people/process/project costs broken down by manager and capability of
top leadership.

TYPES OF HR METRICS

A metric is something that can be quantified to describe the outcome of a business process.
There can be numerous HR metrics that can be classified into both efficiency and
effectiveness metrics or lead and lag measures.

Lagging Indicators

Lagging indicators are outcome measures that help you gauge that your HR progress by
examining the final end result or outcomes of your collective of efforts. Use of the “lagging”
term reflects the delay or gap or between your actions and a change in the final end result.

Characteristics

○ Outcome measure

○ Indicates the end result of the system.

○ Tells you what happened, not what is happening.

○ Less effective at measuring the effects of specific changes made to the system.

This document is intended for circulation in Dept of MBA – RNSIT only. Page 2
RNSIT – MBA

● Health example: heart attack, liver failure, stroke.

● HR Examples (Employee retention, Employee performance, Organizational performance,

Customer retention, Employee productivity)

Leading Indicators

Leading indicators are process measures that help you gauge that incremental progress you
are progress making toward key HR outcome (lagging) measures.

○ Since leading indicators measure the results from your processes, there is , less of a delay
between your actions between and a change in the system.

○ They are the performance drivers performance drivers.

Characteristics

○ Process measure.

○ Immediate feedback to the system.

○ Tells you what is happening now.

○ Can be tracked over time.

○ Provides an “early warning” of emerging” results.

● Health examples: Heart beat, blood sugar level, Blood pressure.

● HR Examples (Employee absenteeism, Employee sick leave use, Customer satisfaction

surveys, Employee satisfaction surveys)

Types of Metrics

I. Time to fill or hire: It is the average number of calendar days from the date a job
requisition is approved to the date an offer is accepted by a hire.

Time to fill= Total days to fill/Total hires

II. Offer Acceptance Rate: It is the ratio of the total number of offers which have been
accepted by the selected candidates to the total number of candidates to whom offers
were extended.

III. Career path ratio: This ratio tells about the percentage of upward movement of
employees within an organization. It can be calculated as follows:

Career Path ratio= Total promotions/ (Total promotions + Total Transfers)

IV. Cost per hire: This ratio tells about the average amount spent on hiring each
employee. It can be calculated as follows.

This document is intended for circulation in Dept of MBA – RNSIT only. Page 3
RNSIT – MBA

Cost per hire= (Internal Recruitment costs+ External Recruitment Costs)/ Total
Number of hires

V. HR headcount ratio: This ratio tells about the number of employees per HR staff
person. It can be calculated as follows:

HR headcount ratio= Headcount/HR headcount

VI. Direct labour cost: This ratio tells about the cost incurred for producing specific
goods or/and services. It can be calculated as follows:

Direct Labour Cost= (1 + Inflation rate) * Headcount * Cost per labour per hour

STAFFING METRICS

Staffing effectiveness and efficiency can be calculated by measuring accession rate, add rate,
replacement rate, career path ratio, cost per hire, time to fill, time to start, offer acceptance
rate, sign-on bonus percentage and sign-on bonus factor. The following is the description of a
few important staffing metrics such as accession rate and cost (internal and external) per hire:

 Accession rate: this ratio tells about all hires as a percentage of the employee headcount. It
can be calculated as follows:
Accession rate=Total hires/Regular headcount
 Internal Cost per hire: This involves costs because of the employees hired from within the
organization which eventually means a movement of employee designation within the
organization. It can be calculated as follows:

Internal cost per hire= (Internal advertising costs + Internal referral bonus paid + Internal
travel costs + Internal Relocation costs + Internal Recruiter Cost+
Internal no cost)/ (Internal Hires)

Note: ‘Internal no cost’ involves the cost incurred due to unsolicited resumes.

 External Cost per hire: This involves costs because of the employees hired from outside the
organization. It can be calculated as follows:

External cost per hire= (External advertising costs + External agency fees + External referral
bonus paid + External travel costs + External Relocation Costs+
External Recruiter costs+ External no cost)/ (External Hires)

This document is intended for circulation in Dept of MBA – RNSIT only. Page 4
RNSIT – MBA

 Attrition rate: Also called as churn rate or labour/HR turnover, this ratio tells about the
rate of employees moving out of a larger group over a specified time period. It can be
calculated as follows:

Attrition rate= (Separations during the year)/ (Average of employees at the beginning &
end of the year)

 Quality of new hires: This is an important lag metric for determining the success of the
recruitment process. This metric indicates whether the new hires have the right potential
for advancement in the organization.

Quality of new hires= (PR+HS+HP) /N

Where

PR: The average performance rating of new hires.

HS: Percentage of new hires retained within one year of recruitment.

HP: Percentage of new hires who are promoted within one year of being recruited.

N: Number of performance indicators used to measure the performance/potential of the

new hires.

TRAINING AND DEVELOPMENT METRICS

Training and development can be calculated by measuring employees trained percentage,

training cost factor (TCF) , training cost percentage, training cost HR expense percentage,
training compensation percentage, training headcount investment factor, training FTE
investment, training staff ratio, headcount training factor, FTE training factor, training cost
per hour and percentage of training hours. The following formulae discuss TCF and training
headcount investment factor in brief:

 Training Cost factor: This ratio tells about the mean amount spent on training for each
training session attended. It can be calculated as:
Training Cost factor= (Total Training Cost) / (Total number of training sessions
attended)

Here, total training cost may include planning costs, programme development and design
costs, delivery costs and evaluation costs.

This document is intended for circulation in Dept of MBA – RNSIT only. Page 5
RNSIT – MBA

 Training headcount investment: This ratio tells about the amount invested in employee
training and development for each employee. It can be calculated as:
Training headcount investment factor= (Total training cost)/ (Regular headcount)

 Training FTE investment factor: This ratio tells about the amount invested directly in
employee training and development for each FTE. It can be calculated as:
Training FTE investment factor= (Total training cost) / (Regular FTE)
Here FTE implies the number of hours worked by an employee on full-time basis.
Regular FTE is determined by dividing the total number of hours a group or department
works with FTE of one employee. For example, if the actual headcount is 10 that is, 10
employees are working but all these work only half of the working hours, the FTE would
be five employees. In the case of FTE, each employee is seen as a bundle of hours he or
she has worked for the organization. FTE includes overtime but excludes factors such as
holidays, leaves and vacations.

 Return on training investment: this ratio tells about the amount of benefits that would
be derived out of each ‘penny’ invested on the training. It is an important training
effectiveness measure because it gives a comparative picture between the total costs and
benefits. It can be calculated as:
Return on Investment= (Total benefits) / (Total costs)

 Payback Period: This ratio tells about the number of months it would take for the
benefits to meet the money invested. In other words, it calculates the ‘break-even point’
for covering the total cost. It is an important training effectiveness measure because it
informs us when the person can expect receiving profits. It can be calculated as:
Payback period= (Total Costs) / (Monthly benefits)
Here the total costs would be the same as for the ROIs. ‘Monthly benefits’ essentially
means the ratio of the total benefits and the duration for which the benefits would accrue.

This document is intended for circulation in Dept of MBA – RNSIT only. Page 6
RNSIT – MBA

1. MEASURING TRAINING ROI

Calculate the missing values (?) in the table below and then calculate the following measures
of training effectiveness

A SAMPLE COST AND BENEFIT SHEET

Training duration 33 hours
Expected number of trainees 750
Training benefits accrued for 12 Months
Costs (in 00's Rs.)
Planning 40,930
Marketing 4,744
Administrative expenses 12,713
Trainer's expenses 86,250
Stationery 15,000
Amenities 40,500
Travel expenses reimbursement 5,53,156
Training effectiveness expenses 872
Total Cost ?
Benefits (in 000's Rs.)
Labour savings 241071
Productivity enhancements 6,75,000
Miscellaneous cost savings 1,61,250
Total Benefits ?

ROI
Payback period (in months) ?

Solution

A SAMPLE COST AND BENEFIT SHEET    

Training duration 33 hours    
Expected number of trainees 750    
Training benefits accrued for 12 Months    
Costs (in 00's Rs.)    
Planning 40,930    
Marketing 4,744    
Administrative expenses 12,713    
Trainer's expenses 86,250    
Stationery 15,000    
Amenities 40,500    
Travel expenses reimbursement 5,53,156    
Training effectiveness expenses 872    

This document is intended for circulation in Dept of MBA – RNSIT only. Page 7
RNSIT – MBA

Total Cost ? 7,54,165  

Benefits (in 000's Rs.)    
Labour savings 241071    
Productivity enhancements 6,75,000    
Miscellaneous cost savings 1,61,250    
Total Benefits ? 1077321  
14.28% means ROI is
high and for every unit of
? the cost incurred 14
14.2849509 units of the return is
ROI 1 guaranteed
8.44 Means
approximately 9 months
8.40044889 required to retrieve the
Payback period (in months) ? 1 costs

ROI=Total Benefits/Total costs

Payback period= Total Costs/Monthly Benefits

2. Shantanu Mangal, a Master of Business Administrator, was really happy that he finally got
the job of HR Analyst in a leading multinational consulting firm, Systematic Consulting Ltd.
Though he had HR Analytics as an elective in his MBA, he was not very confident in
calculating accurate HR efficiency and effectiveness ratios.
He was hoping to receive rigorous training on the ration analyses from the company during
his six-month probation period. Unfortunately, the company had training programmes that
only deal either with the advanced HR analytics of with customized HR Analytics. One fine
day, Mangal’s boss, Sujeet Khanna, invited Mangal to his cabin and gave him a task to
calculate Human Economic Value added (HEVA), Human capital value added (HCVA) and
human capital ROI (HCROI) for their client, AAB international Pvt.Ltd. Khanna gave the
following figures to support the required calculation to Mangal:

He said, Last year, AAB International Pvt. Ltd received a total of ₹90,000,000 from sales.
Cost of goods sold was 45,000,000, cost of ordinary shares was 45,000,000, cost of ordinary
shares was ₹3,750,000, spending on the day-to-day operation was ₹25,000,000 on which
10% tax was applicable, total payroll amounted to ₹14,000,000, and total number of full-time
employees in that period was 1,120,000.

Khanna asked Mangal to come back with the required values within 15 minutes. This
deadline of mere 15 minutes was a real pain for Mangal as he had to search for the meaning
and formulate to calculate the ratios. Anyways, he somehow managed to calculate the ratios
and reported the following to the boss:

HEVA = ₹1018.

This document is intended for circulation in Dept of MBA – RNSIT only. Page 8
RNSIT – MBA

HCVA = ₹3 billion

HCROI = ₹3.2 billion

Do you think Mangal’s figures are correct? If you had been in Khann’a seat, what would
have been your reaction?

Particulars Amount
Revenue ₹ 9,00,00,000
Deduct: Cost of Goods Sold ₹ 4,50,00,000
Gross Profit ₹ 4,50,00,000
Deduct: Operating expenses ₹ 2,50,00,000

Operating Proft or earnings before

interest and tax ₹ 2,00,00,000
Deduct: Tax at 10% ₹ 20,00,000
Operating profit after tax or net profit ₹ 1,80,00,000
   
Other details available  
Cost of equity ₹ 37,50,000

FTE 1120000
TOTAL PAYROLL (EMPLOYEE COST) ₹ 1,40,00,000

a) HEVA is similar to economic value added. It tells about the extent to which the human
resource is able to add economic value to the organization for each employee. It can be
calculated as follows:
HEVA = (Net operating profit after tax-Cost of equity)/Full-time employees

= (1,80,00,000 -37,50,000)/11,20,000

= Rs.12.72

b) HCVA tells about whether the organization is able to generate profits after the salaries and
benefits are adjusted to the revenue for each employee.
HCVA = (Operating Profit + Employee Cost)/FTE
= (1,80,00,000+1,40,00,000)/11,20,000
= Rs.28.57
c) HCROI tells about the ROI on human resources.

HCROI = (REVENUE-(OPERATING COST-LABOR COST)/LABOUR COST)-1

HCROI= (90,000,000-(25,000,000-14,000,000)/14,000,000)-1

This document is intended for circulation in Dept of MBA – RNSIT only. Page 9
RNSIT – MBA

=4.64
It means that the ROI due to human capital is 464%. It shows that employees are working
extremely hard to fetch returns for the organization. It is quite clear from the above results that the
figures calculated by Mangal were all wrong. Therefore, If had been with Khanna, I would have
asked Mangal to work on his fundamentals.

Case Study 1
RECRUITMENT METRICS AT TQR SOLUTIONS

Riya, the HR manager of TQR solutions limited (TQR), was taking stock of the recent
recruitment drive of the company along with her team. The company had recruited 60 candidates
from external as well as internal sources. Riya and her team had to work out the hiring costs and
present this to the management. TQR, a leading private sector banking and financial services
company with its headquarter in Mumbai, India, was planning to recruit suitable employees as it
had witnessed a high employee turnover rate in the past few months. The company was also
planning to expand its operation in the southern zone of the country, which would require 50 new
employees in addition to 10 internal heights for lower level positions. The HR Manager of the
firm along with her team evaluated different sources of online job postings such as LinkedIn,
Monster.com, CareerBuilder, and Ladders and online ads in addition to the print ads and
participation in career fairs.

LinkedIn, being one of the most popular online tools for recruitment, was charging as much as Rs.
50,000 for a monthly job posting and a quarterly charge of Rs.30,000 for resume access. Monster,
was quoting Rs. 30,000, for a monthly job posting and 8000 per month for resume access.
CareerBuilder and ladders relatively more reasonable, and we’re charging rupees 20,000 each for
the monthly job posting and rupees 15,000 each for an annual subscription to access the resumes.
Similarly, participation in a career fair would cost rupees 25,000, and advertisement, both online
and print would lead to a further expenditure of rupees 50,000. However, the Internet
advertisement cost such as ads on notice boats, Internet of the company and interdepartmental job
postings on Internet was as nominal as rupees 10,000. The HR team finally decided to opt for
monster.com due to his huge resume database and popularity. In addition to this, the company
also placed a print add in a dating newspaper.

Riya had also approached one of the leading employment agencies to generate job
applications from potential recruits. The agency personal gave an estimation of Rs. 50,000 for

This document is intended for circulation in Dept of MBA – RNSIT only. Page 10
RNSIT – MBA

generating a requested number of 100 applications for different job positions specified by her.
Riya was confident of getting another 100 applications through the other sources being
considered by the company. Ravi, the Managing Director of TQR, had also agreed to Riya’s
proposal to announce an external referral bonus of Rs. 5000 and Internet referral bonus of Rs.
2000 per hire.

In addition to the above expenditure, the management of TQR prepared a list of other
estimated costs such as salary, benefits and overhead costs for the staff and support staff to
meet the manager and discuss about the sourcing, work with agency and media, screening the
applicants, calling the applicants for interviews, interviewing the candidates and checking the
references, scheduling the interview with the manager after reviewing the candidates and
confirming the offers. Similarly, salary and benefits had to be paid to the senior officials
(managers) for their time spent on time management interviewing the candidates and making
hiring decision.

Though it was clearly stated by Riya that disqualify candidates would spend only one day for
the interview and declaration of results and the selected candidates would spend two days for
interview and other related documentation and formalities, the staffing team consisting of 12
members (5 Staffs, 3 Managers and 4 support staff ) eventually required five days spent to
screen and interview 150 external applicants who turned up for the interview and another one
day to interview and select the internal candidates. Finally, at the end of the recruitment
drive, 50 candidates were hired from external sources and 10 candidates were sourced
internally through transfers and promotions.

Once the job applicants got selected in the interview, it was a mandatory policy at TQR for
the new external hires to undergo a medical examination and get their records verified. In this
regard, TQR had outsourced such activities to various third party agencies and had to incur a
cost of Rs. 600 per candidate on medical examination and Rs.500 per candidate on
employment and record verification. Additionally, the company has a policy to reimburse the
travel cost for each externally recruited candidate to appear for the interview if they were
selected for the job. On an average, a cost of Rs. 2000 was incurred by the selected
candidates for travel to appear for the interview.

With regard to the internal candidates, they were not paid any travel and lodging expenses
since the company policy was to recruit internal hires through telephonic interview and video
conferencing. However, candidates accepting transfer were provided with relocation

This document is intended for circulation in Dept of MBA – RNSIT only. Page 11
RNSIT – MBA

expenses. The average relocation cost, to be paid by the employer, was calculated at Rs.
15,000 per transferred employee. Four of the internally recruited candidates were transferred
from their current location. However, the company decided not to reimburse the relocation
cost for the external hires. The company also decided to keep Rs. 20,000 aside to meet the
other miscellaneous joining expenditure against security checks, opening a new employee file
and making the employment bond for the total number of new hires. Once all the hiring
decision was made, the HR team headed by Riya, gathered to review the total cost incurred
by the company on this recruitment exercise. It was known to them that the salary and
benefits of HR staff was Rs. 200 per hr for 8 working hours a day and for the managers it was
Rs. 600 per hr for 8 working hours a day. The salary and the benefit for the support staff was
Rs. 50 per hard for 8 working hours a day and they would also be paid an overhead charge of
10% on their daily wage in lieu of helping the HR team throughout the six days of the
recruitment process. The team also noted that out of 50 external hires, 18 were hired through
referrals whereas out of 10 internal hires, 5 were hired through referrals. The HR team had to
now calculate and report the entire cost so that the finance department would settle the
accounts. Additionally, Riya, was required to submit a report to the Managing Director
presenting the external cost per hire, internal cost per hire and total cost per hire.

  EXTERNAL COST HIRING    

SL.NO PARTICULARS CALCULATION EXPENSES

1 MONSTER.COM 30000+8000 38000
LEADING EMPLOYMENT
2 AGENCY   50000
3 MEDICAL EXAMINATION 50*600 30000
4 EMPLOYEE VERIFICATION 50*500 25000
5 MISCELLANEOUS   20000
6 TRAVEL 50*2000 100000
7 EXTERNAL(18 REFERALLS) 18*5000 90000
8 PRINT ADVERTISEMENT   50000
  TOTAL   403000
       
INTERNAL COST PER
SL.NO HIRING    

1 PARTICULARS CALCULATION EXPENSES

2 RELOCATION EXPENSES   60000
SALARIES HR STAFF( 5 EMP)
  200Rs per hour 8hours Perday (200*8*6)*5 48000

This document is intended for circulation in Dept of MBA – RNSIT only. Page 12
RNSIT – MBA

FOR 6DAYS
MANAGER (3 EMP) 600Rs per
  hour 8hours Perday FOR 6DAYS (600*8*6)*3 86400
SUPPORT STAFF (4 EMP) 50Rs
per hour 8hours Perday FOR
6DAYS ,WAGES+10% DAILY
3 WAGE {[(50*8)+40]*6)*4 10560
  REFERRAL BONUS :    
  INTERNAL ( 5 REFERALLS) 5*2000 10000
INTERNAL ADVERTISEMENT
4 (INTERNET)   10000
  TOTAL   224960

i) EXTERNAL COST PER HIRING

ECPR= TOTAL EXTERNAL COST /TOTAL EXTERNAL EMPLOYEES RECRUITED =

Rs.4,03,000/50= Rs.8060 per candidate.

ii) INTERNAL COST PER HIRE

ICPH=TOTAL INTERNAL COST/TOTAL INTERNAL EMPLOYEES RECRUITED=

Rs.2,24,960/10= Rs.22496 per candidate.

iii) COST PER HIRE

CPH= EC/EH + IC/IH= Rs.10,466 per candidate.

CASE STUDY 2: MEASURING TRAINING ROI AND PAYBACK

PERIOD
Mindtrap Incorporated, an MNC headquartered at Mumbai, hired 50 new management
trainees in November 2015 from few premier business schools of India. The HR Head,
leading a team of four HR executives, decided to conduct a five-day long training programme
for the new joiners to better facilitate the orientation process. The team identified the training
needs of the participants. They came to the conclusion that the training can be best imparted
by an external training consultancy possessing the requisite skills. The in-house HR team
communicated the proposal to the training firm Xyler Ltd, a professional company in the
business of imparting professional training workshops located in New Delhi, and asked
quotation for their charges. Xyler officials replied back that their charges would comprise of

This document is intended for circulation in Dept of MBA – RNSIT only. Page 13
RNSIT – MBA

a one-time Rs.30,000 payment for the external resource provided by Xyler Ltd. Additionally,
Xyler would rent a hotel for conducting the programme which could cost Rs.10,000 for 8
hours/day. Furthermore, Xyler has given a requisition for a one-time stationery cost
Rs.10,000/day for hours/day. Furthermore, Xyler has given a requisition for a one-time
stationery cost of Rs.30,000 in lieu of providing the training kit materials(licensed software,
copyrighted measurement tools, notepad, brochure, pen and so on).

Xyler also communicated that the food and lodging cost of the programme would have to be
borne by Mindtrap Incorportated. Their HR staff estimated that to cover the food cost of 50
participants in the training, an approximately daily expenditure of Rs.15,000 would have to
be incurred. Accomodation of the participants in the hotel would cost Rs.15,00 per participant
per night and to and fro AC 3-tier railway fare for each participant from Mumbai to New
Delhi and back will be Rs.2500.

The management insisted that one internal trainer would also accompany the trainees to
monitor the process. This would mean that for the five days of the training the HR personnel
will not be productive for Mindtrap Incorporated. The average salary of an
executive(including HR executive) at Mindtrap is Rs.100/hour per day. Typical working
hours of ABC is from 9.00 am to 6.00 pm. Similarly, it is to be considered that the 50
participants will also not be contributing to Mindtrap’s productivity for the five days of
training. If we consider the travel time, it will be seven days of on-duty leave for the
participants and the HR executive. Mindtrap’s HR head pointed out that they would have to
consider other overhead costs such as local conveyance and food consumed during travel as
well plus some other amenities. The team allocated an additional Rs.20,000 as overhead cost.

Xyler promised guaranteed savings in terms of the following factors as an output of their
well-tested training module. The benefits may be listed as:

(a) Expected Labour savings= Rs.1000 per participant per month (in terms of efficiency,
project completion rate),

(b) Productivity increase=Rs.2000 per participant per month (in terms of new product
development, new customer acquisition and so on)

(c) Other cost savings=Rs.500 per participant per month ( in terms of punctuality, deadline)
and

This document is intended for circulation in Dept of MBA – RNSIT only. Page 14
RNSIT – MBA

(d) Other income generation=Rs.500 per participant per month (through innovation, patents)

Mindtrap’s HR team sat down together to analyse the proposition. Would there be sufficient
gain from this initiative? What will Mindtrap benefit out of this? What will be the
approximate period for the benefits to be appreciated? The team decided to crunch some
numbers to understand how long it will take to get return on this training investment
considering 12 months from training completion as a time frame.

Question: Calculate the training ROI and payback period for the firm and suggest
whether they should pursue the training programme for the new joiners.

APPLICATION-ORIENTED EXERCISES/ FUNCTIONS TO HELP CREATE

DASHBOARD

Application-oriented exercises/ functions to help

create dashboard
This document is intended for circulation in Dept of MBA – RNSIT only. Page 15

Name Range VLOOKUP Function

RNSIT – MBA

1. Name Range

The name range function acts as a shortcut to define cell ranges pertaining to a certain array of
values. For example, suppose CELL A of an Excel spreadsheet contains employee salary data
under the heading SALARY, a name range can be specified as ‘Salary’ which can instantly
select the entire range of salary data within the SALARY column/CELL A.
A name range can be created either by typing the desired column name in the top right space in
a spreadsheet as shown in figure 4.1 and then pressing enter, or by first selecting the entire
range of data in the spreadsheet and then going to the FORMULAS tab of MS excel (refer to
figure 4.2) which will select the desired name ranges for respective columns in a single
instance.

In the FORMULAS tab, and Name range can be either created by defining the name ranges or
by clicking on CREATE FORM SELECTION sub-tab and then selecting the top row. This will

This document is intended for circulation in Dept of MBA – RNSIT only. Page 16
RNSIT – MBA

automatically assign name ranges for all the pertaining columns containing the data (refer
figure4.3).

By clicking on the NAME MANAGER sub-tab, one can observe that the respective column
arrays have been designed a name range corresponding to their column headings. The
advantage of this little operation is that, in future, if data from a particular column array is
needed for further calculation, then the entire data can be recalled by only typing the
designated name range instead of using the mouse or keyboard to select the data range
manually.

If the operation is performed successfully, then excel creates separate name ranges for each
column array. The column headings are incorporated in the name range titles. The symbol
signifies that name Ranges have been created which can be confirmed by clicking on the
NAME MANAGER sub-tab under the FORMULAS tab (refer to figure 4.4).

This document is intended for circulation in Dept of MBA – RNSIT only. Page 17
RNSIT – MBA

THE DEVELOPER TAB

The DEVELOPER tab is an add-in which has a range of interesting features that are useful for
creating buttons, drop-down menus and so on. This tab is an add-in for Microsoft Excel, and
it needs to be enabled first for the user to view it in EXCEL. Following steps are to be
followed to enable the DEVELOPER tab in Microsoft Excel 2007:

 Step 1: Click on the OFFICE/FILE button at the top left.

 Step 2: Click on EXCEL OPTIONS tab (figure 4.5)
 Step 3: In the POPULAR option select SHOW DEVELOPER TAB IN RIBBON and click OK.
The steps to install the DEVELOPER tab in Excel 2010 and 2013 are slightly different. Excel
2010/2013 users may follow the following steps to activate DEVELOPER tab:

This document is intended for circulation in Dept of MBA – RNSIT only. Page 18
RNSIT – MBA

 Step 1: Click on the FILE tab.

 Step 2: Click on OPTIONS menu to get access two EXCEL OPTIONS.
 Step 3: Click on CUSTOMISE RIBBONS menu.
 Step 4: Select the DEVELOPER tab option and click OK.
Once the DEVELOPER tab is activated, the tab will appear in the right extreme right of the
tab menu in Excel and if the user clicks on the tab, then a host of additional functions appear
under this tab (refer to figure 4.6).

A cursory glance at the DEVELOPER tab shows three key features add-in: Code, Controls an
eXtensible mark-up language (XML). The code option is meant for creating Macros and
using Visual Basic to customize operations within excel. The XML option can be used to
import XML Maps, XML expansion packs and to use XML commands to create
customizable operations. While both the code and XML options are highly useful for
complex dashboard development, for the sake of maintaining simplicity, our focus in this
chapter will be on the Control option of the DEVELOPER tab. In the Control option, the
focus of our discussion will be on the Form Control function. The Form Control function is
available under the Insert option (Refer to Figure 4.7)

FORM CONTROLS
The Insert tab reveals a host of buttons and Scroll down features under the sub-heading of
FORM CONTROL (refer to figure 4.7). Two such features which can be very useful for
dashboard development purposes are LIST BOX and COMBO BOX (refer to figure 4.8).

This document is intended for circulation in Dept of MBA – RNSIT only. Page 19
RNSIT – MBA

The LIST BOX feature helps to create an empty box with a scroll down menu where any
information can be displayed by suitable data linking procedure. The COMBO BOX feature
helps to create a prominent scroll down arrow button and on clicking the button, the data
linked with the former is displayed in a drop-down menu. Other prominent features of the
FORM CONTROL include the BUTTON, SPIN BUTTON, OPTION, CHECKBOX icons and
SCROLL BAR, to name a few. The ACTIVE X section contains a greater array of the features
for making interactive dashboards.

VLOOKUP

The vertical look up (VLOOKUP) function in Excel is an excellent option for searching
sequentially arranged data in a vertical manner. That is, if a certain range of data is spread
across the table array consisting of five columns named as A, B , C, D and E in a sequential

This document is intended for circulation in Dept of MBA – RNSIT only. Page 20
RNSIT – MBA

manner such that the leftmost column data is searched for first and then subsequent column
data is searched for in an identical manner and the corresponding row data is returned. A
precondition for this function is that the table array must be sorted in ascending order.

The VLOOKUP function comprises of the following syntax:

=VLOOKUP (lookup value, table_array, col_index_num, (range lookup)).

Where

look up_value: is the value the user wants to look up from a data array; must be in the first
column of the table array/specified range.

table_array: a range of data from where the value is searched and returned.

col_index_number: the column number in the specified range from where the value is
returned.

range_look up: option to search for an exact match (FALSE) or an appropriate match (TRUE)

INDEX

The INDEX function is a very useful tool to link form control features with specific column
data within a table array. This function returns a value or reference to a certain value from
within a table array or data range. In order to return a particular value of a specified cell, the
INDEX function can be used in the array form. The syntax for array form is:

INDEX (array, row_num, [column_num])where

array: the range of data containing the cell from which the value is to be returned.

row_number: the row within the table array/data range from which the value is returned.

column_num: specifies the cell at the intersection of the column and the row from where the
value is returned.

INDEX function used in reference form returns the reference of a cell at the junction of a
particular row and for the heater dashboard column. The user can define the selection to look
in if the reference comprises non-adjacent sections. The syntax of the index function is in
reference form is:

This document is intended for circulation in Dept of MBA – RNSIT only. Page 21
RNSIT – MBA

INDEX (reference,row_num,[column_num], [area_num])

Where

reference: a reference to the specified cell range(s)

row_num: the corresponding rule number from where the reference is returned.

column_num: this is an optional syntax and signifies the number of columns from where the
reference is to be returned.

area_number: this is an optional syntax.

SUMIF, AVERAGEIF AND COUNTIF

The IF condition, applied in conjunction with the SUM, AVERAGE or the COUNT function,
can help in executing computations specific to certain from preconditions. For example, a
SUMIF formula can help to generate the total revenue generated by the staff across various
departments. Similarly, the COUNTIF formula can help estimate the department-wise or
designation-wise headcount. These formulae are very useful for creating customized graphs
and charts for the HR dashboards.

STORYBOARDING: CONNECTING THE DOTS AND INTEGRATING THE

FINDINGS

Story boarding is a project planning tool that helps teams identify and illustrate the various
steps needed to complete a given project. Story boarding provides a visual guide to each task
that needs to be assigned and worked on in order for a project to be completed. Storyboarding
is the process of connecting all these steps into one overarching plot so that once debriefed, it
immediately makes an impact with the top management.

Types of Story Boards

i. Traditional Story boards: the designer will typically use pencil to create conceptual
sketches that form the basis of a movie or animation.
ii. Thumbnail Storyboards: they are used to hash out details of a scene in a movie.
Though tiny and loosely drawn they are more granular and detailed than a traditional
storyboard because they include the type of shot you want for each part of the scene
and how to position their camera.

This document is intended for circulation in Dept of MBA – RNSIT only. Page 22
RNSIT – MBA

iii. Digital Storyboards: Many agencies and animation studios rely on digital storyboards
to show what the final cut of an animation will look like. The design characters where
the client can see the character style and review the scenes, shots and narration.

Benefits of Story Boarding

i. Get Buy-in from Stakeholders

ii. Save Time
iii. It enables business to plan better
iv. It allows business to share their ideas

Connecting the Dots and Integrating the Findings

Connect the dots and join the dots mean to put various facts and ideas together in order to see
the whole picture or understand something globally. When someone connects the dots, he
gathers all the data available in order to come to a conclusion. Great leaders also connect the
dots and help people see the big picture about how any one piece of information relates to the
purpose of their work and the goals of the company. Businesses are complex systems that
need strategy, now more than ever. Our world is becoming increasingly connected as
industries merge together and lines are blurred. This is the defining moment for many
companies as they try to balance capitalising on unique circumstances without overextending
themselves.

Companies who want to rush forward without investing in strategic approaches will quickly
find themselves tangled in the complexity of this new market. But those companies that
invest in strategic approaches, who “connect the dots,” will find themselves at the forefront of
innovation and success. They will be the ones who solve the puzzle and win the game. The
“dots” are specific deliverables – what business creates as part of this process documentation.
Dots are connected through a thought process that leads from one dot to the next dot- the
output of one dot helps define the way the next dot works. By keeping the connections
between the dots in mind, we can ensure we are using the DMAIC (Define, Measure,
Analyse, Improve and Control) tools to efficiently solve a problem. It also helps us tell our
story convincingly with our story board. The secret to building a successful storyboard is
simple: connect the dots. Following steps can be followed to build a better storyboard by
connecting the dots.

This document is intended for circulation in Dept of MBA – RNSIT only. Page 23
RNSIT – MBA

1) Goal statement: This is not simply a declaration of what we are trying to achieve – it drives
how we apply the tools. Each of the ensuing dots is driven by the Goal statement, as well as
by previous dots. It clarifies the direction of improvement, along with “from” and “to” values
and a target date.
2) Data Collection Plan: We collect data either to establish baseline performance or to find
clues to possible causes. The data collection plan should always measure the performance in
terms of the goal statement metric. We call these stratification factors and record them as we
gather our data. Later we analyze the data to determine which of them makes a difference –
differences become clues to root causes. We can collect other information of interest, but
process performance over a time period is essential to building the baseline.
3) Baseline performance: the process performance is plotted in a Run Chart to show the
process performance over time. We normally expect to see a random pattern. Any non-
random feature, such as trends, cycling, clustering, shifts or extreme high and low points
suggest that some causes acting upon the process. Digging deeper into these patterns can
often surface clues to root causes. If we collected certification factors, we can sort the data to
find performance differences. These can be seen by plotting box plots for each value or by
using Analysis of Variance (ANOVA).
4) Root Cause Identification: Analysis of the baseline performance will often give us clues to
potential root causes. We dig deeper to find what is behind the data clues. Those clues are not
root causes but may signal that they are nearby. For example, if you find that there is a
difference between the day shift and the night shift, we cannot conclude that the shift is a root
cause. Our process is not smart enough to misbehave on certain shifts. Rather, there’s
something different happening on different shifts- perhaps a somewhat different process.
Those differences are either a root cause, or close it.
5) Root Cause Confirmation: We work to confirm suspected route causes, either through
process observation or data analysis. If the problem we are trying to solve has been around a
while, chances are that others have already tried to solve it- some with great confidence.
Long-standing problems, sometimes called hardy perennials, persist because people assumed
they knew the cause, but missed the true root cause. We have to confirm the suspected root
causes in order to develop solutions with confidence. To confirm a root cause, we should
intervene in the process and either temporarily remove the root cause or do something to
neutralize its effect. We should then run the process for a short time and measure the

This document is intended for circulation in Dept of MBA – RNSIT only. Page 24
RNSIT – MBA

performance. If it is clearly better, we have confirmed the route cause. If not, we keep
looking for them.
6) Solutions: once we confirm a root cause, we should consider how to act on what we have
learned. Our solutions should always include some action to neutralize a root cause. While
we can add anything that makes sense, at least one of the solutions has to be based on a
confirmed root cause.
7) Verification: if the solutions are effective, the process performance should prove it. We
continue to monitor process performance, extending the Run Chart from the baseline
performance past our solution implementation. Once again, we are measuring the metric
target in the goal statement, and we fully expect to see a favorable shift in the performance.
8) Monitoring and Response Plan: once we confirm improvement, we need to be sure it lasts,
so we continue to measure the performance. We set triggers points- levels to “go no higher
than” or “go no lower than” and take corrective action when the process fails to perform as
expected. We should also track leading indicators- input measures or upstream process
measures- that signal a problem before it emerges acting on leading indicators helps us
respond in time to prevent poor process performance

This document is intended for circulation in Dept of MBA – RNSIT only. Page 25