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 be interested in...

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

You may also be interested in...

Employee Timesheet – Free Google Sheet Template

Posted on

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.

This Google Sheet is developed as a solution for anyone who is looking for calculating working hours effectively. Using this Google Sheet Template create printable weekly timesheet, bi-weekly timesheet and monthly timesheets.

This template is designed for Google Sheets, but if you are looking for an Excel templatePlease visit the following link to download the Excel template : Employee Timesheet Excel Template

How to Use the Employee Timesheet Google Sheet Template

The Template contains the following sheets:

  • Information sheet
  • Settings sheet
  • Data entry sheet
  • Weekly timesheet
  • Bi-weekly timesheet
  • Monthly timesheet
  • Help sheet

We will review each of the sheets and explain the process of calculating work hours in a step-by-step manner. Each workbook can be used to enter only one employee’s details. If you need to enter details of multiple employees then you will have to create a copy of this template for each employee separately. 

You can map an employee’s daily, weekly, bimonthly and monthly work hours by analysing time card entries like time in, time out, break time etc. 

The template is created using various working policies and rules in the industry so it makes a HR’s work easy. The timesheet template automates all calculations so it is easy for the employee/manager/human resource (HR) staff alike to use this timesheet to calculate work hours or pay roll. 

Weekly Timesheet Template -Printable – Excel Template
Weekly Timesheet Template -Printable – Excel Template

Topics

  • What is a Timesheet?
  • Features of the Timesheet Excel template
  • How to use the template?
    • Settings explained
    • Time Card Data Entry
    • Automated Timesheets
    • Print/Export to PDF
  • Common policies and how to implement
  • Frequently Asked Questions (FAQ)

What is a Timesheet?

