Newbie Stock Control Questions

[Mal123]

Hi,

I have to do an access database for school and I decided to choose stock control. I've gone through a lot of the forums here and read about it and also, checked sample databases. I keep hearing that a stock control database is one of the most difficult. I wantd to get some opinions about it before I started the project. Why does everyone say it is so difficult? I have about 6 months to complete the project. Is that enough time? I am also a beginner and have never done this before. I have very basic knowledge about it. So far I have a pretty good understanding of tables, relationships and queries. But until I start I won't know how much I don't understand. Is it possible for me to learn and do a stock control system? I have the option to choose another project so if it's not I can change it now. I don't want to start and find out halfway through that I can't do it. I chose this project because I find it interesting and I want to learn how to do it.


So what are your thoughts? Any help would be much appreciated.


Sorry if this is the wrong forum to post this in.

 

[RainLover]

There are different types of Stock Control. Let's look at two.

The first is where the stock has a Serial Number. e.g. Cars. With this type you need to know where each car is. When it was purchased and when sold. To whom and how many etc.

The Second is where you sell volumes. Like Sugar or Rock etc. It does not mater which bucket load you purchased or perhaps produced, you simply need to know in Tons or Lbs how much you produced sold and the current balance.

I have written one based upon volume. You can download a copy from the link in my signature. You will need to change it to suit your needs if it is applicable.

Yes it is difficult. I have not seen a sample except my own. I would doubt that your teacher has developed a good one from scratch. That is not to say one does not exist but it is a big job just to create a sample for the AWF site. Another copy is also in this site's archieves.

Have a look and then check your specs. They may be less demanding than what I wrote. eg They may not require Stock Takes. Spillage etc.

Perhaps you could seek advise here on what other is a suitable project.

 

[gemma-the-husky]

it depends exactly what you are doing. counting stock is not so hard.

buy 8 items
sell 4
buy 6
sell 3
buy 12
sell 9

now you have 10 items.

the thing is to CALCULATE the quantity on hand each time, rather than actually store a running balance.

----
where it gets hard is if you want to use values

eg - with the same as above

buy 8 items at £1 each
sell 4 items
buy 6 items at £1.10 each
sell 3
buy 12 items at £1.20 each
sell 9

how much is your stock now worth. Not easy to do.




.

 

[RainLover]

gemma

Stock valuation is out side the realms of Stock Control. All stock varies in price. There are conventions that can be applied for this type of calculation.

Galaxiom has a good understanding of this and has written about it recently.

 

[gemma-the-husky]

how is valuation outside the realms of stock control?

it's a necessary part of many costing systems surely?

 

[RainLover]

how is valuation outside the realms of stock control?

it's a necessary part of many costing systems surely?

Dave
You and I have different experiences in different environments.

My experience says that a Stock Control system would not necessarily contain a Costing System as you described.

It most likely would contain purchases and the cost of those purchases as well as sales and the value of those sales.

However the valuation of the stock on hand, in my experience is handled outside of the normal functions of Stock Control.

As I said in a previous post Galaxiom is quite knowledgeable in this area.

Your experience appears to be quite different.

where it gets hard is if you want to use values

e.g. - with the same as above

buy 8 items at £1 each
sell 4 items
buy 6 items at £1.10 each
sell 3
buy 12 items at £1.20 each
sell 9

how much is your stock now worth. Not easy to do.

I would be very interested in how you would handle the stock value in your example.

 

[gemma-the-husky]

Stock control

OK - basic stock control is not too hard if all you are bothered about is quantities . Adding stock in, and taking out stock out is pretty trivial.

What starts to make REAL WORLD inventory systems difficult is the real world practical application of stock systems

