Human Resource Toolkit – Excel Templates – Support

Posted on

This is the support page for the Human Resources Toolkit – Excel Templates.

Our Human Resource Toolkit – Excel Template Page

This product is a bundle of 8 individual Excel Templates. The following are links to those individual templates. Please visit them for help material around each of them. If you have questions about the bundle, please leave them in the comments below.

  1. Recruitment Manager – Product Page 
  2. PTO Manager – Salaried – Product Page 
  3. Calendar Maker – Product Page 
  4. Team Vacation Planner – Product Page 
  5. Employee Leave Manager – Product Page 
  6. Employee Retention Dashboard – Product Page 
  7. Invoice Manager – Product Page 
  8. Gantt Chart Maker – Product Page 

Employee Retention Dashboard Google Sheet Template – User Guide

Posted on

In this article, we will explain step by step how to use the Retention Dashboard Google Sheet 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

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 and refreshes every time data is added or edited in the Employees Data sheet.

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.

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 ‘Edit Slicer’ modify the title in the Customize option. 
Modifying Slicer Settings
Modifying Slicer Settings
  • 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.

 How to Print or Export to PDF

The Dashboards are set up to be ready for print. Press Control+P or use Google sheet’s file menu to print. You can also use Google Sheet’s default Download to PDF option and Download 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. 

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.

Invoice Manager Google Sheet Template – User Guide

Posted on

This article provides step by step instructions on how to use the Invoice Manager Google sheet 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.

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 April 13th (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.

Pasting Data from Another Source

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

 

  • 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.
  • When pasting please paste as values. Do not paste the data with Ctrl+V or paste default.
  • Right click on cell A4 and choose Paste Special 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 Google Sheet 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

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 automatically refreshes after entering invoice or payment data.

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

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

Team Vacation Planner Google Sheet Template – Support

Posted on

This article will present step by step instructions on how to use the Team Vacation Planner Google Sheet template.

Purpose

This Google Sheet template is designed to help calculate the number of employees available on any day and compare with the number of employees needed on that day in the company. This will help us identify if there are any days where we are understaffed. By planning ahead of time, taking into account holidays and vacations coming up, we can be better prepared.

Overview of the Steps

  1. Enter Planning period
  2. Enter Number of Employees needed per weekday
  3. Enter Company holidays
  4. Enter List of Employees and their information
  5. Enter dates of Vacation planned by the employees
  6. View Calendar to see if there are any understaffed days
  7. View Report to see summary of understaffed days by month.

VIDEO DEMO

Detailed Step by Step Instructions

Step 1: Planning Period

The template allows planning for up to 366 days. We can choose any start and end date, that accounts to less than 366 days.

Enter Planning Period – Enter Start Date and End Date
Enter Planning Period – Enter Start Date and End Date

Step 2: Number of Employees needed for Weekday

In some companies, the number of employees needed at work can be the same every day. However, in other companies, there may be a varying need based on the day of the week.

For example, in a company that provides Customer Service 7 days a week, more customers may be calling in during Saturday/Sunday compared to other weekdays. In such a scenario, we need more employees at work during Saturday/Sunday.

This template allows you to customize to handle such scenarios that easily. For each weekday, we can set the number of employees needed.

Set Number of Employees needed per weekday
Set Number of Employees needed per weekday

If we enter 0 as Employees Needed, then the template assumes that as weekend day. In the above image, Saturday and Sunday will be considered as Weekends (for reporting purpose).

During weekends, employees will not be available to work.

Step 3: Enter List of Company Holidays

Enter the list of holidays in your company.

Enter list of Holidays in company
Enter list of Holidays in company

Step 4: Enter List of Employees and their Information

We enter the information about employees in the Employees table.

Enter list of Employees, their employment dates and work schedule availability
Enter list of Employees, their employment dates and work schedule availability

We enter the name of employee, hire date and termination date (if the employee has left the company).

For example, Employee 1 does not work on Saturdays , but other employees do. We can just enter No for any weekday an employee doesn’t usually work.

Step 5: Enter Dates of Vacation Planned by the Employees

In the Vacations sheet, we enter the vacation planned by employees.

Enter Vacation dates of Employees – Vacation Start and End Dates
Enter Vacation dates of Employees – Vacation Start and End Dates

This is very simple. Enter Employee name, Vacation Start and Vacation End dates.

If an employee takes only one day off, please enter same date as Start and End dates. Please do not leave the End date empty.

As the employees takes more vacation, just add new rows to this table.

There is no limit to how many vacations can be entered.

This completes our data entry steps.

Step 6: View Calendar to see availability by day.

The Calendar sheet is fully automated.

At the top of the sheet, we see the Availability Summary.

It shows the following information for each day in our planning period.

  1. Day – If it is a Holiday, you will see HOL. If it is Weekend day, WKD and if it is a working day WRK
  2. Employees Needed: This shows how many employees are needed at work on that day.
    1. If the day is a holiday, then this will be set to 0.
    2. Otherwise, this will be the number of employees needed for that weekday (that we entered in Step 2 of data entry in the Settings sheet)
  3. Employees Available: This reflects how many employees are available to work on the day.
    1. If the day is a holiday, this will be set to 0.
    2. If the day is a weekend, this will be set to 0.
    3. It will take into account, the Employees’ hire statuses, Employees’ work schedules on that weekday and vacations planned by employees .
  4. Employees on Vacation: This is how many employees who are employed on that day, but have taken a vacation.
    1. If it is a holiday, this will be set to 0.
    2. If the day is a weekend, this will be set to 0.
    3. Otherwise, this will reflect the count of employees who have planned vacations for that day.
  5. Availability – Needed: This tells us whether we are understaffed or overstaffed or right-staffed.
    1. This is just the difference between Employees Available and Employees Needed. If it is negative, we are understaffed (availability is less than need). If it is positive, we are overstaffed (availability is greater than need). If it is 0, we have the right number of staff (availability equals need).
    2. We will see values in red, there is under-staffing.
      1. We can also create a similar color for over-staffing if needed. We have to edit the conditional formatting rules to do that.

By knowing which days are understaffed or overstaffed, we can take actions accordingly. If we are understaffed, we can either increase capacity by hiring more permanent or temporary employees.

We can also see each employee’s availability on the calendar.

Availability Calendar shows each employees availability and vacation
Availability Calendar shows each employees availability and vacation
  • Green indicates the employee is available to work
  • Red indicates the employee is on vacation
  • Purple indicates a holiday
  • Gray indicates the employee is not working that day (Weekend or the employee does not work on that weekday)

This calendar is set up to handle 50 employees for 366 days.

Step 7: View report to see monthly summary of availability

The Report sheet is also fully automated. It will show the overall summary and summary of availability by month.

Summary Report for Team Vacation Planner – Understaffed Days by Month
Summary Report for Team Vacation Planner – Understaffed Days by Month

We can see, for each month, the number of days in month, holidays, weekends, working days and understaffed days.

% Days Understaffed = (Understaffed Days / Working Days)

This sheet is set up as print-ready. You can print and share with team. You can also export to PDF and share PDF.

PTO Balance Calculator (Hourly) – Free Google Sheet Template

Posted on

A Simple Google Sheet template to help you to track the PTO balances of hourly employees. Download this free Google Sheet 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

This Google Sheet Template is designed in such a way that you can Enter the inputs of the PTO policy and are ready to track PTO balance automatically within a few minutes.

This template is designed for Google sheets, but if you are looking for an Excel template, please visit the PTO (Paid Time Off) Balance Calculator for Hourly Employee in Excel

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

Features: 

  • 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).

