Invoice Manager Excel Template – User Guide

Posted on

This article provides step by step instructions on how to use the Invoice Manager Excel template.

Product page: https://indzara.com/product/invoice-manager-excel-template/

 

Step by Step Instructions

Enter Invoice data in Invoices sheet

Invoices Sheet
Invoices Sheet

The fields required are

  1. Invoice Number: Unique identification of invoices. The Invoice number should not be repeated.
    1. You can have any method to come up with invoice numbers. For example, you can start with invoice number 10001 and then each new invoice, increment by 1. 10001, 10002, 10003, etc.
  2. Customer: Name of the customer to whom the invoice was sent to. It is important to be consistent with the customer name. When payments are made by customer, we will be entering them based on Customer Name and thus being accurate and consistent with Customer Name is important.
  3. Invoice Date: Date of issue of Invoice
  4. Invoice Amount: Total amount on the invoice that Customer needs to pay
  5. Due in N Days: Type 30 for the due date of invoice in 30 days after Invoice Date. For example, an invoice issued on Jan 1st will become due on Jan 31st, if you put 30 in the DUE IN N DAYS column. Depending on the period for each invoice, you can type the appropriate number.
  6. Category: This field is provided so that you can categorize the different invoices. For example, if you are providing two types of service and you want to be able to track them separately, you can categorize the invoices accordingly. Only one category is chosen for each invoice. This will be used in the Dashboard later.
  7. Notes: Optional field where you can type in any comments or notes about the invoice.
  8. Status: This is an automated field which will change color based on the status of the invoice.
Invoice Status Colors
Invoice Status Colors

The four status values possible are

  • PAID IN FULL: If Invoice has been paid in full by payments made by customer
  • OVERPAID: if paid amount Is greater than invoice amount
  • CURRENT: If it is not paid yet, but the due date is in future – in other words, not due yet.
  • PAST DUE: If it is not paid and the due date has passed.

Now, let’s enter our first invoice data.

Important: Start by typing in cell A4.

If you are new to Excel tables, please read the article on Excel tables before using this template. It is extremely critical that you are entering data correctly in the tables.

Entering First Invoice Data
Entering First Invoice Data

In the above example, I have entered an invoice dated 5th Jan 2020 for an amount of 350 due in 60 days after invoice. Today is March 21st (as of writing this tutorial) and as of today, the invoice is past due date and we have not entered any payment yet for this invoice. Hence the status is Red in color.

To enter a second invoice, you would enter a new invoice number in cell A5.

Entering Second invoice
Entering Second invoice

The fields that are required will have a red border if left empty. This serves as a visual reminder to enter those values.

To enter third invoice, you will enter a new invoice number in cell A6.

This is critical as Excel tables auto expand when we follow this method. If you directly go and enter a new invoice number in cell A10 (for example), that will not be included in calculations.

Pasting Data from Another Source

If you are pasting invoice numbers or data from another spreadsheet, please take extra care in pasting.

  1. Ensure the order of columns of data you are pasting align with the columns in this table. For example, please do not paste Invoice number in Invoice Amount column. The columns cannot be altered like that.
  2. When pasting please paste as values. Do not paste the data with Ctrl+V or paste default.
    1. Right click on cell A4 and choose Paste Values icon as shown below.
paste as values
paste as values

After entering my second invoice, it looks like this.

Second Invoice Data Entry
Second Invoice Data Entry

After third invoice, it will look like this.

Third Invoice Data Entry
Third Invoice Data Entry

You can continue to enter up to 5000 invoices like this in the sheet. It is easy to extend even beyond that. The Excel file may become large and you may prefer to start over for the next 5000 invoices.

Entering payments

In the Payments sheet, we will enter payments made by customers in the table. The steps and guidelines for data entry are like the ones we followed in Invoices sheet. So, we will focus here on types of payments.

Full Payments

If the customer chooses to pay the invoice amount in full, with one single payment, the data could be entered as shown below.

Full payment example
Full payment example

Multiple payments (Partial payments)

As an example, I have entered 2 payments as shown below.

Multiple Payments for one invoice
Multiple Payments for one invoice

In this case, Customer ‘First Customer’ made payment of 300 on 4th Feb and remaining 50 on 10th Feb. Both were for same invoice# 1001.

Bulk Payments

Sometimes, customer may make payments across two invoices. They are referred to as Bulk Payments in this template.

Bulk Payment Example
Bulk Payment Example

Let’s take the same ‘First Customer’. Instead of making the 50 remaining balance on Invoice # 1001, let us assume the customer made a payment of 100 as shown below.

In this case, we don’t enter an Invoice number in the Invoice number column. That’s because it is a bulk payment – a payment that goes against more than one invoice. Here, the payment of 100 should be used against invoices 1001 and 1003.

Bulk Payment vs Invoice Payments

The template will automatically apply the bulk payment against open invoices using a ‘first in first out’ rule. Whichever invoice (for the specific customer) appears first in the Invoices sheet will be closed out before applying against the invoice that appears next. The order of entry in the Invoices sheet dictate the payment distribution.

If you need to specifically control which invoices the payment should be applied against, then enter them as invoice payments instead of bulk payments. For example, the same payment of 100 can be split to two invoice payments of 50 as shown below.

Converting bulk payment to invoice payments
Converting bulk payment to invoice payments

Invoice Overpayment

If the customer makes a payment greater than the invoice amount, it will appear as Overpayment in status.

In this case, the payment was 400 was made for invoice # 1001.

Overpaid Amount for Invoice
Overpaid Amount for Invoice

Since the invoice amount was only 350, the status is blue in color indicating overpayment.

Invoice showing overpaid status
Invoice showing overpaid status

The additional amount of 50 will not be used to pay off other invoices.

It will be listed as overpaid in the customer report.

Overpaid amount in Customer Report
Overpaid amount in Customer Report

It is recommended that you update the Payments table to reflect as shown below. 350 for invoice #1001 and 50 as bulk payment.

Correcting overpayment
Correcting overpayment

This would ensure that there is no overpayment for the customer in the report.

Updated Customer report
Updated Customer report

Overpayment of Bulk Payments

In the above case, the customer had another invoice to which the bulk payment was applied. What if the customer didn’t have another open invoice?

In such cases, the customer report will show the extra amount in the top right section as shown below.

Extra Bulk Payments in Customer Report
Extra Bulk Payments in Customer Report

This is the amount you will owe to the customer, since the customer has made extra amount as payment. If a new invoice is issued to customer, then this extra bulk payment amount will be used to pay off the new invoice.

Customer Name validation

If the Customer Name entered for an invoice payment does not match the name in the Invoices sheet, a red border will appear on the payment record.

Invalid Customer Name Entry
Invalid Customer Name Entry

Refresh Calculations

The template uses pivot tables and hence, after entering invoice or payment data, we need to refresh calculations.

In the Data ribbon, click on Refresh All.

Refresh Calculations
Refresh Calculations

In the Payments sheet, you will see a Payment trend by Month.

Payment Trend by Month
Payment Trend by Month

DASHBOARD

In the Dashboard sheet also, the data gets refreshed due to us refreshing in previous step.

The dashboard is designed to give you insights about customers and revenue for your business.

Invoices Dashboard
Invoices Dashboard

The dashboard shows the following visuals:

  1. Invoice Amount by Invoice Month – Displays revenue trend
  2. Balance Amount by Due Month – Displays when balance amount will be due (future revenue projection)
  3. Invoice Amount by Invoice Category – Identifies categories that bring most revenue to your business
  4. Top 10 Customers by Invoice Amount – Customers that bring most revenue to your business.
  5. Top 10 Customers by Balance Amount – Customers who owe the most to your business now

The dashboard can be filtered by Invoice Date slicer.

UNPAID INVOICES REPORT

The report shows all unpaid invoices – broken down into Current and Past Due invoices. You can use this report as one place to see all invoices past due and take necessary actions to follow up with those customers.

Unpaid Invoices Report - Aging and Invoices due today
Unpaid Invoices Report – Aging and Invoices due today
  • It gets updated automatically as soon as you enter/update data in Invoices and Payments sheets.
  • The Aging of invoices
    • For Current Invoices: Number of days from today to Due Date
      • 0 to 30 days, 31 to 60 days, 61 to 90 days and > 90 days
    • For Past Due Invoices: Number of days from Due Date to Today
      • 1 to 30 days, 31 to 60 days, 61 to 90 days and > 90 days
    • 20 current and 20 past due invoices can be shown at a time. To see the next set of invoices, just type in 21 to see invoices 21 to 40.
See next 20 invoices
See next 20 invoices

CUSTOMER REPORT

Customer Report is designed to be a one page view of all invoices for a customer.

Customer Report example
Customer Report example

You can type a Customer name in the customer input field at the top left.

The first section shows the total invoice amount, paid amount and balance amount.

You can choose to see all invoices or only past due.

Customer Report All invoices vs Past Due
Customer Report All invoices vs Past Due

The report shows 30 invoices at a time. But if customer has more than 30 invoices, you can type 31 instead of 1 to see the invoices 31 to 60.

Next 30 invoices
Next 30 invoices

The report is prepared to be print-friendly and you can print in one page.

 

FAQ (Frequently Asked Questions)

Currency formatting

The template has been designed not to display any one specific currency format, as audience all over the world will be downloading this template.

However, if you need to apply your specific currency format, it is easy.

In any sheet, Press Ctrl+G.

Choose currency cells
Choose currency cells

Select CURR and Click OK

This selects all the cells that are currency cells.

Press Control+1 (control and 1 keys together)

Change Currency Format
Change Currency Format

Select Currency Category. Choose the specific Symbol of your choice and click OK

Now, the currency format would be applied automatically.

Repeat this process within each sheet.

 

How to unprotect?

Dashboard, Unpaid Invoices and Customer Report sheets are protected to prevent unintentional editing of formulas, as that would impact the accuracy of the template. Password is not needed to operate the template with all functionalities as described in the product page.

If you are very familiar with Excel formulas, and want to modify the functionality of the template, you can. Please unprotect the sheets using indzara as password and make changes. Before making changes, please save a back-up copy of the file.

More on how to unprotect in Excel

 

How many invoices can be entered?

The template is designed to support 5000 invoices. If you need to increase it to more, please reach out to support@indzara.com

Invoice Manager Excel Template – Support Page

Posted on

Thanks for visiting the support page for Invoice Manager Excel Template which can help simplify tracking and managing invoices and payments.

The following are the support articles to help you get started with using the template. If there are any questions not addressed here, please drop a comment and I will add article/video demo about that topic.

