Description
If your business requires to buy raw materials and convert them to products by assembling or manufacturing, then managing the inventory of products and raw materials is surely a task! This simple Google sheets template is just what you need.
Manage inventories, raw materials, price changes in your products and raw materials. You can also create three different types of orders (Purchase, Manufacture, and Sale) and print invoices: all with one Google sheet file!
HIGHLIGHTS
- 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.
- 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.
- 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.
- 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.
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,2 and 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 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.
- 3 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.
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 Google Sheets 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 clicking on the three dots as shown and choose to replace image.
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, along with corresponding 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 quantity in sales or purchases. You can also see the top performing customers and suppliers based on sale and value (amount), respectively.
For a small manufacturer this template is the perfect solution. It provides me with the ability to use my raw materials to build BOMs (Bill of Materials) or to sell my raw materials directly as finished goods. It easy to implement and it has built in data validations to make sure everything is tying out. If you have a simple manufacturing process then all you need is a simple solution. All you have to do is: add your suppliers and customers, purchase your raw materials, create your BOMs, create your invoices. Like magic, inventories levels are adjusted for raw materials and finished goods and it’s a happy day.