Retail Inventory Tracker 2020 – Free Inventory (Stock) Management template

Posted on
Retail Inventory Tracker
I am glad to present a simple and effective way to manage orders and inventory for your retail business. If you are getting started with a retail business where you plan to buy products from your suppliers and then sell them to customers with a margin, then you would need a tool to track your business in an effective way.

This Excel template is designed for Microsoft Excel, but if you are looking for a Google Sheet template, please visit Retail Inventory Tracker in Google Sheets.

Continue reading Retail Inventory Tracker 2020 – Free Inventory (Stock) Management template

Sales Pipeline Tracker Template – Track sales leads in Excel

Posted on
Sales Pipeline Tracker - Active Sales Pipeline - Metrics
Whether you are a small business owner looking for a tool to manage the data on your sales leads, or a sales person needing a simple CRM software, you have come to the right place. I am glad to present a simple Sales Pipeline Management tool to track and manage your sales leads.

This Excel template is designed for Microsoft Excel, but if you are looking for a Google Sheet template, please visit Sales Pipeline Tracker in Google Sheets.

Continue reading Sales Pipeline Tracker Template – Track sales leads in Excel

Free Excel Inventory Template for Manufacturing Businesses

Posted on
Manufacturing Inventory Tracker - Free Inventory Excel Template

This free inventory tracker template is designed for small businesses which manufacture or assemble products from raw materials. You can use this 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. We can input simple Bill of Materials (BOM) where we define how we can manufacture a products from its raw materials.

Manufacturing Inventory Tracker - Free Inventory Excel Template
Manufacturing Inventory Tracker – Free Inventory Excel Template

 

FREE DOWNLOAD

Manufacturing Inventory Tracker – Excel Template

 

VIDEO TOUR

 

HOW TO USE THE INVENTORY TRACKER?

There are five sheets in the Excel template: 1) Home 2) Products 3) Raw Materials 4) BOM and 5) Orders

 

Overview of steps

  1. Enter Products in Products sheet
  2. Enter Raw Materials in Raw Materials sheet
  3. Enter Bill of Materials in BOM shet
  4. Enter orders in Orders sheet after checking availability
  5. View Current raw material stock in Raw Materials sheet

 

Steps in Detail

I will use an example of a Juice/Smoothie company which creates juices from fruits & vegetables. However, this template can be used in any industry.

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.

Excel Inventory Template - Enter Raw material Purchase and Product Sale Orders
Excel 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.

 

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.

 

Related Inventory Management Templates

You may also be interested in...

Inventory & Sales Manager Excel Template
Retail Inventory Tracker
Rental Inventory Tracker

Free Excel Inventory Template for Rental Businesses

Posted on
Rental Inventory Tracker

If you are a business owner of a rental business where you rent equipment or assets to your customers, you will find this template handy in tracking inventory. This Excel rental inventory management template can help you know how many items are in stock and how many have been rented out. In addition to knowing the current inventory, you can also determine the expected inventory in future based on future dated rental orders/contracts.

Use this free equipment rental software to get organized and be in control of your rental inventory. You can use this template for any rental business, such as equipment rental, furniture rental to parties or events, renting wedding items to weddings, construction equipment rental, audio video equipment rental, bike rental, sports equipment rental or book rental.

This Excel template is designed for Microsoft Excel, but if you are looking for a Google Sheet template, please visit Rental Inventory Tracker in Google Sheets.

 

Rental Calendar - Available Quantity
Rental Calendar – Available Quantity

 

FREE DOWNLOAD

Rental Inventory Tracker Excel Template

 

VIDEO DEMO

HOW TO USE THE RENTAL INVENTORY TRACKER

Overview of Steps

  1. Enter Assets and # of items in Assets sheet
  2. To enter a rental contract order, enter the order details in Orders sheet
  3. Check availability in Orders sheet before committing to the order (max 90 days window)
  4. To see current availability of assets, view Assets sheet
  5. To view at any time availability over a month, use Calendar sheet

 

Detailed Steps

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

 

If you are new to Excel Tables, please read this article first: Introduction to Excel Tables.

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

 

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.

 

ADDITIONAL FUNCTIONALITY

To view one order’s details, use filter on Order Number in Assets table

Filtering by Order Number
Filtering by Order Number

To view orders/assets with return date of today, use filter on Return Date in Assets table

Filtering by Rental Due Date
Filtering by Rental Due Date

 