Support Articles

  • Please see this article on Important Tips before using the template
    • Input Data is always visible & can be edited easily
    • Refresh Calculations after entering or updating input data
    • Backup by saving file regularly
  • This template uses Excel tables for data entry. If you are not very familiar with Excel tables, please see this article on How to enter data using Excel Tables?
    • What is a Table?
    • Why do we use Tables?
    • Components of a Table
    • 3 Basic Data Entry Operations
    • 5 Essential Tips to avoid mistakes
  • Step by Step User Guide
    • Step by Step instructions on entering data

If you have any questions on the template, please post them in the comments section below. I will respond as soon as I can. If it will benefit others, I will also publish a blog post and video answering your question.

Sales Pipeline Manager Excel Template – User Guide

Posted on

This article describes step by step how to use the Sales Pipeline Manager Excel Template. For more information on the features of the template, please visit the product page.

Sales Pipeline Manager 

Overview of Steps

  1. Enter sales stages in your Sales process. Rename if necessary.
  2. Entering a new deal in Deals table
    • Change Currency for Deal Value (amount) field if needed.
    • Enter basic deals data (Company Name, Contact Name, Deal Value, Created Date)
    • Hide the columns for extra stages.
    • Enter deal attributes and contact attributes
    • Enter Win % as the chance of winning the deal.
  3. Updating a deal as you make progress
    • Update Stage dates if the deal has moved forward to the next stage.
    • Update Win % and expected Close Date if it has changed over time.
  4. Closing a deal
    • Enter Status (Won or Lost) if the deal is closed.
    • Enter Close Date
    • If deal is lost, enter Lost reason
  5. Monitoring performance regularly to gain insights
    • View Dashboard to see performance and insights of closed deals. Take actions.
    • View active deals to know the active pipeline and projected sales
  6. Track Sales Activities

VIDEO DEMO

Step 1: Review Stages in Sales Process

By default, the template comes with 4 intermediate stages (Lead, Opportunity, Demo, Quote). The first stage is always creation of a new deal, final stage is Closing a deal. If there are 4 intermediate stages, it means that there are totally 6 stages in your sales process.

You can rename the default 4 and add more or reduce depending on your sales process.

Sales Funnel Stages
Sales Funnel Stages

Step 2. Entering a new Sales deal (or lead)

We can enter new deals by typing in the Deals table.

Currency Format

First, let’s set the currency format for deal value amount field.

Click on cell B6. Press Ctrl+1 to open the Format dialog box.

Change Currency in Deals Table Deal Value
Change Currency in Deals Table Deal Value

Change the format as shown above to any currency of your choice. Click OK to save it.

Enter Required Fields

For more on data entry in Excel tables, please visit Excel Tables for Data Entry

Each row is a separate deal. There are some key types of information we enter in the table. Let’s look at them on by one.

Deals Data Entry
Deals Data Entry
  1. Deal Name: Give each deal a unique name. This is a required field. If no deal name is entered, the deal does not get counted in the Dashboard. Having a unique name allows easily identify a specific deal.
  2. Deal Value is the total value of the deal. Deal Value can be estimated if it has not been determined yet. But please enter a value so that the pipeline can be evaluated.
  3. Created Date is the date when the deal originally was entered. This allows us to track the sales process and timeline from this date.

Stage Dates

As the template can handle 10 intermediate stages, there are 10 stage date columns.

If your sales pipeline process has only 4 intermediate stages, then you can hide the columns Stage 5 to Stage 10. Do not delete. Just hide the columns.

Hide stage columns not applicable
Hide stage columns not applicable

You can select the column. Right click and then choose HIDE.

Right click column and choose Hide
Right click column and choose Hide

When a deal passes each stage, enter the date when it reached that stage. In the screenshot above, first deal became as lead on 20th Oct, became an Opportunity on 22nd Oct and Quote was issued on 24th Oct.

If a deal has not reached a specific stage yet, leave the cell blank.

Enter Win %, Status and Close Date

Then, we enter a Win % for each deal. For deals that are not closed yet, we would enter our estimate of the chances of winning a deal. If we have a 50-50 chance of winning or losing a deal, we would enter 50. The template will automatically convert to 50%.

Win Probability Status and Closing Date
Win Probability Status and Closing Date

When a deal has closed, choose the status as WON or LOST and enter a Close date.

If the deal is still open, leave the status blank.

Enter a win % for all open deals to calculate the sales pipeline value.

Close Date

When a deal is closed, always enter close date. When a deal is open, you can enter a close date in future, if you are expecting the deal to close on that date. This information will be used to project future closing deals in dashboards.

Deal Attributes

There are 5 deal attributes provided.

Lead Source, Industry, Location, Product and Company are provided as default attributes.

They can be renamed and Repurposed.

Rename Deal Attributes
Rename Deal Attributes

These attributes are useful in the analysis of your sales pipeline to understand what types of deals are being successful versus not.

Notes

Notes
Notes

You can type in any notes or comments in this column.

Lost Reason

Lost Reason for Deals Lost
Lost Reason for Deals Lost

This column can be used to store the reason why the deal was lost. Enter only for deals lost.

Contact Attributes

Contact Attributes
Contact Attributes

3 columns are provided as placeholders to store Contact name, Email address, Phone number. You can rename and repurpose the fields according to your needs. You can also add more columns to the right.

Validations

There are validations in place in the template for data entry. There will be an error message at the top of Deals sheet indicating the errors. Also, the cell with error will be filled with red color for your convenience.

Data Validations in Deals Data
Data Validations in Deals Data

The following are considered errors.

  1. Created Date is blank
  2. Close date is not entered when Status is OPEN/LOST
  3. Closed Date < Created Date
  4. Stage Date < Previous Stage Date

The error applications will not be included in the Dashboard sheet.

Step 3: Updating a Deal

As we continue to work on deals, new information may arrive. Based on that, we need to update the deals.

Update Win % for deals as needed, so that your active pipeline is always reflecting the reality.

Enter stage dates when deal reaches each stage.

Step 4: Closing a Deal

Deals can be closed if a decision has been taken by the customer to purchase. This is the scenario of a deal WON.

If prospect decides to not to purchase or if it has been a while since the prospect has been in touch, we can close the deal as LOST.

We can enter this information by entering the STATUS to be ‘WON’ or ‘LOST’. The values must be exactly WON or LOST. Other values are not allowed.

Then, we should enter the CLOSE DATE. This date is used to calculate the Average time taken to close a deal.

If the Status is Won or Lost but Close Date is left blank, you will see a red border indicating that the entry is missing.

Missing Close Dates
Missing Close Dates

This red border will also appear if Close Date is less than Create Date. This is to ensure that the data is accurately entered.

Win % could be deleted for closed deals as we already know the result of the deal. They will be ignored for calculations.

If deal is lost, enter Lost Reason.

Lost Reason for Deals Lost
Lost Reason for Deals Lost

Step 5: Monitor Sales Pipeline performance

This step is one that should be done regularly. It is important to keep an eye on the pipeline metrics to improve performance.

Assessing Performance of closed deals

The DASHBOARD sheet provides information on performance of all your closed deals.

Sales Pipeline Dashboard - Performance Funnel
Sales Pipeline Dashboard – Performance Funnel

The top section of the sheet shows several important metrics needed. Let’s discuss one by one.

Sales Pipeline Funnel KPIs
Sales Pipeline Funnel KPIs

Deals Won and Lost: In the sample above, 18 deals were won for total of $111,584 and 42 deals were lost valued at a total of $227,549.

Conversion Rate: 30% of deals were Won.

Average Time to Close Deals: The 60 deals closed took on average 28 days to close. This is based on the Close Date and Created Date we entered for each deal.

Average Deal value: The 60 deals were on average valued at $5,652.

Sales Funnel: Where do deals drop off?

Sales Funnel (Pipeline)
Sales Funnel (Pipeline)

By knowing where the deals are being lost, we can work on those stages to improve our performance.

The Funnel becomes very telling in revealing our strengths and weaknesses. If Demo is the stage we fail most often, we may need to improve the demo event to convince our prospects about the usefulness of our products.

Why are deals lost?

Lost Reason chart shows the top reasons the deals are lost. This is invaluable information to improve conversion rates.

Dashboard with Deal Lost Reason
Dashboard with Deal Lost Reason

Analysis

The Dashboard also allows analysis of performance by attribute.

Sales Pipeline Dashboard - Performance Analysis by Attribute - Change
Sales Pipeline Dashboard – Performance Analysis by Attribute – Change

This allows you to see what type of deals are being successful and thus enable you to take decisions on future deals selection process.

These attributes can be cutomized by you as explained in the previous step.

Active Pipeline

Use the Active Deals sheet to view information on active (or open) deals.

Active Pipeline Dashboard
Active Pipeline Dashboard

Open Deals: This represents the number of deals that are not closed yet. Any deal whose status is empty (neither WON nor LOST) will be considered as an open deal. Once a status is entered, it becomes a Closed Deal.

Total Value: This is the total value of all open deals. The Deal Value we enter in the Deals table will be used as such. If we win all the open deals, this is the amount we will successfully sell.

Expected Value: The reality is that we will win some deals and lose some deals. So, it is better to be realistic about our estimated sales. Expected value is the weighted sum of deal value by Win %.

Avg Days in Pipeline: This represents the number of days from Deal creation Date to current day, that the deal is open for.

 

Step 6: Track Sales Activities

The template also has an optional sheet which you can use to track interactions/activities with clients.

Sales Activities tracker
Sales Activities tracker
  1. To add a new activity that you are planning in future, just add a new row to remind you of an activity that you need to perform for a specific deal. Leave the Notes field empty. This tells the template that the activity is not completed.
    1. When the activity date is passed and the notes field is empty, the template will count that as an activity past due date. The counter at the top PAST DUE will show the number of tasks past due date.
  2. When you actually perform an activity, enter the details in the Notes field. This tells the template that the activity has been completed. The PAST DUE counter will not count the activity as Past Due anymore.

How you can use this on daily basis.

The DUE TODAY counter counts the activities with Activity Date = Today.

You can filter the table on ACTIVITY DATE to today’s date to only see activities that are due today or any specific date.

Today's Activities
Today’s Activities

When you perform one of those activities, enter in NOTES field.

 


FAQ

1.      How to filter deals?

Since we are using an Excel table, we have the default features such as filtering and sorting.

Filter Deals table by Status – Select Open Deals

In the image above, I have selected (blank) for Status. This will now filter the table to show only deals that are open.

Filter to Open Deals
Filter to Open Deals

Filtering Deals based on deal values: For example, if we want to filter only deals of values > $5000.

Deals filter greater than
Deals filter greater than

The above (Greater Than) is just an example. You can use all other types of filtering available in Excel tables. You can use the filters in any of the fields (example: Company).

2.      How to sort deals?

You can also sort deals.

Sorting deals
Sorting deals

The above shows sorting by Deal value. You can also sort by other columns such as Win % to see the highest win % deals.

3.      How to check date is entered correctly?

