Description

If you buy raw materials and convert them to products by assembling or manufacturing, then you can use this Inventory Sales Excel Template to manage the inventory of products and raw materials. 

Manage price changes in your products and raw materials. You can also create three different types of orders (Purchase, Manufacture, Sale and Stocktaking Variances) and print invoices.

HIGHLIGHTS OF MANUFACTURING INVENTORY SALES EXCEL TEMPLATE

• Manage inventory at a manufacturing/assembly business.
• Automate inventory calculations of raw materials based on products.
  • Detail on the inventory status after each manufactured product.
  • Know the current inventory levels of each product and raw material.
  • Identify if there are stock variances present in your inventories.
• Choose whether you hold manufactured goods or not (at the product level).
• Easy to apply price changes to raw materials, and the template can handle price changes on future dates as well.
• Identify the products and raw materials to be re-ordered.
• Order details with the ability to handle unit-level discounts at the product level.
• Print automatically created invoices.
  • Additional information on the feasibility of the invoiced order is available.
• Product report: Detailed product-level report with extensive product-level details.
• Raw Material report: Detailed raw materials report with extensive inventory details including Stocktaking variances.
• Extensive Report page:
  • Current status of products and raw materials with accounts receivable and payable.
  • Product sale v. raw material purchase report with gross and net profit, respectively along with stocktaking variances in amount and quantity.
  • Key inventory and sales metrics and monthly trend analysis.
  • Identify your best-performing products.
  • Know how different product categories contribute to sales.
  • Product performance report
  • Partner performance report

PRODUCTS

• Can track Re-order points
• At product level, you can include if a product is taxable or not.
• 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.

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 as shown below:

Enter the starting inventory needed and the point at which a re-order needs to be placed.

Similar to the product sheet, here too the template allows you to specify whether the raw materials are taxable or not.

PRICE

A key feature of this template, you can enter the price details (Sale Price) of each of the products while also specifying its manufacturing cost.

Similarly, for all the raw materials you can enter the purchase price in this sheet.

The manufacturing cost is automatically calculated in the sheet, all that you need to enter is the product name and the price effective date, as shown:

MANUFACTURING DETAILS (Bill of Materials BOM)

Enter details on how raw materials are converted to products.

In the image below, please see rows 1 and 2. You can see that 2 Bananas and 3 Strawberries are needed to make 1 unit of Banana Berry Shake (L).

See rows 3 and 4. 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.

Additionally, the template shows the raw material status after each entry of raw materials required per product.
In detail, you can view the inventory that are to be used , what inventory count is available after the corresponding product is made. This also shows, with the remaining inventory, how many finished products can be made after the current product.

PARTNERS

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

Once the details are used in different order types, this sheet updates each partner’ sale and its rank and purchase and its rank:

ORDERS

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

• 4 ORDER TYPES
  • 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.
  • Stocktaking Variances: This refers to the differences between the actual physical inventory counts and the inventory counts as in company’s records. These variances can occur due to several reasons like recording errors, supplier discrepancies, wastage, production process variations, theft/loss etc.

Here, the template can handle the scenario where the order date and the expected delivery date are different.
Here, at order level, one can specify the payment due date, additional charges, discounts and tax rates as shown above.

Note: Expected date is the date when the inventory will be impacted.

ORDER DETAILS

It’s easy to enter the order line items in the Manufacturing Inventory Sales Excel Template.

Can handle discounts at unit level.

Kindly ensure to enter the order details in the same order in which you would enter/raise invoices. This is because the available inventory calculations are based on chronological entries.

INVENTORY LEVELS

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.

PRODUCT REPORT

View the product report (fully automated) which provides all the inventory information at the product level.

This report shows inventory which are under manufacturing, as finished good and as raw material for the corresponding product:

RAW MATERIALS REPORT

View the report (fully automated) which provides the inventory information for each raw material.

INVOICE

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

You can customize the invoice to choose from different header information like Partner Name or Partner Id etc.

Similarly the descriptions for the invoice can also be customized.

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

As an additional feature, you can evaluate whether there are enough inventories and/or raw materials available (based on invoice type) to achieve the particular order. This detail is not in the printable part of the invoice, but can be viewed immediately to the right once the required order no is entered.

EXPENSES

The template can track any expenses made based on the date.

 

REPORT

This dashboard is fully automated and displays key reports and metrics needed for manufacturing inventories and sales.

Current Status

The current status of products and raw materials and status on the inventories.

This also includes, accounts receivables (from customers) and payables (due to suppliers) and also the corresponding ageing report.

Product and Raw Materials Report

For a given start and end date duration, this gives a snapshot of product sales and raw material manufactured report; the stocktaking variances as quantities and variance amounts, along with profits as shown:

Monthly Metrics:

Important sale and purchase metrics are provided per month along with the monthly trend chart for a chosen metric.

Top and Bottom performing products:

Sort the top and bottom-performing products based on various metrics like quantity, amount or margin. 

Product Performance

This shows how each product has performed (i.e the sale trend) which can further aid in choosing whether the product actually drives profits.

Partner Performance

Here, you can get a quick view of each of your partners’ performance based on the quantity of sales, purchases, and stocktaking variances. You can also see the top-performing customers and suppliers based on sales and value (amount), respectively.

 

Google Sheets

Product Tour Video

FAQ

Product Support

Manufacturing Inventory and Sales Manager (Excel) – Step by Step User Guide