First you have to consider the effect of sales orders, stock outs, back orders, partial order supply, part explosion, re-engineering of products, control of non-homogeneous stocks (eg carpet offcuts, mxed lengths of cut steel rod, and so forth. You often also bring in stock control, automatic re-ordering, minimum stock levels, stock obsolescence, and so on.

Take a "simple" order. A customer orders 20 items, you only have 10 in stock. Do you supply 10, and cancel the rest? Do you order more stock from your supplier, then supply the other 10? Do you wait until the stock comes in, then supply all 20? What do you do if the price of the new stock is dearer than previously?

Or a part explosion. A customer wants a bathroom set - which actually consists of a bath, side panel, toilet, cistern, levers, taps and so on. There may be a price reduction for buying a whole set. Some of parts may be in stock, and others not. You want to let the customer order a bathroom set consisting of all these items, but your stock system needs to deal with the constitiuent parts separately. How do you deal with this?

In manufacturing concerns, you will be taking raw materials and making them into assemblies which are then required to make finished product avaialble for sale. You often have multiple warehouses and stock locations to consider.

Added to all of this, you then have to consider the valuation of stock

In a real world you are trying to allocate costs against sales. Therefore you need to evaluate a cost of sales against the sold items, in order to strike a profit.

Now, you could re-average the cost of stock after a movement in, to establish a new average cost. OR you can use a standard cost. However accounting standards may not accept these as valid for accounting purposes.

If you use standard costs, you generally then need a whole set of standard costing paradigms to evaluate variances - in order to establish the TRUE cost of sales, compared with the "budgeted" standard cost.

So, a basic "how many have I got" stock control is not too hard at all. But a real integrated inventory system is extremely hard to develop.

 

[RainLover]

Dave
You have gone to length to get your point across, especially for a school project.

I would doubt that one would write such a program in Access. If you can write a system like that in Access then I bow to your expertise.


I will remain steadfast in my original opinion that valuations are outside of a simple Stock Control system. One must draw a line somewhere.

If one were to take your examples to extremes where would you stop.

 

[ChrisO]

I think that quantity on hand is a completely separate thing from the value of quantity on hand.

A simple example would be that on Friday we have 2 items @ $500.00 each, meaning that the value of quantity on hand would be $1000.00. There is no change in quantity over the weekend and on Monday we still have 2 items on hand. But the value of quantity on hand on Monday has dropped to $0.0 due to the fact that the quantity on hand is under water.

The value of quantity on hand could be rather difficult to calculate or it may be very simple, we do not know the circumstances. It may be that the quantity on hand referred to refrigerators and they were insured for replacement cost. Hence no value of quantity on hand needed to be calculated at all.

So quantity and value seem to me to be two completely separate things. It would also seem to me to be over complicating things to try and merge both into the same calculation. Sort of like a divide and conquer; get quantity correct, get unit value correct and then calculate the total value of quantity on hand.

No simple answer for unknown conditions.

Chris.

 

[ChrisO]

Another area of quantity on hand which people may not normally think about:-

The amount of water in a steam drum of a boiler is done pretty much the same way as stock control. In this case it’s the control of the quantity of water which matters but it is handled the same way.

The thinking is done dynamically in time. High speed mass balance with low speed level measurement to correct the high speed mass balance inaccuracies over time.

This can equate to calculating quantity on hand as the high speed calculation of the difference between in flow and out flow. For example; we run a query against purchases and sales, in and out.

So far so good, but it is known that the high speed calculations have errors. Those errors accumulate over time. The jargon for this is that total error is the integration of the instantaneous error over time. More goes in than comes out, level goes up. More comes out than goes in, level goes down. Pretty simple really.

That is the high speed short term but what about the long term error? Well, we measure the level, we do a stocktake. So the stocktake becomes the slow speed correction of the high speed calculation with errors.

So we can think of quantity on hand as a two part time calculation. The short term quantity on hand is calculated and is usable. But that is known to accumulate errors over time so a long term stocktake is made to correct those errors.

Dynamically, quantity on hand = high speed calculation with low speed correction.

Chris.

 

[RainLover]

Chris

An interesting subject.

As I said earlier to Dave. Where do you stop calling Stock Control, Stock Control and start calling it something like "That program that does anything and everything with regard to Purchasing. Production, Wastage, Estimination, Forcasts, Budgets, Sales and Profits."

Draw the line somewhere. The more you give the more they want until the program is so complex that it is either too big or obsolete.

In either case start again.

 

[Mal123]

I was thinking of doing a stock control system for an electronics retail store.
I attached the database I have done so far. I've only done the tables and relationships. Is it okay? What's missing?

 

[RainLover]

I was thinking of doing a stock control system for an electronics retail store.
I attached the database I have done so far. I've only done the tables and relationships. Is it okay? What's missing?

Mal

Can you convert your Database to Access 2003 and attach again.

I do not use 2007 and neither do a lot of others.

 

[Mal123]

Here you go.

 

[RainLover]

I would question your table tblTransactions. It would appear that this is just repeating the data from Sales and Purchases.

I would however add a table for Stocktakes.

 

[Mal123]

Okay. I removed the tblTransactions and added a tblStocktakes.

I have a question about normalising the addresses.
Both suppliers and employees have addresses. Do I have to make a separate address table for both of them? If I make one address table how would I differentiate between employees and suppliers?


I appreciate all the help. Thank you.

 

[gemma-the-husky]

I wouldn't worry about addresses - certainly not to have a separate table

either add text fields called Address to both the employee table, and supplier table - or add fields for house number, street, town, post code etc - but dont have a separate table

I suspect you are unlikely to be searching based on addresses so there is little point adding complications

the main thing is to get the stock management bit right.

------

OK - I realise the my full explanation was over the top with regard to the OP's request - I hadn't noticed it was a school project.

BUt the fact remains that you can rarely limit stock control systems to management of quantities. In many businesses stock is intrinsically connected with the sales order / purchase order / manufacturing systems.

Even where you can, in a real environment you may still have to deal with multiple stock locations, stock outs, and some of the other thnigs I mentioned

ChrisO pointed out a number of other aspects of stock systems

None of this makes stock systems obsolete. It just makes them exceedingly difficult to implement. In a SME particularly one that gets involved in manufacture, you need to be able to account for profit, and you just cannot do that without "costing" your sales.

 

[RainLover]

Okay. I removed the tblTransactions and added a tblStocktakes.

I have a question about normalising the addresses.
Both suppliers and employees have addresses. Do I have to make a separate address table for both of them? If I make one address table how would I differentiate between employees and suppliers?


I appreciate all the help. Thank you.

You can have a table that stores "People". Just add a field that looks up another table for the type of person. Supplier, Customer or Both.

 

[RainLover]

------

None of this makes stock systems obsolete. It just makes them exceedingly difficult to implement. In a SME particularly one that gets involved in manufacture, you need to be able to account for profit, and you just cannot do that without "costing" your sales.

Profit requires wages, fuel, equipment rent plus a lot more. Therefore the more you produce the cheaper it becomes.

This is a job for your accounting package.

Dave I get the feeling that you are describing the functions of an ERP. Something like JD Edwards, SAP, Great Plains and Maximo to name a few examples. These products do everthing you have previously mentioned plus a lot more. The cost of these are $Millions.