Sales Quotation – Free Excel Template

Posted on

You can use this template to create quotations for your sales prospects, informing them about how much it will cost for the products and services you provide. There is no limit to the number of quotations and the best thing is you can save all your quotations within one excel file.

FEATURES

  1. Create quotations for your small business
  2. Customize it to your need
  3. Automatically calculate totals and subtotals
  4. Handles tax rates at the order level
  5. Accommodates products that are not taxable
  6. Handles discounts at product and order levels
  7. Save time by storing customer and product data

LIMITATION

Accommodates 20 lines per quote.

REQUIREMENTS

  • Microsoft Excel for Windows (2010 and above)
  • Microsoft Excel for Mac (2011 and above)

DOWNLOADS

Click the link below to download the

FREE TEMPLATE

FREE TEMPLATE WITH SAMPLE DATA

HOW TO USE THE TEMPLATE

INITIAL SETUP

  1. Enter Business Information in Settings Sheet
  2. Enter Products in Products Sheet
  3. Enter your Prospects/Customers in the Customers Sheet.
  4. Change the logo in the Quote Sheet
  5. Customize fields in the Quote sheet.All the fields with a light green shade are editable fields which are fields that need to be specified by the user.
  6. Enter Tax Rate % in Quote Sheet (cell I48)
  7. Apply Currency format in Quote Sheet.

Instructions to enter all the data for a Quote

  1. Enter Quote # in cell J15.
  2. Choose customer name in cell C17.
  3. Enter Quotation Date in cell J18
  4. Enter Validity Date in cell J20.
  5. Enter Product ID/Name, Quantity & Discount.
  6. Enter Tax Rate% in cell I50
  7. Enter Order Discount Amount/% in cell I52.
  8. Enter Other charges, if any, in cell J53
  9. Enter any order notes or comments in cell C51
  10. Press Ctrl+P to preview & Print/export to PDF.

CREATING QUOTATIONS

  1. Make a copy of Quote Sheet.
  2. Rename it with Quote #.
  3. Follow the instructions in Quote Sheet.

Support Articles

After reviewing and making sure everything looks good, it’s time to print or export.

Press Ctrl+P to preview print.

Print Preview and Print Invoice

The page is set up to be print-friendly. So, you should be able to just print as it is.

To export to PDF, select the Export menu option.

Export to PDF Menu Option

In the following window, choose a location in your computer to store the PDF file and give a name to it. Tip: use your receipt number in the file name. Save all your receipts with the Receipt Number in the PDF file name in the folder. Thus, you will be able to easily identify a specific PDF if needed in the future.

All the formatting options are available to you. Just select the cells you need to modify format and use Excel’s default formatting options (in the Home ribbon) to change fonts, colors, appearance, number format or alignment.

Excel Formatting Options to customize the invoice

If you change the font size or font, you can adjust the column widths and/or row heights to ensure that the print view is good.

Our Premium SMALL BUSINESS Templates

  • Employee Leave Manager Excel Template

    Sale! US$40
    Add to cart
  • Invoice Manager – Google Sheet Template

    US$35
    Add to cart
  • Invoice Manager

    Invoice Manager Excel Template For Small Business

    US$35
    Add to cart
  • Rental Inventory and Sales Manager – Google Sheet Template

    US$30
    Add to cart
  • Retail Business Manager – Google Sheet Template

    Sale! US$40
    Add to cart
  • Sales Pipeline Manager (CRM) – Google Sheet Template

    US$50
    Add to cart
  • Sales Pipeline Manager (CRM) thumbnail

    Sales Pipeline Manager (CRM) Excel Template

    US$50
    Add to cart
  • Small Business – Paid Time Off (PTO) Manager – Excel Template

    Sale! US$40
    Add to cart

Packing Slip – Free Excel Template

Posted on

If you run a business that has heavy communication between the seller and the shipment department or the warehouse where the goods are stored, download this Free Packing Slip Excel Template to make this communication much simpler. You can save time by storing all the products and customer details to create unlimited packing slips and manage them all in one file.