By default, the Calendar sheet shows 25 assets. It’s easy to extend Calendar for more days and more assets. To unprotect Calendar sheet, use password: indzara. (Article on Unprotecting sheets). Then, just expand the table to more rows to include more assets.


RECOMMENDED TEMPLATES

If you find the above template useful, please see Rental Inventory and Sales Manager, that provides accounting, invoicing, and reporting features for rental businesses.

You may also be interested in...

Inventory & Sales Manager Excel Template
Manufacturing Inventory Tracker - Free Inventory Excel Template
Retail Inventory Tracker

Invoice Tracker Template for Small Businesses – Free Download

Posted on
Invoice Tracker Template

For small businesses, keeping track of the sales invoices and payments made by customers is a critical task. Use this Invoice Tracker template to keep track of all your sales invoices in one place. You can enter payments made by customers and let the template calculate balance outstanding amounts. Never miss an unpaid invoice. Download this invoice tracker spreadsheet free using the link below.

FEATURES

  • 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

REQUIREMENTS

Microsoft Excel 2013 (or later) for Windows

FREE DOWNLOAD

Invoice_Tracker

VIDEO DEMO

HOW TO USE THE TEMPLATE

DATA ENTRY

Invoice Data Entered in Invoice Tracker Excel Template
Invoice Data Entered in Invoice Tracker Excel Template
  • Enter each invoice in the Invoice table beginning from row 15. (If you are new to Excel Tables, please read this article on data entry)
  • 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 border 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.

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


Related Templates


Additional Tips

How to set up a different currency?

Press Ctrl+G and choose CURRENCY_CELLS.

Go To Currency Cells
Go To Currency Cells

Press Ctrl+1 and select your preferred currency symbol.

Choose Currency Symbol as preferred
Choose Currency Symbol as preferred

How to add a new column?

Adding columns is very easy. Just type any field name in cell K14. Then, type values for each invoice in column K from K15 down. You can add any number of columns.

How to sorting invoices by Due Dates?

It’s easy to use the default sorting capabilities within Excel. For example, right click on the due dates column and choose Sort option.

Sort Invoices by Due Dates
Sort Invoices by Due Dates

You can use sorting on any of the fields.

How to track partial payments from customers?

Sometimes your customer may make an invoice payment in installments.  For example, a customer pays $300 invoice in 2 payments $100 and $200. Or a customer makes a payment for two invoices together. Please watch this video tutorial below that explains how to address these scenarios.

How to calculate Due Dates automatically? 

By default the template allows manual entry of due dates. If, in your small business, there is a standard term used such as Net 30 or Net 45, then we can automate this calculation. To create Due Date always as (Invoice Date + 30), just type =[@[INVOICE DATE]]+30 in cell D15.

RECOMMENDED TEMPLATE

You may also be interested in...

Sales Pipeline Tracker - Active Sales Pipeline - Metrics
Sales Invoice Template
Inventory & Sales Manager Excel Template

2020 Excel Invoice Template – Create Invoices for Small Businesses

Posted on
Sales Invoice Template

If you run a small business selling products, download this Free Sales Invoice Template and create invoices easily in Excel. You can create unlimited invoices and manage them all in one file. You can save time by storing your products and customers once and re-using them for invoices whenever needed.

Sales Invoice Excel Template
Sales Invoice Excel Template

 

FEATURES

  • Create professional sales invoices for your small business instantly
  • Customize it to your need
    • Customize customer data that appears on the invoice
    • Change columns (2 columns are flexible) in invoice
    • Discounts can be set as amounts or %
    • Logo and other formats like colors or fonts can be changed as preferred
    • Currency can be set easily to suit your business need
  • Automatically calculate totals and subtotals
  • Handles tax rates at order level
  • Accommodates products that are not taxable
  • Handles discounts at product and order levels
  • Save time by storing customer and product data

 

LIMIT

The template is designed to accommodate 20 line items per invoice.

 

REQUIREMENTS

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

Download Sales Invoice Excel Template 2020

VIDEO DEMO


HOW TO CREATE INVOICES

INITIAL SETUP

Enter Business Information in Settings sheet

Enter Business Information to appear on Invoice
Enter Business Information to appear on Invoice

 

Enter Products in Products sheet

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

Enter list of Products in Products Table
Enter list of Products in Products Table

 

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

 

Enter Customers in Customers sheet

Invoice Builder - Enter Customers in Customer table with contact information
Invoice Builder – Enter Customers in Customer table with contact information

 

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

 