When entering date in Stage Dates, it is extremely important that Excel accepts them as valid date format. Excel has sometimes different formatting based on language/region/country of installation. When entering dates, please check if the date entered is accepted as date by Excel.

An example of a valid date entry and how to find if it is valid.

Valid Date Entries
Valid Date Entries

An example of an invalid date entry. This is how to spot such entries.

Invalid Date Entries
Invalid Date Entries

4.      How to use slicers in dashboard?

To select multiple values in one slicer, press ctrl key while selecting values in a slicer.

To clear all selections press the ‘Clear Filter’ button at top right of the slicer.

Clear Slicers in Dashboard
Clear Slicers in Dashboard

To change the name of a slicer, please right click on Slicer name and choose Slicer Settings. Then, change the Caption to update the name.

Change Slicer name
Change Slicer name

5.      How to modify Currency?

The template by default uses US$ as currency. To change, just select the cells where you want to change the currency. Then, press Ctrl+1.

Change Currency in Deals Table Deal Value
Change Currency in Deals Table Deal Value

Then, change the currency symbol in the dialog box as shown above.

6.      How to add extra column in DEALS sheet?

You can create a column easily by typing the new column name in cell AA5.

After that, if you need another one, enter a column name in cell AB5.

7.      What if a deal skips a stage?

If a deal skips a stage, please leave it empty. The template has calculations in place to handle this. It will assign 0 days as time taken for that stage automatically.

8.      Is the file password protected?

Yes, password protected to prevent unintentional editing. Please use indzara as password to unprotect.

9.      How to change the bar chart colors ?

In case the chart’s bar colors change to Excel’s default blue and you need to change it, or you just want to change the color to something you prefer, please follow the following steps.

Click on the chart’s bar. This should select all the bars.

Then, in the format menu, choose your preferred color as shown below.

Sales Pipeline Manager Excel Template – Support Page

Posted on

Thanks for visiting the support page for Sales Pipeline Manager Excel Template which can help simplify tracking and managing sales deals.

The following are the support articles to help you get started with using the template. If there are any questions not addressed here, please drop a comment and I will add article/video demo about that topic.

Support Articles

  • Please see this article on Important Tips before using the template
    • Input Data is always visible & can be edited easily
    • Refresh Calculations after entering or updating input data
    • Backup by saving file regularly
  • This template uses Excel tables for data entry. If you are not very familiar with Excel tables, please see this article on How to enter data using Excel Tables?
    • What is a Table?
    • Why do we use Tables?
    • Components of a Table
    • 3 Basic Data Entry Operations
    • 5 Essential Tips to avoid mistakes
  • Step by Step User Guide
    • Step by Step instructions on entering data

If you have any questions on the template, please post them in the comments section below. I will respond as soon as I can. If it will benefit others, I will also publish a blog post and video answering your question.

Recruitment Tracker – Free Google Sheet Template

Posted on

For a simple and effective solution to manage the recruiting process and truly understand its performance via effective Recruiting metrics, use this Google Sheets recruiting template to track status of the applicants (candidates) in an organized way. Get an instant view of how many applicants are in the pipeline and where in the funnel applicants are dropping off. You can also easily view the top reasons why candidates were not selected.

This template is designed for Google Sheets, but if you are looking for an Excel template, please visit Recruitment Tracker in Excel.

Recruitment Tracker Dashboard - Google Sheets- Hiring
Recruitment Tracker Dashboard – Google Sheets- Hiring

HOW TO TRACK RECRUITING IN GOOGLE SHEETS

VIDEO DEMO

Following are the steps:

  1. Copy the template and save in your Google Drive
  2. Enter Settings information
  3. Input candidate or applicant information
  4. View Dashboard
  5. Complete Data Entry after choosing candidate

STEP 1: MAKE A COPY OF THE SHEET

Click on the link above and then make a Copy of the Sheet as shown in the screenshot below.

Google Sheets Template - Make a Copy
Google Sheets Template – Make a Copy

You can save the copy in your own drive for your use. You can then share the copy with your colleagues if needed.

STEP 2: ENTER SETTINGS

In the Settings sheet, start by entering Job Title, Recruiter Name and Job Posted (or Hiring Start) Date.

Settings - Enter Job title, start date and recruiter name
Settings – Enter Job title, start date and recruiter name

The template supports 4 recruitment stages before hiring.

Customize 4 Recruitment Stages
Customize 4 Recruitment Stages

 

You can customize the stages by renaming the default stages.

If you have less than 4 stages, for example 3, please enter only 3 stages as shown below.

Example of 3 Stages in Recruitment pipeline
Example of 3 Stages in Recruitment pipeline

Enter Decline Reasons that are used by your company. These are reasons that you can assign to each candidate that didn’t get selected for the role. Having this information tracked allows understanding the reasons why the candidates are not meeting expectations of the job.

Decline Reasons - 10 values
Decline Reasons – 10 values

STEP 3: ENTER APPLICANTS’ DATA

Enter the list of applications in the DATA sheet.

Enter Application or Candidates Data
Enter Application or Candidates Data
  • When you enter a new Applicant Name, the table will auto-expand by showing gray background color.
  • Enter Applicant (Candidate) name and dates when the applicant reached each of the four stages. If the applicant does not reach a stage, leave it blank.
  • When a decision is made to not proceed with an applicant, enter CLOSED in the STATUS column.
  • Enter ‘Decline reason’ for candidates who were not selected.
  • Enter Comments if needed.
  • STAGE is a calculated field that tells which stage an applicant is. Please do not edit the formulas.

If you had only 3 stages in your recruitment process, the fourth one will show as below (Hide this column). You can hide the column so that you only see columns relevant to you.

Hide extra column
Hide extra column

STEP 4: VIEW RECRUITMENT DASHBOARD

When hiring is on-going, the Dashboard sheet will display useful information about the recruitment.

Recruitment Tracker Dashboard - Hiring
Recruitment Tracker Dashboard – Hiring

You can see the total number of applications, days since Job Posted Date, number of closed and active applications.

The Recruitment Funnel shows how the applicants flow through the recruitment stages. This is helpful to know where applicants are falling off in the process.

The Decline Reasons show the top reasons why candidates are not selected.

Active Pipeline shows the stages where the open applications are currently in.

STEP 5: DATA ENTRY AFTER CHOOSING CANDIDATE

Once a candidate has been selected, enter the HIRED APPLICANT and HIRED DATE in the SETTINGS sheet.

Hired Date and Applicant
Hired Date and Applicant

The Dashboard will now represent the complete information about this recruitment.

Recruitment Tracker Dashboard - Google Sheets - Hired
Recruitment Tracker Dashboard – Google Sheets – Hired

The status will change to HIRED and the funnel represents all applications (open and closed).

Please note that the Active applications will still represent all applications that have not been closed. So, please close all applications.

Enter Decline reasons for all applications except the hired one.

Complete data entry after hiring
Complete data entry after hiring

Final dashboard should look like this.

Final Recruitment Dashboard
Final Recruitment Dashboard

 

LIMITATION:

The template is designed to be used for tracking recruitment for only one job. If you need to track for a second job, make a copy of the file and use.

Please do not make a copy of the sheet in the same file. It will not work that way.

 

I will soon be working on a version that will support multiple jobs and positions in one file. Please post your interest in the comments below, if that template will be useful.

You may also be interested in...

2020 Excel Calendar Template – 20 Calendar Designs – Free Download

Posted on
2020 Calendar Excel Template

Download 2020 Excel Calendar Template and create your own personalized calendars in Excel. The calendar template can be used to create calendars for any year.  You can choose from 20 different calendar designs, including annual (yearly), monthly, weekly and daily calendars. All in one file, free to download.

This Excel template is designed for Microsoft Excel, but if you are looking for a Google Sheet template, please visit 2020 Calendar in Google Sheets.

2020 Calendar Template - Monthly - January 2020
2020 Calendar Template – Monthly – January 2020

TOPICS

We will discuss the following topics in this post.

  • Features of 2020 Calendar template
  • How to download this template for free?
  • Instructions on how to use the template
  • 20 Calendar designs – their features
  • Tips to further customize the template

FEATURES OF 2020 CALENDAR EXCEL TEMPLATE

  • Customize calendars by choosing the Year, Starting month and Starting day of week.
  • Enter Holidays and Personal Events to display on the Calendars
  • Automatically creates 20 Calendar designs (as this is 2020 😊)
    • 12 (of the 20) designs automatically list the Holidays and events
    • 2 (of the 20) designs allow typing events directly on the calendar itself
  • All 20 designs are ready to be printed. Can be Exported to PDF as well.
  • Easy to change formatting (font size, font, text colors, fill colors)

DOWNLOAD 2020 CALENDAR TEMPLATE

2020 Calendar Excel Template

2020 Calendar Excel Template with Sample Data


VIDEO DEMO

Watch On YouTube

 

HOW TO USE THE TEMPLATE 

Overview of steps

  1. Enter Basic settings
  2. Choose Weekends
  3. Enter Holidays
  4. Enter your personal or company Events
  5. Pick a Calendar design
  6. Print or export the calendar to PDF

Now, let’s see each step in detail.

Step 1: Enter Basic settings

Basic Settings - 2020 Excel Calendar Template
Basic Settings – 2020 Excel Calendar Template

Enter the year for which you want to build the calendar. I designed it such that you can enter any year and the calendar will still work. 🙂

By default the year begins in January. However, you can modify that to start the calendar with any month.

Change Beginning Month of Calendar
Change Beginning Month of Calendar

By default, the calendar shows weeks starting in Sunday. However, you can modify it to start with any weekday.

Start Week from any day
Start Week from any day

We can give a name to our calendar and the name will appear on some of the calendar designs automatically.

Step 2: Choose Weekends

Choose Weekends
Choose Weekends

Calendars show weekend days in red colored font. You can modify the weekends as needed for your purpose. In the image above, I have selected Saturday and Sunday as weekends. So, all Saturdays and Sundays will be shown in red font on the calendars.

Step 3: Enter Holidays

Enter the list of holidays for your company or business in the Holidays table.

Enter Holidays for 2020 Calendar
Enter Holidays for 2020 Calendar

I have provided a list of holidays in U.S. and India for reference. Please see the Holidays sheet.

I have used Excel Tables for user input. If you are new to Excel tables, please see my article on How to use Excel tables for data entry?

Step 4: Enter your personal or company Events

The template allows the user to enter events, in addition to holidays, to display on the calendars.

Enter events to display on calendar
Enter events to display on calendar

If you are using this template for personal calendar, you can enter your personal events as I have done in the above image.

If you need to create a calendar for your company, enter your company events for 2020 in the Events table.

Step 5: Pick a calendar design from the 20 designs

Based on the input provided in the previous 4 steps, the template builds the calendar designs automatically.

The 2020 calendar template offers 20 different calendar designs to choose from.