Sample Packing Slip
Sample Packing Slip

In general, a Packing slip carries all the details of the particulars in any shipment. It is an important tool to streamline the shipment process. Once prepared a packing slip is sent to the shipping department to help them construct the shipment with all the items mentioned in the packing slip.

FEATURES

  1. Create a packing slip for your small business
  2. Customize it to your need
  3. Reduce friction between seller and warehouse
  4. Save time by storing customer and product data

REQUIREMENTS

  • Microsoft Excel for Windows (2010 and above)
  • Microsoft Excel for Mac (2011 and above)

DOWNLOADS

Click the link below to download the

FREE TEMPLATE

FREE TEMPLATE WITH SAMPLE DATA

HOW TO USE THE TEMPLATE

INITIAL SETUP

  1. Enter Business Information in Settings sheet
  2. Enter Products in Products sheet
  3. Enter Customers in Customers sheet
  4. Change Logo in Packing Slip sheet
  5. Customize fields in the Packing slip sheet

CREATING PACKING SLIP

  1. Make a copy of Packing Slip sheet
  2. Rename it with Package #.
  3. Follow instructions in Packing Slip sheet
  4. To create another packing slip, make a copy of the original Packing slip sheet and rename it as PACKING #4.

Support Articles

After reviewing and making sure everything looks good, it’s time to print or export.

Press Ctrl+P to preview print.

Print Preview and Print Invoice

The page is set up to be print-friendly. So, you should be able to just print as it is.

To export to PDF, select the Export menu option.

Export to PDF Menu Option

In the following window, choose a location in your computer to store the PDF file and give a name to it. Tip: use your receipt number in the file name. Save all your receipts with the Receipt Number in the PDF file name in the folder. Thus, you will be able to easily identify a specific PDF if needed in the future.

All the formatting options are available to you. Just select the cells you need to modify format and use Excel’s default formatting options (in the Home ribbon) to change fonts, colors, appearance, number format or alignment.

Excel Formatting Options to customize the invoice

If you change the font size or font, you can adjust the column widths and/or row heights to ensure that the print view is good.

Our Premium SMALL BUSINESS Templates

  • Employee Leave Manager Excel Template

    Sale! US$40
    Add to cart
  • Invoice Manager – Google Sheet Template

    US$35
    Add to cart
  • Invoice Manager

    Invoice Manager Excel Template For Small Business

    US$35
    Add to cart
  • Rental Inventory and Sales Manager – Google Sheet Template

    US$30
    Add to cart
  • Retail Business Manager – Google Sheet Template

    Sale! US$40
    Add to cart
  • Sales Pipeline Manager (CRM) – Google Sheet Template

    US$50
    Add to cart
  • Sales Pipeline Manager (CRM) thumbnail

    Sales Pipeline Manager (CRM) Excel Template

    US$50
    Add to cart
  • Small Business – Paid Time Off (PTO) Manager – Excel Template

    Sale! US$40
    Add to cart

Property Rental Receipt – Free Excel Download

Posted on

If you run a small property rental business, download this Free Property Rental Receipt Template to create simple and effective receipts in Excel. You can save time by storing all your properties and agreement details, to create unlimited receipts and manage them all in one file.

FEATURES

  • Create printable payment receipts with minimal data entry.
  • Save time by storing property, agreement and receipt data
  • Choose One time setup for each agreement as monthly or daily or weekly
  • Payment amount will be auto calculated automatically for the provided payment period, according to the agreement.

LIMIT

  1. Rental amount to text conversion is limited to billions.
  2. Payment method is limited to 4.
  3. Rental period is limited to Monthly, Daily and Weekly.

REQUIREMENTS

  • Microsoft Excel for Windows (2010 and above)
  • Microsoft Excel for Mac (2011 and above)

DOWNLOADS

Click the link below to download the

FREE TEMPLATE