Change Logo in Invoice sheet

Right Click on the logo image and choose ‘Change Picture. Select an image from your computer to replace.

Change Logo image on Invoice
Change Logo image on Invoice

 

If you don’t need a logo, just select the logo image and press the Delete key.

 

Customize Customer Information on Invoice

It’s easy to customize the appearance by selecting which field should appear.

Customer Information – Rearrange fields as needed
Customer Information – Rearrange fields as needed

 

7 fields can be customized. The list of information available in the drop down is based on columns in the Customers sheet.

Invoice Template - Customize Customer Info Fields
Invoice Template – Customize Customer Info Fields

 

Enter Tax Rate % in Invoice sheet

Enter Tax Rate in cell I50. You can change this later for each invoice if needed.

 

Apply Currency format in Invoice sheet

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
Changing Invoice Currency – Press Ctrl+G and select CURR

 

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

Choose preferred Currency Symbol to use in Invoice
Choose preferred Currency Symbol to use In Invoice

 

After selecting and pressing OK, the invoice will have currency format applied.

Currency Format Applied to Invoice fields
Currency Format Applied to Invoice fields

 


CREATE AN INVOICE WITH INVOICE BUILDER TEMPLATE

With our initial setup complete, it’s time to create our first invoice. First, let’s make a copy of the Invoice sheet.

Make a Copy of Invoice Sheet
Make a Copy of Invoice Sheet

 

Rename the new sheet with the Invoice #. For example, for Invoice #1234, Let’s name the sheet 1234.

Rename the sheet with Invoice Number
Rename the sheet with Invoice Number

 

Now, we will create the invoice #1234.

 

Overview of Steps

  1. Enter Invoice # in cell J15
  2. Choose customer name in cell D17
  3. Enter Invoice Date in cell J18
  4. Enter Payment Due 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 comments or notes in cell C51
  10. Press Ctrl+P to preview & Print/export to PDF.

 

Steps in Detail with Screenshots

1. Enter Invoice # in cell J15.

Sales Invoice – Entering Invoice Number
Sales Invoice – Entering Invoice Number

 

2. Choose customer name in cell D17

Choose Customer for the Invoice
Choose Customer for the Invoice

 

Other customer information will automatically populate.

Customer Information – Rearrange fields as needed
Customer Information – Rearrange fields as needed

 

3. Enter Invoice Date in cell J18

4. Payment Due Date in cell J20

Enter Invoice Date and Payment Due Date
Enter Invoice Date and Payment Due Date

 

5. Enter Product ID/Name, Quantity & Discount

Enter list of Products in Invoice
Enter list of Products in Invoice

 

Choose Product ID from the list.

Selecting Product ID for Invoice
Selecting Product ID for Invoice

 

Description and Unit columns will automatically populate. If you would prefer some other attribute (or information) of products instead, you can just change it. How cool is that?

Choose any product information to display in second and third columns
Choose any product information to display in second and third columns

 

Enter Quantity and unit discount. The Price, Taxable and Amount will automatically populate.

Choose either Amount Before Tax or After Tax
Choose either Amount Before Tax or After Tax

 

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

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

 

6. Enter Tax Rate% in cell I50

As we scroll down, we see the subtotal and total section. Here, we have two options.  Enter Tax Rate% in cell I50.

Enter tax rate in Invoice
Enter tax rate in Invoice

 

7. Enter Discount 

Next is Discount, where we can choose to use Discount Amount or Discount %.

 

Enter Invoice Discount
Enter Invoice Discount

 

If you choose Discount %, enter the value in decimals. For example, 0.01 for 10%. If you do not want to display any discount, just clear the cell.

 

8. Enter Other Charges

Enter Other Charges, if any, in cell J53. You can rename Other Charges to anything that applies. For example: ‘Shipping Charges’.

 

9. Enter Comments

Enter any comments or notes in cell C51.

Enter Comments or Notes in Invoice for customer
Enter Comments or Notes in Invoice for customer

 

10. Printing or Exporting to PDF

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

 

RELATED TEMPLATES

Track invoices and due amounts in a simple free Invoice Tracker template

To manage inventory along with creating invoices, please visit Retail Business Manager


Extensions

What if you sell hourly services?

You can also use this template for services that you may charge hourly. In such cases, you will enter the number of hours in the Quantity column in the invoice. In the Products table, you will enter the Service name and provide the hourly rate as the Price.

 