How to Use the PTO Calculator Google Sheet template for Hourly Employees

Overview of the Steps

  • Make a copy of the Template
  • Enter the Input Data
  • View Balances in PTO Report 

Step 1: Make a copy of the Template

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

Make a copy
Make a copy

You can save the copy in your own google drive for your use. You can then share the copy with your colleagues to collaborate as needed.

Step 2: Enter the Input Data

  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

Enter Details in the 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.

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.

Step 3: View Balances in 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 Google Sheet template is useful to track Paid Time Off (PTO) for hourly employees in your organization.

You may also want to check some of our Google Sheet Templates:

Employee Recruitment Tracker

Sales Pipeline Manager Google Sheet Template – User Guide

Posted on

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

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

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.

Enter Required Fields

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 Background color  indicating that the entry is missing.

Missing Close Dates
Missing Close Dates

This red Background Color 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.

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

PTO Balance Calculator (Salaried) – Free Google Sheet Template

Posted on

A simple Google Sheet Template that can be used as a vacation tracker and PTO vacation accrual calculator. This Google Sheet template can also be used to track and calculate Employee’s PTO (or leave or vacation) accrual balances.

This template is designed for Google sheets, but if you are looking for an Excel template, please visit PTO Balance Calculator (Salaried)

Employee PTO (Paid Time Off) Calculator v2 – PTO Balance
Employee PTO (Paid Time Off) Calculator v2 – PTO Balance

Features:

  • Several settings available to cover most common business PTO policy scenarios
  • Very flexible and easy to customize for your specific business needs
  • Automatically calculates PTO balances for today and any future date
  • Vacation dates can be entered as date ranges
  • File is designed for one employee only. Make a copy of the workbook to use for the second employee.

How to Use the PTO Calculator Salaried Google Sheet template