FREE TEMPLATE WITH SAMPLE DATA

HOW TO USE THE TEMPLATE

HOW TO CREATE RECEIPTS

INITIAL SETUP

Enter Business Information in Settings sheet

 

Enter Business Information to appear on Invoice

Setup Amount to Text conversion currency value: 

Enter currency value

Enter Available Payment Modes

Enter available payment modes

Enter Properties in Property sheet

If you are new to Excel Tables, please read the article on how to use Excel Tables for data entry.

 

Enter list of Properties in Property Table

You can add columns or rename existing columns as you need. You can add more properties anytime.

Enter Agreements in Agreements sheet

 

Enter Agreement in Agreement table with rental information and monthly or weekly or daily rental rate

You can add columns or rename existing columns as you need. You can add more agreements anytime.

CREATE AN RECEIPT WITH RENTAL RECEIPT BUILDER TEMPLATE

With our initial setup complete, it’s time to create our first receipt.

Overview of Steps

  1. Enter Unique Receipt number.
  2. Enter Receipt Date.
  3. Select Agreement number.
  4. Select Payment method.
  5. Enter payment reference number (Optional).
  6. Enter From and To date of rental period. Note: Amount will be auto calculated with this From and To date.
  7. Enter positive or negative Adjustment Amount (Optional).
  8. Agreement amount and final amount is auto calculated as agreement settings.
  9. Enter Receipt Number in Receipt sheet to select the receipt from Rental List.
  10. Press Ctrl+P to preview & Print/export to PDF.

Support Articles

After reviewing and making sure everything looks good, it’s time to print or export.

Press Ctrl+P to preview print.

Print Preview and Print Invoice

The page is set up to be print-friendly. So, you should be able to just print as it is.

To export to PDF, select the Export menu option.

Export to PDF Menu Option

In the following window, choose a location in your computer to store the PDF file and give a name to it. Tip: use your receipt number in the file name. Save all your receipts with the Receipt Number in the PDF file name in the folder. Thus, you will be able to easily identify a specific PDF if needed in the future.

All the formatting options are available to you. Just select the cells you need to modify format and use Excel’s default formatting options (in the Home ribbon) to change fonts, colors, appearance, number format or alignment.

Excel Formatting Options to customize the invoice

If you change the font size or font, you can adjust the column widths and/or row heights to ensure that the print view is good.

When you have a lot of properties to manage, it might become challenging to choose from the drop down menu in the Agreements worksheet, especially if the property IDs/Names are not sorted. By default, the order in which you entered the properties in the Properties table will be used as it is in the drop down menu. That means you could sort the properties in the Properties table and this would automatically give you sorted properties in the drop down menu. This should help you in easier selection of properties in the Agreements.
Choose the cells in the Properties table, Right Click –> Sort.

Sort Property by Name
Sort Property by Name

Our Premium SMALL BUSINESS Templates

  • Employee Leave Manager Excel Template

    Sale! US$40
    Add to cart
  • Invoice Manager – Google Sheet Template

    US$35
    Add to cart
  • Invoice Manager

    Invoice Manager Excel Template For Small Business

    US$35
    Add to cart
  • Rental Inventory and Sales Manager – Google Sheet Template

    US$30
    Add to cart
  • Retail Business Manager – Google Sheet Template

    Sale! US$40
    Add to cart
  • Sales Pipeline Manager (CRM) – Google Sheet Template

    US$50
    Add to cart
  • Sales Pipeline Manager (CRM) thumbnail

    Sales Pipeline Manager (CRM) Excel Template

    US$50
    Add to cart
  • Small Business – Paid Time Off (PTO) Manager – Excel Template

    Sale! US$40
    Add to cart
  • Small Business Digital Catalog – Excel Template

    US$35
    Add to cart
  • Small Business Finance Manager – Google Sheet Template

    Sale! US$40
    Add to cart
  • Small Business Finance Manager Excel Template

    Sale! US$40
    Add to cart
  • Timesheets Manager Excel Template

    Timesheets Manager Excel Template – Timesheets Simplified

    US$15
    Add to cart

