Retail Inventory Tracker – Free Inventory (Stock) Management template

Posted on
Retail Business Process - Retail Inventory Tracker Template
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.

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

Sales Pipeline Tracker Template – Track sales leads in Excel – Free Download

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.

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

Reporting on Rental Business Performance – Monthly Metrics, Top Products & Top Customers

Posted on

Reporting in Rental Business Manager Excel template is fully automated and very extensive. We have reporting to help us get an accurate view of business performance in multiple areas (inventory, sales & finance) from multiple dimensions (product and Customer). Reporting is available on inventory levels, accounts receivable, monthly metrics, top and bottom performing  product and top performing customers.

 

This article is part of a series of support articles of Rental Business Manager Excel template. For more information on the product, please visit Rental Business Manager Excel template.

 

Current Status

First, we can see the current status of inventory and finance.

Report - Current Status of Inventory and Finance (money)
Report – Current Status of Inventory and Finance (money)

 

We can see the following metrics

  • Total inventory (quantity) on hand
  • Quantity rented Out
  • Accounts Receivable – Current: Amount due from Customers on Orders with Due Date >= Today
  • Accounts Receivable – Past Due: Amount due from Customers on Orders with Due Date < Today

 

Summary Metrics

Now, we move to looking at the past performance of business. First, we select our report Start and End dates.

Business Summary Metrics in Report Sheet for any date range
Business Summary Metrics in Report Sheet for any date range

 

  • Rental Qty: Total quantity rented on Booked orders.
  • Rental Revenue: Total Order amount on the Booked orders. Includes product level discounts. Does not include tax, order level charges and order level discounts.
  • Tax: Total tax amounts on Booked Orders
  • Discounts Given: Total amount of discount given to customers on Booked Orders
  • Other Charges: Total of other charges on all Booked orders
  • Expenses: Total of Expenses

 

Monthly Metrics

We can view all the metrics by month for up to 12 months.

Monthly Metrics in table format - Rental Quantity, Revenue, Charges, Discounts and Expenses
Monthly Metrics in table format – Rental Quantity, Revenue, Charges, Discounts and Expenses

 

We can choose one of the metrics to update the chart that shows up to 12 months of trend.

Monthly Metrics in Chart format - Showing monthly trends
Monthly Metrics in Chart format – Showing monthly trends

 

Products – Top Performers

One of the important pieces of understanding business performance is knowing which products are rented the most and which ones are not. We have 2 ways of measuring this – Rental Revenue, and Rental Duration. This allows us to understand the true impact of the products to the business.
First, we will see the top 10 Asset categories and bottom 10 Asset categories.

Top and Bottom Performing Asset Categories
Top and Bottom Performing Asset Categories

 

We can also see the top and bottom performing assets.

Top and Bottom Performing Assets
Top and Bottom Performing Assets

 

Product Performance

In addition to that, we would want to look at a specific product and understand its performance. We can choose a product and see its performance summary during the reporting window.
We can see the current inventory status of the product.

Product Performance - View current inventory levels as well as monthly metrics
Product Performance – View current inventory levels as well as monthly metrics

 

We can also see the sales quantity trend by months of that product.

Product Sales Performance - Rental Revenue Trend by Month
Product Sales Performance – Rental Revenue Trend by Month

 

CUSTOMER PERFORMANCE

Another important aspect to understand is best customers.

Top Customers in terms of Rental Revenue
Top Customers in terms of Rental Revenue

 

We can choose one specific partner and see their details as well.

Metrics for specific customer - Inventory, Sales, Accounting
Metrics for specific customer – Inventory, Sales, Accounting

 

Finally, the Assets sheet, the Customers sheet and Order Headers sheet also have useful information that we can filter on to get to the data we need.

 

In the next article, we will discuss how we can add or retire assets for rental

Accounting and Finance Management in Excel for Rental Businesses

Posted on

Rental Business Manager template enables simple and effective finance management for rental businesses. We have options to enter taxes and discounts. We can track payments made by customers on orders. In addition, we can also record operational business expenses. In this article, we will see these features in more detail.

 

