it in management- inventory management system

8/4/2019 IT in Management- Inventory Management System

1/17

i | P a g e Inventory Management System

IT in Management

Project Report on

Inventory Management

System

By

Mickey Haldia Roll Number 19

Monika Mane Roll Number 20

Mrinal Banerjee Roll Number 21

Neha Goyal Roll Number 22Nikhil Jain Roll Number 23

Nikesh Desai Roll Number 24


2/17

ii | P a g e Inventory Management System

Table of contents

1 Introduction. 2

2 Business Model... 3

3 Table Details .. 4

4 Business Rules... 7

5 Query and Reports 8

6 Interface Mechanism .... 13

7 Future Expansions . 17


3/17

2 | P a g e Inventory Management System

1. Introduction

Every medium to big size departmental store needs to manage their inventory for smooth

functioning of its operations. As the operation of the store grows, it becomes necessary for

the store to automate its operations for optimal functioning. The store hence needs to identify

and keep in place a system which will help them achieve this.

An inventory management system can help provides a solution for this problem. Through this

system, the store can not only keep a track of the sales that are being made, but also keep

track of the stock in hand.

The system will also notify the user if the inventory for a particular item falls below a certain

limit. Further, through the system the user can also track the order given to purchase a certain

item from a particular vendor. The user will have to jest enter the supplier number of a

particular vendor, and the system will provide all the details of the purchases made from that

supplier.

Similarly, if the store wants to check the sales made to a particular customer, as and when the

user fill in the customer code, all the sales made to that particular customer will be displayed

by the system.

The customer can place his order by giving the following details:

Item name Quantity

After all the formalities are complete, the bill is generated given to the customer.

The primary user for this system will be the store manager, who would like to know the entire

sales data or details about a particular transaction. This can also help the inventory team to

carry out their work in a more efficient manner.

The details incorporated to develop this system and processes would be described in

following sections.


4/17


2. Business Model

The business model of the Inventory will be as follows:

1) The customer will place an order to the store and the store will record the sales transactions.2) Incase the store has to order or reorder inventory; they will place a purchase order to the

supplier.

3) The supplier will supply the items ordered to the store.


5/17


3. Table Details

1.

Item Master Table

This table will contain all the details about the various items that are up for sale at

the departmental store. The details will include the name of the item, the selling

price, the purchasing price and the stock in hand. The table has the following

fields:

item_code item_name sale_unit_price purchase_unit_price stock_in_handThe primary key for this table is the item code

2. Supplier Master TableThis table will contain all the details of the supplier which are required by the

store to place an order with the supplier for a particular item. The details include

the name of the supplier, the supplier code, the supplier address and his telephone

number. The table has the following fields:

supplier_code supplier_name supplier_addr supplier_telThe primary key for this table is the supplier_code.

3. Customer MasterThis table will contain all the details relevant to the customer. These details are

required by the store in case the customer wants a home delivery for the items.

The details include the unique customer code, the customer name, customers

address and his telephone number. The table has the following fields:

cust_code cust_name cust_addr cust_tel


6/17


The primary key for this table is the cust_code

4. Customer Transaction TableThis table will contain the details of the order the customer has placed at the store.

The details include the customers order number, the item code of the product

demanded by the customer and the quantity demanded. The table has the

following fields:

cust_orderno item_code sale_quantity

The composite key of the table are the cust_orderno and item_code.

5. Sale Transaction Master TableThis table will contain the details of the sales transactions between the store and

the customer. This table will help to generate the final bill to the customer. The

details include the customers order number, the item code the customer

purchases, the quantity the customer purchases. Once we get these values, we can

generate the total amount the customer has to pay. The table has the following

fields:

cust_orderno item_code sale_quantity

The composite key for this table is the cust_orderno and theitem_code.

6. Purchase Transaction Master TableThis table will contain the details of the transactions between the store and the

supplier. This table will help the store to generate the purchase bill from the

supplier. The details in this table include the supplier code, the item code of the

item to be purchased from the supplier and the quantity of the purchase. The table

has the following fields:

supplier_order_no item_code purchase_quantity

The composite key for this table is the supplier_order_no and the item_code.


7/17


7. Customer Order Master TableThis table is required if the store wants inspect a particular transaction with a

particular customer on a given date. The details in this table include the customer

order number, the customer id and the date of the transaction. The table has thefollowing fields:

Cust_orderno Cust_code Cust_order_date

The cust_orderno is the primary key for this table.


8/17


4. Business Rules

Process 1: Sale Transaction Records

The store records all the transactions made to a particular customer andprovides the customer with a bill.

The store records all the details of the customer incase the customer requestsfor a home delivery.

The store provides a unique code to each customer for this purpose. The store provides a unique ID to each and every item present in the store. The customer can chose a particular item, or multiple items while shopping at

the store.

The store can also see the entire sales record or the records of sale to aparticular customer.

Process 2: Purchase Transaction Records