What if it is consulting projects and not hourly?

You can also use this template if you work on consulting projects. You can enter your Project ID in the ID field, Project Name in Name field and enter Project description in the Description field. You can enter the project’s cost in the Price field.  You can enter Quantity as 1.

 

What if you want to use Product Name instead of Product ID as unique identifier?

You can choose to user Product ID or Product Name or your own field as key identifier of products. Just select the field in cell C28 in the Invoice sheet. Now, you can enter.

Choose unique Product identifier from ID/NAME
Choose unique Product identifier from ID/NAME

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

 

How can I change the colors, fonts or other formatting?

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

 

How to sort data in Product list?
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 easier selection of products in the invoice.
Choose the cells in the Products table, Right Click –> Sort.

Sort Products by ID or Name
Sort Products by ID or Name

 

For more small business templates, please visit Small Business Excel Templates

You may also be interested in...

Invoice Tracker Template
Inventory & Sales Manager Excel Template
Small Business Product Catalog

Small Business Product Catalog in Excel – Free Template

Posted on
Small Business Product Catalog

Have you wondered if it would be handy to have a product catalog for your small business? Did you know that we can create the entire online product catalog or online store experience within Excel? Without payment processing, of course 🙂  You could use this to share your product info with your prospects and customers in an interactive tool.

This is how the product catalog/store appears in Excel.

Product Catalogue - Screenshot in Excel
Product Catalogue – Screenshot in Excel

For more features try our premium template Small Business Digital Catalog

If this sounds interesting, please read further on the functionality and for how to use this (hint: it’s so easy. Just enter your data and you are done). You can download this Excel template for free using the link below.


FREE DOWNLOAD

Small Business Product Catalog Excel Template


 

PRODUCT CATALOG – FEATURES

  • Filter products by Categories
  • Search products by keywords (on Product Name)
  • Sort products by Price (Ascending or Descending)
  • Scroll to see products in sets of 8
  • Add thumbnail images to appear on Catalog
  • Add links to website as needed on product detail sheets
  • Catalog links to individual product detail sheets
  • Format the catalog as needed with usual Excel formatting options
  • Customize product detail pages as needed
Small Business Product Catalog in Excel - Features (Sort, Filter, Search, Scroll)
Small Business Product Catalog in Excel – Features (Sort, Filter, Search, Scroll)

VIDEO TOUR

 


HOW TO CUSTOMIZE FOR YOUR BUSINESS

Overview of Steps

  1. Unhide hidden Input_Data sheet. Enter product categories and product data for your business.
  2. In Product_Catalog sheet, change logo and tagline text to suit your business.
  3. In each product’s individual product detail sheet, rename the sheet to match the product name
    • Update product image and highlights for each product
    • Change formatting – colors, fonts, etc. as needed

 

Detailed Steps

Entering product data

Unhide Input_Data sheet.

Unhide Sheet
Unhide Sheet

 

Select Input_Data Sheet.

Select Input Data Sheet
Select Input Data Sheet

 

Now, the sheet should be open and visible.

Enter Product Categories you need. It is set up for up to 6. If you need only 5, just select the 6th category (cell A8) and hit the Delete key to clear value.

Enter Product Categories for your small business
Enter Product Categories for your small business

 

Enter Product Data in the Products table.

Enter Product Data for your Small Business
Enter Product Data for your Small Business

 

Click on a product thumbnail image and choose Change Picture. Select picture from your computer.

Change Product Thumbnail Image
Change Product Thumbnail Image

 

Enter product name, price and category.

This ends our product data entry process for one product. Repeat for all products. After you enter all your products, you can hide this sheet. Now, we are ready to customize the catalog/store appearance.

 

Customize Catalog Appearance

First, in the Product_Catalog sheet, change logo by changing picture as explained above. Change the tagline text to suit your business.

Change Logo and Tagline in the header of Product Catalog
Change Logo and Tagline in the header of Product Catalog

 

If you want to change the colors, fonts or any formatting, please select cells and change formatting as usual in Excel.

Change formatting in Catalog
Change formatting in Catalog

 

Customizing Product Detail Sheets

You will notice that in the catalog, there are links to product detail sheets. Each product has its own sheet where we have its image and description.