This article is part of a series of support articles of Rental Business Manager Excel template. For more information on the product, please visit Rental Business Manager Excel template.

 

Order Related Costs and Discounts

When we enter the order line items in the Order Details sheet, we are entering the rental prices of products. We are also entering any product level discounts.

Entering order details such as Asset, quantity, price and discount
Entering order details such as Asset, quantity, price and discount

 

In addition to this, any order level charges can be entered in the Order Headers sheet. This will be added to the Total Order Amount. Also, any Order Level Discounts can be entered there. This amount will be deducted to calculate the Total Order Amount.

Entering Order Level Charges such as shipping charges and Order Level Discounts
Entering Order Level Charges such as shipping charges and Order Level Discounts

 

The template will use these to calculate the total order amount and subtotals in the Invoice and Report sheets.

 

Tracking Payments and Due Amounts

When the customer makes the payment, we enter the paid amount in the Order Headers sheet.

Entering Amount Paid by Customer in Order Header Sheet
Entering Amount Paid by Customer in Order Header Sheet

 

Due Amount will be calculated for each order as (Total Order Amount – Paid Amount).

Calculating Amount due on each order. Overpaid orders and Zero Due.
Calculating Amount due on each order. Overpaid orders and Zero Due.

 

You can filter on this DUE AMOUNT field to limit the data to only orders which are not fully paid yet (Filter to values other than 0).

Filter Orders to see all unpaid invoices
Filter Orders to see all unpaid invoices

 

Similarly, you can filter on PAYMENT DUE DATE field to see orders which are past due date. First, choose Date Filters —> Before.

Accounts receivable - Filter to Orders past due date
Accounts receivable – Filter to Orders past due date

 

Then, Choose Today.

Choose Today in the dialog box
Choose Today in the dialog box

 

Click OK and now the table will be filtered to orders which are past due dates.

 

Reporting

We can see the overall current status of accounts receivable in the Report sheet.

Report - Current Status of Inventory and Finance (money)
Report – Current Status of Inventory and Finance (money)

 

When we choose a specific customer, we can see the financial status along with other metrics.

Metrics for specific customer - Inventory, Sales, Accounting
Metrics for specific customer – Inventory, Sales, Accounting

 

Tracking Operational Expenses

All the operational expenses can be entered in the Expenses sheet. For example, if you buy any stationery for your business or subscribe to any other business service, you can enter those expenses here.

Enter Business Operational Expenses in the template
Enter Business Operational Expenses in the template

 

These expenses will be summarized and reported in the REPORT sheet.

Monthly Metrics in table format - Rental Quantity, Revenue, Charges, Discounts and Expenses
Monthly Metrics in table format – Rental Quantity, Revenue, Charges, Discounts and Expenses

 

 

In the next article, we will see in detail the automated 5 page interactive report in Rental Business Manager.

How to create and print invoice for rental orders in Excel?

Posted on

Creating invoices for rental orders using the Rental Business Manager is very easy.  In this article, we will see how we can create them on-demand and then print or export to PDF.

 

This article is part of a series of support articles of Rental Business Manager Excel template. For more information on the product, please visit Rental Business Manager Excel template.

 

We had discussed in the earlier article on how we can customize the invoice.  I am assuming that we have already done that, for the purposes of this article.

 

Creating and Viewing the Invoice

To view the invoice, let’s go to Invoice sheet and enter the order number at the top.

Invoice - Rental Business - Enter Order Number
Invoice – Rental Business – Enter Order Number

 

If we enter an invalid order number, we will see this warning message.

Invoice - Rental Business - Invalid Order - Order Not Found Error message
Invoice – Rental Business – Invalid Order – Order Not Found Error message

 

Let’s enter 1 as Order Number. The invoice will now auto-populate. It’s that simple. You can generate an invoice whenever needed. The data comes primarily from Order Details sheet and some information from Customers, Orders and Assets sheets.

Sample Invoice for Rental Businesses
Sample Invoice for Rental Businesses

 

Printing the Invoice