A timesheet (or time sheet) is a method for recording the amount of a worker’s time spent on each job…The time cards stamped by time clocks can serve as a timesheet or provide the data to fill one. These, too, are now often digital. (Wikipedia,”Timesheet,”n.d., Para 1, https://en.wikipedia.org/wiki/Timesheet)

Timesheets are digital logs  employees’ work hours, travel hours, break hours, overtime and other important information. These data entries record the employee’s productivity and work hours so as to serve useful for payroll calculations.

  • Timesheets are paperless; they save tonnes of money. 
  • Digital timesheets enable streamlined processes for clocking in large amounts of data. 
  • Since, the calculations on timesheets are automated, they are accurate and efficient.
  • We can perform various calculations and display the data in various methods. 
  • We can create multiple reports to map the productivity of employees and even compare their performances. 

Features of the Timesheet Google Sheet template

  • Enables simple data entry: Time In, Time Out and Break duration (in hours and minutes). Here, time is rounded to a minute.
  • Supports 3 Tiers of time and pay rates (Regular, Overtime and Double Overtime)
  • Dates and Days automatically populate based on Start Date. 
  • It is extendable. No need to create new sheets for each week or a month. You can enter a year’s worth of records on the same sheet. 
  • Automated Weekly, Biweekly and Monthly Timesheet reports
  • Customized settings
    • It is designed with formulae that set daily limits, weekly overtime limits
    • The template works based on different overtime rules and policies for weekends, holidays and weekdays. 
    • Supports the California State Rule Policy for Seventh consecutive day of work in a workweek 
    • You can determine the start of the week and the weekends as well. 

Limitations

  • The template is designed for entering data for only one one employee. To track data for multiple employees, please make copies of this template. 
  • It is however important to not keep copies of the sheets in the same file as that will clash with the template’s settings. 
  • Pay rates must be fixed for each employee during the pay period for the calculations to work correctly. 

Overview of the Steps

  • Make a copy of the template
  • Enter Input Data
  • Automated Timesheets

Step1: Make a copy of the template

To use this template simply make a copy of our existing Employee Timesheet Google Sheet Template, rename it as per your needs, and save it on your Google Drive.

Make a Copy of the Sheet
Make a Copy of the Sheet

Step 2: Input Data

The template uses two sheets for data inputs: 

  1. The settings sheet
  2. The data entry sheet (entering Time Card Data)

It uses three sheets for automated data output:

  1. Weekly Timesheets
  2. Bi-weekly Timesheets
  3. Monthly Timesheets

The above-mentioned sheets are print-ready and easy to view.

The Settings Sheet

Employee Info

In the employee info space, we enter details such as Name, Job Title and  Manager of the Employee. We specify a date as to when we want the Timesheet to track the employee’s work hours. Accordingly, the template will automatically populate the dates for the log. There is a custom field for you to enter any other important information about the employee. 

Settings – Enter Employee Information – Timesheet Template
Settings – Enter Employee Information – Timesheet Template

Company Info

Under the company info, we enter the company’s name,  its tagline and address. You can use the custom field here to display any other important information about the company as well. 

Settings – Enter Company Information – Timesheet Template
Settings – Enter Company Information – Timesheet Template

Weekends, Start of Week and Holidays

The template is customizable in the sense that you can select any day(s) of the week to be weekend(s). You can select any day of the week to be the start of the week as well.

The template is customized to be flexible because different countries have different week -beginning days, work days and weekends. 

In the below scenario, we have chosen MON (Monday) to be the start of the week and SUN (Sunday) as the weekend. 

Settings – Select Weekend Days Start Day of Week, Holidays, Weekends
Settings – Select Weekend Days Start Day of Week, Holidays, Weekends

Settings – Choosing Start Day of Week
Settings – Choosing Start Day of Week

Holidays

This template is completely customizable for holidays as well. You can also enter your own endless list of company holidays. A thing to note is that each holiday must be entered separately. 

The template has a feature where it can distinguish between holidays and weekends  automatically and accordingly apply different policies for those. 

Overtime policy settings

Rules and working policies around Overtime and wages vary across countries and companies. The template uses the most common set of rules to calculate the same:

  1. Overtime Tiers and Limits
  2. Policy for Weekends and Holidays
  3. Policy for Seventh Consecutive work day in workweek (California State’s Day of Rest law)

Overtime Tiers & Limits

The template handles 3 work-hours time tiers: Regular, Overtime, Double Overtime. You can calculate pay for work hours based on your company policy by setting up the time tiers. 

The template is setup based on the following policy:  

  • Upto 8 hours of work, the employee will earn a Regular Rate of $X per hour. 
  • After 8 hours and upto 12 hours of work, he or she will earn an Overtime Rate of $Y per hour. 
  • Beyond 12 hours of work, the employee will earn a Double Overtime Rate of $Z per hour.
  • For more than 40 non-overtime hours of work in a week, the employee will earn an Overtime Rate of $Y per hour. This rule is applied when the extra time worked add up to 8 hours only.
  • For more than 40 non-overtime hours of work in a week, if the employee puts in more than 8 hours of work time, it will be calculated as Double Overtime.

General Policy Text

The template generates the policy text automatically so that the company staff can accurately implement it. 

 Please review the below policy to figure out if your company follows the same. Else, you are free to modify the template based on your company policy. 

Employee Pay Calculation – Overtime and Pay Rate – Policy Text
Employee Pay Calculation – Overtime and Pay Rate – Policy Text

Customizable Overtime Tiers and Pay

In the below screenshot, the template is set in the following way: 

  • Upto 8 hours of work, the employee will earn a Regular Rate of $10 per hour. 
  • After 8 hours and upto 12 hours of work, he or she will earn an Overtime Rate of $15 per hour. 
  • Beyond 12 hours of work, the employee will earn a Double Overtime Rate of $20 per hour.

However, the daily work-time limits, weekly work-time limits and rates are completely customizable. If they do not apply, their entries can be left blank. 

Settings – Enter Overtime tiers and Limits for Timecards
Settings – Enter Overtime tiers and Limits for Timecards

Policy for Weekends and Holidays

You can apply any of the Overtime tiers to Weekends and Holidays. Employees can be compensated on either Regular, Overtime or Double Overtime rates for any extra work hours they put in during weekends or holidays. 

Settings – Policy for Weekends and Holidays
Settings – Policy for Weekends and Holidays

Policy for Seventh Consecutive work day in week (California State’s Day of Rest law)

This policy may be applicable only for the state of California. However, if you want to implement such a specific policy for your company’s employees, then you can use this option. Please read the law to understand more: California Law

Settings – Seventh Day policy – Day of rest Law
Settings – Seventh Day policy – Day of rest Law

In the above screenshot, the template has been setup in the following manner:

  • For more than 40 non-overtime hours of work in a week, the employee will earn an Overtime Rate of $15 per hour. This rule is applied when the extra time worked adds up to 8 hours only.
  • For more than 40 non-overtime hours of work in a week, if the employee puts in more than 8 hours of work time, it will be calculated as Double Overtime.

You can customize the overtime tiers and hours limit. The settings are one-time entries and you do not have to change them again unless your company’s policies change.

Settings-Seventh-Day-policy-Day-of-rest-Law-Customize
Settings-Seventh-Day-policy-Day-of-rest-Law-Customize

Just like the other settings, you can opt-out of using this policy by choosing the “No” option. Please refer to the two scenarios below:

Settings- Seventh Day policy – Day of rest Law – Not selected
Settings- Seventh Day policy – Day of rest Law – Not selected

Note: As soon as you choose the “no” option the adjacent work-hour and Overtime entries become redundant whether or not they have values in them.

Settings – Seventh Day policy – Day of rest Law – Not selected
Settings – Seventh Day policy – Day of rest Law – Not selected

Time Card Entry

The daily Time Card entries have the first columns, namely the Date and Day columns, automatically populated through formulas for ease of data entry. Then, Data entry is needed for three columns: Time In, Time Out and Break Time. 

Time In and Time Out refer to the starting and ending time of work for each day, represented in HH:MM format. The entries are made this way:

  • 7:00 for 7:00 AM
  • 19:00 for 7:00 PM (please note that the moment you enter 19:00, the template will display the time as 7:00 PM

Break time is unpaid work and it will be deducted from the actual Work hours. Break time is entered in HH:MM format but it is actually the number of hours and minutes used up.For instance, if an employee has taken 2 hours and 30 minutes of break, the data entry should be 02:30.

Time Card Entry in the Google Sheet template
Time Card Entry in the Google Sheet template

Validation

The template alerts you if you enter a time in the In-Time Column that is greater than your Out-Time. As shown in the below screenshot, the wrong entries will be marked in red to indicate the errors.

Time Card Entry in the Timesheet Google Sheet template – Validation
Time Card Entry in the Timesheet Google Sheet template – Validation

Another thing to note is that, you can not leave just one of the entries (eitherTime In or Time Out) blank. If done, the work hours for the day will be calculated as zero. So, it is essential to review all the entries before calculating payroll. 

Refer to the screenshots below to understand how a blank space in Time Card entry affects the output sheet.

Time Card Entry in the Timesheet Google Sheet template – Validation
Time Card Entry in the Timesheet Google Sheet template – Validation

Time Card Entry in the Timesheet Google Sheet template – Validation - Output Sheet Display
Time Card Entry in the Timesheet Google Sheet template – Validation – Output Sheet Display

Holidays/Weekends

Holidays are shown in blue and Weekends are shown in Yellow.

Time Card Entry in the Timesheet Excel template – Illustrated
Time Card Entry in the Timesheet Excel template – Illustrated

Step 3: Automated Timesheets

The template is automated in such a way that we can update the daily timesheet on sheet instead of using several. And if we need to track data on a weekly, biweekly or monthly basis, the template gives us the reports through three output sheets. 

These reports are print-ready and could be exported to PDF as well: 

  1. Weekly (7 days)
  2. BiWeekly or Fortnightly (14 days)
  3. Monthly (Complete Month)

On Demand

The above-mentioned reports are generated  on demand, that is, if you input the pay period’s start date, the template calculates and pulls up the corresponding work-hours and payroll report automatically.

Auto populating timesheets
Auto populating timesheets

Report Layout

The different components of the  Timesheet report are as follows:

  • The template automatically populates the Employee and Company Information we inputted in the Settings sheet. 
  • The work hours of each day of the pay period are calculated and shown. It also displays the breakdown of Regular, Overtime and Double Overtime hours. If you opt-out of any of the tiers, the break down will exist but the pay will not be calculated for the unselected tier. For example, if you opt-out of Double Overtime, the pay will not be calculated for it. 
  • Weekends are flagged with a W and Holidays with an H.
  • The template displays the total hours worked under each tier, the corresponding hourly rates. Then the Grand Total of hours worked and Pay under each tier is displayed. Finally, the Total Payment to be made for the entire pay period.
  • The template provides Placeholders for employee’s and manager’s signatures.
  • The template displays the Custom fields inputted  in the Settings sheet as well.

Weekly Timesheet – Illustrated
Weekly Timesheet – Illustrated

Weekly Timesheet – Illustrated - without Double Overtime
Weekly Timesheet – Illustrated – without Double Overtime

Custom fields in Timesheets
Custom fields in Timesheets

The following are the 3 screenshots of Weekly, BiWeekly and Monthly Timesheets that can be created with this template.

Weekly Timesheet template

Weekly Timesheet Template -Printable – Google Sheet Template
Weekly Timesheet Template -Printable – Google Sheet Template

Biweekly Timesheet Template

BiWeekly Timesheet Template -Printable – Google Sheet Template
BiWeekly Timesheet Template -Printable – Google Sheet Template

Monthly Timesheet Template

Monthly Timesheet Template -Printable – Google Sheet Template
Monthly Timesheet Template -Printable – Google Sheet Template

Common Policy Settings and How to Implement

The template accommodates various policies and work-rule settings. We will now elaborate on how to implement 6 common policies using this template. 

To illustrate this, we will use this sample Time Card entry of an employee who worked the following hours in a work week:

Employee Work Hours Sample
Employee Work Hours Sample

1. Single Tier policy

In this policy, there is only a single pay rate for all the time the employee works. There is no concept of Overtime here. To set this up, we name the tier and add an hourly rate to it. 

In the image below, we have named this tier “Regular” and pay $10 per hour for the work hours. In this tier, the work done on Weekends and Holidays will compensated with the same hourly rate.

The policy text states that the employee will earn at a Regular rate of $10 per hour. Work done during Weekends will be charged at Regular rate. 

 Process- Print - Timesheet
Process- Print – Timesheet

Output

The employee’s total number of work hours are charged at a Regular hourly rate of $10 and the employee earns $770 for 77 hours of work. Please view the image below to see the calculation.

Single-Tier-Weekly-Timesheet-Example
Single-Tier-Weekly-Timesheet-Example

2. Two Time-tiers Policy with No Weekly Limit

In this policy, we have two-tiers of pay: Regular and Overtime. We have a daily work-hour limit for Regular pay, above which Overtime charges are applied. However, we do not have a weekly work-hours limit in this policy. 

To set this up, we enter hourly rates for Regular  and Overtime work. We also set a daily limit for Regular work. In the below example, we have set up 8 hours of work as Regular time with an hourly wage of $10, above which will be considered Overtime (with hourly wage of $15). 

The policy text reads that the employee will earn at a Regular rate of $10 for the first 8 hours of  a day.The employee will earn at an hourly Overtime rate of $15 for any work done above 8 hours in a day. Work done during weekends will be charged at OVERTIME rate.

Two time tiers settings – with no weekly limit
Two time tiers settings – with no weekly limit

Output

In the below image, we have given an example of how Overtime wages are calculated. The employee earns an Overtime hourly wage of $15 for any work done beyond 8 hours in a day. Also, the employee will earn Overtime pay for 14 hours of work on Sunday as it is a weekend (W). So, in total the employee earns $920 for 77 hours of work.

Two-Tiers-Regular-and-Overtime-with-no-Weekly-Limit-Weekly-Timesheet-Example
Two-Tiers-Regular-and-Overtime-with-no-Weekly-Limit-Weekly-Timesheet-Example

3. Two Time-tiers Policy with Weekly Limit

In this policy, we have two Time tiers: Regular and Overtime. We have a daily and weekly limit for the Regular tier as well. 

To set this up, we enter hourly rates for Regular  and Overtime work. We also set a daily and weekly limit for Regular work. In the below example, we have set up 8 hours of work as Regular time with an hourly wage of $10, above which will be considered Overtime (with hourly wage of $15). We have also set up a weekly limit of 40 hours for Regular work, above which will be considered as Overtime. 

Note: If you have a weekly limit but no daily limit, please enter 24 in Daily Limit.

The policy text reads that the employee will earn at a Regular hourly rate of $10 for 8 hours of work in a day. Above which will be considered Overtime with hourly rate of $15. The employee will earn at Overtime for any work done beyond 40 non-overtime (Regular) hours in a week. Work done on weekends will be charged as Overtime. 

Two time tiers settings
Two time tiers settings

Output 

The important difference between this and the previous policy is that when an employee crosses 40 Regular hours of work time in a week, he or she gets compensated at Overtime rates for extra hours worked. 

In the below image given as example, the employee crosses 40 hours of Regular work time by working extra 12 hours on Saturday. The employee puts in 39 hours of work from Monday to Friday and 13 hours of work on Saturday. In this 1 hour is added to 39 hours to make it 40 hours. The additional 12 hours worked on Saturday are counted as Overtime work. Thus, the employee earns $955 for 77 hours of work. 

Two-Tiers-Regular-and-Overtime-with-Weekly-Limit-Weekly-Timesheet-Example
Two-Tiers-Regular-and-Overtime-with-Weekly-Limit-Weekly-Timesheet-Example

4. Three time-tiers Policy with no Weekly Limit

In this policy we have 3 Time tiers: Regular, Overtime and Double Overtime with a daily limit for Regular and Overtime work hours. However, there is no weekly limit for work hours. 

To set this up, we have a daily limit of 8 hours for Regular work compensated at $10 hourly. Above 8 hours upto 12 hours of work is considered Overtime with an hourly rate of $15. Any work done beyond 12 hours in a day is considered as Double Overtime.

The policy text states that the employee will earn a Regular hourly rate of $10 for 8 hours  of work. Beyond which, he or she is compensated at an Overtime hourly rate of $15 which has a cap of 12 hours. Beyond 12 hours, the employee is compensated at a Double Overtime hourly rate of $20. Work done during weekends will be charged at Overtime rate.

Three time tiers settings – with no weekly limits
Three time tiers settings – with no weekly limits

Output

As shown in the below example, the employee gets Overtime charges for work done between 8 and 12 hours in a day. Beyond 12 hours of work, the employee gets compensated with Double Overtime pay charges. Thus, the employee gets Double Overtime charges for three days in the week where more than 12 hours of work was done. In total, the employee earns $935 for the 77 hours of work.

Three-Tiers-Regular-Overtime-Double-Overtime-with-no-Weekly-Limit-Weekly-Timesheet-Example
Three-Tiers-Regular-Overtime-Double-Overtime-with-no-Weekly-Limit-Weekly-Timesheet-Example

5. Three time-tiers Policy with Weekly Limit

In this policy, we have three time tiers: Regular, Overtime and Double Overtime. We have a daily work limit for Regular and Overtime tiers. The main difference between this and the previous policy is that we have set up a weekly limit for Regular work hours.  

To set this up, we have a daily limit of 8 hours for Regular work compensated at $10 hourly. Above 8 hours upto 12 hours of work is considered Overtime with an hourly rate of $15. Any work done beyond 12 hours in a day is considered as Double Overtime. Additionally,  when an employee crosses 40 Regular hours of work time in a week, he or she gets compensated at Overtime rates for extra hours worked.

The policy text states that the employee will earn a Regular hourly rate of $10 for 8 hours  of work. Beyond which, he or she is compensated at an Overtime hourly rate of $15 which has a cap of 12 hours. Beyond 12 hours, the employee is compensated at a Double Overtime hourly rate of $20. Work done during weekends will be charged at Overtime rate. Employees will earn an hourly Overtime rate of $15 for any non-overtime work done in a week that exceeds 40 Regular hours. Work done during weekends will be charged at OVERTIME rate.

Output 

In the below example, the employee earns Overtime charges whenever he crosses 8 work hours and Double Overtime charges when he crosses 12 work hours in a day. 

The employee has completed 39 Regular work hours from Monday to Friday and has worked a total of 13 hours on Saturday. In those 13 work hours, 1 hour is added to the existing 39 hours to complete the 40 work-hour limit.  The next 11 hours are allotted as Overtime while the last 1 hour is allotted as Double Overtime for Saturday. In total, the employee earns $970 for 77 work hours. 

Three-Tiers-Regular-Overtime-Double-Overtime-with-Weekly-Limit-Weekly-Timesheet-Example
Three-Tiers-Regular-Overtime-Double-Overtime-with-Weekly-Limit-Weekly-Timesheet-Example

6. Three time-tiers with Weekly Limit and Seventh Day Policy

Now, if there were another condition applied to the previous policy, how would it pan out?

Let us assume one more scenario where the employee is given extra pay for the Seventh Consecutive Work day in Week (Day of rest).

To set this up, we choose “yes” in the settings page to enable this policy where on the 7th continuous work day, the employee gets Overtime pay for 8 work hours and beyond that, Double Overtime pay. 

How to set up?

California 7th Day – Day of Rest – Labor Code 550
California 7th Day – Day of Rest – Labor Code 550

Output

In the below example, the employee earns the following:

  1. Regular pay for 8 hours of work each day
  2. Overtime pay for work between 8 and 12 hours each day
  3. Double Overtime pay for work that extends beyond 12 hours each day
  4. Overtime and Double Overtime pay for work that crosses 40 non-Overtime hours in the week.
  5. Here, since we have applied the 7th Consecutive Work Day of the Week Policy. So, on Sunday, instead of applying Weekend work charges,  the template applies the mentioned policy. Since, Sunday becomes the 7th consecutive work day, the employee earns Overtime charges for the first 8 hours and beyond that, Double Overtime charges.

In total, the employee totally earns $1000 for 77 hours of work.

Three-Tiers-Regular-Overtime-Double-Overtime-with-Weekly-Limit-and-California-Law-Weekly-Timesheet-Example
Three-Tiers-Regular-Overtime-Double-Overtime-with-Weekly-Limit-and-California-Law-Weekly-Timesheet-Example

What are some useful resources on this topic?

You may also be interested in...

Sales Pipeline Tracker – Free Google Sheet Template

Posted on

As an owner of a small business, you will need a tool to manage the data on your sales leads. This Google Sheet Template is developed to help you in tracking your sales leads and in managing them. 

This Google Sheet Template can be effectively used to track the status of your sales pipeline. You can determine where the deals are lost using this Google Sheet template.

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

Sales Pipeline Tracker – Excel Template – Reporting Metrics
Sales Pipeline Tracker – Excel Template – Reporting Metrics

Features of Sales Pipeline Google Sheet Template

  • Track all your sales deals in an organized way in one sheet
  • View active sales pipeline broken down by stages
  • Calculates Expected Deal Values (based on Win %)
  • Calculates average time (days) taken to win deals
  • Identifies stages where deals are lost
  • Easy tool to filter deals by stage and status
  • Easy to customize

How to Use the Sales Pipeline Tracker Google Sheets Template

Steps to use this template:

  1. Make a Copy and save this template in your Google Drive.
  2. Configure the input entries.
  3. The reports are populated automatically.

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. Once copied, it can also be shared with your colleagues to collaborate as needed. 

Step 2: Configure the input entries

Review Stages in Sales Process

By default, the template comes with 5 stages (Lead, Opportunity, Demo, Quote and Sale) . The first 4 can be renamed easily by typing directly on the stage names.

4 Stages in the Sales Pipeline – Rename Stage names
4 Stages in the Sales Pipeline – Rename Stage names

Entering a new Sales deal (or lead)

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

Each row is a separate deal and it would have Company name, Contact person name, Deal Value and Created Date.

Enter sales leads data – Company name Contact name Deal value and Deal created date
Enter sales leads data – Company name Contact name Deal value and Deal created date

Deal Value can be estimated if it has not been determined yet. But please enter a value so that the pipeline can be evaluated.

We will then enter the current Stage of the deal.

Enter Current Stage of each deal from the drop down
Enter Current Stage of each deal from the drop down

The 4 stages we had defined earlier will be the values to choose from, in the drop down list.

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

In some businesses, the Win % are defined to be constant for a specific stage. Example: 10% for all Leads, 30% for all Opportunities, 50% for all Demos, 80% for all Quotes.

This is a sample data set with Stage and Win % entered.

Sample Sales Deals data with Stage and Win %
Sample Sales Deals data with Stage and Win %

4 more Columns are provided to enter the Email address, Phone Number, Next Activity Date and Notes.

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 % and Stage columns for deals as needed, so that your active pipeline is always reflecting the reality.

After working on the deal, we would usually schedule the next activity for a future date. We can enter that next activity date in the Next Activity Date column and enter details in the Notes column.

Enter follow up details – Next Activity Date and Notes
Enter follow up details – Next Activity Date and Notes

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

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

Sample Closed Deals data with Status Won or Lost and Close Date
Sample Closed Deals data with Status Won or Lost and Close Date

If the Close Date is left blank, you will see a red background color indicating that the entry is missing.

Data Validation – Missing Close Date for Closed Deals – Shown by Red data field
Data Validation – Missing Close Date for Closed Deals – Shown by Red data field

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.

Tips:

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

For lost deals, Stage should reflect the last stage the deal was in. This will be used to identify where deals are being lost the most.

Step 3: Monitor Sales Performance

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

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

Active Pipeline

Sales Pipeline Tracker – Active Sales Pipeline – Metrics
Sales Pipeline Tracker – Active Sales Pipeline – Metrics

First, the pipeline summary metrics.

Sales Pipeline Summary Metrics Deals, Deal Value and Expected Value
Sales Pipeline Summary Metrics Deals, Deal Value and Expected Value
  1. 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.
  2. 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.
  3. 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 %.

Metrics by Stage

As the summary metrics describe the current scenario of the entire pipeline, it is also equally important to know the metrics on the deals by stage.

Sales Pipeline Metrics by Stage – Deals, Deal Value and Expected Value
Sales Pipeline Metrics by Stage – Deals, Deal Value and Expected Value

As shown in the image above, the same three metrics (Number of Open Deals, Total Deal Value and Expected Deal Value) are shown for each of the 4 stages.

Closed Deals Performance

The previous section showed the active pipeline. As important that is, it is also important to know how we have been performing in closing deals. It can tell a lot about the future.

The next section shows the performance of closed deals.

Performance Metrics of Closed Deals – Conversion Rate, Time To Win Deals
Performance Metrics of Closed Deals – Conversion Rate, Time To Win Deals

Deals Won and Lost

Closed Deals – Number of Deals Won and Lost – Deal Value
Closed Deals – Number of Deals Won and Lost – Deal Value

In the sample above, 3 deals were won for total of $161,000 and 7 deals were lost valued at a total of $74,000.

Conversion Rate & Average Time to win deals

Conversion Rate and Average Time to win Deals
Conversion Rate and Average Time to win Deals

3 deals were won out of 10 closed deals and hence Conversion Rate is shown as 30%.

The 3 won deals took on average 17 days to close. This is based on the Close Date and Created Date we entered.

Where are deals lost?

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

Deals Lost by Stage
Deals Lost by Stage

Of the 7 deals we lost, 29% were lost in the Lead stage, 14% in the Opportunity stage, 43% in Demo stage and 14% in Quote stage.

We have entered a small sample of deals here. As we enter more and more data, these % will become 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.

You may also be interested in...

Rental Inventory Tracker – Free Google Sheet Template

Posted on

As an owner of a rental business, it is important to know the amount of inventory that is available and the amount of inventory rented out. This Google Sheet Template is developed to help people in tracking the availability of inventory so that it can be rented out. 

This Google Sheet template can also provide details about the availability of a particular product within a particular time period.

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

Rental Calendar – Available Quantity
Rental Calendar – Available Quantity

How to Use the Rental Inventory Tracker Google Sheets Template

Steps to use this template:

  1. Make a Copy and save this template in your Google Drive.
  2. Configure the input entries.
  3. The calendar is populated automatically.

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 copy
Make a copy of the sheet

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

Enter the list of assets or equipment in the Assets sheet.

Enter Rental Equipment or Rental Assets Items
Enter Rental Equipment or Rental Assets Items

Enter Asset Name, Description and # of Items you have of each asset. The last two columns are automatically calculated. When you begin, Rented Out will be 0 and all the items will be In Stock.

Enter Rental orders in Orders sheet

Rental Inventory Tracker – Enter rental orders details
Rental Inventory Tracker – Enter rental orders details

Enter Order Number,  Order Date, Asset being rented and Quantity being rented. Enter Rent Out Date (date when the asset needs to be given to customer) and Return Date (date when asset will be returned by the customer).

Use the Notes column to keep track of any information related to the order. For example, you can enter contact information of the customer.

Check Availability of Rental Asset

At the top of the Orders sheet is a simple availability calculator, which will provide the quantity available to rent for a specific asset given the rent out and return dates.

Check availability of Rental Items
Check availability of Rental Items

If the available quantity is negative, that indicates that there is not enough inventory.

Check availability of Rental Items – Not enough Inventory
Check availability of Rental Items – Not enough Inventory

This availability calculator can handle rental windows of up to 90 days long.

Check availability – Rental Assets – Window longer than 90 days
Check availability – Rental Assets – Window longer than 90 days

Step 3: Calendar is populated automatically

View Availability Calendar

The Calendar sheet provides a flexible and useful calendar that shows the availability of rental assets over 31 days. You can choose any start date for this calendar view. Also, you can choose to see Available Quantity or Rented Quantity. Here is a screenshot of Available Quantity.

Rental Calendar – Available Quantity
Rental Calendar – Available Quantity

The red colored cells indicate days when assets will not have enough inventory to cover the rental orders. You need to make sure that you re-do the rental order by working with the customer or may have to cancel the order.

Here is a screenshot of Rented Quantity.

Rental Calendar – Rented Quantity
Rental Calendar – Rented Quantity

This will help you to be aware of how many items will be rented out on any specific day.

You may also be interested in...

Inventory and Sales Manager – Free Google Sheet Template

Posted on

If you are struggling in managing your product inventory and sales this Google Sheet is suited for you. For everyone who runs a small business of buying products from suppliers and selling them to customers this sheet will be very helpful. This template is suitable for both retail and wholesale business. 

What more? The template will assist in knowing the inventory levels of each product and understanding which products to re-order. 

This template is designed for Google sheets, but if you are looking for an Excel template, please visit the Inventory and Sales Manager.

Inventory Spreadsheet – Summary Metrics
Inventory Spreadsheet – Summary Metrics

Features of the Inventory and Sales Manager Google Sheets Template

  • Enter and manage up to 2000 different Products
  • Set custom re-order points for each product
  • Simple and Easy data entry
  • Know current inventory levels of each product
  • Identify the products to be re-ordered
  • Know if the sale orders can be fulfilled
  • Easily understand the sales and purchase patterns (monthly and cumulative)
  • Quickly see your top customers and suppliers
  • Identify your best performing products
  • Know how the different product categories contribute to sales
  • Easily retrieve and view your order details

How to Use the Inventory and Sales Manager in Google Sheets Template

Overview of steps

  • Make a copy of the sheet
  • Input the Product and Order details
  • View details & Report

Step 1: Make a copy of the sheet

Click on the link above to access the Google Sheet. Make a copy of the Sheet as shown in the screenshot below. Save this template on your Google Drive.

Make a copy
How to make a copy of Google Sheet

Note: Once you open the saved template, you have to just configure your inputs to customize the calendar according to your needs. The reports will be auto populated. 

Step 2 : Input the Product and Order details

Enter Products

Enter list of products and re-order points in the Products worksheet

Google Sheet Inventory Management – Products Table
Google Sheet Inventory Management – Products Table

Product Category: This allows you to categorize products. If you have numerous products, categorizing similar products together can help in understanding product performance.

Re-order Point: Amount that you set for each product, where when the current inventory level hits that amount, you will place a new purchase order to replenish inventory. (For more, read Re-Order Point Article in Wikipedia)

Enter Orders

Enter the line items for all the orders (both purchase and sale) in the Orders_and_Inventory worksheet

Inventory Manager Template – Enter Order Details
Inventory Manager Template – Enter Order Details

If you have any existing inventory when you start using the template, enter them first. You can then continue to enter your new orders (purchase and sales) as they happen. The template will then give you accurate count of your inventory.

  • Order Number: This Order number is not used in the template to calculate anything. This has been provided for you to track your orders easily. You can filter the Orders table by choosing specific order number to see all the items in that order. If your systems generate any order numbers, you can enter them here. If you don’t have any such systems, you can create your own. The only recommendation is that you should have a unique order number for each order.
  • Order Type: There are two types of Orders: Purchase and Sale. When you place an order to acquire products from suppliers, it is called a Purchase order. When your customer places an order to buy products from you, it is called a Sale.
  • Order Date: For Purchase orders, this is the date when the order is placed by you to your supplier. For Sale orders, this is the date when the order is placed by your customer to you.
  • Expected Date: For Purchase orders, this is the date when the inventory becomes available for you to sell. For Sale orders, this is the date when the inventory will leave you to the customer.
  • Partner: For Purchase orders, your supplier is the Partner. For Sale orders your customer is the Partner.
  • Quantity: Number of units of products. The unit can be any numeric value. Even if your unit is not whole numbers, you can still use the Quantity field.
  • Unit Price: In Purchase orders, this is the cost of buying one unit of the product. In Sale orders, this is the revenue from selling one unit of the product.
  • Amount (Calculated field): (Unit Price X Quantity) = represents the amount of money. In Purchase orders this would be money leaving you and in Sale orders, this would be money that customers pay you.
  • Inventory Availability (Calculated field): This is the quantity (number of items) of the product available in inventory as of the Expected Date.

Step 3 : View details & Report

View information about overall inventory availability

Inventory Googlesheet Template – Summary Metrics
Inventory Googlesheet Template – Summary Metrics
  • Current Inventory of a product = (Total Purchases of Product – Total Sales of Product) as of today
  • Products Available: Number of Products where the current inventory level is greater than 0.
  • Quantity: Total Number of items of all Products currently available
  • Products to Re-order: Number of Products where the current inventory is less than or equal to the re-order point
  • Order Items that cannot be fulfilled (Current): Among the orders where the fulfillment date is less than or equal to today, number of line items in orders where the available inventory is less than the Sale quantity
  • Order Items that cannot be fulfilled (Future): Among the orders where the fulfillment date is in the future, number of line items in orders where the available inventory is less than the Sale quantity

View details of one specific product

Choose a product from the drop down and see details of that specific product.

Choose Product to view current inventory
Choose Product to view current inventory
  • Pending Purchase Quantity: Quantity in the Purchase Orders that are expected to be available in the future
  • Pending Sale Quantity: Quantity in the Sale Orders that are expected to be delivered in the future

View products to re-order

List of Products to order
List of Products to order

If there are line items that cannot be fulfilled or if there are products to re-order, take actions appropriately.

View Report

View the Report worksheet to understand the purchase/sales trends and also to identify the top performing products and most valuable suppliers/customers.

The report sheet has slicers (filters) at the top.

Inventory and Sales Manager – Google Sheet Template – Report Filters/Slicers
Inventory and Sales Manager – Google Sheet Template – Report Filters/Slicers

Amount and Cumulative Amount by Month

Inventory and Sales Manager – Google Sheet Template – Report – Amount and Cumulative Amount
Inventory and Sales Manager – Google Sheet Template – Report – Amount and Cumulative Amount

Quantity and Cumulative Quantity by Month

Inventory and Sales Manager – Google Sheet Template – Report – Quantity and Cumulative Quantity
Inventory and Sales Manager – Google Sheet Template – Report – Quantity and Cumulative Quantity

Amount distributed across Product Categories by Month

Inventory and Sales Manager – Google Sheet Template – Report – Amount by Product Category
Inventory and Sales Manager – Google Sheet Template – Report – Amount by Product Category

Quantity distributed across Product Categories by Month

Inventory and Sales Manager – Google Sheet Template – Report – Quantity by Product Category
Inventory and Sales Manager – Google Sheet Template – Report – Quantity by Product Category

Amount Distributed across Partners

Inventory and Sales Manager – Report – Amount by Partner
Inventory and Sales Manager – Report – Amount by Partner

Product Ranking based on Sales Amount or Quantity

Inventory Sheet – Google Sheet Template – Report – Product Ranking
Inventory Sheet – Google Sheet Template – Report – Product Ranking

You may also be interested in...

Retail Inventory Tracker – Free Google Sheet Template

Posted on

As an owner of a retail business, it is important to know the availability of inventory to manage orders. This Google Sheet Template is developed to help people in buying products from suppliers and selling them to their customers in a retail business scenario.

This Google Sheet Template can be effectively used to determine which products to reorder, who are your best suppliers and customers. 

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

Retail Inventory Tracker

Key Features of this template are

Order Management

  • 3 types of orders (Sale, Purchase, Adjust)
  • Handles product returns
  • Auto-Populate product prices in orders

Inventory Management

  • Calculates current inventory of each product
  • Set re-order points and know what to order

Finance

  • Handles tax
  • Handles product level and order level discounts
  • Calculates Cost of Goods Sold (COGS) and Profit

Data Management

  • Easily access Product, Partner (Customer and Supplier) and Order Lists
  • Maintain history of Product price data

Reporting

  • 6 page interactive report of business metrics
  • 12 month trends of key metrics
  • Identify best products and partners
  • Calculates Inventory value

How to Use the Retail Inventory Tracker Google Sheets Template

Steps to use this template:

  1. Make a Copy and save this template in your Google Drive.
  2. Configure the input entries.
  3. The reports are populated automatically.

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.

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.

Initial Setup

These Initial Setup steps are to be done first as a one-time activity.

This Google Sheet Template is one of the simplest templates to get started. Following are the inputs needed for this template to populate the reports automatically.

Enter Business Information

In the Settings sheet, Enter your business information such as address, email and phone number.

Enter business information such as addresses and phone number
Enter business information such as addresses and phone number

Enter Product Categories

If you are selling several products in your business, it is recommended that you categorize your products. This helps a lot in managing them and understanding their sales performance.

Enter Product Categories in your retail business
Enter Product Categories in your retail business

Enter Products

In the Products sheet, let’s enter each of our products in a separate row.

Please start entering from row 4

Enter Products with Name, Description Starting inventory and Re-order point
Enter Products with Name, Description Starting inventory and Re-order point

Let’s see each of the fields in the Products table. 

  • ID: Unique identification of product. This has to be unique. Please do not repeat the same ID or leave the field blank.
  • NAME: Name of the product
  • DESCRIPTION: Description of the product, as needed in our business.
  • STARTING INVENTORY: This is the quantity of the product we have when we begin using the template. This is entered only once and does not have to be updated daily.
  • RE-ORDER POINT: The quantity of product at which you would like to replenish by ordering.

There are a few more columns of product information we can input.

Enter Unit of measurement, Product category, Tax information for each product
Enter Unit of measurement, Product category, Tax information for each product
  • UNIT: This is how we measure this specific product.
  • CATEGORY: Product category to which this product belongs.
  • TAXABLE: In our business, if we have products that are not taxable, we can enter NO. If tax is applicable, just leave it blank. By default, tax will be applicable.
  • PR CUST FIELD: This field is provided as a placeholder for you to enter any information you need at Product level. You can rename the field and use it as needed.

The other columns in this sheet are all calculated columns.

The columns that have Green colored labels are all calculated columns. Please do not edit the formulas in them. 

Enter Product Prices

In the Prices sheet, we will be entering Purchase and Sales prices.  This information will be used to auto-populate prices in our orders. This will save a lot of time in data entry of orders.

Enter product purchase and sales prices in Prices sheet
Enter product purchase and sales prices in Prices sheet

Purchase Price is the price we pay our suppliers to purchase products. Sales Price is the price we sell the products to our customers at.

To begin with, let’s assume we start using this template from Nov 1, 2016 to enter orders.

We enter each product in this Prices table and enter Nov 1, 2016 as the Effective Date. The Purchase and Sales prices we enter will be the prices effective as of Nov 1, 2016.

What if price changes?

The template is designed to accommodate price changes for products. You may have an increase in prices of certain products over time. Not a problem.

If price changed for a product from Jan 1, 2017, we will just add a new row, enter the Product ID, Effective date (as 01-Jan-2017) and the new Purchase and Sales prices. Please note that we have to add new rows whenever prices change, and not to replace the older data.

We have to enter both purchase and sales price in each row, even if only one of them changes.

Enter list of Partners 

In the Partners sheet, we store the list of our partners. Partners include Suppliers and Customers.

Enter customer and supplier information in Partners sheet
Enter customer and supplier information in Partners sheet

If a partner is both a customer and a supplier (it is possible in some scenarios), enter the partner only once.

  • Partner ID and Partner Names should be unique.
  • Enter Shipping and Billing address, E-mail address and Phone number.
  • Enter the primary person of contact for each company in the CONTACT field.

This sheet now serves as a nice organized set of data about your partners.

We have completed the initial set up now. It’s time to enter our first order.

Creating Orders

Before we enter our order, let’s learn about the types of orders. You can create 3 types of orders in this template.

3 types of orders Purchase, Sale and Adjust
3 types of orders Purchase, Sale and Adjust
  1. PURCHASE: When we purchase products from our suppliers, we enter a PURCHASE order. This order will add the purchased items to inventory on the Expected Date.
  2. SALE: When we sell products to our customers, we enter a SALE order. This order will subtract sold items from the inventory on the Expected date.
  3. ADJUST: We can create an ADJUST order and enter negative quantity values to reduce inventory or positive values to increase inventory as needed. This can be used to adjust our inventory numbers to ensure that the numbers match the inventory on hand. For example, we may lose products due to damage or expiry or other reasons. We would want to adjust our inventory accordingly and that’s where we can use ADJUST order type.

Creating a Purchase Order

Orders are entered in this template in 2 stages – 1) Order Header  and 2) Order Details. Let’s use an example. The products here are shirts for boys and girls. They are available in different colors.