Steps to use this template:

  1. Make a Copy and save this template in your Google Drive.
  2. Configure the input entries.
  3. Review the PTO policy and first accrual window details
  4. Enter PTO info
  5. View Balance and Balance Trend

Step 1: Make a Copy

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

Make a copy
Make a copy

You can save the copy in your own google drive for your use. Once copied, it can also be shared with your colleagues to collaborate as needed. 

Step 2: Configure the Input Entries

Monthly Accrual PTO Calculator – Inputs to Template
Monthly Accrual PTO Calculator – Inputs to Template

Step 3: Review the PTO policy and first accrual Window details

Monthly Accrual PTO Calculator – Review PTO Policy
Monthly Accrual PTO Calculator – Review PTO Policy

Step 4: Enter PTO info

Enter Vacation rates in PTO Calculator
Enter Vacation rates in PTO Calculator

Step 5: View PTO Balance and Balance Trend

View PTO Balance calculated by Template
View PTO Balance calculated by Template

Components of the PTO policy

Though the template is very simple to use, there are quite a few terms to understand and several calculations that happen behind the scenes. Let’s start from the beginning. Let’s start with the simple terms first.

User Inputs on PTO Policy
User Inputs on PTO Policy

EMPLOYEE NAME

This does not need any explanation. Enter name of employee for whom we will be tracking and calculating PTO balance.

HIRE DATE

A lot of the calculations for employee’s PTO balance depends on the Hire date of employee. Just enter Hire date. Even if you have been tracking PTO using some other tool and now want to use this template, enter the actual hire date of the employee. Tenure (how long an employee has been with the organization) is calculated from the hire date and companies may have tenure based increase in PTO.

PTO UNIT

We can choose to track employee PTO in units of days or hours. If we choose Hours, we have to enter PTO taken by employee in Hours. If we choose Days, we can just enter PTO dates (which we will discuss later) and ignore hours taken off.

ANNUAL PTO ACCRUAL RATE

Annual Accrual Rate is the PTO that an employee accrues in one year. For example, a company may offer 120 hours of PTO per year.

PTO ACCRUAL PERIOD

This is to inform how we accrue the annual PTO rate. Continuing with the above example of 120 hours per year, how will the employee receive these 120 hours. We have 6 options here: Weekly, Every 2 Weeks, Twice a Month, Monthly, Quarterly and Annual.

PTO Accrual Period Options – Weekly, Every 2 Weeks, Monthly, Twice a Month, Quarterly and Annual
PTO Accrual Period Options – Weekly, Every 2 Weeks, Monthly, Twice a Month, Quarterly and Annual

Let’s see how a Monthly scenario would work.

PTO Accrual Frequency and Annual PTO Accrual Rate
PTO Accrual Frequency and Annual PTO Accrual Rate

120 hours will be given to the employee at 10 hours each month for 12 months.

FIRST ACCRUAL PERIOD BEGIN DAY and ACCRUAL TIMING

In order to discuss the next two terms, we need to take an example. Let me use a Weekly accrual example to demonstrate.

Weekly PTO Accrual Example Inputs for Template
Weekly PTO Accrual Example Inputs for Template

In this example, the employee’s hire date is Jan 1st, 2020. Employee’s Annual PTO accrual rate is 120 hours and that is accrued weekly.  When we think of accrual periods, we have to think of a window with a start date and an end date.

Let’s enter First Accrual Period Begin Date as Jan 1st, 2020 (this is user input). So, the first accrual period window will be 1st Jan to 7th Jan.

When does the employee receive the accrued PTO? Is it on 1st or 7th? This can be controlled easily. In the above example, we have chosen ‘End of accrual period’. So, the employee receives the PTO accrued on 7th Jan.

We don’t have to remember all these calculations because that’s why we use such a PTO calculator tool.  Let’s review the policy as calculated by the template.

Weekly PTO Accrual Example – Review Policy
Weekly PTO Accrual Example – Review Policy

The Policy shows that the employee will accrue 2.308 hours per week. The first accrual window is 1st Jan to 7th Jan. First accrual day where PTO will be awarded to the employee is 7th Jan. The amount on that day will be 2.308 hours. This amount is the same as the weekly rate, because the employee starts on 1st Jan and the weekly window also begins on 7th.

We all know that employees can start in a new job on any day. So, let’s take the same example but for an employee who started on 3rd Jan.

Weekly PTO Accrual Example where Employee Starts in the middle of accrual window
Weekly PTO Accrual Example where Employee Starts in the middle of accrual window

We can see that the first valid accrual window is still 1st Jan to 7th Jan, and the accrual happens in 7th Jan (end of window).  However, the amount if only 1.648 hours because the employee only accrues for 5 days (3rd Jan to 7th Jan). Thus the template can easily prorate the PTO awarded when an employee joins in the middle of an accrual window.