Press Ctrl+P to open the print preview dialog box. Preview the sheet and then click print.

Print Preview and Print
Print Preview and Print Invoice

 

By default, one page is printed that can handle up to 25 lines of the order. If the order has more than 25 lines, please change the number of pages in the Print Preview dialog box so that 2 pages can be printed. The template can handle up to 65 line items.

 

Exporting the Invoice as PDF

To export as PDF, Click File – Export as PDF.

Export to PDF Menu Option
Export to PDF Menu Option

 

Choose a place to store the file, name it as desired (recommend adding the order number and/or invoice number) and click Publish.

Export to PDF - Save File
Export to PDF – Save File

 

Then, we can email the file to our customer.

 

In the next article, we will learn more about finance management and accounting in Rental Business Manager. 

How to use Rental Availability Calendar for your rental business?

Posted on

Let’s learn how to use rental availability calendar to track available assets in our rental business.

 

This article is part of a series of support articles of Rental Business Manager Excel template. For more information on the product, please visit Rental Business Manager Excel template.

 

As we keep entering multiple orders (how to enter orders), things can get complex to know the exact availability of each asset on any given window. This is where the built-in calendar will help us. To illustrate, we have entered date for 15 orders in the Order Headers and Order Details sheet.

 

Rental Orders - Sample - Order Headers
Rental Orders – Sample – Order Headers

 

We have also entered specific assets being rented as part of these orders.

Rental Orders - Sample - Order Details, Assets, Quantity, Price.
Rental Orders – Sample – Order Details, Assets, Quantity, Price.

 

The calendar appears as below.

Rental Availability Calendar - Easy to Customize
Rental Availability Calendar – Easy to Customize

 

We can enter any start date and the calendar will display data for 31 days.
We can choose to see either Available Quantity or Rented Quantity to display.
In the above image, we have chosen AVAILABLE to display. The quantity displayed is the quantity available after fulfilling all the BOOKED orders entered in the Order Details sheet. we can see that three are 4 days where we have overbooking. The other days in this window have a check mark indicating there is enough inventory to fulfill orders.

If we choose to display RENTED quantity, the calendar will look like below.

Rental Availability Calendar - View rented quantity of assets
Rental Availability Calendar – View rented quantity of assets

 

That’s it. It is a very simple but effective calendar view. By default, it displays up to 30 assets. If we have more assets in our business, we can unprotect the sheet and then extend the table down for more rows.

Expanding the Calendar

This method applies to all tables in locked or protected sheets. First, we will unprotect the sheet using indzara as password.

Go to Review ribbon and click on Unprotect sheet. This pop up window will appear.

Unprotecting Rental Availability Calendar Sheet
Unprotecting Rental Availability Calendar Sheet

 

Enter indzara as password.
Now, click on any cell outside the calendar. For example, click in cell AJ 45. Then, move your mouse cursor to AI 37. You will see a small downward arrow as shown below.

Extending the Rental Calendar to show more assets - Extend rows
Extending the Rental Calendar to show more assets – Extend rows

When you hover over that, your mouse cursor becomes double pointed arrow. Just click and drag down to as many rows as you need to expand. This will now expand the calendar functionality to the additional assets.

Please protect the sheet again to prevent any unintentional editing of formulas.

 

In the next article, we will see how we can view and print the Invoice

How to Enter Rental Orders in Rental Business Manager Excel Template?

Posted on

In this article, we will see how we can enter rental orders in the template. If you have any questions, please leave a comment below.

 

This article is part of a series of support articles of Rental Business Manager Excel template. For more information on the product, please visit Rental Business Manager Excel template.

 

Entering Order in 2 Steps

We will enter orders in 2 steps. First is the Order Header and then the Order Details. Order Header includes information that are unique for an order. For example, there will be only one Customer Name and one Order Date for each order. Order Details include the specific assets in the rental order. We will see how we can create them.

 

Creating a rental Order

Let’s start with entering a simple order where we are renting 3 products to our customer ‘Customer One’. First, we will enter in the Order Headers sheet.

Entering a Sample order in Order Header sheet
Entering a Sample order in Order Header sheet

 

