Manufacturing Inventory and Sales Manager (Google Sheets) - Step by Step User Guide
Modified on: Thu, 30 Mar, 2023 at 1:15 AM
You can enter your business information in the SETTINGS sheet. This will be used to print on invoice later.
Enter the product categories that each of your products will fall into:
Enter products in the PRODUCTS sheet. Each line should be a unique 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.
Starting Inventory: Quantity of Product that you have as finished goods when you begin using this template. Enter only once.
Taxable: You can let the template automatically calculate tax amounts. Just provide whether the product is taxable or not. Leave black if Yes.
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.
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.
Taxable: Whether the raw material is taxable or not? Leave black if Yes.
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:This, in turn shall be useful in determining the sale price of the product.
The price effective date column is used to automate any price changes for the product or raw materials, alike. This can be a future date also. That is the template will make the price change effective for a future date as well.
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 and 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.
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:
Enter the list of orders in the ORDERS sheet. Each line represents an order.
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.
EXPECTED DATE: This is the date on which order is expected to be completed.
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.
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.
It’s time to enter the order details.
ORDER NUMBER: Choose the Order Number from the drop down menu.
PRODUCT NAME: The 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 DISCOUNT: Discount, if any, you would like to apply to the product
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.
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.
TO ORDER: This shows a quick view whether to place a re-order
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. 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.
INVENTOY VALUE: The price of the corresponding inventory as on date based on availabilty.
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 headers or column descriptions :
You can change the logo easily by clicking on the three vertical dots and replacing the 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.
The template can track any expenses made based on the date.
This dashboard is fully automated and displays key reports and metrics needed for manufacturing inventories and sales.
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:
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.
This shows how each product has performed (i.e the sale trend) which can further aid in choosing whether the product actually drives profits.
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.