The approach is the same for Weekly, Every 2 Weeks, Quarterly and Annual accrual frequencies. Twice a Month and Monthly are slightly different.

TWICE A MONTH

For Twice a Month, we don’t need to provide First Accrual Period Begin Date. We will enter 2 days.

Twice a Month PTO Accrual – Enter 2 days. 2nd day can be ‘Last Day’
Twice a Month PTO Accrual – Enter 2 days. 2nd day can be ‘Last Day’

The template will then take those two days as the accrual days every month. You can choose ‘Last day’ for the second day and the template can automatically assign the last day of each month, whether it is 28th (Feb) or 29th (Feb – Leap year) or 30th or 31st.

MONTHLY

For Monthly, we don’t need to provide First Accrual Period Begin Date. Instead we will choose a day of Month. The options are 1 to 28 and Last day.

Monthly PTO Accrual – Input day of month – First day example
Monthly PTO Accrual – Input day of month – First day example

The ‘Last day’ will be accounted for, correctly whether it is 28th (Feb) or 29th (Feb – Leap year) or 30th or 31st.

Monthly PTO Accrual – Input Day of Month – Last Day Example
Monthly PTO Accrual – Input Day of Month – Last Day Example

Now, let’s look at some more options we have with setting PTO/Vacation policy.

ANNUAL PTO ROLLOVER POLICY

As an employee continues to accrue PTO every period, the balance keeps growing, assuming there are no vacations taken. Typically, companies do not want employees to accrue a very large balance. Two reasons:

  1. Employees are encouraged to take regular time off to maintain a healthy work-life balance.
  2. 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. So usually, there is a rollover policy. This determines how many hours of PTO can the employee carry over from one year to the next year.

The template allows three possibilities.

PTO Rollover Policy Settings – Zero Rollover, Rollover Limit, Unlimited Rollover
PTO Rollover Policy Settings – Zero Rollover, Rollover Limit, Unlimited Rollover
  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. This is an unusual policy for a company.

 To see this in action, I have chosen a 40 hour roll over limit.

PTO Rollover Limit – Example – Monthly Accrual Rate
PTO Rollover Limit – Example – Monthly Accrual Rate

At the end of the year, the employee’s PTO balance comes from 120 to 50. It is 50 because 40 is carried over and the employee accrues 10 hours for January of next year.

Now with this rollover policy, there is another variation. Companies may apply rollover at calendar year change or on work anniversary dates. You can easily change that setting.

PTO Rollover timing can be Calendar year or Work Anniversary
PTO Rollover timing can be Calendar year or Work Anniversary

The next section covers the remaining options in PTO policy.

More PTO policy settings and options – Probationary period, Maximum allowed PTO and tenure based PTO
More PTO policy settings and options – Probationary period, Maximum allowed PTO and tenure based PTO

PROBATIONARY PERIOD

In some roles, employees may not be awarded any PTO for the first X number of days. For example, employee does not earn any PTO during the first 30 days of employment. You can set that easily in this template.

 MAXIMUM ALLOWED PTO BALANCE

The rollover limit only applies to the end of the year balance. Some companies can set a limit on maximum balance at any time. We can set the amount in the Maximum Allowed PTO Balance.

 ACCRUAL RATES VARY BY TENURE

Companies increase the annual accrual rate for employees who stay with the company for more years. We can handle such scenarios as well. We would choose YES to this first and then fill out the table below.

Employee Vacation Accrual – Annual Rate increased by Tenure
Employee Vacation Accrual – Annual Rate increased by Tenure

We can set the Annual PTO Accrual rate and Maximum PTO balance. In the example above, the employee will receive at the rate of 56 hours in the first year, then rate of 106 hours in the second and third year, 144 hours in years 4 to 10.

Important: Please make sure that the first entry here is for 0 completed years.

You can enter more rows as needed.

Now we have gone through the various input options in the PTO calculator. These settings have to be entered only once for an employee. After these are finalized, we will enter PTO dates whenever an employee is taking vacation.

PTO OR VACATION DATES

Enter Vacation dates in PTO Calculator – Enter range of dates and hours for each day
Enter Vacation dates in PTO Calculator – Enter range of dates and hours for each day

If we track PTO in hours, we have to enter the PTO hours column. We can ignore it if our PTO unit is days. We can enter date ranges to enter multi-day vacation. However, if it is a single day vacation, please enter both start and date as the same date.

In the above example, 3 hours of PTO for each of the 3 days (June 2, June 3 and June 4) – in total 9 hours – will be subtracted from the PTO balance. 2 hours will be subtracted on 5th July.

You can enter more vacations by just typing new row of data in the table.

PTO BALANCE

As we enter PTO dates, the balances get updated.