In the Order Headers sheet, we enter the following information.

Entering a purchase order in Order Headers sheet
Entering a purchase order in Order Headers sheet

The Order Number should be unique. In other words, each order should be entered in one and only one row. The field should not be blank.

We can enter any method of numbering orders. The template does not limit that and does not create any pre-defined order numbers. Here, we have entered ‘P1’ as order number, to reflect that it is the first purchase order we are entering. 

Order Date and Expected Date

Each order will have 2 dates. Order Date and Expected Date. Order Date is the date when the order is placed. Expected Date is the date when the inventory is impacted.

For example, if you place a purchase order on Nov 5th. The supplier says the products will reach your inventory on Nov 25th. Here, Nov 5th is Order Date and Expected Date is Nov 25th. If there is a delay later and the supplier says it will only reach on Nov 27th, then we have to update the Expected Date of our order to Nov 27th.

Order Type is ‘Purchase’ and we have chosen our supplier in the Partner Name field.

There are additional information we can enter in the Order Header.

Additional information on Purchase Order – Order Header
Additional information on Purchase Order – Order Header
  • OTHER CHARGES: Any additional cost on the order. For example, shipping charges.
  • ORDER DISCOUNT: Any additional order level discount amount (not %). We will be entering product level discounts later.
  • TAX RATE: Tax Rate % applicable for this order. We can have different tax rates for different orders.
  • ORDER NOTES: Enter any notes for your reference to this specific order.