Asset Rental Invoice and Receipt – Free Excel Download

Posted on

If you run an asset rental business, download this Free Asset Rental Invoice & Receipts Template to create simple and effective invoices and receipts. You can store all your assets and customer details to create unlimited invoices and receipts and manage them all in one file.

Features

  1. Create invoices for your asset rental business
  2. Customize invoice and receipt to your business need
  3. Automatically calculate totals and subtotals
  4. Handles tax rates at the order level
  5. Accommodates products that are not taxable
  6. Handles discounts at product and order levels
  7. Save time by storing customer and product data

Limitation

  • The template is designed to accommodate 20 line items per invoice or receipt.
  • Designed for daily (one or multiple days) rentals and not hourly rentals.

Requirements

  • Microsoft Excel for Windows (2010 and above)
  • Microsoft Excel for Mac (2011 and above)

Downloads


How to use the template

Initial Setup

  • Enter Business Information in Settings sheet
  • Enter Payment Methods in Settings sheet.

If you are new to Excel Tables, please read the article on how to use Excel Tables for data entry.

  • Enter Assets details in the Assets sheet

You can add columns or rename existing columns as per your business need. You can maintain all your assets in one place.

  • Enter Customer details in the Customers sheet

You can add columns or rename existing columns as per your business need. You can add and maintain all your customers on this sheet.


Generate Invoice

The Invoice sheet explained below in detail

1.Invoice:  Enter the Invoice number in cell G5.

2. Date:  Enter the date of the invoice in cell G7.

3. Place your Business logo:  Right-click on the Your logo Here image and click on Change Picture. If you don’t need a logo, just select the logo image and press the delete key.

4. Customer: This cell lists down all the customer names in a drop downfield. Enter the Customer’s name in cell C14. On selection of this field, the corresponding customer information below (billing address) gets populated. You can choose to have any other customer information field here – use the dropdown to select the information you want to populate.

5. Out Date:  Enter the Out Date in cell F14. This is the start date of the asset rental.

6. Return Date: Enter the Payment Due Date in cell F17. This is the return date of the asset rental.You can choose whether to include the return date to be charged:The number of days rented will be automatically calculated based on this selection.This will be multiplied by the total amount of each asset.

7. Payment Due Date: Enter the Payment Due Date in cell I14. This is the date by which the payment has to be made.

8. Tax @Rate: Enter the Tax Rate in cell I44. You can change this later for each invoice if needed.

9. Discount Amt: Enter the Discount Amount in cell I46. Your value can change for each invoice. Leave this field empty if you do not want to give any discount.

Now, how to enter the list of assets into the invoice – please refer to the screenshot given below for the section that can be editable and others that are auto-populated based on the fields edited.

You can choose to display either Amount Before Tax or Amount After Tax

Continue entering assets as many there are in the invoice. The maximum allowed is 20 in this template.


Get Receipt

To create a receipt, just choose Receipt (instead of invoice) on the top left.

 

Receipt for rental business
Receipt for rental business

Enter the Mode of payment using the drop down list. This cell I14 picks up the value from the data entered in the payment section of the settings sheet.

Enter the Paid Date as well.


Creating multiple Invoices/Receipts

2 options:

Option 1: Save each invoice as a separate Excel file. To create a new one, just do File –> Save As, and give a new name for the new file.

 

Option 2: Save multiple invoices in 1 Excel file. Follow the steps given below.

  1. Keep the Invoice sheet on which the initial setup was done as the master sheet.
  2. Take a copy of the master sheet.
  3. Rename the copy with the invoice number.
  4. Edit the input fields along with the asset details.
  5. Print the invoice if needed. Export to PDF.
  6. Save the excel file.

Repeat this process to create as many invoices/receipts as needed.

Record Keeping – Save your invoices and receipts