Current PTO Balance shown by default and PTO Balance shown from Hire Date
Current PTO Balance shown by default and PTO Balance shown from Hire Date

By default, today’s PTO balance is shown at the top. You can modify the date and can view PTO balance any date. To put it back to today, just type =TODAY().

Similarly, the balance trend chart shows data by default from Hire Date of Employee. You can edit and modify that as well.

Enter the number of days to control the duration displayed in the chart

PTO ADJUSTMENT

If you would like to add or remove PTO, 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).

Make positive and negative adjustments to PTO Balance easily
Make positive and negative adjustments to PTO Balance easily

An example would be an employee who has been with the company for a few years. You were using some system to track the PTO balance and now you want to migrate to this template. You don’t have to enter all the vacation dates from the past. You can just enter the adjustment amount to bring the current balance to the correct amount. If the employee has taken 60 hours of PTO already, then enter -60 as adjustment. 

PRORATING WHEN ACCRUAL RATE CHANGES

As we had discussed earlier, the accrual rate can vary by employee tenure. If the work anniversary happens to be in the middle of an accrual window, then we have to prorate the PTO accrued.

Let’s take an example where an employee’s hire date is Jan 16th 2019. Accrues 10 hrs a month in 1 year and then 20 hrs a month in 2nd year. So, for Jan 1, 2020, he will earn 15.16 hrs. 15 days at the rate of 10 hrs per month and 16 days at the rate of 20 hrs a month.

The template does this prorating calculation by default.

DATA VALIDATIONS

When you enter the First Accrual Period Begin Date, if it is earlier than or after the first valid accrual window, an error will appear. Let’s look at an example.

Data Validations built in the template – Example Inputs
Data Validations built in the template – Example Inputs

Though the  employee starts on Jan 1st, 2020, we have entered Dec 1, 2019 as First Accrual Period Begin date.

First Accrual Period Begin Date is too late
First Accrual Period Begin Date is too late

Employee is eligible to accrue from Jan 1, 2016, but the first accrual window is Dec 1, 2019 to Dec 7, 2019. If the employee will not accrue any balance from Jan 1st to Dec 1st, it is incorrect. This is due to the data entry error. Once we update the First Accrual Period Begin Date correctly, error will go away.

I would like to hear your feedback. Have I missed any of the scenarios that happen in your workplace? Do you find this useful? Please leave your comments below. Please share with your friends.

You may also want to check some of our Google Sheet Templates:

Employee Recruitment Tracker

Manufacturing Inventory Tracker – Free Google Sheet Template

Posted on

This Google Sheet is developed as a solution to automatically calculate the current raw material stock as well as determining how many units of each product you can make using the raw materials available. 

This template is designed for Google sheets, but if you are looking for an Excel template, please visit the Manufacturing Inventory Tracker in Excel

How to Create an Manufacturing Inventory Tracker in Google Sheets

Steps to use this template:

  1. Make a Copy 
  2. Enter the Input Data
  3. View the current raw material stock

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.

Make a copy
Make a copy

You can save the copy in your own google drive for your use. You can then share the copy with your colleagues to collaborate as needed.

Step 2: Enter the Input Data

Enter list of parts or products in the Products sheet as shown below

Inventory Tracker – Enter List of Products
Inventory Tracker – Enter List of Products

The Sales Quantity column is calculated based on sales orders that we will be entering soon.

Enter list of raw materials you use in your business in the Raw Materials sheet:

Inventory Template – Enter Raw Materials used in Business, their starting inventory and Re-Order Point
Inventory Template – Enter Raw Materials used in Business, their starting inventory and Re-Order Point
  • Starting Inventory: In this sample data, we have 10 Apples and 10 Bananas in stock when we begin using the template.
  • Re-Order Point: We have re-order points (Wikipedia Article on Re-Order Point) set for each raw material.
  • Available Now: Displays the current stock level of each raw material.
  • To Order: When current stock for a raw material goes below its re-order level, this field displays YES. Also, the template highlights the raw material name in red font.

Enter Bill of Materials (BOM) in BOM sheet:

Inventory Template: Enter Bill Of Materials for each product
Inventory Template: Enter Bill Of Materials for each product

In the sample above, 1 unit of Banana Berry Shake is created from 1 Banana, 5 Strawberries and 10 Blueberries. 1 unit of Apple Banana Shake is created from 2 Apples and 2 Bananas. Unit of Measure column is just for your reference.

Once you have entered BOM for all your products, the initial set up is done.

Entering purchase and sale orders in Orders sheet.

As a manufacturing business, you may buy raw materials from your suppliers and sell the finished products to your customers. So, there are two key transactions – Purchase and Sale.

We can enter Purchase and Sale Orders in a single table in Orders sheet.

