Inventory system in excel format


Hi would like to hv an inventory system using excel.

The source of input is fr the sales n purchases


Per sales invoices hv about 10 different items and hv

serial code for each item within a category.

Would like to hv a report to shows the detailed

transactions, as in the sales of each item.

My concern is:

how to reduce the number of input steps ( 10 items

on 1 sales invoice )..i need to key in 10 times on the

invoice number and also the customer's name.

Would like to hv a template for this.

Very much appreciated as boss has no means to get a

auto system.

example of an sales invoice:

Buyer:  ABC Ltd

qty      model     description    unit price    cost

2          A123E    Roller castor   $5            10.00

                           s/n: A1857X

                           s/n: Z9896P

1         TM-90      scanner         $10            10.00

                           s/n: U0909D



Selected Answer

Hello KY,

Please tell your boss that he will have to pay the people he wants to work for him. However, since he already pays you we could help you if you would agree to do the work.

"The work" consists of (a) having an idea, (b) creating a layout and (c) automating it. Out of these three you have done only the first part, partially. In this process you came to the problem of keying in the invoice details 10 times. That is a typical issue with which we might help you here. However, it is part of the data entry process which you haven't designed.

Logically, the data entry process lies between the invoice and the database, meaning, in your work flow you receive the invoice and enter its data into a database from which other parts of your system create evaluations, such as an inventory list. More applied logic would lead you to the realisation that there are more data to be entered, namely the supplier's invoice when you receive goods and other documents when goods are damaged, stolen or otehrwise lost. In all these instances you can't start thinking about data entry before you have a location where to enter the data to - the database.

Nobody here will or can help you design it. Nobody will because it isn't very difficult when you put your own mind to the task. Nobody can because only you know which information you want in your database (transaction date, voucher number, quantity, customer/supplier name/ID, price, minimum and maximum stock levels, stock location to name just a few).

As a final word of advice to your boss, creating an inventory system in Excel isn't something that an Excel novice can do in his spare time. It takes an expert probably 100 hours (half a month full time) to create a system which isn't even very good because if he wants a very good system he wouldn't use Excel. Therefore it is a good idea to start the planning with a list of the informtion which is to be provided by it and start the design work with the layout of the desired output, for example, the inventory list. Then you work backward from the output to the database and from the database to the input.

If you work in that sequence your problem about entering the invoice details 10 times will have to be faced only near the end of the work and the disappointment of your boss at the result can be avoided if he knows what he is going to get from the beginning..


Answer the Question

You must create an account to use the forum. Create an Account or Login