Now, we enter the items on the order in the Order Details sheet.

Entering order line items in Order Details sheet – Product, Quantity and any discount
Entering order line items in Order Details sheet – Product, Quantity and any discount

It is very simple. Enter Order Number, Product ID, Quantity and any Unit Discount.

Here, we have entered a purchase order to purchase 15 units of Boys Shirt in Red color and 10 units of Girls Shirt in Red color. There is a discount of $2 (any currency you use) for each of the 10 Girls shirts and no discounts for the Boys shirts.

The template will calculate amounts for each line item.

Calculations of Amount and tax for each line item in the order
Calculations of Amount and tax for each line item in the order

Creating a Sales Order

Entering a sales order is very similar to the purchase order, except that our Order Type is ‘Sale’ now.

Entering a Sale Order in Order Header sheet
Entering a Sale Order in Order Header sheet

As shown in the image above, to add an order, we just add our entry in a new row in Order Headers sheet.

We enter S1 as Order Number. This sale order was placed on Nov 26th and products were given to customers on the same day (Nov 26th).

 In the Order Details sheet, we add 2 rows as we are selling two products (MSBL and MSGR).  10 units of MSBL and 5 units of MSGR.

Entering Sale Order line items with Product and Quantity in Order Details sheet
Entering Sale Order line items with Product and Quantity in Order Details sheet