Manufacturing Inventory Template – Enter Raw material Purchase and Product Sale Orders
Manufacturing Inventory Template – Enter Raw material Purchase and Product Sale Orders
  • Order Type: Order P1 is to purchase 50 Apples and 20 Bananas – So, we use Order Type of PURCHASE. Order S1 is an order to sell 5 Banana Berry Shakes. We use SALE order type.
  • Order Date: Date when the order is placed.
  • Expected Date: Date when the inventory is impacted. For example, order P1 was placed on 3rd July, but raw materials will reach us only on 4th July. So, Order date is 3rd July and Expected Date is 4th July.
  • Product or Raw Material Name: The template allows drop down selection for product / raw material name. If the order type is Purchase, it will allow Raw Materials and if order type is Sale, it will allow Products.
  • Quantity: Enter quantity or units of items on the order

If an order has 5 line items, then enter as 5 rows.

Step 3: Enter the Input Data

Check Availability

Before entering a new Sale order, if you would like to check the current capacity for a product, you can easily do that.

Inventory Availability – Check capacity to make a Product
Inventory Availability – Check capacity to make a Product

When you select a product, the template runs the calculations to figure out how many units of the product can be manufactured using the raw materials in stock right now. In the image above, we see that we can make 13 Apple Banana Shakes.

 Limit

The template cannot handle complex scenarios where sub-assemblies of raw materials are raw materials to the final product.

You may also want to check some of our Google Sheet Templates:

Employee Recruitment Tracker

Invoice Tracker – Free Google Sheet Template

Posted on

This Google Sheet is developed as a solution to keep track of the sales invoices and payments made by customers in one place. You can enter payments made by customers and let the template calculate outstanding balance amounts. 

This template is designed for Google sheets, but if you are looking for an Excel template, please visit Invoice Tracker

Features of Invoice Tracker Template:

  • Track all invoices in one place
  • Identify which invoices are past due and how late they are
  • Calculate how much amount is outstanding
  • Calculate expected payments in immediate future
  • Easy to identify due amounts by Customer
  • Simple and easy to use

How to Use the Invoice Tracker Google Sheets Template

Steps to use this template:

  • Make a copy
  • Enter Input Data
  • View results in Dashboard

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.

Make a copy
Make a copy

You can save the copy in your own google drive for your use. You can then share the copy with your colleagues to collaborate as needed.

Step 2: Enter Input Data

DATA ENTRY

Invoice Data Entered in Invoice Tracker Google Sheet Template
Invoice Data Entered in Invoice Tracker Google Sheet Template
  • Enter each invoice in the Invoice table beginning from row 15.
  • Enter Invoice Number, Customer, Invoice Date, Due Date and Invoice Amount
  • When Customer makes payment, enter it in Paid Amount column.
  • Green colored columns have formulas. Do not edit.
Calculated Columns in Invoice Tracker
Calculated Columns in Invoice Tracker

Outstanding Amount: This is calculated as Invoice Amount – Paid Amount

Status: There are five possible values for Status.

  • ‘ERROR”, when at least one of the fields Invoice Amount, Invoice Date, Due Date is left blank. Also, when Due Date < Invoice Date.
  • PAID IN FULL: Outstanding Amount is 0
  • CURRENT: If Outstanding Amount >0 and Due Date > Today (not due yet)
  • DUE TODAY: If Outstanding Amount > 0 and Due Date = Today
  • PAST DUE: If Outstanding Amount is 0 and Due Date = Today
  • OVERPAID: If Outstanding Amount < 0 (i.e., Paid Amount is > Invoice Amount)

Note: Error rows are not used in calculation of metrics. 

Rows with errors will be highlighted with a red Background color for your assistance.

Error Validations – Invoice Tracker – Missing Due Date
Error Validations – Invoice Tracker – Missing Due Date

Past Due Age: If the Status is ‘Past Due’, then this represents the aging bucket (1 – 30 Days, 31 – 60 Days, 61 – 90 Days, 91+ Days). Otherwise, it will be blank.

Selected: This will display 1 if the record (or row) is not filtered by the slicers.

Step 3: View results in Dashboard

The top of the Invoices sheet will present the summary in a dashboard as shown below. This will update instantly as you add more invoices to the table.

Invoice Tracker Template – Summary Metrics – Dashboard View
Invoice Tracker Template – Summary Metrics – Dashboard View

Let’s break it down in 3 sections.

Current Invoices:

Number of current (Due Date is in the future) invoices will be shown along with outstanding amount due.

Invoice Tracker Spreadsheet – Current Invoices and Invoice Amount
Invoice Tracker Spreadsheet – Current Invoices and Invoice Amount

Past Due Invoices:

