I am glad to announce that the Manufacturing Inventory and Sales Manager Excel Template has been upgraded from v1 to v2. Customers who have already purchased receive the v2 for free. Please create an account with indzara.com and you can log in to access all your files anytime. If you would like to continue using the v1 file, please do so. This is an optional upgrade if you are interested in receiving the enhancements. The following are the key changes in v2 from v1.
Holding as Finished Goods
v1 of the template allowed only products of one kind, either holding as finished goods or not. The v2 of the template allows mix of products that are manufactured and held as finished goods and products that are assembled on demand (without holding as finished goods). You can indicate this for each product in the PRODUCTS sheet.
Choose Yes if you hold finished goods in inventory. If you choose Yes, then while entering orders, you should enter Purchase, Manufacturing and Sale orders.
Leave it blank if you do not hold finished goods in inventory (i.e., you manufacture on demand and sell immediately), then you can just enter Purchase and Sale orders. Manufacturing orders are not required.
You can create invoices instantly by typing just the order number.
Now you can calculate tax on your orders easily. You will indicate tax rate for each product in the PRODUCTS sheet and the tax calculations will be made automatically. They will also appear on the invoice.
You can enter discounts on orders. In the ORDER DETAILS sheet, while entering order line items, you can enter discount in the UNIT DISCOUNT column. The template will automatically apply discounts to the amount calculations.
Re-Order Point for Raw Materials
Raw Materials can also have re-order points. You will enter them in the RAW_MATERIALS sheet.
Raw Materials report can now be used to identify which raw material should be ordered. Please use the last column TO ORDER to filter on raw materials that need to be ordered.
Store Partner Information
Partner (Suppliers and Customers) information can be stored. This will be used in the invoice. By entering partner information once, you can re-use them automatically in the invoice for all future orders.
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 save time by the automatic calculation of current inventory levels of products (finished goods) and also products (in the form of raw materials). All the order details will be managed in one place and you can see purchase and sales trends instantly in this Excel template.
Windows: Microsoft Excel 2010, Microsoft Excel 2013
PRODUCT TOUR VIDEO:
PRODUCT DEMO VIDEO:
The template can handle two different scenarios.
Scenario 1: Buy raw materials from suppliers, receive customer orders and manufacture products and sell them.
Scenario 2: Buy raw materials from suppliers, manufacture products and hold them, and sell them to customers when you receive orders.
In the first scenario, you are not holding finished goods. In the second one, you are. Before you begin using the template, choose the setting that is appropriate for your business.
Enter products in the PRODUCTS sheet. Each line should be a separate product.
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.
2. 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.
Starting Inventory: Quantity of Raw material that you have when you begin using this template. Enter only once.
3. 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.
4. ORDER DETAILS
It’s time to enter the order details. If you hold finished goods inventory, then there are three types of Orders. Purchase, Sale and Mfg.
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 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.
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.
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.
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.
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.
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.
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. In Mfg orders, this is the amount it spent to manufacture.
After entering any data in the template, please refresh. You can do so by pressing ‘Refresh All’ from DATA Ribbon.
You can instantly view overall current inventory levels (Products available, Quantity and also number products to be re-ordered).
CHECK INVENTORY AVAILABILITY
You can see availability of any product by selecting it from the drop down menu.
5. 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.
6. 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
7. SALES REPORT
Sales report (fully automated) allows drilling down into the purchase and sales trends overall and also for each product or category.
Purchase and Sales (Qty and Amounts) in the last 36 months.
Friends, I am glad to announce that I have published a new Excel template. The Retail Inventory and Sales Manager is the most popular premium Excel template at indzara.com. It is applicable to retail businesses, but not for manufacturing type of businesses.
The new Manufacturing Inventory and Sales Manager Excel template can be used in scenarios where you purchase raw materials from suppliers, manufacture products and then sell to customers. The template is simple and very easy to use. If interested, please see the product page. Early adopters will receive a discount. For a limited time, you can get a 40% discount.
This is the first version of this product. Please provide your feedback in the product support page so that I can understand what upgrades to make in the next version. Thank you for the support.