Save each invoice and receipt as PDFs (Export as PDFs). Give the Invoice Number as the file name. Eg. Invoice_123.pdf


Support Articles

After reviewing and making sure everything looks good, it’s time to print or export.

Press Ctrl+P to preview print.

Print Preview and Print Invoice

The page is set up to be print-friendly. So, you should be able to just print as it is.

To export to PDF, select the Export menu option.

Export to PDF Menu Option

In the following window, choose a location in your computer to store the PDF file and give a name to it. Tip: use your invoice number in the file name. Save all your invoices with the Invoice Number in the PDF file name in the folder. Thus, you will be able to easily identify a specific PDF if needed in the future.

We will be coming up with another free template to handle hourly rentals.

You can choose to use a Product ID or Product Name or your own field as a key identifier of products.

If you choose Name, the drop-down list automatically will use Product Name instead of Product ID. Everything works smoothly.

All the formatting options are available to you. Just select the cells you need to modify the format and use Excel’s default formatting options (in the Home ribbon) to change fonts, colors, appearance, number format, or alignment.

Excel Formatting Options to customize the invoice

If you change the font size or font, you can adjust the column widths and/or row heights to ensure that the print view is good.

When you have a lot of products, it might become challenging to choose from the drop-down menu in the Invoice worksheet, especially if the product IDs/Names are not sorted. By default, the order in which you entered the products in the Products table will be used as it is in the drop-down menu. That means you could sort the products in the Products table and this would automatically give you sorted products in the drop-down menu. This should help you in the easier selection of products in the invoice.

Choose the cells in the Products table, Right Click –> Sort.

The template comes with no currency formatting by default as I would like the template to be useful in all countries. It’s easy to apply your currency.

In the Invoice sheet, press Ctrl+G. This opens a new dialog box.

Changing Invoice Currency – Press Ctrl+G and select CURR

Choose CURR_INV or CURR_RCPT and click OK. This will select all the cells that need to be formatted as currency. Press Ctrl+1.

Invoice Manager Google Sheet Template – User Guide

Posted on

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

 

Step by Step Instructions

Enter Invoice data in Invoices sheet

Invoices Sheet
Invoices Sheet

The fields required are

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

The four status values possible are

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

Now, let’s enter our first invoice data.

Important: Start by typing in cell A4.

Entering First Invoice Data
Entering First Invoice Data

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

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

Entering Second invoice
Entering Second invoice

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

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

Pasting Data from Another Source

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

 

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

 

paste as values
paste as values

After entering my second invoice, it looks like this.

Second Invoice Data Entry
Second Invoice Data Entry

After third invoice, it will look like this.

Third Invoice Data Entry
Third Invoice Data Entry

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

Entering payments

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

Full Payments

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

Full payment example
Full payment example

Multiple payments (Partial payments)

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

Multiple Payments for one invoice
Multiple Payments for one invoice

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

Bulk Payments

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

Bulk Payment Example
Bulk Payment Example

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

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

Bulk Payment vs Invoice Payments

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

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

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

Invoice Overpayment

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

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

Overpaid Amount for Invoice
Overpaid Amount for Invoice

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

Invoice showing overpaid status
Invoice showing overpaid status

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

It will be listed as overpaid in the customer report.

Overpaid amount in Customer Report
Overpaid amount in Customer Report

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

Correcting overpayment

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

Updated Customer report
Updated Customer report

Overpayment of Bulk Payments

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

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

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

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

Customer Name validation

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

Invalid Customer Name Entry
Invalid Customer Name Entry

Refresh Calculations

The template automatically refreshes after entering invoice or payment data.

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

Payment Trend by Month
Payment Trend by Month

Dashboard

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

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

Invoices Dashboard
Invoices Dashboard

The dashboard shows the following visuals:

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

The dashboard can be filtered by Invoice Date slicer.

Unpaid Invoices Report

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