Past Due Invoices and A/R Aging Report
Past Due Invoices and A/R Aging Report

Number of Past due (Due Date is in the Past ) invoices will be shown along with outstanding amount due.  Accounts Receivable (A/R) aging breakdown is also provided.

Expecting Payments:

Expecting Payments in Next 7 and Next 30 days
Expecting Payments in Next 7 and Next 30 days

Payments that are expected in the next 7 days (including today) and next 30 days (including today) will be displayed.

Slicers (Filters):

These metrics can be easily filtered using the Slicers provided.

Slicers allow filtering of Table and calculated metrics
Slicers allow filtering of Table and calculated metrics

The invoice table will be filtered as well. One can use this feature to easily target selective invoices. For example, click on Status = Past Due, to see only invoices that are past due.

You may also want to check some of our Google Sheet Templates:

Employee Recruitment Tracker

Employee Leave Vacation Tracker – Free Google Sheet Template

Posted on

Whether you are a HR Manager, a Small Business Owner or a Project Manager it is important to track leave/vacation/time-off taken by employees.  This Google Sheet Template is developed to help people in tracking leave/vacation in an organized way, calculating employee attendance.

This Google Sheet Template presents a calendar view of leave across multiple employees and also prints an annual employee report.

This template is designed for Google sheets, but if you are looking for an Excel template, please visit Employee Vacation Tracker

Employee Leave Tracker Dashboard for All Employees tracking vacations
Employee Leave Tracker Dashboard for All Employees tracking vacations

Features of the template:

  • Customize settings to meet your business needs
    • Track 5 types of employee leave
    • Customize which days are weekends (not working days)
    • Account company holidays
  • Practical Features
    • Track vacation for multiple employees
    • Employees can have start dates and termination dates
    • Can handle partial days of leave
  • Ease of Use
    • Easy to enter Leave in ranges of dates (example: 4 days of leave from Nov 21st to Nov 24th)
    • Easy to customize colors to suit your preferences
    • Can use the template continuously for many years (keep all your data organized in one file :))
  • Automated Calculations
    • Fully Automated Vacation Tracker Dashboard with monthly calendar view
    • Calculates Worked Days automatically
    • Automated Annual Employee attendance report

How to Use Employee Leave vacation tracker in Google Sheets

Steps to use this template:

  1. Make a Copy and save this template in your Google Drive
  2. Configure the Settings information
  3. Input Employee Data, Leave Information
  4. View reports on Team Dashboard and Employee Report

Step 1: Make a copy of the template

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

Make a copy
Make a copy

You can save the copy in your own google drive for your use. You can then share the copy with your colleagues to collaborate as needed.

Step 2: Configure the Settings information

Enter up to 5 types of leave to suit your company

The template by default supports 5 types of categorizing your leave. Since different companies may have different names and different number of leave types, the template supports customizing them.

Enter 5 types of Employee Leave types to suit your business
Enter 5 types of Employee Leave types to suit your business

By default, you will see the leave types (Vacation, Sick, Unpaid, Half Day, Other). You can just type over these names and replace with your own preferred names.

How to handle half-day leaves

You can see that there is an option to assign ‘Day Value’ to each leave type. This setting is present to handle partial days of leave. For example, I have entered 0.5 for ‘Half Day’ leave type. That means that when an employee takes Half Day leave, template will add 0.5 to leave taken total and reduces the Worked days by 0.5.

If your company allows 2 hours leave in an 8 hour workday setup, that would be 0.25 day. You can create a leave type and enter 0.25 as ‘Day Value’.

Select your company Weekends

In order to calculate attendance of employees automatically, we need to know company weekends so that we can remove them from working days.

I learnt from our readers that weekend days can vary a lot by country. Though Saturday/Sunday is the most common weekend choice, there are so many countries that use a different work week and weekend setup. (Wikipedia article on Weekends)

Select Company Weekends to use for employee attendance tracking
Select Company Weekends to use for employee attendance tracking

Since our goal is to provide a global solution to vacation tracking, we have the option of selecting any combination of weekend days. In the screenshot above, I have chosen Sunday as Weekend.

So, this tells the template to do 3 things.

  1. Template will highlight Sundays visually differently on the vacation dashboard
  2. While calculating attendance, it will not include the Sundays and Saturdays as Working days.
  3. Sundays and Saturdays will not get counted as leave.  When an employees takes Thursday to Monday as Vacation, it will count only as 3 days of leave (Thu, Fri, Mon).

Step 3: Enter Input Details

Enter company holidays

Enter list of holidays in your company.

Enter company holidays
Enter company holidays

The template will treat the holidays similar to Weekends for calculation purposes, but holidays will be displayed in a different color on the calendar. This allows us to view holidays and weekends distinctly.

Enter Employees data

Now, we move to the Employees sheet.