In the Pick a Calendar sheet, we can see the 20 designs.

20 Calendar Designs in 2020 Excel Calendar Template
20 Calendar Designs in 2020 Excel Calendar Template

Just click on any design on and you will be taken to the corresponding calendar sheet.

You can also directly go to any of the sheets named 1 to 20.

20 Calendar Sheets
20 Calendar Sheets

Note: If you change the sheet names of the calendars, then hyperlinks in the Pick a Calendar sheet will not work.

Step 6: Print the calendar or export to PDF

Excel’s default print menu options and Export options work as usual. If you are not familiar, it is not a problem. I explain them in detail with screenshots towards the end of this post.


20 CALENDAR DESIGNS

Let’s look at each of the 19 calendar designs.

2020 Calendar Designs
2020 Calendar Designs

Calendar Design 1

1 Page 12 Months – 4 X 3

2020 Calendar Design 1 – 1 Page 12 Months – 4 X 3
2020 Calendar Design 1 – 1 Page 12 Months – 4 X 3
  • This design prints the entire year in 1 page.
  • The 12 months are shown in 4 rows X 3 columns.
  • The Holidays, Events and Weekends are highlighted on the calendar.
  • Holiday Names and Event Names are not listed in this design.
  • There is a place to enter any notes at the bottom of the page.

When both Event and Holiday fall on the same day, Event is prioritized and event color is displayed.

Calendar Design 2

1 Page 12 Months – 3 X 4

2020 Calendar Design 2 – 1 Page 12 Months – 3 X 4
2020 Calendar Design 2 – 1 Page 12 Months – 3 X 4
  • This design prints the entire year in 1 page.
  • The 12 months are shown in 3 rows X 4 columns.
  • The Holidays, Events and Weekends are highlighted on the calendar.
  • Holiday Names and Event Names are not listed in this design.
  • There is a place to enter any notes at the bottom of the page.

Calendar Design 3

1 Page 12 Months – 2 X 6 with Events

2020 Calendar Design 3 – 1 Page 12 Months – 2 X 6
2020 Calendar Design 3 – 1 Page 12 Months – 2 X 6
  • This design prints the entire year in 1 page.
  • The 12 months are shown in 2 rows X 6 columns.
  • The Holidays, Events and Weekends are highlighted on the calendar.
  • 20 Holiday Names and 20 Event Names are listed in this design.

Calendar Design 4

1 Page 12 Months – 6 X 2 with Events

2020 Calendar Design 4 – 1 Page 12 Months – 6 X 2
2020 Calendar Design 4 – 1 Page 12 Months – 6 X 2
  • This design prints the entire year in 1 page.
  • The 12 months are shown in 6 rows X 2 columns.
  • The Holidays, Events and Weekends are highlighted on the calendar.
  • 20 Holiday Names and 30 Event Names are listed in this design.

Calendar Design 5

1 Page 12 Months – 12 X 42 with Events

2020 Calendar Design 5 – 1 Page 12 Months – 12 X 42 with Events
2020 Calendar Design 5 – 1 Page 12 Months – 12 X 42 with Events
  • This design prints the entire year in 1 page.
  • The 12 months are shown in 12 rows X 42 columns.
  • The Holidays, Events and Weekends are highlighted on the calendar.
  • 10 Holiday Names and 20 Event Names are listed in this design.

Calendar Design 6

1 Page 12 Months – 12 X 31 with Events

2020 Calendar Design 6 – 1 Page 12 Months – 12 X 31 with Events
2020 Calendar Design 6 – 1 Page 12 Months – 12 X 31 with Events
  • This design prints the entire year in 1 page.
  • The 12 months are shown in 12 rows X 31 columns.
  • The Holidays, Events and Weekends are highlighted on the calendar.
  • 10 Holiday Names and 20 Event Names are listed in this design.

Calendar Design 7

6 Pages – Planner

2020 Calendar Design 7 – 6 Pages - Planner
2020 Calendar Design 7 – 6 Pages – Planner
  • This design prints the entire year in 6 pages.
  • The 12 months are shown in 31 rows X 12 columns.
  • The Holidays, Events and Weekends are highlighted on the calendar.
  • Holiday Names and Event Names are not listed in this design.
  • Type notes directly next to each date on the calendar.

Calendar Design 8

1 Page – 54 Weeks – with Events

2020 Calendar Design 8 - 1 Page – 54 Weeks – with Events
2020 Calendar Design 8 – 1 Page – 54 Weeks – with Events
  • This design prints the entire year in 1 page.
  • The 12 months are shown in 54 rows X 7 columns.
  • The Holidays and Events are not highlighted on the calendar.
  • 20 Holiday Names and 30 Event Names are listed in this design.

Calendar Design 9

12 Pages – Monthly

2020 Calendar Template - Monthly - January 2020
2020 Calendar Template – Monthly – January 2020
  • This design prints the entire year in 12 pages.
  • The 12 months are shown as one month in each page and each month as 6 rows X 7 columns.
  • The Holidays, Events and Weekends are highlighted on the calendar.
  • Holiday Names and Event Names are not listed in this design.

Calendar Design 10

6 Pages – with Events

2020 Calendar Design 10 – 6 Pages with Events
2020 Calendar Design 10 – 6 Pages with Events
  •  This design prints the entire year in 6 pages.
  • The 12 months are shown as 2 months in each page and each month as 6 rows X 7 columns.
  • The Holidays, Events and Weekends are highlighted on the calendar.
  • 10 Holiday Names and 10 Event Names are listed for each month in this design.

Calendar Design 11

4 Pages – with Events

2020 Calendar Design 11 – 4 Pages with Events
2020 Calendar Design 11 – 4 Pages with Events
  • This design prints the entire year in 4 pages.
  • The 12 months are shown as 3 months in each page and each month as 6 rows X 7 columns.
  • The Holidays, Events and Weekends are highlighted on the calendar.
  • 5 Holiday Names and 5 Event Names are listed for each month in this design.

Calendar Design 12

3 Pages – 4 Months on each page

2020 Calendar Design 12 – 3 Pages with Events
2020 Calendar Design 12 – 3 Pages with Events
  • This design prints the entire year in 3 pages.
  • The 12 months are shown as 4 months in each page and each month as 6 rows X 7 columns.
  • The Holidays, Events and Weekends are highlighted on the calendar.
  • Holiday Names and Event Names are not listed in this design.

Calendar Design 13

2 Pages – 6 Months on each page

2020 Calendar Design 13 – 2 Pages - 6 Months each
2020 Calendar Design 13 – 2 Pages – 6 Months each
  • This design prints the entire year in 2 pages.
  • The 12 months are shown as 6 months in each page and each month as 6 rows X 7 columns.
  • The Holidays, Events and Weekends are highlighted on the calendar.
  • Holiday Names and Event Names are not listed in this design.

Calendar Design 14

12 Pages – Monthly – with Events

2020 Calendar Design 14 – 12 Pages with Events
2020 Calendar Design 14 – 12 Pages with Events
  • This design prints the entire year in 12 pages.
  • The 12 months are shown as 1 month in each page and each month as 6 rows X 7 columns.
  • The Holidays, Events and Weekends are highlighted on the calendar.
  • Holiday Names and Event Names are listed for each month in this design.

Calendar Design 15

12 Pages – Monthly – Type Events

2020 Calendar Design 15 – 12 Pages - Type your events
2020 Calendar Design 15 – 12 Pages – Type your events
  • This design prints the entire year in 12 pages.
  • The 12 months are shown as 1 month in each page and each month as 6 rows X 7 columns.
  • The Holidays, Events and Weekends are highlighted on the calendar.
  • Holiday Names and Event Names are not listed for each month in this design.
  • Type any text just below any date directly on the Calendar.

Calendar Design 16

12 Pages – Picture Calendar

2020 Calendar Design 16 – Picture Calendar
2020 Calendar Design 16 – Picture Calendar
  • This design prints the entire year in 12 pages.
  • The 12 months are shown as 1 month in each page and each month as 6 rows X 7 columns.
  • The Holidays, Events and Weekends are highlighted on the calendar.
  • Holiday Names and Event Names are not listed for each month in this design.

Right Click on image and change picture for each month. Choose from file on computer or online

Change picture in Picture calendar Choose from file on computer or online
Change picture in Picture calendar Choose from file on computer or online

Calendar Design 17

12 Pages – Quotes Calendar

2020 Calendar Design 17 – Quotes Calendar
2020 Calendar Design 17 – Quotes Calendar
  • This design prints the entire year in 12 pages.
  • The 12 months are shown as 1 month in each page and each month as 6 rows X 7 columns.
  • The Holidays, Events and Weekends are highlighted on the calendar.
  • Holiday Names and Event Names are not listed for each month in this design.

Click on placeholder text and type any text for each month. Create your own customized calendar of quotes.

Calendar Design 18

1 Page – Weekly Calendar

2020 Calendar Design 18 – Weekly Calendar
2020 Calendar Design 18 – Weekly Calendar
  • This design prints one week in 1 page.
  • Type the Week start date to generate a weekly calendar for any week.
  • Weekends are highlighted on the calendar.
  • Holiday Names and Event Names are listed in this design.
  • Up to 10 Events per day and up to 5 Holidays per day.

Calendar Design 19 (Daily Calendar)

1 Page – Daily Calendar

2020 Calendar Design 19 – Daily Agenda Calendar
2020 Calendar Design 19 – Daily Agenda Calendar
  • This design prints one day in 1 page.
  • Type the Date to generate a daily calendar for that date.
  • Weekends are highlighted on the calendar.
  • Holiday Names and Event Names are listed in this design.
  • Up to 20 Events per day and up to 5 Holidays per day.

Calendar Design 20 (Two Years Calendar)

1 Page – 2 Years Calendar

2020 Calendar Design 20 – Two years Calendar
2020 Calendar Design 20 – Two years Calendar
  • This design prints one day in 1 page.
  • Displays 2 years
  • Weekends are highlighted on the calendar.
  • Holidays and Events are highlighted.
  • Holiday Names and Event Names are not listed in this design.

TIPS TO CUSTOMIZE THE 2020 CALENDAR TEMPLATE

1. How to change formatting (colors/fonts/borders)?

One of the common reasons for making changes would be to change the formatting of the calendar. I am sure that you may have your personal favorites when it comes to colors and fonts. The template is designed so that you can make these changes yourself without negatively affecting the functionality of the calendar.

Select Cells to change formatting

Select Cells to change formatting
Select Cells to change formatting

For example, in the image above, I have selected the 2 months (January & February).

In the Home ribbon, you will see the Font section (as shown below).

How to change formatting on calendar (font, font size, font color, border, bold, italics)
How to change formatting on calendar (font, font size, font color, border, bold, italics)

We can change the font, font size, font color, fill color, borders and also apply bold and italics format.

