Indzara Support

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.

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.

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.

Step 4: Enter List of Employees and their Information

We enter the information about employees in the Employees table.

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.

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.

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

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.