Enter Employee Name, Start Date and Termination Date (optional)
Enter Employee Name, Start Date and Termination Date (optional)

We will enter the Employee Name, Start Date (date when the employee began employment) and then Termination Date (if applicable). The Start Date and Termination Date allow the template to calculate the count of working days for each employee correctly.

Since the template can help you manage leave for multiple employees for long term windows (even many years, as there is no limit 🙂 ), it is quite possible that new employees join the company and current employees may leave. I want the template to work smoothly for you even when that happens.

Enter Employee Leave Data

Now, for the last data entry section, we move to the Leave sheet.

We enter leave taken by each employee here. There are four fields (Employee Name,  Leave Start Date, Leave End Date and Leave Type) in this table.

Enter Employee Leave Data – Employee Name, Leave Start Date, Leave End Date and Leave Type
Enter Employee Leave Data – Employee Name, Leave Start Date, Leave End Date and Leave Type

If an employee takes a 5 day vacation, you can enter that in one row by providing Leave Start and Leave End dates.

If an employee takes a single day leave, please enter same date in Start Date and End Date fields.

Leave Type field has data validation built-in which shows the 5 options we set in the Settings sheet.

Leave Type – Drop down options for data entry
Leave Type – Drop down options for data entry

Similarly the Employee Name also has data validation to allow only employee names available in the Employees sheet. But the drop down option will not activate until a new row is created in the table. If you like typing in the name directly, you can go ahead. If you prefer the drop down options, please read further below in the ‘Extensions’ section where I discuss a work-around .

We are done with data entry. It’s time to see the template’ magic in creating the vacation calendar and report.

Step 4: View Reports

View Team’s Monthly Leave Dashboard

The dashboard is interactive as it allows the user to choose the month for which we want to view the leave data for. We will enter the month by selecting Year and Month.

Choose Month for Vacation Tracker Dashboard
Choose Month for Vacation Tracker Dashboard

There are two pages in this Dashboard sheet. First is the leave calendar view.

Leave Tracker Dashboard – showing calendar for all employees
Leave Tracker Dashboard – showing calendar for all employees

As you can see from the above screenshot, the leave types are shown in different color. The legend is shown at the top indicating what each color means. In addition to the 5 leave types, we also have display for the following: Holidays (purple), Weekends (Dark Gray), Not Employed (Dot pattern) and Not Applicable (Diagonal Line pattern).

Order of priority for colors: Not Applicable > Not Employed > Holidays > Weekends > leave types

Holidays and Weekends are self-explanatory. Not Employed will represent the days before the employee began employment and days after the employee termination date (if applicable).

Not Applicable represents the days that are not actual dates. For example, if you choose November month, 31st day will be Not applicable. For February in a non-leap year, 29, 30, 31 will be Not Applicable days.

You can use this calendar view to assess if you will have enough coverage in your team for specific weeks (for example, during the holiday season). 

Monthly Summary

This sheet also shows the monthly summarized count of days taken off under each leave type, total leave taken and Worked Days.

Leave Tracker Dashboard in Excel showing monthly summary of leave days and worked days
Leave Tracker Dashboard in Excel showing monthly summary of leave days and worked days

Total Leave is the sum of leave taken under each of the 5 leave types.

If an employee’s leave overlaps with a holiday or a weekend, it does not count as a leave day.

Worked Days is calculated as (Company Working days – Holidays – Weekends – Not Employed Days – Total Leave days)

Worked days represents the number of days an employee worked or is scheduled to work.

You can look at this view during the last week of a month, to calculate the next month’s capacity (in work days) for the entire team. This will be helpful to evaluate whether you have enough capacity to meet the demand (work) ahead in the next month.

View Annual Employee Attendance Report

While the previous sheet was about viewing all employees at once in a calendar view one month at a time, we also need to see one employee’s data across months. This is where the Employee Report comes in handy.

Here we have three inputs.

Select year, Employee name and End date for the Annual employee report
Select year, Employee name and End date for the Annual employee report

The Year and Employee Name selections should be easily understandable.

End Date is set to Today’s date by default. This allows the report to show attendance/leave data as of today.

You can change this End Date by manually typing in any date, if you want to create a report as of any specific date (a month end, for example).

This sheet has 2 pages as well. First, the calendar view.

Employee Attendance – Leave Report showing types of leave, holidays and weekends
Employee Attendance – Leave Report showing types of leave, holidays and weekends

As we discussed with the Team Dashboard, the colors and the legend work the same way. For the chosen employee and year, we can visualize the complete year’s leave patterns.

The second page presents the summarized count of leave and worked days.

Employee Attendance Report – Monthly Summary of leave by type and worked days
Employee Attendance Report – Monthly Summary of leave by type and worked days