Some of the border options require unprotecting the sheet. We will see how we can unprotect later in this post.

A couple of things to note here:

  • If you change the font color, the dates’ color will change except for the Weekends. Weekends are set up to show with red color.
  • If you change the fill color, you will notice that the days with Holidays and Events will not change. Holidays and Events are set up separately with their own colors.

I will explain now how both the above points can also be addressed.

2. How to change Event and Holiday colors?

Events and Holidays are colored automatically on the calendar using conditional formatting. As with everything with this template, it’s easy to change them too.

First, click on Conditional Formatting menu in the Home ribbon, and select Manage Rules.

Open Conditional Formatting Menu
Open Conditional Formatting Menu

The next dialog box shows the 3 rules we have used in this worksheet. Make sure that you choose ‘This worksheet‘ at the top.

Three conditional formatting rules for Weekends Holidays and Events
Three conditional formatting rules for Weekends Holidays and Events

We can modify each of the rules separately. Just click on one of the rules and then click on ‘Edit Rule’ button. In this example, I have clicked on the ‘Events’ rule.

Conditional formatting - Edit Format for a rule
Conditional formatting – Edit Format for a rule

Click on the Format button. It opens the next dialog box.

Conditional formatting - Change format for the Events dates cells
Conditional formatting – Change format for the Events dates cells

We can modify not just the fill color, but also the Number format, font and border. For this tutorial, I have just highlighted where you can change the background fill color.

You can choose one of the standard colors or click on ‘More Colors‘ button to choose any color from the spectrum.

Click OK to the dialog box and then the following open dialog boxes.

Then, you should see the changes applied to the entire calendar sheet. Save the file to save the changes.

3. How to change the order in which events/holidays appear on calendar?

Some of the calendar designs list the names of events and holidays on the calendar. They are designed to be listed in the same order in which they were entered in the Holidays and Events tables in Settings sheet.

If you had entered them in a random order in the tables, then you can easily sort them. For example, in the Events table, click on the Event Date header label. A menu pops up where you can choose ‘Sort Oldest to Newest‘.

Sort Events by Dates in Events table
Sort Events by Dates in Events table

This would sort the Events table with dates in ascending order.

You can apply the same technique to the Holidays table.

4. How to change the date format for events and holidays?

I have used the date format MMM-DD (example: Jan-05) to show the dates. I usually try to show the month in text instead of numbers as I understand that there are differences in formats used by different countries. 1/10 could be interpreted as Jan 10th or Oct 1st, depending on which country you live in.

However, you can easily change it to any format to suit your needs.

Just select the cells where you want to apply the change.

Then, press Ctrl+1 to open the Format cells dialog box.

Change date formats using Format cells option
Change date formats using Format cells option

You can type your own custom format or click on ‘Date’ in the list on the left. Then, you can choose from the standard date formats.

Choose one from the standard date formats
Choose one from the standard date formats

5. How to unprotect (unlock) sheets?

All the customization mentioned above can be made without unprotecting any sheets.

If you plan to make more modifications and you see that you are not able to, you can unprotect using indzara as password.

For more details, please see my article on how to unprotect sheets.

Please remember to protect the sheets again in order to prevent accidental editing of formulas.

6. How to print calendars?

To print, press Ctrl+P or find the Print option in the File menu.

Open Print settings with Ctrl P
Open Print settings with Ctrl P

Since the template is already pre-set for printing calendars, you can quickly review the calendar in preview and then click on Print to print.

If you need to print only a subset of pages, please enter page number range.

7. How to export calendar to PDF?

To export to PDF, choose Export in the File menu and then click on Create PDF/XPS.

Export calendars as PDF
Export calendars as PDF

In the following dialog box,

  • Choose the place in your computer to store the PDF.
  • Give a meaningful name to the PDF
  • If you need to control which pages are being exported to PDF, click on Options. Choose specific page numbers you want to export and click OK.
  • Click Publish to publish PDF.

RECOMMENDED TEMPLATE

Event Calendar Maker - Product Logo
Event Calendar Maker – Product Logo

Additional Features in Event Calendar Maker

  • 12 Frequency Types (automatically generate recurring events)
  • Categorize events into 12 Event Types
  • Choose from 15 Colors to highlight
  • 7 Calendar Designs (2 Yearly, 3 Monthly, Weekly and Daily calendars)
  • Works for any year
  • 300 events and 1200 instances
  • Control which events you would like to display by deactivating events
  • Control order of priority of events
  • Switch Off Non-business days easily

If you find the template useful, please share with your friends. Please leave a comment below to let me know your feedback. Thank you.

Have a wonderful 2020.

All Purpose Calendar Excel Template
2013 Tamil Calendar

Retention Dashboard – Excel Template – Step by Step User Guide

Posted on

In this article, we will explain step by step how to use the Retention Dashboard Excel Template. To learn more about the features of the template and to download, please visit the Product page.

OVERVIEW OF STEPS

  1. Enter data in Employees Data sheet
  2. Refresh Calculations
  3. View/Interact with 3 Dashboards

VIDEO DEMO

STEP 1: ENTER DATA IN EMPLOYEES DATA SHEET

All the input happens in one sheet. This is a table with default 1000 rows.

Employee Data Entry Table
Employee Data Entry Table

Each row in this table represents one employee’s record.

Start by entering Employee Name, Employee ID and Email information.

Employee ID is a required field.

You can rename EMAIL field and use it to store any other information as needed.

Now, let’s look at 14 other columns in this table. We can categorize them into 3 categories of attributes.

Types of Attributes

4 Employee Attributes – Date of Birth, Hire Date, Gender, Hire Source

Employee Attributes - Date of Birth, Hire Date
Employee Attributes – Date of Birth, Hire Date
  • These attributes are typically those that do not change over time for an employee
  • Hire Date and Date of Birth columns should not be repurposed. They are used for calculating Employee Tenure and Age. Modifying this will break the calculations.
  • Gender and Hire source can be renamed and repurposed. Repurposed means the column can be used to store other information. For example, you can rename Hire Source as ‘Salary Method’ and note values on how the employee gets paid (Check/Cash/Direct Deposit). They will still flow through to the dashboards and be displayed as expected.

7 Job Attributes – Location, Department, Job Type, Job Category, Job Level, Job Title, Salary

Job Attributes - Location, Department
Job Attributes – Location, Department
  • These are attributes that can be updated over time for an employee. For example, an employee can move from one Department to another.
  • 5 are set up to automatically appear on Dashboards.
  • 2 (Job Title and Salary) are available to store data in the table, but do not flow through to the dashboards.
  • All 7 attributes can be customized. They can be renamed and repurposed.

3 Exit Attributes – Exit Date, Exit Type, Exit Reason

Exit Attributes - Exit Date, Exit Type and Reason
Exit Attributes – Exit Date, Exit Type and Reason
  • These attributes are required to be entered only for employees who have left the company. They can be left blank for active employees.
  • Exit Date and Exit Type cannot be repurposed as calculations are based on those two columns.
    • Exit Type requires Voluntary and Involuntary as values.
    • Also called as Termination Type in some companies.
  • Exit Reason can be repurposed. It will appear on Exit Dashboard automatically.
    • Also called as Termination reasons in some companies.

In General values are not limited in any column except Exit Type column.

Exit Type values - Voluntary, Involuntary
Exit Type values – Voluntary, Involuntary

When to enter what data?

  • To begin: When you first start using the template, you will enter all existing employees in your organization.
  • After that, when new employees join, you will enter that new employee’s information in the table.
  • When employees move from one department to another or from one location to another, you can just update the specific employee’s data in the table.
  • When employees leave the company, enter the Exit Attributes for those employees.

How to rename and use customizable fields

In the Employees Data sheet, just click on the column header and rename it.

How to add more attributes

Adding new columns
Adding new columns

You can click on cell R3 and type a new column name. This will add a new column. You can add any number of such columns to track information about employees.

Note: new columns will not appear on dashboards automatically.

Step 2: REFRESH CALCULATIONS

The template uses pivot tables and hence the calculations have to be refreshed every time data is added or edited in the Employees Data sheet.

From the DATA ribbon, click on Refresh All.

Refresh Calculations - Data - Refresh All
Refresh Calculations – Data – Refresh All

STEP 3: DASHBOARDS

Once the data is entered and refreshed, we can view the 3 dashboards.

The template provides three automated dashboards:

Retention Dashboard

Retention Dashboard presenting 7 KPIs with trend over the last 12 months.

Employee Retention Dashboard
Employee Retention Dashboard

7 KPIs

Following 7 KPIs are automatically calculated by the template

  • Active Employees: Number of employees active with the company
  • Hires: Number of new employees hired
  • Exits: Number of employees who have left the company
  • Turnover Rate: (Number of Exits/Avg. Number of active Employees during the period) * 100
  • Retention Rate: (Number of Exits from Employees active at the Beginning of the period /Number of Employees active at the beginning of the period) * 100
  • Tenure of employees: Average duration of employment of active employees
  • Tenure of Exit Employees: Average duration of employment at the time of exit

Snapshot Dashboard

Snapshot Dashboard provides interactive view of # of Employees and Avg. Tenure by 7 different employee and job attributes.

Page 1 shows the number of employees and breakdown by various attributes.

Company Snapshot Dashboard
Company Snapshot Dashboard

Page 2 shows the Average Tenure of employees and breakdown by various attributes.

Company Snapshot Dashboard - Tenure
Company Snapshot Dashboard – Tenure

There are 10 Slicers available to drill down.

Dashboard Slicers
Dashboard Slicers

To select multiple values in a slicer (for example, Hire Source LinkedIn and Indeed) just press Control while clicking on the specific values.

Retention Dashboard Excel Template - Slicers in Dashboard
Retention Dashboard Excel Template – Slicers in Dashboard

Exit Dashboard

Exit Dashboard provides interactive view of exit data. Exit Type (Voluntary/Involuntary) and Exit Reason can be quickly viewed to understand how, why and when employees are leaving the company.

Employee Exit Dashboard
Employee Exit Dashboard

12 slicers are available to drill down or filter.

This dashboard represents only employees who left the company.

Modifying Dashboards

  • Charts: If you do not need one of the charts in the Dashboard, just remove that one chart by selecting and deleting, without impacting the rest of the dashboard.
  • Slicers: If you do not need one of the slicers in the Dashboard, just remove that one slicer by selecting and deleting, without impacting the rest of the dashboard.
  • Renaming Slicers: You can rename the slicers by changing the settings of the slicer.
    • Right click on a slicer and choose ‘Slicer Settings’
    • Modifying Slicer Settings
      Modifying Slicer Settings

      Rename the Caption

      • Renaming Slicer
        Renaming Slicer
  • Resizing Charts: Depending on the data in your organization, you may need certain charts and slicers to be long or short. Not a problem. Just unprotect the sheet and resize the chart to fit your needs. It will not impact the dashboards in any negative way.