This order will now automatically reduce inventory for each of the products, effective as of Nov 26th (Expected date).

Handling Supplier Return

If we have a situation where we want to return products back to our supplier due to some reason (example: defective products), we can do so easily.

We will enter a new Purchase order.

Tip: For easier identification of return orders, you can enter order number differently. For example, use a prefix of PR for purchase return orders.

In our example, after receiving the products on Nov 25th, we notice that there are 5 defective BRD units. We want to return them.

So, on the next day (Nov 26th), we send the products back to the supplier.

Purchase Return Order – Handling Supplier returns in retail
Purchase Return Order – Handling Supplier returns in retail

In the Order Details sheet, we will enter the information on returning product and quantity.

Purchase Return Order – Handling Supplier returns in retail
Purchase Return Order – Handling Supplier returns in retail

Since we are returning 5 units of BMGR, I have entered -5 as Quantity. Entering a negative value is important. That ensures that our inventory is reduced by 5 units for this product.

Handling Customer Return

Similar to the Supplier Return, we can also handle customer returns. If customer decided to return products to us, we can enter that information in the template. We use a Sale order for that purpose.

Customer Return Order – Handling products returned by customer
Customer Return Order – Handling products returned by customer

In this example, SR1 is the sale return order that is placed on Nov 30th.

