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
Enter data in Employees Data sheet
Refresh Calculations
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
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
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.
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
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
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
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
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
Page 2 shows the Average Tenure of employees and breakdown by various attributes.
Company Snapshot Dashboard – Tenure
There are 10 Slicers available to drill down.
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
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
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
Rename the Caption
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.
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.
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.
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.
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?
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.
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 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.
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.
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.
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.
Enter your company’s Weekends and Holidays in WEEKENDS HOLIDAYS Sheet
When employee works different hours (more or less) than the default daily hours, enter them in the HOURS WORKED sheet
When employee takes PTO, enter PTO info in the PTO USED sheet
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
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
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
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
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
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
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
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
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
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
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
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
Zero Rollover: Employee loses all the PTO balance at the end of the year and starts from scratch in the next year.
Rollover Limit: We can set a limit on how many hours are carried over.
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
PROBATIONARY PERIOD
Enter the duration of the Probationary period in days.
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
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
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
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
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
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
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
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.
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.
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
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
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
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
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.
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.
Probationary Period: Is there a probationary period for an employee where PTO is not accrued?
Accrual Period: How frequently and when does the PTO accrual happen?
Accrual Rate: How much PTO does an employee accrue during each period? Does it change by Tenure?
Rollover Policy: Can balance from one year be rolled over to the next year? If so how much and when?
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.
Zero Rollover: Employee loses all the PTO balance at the end of the year and starts from scratch in the next year.
Rollover Limit: We can set a limit on how many hours are carried over. This is the most common practice among companies.
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
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
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.
PTO Accrued in Period: How much PTO has employee earned in this period alone?
Unaccrued Work Hours: How much hours did the employee work that have not converted to PTO yet?
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 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 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 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
= 2hours
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
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
= 1hour
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 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
= 3hours
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
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
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
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.
If you are new to Excel templates, please visit the Support Page where there is important information that you should know about using Excel templates.
Overview of Steps
ENTER ONE-TIME SETTINGS
Enter Leave Types (up to 12)
Choose Weekend
Enter Holidays
Enter Reporting Period
Enter Entitlement Settings
ENTER DATA
Enter Employees Data
Enter Leave Entries
VIEW AUTOMATED REPORTS & DASHBOARD
Team Balance Report
Daily Report
Employee Report
Monthly Team Dashboard
Enter One-time Settings
Leave Types
Enter Leave Types in the LEAVE TYPES table.
Enter Leave Types
You can enter up to 12 leave types. You can name the leave types as you need for your organization.
Colors are shown as they will be used in the Reports and Dashboards.
Colors are automatically assigned and cannot be modified.
Choose No for BALANCE TRACKING if you would like the specific leave type to be not tracked in Balance Reports. Examples would be Offsite meetings and training that the employee has to attend but should not be counted as leave against the allowed leave. The employee is unavailable for normal work but is still working for the company in offsite events or training sessions.
Leave the field blank if you would like to track in Balance reports.
Weekends
The template can support customized weekends. You can choose the days of the week that your company considers as weekends. Just select TRUE for those days.
Choose Weekends
When calculating the workdays, the template will exclude weekends.
If an employee is taking leave from one Monday to the next Monday (assuming Sat and Sun are weekends), it will count as 6 leave days and not 8. Sat and Sun will not be counted as leave days against the leave allowed for the employee.
Holidays
The template can support customized holidays. You can choose the days of the week that your company considers as company holidays. Just enter the date and name of the holiday.
Enter Holidays
You can enter as many holidays as needed. When you continue using template for next year, you can just keep adding holidays for next year.
When calculating the workdays, the template will exclude holidays.
If an employee is taking leave from one Monday to Wednesday (assuming Tue is a holiday) it will count as 2 leave days and not 3. Tue will not be counted as leave day against the leave allowed for the employee.
Reporting Period
The reporting period is the range of dates that are used to calculate the leave balance of employees. Just enter the Start and End Dates for your company’s reporting period.
Enter Reporting Period
In the above image, we have set up the reporting period to be from Jan 1, 2019 to Dec 31, 2019.
The max reporting period allowed is 12 months. If you enter a date range that is more than 12 months you will see an error message.
Enter Reporting Period – Error – More than 12 months
Report End Date cannot be before the Report Start Date. An error message will appear if that is detected.
Enter Reporting Period – Error – End Before Start
These validations are present to ensure that the date inputs are correct. If there are errors, the Balance report and Employee report will not be populated.
Entitlement Setup
The template provides two options for entitlement (or quota) setup. You can set the entitlement at the total level or per leave type.
Enter Entitlement Option
If you choose TOTAL, you can then enter the total entitlement of leave days.
Set Total Leave Entitlement
In the above example, we have chosen 15 days as total allowed leave for all employees.
If you choose PER LEAVE TYPE, you can then enter the entitled leave days for each leave type.
Set Leave Entitlement per Leave Type
The leave types that we set to NO for Balance Tracking earlier, do not need any Entitled leave and can be set to 0. In the above example, each employee is allowed 8 days of Vacation, 5 days of Sick, 2 days of Medical and so on.
If you would like to have some employees have more or less entitled leave, you can enter them in Adjustments sheet.
Enter Data
There are two data inputs you would have to enter. 1) Employees 2) Leave Entries
Enter Employee Data
In the EMPLOYEES sheet, you can enter list of employees in your organization.
Enter Employees Data
Employee Name and Start Date are Required Fields. End Date should be used if the employee has left the company.
Employee Name should be unique. Do not repeat the same employee name twice in this table.
Other columns in blue fill can be customized as needed. You can rename the columns and use to store any information you want to store about the employees.
Enter Leave Entries
In the LEAVE sheet, you can enter list of leave entries for all employees in your organization.
Enter Employee Leave Data in Table
For each leave entry, enter the Employee Name, Start of leave, End of leave, Leave Type and Daily Leave Value.
If an employee is taking a single day leave, enter the END DATE same as START DATE. Do not leave the END DATE blank or empty.
DAILY LEAVE VALUE can be partial days as well. 0.5 would represent half a day.
If an employee is taking multiple day leave, for example, from Aug 5, 2019 to Aug 8, 2019 and Daily Leave Value is 0.5, that would mean 4 days of 0.5 each, resulting in 2 whole days of leave taken. Do not enter the total leave value in the DAILY LEAVE VALUE field.
DAILY LEAVE VALUE can be entered as negative value if an employee is working on a holiday or a weekend. Let’s call it a ‘Make up Day’. The template can support such scenarios.
Validations
There are some validations put in place to highlight data entry errors in different colors.
Leave Entries – Validations
If you are entering negative leave value (make up day) it needs to be for a holiday or a weekend.
Leave Start Date cannot be after the Employee has left the company.
Leave Start Date cannot be before the Employee has joined the company.
Leave End Date cannot be before Leave Start Date.
Any leave Entries with these errors will be excluded from reporting.
Custom Columns
Just like in Employees table , we can add custom columns to the Leave table.
Enter Employee Leave Data – Custom columns
You can enter new columns to the end of the table to record information about leave. However, do not insert any columns in between.
That completes our data input for the template. You are now ready to view the automated output from the template.
View Automated Reports & Dashboards
Balance Report
Balance report lists the employees along with their leave balance information at total as well as each leave type.
Team Balance Report
There are 3 controls available in the Balance report to customize it.
Display
You can choose to display one of three measures on the report.
Entitled: Number of days of leave entitled; Used: Number of days of leave used ; Balance: Number of days of leave available as balance
Filter
You can choose a Filter field from the drop down. The list of fields in Employees table are available for you to filter by, including any custom columns you created.
You can then enter a Filter value. For example, if Filter field was ‘Department’ and filter value was ‘Finance’, the report will display only employees in Finance Department
Please note that any employee who is not employed during the reporting period will automatically not be displayed in this report.
Page #
The report shows 100 employees at a time. You can enter 2 as Page number to see employees 101 to 200.
Negative balances will be shown in red fill.
Daily Report
Daily report lists all the leave entries for a specific date.
Daily Report
Here, you can type in any date and the report will pull the leave entries for that specific date.
The report also summarize the total leave by leave type on the left.
The last column on display is a custom column where you can replace it with any column from Leave table.
Employee Report
Employee report is a printable report that will display the summary and details of leave and balance for a specific employee.
This report has two pages. Page 1 displays the summary by leave type for the entire reporting period at the top and a calendar view in the bottom showing leave information.
Employee Report Page 1 with Calendar
Page 2 displays the monthly totals by leave type and the summary for the entire reporting period. It also displays the Worked Days calculated as (Total Workable Days in the Month – Leave taken by each employee).
Employee Report Page 2 – Monthly Totals by Leave Type
Monthly Team Dashboard
Team Dashboard displays the leave information of all employees in one specific month.
Page 1 displays the calendar view showing leave of each employee, where leave types are shown in different colors.
Monthly Team Dashboard Page 1 with Calendar view
Page 2 shows the totals for each employee by leave type. It also displays the overall total leave and worked days.
Monthly Team Dashboard Page 2 with totals by Leave Types
There are 3 controls available in the Team Dashboard to customize it.
Custom Column
As the second column next to Employee Name, you can choose any column from Employees table. In the above example, we have chosen Department.
Filter
You can choose a Filter field from the drop down. The list of fields in Employees table are available for you to filter by, including any custom columns you created.
You can then enter a Filter value. For example, if Filter field was ‘Department’ and filter value was ‘Finance’, the report will display only employees in Finance Department
Please note that any employee who is not employed during the Month chosen will automatically not be displayed in this report.
Page #
The report shows 100 employees at a time. You can enter 2 as Page number to see employees 101 to 200.
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.
Input Data is always visible & can be edited easily
Backup by saving file regularly
Bonus Tip: Do not insert columns in between in Leave sheet. Add to the end of the table.
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?
How to handle if some employees have different holidays than others?
How to ensure dates are entered in correct format?
What are the differences between the free vacation tracker and the Employee Leave Manager templates?
Which template is suitable for your organization? PTO Manager or Leave Manager?
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.
If you are looking for a simple and effective Timesheet template or Time Card template, you have reached the right place. In this blog post, I will present a free time sheet template in Excel and explain step by step how we can use it to calculate hours worked.
You can enter time card entries like time in, time out and break time and let the template create printable weekly timesheet, biweekly timesheet and monthly timesheets.
If you are an employee, no more wondering ‘How many hours did I work?’ or searching for ‘How to calculate hours worked?’. This timesheet template makes it easy by automating all the calculations.
If you are an HR (Human Resources) professional who needs a work hours calculator for the employees in the company or team, you can use this too. The template accounts for various commonly used rules in the industry and makes your task simple and easy.
Essentially, this template can be used by anyone who needs a timesheet in Excel or work hours tracker or payroll hours calculator.
In this blog post, we will see step by step how we can use the Resource Capacity Planner Excel template. We will take a simple example planning scenario and see how the template can help us in smarter planning.
The Resource Capacity Planner template is designed to determine the available resource capacity, compare with demand and identify surplus/deficit in capacity. It also allows us to modify the resource availability or demand (to meet our objectives) and see impact of the changes instantly.
For more on template’s features, please visit the product page.
Template Version
This user guide is for the latest version v3 of the template. If you are using a previous version and need the new file, please email support@indzara.com with your Order Number. If you need support with the previous version, please email your questions to support@indzara.com
Overview of steps:
Enter Settings
Enter Resource list with standard availability and allocate Capacity to Skills and Projects
Enter vacation and overtime
Enter Demand (tasks) data
Refresh Calculations
View Dashboard to evaluate plan
Address any over-utilization and under-utilization
View Calendar to ensure desired utilization at granular time periods
Step 1: Enter Settings
In the Settings sheet, we will enter the basic settings.
Planning Period We will set the planning period by entering a Start Date and End Date. The template allows a maximum of 2-year planning period.
Step 1 Enter Planning Period Dates
We will use a 3 month period from Apr 1, 2019 to June 30, 2019.
Weekends In most work places, we have days during a week where we don’t work (also called weekends). We can choose which weekdays are weekends from the list of options available. The template will set the availability of resources to 0 by default.
If you don’t have any weekends, then leave this input blank. Just click the cell and press the Delete key. It will remove the contents of the cell. Please do not delete the column or row.
Enter Weekends
We will choose SAT & SUN as weekends for this tutorial.
Enter Settings – Weekends
If a resource works during a weekend day, we can enter that as overtime in the VACATION_OVERTIME sheet.
Excel Tables
In the next set of inputs, we use Excel Tables. Entering the data correctly inside the Excel tables is critical to the functioning of the template. If you are new to Excel Tables, please see our article on How to use Excel Tables for data entry?
Tip: Start entering data right below the Header of each Table
Holidays Since most companies have holidays (other than weekends), it is important that we factor that in when we calculate capacity. We can enter our company holidays and the template will set the availability of resources to 0 by default on those days. Each holiday must be entered individually.
We will enter a couple of days in the Holidays table.
Step 3 – Enter List of Holidays
If a resource works during a holiday, we can enter that as overtime in the VACATION_OVERTIME sheet.
Projects The template allows managing capacity for multiple projects. We will enter 2 projects, to keep it simple.
Enter Settings – Project List
Skill Groups Skill Group is how we can group resources for planning.
Skill Group and Skill are used interchangeably in the template.
In our example, we have resources from 2 skill groups: Project Mgmt and Development.
Enter Settings – Skill Group List
Resources Enter list of Resources available for planning.
The template uses the term ‘Resource’. The most commonly used resource in most scenarios is the human resource (can be called as Employee). But a machine could be a resource too. Hence instead of using ‘Employee’, the term ‘resource’ is generally used.
Let’s assume we have 6 resources in total.
Step 6 – Enter list of Resources
Step 2: Enter Resources’ Capacity
Now, we move to the Capacity sheet in the template.
We must enter the resources, provide start and end dates of availability, available hours every weekday and other information related to capacity.
Tip: To help with data entry, you can copy the values you entered in the Settings sheet, for Resources, and then paste in the Capacity sheet. Please make sure that you paste as values. After copying the values, right click on cell A4 in Capacity Sheet. In the menu that pops up, choose Values under Paste Options. This ensures that only the values are carried over, without impacting the formatting and drop-down validations.
Pasting Resource Names as Values
After entering resource names, we enter the Start Date of each resource.
Resources can be hired anytime and similarly resources may leave a company anytime. The template will assign 0 availability before start date and after end date.
Enter Resources Capacity – Available Work hours by Weekday
We enter the number of hours each resource will be available each weekday. For example, Project Planner 1 is available 8 hours a day from Mon to Fri. The template can handle varying hours by weekday as well.
We assign the 6 resources each to a Skill Group and Project.
We can provide the End Date of each resource if there is an End Date for the availability. If no end date is provided, the template will assume that the resource is available through the end of the planning period.
Resources Capacity Table – Entered
Finally, we enter the Cost per Hour for each resource.
Important: The last column is a calculated field that is critical to the functioning of the template. Please do not edit/delete it. If you don’t want to see it, you can hide the column
How to allocate same resource to multiple skills?
You can allocate each resource to one or more skills.
To allocate one resource’s capacity to multiple skills, add a new row to the table and assign a different skill. Ensure the available work hours are distributed between the two skills. For example, if Developer 4 was available 6 hours in total and can perform Development role for 4 hours and Project Mgmt role for 2 hours, we would enter as shown below.
Entering same resource with multiple skills
Resource name will be same Developer 4, but work hours may vary, and Skill Group will be different. In this case, we assigned to same Project B.
How to allocate same resource to multiple Projects?
You can allocate each resource to one or more Projects.
To allocate one resource’s capacity to multiple projects, add a new row to the table and assign a different Project. Ensure the available work hours are distributed between the two Projects.
For example, if Developer 4 was available 6 hours in total (Development skill), and will work 4 hours for Project B and 2 hours for Project A, we would enter as shown below.
Entering same resource with multiple Projects
Resource name will be same Developer 4, but work hours may vary, and Project will be different.
How to allocate same resource to multiple Projects and Skills?
You can allocate same resource to one skill in one project and a different skill in another project.
For example, if Developer 4 was available 6 hours in total, and will work 4 hours in a Development role for Project B and Project Mgmt role for 2 hours for Project A, we would enter as shown below.
Entering same resource with multiple Projects and Skills
How to shift same resource to a different Project after a certain date?
If we want a resource to work in one project for a certain date range and then change to a different Project, we can use the same approach. We can use the END DATE column to enter the end date of a certain Project assignment and then enter a new row with a different project.
Assigning a resource to different projects over time
In the above illustration, Developer 4 works on Project B for 6 hours a day until 30th Apr. From May 1st, the resource works 6 hours a day on Project A. You can apply the same method if you want a resource to change skill from a certain date.
Let’s go with the simple setup now where each resource is assigned to only one skill and one project.
Limitation:
Now, the above examples would have made it clear that this Capacity table can have more than 1 record per resource. Though the default Resource limit is 100, this table can accept 300 entries.
For example, if you have 50 resources, each resource assigned to 2 skills on average and 3 projects on average, that would require 50 X 2 X 3 = 300 entries.
300 unique Resource-Skill-Project combinations can be entered.
Step 3: Enter Vacation and Overtime
Though we have entered standard availability above, we know that employees may take vacation or sometimes work overtime. We can enter any vacation or overtime taken by resources in the VACATION_OVERTIME sheet.
Enter Vacation Overtime taken by Resources
Project Planner 1 is taking 3 days off (8 hrs each day) from 16th Apr to 18th Apr.
Two important things to note here:
It is 8 hrs of vacation each and hence totally 24 hours in total for all 3 days. However, we only enter 8 hrs as the column is for daily hours. The template will calculate automatically the total.
If the employee’s vacation hours vary over the three days, then we must create separate rows.
We enter -8 since it is vacation. If it is overtime, we will enter 8.
If vacation hours for a day is greater than the standard availability of that resource, then the result will be set to 0 to avoid negative availability.
Project Planner 2 is taking one day off (8 hrs) on 21st May. We enter 21st May as Start and End Dates. It is important that we fill both dates.
Finally Project Planner 2 also does overtime on 25th May. We enter 8 (not -8) in Hrs, since this is overtime.
This 25th May is a Saturday and is a weekend day. However, overtime can be done on weekends and holidays as well. The template supports that.
Overtime is positive to capacity and Vacation is negative to capacity.
This completes our capacity inputs. Now, we move to the Demand data input.
Important:
If you have assigned a resource to multiple skills and/or projects, you will have to enter vacation or overtime separately for each combination. This allows more flexibility in capacity planning as you can enter overtime only for a certain project instead of both projects (assuming resource assigned to 2 projects).
Step 4: Enter Demand data
In this template, demand is entered as Tasks in the Demand sheet.
Enter Demand as Tasks assigned to Resource Project and Skill.
Required fields: Date of the task, Resource assigned to do that task, Hours of work required, Project assigned and Skill Group.
Detail column is optional to enter any details about the task or any notes.
The three calculated cells on the right should not be edited or modified. That would break the functionality of the template. Please do not delete or edit these columns.
Do Not Edit Calculated Fields
Planning granularity
An important concept to understand is planning granularity. We have entered daily level of tasks in the above image. What level of tasks to be entered depends on the planning granularity you need.
When we say ‘weekly planning’ it means that our goal is to ensure all the tasks for the week are completed by end of week. If we are behind on Monday, Tuesday or Wednesday, that is okay. As long as we complete by end of week, we will be accomplishing our goals. In this scenario, you don’t have to enter daily level tasks. You could enter just one record for a resource for each week.
If you want to ensure you will be meeting your demand every single day, then, you should enter daily tasks individually.
Thus, the template can accommodate more granular demand entry (daily – even multiple tasks per day) or aggregated demand entry (weekly or monthly or quarterly).
Once we complete entering the demand, we are done with data entry.
Step 5: Refresh Calculations
The template uses pivot tables and hence every time we change input data, please refresh calculations.
Data ribbon –> Refresh All.
Refresh All calculations
Step 6: Review Dashboard
Dashboard
Dashboard sheet shows the summary of metrics across all projects, skills and resources.
Capacity Planning Dashboard
On the left side, you will have the slicers (filters) that you can use to drill down to specific Project(s), Skill group(s) and Resource(s).
Dashboard Slicers
At the top, you will see the overall summary of metrics.
Capacity Planning Dashboard – Summary Metrics
You can see the number of Projects (2), Skills (2) and Resources (6) in the Plan.
On the left side, you can see the Capacity (2472 hrs), Demand (2287 hrs), Surplus of 185 hrs at 93% Utilization Rate.
On the right side, you can see the same metrics – based on Cost. The resources available cost totally $25,554 while the demand tasks will cost $23,273 resulting in a surplus of $2,281 at 91% utilization.
We also see that there are 2 over utilized resources and 3 under-utilized resources.
Why is this important?
The overall aggregate utilization rate will not provide the complete picture. If there is a Developer resource who is over utilized (utilization rate > 100% or capacity < demand) has 50 hrs over utilized and another Project Mgmt resource who is under-utilized (utilization rate < 100% or capacity > demand) with 50 unutilized hrs, they cancel each other when we aggregate. Though the overall utilization rate may be 100%, individual resources have surplus and deficit and that must be addressed.
Hence it is important to view utilization at resource level.
Changing Currency
To change the currency from US$ to another currency, please follow the steps below:
Press Ctrl+G and select CURR named range.
Changing Currency – Press Ctrl+G and select CURR
Press Ctrl+1 to open the Formatting dialog box. Choose the currency desired and click OK.
Press Ctrl+1 and Change Currency
Now, the currency on the dashboard display will change accordingly.
Let’s continue with the rest of the dashboard.
Project level Capacity vs Demand
The above visual presents the capacity vs demand for each of the Projects.
On the left, you will see projects where there is deficit – that is, capacity is less than demand. In this case, we don’t have any.
A project will either be in deficit or surplus – not both.
On the right, you will see projects where there is surplus – capacity is greater than demand. Project A has capacity of 1236 hours while demand is only 1097 hours. The surplus is 139 hours.
Project B has capacity of 1236 hours while demand is only 1190 hours. The surplus is 46 hours.
Skill level Capacity vs Demand
The above visual presents the Skills which are over utilized (on the left side) and under-utilized (on the right side).
Development has a total capacity of 1488 hours but demand of 1495 hours. 7 hours overutilized (deficit).
Project Mgmt has a total capacity of 984 hours but demand of 792 hours. 192 hours under-utilized (surplus).
A Skill Group will be in Surplus or Deficit or neither; never in both.
Resource table shows which specific resources are over utilized and under-utilized.
Resource level Capacity vs Demand
Clearly two resources Developer 3 and Developer 2 do not have enough capacity to complete the tasks assigned to them. Meanwhile, 3 other resources have a lot of additional hours available but not utilized.
In the next page of the dashboard, you can see the charts of Capacity vs Demand in Hours.
The charts show the overall capacity vs demand for each Project, Skill group and Resource.
The Dashboard has so far given us a good overview of our plan and identified 2 over-utilized resources which need to be addressed.
3 resources have unutilized hours that will be costing the team. In this tutorial, I am assuming the under-utilization is not a major concern. In some project scenarios, underutilization can also be an issue. You can follow similar steps as explained below to address that too.
Now, let’s see how the template will help us address this.
Step 7: Addressing Over-utilization and Under-Utilization
In order to address the over-utilization and under-utilization, we must do one or more of the following:
Change Capacity
Adjust standard availability of resources in the Resources sheet.
Increase (to increase capacity) or Decrease (to reduce capacity) available hours
Add overtime (to increase capacity) or vacation time (to reduce capacity)
Change Demand
Change the demand hours if possible. This would reduce the demand and allow the capacity to fulfill the demand.
Change Allocation/Assignment
Change assignment of tasks (demand) to a different resource.
Change capacity allocation of resource to Projects.
The first option Change Capacity is straight forward and don’t need much explanation. The impact is that it will cost more to increase capacity.
The second option Change Demand is also straight forward to implement in the template (update the demand sheet), though it may be hard to get project stakeholders to reduce the demand (or scope).
We will focus on the 3rd option which may neither increase the cost nor reduce the demand. It is the re-assignment of capacity and demand (tasks) to existing resources to remove over-utilization.
Changing Assignment of Tasks to a Different Resource
Let’s filter the dashboard to Project A by using the slicers on the left of the Dashboard.
The Resources table shows the following.
Assigning Resources in Project A – Capacity Planning
Developer 2 is overutilized by 5 hours while Developer 1 has 37 unutilized hours.
In the Demand sheet, Filter to Resource Developer 2. Find a task for 5 Hours and re-assign to Developer 1.
Reassigning a task to Developer 1
Let’s refresh the calculations. Data ribbon –> Refresh All.
That will update the dashboard calculations.
Project A – Resource – Capacity vs Demand – After assignment
Now, there are no overutilized resources in Project A.
Let’s clear the Project filter in Dashboard.
Now, this is what the Resources table shows.
All projects – Resource – Capacity vs Demand
Developer 3 has 39 overutilized hours, but we don’t have a Developer with that many unutilized hours.
Project Planner 2 who also works on Project B has 85 unutilized hours. To demonstrate how this template can handle a resource having multiple skills, we will assume that Project Planner 2 can handle Development tasks.
Now, we need to assign some of the capacity of the Project Planner 2 to Development skill.
Assign Project Planner 2 Development capacity
Important things to note here:
A new record must be created in the Capacity Sheet as shown above (last row). Resource Name will be same, but Skill will be Development. Cost can vary and can be entered any value.
When we enter the standard work hours of the new record (we have entered 1 hour a day), we must review the work hours already assigned for the resource (Project Planner 2). Previously Project Planner 2 had 8 hours a day availability. We have updated that to 7 hours for Project Mgmt and assigned 1 hour to the Development. This assumes that the resource is still working same total number of hours. It’s just that we are changing the skill being used.
When you split a resource to multiple skills or projects, check the Vacation/overtime sheet and update if that resource has Vacation/Overtime entries.
In this case, Project Planner 2 has vacation and overtime entries. We create additional entries for the new skill and update the previously entered entries.
Review Vacation and Overtime entries when splitting resources
Now that we have created the capacity allocation for Development skill, we need to assign tasks from Developer 3 to Project Planner 2 in Demand Sheet. Select enough tasks to switch to overcome the overutilization.
Assign tasks from Developer 3 to Project Planner 2
Let’s refresh the Dashboard again.
All projects – Resource – Capacity vs Demand – Final
We have no resources over utilized. 🙂
Overall metrics look like below.
Capacity Planning Dashboard – Summary Metrics – After Optimization
All the resources will have enough capacity to meet the demand. We have 185 hours surplus capacity and are at 93% utilization.
This was just an illustration of how to use the template to modify capacity and demand. The target utilization rate can vary in each organization.
The Dashboard provides the metrics for the entire planning period duration in aggregate. If, in your organization, it is important to ensure a certain utilization every day, then you can update the planning period to a day or use the Calendar sheet to view capacity/demand at a daily level.
Step 8: View Calendar for granular time periods
Calendar is very flexible as it has many options to choose from. It also has the slicers (filters) just like the Dashboard. The below shows the capacity in hours for each resource every week.
Calendar – Capacity by Resource – Hours – Weekly
There are four selections (Dimension, Measure, Unit, Time Dimension) to make and each selection has several options.
Resource Capacity Planner Calendar Settings
Dimension The calendar can be created for the list of resources or list of skill groups or list of projects.
Calendar Settings – Dimension Resource Skill or Project
Measure You can select to display Capacity data or Demand data or Surplus/Deficit data.
Calendar Settings – Measure Capacity Demand or Surplus Deficit
Unit The options for Unit are Hours and Cost.
Calendar Settings – Unit Hours or Cost
Time Dimension Finally, you can choose to show daily, weekly, monthly, quarterly or annual data.
Calendar Settings – Time Dimension Daily Weekly Monthly Quarterly or Annual
The template will display up to 53 periods (53 days, 53 weeks, 24 months, 8 quarters, 2 years).
When you make the selections, the calendar display will update automatically to reflect the selections.
For example, let’s see Surplus/Deficit Hours by week.
Calendar – Surplus Deficit by Resource – Hours – Weekly
We can see that though overall there may be no deficit for a resource based on total hours, some weeks have deficit which are made up by surplus capacity in some other weeks.
If we want to ensure that the team completes the tasks every week, then we need to follow the steps outlined earlier on modifying the capacity or demand.
If there are any questions about this template, please see the Product Support page for more information.
In this blog post, we will cover the most frequently asked question about Recruitment Manager Excel template.
To learn more about the template’s features, please visit the product page.
To learn how to use the template, please visit the support page.
Before we begin, we are assuming that the template’s formulas have not been edited/modified.
Why does the Dashboard not reflect data entered?
This is by far the most common question.
The answer is somewhat long, as we need to cover various scenarios which could lead to this. Let’s explain the different reasons and how to address each of them.
7 Reasons for Why Dashboard does not reflect data
VIDEO DEMO
1. Data Not Refreshed
The template uses pivot tables and hence we should refresh the calculations whenever we make any changes to existing data or add new data.
If you have entered data for the first time in Jobs and Applications sheets, but see that the Dashboard is completely blank, this could be one of the reasons.
Solution: Refresh the calculations. In the DATA ribbon press Refresh All button.
Data Ribbon — Refresh All
2. Errors in applications Data
Applications which have data entry errors will not be included in Dashboard and hence if you have any errors, that could be the reason why you don’t see what you expect in the Dashboard. We put these data validations in place so that the metrics calculated in the Dashboard are always correct.
To identify if this is true in your case, please see ERROR column (column Q in v2 of the template) in Applications sheet.
Error column in Applications sheet
This is a calculated column. So, please don’t edit it. But you can filter using this column to see the applications having Errors.
There are two main reasons why there would be an Error.
Required Dates are missing.
Dates are not chronological
Let’s use a sample data set to illustrate this clearly.
For Hired Applications, all the dates until the hiring stage should have a date. In other words, applications should go through and complete all the stages to be hired.
Errors in Hired Applications
In the above screenshot, first application is missing OFFER DT. The application status is ‘HIRED’. Hence we need to have all the stage dates until the Hire stage.
The second application has all the dates, however, the MGR INTERVIEW DT is Feb 22nd whereas the previous stage PHONE SCREEN DT is Feb 24th. That is an error, as it is assumed that the stages are done in sequence.
Note: Job Posted Date is coming from the Jobs sheet for the specific Job ID. You can also see the Job Posted Date in column Y in Applications sheet.
The third application in the screenshot does not have an error, as the dates are present and they are chronological.
It is a little different for not hired applications. This includes applications with status ‘NOT HIRED’ and applications with no status (where we have not taken a decision yet).
The dates must be in chronological order. However, we don’t need all the dates to be populated. If an application goes to Stage 3 and then is put in NOT HIRED status, then we don’t need dates for Stages 4 to 6.
Errors in Not Hired Applications
In the screenshot above, the first application is missing PHONE SCREEN DT. As it has an APPLICATION DT and MGR INTERVIEW DT, it should also have the PHONE SCREEN DT in-between.
Second application has a MGR INTERVIEW DT (25th Apr) that is prior to the PHONE SCREEN DT (4th May). That is an error.
What if a candidate skips a stage?
Sometimes a candidate may skip a stage and go to the next stage directly. In such cases, it is recommended that we enter the same date and not leave it blank.
For example, if a candidate skips Phone Screen and directly goes to the Mgr Interview because it is an internal candidate and a Phone Screen may be considered unnecessary. In that case, I would enter the Application Date again as the Phone Screen Date. This will result in the calculations of time taken for Phone screening as 0 days.
Solution: Enter missing dates and ensure they are chronological
3. Required fields are missing
In the Jobs sheet, required fields are Job ID, Job Posted Date, Positions and Status.
Job ID required in Jobs sheet
Required Fields in Jobs sheet
Each Job has to be in one of the three status values (OPEN, COMPLETED or CANCELLED).
In the Applications sheet, JOB ID and APPLICATION DT are required fields.
Required Fields in Applications sheet
Required fields are marked as shown above in the template for your identification.
Solution: Enter any missing values in required fields.
4. Dates in invalid format
Please check that the dates are in correct date format. In certain language and region versions of Excel, the date formats are different from the U.S. version. This could cause Excel to not recognize the entries as dates and thus result in errors in ERROR column.
To check if the date is a valid date format, please click on the cell with the specific value. Then, click on the Number format drop down list.
If Excel shows all the formats with the same value, it means that Excel is treating it as text and not date.
Correct Date format check – Example Invalid Date
If Excel treats as date, it would appear as shown below.
Correct Date format check
Solution: Enter dates in correct format.
5. Filters applied in Dashboard
If you have chosen any filters (slicers) in the Dashboard sheet, that will narrow down the data set used to calculate metrics on Dashboard. If you believe that the Dashboard is not reflecting all the jobs and applications, please check if there are any filters applied. If so, clear the filters.
Clear Filters in Dashboard
Solution: Clear filters applied.
6. Open positions with 0 applications
Template uses data in Applications sheet to build dashboard. Jobs data is also pulled over to Applications sheet.
If we have 0 applications for a job, then they will not included in the Open Positions metric. It will be shown separately next to it as ‘Open Positions w/o Applications in Market’
Open Positions with 0 Applications
Solution: Check if there are applications in Applications sheet for the job. Check the Open Positions w/o Applications metric.
7. No Hired/Relevant Applications
Some parts of dashboard may not be populated if there are no relevant data yet. Each section of the Dashboard calculates certain metric that needs relevant data to make that metric meaningful. If that relevant data is not present, the section will appear blank.The following shows the data that is relevant to each section on the dashboard.
4 KPIs related to Hired Positions – only if there are HIRED applications.
HR Dashboard Summary Metrics
3 KPIs related to Open Positions – even if there are no HIRED applications.
Open Positions with 0 Applications
Recruitment Funnel – if there are applications with HIRED status or NOT HIRED status – basically applications where decisions have been taken.
Recruitment Funnel (up to 6 stages)
Monthly Metrics – only if there are HIRED applications.
Monthly Metrics (Hired and Days to Hire)
Pipeline Efficiency of Hiring – only if there are HIRED applications.
Pipeline Efficiency – Time Spent in each stage
Application Sources – only if there are HIRED applications.
Application Sources – Hired & Conversion Rate
Decline Reasons – only if there are NOT HIRED applications.
Decline Reasons for Candidates/Applications
Cost – if cost is entered in Cost column in Jobs sheet.
Active Pipeline – if there are applications with no status – basically applications where decisions have not been taken.
Active Recruiting Pipeline – Stage breakdown
Solution: Ensure that there is relevant data in the Jobs and Applications sheet.
If you followed the above suggestions and still have any questions, please post them below in the comments section. Thanks for your support.
Our Small Business PTO Manager Excel template can be used to calculate and report PTO balances for employees in a company. One of the features of the template is the Calendar which shows the monthly view of PTO taken/scheduled by the employees. By default, this sheet was limited to show 40 employees at a time so that it can fit within 1 page to print. If your company has more than 40 employees to track PTO, then this could be a limiting factor. In this blog post, we will learn how to extend the Calendar view to show more than 40 employees.
The default calendar view looks like this.
PTO Manager – Calendar View – Default
It would stop with 40th Employee.
Calendar shows Employees 1 to 40
For this exercise, let’s assume we have 80 employees in our company. We will use the following simple steps to view more than 40 employees.
VIDEO DEMO
Option #1 (40 employees at a time)
If you don’t need to see data for all 80 employees in the calendar at the same time, then we have a very easy solution already built-in.
Just type 41 in cell A7.
PTO Manager Calendar – Enter different starting number
Now, you will be able to see data for employees 41 to 80.
Calendar shows Employees 41 to 80
Option #2 (More than 40 employees at a time):
This involves a few more steps.
Before we begin, please save a copy of the file for backup. We will be making formula changes and it is always recommended to create a backup.
Tip: It is also recommended to create backups regularly even if you don’t make formula changes.
Step 1: Unprotect sheet
Please unprotect sheet with password indzara
Unprotect Sheet
Step 2: Select Row 45
Click on 45 to select row 45.
Click on 45 to select row 45
Step 3: Right click and choose ‘Copy’
Right Click and Choose Copy
Step 4: Select rows 46 to 86
Select rows 46 to 86 (we need to select 1 row more than our desired expansion. Since we want to expand by 40 rows, we select 41 rows)
Step 5: Right click and choose ‘Insert Copied Cells’
Right click and choose Insert Copied Cells
Step 6: Select row 87
Click on 87 to select row 87
Click on 87 to select Row 87
Step 7: Clear Row 87
Right click and choose ‘Clear Contents’
Right click and choose Clear Contents
Step 8: Change Print settings
Since the calendar will be more than 1 page long, we need to make some modifications to the Print Settings.
First, Click on ‘Print Titles’ in the Page Layout ribbon.
Select Print Titles
Set rows 1 to 6 to repeat. This allows the 6 rows to print on second page (employees 41 to 80) as well – allowing the reader to align the dates to the calendar.
Select rows 1 to 6 to repeat in the Page Setup
Step 9: Protect sheet
Now we are done with making the changes to extend the calendar. We can protect the sheet again to prevent unintentional editing of formulas.
Protect sheet
Recommended
Support Page: For all the help articles on Small Business PTO Manager Excel template
Product Page: For product features and highlights of the PTO Manager Excel Template