Product Detail Sheet in Catalog - Customize
Product Detail Sheet in Catalog – Customize

 

  1. Rename the sheet to match your product name. If your product name is Cool Product, please rename the sheet to Cool Product.
  2. Replace image and description with your products’ info.
  3. I have added two links, one to the product page on the website and the other to go back to Catalog. Replace the web link with your product web page link. You can remove the web link if you don’t need or have.
  4. (Optional) You can change formatting (colors, fonts) as needed. There are no formulas in the product detail sheet.

Repeat this step for each of your products.

Tip: to insert a bullet as I did in the product highlights, use Alt+0149.

 

Once you have completed all your product detail sheets, you are done. You can go back to the Product_Catalog sheet and your catalog is ready. Start. Sort, Search, Filter, Scroll. 🙂

 

Print/Export

You can export to PDF or print as all sheets are set up as print friendly.

 

Limits

  • Designed by default for 30 products & 6 product categories. Can be extended if needed.
  • Names of Products should match the Product Detail Sheet Names. This allows hyperlinks to function properly.

For more small business templates, please visit Small Business Management Excel Templates

 

As always, I look forward to your feedback to understand how this can be extended to suit more small business needs. If you have any feedback or suggestions, please leave them in the comments. Thanks.

You may also be interested in...

Sales Invoice Template

Inventory and Sales Manager 2020 (Free Excel Template) for Small Business

Posted on
Inventory & Sales Manager Excel Template

This Inventory and Sales Manager Excel template is suited for managing inventory and sales if you are running a small business of buying products from suppliers and selling to customers. (Retail/Wholesale).

This retail inventory excel template will assist in knowing the inventory levels of each product and understanding which products to re-order. Also, you can quickly view the purchases/sales patterns over time and the best performing products.

This Excel template is designed for Microsoft Excel, but if you are looking for a Google Sheet template, please visit Inventory and Sales Manager in Google Sheets.


For Retail businesses, a new version is available Retail inventory Tracker (Free)

For Manufacturing businesses, Manufacturing Inventory Tracker Excel Template (Free)

For Rental businesses, Rental Inventory Tracker Excel Template (Free)


Inventory Spreadsheet - Summary Metrics
Inventory Spreadsheet – Summary Metrics

Download Retail Inventory 2020 Template (Windows)

Download Retail Inventory 2020 Template with Sample Data (Windows)

Download Retail Inventory 2020 Template (Mac)

Requirements:

Windows and Excel 2010 (or above version)

Mac and Excel 2011 (or above version)

FEATURES

  • 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

 

VIDEO DEMO

Click here to watch the video on YouTube.


HOW TO USE THE TEMPLATE

Enter Products

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

Excel Inventory Management - Products Table
Excel 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 Template – Enter Order Details
Inventory 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.

 

View information about overall inventory availability

Inventory Spreadsheet Excel Template - Summary Metrics
Inventory Spreadsheet Excel 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.

Since there are pivot tables and charts, please refresh the data by pressing Ctrl+Alt+F5 or going to DATA ribbon and selecting Refresh All. This updates the charts with your new transactions.

Excel Spreadsheet - Data Refresh
Excel Spreadsheet – Data Refresh

 

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

Inventory and Sales Manager - Excel Template - Report Filters/Slicers
Inventory and Sales Manager – Excel Template – Report Filters/Slicers

 

Amount and Cumulative Amount by Month

Inventory and Sales Manager - Excel Template - Report - Amount and Cumulative Amount
Inventory and Sales Manager – Excel Template – Report – Amount and Cumulative Amount

 

Quantity and Cumulative Quantity by Month

Inventory and Sales Manager - Excel Template - Report - Quantity and Cumulative Quantity
Inventory and Sales Manager – Excel Template – Report – Quantity and Cumulative Quantity

 

Amount distributed across Product Categories by Month

Inventory and Sales Manager - Excel Template - Report - Amount by Product Category
Inventory and Sales Manager – Excel Template – Report – Amount by Product Category

 

Quantity distributed across Product Categories by Month

Inventory and Sales Manager - Excel Template - Report - Quantity by Product Category
Inventory and Sales Manager – Excel 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 - Excel Template - Report - Product Ranking
Inventory Sheet – Excel Template – Report – Product Ranking

 

If you find the template useful, please share with others. If you have any feedback, please share it in the comments below.

 

You May also like the following Excel Templates:

 

RECOMMENDED RETAIL INVENTORY TEMPLATES

Retail Business Manager - Excel Template
Retail Business Manager – Excel Template

 

Manufacturing Inventory Tracker - Free Inventory Excel Template
Rental Inventory Tracker
Retail Inventory Tracker