Customer Return Order – Entering quantity of products returned by customer
Customer Return Order – Entering quantity of products returned by customer

4 units of BMGR are returned by the customer.  We enter -4 as Quantity. This will be used by the template to add 4 units to GRD inventory, effective as of Nov 30th.

Creating an ADJUST order

On some occasions, we may find that a product is either expired or damaged locally at the warehouse. We cannot return it to the supplier, and we cannot sell that to customer too. We need to make sure that our current inventory calculations reflect the true available inventory to sell. This is where we can use the order type ‘Adjust’.

In the Order Headers sheet, we first create a new Adjust order.

Entering an Adjust order to handle scenarios by product expiry or damage
Entering an Adjust order to handle scenarios by product expiry or damage

For example, one BMGR shirt was damaged in the warehouse and we notice it on Dec 1st. So, we enter it as shown below.

Adjust Order – Entering quantity and discount
Adjust Order – Entering quantity and discount

We enter -1 as Quantity. This will reduce the inventory by 1.

If we want to increase inventory levels without entering a purchase order, we can use an ADJUST order where we enter positive values as Quantity.

 We enter 35 as Unit Discount (as that is the sales price of the product). This is to zero out the impact on cost. If we are not incurring any additional cost by disposing the shirt, then this method is recommended.

If we incur any additional cost, then we enter the appropriate Unit Discount so that the total Amount after Tax reflects the disposal cost.

Step 3: Automatically populate reports

Business Performance Reporting

The template has extensive automated and interactive reporting in the Report sheet.

Current Status (Inventory level and Inventory value)

Current Status of Inventory – Retail Business
Current Status of Inventory – Retail Business

The following metrics are displayed to reflect the status as of today.

  • Total inventory (quantity) on hand
  • Total inventory to Come (ordered from suppliers already and will reach our inventory in future)
  • Total inventory to Go (ordered by customers already and will leave our inventory in future)
  • Number of Products to re-order (products whose current inventory is at or below its Re-Order Point)
  • Inventory Value (calculated based on current purchase price of the products on hand)

 The above presents the overall summary of all products together. We would also want to see this information individually for each product. To do that, we go to the Products sheet.

Inventory levels of each product in Products table
Inventory levels of each product in Products table

Now, the rest of the Report sheet presents an interactive way of accessing business performance metrics.

We can customize the date range for the report by choosing any start and end dates.

Enter Start and End Date for Report
Enter Start and End Date for Report

We leave the Refresh as ON. If you enter a lot of order data over time and if you notice the file is getting slower, you can turn this OFF. It will stop the report from refreshing constantly and that will improve performance.

For the date range we entered, we can see the summary metrics.

Summary Business Performance Metrics for Retail Business
Summary Business Performance Metrics for Retail Business
  • SALES
    • Sales Qty: Total of quantity on Sale orders. Considers returns as well.
    • Sales Amount: Total Order amount on the Sale orders. Includes product level discounts. Does not include tax, order level charges and order level discounts.
    • Sales Tax: Total tax amounts on Sale Orders
    • Qty Returned from Customer: Quantity returned by customers
    • Discount Amt Given: Total amount of discount given to customers
    • Other Charges: Total of other charges on all Sale orders
  • PURCHASE
    • Purchase Qty: Total of quantity on Purchase Orders. Considers returns as well.
    • Purchase Amount: Total Order amount on the purchase orders. Includes product level discounts. Does not include tax, order level charges and order level discounts.
    • Tax: Total tax amounts on Purchase Orders
    • Qty Returned to Supplier: Quantity returned to suppliers
    • Other Charges: Total of other charges on all Purchase orders
  • PROFIT
    • Gross Profit: Sales Amount – Cost of Goods Sold
      • Cost of Goods Sold is the sum of purchase price of products sold. Purchase price is the price of product as of Sale order date.

We can view these metrics by month, for 12 months at a time.

Monthly Metrics on Sales and Purchases – Sales Amount, Cost of Goods Sold and Profit
Monthly Metrics on Sales and Purchases – Sales Amount, Cost of Goods Sold and Profit

We can choose one of the metrics to display data on a chart showing trends over 12 months.

Choose metric to display on chart
Choose metric to display on chart

Monthly Metrics on Sales and Purchases – Trend Chart
Monthly Metrics on Sales and Purchases – Trend Chart

Top 10 and Bottom 10 Products

One of the important pieces of understanding business performance is knowing which products are selling the most and which ones are not. We have 3 ways of measuring sales – Quantity, Amount and Margin. This allows us to understand the true impact of the products to the business.

Choose one of the 3 Sales metrics (Sales Quantity, Sales Amount and Margin)
Choose one of the 3 Sales metrics (Sales Quantity, Sales Amount and Margin)

We will see top 10 and bottom 10 Product Categories by the selected Sales metric.

Top 10 Product Categories by Sales Metric
Top 10 Product Categories by Sales Metric

Bottom 10 Product Categories by Sales Metric
Bottom 10 Product Categories by Sales Metric

Similarly, the top 10 and bottom 10 Products by sales metric.

Top 10 Products by Sales Metric
Top 10 Products by Sales Metric

Bottom 10 Products by Sales Metric
Bottom 10 Products by Sales Metric

If we want to look for details of a specific product, we can choose the product ID from the drop down.

Choose Product to view Performance metrics
Choose Product to view Performance metrics

Product Performance Summary metrics
Product Performance Summary metrics

View Inventory level for selected product
View Inventory level for selected product

Sales Quantity – Monthly Trend for chosen product
Sales Quantity – Monthly Trend for chosen product

Partner Performance

Another important aspect is to understand best partners (customers and suppliers).

Top 10 Customers by Sales
Top 10 Customers by Sales

Top 10 Suppliers by Purchase Amounts
Top 10 Suppliers by Purchase Amounts

We can then see the details of one specific partner at a time.

Choose Partner to view Performance metrics
Choose Partner to view Performance metrics

Partner Performance Summary metrics
Partner Performance Summary metrics

You may also be interested in...

2020 Calendar – Free Google Sheet Template

Posted on

You can plan ahead for the upcoming year by mapping out holidays and important events on a Google Sheets Calendar. We have provided 20 different design templates for the 2020 Calendar that are customizable and print-ready. The design templates include annual (yearly), monthly, weekly and daily calendars.

What more? The templates are expandable so you can continue using them for the forthcoming calendars like 2021, 2022 etc., as well. This Calendar could be used for personal as well as professional purposes. 

This template is designed for Google sheets, but if you are looking for an Excel template, please visit the 2020 Excel Calendar.

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

Through this article, you will understand:

  • Overall features of the 2020 Calendar Google Sheets Template
  • How to use 2020 Calendar in Google Sheets Template
  • How to download this template for free?
  • 20 Calendar designs and  their features
  • Tips to further customize the template

How to download the 2020 Calendar in Google Sheets Template

Click on the link above to access the Google Sheet. Make a copy of the Sheet as shown in the screenshot below. Save this template on your Google Drive. 

How to make a copy of Google Sheet
How to make a copy of Google Sheet

Note: Once you open the saved template, you can see that the Dashboard and Calendar are populated automatically. You have to just configure your inputs to customize the calendar according to your needs. 

Features of the 2020 Calendar Google Sheets Template

  • You can customize the calendar by choosing the Year, Weekends, Starting month and Starting day of week. 
  • You can enter Enter Holidays and Events to be displayed on the Calendar
  • The template automatically creates 20 Calendar designs 
    • 12 (of the 20) designs automatically list the Holidays and events
    • 2 (of the 20) designs allow you to type events directly on the calendar.
    • All 20 designs are ready to be printed or exported to PDF as well.
  • Easy to format/reformat (font size, font, text colors, fill colors)

How to Use the Template

Overview of steps

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

Step 1: Enter Basic Settings

  • To build the calendar, enter the year you want. The template in such a way that it will automatically populate the Calendar no matter what year you choose.
Basic Settings – 2020 Excel Calendar Template
Basic Settings – 2020 Excel Calendar Template
  • By default, the starting month is January but you can modify the starting month to suit your needs. For instance, the starting month of a new fiscal year may not necessarily be January. 
Change Beginning Month of Calendar
Change Beginning Month of Calendar
  • By default, the calendar shows weeks that start with Sunday. However, you can modify it to start with any weekday.
Start Week from any day
Start Week from any day
  • Once you name your calendar, it will appear on some of the calendar designs automatically.

Step 2: Choose Weekends

  • The Calendar highlights the weekend days in red colored font. You may modify the weekends to suit your purpose. 

Choose Weekends
Choose Weekends

Chosen Weekends - Sample
Chosen Weekends – Sample

In the above image, we have chosen Sundays and Saturdays as weekends. Hence, they appear in red font on all the calendars. 

Step 3: Enter Holidays

Enter the list of holidays for your personal/professional/company/business needs in the Holidays table. The template displays a list of holidays in the U.S. and India for reference.

Enter Holidays for 2020 Calendar
Enter Holidays for 2020 Calendar