Retention Dashboard - HR Excel Template - Modify Charts
Retention Dashboard – HR Excel Template – Modify Charts

How to Print or Export to PDF

The Dashboards are set up to be ready for print. Press Control+P or use Excel’s file menu to print. You can also use Excel’s default Export to PDF option and export as PDF.

Unprotecting Sheets

You would not need a password to use the template. The template performs the default functionality without having the need to unprotect protected sheets. However, if you would like to modify functionality, you can use indzara as password to unprotect and then make changes.

How to unprotect sheet?

Whenever you unprotect the sheet, please protect it after making the adjustments.

Extending beyond 1000 employee limit

The template has a hidden sheet named HELP which is limited to 1000 rows. That table needs to be extended down to more rows. Then, automatically the dashboards will pick up more than 1000 rows entered in the Employees Data sheet.

 

If there are any questions on this template, please post in the comments section below.

Retention Dashboard – Excel Template – Support Page

Posted on

Thanks for visiting the support page for Retention Dashboard Excel Template which can help simplify employee retention tracking in your organization.

The following are the support articles to help you get started with using the template. If there are any questions not addressed here, please drop a comment and I will add article/video demo about that topic.

Support Articles

  • Please see this article on Important Tips before using the template
    • Input Data is always visible & can be edited easily
    • Refresh Calculations after entering or updating input data
    • Backup by saving file regularly
  • This template uses Excel tables for data entry. If you are not very familiar with Excel tables, please see this article on How to enter data using Excel Tables?
    • What is a Table?
    • Why do we use Tables?
    • Components of a Table
    • 3 Basic Data Entry Operations
    • 5 Essential Tips to avoid mistakes
  • Step by Step User Guide 
    • Step by Step instructions on entering data

If you have any questions on the template, please post them in the comments section below. I will respond as soon as I can. If it will benefit others, I will also publish a blog post and video answering your question.

PTO (Paid Time Off) Balance Calculator for Hourly Employee

Posted on
Employee Report - PTO Calculator - Hourly Employees - Screenshot

If you are looking for a Simple & Effective solution to calculate and track Paid-Time-Off (PTO) for your hourly employee, you have reached the right place. You can download this free Excel template to track and calculate Employee’s PTO accrual balances based on hours worked by the employee.

If you need a PTO Calculator that calculates accrual based on employee tenure, please see PTO Calculator (Salaried Employees)

If you are familiar with PTO calculation, you would be aware that though it appears to be simple from the outside, accounting for various scenarios and PTO policies make such calculation complex and not easy to implement.

For more details on key components of a PTO policy and how to calculate PTO balances, please read PTO Policy Factors and Balance Calculation

Don’t worry. This Excel template makes all that simple to implement, as all the hard work is already done. You enter the inputs of your PTO policy and are ready to track PTO balance automatically within a few minutes.

Employee Report - PTO Calculator - Hourly Employees - Screenshot
Employee Report – PTO Calculator – Hourly Employees – Screenshot

In this article, we will cover the following topics.

  1. Overview of Features of the Excel template
  2. Download link to the free Excel template
  3. Overview of how to use the template
  4. Detailed Step by Step user guide on how to track PTO for your hourly employee

KEY FEATURES OF THE EXCEL TEMPLATE

  • Options available covers most common business PTO policy scenarios
  • Very flexible and easy to customize for your specific business needs
  • Automatically calculates current PTO balance and projected future balance
  • Interactive and printable PTO Report showing all details in a simple calendar view
  • Supports tenure-based accrual rate tiers
  • File is designed for one employee only. Make a copy of workbook to use for second employee.
  • Tracks PTO balance for at least 2 years (and can be reset in new file and extended).

FREE DOWNLOAD

PTO Calculator (Hourly Employee) Excel Template

REQUIREMENTS

Microsoft Excel 2010 or newer

LIMITS

  • Tracking PTO for 1 employee in a file. Please make copy of the entire file to track second employee.
  • 500 Leave Entries

HOW TO USE THE EXCEL TEMPLATE

VIDEO DEMO

OVERVIEW OF STEPS

Before we get into all the details, I want to provide an overview of the 5 simple steps in using the template.

  1. Enter employee details and PTO policy inputs in the PTO POLICY SETTINGS sheet
    • Review first set of accrual days to ensure information is correct. Ensure there are no errors in the data entry validation.
  2. Enter your company’s Weekends and Holidays in WEEKENDS HOLIDAYS Sheet
  3. When employee works different hours (more or less) than the default daily hours, enter them in the HOURS WORKED sheet
  4. When employee takes PTO, enter PTO info in the PTO USED sheet
  5. View PTO balance and balance trends in the PTO REPORT sheet

STEP BY STEP GUIDE

Let’s start from the beginning – in the PTO POLICY SETTINGS sheet.

Settings - Employee Details and Start Date
Settings – Employee Details and Start Date

STARTING DATE

Enter the date from which you would like to use this template to track PTO. There are two key purposes for this date. 1) The template will only calculate PTO from this date. 2) The Starting Balance (which we will discuss soon) will be the balance at the end of the day before this Starting Date.

Employee Details

EMPLOYEE NAME

Enter name of employee for whom we will be tracking and calculating PTO balance. This will automatically then appear in the PTO Report.

HIRE DATE

Enter the date when employee joined the company. Even if you have been tracking PTO using some other tool and now want to switch to use this template, enter the actual hire date of the employee. I will soon explain how you can carry over balance from your previous tool.

STARTING BALANCE

Here, we can enter balance we carry over for employees whom you have been tracking PTO from other tool before you switch to using this template.

For example, if the employee started working in 2017, but you started to switch to this template on July 3, 2019, then you can enter the starting balance as of end of July 2, 2019. Any PTO used from July 3, 2019 will have to be entered in the template.

DEFAULT HOURS WORKED

This is to record how many hours this employee works every working day. This option is given to reduce data entry. Let’s say the employee works usually 8 hours a day but occasionally he/she may work less or more than 8 hours. You can enter 8 as default working hours. On all days except holidays and weekends, the template will assign the default hours worked by employee.

You can then enter adjustments for specific dates when the employee did not work 8 hours.

Instead of entering the actual hours every day, the template allows setting the default hours once and then only entering work hours when it deviates from the default. Less data entry. Simple and Effective.

If your employee has varying hours daily, then you can enter 0 hours as default and choose to enter actual hours for every day (I will show where to enter that information in the upcoming steps below).

That’s how flexible the template is. You can make it work to suit your business requirements.

Now, let’s get into the PTO Policy. Fun Stuff!

Here is a sample policy.

PTO Policy - Hourly Employee
PTO Policy – Hourly Employee

There are several components of a PTO policy. We will go through one by one.

PTO ACCRUAL PERIOD

This is to inform how we often accrue the PTO. We have 4 options here: Weekly, Every 2 Weeks, Twice a Month and Monthly.

PTO Accrual Period - Options - Weekly, Every 2 Weeks, Twice a Month, Monthly
PTO Accrual Period – Options – Weekly, Every 2 Weeks, Twice a Month, Monthly

Weekly

This option means that employee can accrue PTO balance once a week, provided he/she has worked enough hours (cumulatively) to meet the Accrual rate policy.

PTO Policy - Weekly Accrual
PTO Policy – Weekly Accrual

In the above screenshot, you can see that a new input (First Accrual Date) becomes necessary for weekly policy. Since a weekly accrual can happen on Fridays in some companies and Mondays in some companies, we provide the option to enter a specific date when the first accrual should happen. Here, we entered 5th July 2019 – which is a Friday.

So, the template will set accruals every Friday beginning on 5th July 2019. 5th July, 12th July, 19th July…..

Every 2 Weeks

Entering an Every 2 Weeks policy is similar to weekly. The template, in this case, will only set accruals every 2 weeks.

PTO Policy - Every 2 Weeks Accrual
PTO Policy – Every 2 Weeks Accrual

If the first accrual date is 5th July 2019, the template will automatically set up accruals every other Friday – 5th July, 19th July, 2nd Aug, 16th Aug…..

Twice a Month

Now, let’s move to Twice a Month option. Here, we can enter the two dates of the month the accrual will happen.

PTO Policy - Twice a month Accrual
PTO Policy – Twice a month Accrual

For example, in the screenshot above, we have chosen 1st and 15th of every month.

You have control over the specific 2 days of the month. The template even supports ‘Last Day’ option.

PTO Policy - Twice a month Accrual - Last Day
PTO Policy – Twice a month Accrual – Last Day

For example, the above scenario will set up accrual on 15th of every month and Last Day of every month. If the month has 28 days or 29 days or 30 days or 31 days, the template will automatically figure out the last day and set up accrual for that day.

Monthly

I am sure that by now, you see the pattern here. For monthly, we can set the day of month that we want accrual to happen every month.

PTO Policy - Monthly Accrual
PTO Policy – Monthly Accrual

For example, as shown above, we can do accruals on 1st of every month.

Instead of 1st, you can type in any day of the month. You can also choose ‘Last Day’ of month.

PTO Policy - Monthly Accrual - Last Day
PTO Policy – Monthly Accrual – Last Day

REVIEW FIRST ACCRUAL

We need to check and ensure that the data provided in the inputs are correct and result in a valid accrual policy. The template shows the first set of accrual dates.

Review First Set of Accrual Days
Review First Set of Accrual Days

In the above screenshot, there are no errors. The accrual dates are weekly from 5th July. (Probationary period setting is not considered in this list of dates and the dates shown will include dates in any probationary period you may have set).

The template has in-built data validations to identify if the inputs do not align.

For Weekly and Every 2 Weeks options, First Accrual Date should be within 1 or 2 weeks from the Starting Date. Otherwise, you will see an error message like this one.

Data Validation - First Accrual Date should be within 1 week of Starting Date
Data Validation – First Accrual Date should be within 1 week of Starting Date

In these cases, update the First Accrual Date input to ensure that it is correct.

Now let’s discuss Rollover Policy.

ANNUAL PTO ROLLOVER POLICY

The template provides three options.

Rollover policy options
Rollover policy options
  1. Zero Rollover: Employee loses all the PTO balance at the end of the year and starts from scratch in the next year.
  2. Rollover Limit: We can set a limit on how many hours are carried over.
  3. Unlimited Rollover: Here the employee does not lose any PTO and will carry over everything to next year.

With rollover policies, there is another variation. Some companies may apply rollover at calendar year change 1st Jan of every year or on work anniversary dates (which vary for each employee). You can easily change that setting.

Rollover Timing options
Rollover Timing options

PROBATIONARY PERIOD

Enter the duration of the Probationary period in days.

Settings - Probationary Period, Maximum Allowed Balance
Settings – Probationary Period, Maximum Allowed Balance