The Order Number should be unique. In other words, each order should be entered in one and only one row. The field should not be blank. We can enter any method of numbering orders. The template does not limit that and does not create any pre-defined order numbers. Here, we have entered ‘1’ as order number.
The order was placed on Aug 1st, 2016 and the customer wants to rent assets from 5th Aug to 16th Aug.  Let’s understand that each order will have 3 dates. Order Date, Rent Out Date and Return Date.

  • Order Date is the date when the order is placed. (Aug 1st)
  • Rent Out Date is the date when the item will be rented out. (Aug 5th)
  • Return Date is the date when the rented item will return to the inventory. (Aug 16th)

Important: All the items within an order should have the same rent out and return dates. This implies that we cannot return some or partial items separately.

Order Status
Orders can be put in one of 3 Order Statuses.

3 Types of Rental Order Status
3 Types of Rental Order Status

 

  1. BOOKED: When the rental order is confirmed and we want to reserve the inventory for that order, we choose BOOKED status. This status will impact inventory calculations by reducing the inventory for those products on the order, between Rent Out Date and Return Date.
  2. CANCELLED: When the rental order is cancelled and we want to make those products available in our inventory for other orders, we do so by choosing CANCELLED status. Such orders will not be counted for revenue reporting.
  3. INQUIRY: When we want to provide a quote to our customers, we use the INQUIRY order status. This order will not impact inventory calculations. Items will not be reserved for such orders. They will also not be included in reporting.

 

In an example scenario, a customer can ask for a quote for a future date and we use INQUIRY status. A couple of days later, the customer confirms the order and we can change the status to BOOKED. A few days later, the customer requests a cancellation and we can change the status to CANCELLED.

 

Another important component of an order is how we are going to charge for rentals. We have options to choose from 2 rental types.

2 Types of Rental Charge Rate Types
2 Types of Rental Charge Rate Types

 

PER DAY when we charge for each day (rounded) or we want to enter a fixed rate for the entire rental duration. The template will use this to calculate order amounts.

 

Additional Fields

Entering Additional fields in Order Header Sheet
Entering Additional fields in Order Header Sheet

 

  • TAX RATE: We can set the tax rate for the entire order. You can enter 0 if tax is not applicable.
  • INVOICE #: You can enter an invoice number that will appear on the invoice.
  • PAYMENT DUE DATE: Date when the payment for the order is due.
  • OTHER CHARGES: Any additional cost on the order. For example, shipping charges.
  • ORDER LEVEL DISCOUNT: Any additional order level discount amount. We will be entering product level discounts later.
  • ORDER NOTES: Enter any notes we would want to appear on the invoice.
  • PAID AMOUNT: Any amount that has already been paid by customer for this order.

 

Entering Order Details

Now, let’s enter the products or items that are part of the order. We go to the Order Details sheet. In this example, customer wants to rent 10 Blue folding chairs, 5 Red Folding Chairs and 1 Red Folding Table.
There are 2 methods of checking rental availability of assets. We can check for availability before entering the order in the Order Details sheet.

Checking Availability Before Entering Rental orders
Checking Availability Before Entering Rental orders

 

At the top of the Order Details sheet, we can choose an asset and rental dates. The available quantity will be shown. This availability checker can calculate availability for rental periods up to 90 days. If we enter a longer window than 90 days, we will see an error message.

Error Message when rental duration window is longer than 90 days
Error Message when rental duration window is longer than 90 days

Important: Availability checker will consider all booked orders entered in the table, while calculating availability.

Now, once we have checked for availability for each of the 3 products and confirming that we have enough to rent, we can enter them in the table.

Entering order details such as Asset, quantity, price and discount
Entering order details such as Asset, quantity, price and discount

 

We enter Order Number, then choose Asset Category from drop down list. Then when we go to Asset Name column, its drop down list will show only applicable assets. For example, when I chose Chairs as my Asset Category, then Asset Name drop down will only show chairs. This helps in data entry, especially if you have a lot of assets. You can also just type Asset Name instead of choosing from drop down list.
Enter Quantity of each asset being rented and the rental price for each asset.