Step 4: Enter your personal or company events

In addition to holidays, you can either add personal events or your company’s events in the calendar based on your needs. The following screenshot features some sample personal events for your reference.

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

Step 5: Pick a Calendar Design from the 20 Designs

  • Once the above inputs are fed, the template populates your desired calendar for display. 
  • Use the ‘Pick a Calendar’ sheet to view the blueprint of those calendars. Click on any design you want and the template will take you to the corresponding calendar sheet.
20 Calendar Designs in 2020 Calendar Template
20 Calendar Designs in 2020 Calendar Template
  • Alternatively, you can navigate through sheets 1 and 20 to pick your calendar designs.
20 Calendar Sheets
20 Calendar Sheets

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

Step 6: Print the Calendar or Export to PDF

  • To print, use the Print option from the File Menu (alternatively, Ctrl+P)
  • Since the template is preset for printing calendars, you can quickly preview the calendar and click on Print. 
  • If you need only specific pages printed then choose the option Workbook under Print. Then under Selection, choose the pages you want to print and click, Apply. 
  • Choose Next on the top-right corner of the page. It will lead you to a dialogue box which will give you options to either print the files or save them as PDF.
Printing the Calendar
Printing the Calendar
  • Alternatively, you can download the sheets as PDF. Just choose the Download option from the File Menu. Choose PDF Document (.pdf). This will take you to the Print page, except that you will be able to export the sheets/Workbook to PDF. 

Saving the Calendar as PDF

Saving the Calendar
Saving the Calendar as PDF

20 Calendar Designs

Let’s review the features of each of the 20 calendar designs:

Calendar Design 1

1 Page 12 Months – 4 X 3 

2020 Calendar Design 1 – 1 Page 12 Months – 4 X 3
2020 Calendar Design 1 – 1 Page 12 Months – 4 X 3
  • The entire calendar year is printed on a page, featuring the months in 4 rows and 3 columns. 
  • The calendar highlights Holidays, Events and Weekends.
  • The calendar doesn’t list Holidays and Events. 
  • You can enter the Notes at the bottom of the page. 

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

Calendar Design 2

1 Page 12 Months – 3 X 4

2020 Calendar Design 2 – 1 Page 12 Months – 3 X 4
2020 Calendar Design 2 – 1 Page 12 Months – 3 X 4
  • The entire calendar year is printed on a  page, featuring the months in 3 rows and 4 columns. 
  • The calendar highlights Holidays, Events and Weekends.
  • The calendar doesn’t list Holidays and Events.
  • You can enter the Notes at the bottom of the page.

Calendar Design 3

1 Page 12 Months – 2 X 6 with Events

2020 Calendar Design 3 – 1 Page 12 Months – 2 X 6
2020 Calendar Design 3 – 1 Page 12 Months – 2 X 6
  • The entire calendar year is printed on a page, featuring the months in 2 rows and 6 columns. 
  • The calendar highlights The Holidays, Events and Weekends.
  • The calendar lists 20 Holidays and 20 Events.

Calendar Design 4

1 Page 12 Months –  6 X 2 with Events

2020 Calendar Design 4 – 1 Page 12 Months – 6 X 2
2020 Calendar Design 4 – 1 Page 12 Months – 6 X 2
  • The entire calendar year is printed on a page, featuring the months in 6 rows and 2 columns.
  • The calendar highlights The Holidays, Events and Weekends.
  • The calendar lists 20 Holidays and 30 Events.

Calendar Design 5

1 Page 12 Months –  12 X 42 with Events

2020 Calendar Design 5 – 1 Page 12 Months – 12 X 42 with Events
2020 Calendar Design 5 – 1 Page 12 Months – 12 X 42 with Events
  • The entire calendar year is printed on a page, featuring the months in 12 rows and 42 columns. 
  • The calendar highlights The Holidays, Events and Weekends.
  • The calendar lists 10 Holidays and 20 Events.

Calendar Design 6

1 Page 12 Months –  12 X 31 with Events

2020 Calendar Design 6 – 1 Page 12 Months – 12 X 31 with Events
2020 Calendar Design 6 – 1 Page 12 Months – 12 X 31 with Events
  • The entire calendar year is printed on a page, featuring the months in 12 rows and 31 columns.
  • The calendar highlights The Holidays, Events and Weekends.
  • The calendar lists 10 Holidays and 20 Events.

Calendar Design 7

6 Pages – Planner

2020 Calendar Design 7 – 6 Pages – Planner
2020 Calendar Design 7 – 6 Pages – Planner
  • The entire calendar is printed in 6 pages, featuring the months in 31 rows and 12 columns.
  • The calendar highlights the Holidays, Events and Weekends.
  • The calendar doesn’t list Holidays and Events. 
  • You can type notes directly next to each date on the calendar.

Calendar Design 8

1 Page – 54 Weeks – with Events

2020 Calendar Design 8 – 1 Page – 54 Weeks – with Events
2020 Calendar Design 8 – 1 Page – 54 Weeks – with Events
  • The entire calendar is printed on a page, featuring the months in 54 rows and 7 columns. 
  • The calendar doesn’t highlight the Holidays and Events.
  • The calendar lists 20 Holidays and 30 Events. 

Calendar Design 9

12 Pages – Monthly

2020 Calendar Template – Monthly – January 2020
2020 Calendar Template – Monthly – January 2020
  • The entire calendar year is printed in 12 pages. 
  • Each page displays a month in 6 rows and 7 columns.
  • The calendar highlights Holidays, Events and Weekends
  • The calendar doesn’t list Holidays and Events. 

Calendar Design 10

6 Pages – with Events

2020 Calendar Design 10 – 6 Pages with Events
2020 Calendar Design 10 – 6 Pages with Events
  • The entire calendar year is printed in 6 pages.
  • Each page displays 2 months, featuring one month in 6 rows and 7 columns. 
  • The calendar highlights Holidays, Events and Weekends
  • The calendar lists 10 Holidays and 10 Events.

Calendar Design 11

4 Pages – with Events

2020 Calendar Design 11 – 4 Pages with Events
2020 Calendar Design 11 – 4 Pages with Events
  • The entire calendar year is printed in 4 pages.
  • Each page displays 12 months and each month is featured in 6 rows and 7 columns.
  • The calendar highlights Holidays, Events and Weekends.
  • The calendar lists 5 Holidays and 5 Events

Calendar Design 12

3 Pages – 4 Months on each page

2020 Calendar Design 12 – 3 Pages with Events
2020 Calendar Design 12 – 3 Pages with Events
  • The entire calendar year is printed in 3 pages. 
  • Each page shows 4 months and each month is displayed in 6 rows and 7 columns. 
  • The calendar highlights Holidays, Events and Weekends
  • The calendar doesn’t list Holidays and Events

Calendar Design 13

2 Pages – 6 Months on each page

2020 Calendar Design 13 – 2 Pages – 6 Months each
2020 Calendar Design 13 – 2 Pages – 6 Months each
  • The entire calendar year is printed in 2 pages. 
  • Each page shows 6 months and each month is displayed in 6 rows and 7 columns
  • The calendar highlights Holidays, Events and Weekends.
  • The calendar doesn’t list Holidays and Events

Calendar Design 14

12 Pages – Monthly – with Events

2020 Calendar Design 14 – 12 Pages with Events
2020 Calendar Design 14 – 12 Pages with Events
  • The entire calendar year is printed in 12 pages
  • Each page shows a month and each month is displayed in 6 rows and 7 columns
  • The calendar highlights Holidays, Events and Weekends.
  • The calendar lists Holidays and Events for each month separately. 

Calendar Design 15

12 Pages – Monthly – Type Events

2020 Calendar Design 15 – 12 Pages – Type your events
2020 Calendar Design 15 – 12 Pages – Type your events
  • The entire calendar year is printed in 12 pages.
  • Each page shows a month and each month is displayed in 6 rows and 7 columns.
  • The calendar highlights Holidays, Events and Weekends.
  • The calendar doesn’t list Holidays and Events for each month. 
  • However, you can type any text below any of the dates on the calendar directly.

Calendar Design 16

12 Pages – Picture Calendar

2020 Calendar Design 16 – Picture Calendar
2020 Calendar Design 16 – Picture Calendar
  • The entire calendar year is printed in 12 pages.
  • Each page shows 1 month and each month is displayed in 6 rows and 7 columns.
  • The calendar highlights Holidays, Events and Weekends.
  • The calendar doesn’t list Holidays and Events for each month. 
  • The calendar has a provision for you to upload images for each calendar month. You can Left Click on the image where you see 3 vertical dots and change the picture for each month. Choose your images online or from saved files on your computer. 

Adding Image to the Calendar
Adding Image to the Calendar

Choosing Images for the Calendar
Choosing Images for the Calendar

Calendar Design 17

12 Pages – Quotes Calendar

2020 Calendar Design 17 – Quotes Calendar
2020 Calendar Design 17 – Quotes Calendar
  • The entire calendar year is printed in 12 pages. 
  • Each page shows a month and each month is displayed in 6 rows and 7 columns.
  • The calendar highlights Holidays, Events and Weekends.
  • The calendar doesn’t list Holidays and Events for each month.
  • Each calendar has a provision for you to display some text for each calendar month. 
  • Create your own customized calendar of quotes: click on placeholder text and type any text for each month. 

