Inventory-management-excel-template-help
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View Inventory-management-excel-template-help as PDF for free.
More details
- Words: 1,062
- Pages: 20
Loading documents preview...
Simplified Tax – Inventory Management Excel Template Help Quick Tips:..................................................................................................................................................................................................................................................... 2 Sheet Wise Help ......................................................................................................................................................................................................................................... 4 1.
Home Tab ........................................................................................................................................................................................................................................ 4
2.
Inventory Master........................................................................................................................................................................................................................... 5
3.
Customer Master .......................................................................................................................................................................................................................... 7
4.
Supplier Master ............................................................................................................................................................................................................................. 8
5.
Purchase........................................................................................................................................................................................................................................... 9
6.
Sales................................................................................................................................................................................................................................................ 11
7.
Receipts ......................................................................................................................................................................................................................................... 14
8.
Payments ...................................................................................................................................................................................................................................... 15
9.
Stock Summary .......................................................................................................................................................................................................................... 16
10. Reminder ...................................................................................................................................................................................................................................... 17 11. Dashboard ................................................................................................................................................................................................................................... 18 12. Invoice ........................................................................................................................................................................................................................................... 19
Let's learn how to use inventory management excel template in order to get most out of it.
Quick Tips: 1. Press " Ctrl + D " in the immediate next row of table to add new row.
2. Select Up Arrow to Go to the top of sheet.
Sheet Wise Help 1. Home Tab It is the starting step to inventory Management. Enter Business name and accounting period.
2. Inventory Master Input the particulars of stock maintained by you along with the opening stock for period and other details as required.
Product : Enter Your stock name here Unit : Enter the unit of measurement Opening Qty : Enter opening quantity of particular stock Opening Value : Enter opening value of particular stock
Reorder Level : Define the level at which you would intend to place a new order of purchase. (e.g. Suppose today’s A.C. stock is 3 unit. Now you know, that it takes 3 days to receive new A.C. from supplier and you sell 1 A.C. Per day. So if you do not order today, you will be running out of stock. So 3 is your reorder level.) Reorder Size : Define the size of order to be placed on stock hitting of reorder level. (e.g. Today’s A.C. stock count is 3 unit. You generally place an order of 5 unit. Then 5 is your reorder size for Product A.C.) Following Columns are auto generated for better stock management. Do not enter data in them. Today's Stock : It shows today’s stock quantity. Average Closing Stock value p.u. : It shows today’s stock value per unit. Today's stock Value : It shows today’s stock value. Reorder : It shows whether reordering required or not? Qty to be Ordered : If reorder is required, how much quantity is to be ordered. Product Name : It shows the product name to be ordered. Reorder Estimated Cost : It shows reorder estimated cost based on past purchases. Profit Per unit sold : It shows profit per unit from stock item till date. Total Profit : It show profit earned from stock item till date. Sales : It shows total sales of stock item till date.
3. Customer Master Enter Basic details of customer in this sheet. It will be used while recording sales and generating invoice.
4. Supplier Master Enter Basic details of Suppliers in this sheet. It will be used while recording purchase.
5. Purchase Record your purchases here.
Invoice No. : Enter Purchase invoice number here. Date : Enter purchase date here. Supplier Name : Select the Supplier Name. (Based on Suppliers entered in Supplier Master) Product Name : Select the Stock item Name. (Based on stock item entered in Inventory Master) Quantity : Enter Quantity purchased.
Rate : Enter Rate per unit. Value : It will be multiplication of Quantity * Rate Due Date : Enter the date on which you need to pay to supplier. Amount Paid : It shows the amount already paid to supplier for the supply of stock. It will be generated from Payment Sheet. Do not enter anything here. Amount Due : It shows amount to be paid. (Note that the amount due will only shows the amount which has already due but not paid.)
6. Sales Record Your Sales here.
Invoice No. : Enter the Sales Invoice number here. (Note: If you want to prepare invoice with more than one product. Keep invoice number of both item same and enter different serial number.) Sr No. : It is unique line item number assigned to sales of stock item.
Date : Enter date of sales. Customer Name : Select the Customer Name. (Based on Customer entered in Customer Master) Product Name : Select the Stock item Name. (Based on stock item entered in Inventory Master) Quantity : Enter Quantity sold. Rate : Enter Rate per unit. Value : It will be multiplication of Quantity * Rate Due Date : Enter the date on which you are supposed to receive payment from customer.
Amount Received: It shows the amount already received from customer for the supply of stock. It will be generated from Receipt Sheet. Do not enter anything here. Amount Due : It shows amount yet to be received. Cost Price : It shows cost of product sold. It is generated based on opening price as well as average purchase price.
Total Profit : It shows profit earned from sales of stock. It is generated based on opening price as well as average purchase price and sales price. % of Sales : It shows Profit as a % (Percentage of Sales).
7. Receipts Enter the details of payment received from customers.
Sr. No. : Enter unique serial number assigned to particular sales. (Against which you are receiving payment.) Date : Enter Date of Receipt. Party Name : This will be auto generated based on Serial number of sales tab. Amount Received : Enter the amount received.
8. Payments Enter the details of Payment made to Suppliers.
Invoice No. : Enter invoice number of purchase. Make sure it is unique. (Against which you are making payment.) Date : Enter Date of Payment. Party Name : This will be auto generated based on Invoice number of Purchase tab. Amount Paid : Enter the amount Paid for supply of stock.
9. Stock Summary Here you can view the date wise product wise stock summary covering opening, purchase, sales and closing stock in quantity, rate and value. Just select the product name and rest all are auto generated.
10. Reminder Enter your to-do / reminder/ tasks to be performed. First define types of work and status of work and then enter tasks. On due date, it will be reflected on Dashboard.
11. Dashboard Dashboard shows summary as well as detailed reports to analyses your inventory, control inventory and mange it properly.
12. Invoice Select the Sales invoice number.
Select product serial number and tax rate applicable and your invoice will ready to print.
Home Tab ........................................................................................................................................................................................................................................ 4
2.
Inventory Master........................................................................................................................................................................................................................... 5
3.
Customer Master .......................................................................................................................................................................................................................... 7
4.
Supplier Master ............................................................................................................................................................................................................................. 8
5.
Purchase........................................................................................................................................................................................................................................... 9
6.
Sales................................................................................................................................................................................................................................................ 11
7.
Receipts ......................................................................................................................................................................................................................................... 14
8.
Payments ...................................................................................................................................................................................................................................... 15
9.
Stock Summary .......................................................................................................................................................................................................................... 16
10. Reminder ...................................................................................................................................................................................................................................... 17 11. Dashboard ................................................................................................................................................................................................................................... 18 12. Invoice ........................................................................................................................................................................................................................................... 19
Let's learn how to use inventory management excel template in order to get most out of it.
Quick Tips: 1. Press " Ctrl + D " in the immediate next row of table to add new row.
2. Select Up Arrow to Go to the top of sheet.
Sheet Wise Help 1. Home Tab It is the starting step to inventory Management. Enter Business name and accounting period.
2. Inventory Master Input the particulars of stock maintained by you along with the opening stock for period and other details as required.
Product : Enter Your stock name here Unit : Enter the unit of measurement Opening Qty : Enter opening quantity of particular stock Opening Value : Enter opening value of particular stock
Reorder Level : Define the level at which you would intend to place a new order of purchase. (e.g. Suppose today’s A.C. stock is 3 unit. Now you know, that it takes 3 days to receive new A.C. from supplier and you sell 1 A.C. Per day. So if you do not order today, you will be running out of stock. So 3 is your reorder level.) Reorder Size : Define the size of order to be placed on stock hitting of reorder level. (e.g. Today’s A.C. stock count is 3 unit. You generally place an order of 5 unit. Then 5 is your reorder size for Product A.C.) Following Columns are auto generated for better stock management. Do not enter data in them. Today's Stock : It shows today’s stock quantity. Average Closing Stock value p.u. : It shows today’s stock value per unit. Today's stock Value : It shows today’s stock value. Reorder : It shows whether reordering required or not? Qty to be Ordered : If reorder is required, how much quantity is to be ordered. Product Name : It shows the product name to be ordered. Reorder Estimated Cost : It shows reorder estimated cost based on past purchases. Profit Per unit sold : It shows profit per unit from stock item till date. Total Profit : It show profit earned from stock item till date. Sales : It shows total sales of stock item till date.
3. Customer Master Enter Basic details of customer in this sheet. It will be used while recording sales and generating invoice.
4. Supplier Master Enter Basic details of Suppliers in this sheet. It will be used while recording purchase.
5. Purchase Record your purchases here.
Invoice No. : Enter Purchase invoice number here. Date : Enter purchase date here. Supplier Name : Select the Supplier Name. (Based on Suppliers entered in Supplier Master) Product Name : Select the Stock item Name. (Based on stock item entered in Inventory Master) Quantity : Enter Quantity purchased.
Rate : Enter Rate per unit. Value : It will be multiplication of Quantity * Rate Due Date : Enter the date on which you need to pay to supplier. Amount Paid : It shows the amount already paid to supplier for the supply of stock. It will be generated from Payment Sheet. Do not enter anything here. Amount Due : It shows amount to be paid. (Note that the amount due will only shows the amount which has already due but not paid.)
6. Sales Record Your Sales here.
Invoice No. : Enter the Sales Invoice number here. (Note: If you want to prepare invoice with more than one product. Keep invoice number of both item same and enter different serial number.) Sr No. : It is unique line item number assigned to sales of stock item.
Date : Enter date of sales. Customer Name : Select the Customer Name. (Based on Customer entered in Customer Master) Product Name : Select the Stock item Name. (Based on stock item entered in Inventory Master) Quantity : Enter Quantity sold. Rate : Enter Rate per unit. Value : It will be multiplication of Quantity * Rate Due Date : Enter the date on which you are supposed to receive payment from customer.
Amount Received: It shows the amount already received from customer for the supply of stock. It will be generated from Receipt Sheet. Do not enter anything here. Amount Due : It shows amount yet to be received. Cost Price : It shows cost of product sold. It is generated based on opening price as well as average purchase price.
Total Profit : It shows profit earned from sales of stock. It is generated based on opening price as well as average purchase price and sales price. % of Sales : It shows Profit as a % (Percentage of Sales).
7. Receipts Enter the details of payment received from customers.
Sr. No. : Enter unique serial number assigned to particular sales. (Against which you are receiving payment.) Date : Enter Date of Receipt. Party Name : This will be auto generated based on Serial number of sales tab. Amount Received : Enter the amount received.
8. Payments Enter the details of Payment made to Suppliers.
Invoice No. : Enter invoice number of purchase. Make sure it is unique. (Against which you are making payment.) Date : Enter Date of Payment. Party Name : This will be auto generated based on Invoice number of Purchase tab. Amount Paid : Enter the amount Paid for supply of stock.
9. Stock Summary Here you can view the date wise product wise stock summary covering opening, purchase, sales and closing stock in quantity, rate and value. Just select the product name and rest all are auto generated.
10. Reminder Enter your to-do / reminder/ tasks to be performed. First define types of work and status of work and then enter tasks. On due date, it will be reflected on Dashboard.
11. Dashboard Dashboard shows summary as well as detailed reports to analyses your inventory, control inventory and mange it properly.
12. Invoice Select the Sales invoice number.
Select product serial number and tax rate applicable and your invoice will ready to print.
More Documents from "Daniel APP"
Inventory-management-excel-template-help
February 2021 1
Final Work Instructions - Electrical Erection
February 2021 1