For example, in the screenshot above, employee does not earn any PTO during the first 90 days of employment.

MAXIMUM ALLOWED PTO BALANCE

You can set a limit on maximum balance at any time, to ensure that there is a limit to PTO accrual. You can set the amount (hours)in the Maximum Allowed PTO Balance field.

ROUNDING IN PTO BALANCE

When the template calculates balances and display in the report, you can control the precision by just entering the number of decimals.

PTO ACCRUAL RATE

This defines how much PTO hours are earned by the employee for hours worked.

Accrual Rate Based on Tenure - One Policy
Accrual Rate Based on Tenure – One Policy

For example, in the above screenshot, the employee would earn 1 hour of PTO for every 40 hours worked.

Since the tenure completed is 0 and we have only tier, the policy will apply to all employees regardless of tenure.

There should always be a record with 0 tenure completed

  • If the employee worked 39 hours, he/she would not earn any PTO. But once the 40th hour is completed, he/she will earn 1 hour of PTO.
  • The 40 hours do not have to be in the same week or month.

If in your company, the employee would earn 4 hours of PTO for every 160 hours worked, then you would enter 4 and 160 in the corresponding cells.

Accrual Rate Based on Tenure - One Policy - Multiples
Accrual Rate Based on Tenure – One Policy – Multiples

In this scenario, for the first 159 hours, the employee does not earn anything. But once the 160th hour of work is completed, then it accrues 4 hours of PTO. Essentially, the employee accrues in multiples of 4 hours.

You can also have a tiered structure where you reward loyal employees with higher PTO accrual rate.

Accrual Rate Based on Tenure - Tiered Policy
Accrual Rate Based on Tenure – Tiered Policy

In the above screenshot, employees with less than 12 months tenure will earn 1 PTO hour per 40 hours worked. Employees between 12 to 24 months tenure will earn 2 PTO hours for same 40 hours worked, while employees above 24 months tenure will earn 3 PTO hours per 40 hours worked.

WEEKENDS & HOLIDAYS

Now, we are ready to enter the company weekends and holidays.

Weekends
Weekends

Choose TRUE for days that are considered weekends in your company.

For example, in the above screenshot, we have set Saturday and Sunday to be weekends. Hence the employee will not by default work any hours during those days. Also, if an employee takes PTO that spans across weekends, the weekend days will not be counted as PTO taken and thus not reduce the PTO balance.

Holidays work similarly, except here you must enter each holiday individually. This is done in the HOLIDAYS sheet.

Holidays
Holidays

For example, in the below screenshot, we have set Saturday and Sunday to be weekends. Hence the employee will not by default work any hours during those days. Also, if an employee takes PTO that spans across weekends, the weekend days will not be counted as PTO taken and thus not reduce the PTO balance.

Read how to enter and delete data in Excel tables

Now we have gone through the various PTO policy input options in the PTO calculator. These settings have to be entered only once for an employee.

HOURS WORKED

In this template entering data on hours worked is simple, as we have already set default hours worked initially. On days when employee worked hours different than the default, then we enter them in the HOURS WORKED sheet.

Enter Hours Worked by Employee
Enter Hours Worked by Employee

It is important to note that you should only enter the adjustment.

For example, if the default hours worked is 8 and if you enter an adjustment of -8, that means the employee worked 0 hours on that day.

If the adjustment is 1, then the employee worked 9 hours on that day.

PTO USED

When an employee takes PTO or plans to take PTO, you can enter that in the PTO USED sheet.

Enter PTO Days taken by employee
Enter PTO Days taken by employee

We can enter date ranges to enter multi-day vacation. Even if it is a single day vacation, please enter both start and date as the same date. All 3 fields (PTO START DATE, PTO END DATE, PTO HOURS) are required entries here.

Multi-day Vacations
It is important to note here that the PTO Hours you enter is the daily value not total for multiple days.

In the above example, 1 hour of PTO for each of the 2 days (May 7th, May 8th) – in total 2 hours – will be subtracted from the PTO balance, assuming those dates are not Weekends and Holidays in your company. However, you should enter 1 and not 2 in PTO Hours column.

Now that we have entered the necessary data inputs, we are ready to view the PTO report.

PTO REPORT

Employee Report - PTO Calculator - Hourly Employees - Screenshot
Employee Report – PTO Calculator – Hourly Employees – Screenshot

Let’s take the report in two sections. The top section shows the summary.

Employee Report - PTO Calculator - Hourly Employees - Summary
Employee Report – PTO Calculator – Hourly Employees – Summary

BALANCES

By default, the report shows today’s PTO balance for the employee. The Projected Balance on a future date can also be shown. You can modify the date and can view PTO balance at any date. That will consider future data (work hours, PTO used, holidays and weekends).

POLICY DETAILS

Policy Details along with key dates are shown so that when you print the report, the policy details are also clearly visible.

The bottom section provides an interactive calendar.

Employee Report - PTO Calculator - Hourly Employees - Calendar
Employee Report – PTO Calculator – Hourly Employees – Calendar

DATES

The Calendar (12 month) view is to show specific details on each date for a period you choose. You can enter the FROM and TO dates. The calendar will then display information for that specific period.

DISPLAY

The display inside the calendar shows the values for each date and you can control what is displayed.

Calendar Display Choices
Calendar Display Choices

You can display Worked (Number of Hours worked by employee on the day) or Balance Change (PTO Balance Accrued on the day) or PTO Used (PTO used by employee on the day).

WORKED

Employee PTO Report - Calendar Hours Worked
Employee PTO Report – Calendar Hours Worked

You can view the number of hours worked by employee on each day. This will include any work hours adjustments you entered.

BALANCE CHANGE

This shows the accrual days and the amount of PTO Balance change (hours) on those accrual days.

Employee PTO Report - Calendar - Balance Change
Employee PTO Report – Calendar – Balance Change

PTO USED

Employee PTO Report - Calendar - PTO used
Employee PTO Report – Calendar – PTO used

This will reflect the dates when the employee takes PTO and the number represents the number of hours of PTO used.

MONTHLY BALANCES

On the right side you can view the Monthly starting PTO Balance and Monthly Ending PTO Balance. Please note that all balances reflect at end of day.

MONTH-END BALANCE TREND

At the bottom of the report, the month-end balance trend chart shows the balances month over month.

PRINT OR EXPORT

You can Print the report or export to PDF and share.

BALANCE ADJUSTMENTS

If you would like to add or remove PTO to the balance outside the PTO policy settings you have entered, then you can use the Adjustment table. This allows you to add to PTO balance (enter positive value) or reduce from PTO balance (enter negative value).

For example, if you would like to reward employee with extra PTO, you can enter here as a positive adjustment.

Balance Adjustments
Balance Adjustments

This sheet can be ignored if you don’t need to make such adjustments.

 

I hope this free Excel template is useful to track Paid Time Off (PTO) for hourly employee in your organization.

Does this template address policy settings in your organization? Does this save time in calculating PTO balances? Do you have any suggestions on how this template can be improved further?

Please provide your feedback in the Comments below. Thanks for your support.

You may also be interested in...

Employee PTO Tracker - Salaried
Employee Timesheet Exclel Template

PTO Policy for Hourly Employees & How to calculate PTO Balance

Posted on
PTO Policy for Hourly Employees

In this article, we cover the common components of a PTO (Paid Time Off) policy for hourly employees. PTO Policy is an important part of a company’s Employee Benefits package. It is one of the factors being considered by candidates when they decide to join a company. An effective employee PTO policy will benefit both employees and employer.

We will go over each of the PTO policy components, explain them in detail and show examples to illustrate how PTO balances are calculated.

COMPONENTS OF A PTO POLICY

Typically, a PTO policy would address the following aspects and they are implemented in the templates from indzara.com.

  1. Probationary Period: Is there a probationary period for an employee where PTO is not accrued?
  2. Accrual Period: How frequently and when does the PTO accrual happen?
  3. Accrual Rate: How much PTO does an employee accrue during each period? Does it change by Tenure?
  4. Rollover Policy: Can balance from one year be rolled over to the next year? If so how much and when?
  5. Max Balance: Is there a maximum balance that an employee can carry at any time?

Now, let’s look at each of them in detail.

1. PROBATIONARY PERIOD

In some companies, employees may not be awarded any PTO for the first X number of days of employment. For example, employee does not earn any PTO during the first 90 days of employment. This period may be called as Probationary period. Work done during this period will not result in any PTO Accrual.

2. PTO Accrual Period

This is to define how often PTO is accrued. Most common options are Weekly, Every 2 Weeks, Twice a Month and Monthly. This is typically aligned with your pay periods.

Weekly: This option means that employee will accrue PTO once a week, provided he/she has worked enough hours (cumulatively) to meet the Accrual rate policy requirements. For example, accruals happen every Friday.

Every 2 Weeks: Every 2 Weeks policy is similar to weekly except it accrues every other week. For example, every other Friday.

Twice a Month: Accruals can happen 1st and 15th of every month. Or 15th and Last day of every month.

Monthly: For example, we can do accruals on 1st of every month or ‘Last Day’ of every month.

3. PTO Accrual Rate

This defines how much PTO hours are earned by the employee on each accrual day.
To explain this, let’s take an example policy: Employee would earn 1 hour of PTO for every 40 hours worked.

  • If the employee worked 39 hours, he/she would not earn any PTO. But once the 40th hour is completed, he/she will earn 1 hour of PTO.
  • The 40 hours do not have to be in the same accrual period.
  • The employee accrues in multiples of 1 hour.

Another policy example: Employee would earn 4 hours of PTO for every 160 hours worked.

  • For the first 159 hours of work, the employee does not accrue anything. But once the 160th hour of work is completed, the employee accrues 4 hours of PTO.
  • The 160 hours are accumulated over periods.
  • The employee accrues in multiples of 4 hours.

Companies may also have a tiered structure, where loyal employees (with more tenure) will earn PTO at a higher rate.
For example,

  • Employees with <= 2 years of tenure at the company may receive an accrual rate of 1 hour of PTO for every 40 hours worked.
  • Employees with >2 years of tenure at the company may receive an accrual rate of 2 hours of PTO for every 40 hours worked.

You can have more than 2 tiers as well. This may lead to improved employee loyalty.

4. ANNUAL PTO ROLLOVER POLICY

As an employee continues to accrue PTO every period, the balance keeps growing, assuming there are no PTO/vacations taken. Employees are encouraged to take regular time off to maintain a healthy work-life balance.

Also, companies may consider remaining PTO balance as cash that needs to be paid to employee if employee leaves the company. So, very high balance could mean more cash out the door for the company. These are reasons why there is usually a rollover policy.

This policy setting determines how many hours of PTO the employee can carry over from one year to the next year.