Calendar Design 18

1 Page – Weekly Calendar

2020 Calendar Design 18 – Weekly Calendar
2020 Calendar Design 18 – Weekly Calendar
  • A calendar week is printed on a page
  • To generate a weekly calendar for any year, type in the week’s start date.
  • The calendar highlights Weekends
  • The calendar lists 5 Holidays and 10 Events for each day. 

Calendar Design 19 (Daily Calendar)

1 Page – Daily Calendar

2020 Calendar Design 19 – Daily Agenda Calendar
2020 Calendar Design 19 – Daily Agenda Calendar
  • A day of the calendar year is printed on a page. 
  • To generate a daily calendar for a particular year, type in the Date. 
  • The calendar highlights Weekends
  • The calendar lists 5 Holidays and 20 Events for each day.

Calendar Design 20 (Two Years Calendar)

1 Page – 2 Years Calendar

2020 Calendar Design 20 – Two years Calendar
2020 Calendar Design 20 – Two years Calendar
  • Two calendar years are printed in this design featuring each calendar year on a separate page. 
  • The calendars highlight Weekends, Holidays and Events.
  • The calendars don’t list Holidays or Events. 

TIPS TO CUSTOMIZE THE 2020 CALENDAR TEMPLATE

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

The calendar template designs are completely customizable and formatting is no exception. You can style the calendars as per your favourite colors and fonts. This won’t affect the functionality of the template calendar negatively. 

Steps: 

  • Select cells to change the formatting. In the image below, 2 months are selected. 
Select Cells to change formatting
Select Cells to change formatting
  • To use some border options you have to unprotect the sheet (this will be explained later in the article). 
  • Note: Even if you change the font color, the color of the dates for Weekends will remain in red, unchanged. However, the rest of the dates can be displayed in any color. 
  • Holidays and Events are designed with preset colors and even if you change the fill color, the days with Holidays and Events will not change. 

2. How to change Event and Holiday colors?

Using conditional formatting, you can change the event and holiday colors. 

  • To use this, first select the sheet you want to format
  • Then, choose Conditional Formatting from the Format menu.
Open Conditional Formatting From Menu
Open Conditional Formatting From Menu
  • A dialog box will show all the rules we have used in the worksheet. We can edit each of the rules separately. 
Conditional formatting rules for Weekends Holidays and Events
Conditional formatting rules for Weekends Holidays and Events
  • Click on one of the rules; it will show the conditions used for formatting. For instance, the below image shows the condition for Holidays.
  • Modify the text color, fill color and font formats as per your needs and click on “Done.” In the below image, you can notice that the Holidays have a yellow font with a green fill color. Note that you can choose your custom colors for the sheet as well. 
  • Now, you can see the changes applied on the entire calendar sheet. Save the file to preserve the changes.
Conditional formatting – Edit Format for a rule
Conditional formatting – Edit Format for a rule

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

Usually, Holidays and Events appear on the calendar in the way they have been listed on the table (in the Settings sheet). If you have entered the Holidays and Events in a random order on the table then you can sort the list to order them appropriately. You can sort them in ascending order (A – Z) or in descending order (Z-A). And click on “OK.”

Sort Holidays by Ascending or Descending Order in Holidays table
Sort Holidays by Ascending or Descending Order in Holidays table

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

In the template we have used the date format MMM-DD (example May-05) since months could sometimes be misinterpreted as days in other countries. However, it is easy to change the date format as per your needs. 

  • Select the cells that you want to change
  • Use the Format menu and click on the Number option. A sub-menu opens up. Choose “More Formats” and subsequently “More Date and Time Formats” from it. 
  • Here you can pick the format you like and click on Apply for the change to reflect on the sheet. 

Change date formats using Format Menu option
Change date formats using Format Menu option

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

5. How to unprotect (unlock) sheets?

The above-mentioned customizations can be made normally. However, for further customizations, you have to unprotect the sheets. 

INDZARA is the password for unprotecting the sheet.Once the customizations are done, protect the sheets again to avoid any accidental edits on the sheets.

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

Employee Recruitment Tracker

New Employee Checklist – Free Google Sheet Template

Posted on

This Google Sheet is developed as a solution to document the completion of activities in a checklist. The HR departments in companies can use a copy of this template to track or document the new employee on-boarding activities. Ideally, there is a list of onboarding activities each employee has to complete during the onboarding process. This template can be a solution to track such onboarding activities within your company.

This template is designed for Google sheets, but if you are looking for an Excel template, please visit the New Employee Checklist in Excel

The key features in this template are

  • Customize activities for your business (maximum 12 activities)
  • Printable checklist for each employee
  • Choose whether to print dates
  • Customize header with ordering fields as you prefer
  • Rename fields as needed (except the activity date fields)
  • Customize footer with your business information
  • % Complete progress bar
  • All employees’ data in one file

How to Create an Onboarding Employee Checklist 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
  4. Choose the employee and Print

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 copy of sheet
Make copy of sheet

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 Settings

In the Settings sheet, start by entering the Company name. If we don’t want to print activity dates, then choose No to that.

Finally, enter the list of activities.

Enter Settings
Enter Settings

STEP 3: Employee Data

In the Employees sheet, let us enter each employee and their information

Tip: If you would prefer to print empty checklists and check items in the hard copy, then skip entering the Employee Data and directly move to Print step below.

Enter Employee Data
These fields can be renamed as needed.

Then, we enter the activity completion dates for each employee, as and when they complete the activities.

Enter Activity Completion Dates
Please do not rename these date fields, as they will impact the formulas used.

STEP 4: Print

In the print sheet, we can print the certificate for each employee separately by choosing the employee name.

Choosing Employee

The fields in the header can be changed by using the dropdown. This allows us to reorder fields to suit our needs. Also, if you have renamed the employee fields earlier, they will automatically appear here.

There is no need to change the Activities section of the certificate.

Activity Status

At the end of the activities section, a Checklist Completed DT will appear if the employee has completed all the activities.

We can customize the footer area by providing contact information for your business and any internal links for reference to the new employees. Feel free to type in your details directly – these fields do not have any formulas.

Footer Details

Now that we have the certificate customized, we can print or download as PDF.  The following are a couple of sample certificates printed.

Sample Checklist

Another Sample Checklist

You may also be interested in...

Employee Vacation Planner – Free Google Sheet Template

Posted on

As a team leader or a manager of a team, it is important to know the staff coverage available every day, especially during the holiday season. This Google Sheet Template is developed to provide a simple and effective solution to help assess the coverage of team members’ availability during a particular month. 

This Google Sheet Template can be used every month to ensure that you are aware of the exact coverage and plan accordingly. 

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

 

Dashboard Summary for Employee Vacation Planner showing daily availability
Dashboard Summary for Employee Vacation Planner showing daily availability

How to Use the Employee Vacation Planner Google Sheets Template

Steps to use this template:

  1. Make a Copy and save this template in your Google Drive.
  2. Configure the input entries.
  3. Dashboard and the Calendar are populated automatically.

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

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.

This Google Sheet Template is one of the simplest templates to get started. Following are the inputs needed for this template to populate the dashboard and the calendar automatically.

Settings

 

  • Start Date – The template builds out the calendar for 31 days beginning from the Start Date
  • # of Employees needed daily – This data is used to identify understaffed days. 
  • Weekends – Select the weekend days of your company. These days will not be counted as working days. 
  • Holidays – Enter up to 7 holidays. These days will not be counted as working days. 
  • Vacation Dates of employees
    • Enter Employee Name and the planned vacation dates.
    • If an employee plans to take two periods of vacation (Jan 02 to Jan 03 and then on Jan 23), enter them as different entries in two rows.
    • If an employee does not plan to take any vacation, then enter just the employee name in a row and leave the date columns blank. It is important that all employees appear at least once in the table.
    • There is a limitation to add 1000 rows of employee vacation that can be captured.

Step 3: Dashboard populated automatically.

Once the inputs are provided, the template automatically calculates the metrics as a quick summary.

Dashboard Summary for Employee Vacation Planner showing daily availability
Dashboard Summary for Employee Vacation Planner showing daily availability
  • # of Employees
  • # of Working Days
  • # of UnderStaffed Days

In addition it also shows the number of employees at work for each of the 31 days. Holidays and Weekends will not display any numbers.

The Red bars indicate understaffed days. Understaffed days are days where the number of employees working is less than the number of employees needed.

Step 4: Calendar populated automatically.

This Google Sheet template also has a vacation calendar that visually shows the vacation days on a calendar view. Let’s take a closer look at the top of the calendar.

Each employee is listed on the left. The holidays are shown in orange, weekends in gray and employee vacations are shown in green. The calendar can display 31 days and 20 employees at a time. Here is the look of the entire calendar (vertically)

Each employee is listed on the left. The holidays are shown in orange, weekends in gray and employee vacations are shown in green. 

The calendar can display 31 days and 20 employees at a time.

Here is the look of the entire calendar (vertically)

Daily Summary

A daily summary metrics is provided at the bottom of the calendar. Number of Employees on Vacation and Number of Employees at work for each day are summarized.

Now that we have the calendar and dashboard automatically updated, we can print or download as PDF.  Following is a sample of the document.

Sample of the document

You may also be interested in...