If you are providing any discount, enter in Unit Discount field.

As an example, the first row entered in the table shows we are renting 10 Blue Folding Chairs. Each chair costs $2 to rent per day (since our order rate type was set up as PER DAY). But we want to give $1 discount per day.
Now, let’s see how the template calculates amounts. When we scroll to the right on the Order Details sheet, we will see columns in green colored headers.

Please note that these are calculations and should not be edited. Any editing or deleting will break the workbook.

 

Calculated Fields in Order Details - Amount, Tax, Net Price
Calculated Fields in Order Details – Amount, Tax, Net Price

We can see that the data we entered in Order Headers sheet are brought automatically here for calculation purposes. This saves us time as we don’t have to enter them again and again for each line in our order.

  • Rental Duration shows the number of days the assets on the rental order will be rented out. In this case, from Aug 5th to Aug 16th will be 12 days (including both 5th and 16th).
  • Net Price will be Unit Price – Unit Discount. For Blue Folding Chairs, $2 – $1 Discount = $1.
  • Amount Before Tax is Net Price * Rental Duration * Quantity. So, $1 net price for 12 days for 10 blue folding chairs will be 1* 12 * 10 = $120.
  • Tax of 10% will be $12.
  • Amount After Tax is $120 + $12 to $132.

Similarly, each line item’s calculations are automatically done.

That’s it. We have completed entry for the rental order. In this case, we entered 3 rows as we are renting 3 different assets. If you have more in your order, please add more rows.

How to add rows to Excel tables?

 

In the next article, we will learn How to use Rental Availability Calendar?

Getting Started with Rental Business Manager Excel Template – Initial Setup

Posted on

In this post, we will see how we can do the initial set up for the template to customize it for our rental business.

 

This article is part of a series of support articles of Rental Business Manager Excel template. For more information on the product, please visit Rental Business Manager Excel template.

 

Step 1: Enter Business information

Enter our Business Information in the Settings sheet. This information will be used to auto-populate in our Invoice.

Enter Business Details in Rental Business Manager template
Enter Business Details in Rental Business Manager template

 

Step 2: Enter List of Asset Categories

Please enter groups in which you would like to categorize your assets or products.

Enter list of Asset Categories or product categories in Rental Business
Enter list of Asset Categories or product categories in Rental Business

 

There are two key benefits of categorizing your rental assets or products. 1) We can analyze our business performance by identifying categories that bring more revenue versus those that do not. 2) We can make data entry in orders easier by first selecting Asset Category and the Asset drop down list becomes a shorter list to pick from. We have entered 4 Asset categories for this demo.

 

Step 4: Enter the list of Assets or Products in Assets sheet

Let’s enter assets and its related information.  Please make sure that your first asset is in row 6.

Enter list of Rental Assets or Products
Enter list of Rental Assets or Products

 

  • ASSET ID: Unique identification of Asset. This has to be unique. Please do not repeat the same ID or leave the field blank.
  • ASSET NAME: Name of the asset. This needs to be unique.
  • ASSET CATEGORY: Choose the Asset category to which each item belongs. The Asset Categories we had set in the Settings sheet will be used to populate this drop down list.
  • ASSET DESCRIPTION: Description of the rental asset , as needed in our business.
  • STARTING QUANTITY: This is the quantity of the asset owned by the business when we begin using the template. This is entered only once and does not have to be updated daily. If you have rented items out, please include them here. This number needs to reflect all the assets currently owned (rented out or not). When we decide to retire an asset in the future or buy more assets, we can handle that separately. We will discuss that later.
  • TAXABLE: In our business, if we have assets that are not taxable, we can enter NO. If tax is applicable, just leave it blank. By default, tax will be applicable.

 

Step 5: Enter Customer information

In the Customers sheet, we will enter the list of our customers.

Enter list of customers and their data
Enter list of customers and their data

 

