STEP 1: Enter Settings information
Let's begin by defining our recruitment process steps or stages.
In the Settings Sheet, we can enter the different stages.
The template can accommodate up to 10 stages in recruiting process. If you have fewer stages, you can enter only those stages and clear the other stages.
For example, in the above screenshot, we have entered 7 stages.
A few things to note:
- First stage is always considered as the Application stage - the stage of receiving an application from the candidate. You can name this how you need. But for calculation purposes, this stage will be treated as the stage when the application was received from the candidate.
- Last Stage (if we have 7 stages, 7th is the last stage) should be the Hired Stage. You can name this differently as you need.
- The stage before the last is the Offer stage.
The above setup enables us to later easily calculate our Recruitment KPIs such as Offer rates and Acceptance rates.
Next, enter the the list of sources where you receive applications from. You can customize the list as needed. Enter up to 15 sources.
Documenting the reasons why a candidate was declined the offer and why candidates decline offers from the company are extremely critical for improving the process.
Enter the list of such reasons that are relevant for your company. You can customize the list as needed. Enter up to 15 reasons.
Enter currency symbol to display the cost information in currency of your choice.
STEP 2: Enter Jobs or Vacancies
In the Jobs sheet, you will enter each job in a new row with the relevant information.
Click on image to view in full size
JOB ID is the unique identification of a job opening.
You can enter job information such as Job Title, Location, Hiring Manager, Department, Job Type, Job level, and Recruiter Name.
Job Posted Date is a required field. This is the date when the job is posted for the vacancy.
Positions: This template can handle multiple openings of same title. For example, if you have 3 openings for Graphic Designer, you can enter them in one row and enter 3 in Positions field/column.
Status: When you enter a new job to be filled, enter ‘OPEN’ as Status. When all the positions for that job are filled, enter ‘COMPLETED’ as Status. If the job is cancelled, enter ‘CANCELLED’.
STEP 3: Enter Applications or Candidates
We move to the Applications sheet.
If you indicated in Settings that there are less than 10 stages of recruitment in your company, then you will have columns that are not needed in the Applications sheet. Let's hide them first.
To make it easier for you, they are already labeled as 'Hide column'.
Select those columns, Right click and choose Hide.
Enter each application you receive as a new row in the APPLICATIONS sheet. Start entering from Row 6.
- Enter Job ID for which the application was sent.
- Enter Candidate name and the source through which you received the application.
- Enter Application date (first stage of the recruitment process)
STEP 4: Track Progress of Applications
As you review the applications and the candidates go through each stage, just enter the dates when each candidate reach each stage.
If a candidate does not reach a stage, please don’t enter anything in that column.
In the above screenshot, Todd Moore has reached the Tech Interview 1 stage, Thomas Meyer reached Tech Interview 2 stage, while Jimmy Lawrence has not gone past the Application stage yet.
Enter Comments or notes for each candidate in the Comments column provided.
STEP 5: View Job Report
In the Job Report sheet, we can choose a Job ID and view all details for that one job.
We can also see the Candidates and comments.
This can be a useful tool to have a meaningful discussion with the recruiting team, interview panel and the hiring manager on the applications for that job.
The Job report can list up to 40 candidates for a job.
You can also filter to candidates in a specific stage (like only those in Hiring Mgr Interview stage).
STEP 6: Record Application Decision
When a decision is made on a candidate, update APPLICATION STATUS column.
Enter ‘HIRED’ if hired or ‘NOT HIRED’ if decision was taken not to hire the person.
If the status is blank, it means that the decision has not been taken yet and it is a pending application.
Enter the decline reason if the candidate was not hired. For example, if the candidate reaches the interview stage but does not get the offer due to ‘Technical’ knowledge, then enter ‘Technical’ as Decline Reason.
Enter the Cost of hiring if you hire a candidate. Though the cost is incurred for the overall job and all candidates, we can keep it simple by entering the total cost in the Hired candidate's row.
Enter Comments or notes for each candidate. It's a good practice to enter notes which could be reviewed.
STEP 7: Update Job
When a job order is fully completed, go back to the Jobs sheet and update status.
Status: When all the positions for that job are filled, enter ‘COMPLETED’ as Status.
If the job is cancelled, enter ‘CANCELLED’.
STEP 8: View Dashboard to gain insights
View Dashboard anytime to view performance metrics.
The dashboard is fully automated. There are 4 pages.
The first page focuses on the efficiency of Hiring process.
Click on image to view in full size.
The KPIs displayed are
- Hired: Number of Hired candidates during the period chosen.
- Avg. Days to Fill: Average number of days from Job Posted Date to Hired Date for all hired candidates during the period chosen.
- Total Cost: Sum of cost entered for all hired candidates during the period chosen.
- Cost per Hire: Total cost divided by the number of hired candidates during the period chosen.
We can also see the KPIs trended by month, so that we can understand if we are improving on these KPIs monthly.
Days taken for each stage.
Of the average number of days taken for filling a position, we can see which stages take more time.
We can see which application source brings in most of the hired candidates. This is purely based on number of hires - quantity not quality (which we will cover later).
The second page of the Dashboard presents KPIs related to measuring the quality of applications.
The KPIs displayed are
- Applications Received: Number of applications received during the period chosen.
- Offer %: Number of candidates offered a position/ Number of candidates applied during the period
- Acceptance %: Number of candidates accepting a position/ Number of candidates offered a position during the period
- Hired %: Number of candidates hired / Number of candidates applied during the period
We have the monthly trends of these KPIs as well.
Analysis of Application Sources
Decline Reasons Analysis
The third page of the Dashboard focuses on Active pipeline - Open Jobs being hired, and Pending applications to make decisions on.
The KPIs displayed in this page are
- Open Positions: Number of Positions that are still not filled as of now
- Avg. Days in Market: Average number of days from Job Posted date to Today for open positions
- Pending Applications: Number of Applications where the status is blank
- Applications per position: Pending Applications divided by Number of Open positions
Active Pipeline of Pending Applications
Open Positions sorted by Days in Market
Pending Applications by Month of Application
Page 4 of our dashboard allows us to compare and analyze all the KPIs by department or location or hiring manager or other job attribute.
You can choose the analysis dimension from the drop-down.
Instantly the KPIs update.
All the 4 pages can also be filtered on all the job attributes as well as dates.