Unpaid Invoices Report – Aging and Invoices due today
Unpaid Invoices Report – Aging and Invoices due today
  • It gets updated automatically as soon as you enter/update data in Invoices and Payments sheets.
  • The Aging of invoices
    • For Current Invoices: Number of days from today to Due Date
      • 0 to 30 days, 31 to 60 days, 61 to 90 days and > 90 days
    • For Past Due Invoices: Number of days from Due Date to Today
      • 1 to 30 days, 31 to 60 days, 61 to 90 days and > 90 days

Customer Report

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

Customer Report example
Customer Report example

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

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

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

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

Sales Pipeline Manager Google Sheet Template – User Guide

Posted on

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

Overview of steps

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

Step 1: Review Stages in Sales Process

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

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

Sales Funnel Stages
Sales Funnel Stages

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

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

Enter Required Fields

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

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

Stage Dates

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

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

Hide stage columns not applicable
Hide stage columns not applicable

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

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

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

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

Enter Win %, Status and Close Date

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

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

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

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

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

Close Date

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

Deal Attributes

There are 5 deal attributes provided.

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

They can be renamed and Repurposed.

Rename Deal Attributes
Rename Deal Attributes

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

Notes

Notes
Notes

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

Lost Reason

Lost Reason for Deals Lost
Lost Reason for Deals Lost

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

Contact Attributes

Contact Attributes
Contact Attributes

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

Validations

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

Data Validations in Deals Data
Data Validations in Deals Data

The following are considered errors.

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

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

Step 3: Updating a Deal

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

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

Enter stage dates when deal reaches each stage.

Step 4: Closing a Deal

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

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

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

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

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

Missing Close Dates
Missing Close Dates

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

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

If deal is lost, enter Lost Reason.

Lost Reason for Deals Lost
Lost Reason for Deals Lost

Step 5: Monitor Sales Pipeline performance

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

Assessing Performance of closed deals

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

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

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

Sales Pipeline Funnel KPIs
Sales Pipeline Funnel KPIs

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

Conversion Rate: 30% of deals were Won.

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

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

Sales Funnel: Where do deals drop off?

Sales Funnel (Pipeline)
Sales Funnel (Pipeline)

 

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

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

Why are deals lost?

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

Dashboard with Deal Lost Reason
Dashboard with Deal Lost Reason

Analysis

The Dashboard also allows analysis of performance by attribute.

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

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

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

Active Pipeline

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

Active Pipeline Dashboard
Active Pipeline Dashboard

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

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

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

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

Step 6: Track Sales Activities

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

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

How you can use this on daily basis.

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

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

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

Manufacturing Inventory Tracker – Free Google Sheet Template

Posted on

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

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

How to Create an Manufacturing Inventory Tracker in Google Sheets

Steps to use this template:

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

Step 1: Make a copy of the sheet

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

Make a copy
Make a copy

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

Step 2: Enter the Input Data

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

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

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

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

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

Enter Bill of Materials (BOM) in BOM sheet:

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

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

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

Entering purchase and sale orders in Orders sheet.

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

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

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

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

Step 3: Enter the Input Data

Check Availability

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

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

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

 Limit

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

You may also be interested in...

Some of our related products...

  • Manufacturing – Inventory and Sales Manager – Excel Template

    US$30
    Add to cart

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

Some of our related products...

  • Invoice Manager – Google Sheet Template

    US$35
    Add to cart
  • Invoice Manager

    Invoice Manager Excel Template For Small Business

    US$35
    Add to cart

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

Some of our related products...

  • Sales Pipeline Manager (CRM) – Google Sheet Template

    US$50
    Add to cart
  • Sales Pipeline Manager (CRM) thumbnail

    Sales Pipeline Manager (CRM) Excel Template

    US$50
    Add to cart

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

Some of our related products...

  • Rental Inventory and Sales Manager – Google Sheet Template

    US$30
    Add to cart
  • Rental Inventory and Sales Manager Excel Template

    US$30
    Add to cart