Let’s look at three flavors of Rollover policy.

  1. Zero Rollover: Employee loses all the PTO balance at the end of the year and starts from scratch in the next year.
  2. Rollover Limit: We can set a limit on how many hours are carried over. This is the most common practice among companies.
  3. Unlimited Rollover: Here the employee does not lose any PTO and will carry over everything to next year. This is unusual for a company.

ROLLOVER TIMING

With rollover policies, there is another aspect. Some companies may apply rollover at calendar year change (1st Jan of every year for all employees) or on work anniversary dates (which vary for each employee).

5. MAXIMUM ALLOWED PTO BALANCE

The rollover limit only applies to the end of the year balance. Some companies can set an additional limit on maximum balance at any time during the year, to ensure that there is a limit to PTO accrual. This would mean the employees may have to take regular PTO throughout the year, instead of accumulating towards year end.

This is referred to as the Maximum Allowed PTO Balance.

ILLUSTRATION

Now that we defined the key factors, let’s take an example employee and illustrate how these components work and how to calculate PTO policy for an hourly employee.

EMPLOYEE DETAILS

Let’s assume an employee with the following details for this illustration.

  • Works 8 hrs every weekday
  • Hire date: 3 July 2019
  • Probationary Period: 90 Days
  • Rollover limit: 10 Hours on 1st Jan
  • Max PTO Balance: 30 Hours
  • PTO Taken: 1 hour on 25 Oct 2019, 7 May 2020 and 8 May 2020
  • Accrual Rate:
    o <= 9 Mths Tenure: 1 Hour PTO per 40 Hours Worked
    o >9 Mths Tenure: 2 Hours PTO per 40 Hours Worked

PROBATIONARY PERIOD

As the employee was hired on 3rd July 2019, the employee will not be eligible for PTO accrual until Sep 30th.

First day of eligibility will be Oct 1, 2019

Probationary Period
Probationary Period

The image above shows the number of hours worked by employee, that are used for calculating PTO accrual.

Though the employee may have worked during the probationary period, the work does not lead to PTO accrual. Only work done from Oct 1st will be used for PTO accrual calculations.

ACCRUAL PERIOD

Since our employee accrues PTO every week, you can see the accrual calendar below where accrual happens on Oct 4, 11, 18, 25…..

First accrual day is Oct 4th. 

Weekly Accrual Period in Calendar
Weekly Accrual Period in Calendar

The numbers above refer to how much PTO balance changed each period. We will get to how we calculated that shortly.

ACCRUAL RATE

We will begin with simpler calculations and layer in more complexity later.

WEEK 1

Let’s take the first accrual period ending Oct 4th.

There are 2 key inputs needed. Hours Worked and PTO Hours used.

  • Employee worked for 32 hours from Oct 1st (first eligible date) to Oct 4th (Friday).
  • Employee didn’t take any PTO.

For every period, we calculate 3 output.

  1. PTO Accrued in Period: How much PTO has employee earned in this period alone?
  2. Unaccrued Work Hours: How much hours did the employee work that have not converted to PTO yet?
  3. PTO Balance at end of period: How much is the PTO Balance at end of period, available for employee to use?

 

PTO Accrual Rate Calculation - Week 1
PTO Accrual Rate Calculation – Week 1

PTO Accrued in Period

= (Hours worked in this period + Any unaccrued Work Hours from Previous period) * Accrual Rate

= (32+0)* Accrual Rate = 32 Hours *Accrual Rate

= 0 hours of PTO accrued + 32 work hours unaccrued.

PTO Accrued in Period is 0 since employee has not reached 40 work hours yet. However, the 32 hours should count towards the following week’s balance calculation. So, we store that in the ‘Unaccrued Work Hours’ column.

PTO Balance

= Previous Period Balance + PTO Accrued in Period – PTO Used

= 0 + 0 – 0

= 0 hours

PTO Balance for the employee at the end of Oct 4th is 0 as the previous balance is 0 and no PTO was used during the period.

WEEK 2

Let’s move to the next week – ending on Oct 11th.

Inputs:

  • Employee worked for full 40 hours.
  • No PTO taken.
PTO Accrual Rate Calculation - Week 2
PTO Accrual Rate Calculation – Week 2

PTO Accrued in Period

= (Hours worked in this period + Any unaccrued Work Hours from Previous period) * Accrual Rate

= (40+32)* Accrual Rate = 72 Hours *Accrual Rate

= 1 hour of PTO accrued + 32 work hours unaccrued.

PTO Accrued in Period is 1 since employee has reached 40 work hours cumulatively. The 32 unaccrued work hours from previous week get added to total hours worked.

PTO Balance

= Previous Period Balance + PTO Accrued in Period – PTO Used

= 0 + 1 – 0

= 1 hour

WEEK 3

Now, third week comes along.

Inputs:

  • Employee only worked for 8 hours in that week.
  • No PTO Taken
PTO Accrual Rate Calculation - Week 3
PTO Accrual Rate Calculation – Week 3

PTO Accrued in Period

= (Hours worked in this period + Any unaccrued Work Hours from Previous period) * Accrual Rate

= (8+32)* Accrual Rate = = 40 Hours *Accrual Rate

= 1 hour of PTO accrued + 0 work hours unaccrued.

PTO Balance

= Previous Period Balance + PTO Accrued in Period – PTO Used

= 1 + 1 – 0

2 hours

WEEK 4

Now, on to the fourth week .

Inputs:

  • Employee takes 1 hour of PTO and works only 39 hours.

Though the employee does get paid for 1 hour of PTO, for our PTO accrual calculation that 1 hour of PTO will not be used. Essentially, an employee’s PTO time does not earn him more PTO accrual.

Accrual Rate Calculation - Week 4
Accrual Rate Calculation – Week 4

 

PTO Accrued in Period

= (Hours worked in this period + Any unaccrued Work Hours from Previous period) * Accrual Rate

= (39+0)* Accrual Rate = 39 Hours *Accrual Rate

= 0 hours of PTO accrued + 39 work hours unaccrued.

PTO Balance

= Previous Period Balance + PTO Accrued in Period – PTO Used

= 2 + 0 – 1

1 hour

WEEK 5

Now for the 5th week.

Inputs: Employee does overtime and works for 41 hours. No PTO Taken.

PTO Accrual Rate Calculation - Summary
PTO Accrual Rate Calculation – Summary

PTO Accrued in Period

= (Hours worked in this period + Any unaccrued Work Hours from Previous period) * Accrual Rate

= (41+39)* Accrual Rate

= 80 Hours *Accrual Rate

= 2 hours of PTO accrued + 0 work hours unaccrued.

PTO Balance

= Previous Period Balance + PTO Accrued in Period – PTO Used

= 1 + 2 – 0

3 hours

SUMMARY

In summary,

Employee worked 160 hours in 5 weeks, earned 4 hours of PTO (1 hr per 40 hours worked) and used 1 hour of PTO. The Balance as of Nov 1st is 3 hours of PTO.

Key Takeaways:

  •  PTO Accrual is based on cumulative work hours and not just hours worked in that period.
  •  Unaccrued work hours are tracked to be used for next week’s calculation
  • PTO used will be deducted from balance
  • PTO hours do not yield PTO accrual
  • Formulas:
    • PTO Accrued = (Hours worked in this period + Any unaccrued Work Hours from previous) * Accrual Rate
    • PTO Balance = Previous Period Balance + PTO Accrued in Period – PTO Used

ROLLOVER POLICY

Rollover limit was 10 hours and it was applied on calendar year end. So, the first accrual date in Jan 2020 (Jan 3rd, 2020) will see the rollover policy implemented.

The formula we used for calculating balance in the previous section gets an upgrade only for rollover periods.

Assuming balance as of Dec 27th was 11 and 30 unaccrued work hours.

Rollover Policy - Calculation
Rollover Policy – Calculation

Calculating the balance for Jan 3.

PTO Accrued

= (Hours worked in this period + Any unaccrued Work Hours from previous) * Accrual Rate

= 40 * Accrual Rate

= 1 Hour of PTO Accrued + 0 unaccrued work hours

PTO Balance

= min(Rollover limit, Previous Day Balance) + PTO Accrued in Period – PTO Used
= Min (10,11) + 1 – 0
= 10 + 1 – 0

= 11 Hours

Only 10 hours get carried over from Dec 27th Balance due to the rollover limit of 10 hours. If the rollover policy was 0 rollover, then all 11 hours will be lost. In case of unlimited rollover policy, all 11 hours will be carried over.

So, the new balance as of Jan 3rd is 11 hours.

From Jan 10th again, the employee will carry over the balance over to the next week as usual without any rollover limit. The limit is applied only on rollover windows once a year.

TENURE BASED ACCRUAL RATE

As the employee continues to work at the company, he/she reaches the 9 month tenure period where the accrual rate increases to 2 hours of PTO for every 40 hours worked.

Employee completes 9 months on Apr 2nd and on Oct 3rd will be eligible for the new rate.

Tenure Based Accrual Rate calculation
Tenure Based Accrual Rate calculation

As shown above, the employee earned 1 hour per 40 hours worked on Mar 27.

From Apr 3rd, the employee earns 2 hours of PTO for 40 hours worked.

Note: New rate applies to all hours worked during transition week (Mar 28th to Apr 3) regardless of the exact date when the rate transition happened.

MAX PTO BALANCE

Now, for the last setting: MAX PTO BALANCE of 30 hours.

The employee continues to earn more balance over time and on Apr 24th the balance reaches 30 hours.

MAX PTO Balance calculation
MAX PTO Balance calculation

The employee worked 40 hours as usual during the next week.

PTO Accrued = (Hours worked in this period + Any unaccrued Work Hours from previous) * Accrual Rate

= 40 * Accrual Rate = 1 Hour of PTO Accrued + 0 unaccrued work hours

PTO Balance = min (Max PTO Balance , (Previous Day Balance + PTO Accrued in Period – PTO Used))

= Min (30,30+1-0) = Min (30,31) = 30 Hours

Note: Though the PTO accrued should be 1 hour, it is represented as 0 in the image above, in order to avoid confusion. Employee does not actually get the benefit of that 1 hour as it will exceed the Max PTO Balance Policy limit.

When the employee uses 2 hours of PTO during the week ending May 8th, the PTO balance becomes 28 hours.

In the following week, the employee will restart accruing new PTO as 28 is less than 30 (max limit).

CONCLUSION

I hope the above example illustrates how PTO balance can be calculated for hourly employees. The 5 key policy factors we discussed can become complex to implement due to various flavors possible and the calculations are not very easy to build. I spent quite a bit of time writing the formulas to accommodate the various scenarios.

I am happy to inform that you don’t have to spend your time building these formulas. I will be publishing a free Excel Template that comes pre-built with the formulas. You can implement your PTO policy and start calculating balance for an hourly employee within 5 minutes.

Please post your feedback in the comments below. Thank you.