The store records all the transactions it makes while purchasing items goodsfrom a particular supplier.

The store will record all the details of the supplier such as the address,telephone number etc.

The store provides a unique id to each and every customer. The store can order a particular item from a single supplier or multiple

suppliers.

If the inventory a particular item falls below a specified level, the storeautomatically orders the supplier to supply additional inventory.

The store can see the record of all the orders placed to all the suppliers or canview the order placed to a particular supplier for a particular item.


9/17


5. Query and Reports

1) Total SalesThrough this report, the user can check the total sales that have taken place

over a period of time. The report will provide the details such as order date,

order number, the price of the item sold, the quantity of the item sold and the

total amount for which the items are sold. The report will be in the following

format:

The structure of the query will be in the following format:


10/17


In the query we are linking the customer order master, the sale transaction

master and item master tables.

2) Total PurchaseThis report will help the user to analyze the total purchases made from various

suppliers. The report will provide details such as the date on which the

Purchase order was placed, the Items for which the order was placed, the

supplier code, the quantity of order placed and the total amount. The report

will be in the following format:


In the query we are linking the Item master, the purchase transaction and the

supplier order master tables.


11/17


3) Sale to a particular customerThis report will help the user to view the sales transactions made to a

particular customer. The report will include the details such as the customer

name, order number, the customer code, the address of the customer, thetelephone number of the customer and the details of the purchases.

Additionally, the total amount the customer has to pay will also be shown.

For Eg: If the store wants to check the sales made to customer with customer

code C001, then the store will have to view this report. The report will be in

the following format:


In the query we are linking the customer master, the customer order master,

the sales transaction master and the item master table to get the details.

4) Purchase from a particular supplierThis report will help the user to view the details of a particular supplier of a

given product. The report will include details such as the order number for the

item, the supplier code, order date, the name of the supplier, the address of thesupplier, the name of the product purchased and other transaction details.


12/17


The structure of the report will be in the following format:

For example, in the above report we can see that supplier for the Pepsodent

Brushes is Adarsh traders and the quantity ordered is 10. The total amount that

has to be paid to Adarsh Traders is Rs. 130.


In the query, we are linking the item master, the purchase transaction master,

the supplier order master and the supplier master tables to get the details.

5) Stock in HandThis report will help the user to check the inventory turnover and the stock

remaining. This report is the most important report with respect to the store

because it will help the user to get an effective evaluation of the stock and

analyze which items are in demand and which items can be ordered in a lesser

quantity in the future. The report will contain the details such as the inventory

in hand, the quantity sold and the stock remaining. The format of the report

will be as follows:


13/17


The structure of the query will be as follows:

In the query we are linking the item master table with the stock sold query.

The stock sold query contains the record of the stock that has already been

sold. Based on this linkage, we can then find out the stock remaining in the

inventory by subtracting the stock sold from the total inventory.


14/17


5. Interface Mechanism

The main interfaces, which would have to be developed in Visual Basic, are the Item

Details, the Sales bill and the Purchase Bill.

1) Item DetailsThe structure of this interface will be as follows

ITEM DETAIL

Company Name: Date:

ITEM CODE ITEM NAME QTY IN STOCK MAX QTY REORDER QTY

find save delete update add item

Through this interface, the store can add the details of a new product in their data

base. They can specify the reorder quantity, which depicts that if the inventory for a

product falls below this specified limit, then the item will have to be ordered from a

particular supplier. The interface will also help find the details for a particular item,

update the details of an item, delete an item from the database, apart from allowing

the user to add a new item.

2)

Sales Bill

The structure of this interface will be as follows:


15/17


SALE BILLCustomer id: Date:

Customer_Name:

Customer_Add:

Telephone Number: Bill_No:

Item Code Item Name Quantity Unit Price Amount

Grand Total

Submit

Through this interface the store generates a bill for a customer. Apart from storing the

transactions of a customer, the interface also helps to store customer details such as

the name of the customer, his address and telephone number besides noting the date

of the transaction. The interface will also allow the user to print the bill for the

customer.

3) Purchase OrderThe structure of the interface will be as follows:

Print


16/17


PURCHASE ORDERCompany Name: Date:

Supplier Name:

Order

Code:

Supplier Add:

Supplier

Code:

Telephone

Number:

S.No Item Code Item Name Quantity/ UnitDate of

Delivery

Add Delete Find Update

Through this interface, the store can place an order to a supplier for a particular

product. The interface will allow the user to add a new vendor, generate a unique

supplier code, and create a new order number. The user can also search for anexisting supplier and update details of a supplier. As soon as the inventory falls

below a certain limit, the store can place an order for replenishing the inventory.


17/17


6. Future expansion

The system can be improved in the future by automating the system togenerate a Purchase order as soon as the inventory falls below a specifiedlevel.

The system can be made more robust by including login and access rightsbased on the users designation in the store.

it in management- inventory management system

Documents