Please make sure that your first asset is in row 5.

  • Customer ID and Customer Name should be unique here. Do not leave them blank.
  • Enter Shipping and Billing Addresses. Enter Email and Phone Number. These details can be displayed on the invoice automatically.
  • We can also add additional columns in this table. 2 Custom Columns are available that you can rename and use for your needs.

Step 6: Set up Invoice Sheet

Invoice sheet is the printable invoice that can be automatically created for any rental order. This sheet is very customizable to suit your needs.
First, let’s update the logo. Right click on the existing logo and click on Change Picture.

Change Rental Business Logo on Invoice
Change Rental Business Logo on Invoice

 

Then, choose your logo image from your computer and click on Insert. Now, your logo will appear on the invoice when printing.

To change the size or other attributes of the logo image, press Ctrl+1 or click the Picture Tools — Format ribbon.

Then, let’s review the fields that are displayed on the invoice.

Rental Business Invoice - Customize Invoice easily - Ready to print or export to PDF
Rental Business Invoice – Customize Invoice easily – Ready to print or export to PDF

 

There are 4 key sections in the invoice: Customer Information, Order Information, Rental Asset Information and Order Total Information. The invoice sheet is fully automated and for each order, all we have to do is to type order number to create the invoice. However, before we begin creating invoices, it is recommended that we review the field displayed in the invoice and customize to suit our business needs.

 

Customer Information (8 fields)

Invoice for Rental Business - Customer Information
Invoice for Rental Business – Customer Information

 

Order Information (5 fields)

Invoice for Rental Business - Order level information
Invoice for Rental Business – Order level information

 

Rental Asset Information (8 fields)

Invoice for Rental Business - Asset Information
Invoice for Rental Business – Asset Information

 

Order Total Information (6 fields)

Invoice for Rental Business - Order Total Information
Invoice for Rental Business – Order Total Information

 

Please review all the fields to make sure that the invoice meets your business needs. All these sections are customizable.

  • If you do not need a certain field, just click on a field name and press the Delete key.
  • If you need to choose another field instead, click on the field name. You will see a drop down menu to choose from a list of available fields. Just select the one you need.

 

Once the fields are customized and everything looks good, let’s save the file by pressing Ctrl+S. Now, every time when we create our invoices, it will have the information we need. It’s just that simple.

 

Step 8: Set up Currency symbols

By default, the template does not display any currency symbols as this is meant to be a tool that can be used by any business in any country. However, it is set up to easily apply currency symbols as needed.
Let’s go to Invoice sheet. Press Ctrl+G to open the Go To dialog box.

Selecting currency cells for formatting
Selecting currency cells for formatting

 

Find the name ‘CURR’ and click OK.
Excel will now select all the cells for fields UNIT PRICE, UNIT DISCOUNT, NET PRICE, AMOUNT AFTER TAX and the Order Total section fields.

This is assuming you have not modified the invoice fields. If you have changed or re-ordered the fields, then, please select the appropriate cells you need to apply currency symbols.

Press Ctrl+1 to open the Format Cells dialog box.

Changing Currency Format in Dialog Box
Changing Currency Format in Dialog Box

 

Select ‘Currency’ in the Category and then choose the symbol from the drop down. Click OK. This will now apply the chosen currency to all the selected cells in the Invoice sheet.
For other sheets, if you would like to apply currency formats, please select the cells and then press Ctrl+1.

The calculations in the template are not impacted by currency symbols. This is purely for visual presentation.

 

With this, we have completed our initial setup.

In the next article, we will learn how to enter rental orders.

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

Free Inventory Management Excel template from ExcelChamps

Posted on

I am very glad to share a new inventory management template that my friend Puneet from ExcelChamps has published. If you are interested in a very simple-to-use template that uses forms to enter your stock in and stock out, you will find this one very useful. The Dashboard calculates the current inventory level and value automatically. The template also uses an attractive menu based interface which keeps the interaction simple and easy.

 

Excel Inventory Management Template - Dashboard
Excel Inventory Management Template – Dashboard

 

DOWNLOAD

To download the free template, please visit Inventory Management Excel Template (on ExcelChamps.com)

 

Related Inventory Management Templates