Stock Management System

BYTEr

BYTEr

BYTEr

Associate
Joined
2 Oct 2004
Posts
1,048
Hi all,

I'm looking for some advice/suggestions.

My work are currently wanting to implement a new stock management system, as at present, it's all done manually by counting stock and inputting into a spreadsheet, as a result we have no real time stock levels, no form of automation and essentially not in full control, a considerable risk to the business.

I have been tasked with creating a project plan to implement a new system with the following two main objectives:

1. To have an automated stock ordering system (via an email to the supplier) if an item quantity falls below a certain level - but will likely require some more detailed rules.

2. To be able to get an instant snapshot of quantities of all items.

I can think of loads more things that we could introduce but at present, the business just wishes to achieve the above as it is hoped that it will dramatically reduce the amount of man hours required and allow us to gain some form of control.

The manager who has assigned this to me is quite technical and has stated he would like the above to be achieved in a database structure using SQL.

I have very little experience in SQL, I'm fairly proficient in Excel and have used Access a little bit, but clearly this is probably out of my level of expertise so obviously in my project plan, under resources I will state I will need someone who is proficient in SQL.

I do believe a report is created every time for a new job which states the materials required, so I'm thinking this can be taken from the stock quantities, and when we order stock add to the quantities.

Anyway, I'm just looking for any suggestions of where to start, questions to ask, tools to use etc.

Thanks in advance.
 
I should have mentioned, the company are very adverse against of the shelf packages, the company is unique due to an in house developed program which all work revolves around, they very much prefer to build things in house otherwise this would have been my first suggestion.
 
Do you have in house developers? Lot of work involved.

First step is to investigate exactly what is required and write a specification for the software.

Would probably need a consultant/contractor or outsource it if you dont have in house developers. May be able to cobble something together but its probably going to suck. :)
 
Last edited:
This is not a simple job you can teach yourself and do a good job at. You need a professional software engineer. They would make:
- a database
- a website front-end so you can view the data in useful formats, input things as required, etc.
- a script which runs at regular intervals to check stock and place orders as required
- stock level needs to be reduced when someone orders it
- stock level needs to be increased when a delivery arrives, or maybe returns
- you need a way to flag stock as "will not be restocking" so you can EOL something
- you need to be able to add new products
- consider how you would recall a product e.g. if there was a health/safety issue

Edit:
- this would all have to be hosted somewhere
- and secure so only certain people can do certain things
 
Thanks for the replies.

We must have some sort of developers, as like I mentioned, our company can only do what it does based on an in house created program which is updated frequently, the company have also made other internally created programs for other areas of the business so we must have people talented in this area.

Just to be clear, this is materials we use internally to create a product we send onto customers, customers don't order any of the materials.

I did have a feeling that this is more work than perhaps has been appreciated, however I'm trying to get as good idea as possible to create a start to a project plan.
 
Well I’m going to first speak to the person who has assigned this to me to make sure they understand the complexity of a bespoke system, I’m happy to try and spec what we want, how we want it to work, resources and time needed etc but I’m not equipped to make an actual start to it programming wise.
 
How many warehouses, stock items and whats the turnover of these items?

Are you talking about 3 guys in a store + backroom who sell maybe 20-50 items a day, or are we talking multiple locations, drop-offs from suppliers, multiple shifts of workers/pickers and automated picking systems?

Do you want QR or barcode readers and do check-in / check-out of stock items (using handhelds)? Do you need label printers or can you use the barcodes on the stock items themselves? How do you deal with materials or stock that is bought by the reel / drum / pallet, but used by the metre / gram / unit ?

Does it need to integrate real-time with the sales manufacturing systems?

You need some kind of scope before considering building an in-house system vs buying one.
 
What about this? It's a starting point for the developers who can develop in php? A quick google brings up this. It's free by the looks of it. It uses PHP/Bootstrap/SQL.

This is a huge project and it might take a few months to actually get something working properly.

 
Last edited:
skyripper said:
How many warehouses, stock items and whats the turnover of these items?

Are you talking about 3 guys in a store + backroom who sell maybe 20-50 items a day, or are we talking multiple locations, drop-offs from suppliers, multiple shifts of workers/pickers and automated picking systems?

Do you want QR or barcode readers and do check-in / check-out of stock items (using handhelds)? Do you need label printers or can you use the barcodes on the stock items themselves? How do you deal with materials or stock that is bought by the reel / drum / pallet, but used by the metre / gram / unit ?

Does it need to integrate real-time with the sales manufacturing systems?

You need some kind of scope before considering building an in-house system vs buying one.
Click to expand...

2 identical warehouses, 100’ish stock items, average turnover is 145k a month but the median is only 900 (highest turnover is 500k a month, lowest is only 30).

We roughly do 600-700 jobs a day, all picked up by the same company, single shift and no automated picking system as materials are loaded onto machines depending on which run of jobs is next.

Automated check-in of items via barcode can be achieved I think, it’s not needed for check-out as material usage is measured per job.

It does need some real-time integration, but at the start even if it’s only updated once a day would be a massive improvement.

Slight difficulty is I haven’t actually been to the manufacturing sites and only been here a few months so my knowledge of current systems isn’t exactly great but it's all completely manually requiring constant stock counts.
 
Not to mention to the above, you're going to need to close down for a couple of days to do a complete stock take and then update the system with all the figures.

You could do this on a rolling project but in my experience, it opens it up to errors.

You're also going to need to consider stock takes and any stock corrects as well as a reporting system on stock in, stock out, stock missing, damaged etc.

My question would be, how do you take orders from clients? what software deals with those and produces their orders because in reality, you're going to want to tie them all up. Not only will this help with stock tracking but also stock projection. If you've 10 of X in stock and customer Y orders 4 you need to see that you've got, 6 free.

EDIT, if it's done via sales, you won't need to manually track materials leaving as marking orders complete will automatically update the system. You will just need to be able to update stock counts and account for any waste/damage.
 
AHarvey said:
Not to mention to the above, you're going to need to close down for a couple of days to do a complete stock take and then update the system with all the figures.

You could do this on a rolling project but in my experience, it opens it up to errors.

You're also going to need to consider stock takes and any stock corrects as well as a reporting system on stock in, stock out, stock missing, damaged etc.

My question would be, how do you take orders from clients? what software deals with those and produces their orders because in reality, you're going to want to tie them all up. Not only will this help with stock tracking but also stock projection. If you've 10 of X in stock and customer Y orders 4 you need to see that you've got, 6 free.

EDIT, if it's done via sales, you won't need to manually track materials leaving as marking orders complete will automatically update the system. You will just need to be able to update stock counts and account for any waste/damage.
Click to expand...

In terms of taking orders from clients, it’s a little bit unique in that we have an in-house created program that is loaded onto a customer’s computer, from here they order what they want produced which is then automatically sent to either of our two production sites depending on where it is to be delivered.

Stock tracking would certainly be required if we are to automate stock ordering (via an email to supplier) so when an item falls below a certain quantity it fires of an email.

I do believe we will be able to track the stock we have used, as each job we do has an automatic list of materials to be used, using this, we can subtract from the amount of stock we have to have a live stock quantity which is really the main objective at present.
 
I created an application for a company I worked for about 8 years ago. The main thing is the database, I used stored procedure so the logic was hidden from the application. When an order is processed the items stock count is adjusted. When a goods received note is processed the stock count is adjusted. I also had to add a stock take part for when the stock take was done, this deals with breakage. You might also need to add returns and transfers from one outlet to another. Automate as much as you can as end users mess up a lot!
 
You must log in or register to reply here.
Back
Top Bottom