PRODUCT

Manufacturing – Inventory and Sales Manager – Excel Template

PURPOSE

This Excel template helps in managing inventory at a manufacturing/assembly business. If you buy raw materials and convert them to products by assembling or manufacturing, then you can use this template to manage inventory of products and raw materials. You can also create orders and print invoices. You can save time by the automatic calculation of current inventory levels of products and raw materials. You can also see purchase and sales trends instantly in this Excel template.

 

REQUIREMENTS

Windows: Microsoft Excel 2010, Microsoft Excel 2013, Microsoft Excel 2016

 

PRODUCT TOUR VIDEO

FEATURES

  • Manage inventory at a manufacturing/assembly business
  • Automate inventory calculations of raw materials based on products
  • Choose whether you hold manufactured goods or not (at product level)
  • Know current inventory levels of each product and raw material
  • Identify the products and raw materials to be re-ordered
  • Print automatically created invoices
  • Easily understand the sales and purchase patterns
  • Identify your best performing products
  • Know how the different product categories contribute to sales
  • Easily retrieve and view your order details

HIGHLIGHTS

ABOUT

You can enter your business information in the ABOUT sheet. This will be used to print on invoice later. This sheet also has instructions to use the template, password to unlock and edit, links to the Product page, and definitions of the terms used.

Manufacturing Inventory and Sales Manager - Excel Template - Business Information
Manufacturing Inventory and Sales Manager – Excel Template – Business Information

 

 

PRODUCTS

Enter products in the PRODUCTS sheet. Each line should be a unique product.

Manufacturing Inventory and Sales Manager - Excel Template - Products
Manufacturing Inventory and Sales Manager – Excel Template – Products
  • Description of the product for your reference.
  • 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. Quantity 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 Wikipedia article http://en.wikipedia.org/wiki/Reorder_point)
  • Starting Inventory: Quantity of Product that you have as finished goods when you begin using this template. Enter only once.
  • Tax Rate: You can let the template automatically calculate tax amounts. Just provide tax rates for each product.
  • Hold Product as Finished Good?: The template can handle two different scenarios.
    • Enter No if you buy raw materials from suppliers, receive customer orders and manufacture products and sell them.
    • Enter Yes if you buy raw materials from suppliers, manufacture products and hold them, and sell them to customers when you receive orders.
Manufacturing Inventory and Sales Manager Excel Template - Scenarios
Manufacturing Inventory and Sales Manager Excel Template – Scenarios

In the first scenario, you are not holding finished goods. In the second one, you are.

 

RAW MATERIALS

Enter list of raw materials in the RAW MATERIALS sheet. Each line should be a separate raw material.

  • Name of the Raw material. Unique Raw Material names required.
  • Description of the Raw Material for your reference
  • Raw Material Category: This allows you to categorize Raw materials. If you have numerous raw materials, categorizing similar ones together can help in understanding performance.
  • Re-Order Point: Quantity that you set for each raw material, where when the current inventory level hits that amount, you will place a new purchase order to replenish inventory.
  • Starting Inventory: Quantity of Raw material that you have when you begin using this template. Enter only once.
Manufacturing Inventory and Sales Manager - Excel Template - Raw Materials
Manufacturing Inventory and Sales Manager – Excel Template – Raw Materials

 

MANUFACTURING DETAILS

Enter details on how raw materials are converted to products. In the image below, please see rows 1 to 3. You can see that 1 Banana, 5 Strawberries and 10 Blueberries are needed to make 1 unit of Banana Berry Shake (L). See rows 4 and 5. 2 Apples and 2 Bananas are needed to make Apple Banana Shake (L). The Excel template can now understand how inventory needs to be calculated for raw materials and products.

Manufacturing Inventory and Sales Manager Excel Template -Manufacturing Details
Manufacturing Inventory and Sales Manager Excel Template -Manufacturing Details

 

PARTNERS

Enter Partners’ (Suppliers and Customers) information in the PARTNERS sheet. This will be used in invoice later.

Manufacturing Inventory and Sales Manager - Excel Template - Partners
Manufacturing Inventory and Sales Manager – Excel Template – Partners

 

ORDERS

Enter the list of orders in the ORDERS sheet. Each line represents an order.

Manufacturing Inventory and Sales Manager - Excel Template - Orders
Manufacturing Inventory and Sales Manager – Excel Template – Orders
  • ORDER NUMBER: 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 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.
    • For Mfg orders, this is the date when you placed an internal order to manufacture.
  • PARTNER:
    • For Purchase orders, your supplier is the Partner.
    • For Sale orders your customer is the Partner.
    • For Mfg orders, you can choose ‘Self’ as the Partner name, since you are manufacturing and holding the finished goods yourself.
  • ORDER TYPE
    • Purchase Order: When you place an order to acquire raw materials from suppliers.
    • Sale Order: When your customer places an order to buy products from you.
    • Mfg Order: When you manufacture goods and store as finished goods, choose Mfg as Order Type. If you don’t hold finished goods inventory, please do not use Mfg order type.

ORDER DETAILS

It’s time to enter the order details.

Manufacturing Inventory and Sales Manager - Excel Template - Order Details Entry
Manufacturing Inventory and Sales Manager – Excel Template – Order Details Entry

 

  • ORDER NUMBER: Choose the Order Number from the drop down menu.
  • EXPECTED DATE:
    • For Purchase and Mfg 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.
  • PRODUCT NAME:
    • For Purchase orders, enter raw material name.
    • For Sale and Mfg orders enter product name.
  • 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. For Mfg orders, it is the cost of manufacturing one unit of product.
  • UNIT DISCOUNT: Discount, if any, you would like to apply to the product
  • AMOUNT WITH TAX (Calculated field): = represents the amount of money including any applicable tax. In Purchase orders this would be money leaving you and in Sale orders, this would be money that customers pay you. In Mfg orders, this is the amount it spent to manufacture.

 

