Low Cost-Inventory Control System - Assignment Example

The inventory control system deals with which items are issued from, the date of delivery, items delivery and stock take, receiving orders, and reordering of items.

The inventory system could become more sufficient and valuable for any business when the accounting system is directly and properly linked with the inventory system because the accounting system keeps track of the company’s financial transactions. It includes 3 more sub-systems.

1. Accounts receivable
2. Accounts consumable
3. General accounts

Manually, it is very difficult to keep such information accurate and updated in the stock registers and the business financially may become at a loss when incorrect information about their item has been entered in these registers. So, it is very necessary to make their inventory and accounting system computerized. For that purpose, you create a database for your small business “Cloth-Shop”, which provide you with an environment from which you can handle the process of inventory and accounting at the same time by investing nominal cost in it.

The necessary equipment and the costs involved in computerizing the system

S #

Necessary Equipment

Estimated Cost

Purpose

1

A Computer System

$250

Essential for entering data and receiving orders 

2

A Printer

$75

Used to print the invoices

3

Papers for Printing

$12

Giving Hard copies of invoices to the customers

4

A Software/program CD

$3

Windows7 or Ms-Office

Total

$340

Workflow Diagram of Inventory & Account System (Fig #1)

Customer Order

Fig #1 shows the relationship between customer and inventory system.

1. Receive the customer order
2. Verify the order. If the order is verified then step 3 else go to step 1
3. Check item’s availability & generate transfer order. If it is local then step 5 else step 4
4. Generate transfer delivery advice & prepare shipment step 7
5. Generate local delivery order and go to step 6
6. Prepare invoice for all orders
7. Prepare for shipment and then go to step 8
8. Arrange the transport for delivery

Required software that illustrates how the system will work

Ms-Access, a database system is just a computerized record-keeping system used to create such programs where transactions are needed because it keeps the data in the form of tables on which a user can perform a variety of operations easily and quickly. Such as:

• Adding new data into empty files
• Inserting data into an existing file
• Retrieving data from an existing file
• Changing data in existing file
• Deleting data from an existing file
• Removing files from the database

Advantages of computerized database system

• Compactness: no need for massive use of paper
• Speed: retrieve and update the data far faster than a manual system. Queries can be answered more quickly without any need for time-consuming manual or visual searches.
• Reduce data redundancy
• Improve data integrity
• Maintain data independently
• More powerful manipulation of data allowed
• Search easily: Linked easily from one table of data to another table of data

How to create a database for the inventory system

Create 3 tables, one for customer information, the second for item information, and the third for stock availability. Required fields for Table # 1 are Customer-id, Customer-name, Item-no, Date-Purchase, Item-Price, etc. For Table # 2 are Item-no, Size, Color, Quantity, date-received, and company-no. etc.  For Table # 3 are Item- no., Item-sold, Item available, etc

Create Tables

1. Invoke Ms-Access
2. Click the “blank access database” option
3. The File New Database dialog box appears, save the database by specifying the name, as “Golden Cloth-Shop”
4. Click on create button
5. Select the “Create table in Design view” shortcut window and click the Open button. A Table design view window opens.
6. Type the required data field in the left column of the first row and press the Tab key to move the cursor into the Data type column and select the data type for this field.
7. Press the Tab key to move the cursor into the description column. Type primary key.
8. Click the (primary key) button on the database toolbar to make this field the primary key.
9. Repeat steps 6 & 7 to define the remaining fields.
10. Click on the save button in the standard toolbar.
11. Enter the Table name, named CUSTOMER, and click OK.
12. Click on the datasheet View button in the standard toolbar.
13. Enter the data.
14. Similarly, create Tables 2 & 3 and save them as ITEM-INFO & ACCOUNT respectively.

Create Relationship

1. Click on the relationship button OR select the relationship from the Tool menu
2. From the show Table window select Table and click Add button
3. Do the same for Tables 2 & 3 and click the Close button
4. Edit Relationship dialog box appears
5. Select the “Enforce Referential Integrity” check box
6. Select the “Cascade Update….” As well as the “Cascade Delete…” checkboxes and click on the Create button
7. Close the Relationship window

How to maintain a database (apply Queries / Operations)

Searching a Table for a specific record

1. Open the desired Table in Datasheet View
2. Choose Find from the Edit Menu
3. The Find and Replace dialog box will appear, type the required information and follow the instructions
4. Click the Find Next button

Deleting a record from Table

1. Position the mouse over the area to the left of the record and then select the record
2. Click on the Delete Record button to delete the record
3. A warning dialog appears, click Yes to permanently delete the record

Updating a record

1. Find a record using the Find Option
2. In the search record double click on the desired field
3. Enter the new data and press the Enter key