REFRESH DATA

After entering any data in the template, please refresh. You can do so by pressing ‘Refresh All’ from DATA Ribbon.

Manufacturing Inventory and Sales Manager Excel Template - Refresh Data
Manufacturing Inventory and Sales Manager Excel Template – Refresh Data

 

INVENTORY LEVELS

You can instantly view overall current inventory levels (Products available, Quantity and also number products to be re-ordered).

Manufacturing Inventory and Sales Manager Excel Template - Overall Inventory Levels
Manufacturing Inventory and Sales Manager Excel Template – Overall Inventory Levels

 

CHECK INVENTORY AVAILABILITY

You can see availability of any product by selecting it from the drop down menu.

Manufacturing Inventory and Sales Manager Excel Template - Check Inventory Availability
Manufacturing Inventory and Sales Manager Excel Template – Check Inventory Availability

PRODUCT REPORT

View the product report (fully automated) which provides all the inventory information at the product level. The report by default shows the first 1000 products, but you can extend the table by filling down.

  • STARTING INVENTORY: This is the information you provided in Products sheet at the beginning.
  • SALES: Units of the product sold until (and including) today
  • CURRENT INVENTORY (FG) Units of the product currently in inventory as finished goods. = Starting inventory + Manufactured quantity – Sales quantity
  • CURRENT INVENTORY (RM) Units of the product that can be manufactured based on currently available raw materials
  • TOTAL INVENTORY: Units of product that are either available as finished goods or as raw materials. CURRENT INVENTORY (FG) + CURRENT INVENTORY (RM)
  • TO ORDER?: If total inventory is less than or equal to Re-Order Point of product, then ORDER, else NO. You can choose only products to order by selecting ORDER in the filter.
  • SALES RANK: This is the sales rank of the product.
    • Please note that if you have applied filters in SALES_REPORT sheet (which we will see in a few minutes), then SALES RANK will update accordingly.
Manufacturing Inventory and Sales Manager - Excel Template - Product Report
Manufacturing Inventory and Sales Manager – Excel Template – Product Report

 

RAW MATERIALS REPORT

View the report (fully automated) which provides the inventory information for each raw material. The report by default shows the first 1000 products, but you can extend the table by filling down.

  • STARTING INVENTORY + PURCHASES: Units of raw materials in Starting Inventory + Units purchased until (and including) today
  • USED (TILL NOW): Units of raw materials used in manufacturing products until (and including) today
  • AVAILABLE NOW: Units of raw material available today for use
  • TO ORDER: If ‘Available Now’ is less than or equal to Re-Order Point of Raw Material, then ORDER, else NO. You can choose only Raw Materials to order by selecting ORDER in the filter.
Manufacturing Inventory and Sales Manager - Excel Template - Raw Materials Report
Manufacturing Inventory and Sales Manager – Excel Template – Raw Materials Report

 

INVOICE

Enter Order Number to create the invoice. Amount calculations, Tax calculations and discounts will be automatically handled by the template.

Manufacturing Inventory and Sales Manager - Excel Template - Invoice
Manufacturing Inventory and Sales Manager – Excel Template – Invoice

 

You can customize the invoice with the following

Manufacturing Inventory and Sales Manager - Excel Template - Invoice
Manufacturing Inventory and Sales Manager – Excel Template – Invoice
  • Choose whether to display discounts or not.
  • Choose to display Custom Column in Order Details
  • Choose to display one or two custom columns from Partners sheet.

 

You can change the logo easily by right clicking on the image and changing picture.

Manufacturing Inventory and Sales Manager - Excel Template - Invoice Logo
Manufacturing Inventory and Sales Manager – Excel Template – Invoice Logo

SALES REPORT

Sales report (fully automated) allows drilling down into the purchase and sales trends overall and also for each product or category.

Manufacturing Inventory and Sales Manager Excel Template - Sales Report Filters
Manufacturing Inventory and Sales Manager Excel Template – Sales Report Filters

 

Manufacturing Inventory and Sales Manager Excel Template - Sales Report Filters
Manufacturing Inventory and Sales Manager Excel Template – Sales Report Filters

 

Purchase and Sales (Qty and Amounts) in the last 36 months.

Manufacturing Inventory and Sales Manager Excel Template - Purchases Chart Report
Manufacturing Inventory and Sales Manager Excel Template – Purchases Chart Report
Manufacturing Inventory and Sales Manager Excel Template - Sales Chart Report
Manufacturing Inventory and Sales Manager Excel Template – Sales Chart Report

 

Amount contribution % by product category

Manufacturing Inventory and Sales Manager Excel Template - Contribution by Product Category
Manufacturing Inventory and Sales Manager Excel Template – Contribution by Product Category

 

Identify the top 10 products by Sales Amount

Manufacturing Inventory and Sales Manager Excel Template - Top 10 Products
Manufacturing Inventory and Sales Manager Excel Template – Top 10 Products

 

 

BUY

US$30

CUSTOMER RATING

100% SATISFACTION

If you are not satisfied with the product, please e-mail support@indzara.com and we will issue a full refund.

FILE DELIVERY PROCESS

You will be able to download the file immediately upon payment. You will also receive an e-mail immediately with the download link.

FREE UPGRADES

All future updates and upgrades are absolutely free.

LICENSE TERMS

PRODUCT SUPPORT

Product Support

OFFER

Offer Details

Special Offer - Buy One, Write Review, Get One Free

ADD A REVIEW

1 2 3 4 5

US$30