Methodology for inventory control
Elaine
your business, ask your accountant (Chartered or Certified) to go
over the accounting theory and the application as applies to your
business. Take a deep breath, then reconsider whether this
should be done outside your accounting program. More is involved
than simply counting.
For example, I believe that a reply in this thread mistakenly
asserted that you should avoid negative numbers. That is
incorrect, from an accounting, and even from a business point of
view. Hint: BACKORDER.
There are timing issues, cost issues, accounting methods issues,
etc. etc. Many programmers tell you never to store any
calculated information. Well, that's absolutely incorrect from
an accounting standpoint. For example, if your company received
3000 of Widget B 65 days ago at a cost of .05 monetary units per
and 3500 today at a cost of .045 monetary units per, what is your
inventory valuation? What is your inventory valuation for that
part, assuming a beginning value of 0, if today's physical count
is 700? That answer would vary according to your inventory
valuation method, a business (accounting) rule. You have to have
the historic cost information, *and* the algorithm.
Also, I wouldn't worry about accounting separately for breakage &
etc. unless your inventory consists of uniquely identifiable and
expensive units, like jet plane engines, because the definition
of units on hand is Beginning Inventory + Purchases - Ending
Inventory. However it got not to be a part of the ending count
is irrelevant (except for the kind of inventory noted above, or
in case of casualty, like fire, earthquake, etc).
These are just some of the parameters.
HTH,
Elaine
On Sat, 25 Mar 2000 12:15:54 +1100, lie...@hotmail.com managed
to spit out:
>I need to add inventory control to our Access 97 database, but I can't seem
>to find a reference as how to do it. The sample NorthWind database contains
>stock level, but looks like it needs to be edited manually?
>
>What I need to achieve is to record of the stock quantity for each product.
>Naturally I would need to be able to stock in and any order processed will
>deduct the quantity. I may also need to have something for quantity
>adjustment in case of brokage etc.
>
>I am not sure which way to achieve this. Should I treat Qty as a query
>result or should it be a separate field. If I treat it as a query result,
>then every time when I need to know the Qty in stock, it would have to sum
>up all the ins and outs. I am not quite sure how to add or subtract the Qty
>if I make it as a field? by VBA/Events?(I only know how to modify existing
>codes with common sense:))
>
>This could be a stupid question, but I have been thinking about this for a
>while and could not find any reference by searching news posting other than
>buying a commercial product.
Elaine Nance
Consultant - Developer
Juneau, Alaska
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Computers are useless. They can only give you answers.
-- Pablo Picasso --
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Some Access links:
ATTAC Consulting Group - MS Access Tips & Code
http://ourworld.compuserve.com/homepages/attac-cg/ATip.htm
Tony's Main Microsoft Access Page
http://www.granite.ab.ca/accsmstr.htm
Trevor's MS Access FAQ
http://easyweb.easynet.co.uk/~trevor/AccFAQ/
C. Tripp's Developer and Job Search Links to Everywhere
http://home.earthlink.net/~ctripp1/index.html
lie...@hotmail.com
to find a reference as how to do it. The sample NorthWind database contains
stock level, but looks like it needs to be edited manually?
What I need to achieve is to record of the stock quantity for each product.
Naturally I would need to be able to stock in and any order processed will
deduct the quantity. I may also need to have something for quantity
adjustment in case of brokage etc.
I am not sure which way to achieve this. Should I treat Qty as a query
result or should it be a separate field. If I treat it as a query result,
then every time when I need to know the Qty in stock, it would have to sum
up all the ins and outs. I am not quite sure how to add or subtract the Qty
if I make it as a field? by VBA/Events?(I only know how to modify existing
codes with common sense:))
This could be a stupid question, but I have been thinking about this for a
while and could not find any reference by searching news posting other than
buying a commercial product.
--
Lier Duh
LARS ÅM
care of what goes in and out of stock, and the current stock demands a lot
of considerations.
Basically I think what you need is:
A table that has the current stock
Another table to store all transactions history.
A table with Item information
Needed forms for different transactions: Incoming, outgoing, adjustments.
Basically: Enter the transaction in a form. Use the values in the form to
add or subtract from the current stock. Store the transaction in the
transaction history table, together with the value for the stock before and
after the transaction, and the transaction date and time, and the user that
generated the transaction.
You will have to set up rules to what are allowed and not allowed.
Will negative stock be allowed ? It gives no sense to have it. How will
you avoid it ?
If you add orders and order handling to this, and perhaps you want to know
what places you have your stock, my best recommendation is to search for a
finished set up application. I know I have seen several Sharewares on the
net, that might be useful.
Lars
<lie...@hotmail.com> skrev i
meldingsnyheter:tp4ods8o8m4nsr54j...@4ax.com...
Allen Browne
data. You should not store the StockInHand value. Instead,
calculate the value when needed.
For Product 5, the quantity on hand is:
Sum("Quantity", "tblAcquisition", "ProductID = 5") -
Sum("Quantity", "tblInvoiceDetail", "ProductID = 5")
Most inventory databases involve periodic stock takes.
If so, you will have a tblStockTake with these fields:
StockTakeID AutoNumber Primary Key
StockTakeDate Date/Time Date of this stock take
ProductID Number (Long) Relates to tblProduct.ProductID
Quantity Number (Long) Quantity in hand at this date.
Get the most recent stock take date for a product like this:
DMax("StockTakeDate", "tblStockTake", "ProductID = 5")
Then only sum the acquisitions and sales since that date.
Here's a largely untested function that returns the quantity
on hand using this approach:
-------------------------------------------------------------
Function QuantityOnHand(lngProdID As Long) As Long
'Purpose: Return the quantity on hand for a product.
'Argument: The product to find quantity of.
'Return: Quantity. Zero on error.
'Method: Quantity at the last stock take, plus
' acqusitions less sales since then.
'Author: Allen Browne, abr...@odyssey.apana.org.au
Dim strWhere As String 'WHERE clause
Dim strSQL As String 'SQL statement
Dim strDate As String 'StockTake date
Dim db As Database 'CurrentDb()
Dim rs As Recordset 'Various recordsets.
Dim lngReturn As Long 'Return value (quantity)
Set db = CurrentDb()
'Get quantity and date of last stock take for product.
strWhere = "(ProductID = " & lngProdID & ")"
strSQL = "SELECT TOP 1 StockTakeDate, Quantity " & _
"FROM tblStockTake WHERE " & strWhere & _
" ORDER BY StockTakeDate DESC;"
Set rs = db.OpenRecordset(strSQL)
With rs
If .RecordCount > 0 Then
lngReturn = Nz(!Quantity, 0)
If Not IsNull(!StockTakeDate) Then
strDate = "#" & Format$(!StockTakeDate, _
"mm\/dd\/yyyy") & "#"
End If
End If
End With
rs.Close
' Add the quantity acquired since that date.
If Len(strDate) > 0 Then
strWhere = "(ProductID = " & lngProdID & _
") AND (AcquisitionDate > " & strDate & ")"
End If
lngReturn = lngReturn + Nz(DSum("Quantity", _
"tblAcquisition", strWhere), 0)
'Subtract the quantity sold since that date.
If Len(strDate) > 0 Then
strWhere = "(ProductID = " & lngProdID & _
") AND (InvoiceDate > " & strDate & ")"
End If
strSQL = "SELECT Sum(tblInvoiceDetail.Quantity) AS Sold " & _
"FROM tblInvoice INNER JOIN tblInvoiceDetail ON " & _
"tblInvoice.InvoiceID = tblInvoiceDetail.InvoiceID " & _
"WHERE " & strWhere & ";"
Set rs = db.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
lngReturn = lngReturn - Nz(rs!Sold, 0)
End If
rs.Close
QuantityOnHand = lngReturn
Set rs = Nothing
Set db = Nothing
End Function
-------------------------------------------------------------
HTH
lie...@hotmail.com wrote:
>
> I need to add inventory control to our Access 97 database, but I can't seem
> to find a reference as how to do it. The sample NorthWind database contains
> stock level, but looks like it needs to be edited manually?
>
> What I need to achieve is to record of the stock quantity for each product.
> Naturally I would need to be able to stock in and any order processed will
> deduct the quantity. I may also need to have something for quantity
> adjustment in case of brokage etc.
>
> I am not sure which way to achieve this. Should I treat Qty as a query
> result or should it be a separate field. If I treat it as a query result,
> then every time when I need to know the Qty in stock, it would have to sum
> up all the ins and outs. I am not quite sure how to add or subtract the Qty
> if I make it as a field? by VBA/Events?(I only know how to modify existing
> codes with common sense:))
>
> This could be a stupid question, but I have been thinking about this for a
> while and could not find any reference by searching news posting other than
> buying a commercial product.
--
Perth, Western Australia
Tips for MS Access users at:
http://odyssey.apana.org.au/~abrowne
Henry Craven
Before you even start you should decide how you will handle
( the status of ) things like the following.
Units in Store
Units on Display
Units in External Warehouse
Units Paid for and In Transit
Units on Demo/Loan
Units in Bond Store
Units Ordered In
Samples Received
Samples Given
Units Ordered Out
Units Invoiced
Units Packed
Units Shipped
Units Returned
Damages Units
Units Expired / Past Useby
Repaired Units
Units under O/S Warranty/Repair
Units Invoiced and Paid but not yet Shipped
Units In on Backorder
Units Out on Backorder
Units Stolen/Missing
Unit Age
Unit Cost (A whole other can of worms-OrderPrice,Landed etc)
Inventory Unit Cost (Real,Average,Weighted Average)
etc..etc..
When you've answered all of those, and possibly others specific to your
situation,
you'll know what, how and when to store, Increment/decrement, flag value and
calculate.
At it's simplest, if you just have orders & sales then link them to the
Stock Items table
by Item ID, and run an Aggregate Query to total Orders, Sales and Orders -
Sales.
But It's never that easy is it.
--
Henry Craven
-------------------------
H_Cr...@Bigpond.com
<lie...@hotmail.com> wrote in message
news:tp4ods8o8m4nsr54j...@4ax.com...
> I need to add inventory control to our Access 97 database, but I can't
seem
> to find a reference as how to do it. The sample NorthWind database
contains
> stock level, but looks like it needs to be edited manually?
>
> What I need to achieve is to record of the stock quantity for each
product.
> Naturally I would need to be able to stock in and any order processed will
> deduct the quantity. I may also need to have something for quantity
> adjustment in case of brokage etc.
>
> I am not sure which way to achieve this. Should I treat Qty as a query
> result or should it be a separate field. If I treat it as a query result,
> then every time when I need to know the Qty in stock, it would have to sum
> up all the ins and outs. I am not quite sure how to add or subtract the
Qty
> if I make it as a field? by VBA/Events?(I only know how to modify existing
> codes with common sense:))
>
> This could be a stupid question, but I have been thinking about this for a
> while and could not find any reference by searching news posting other
than
> buying a commercial product.
>
> --
> Lier Duh
Larry Linson
Access" from www.databasecreations.com? I believe it has an inventory
component. I've never looked at its source, but Cary Prague does have a
good reputation and, from his writings, obviously knows Access. You
might be able to purchase that and adapt its inventory control part to
your application. At worst, you will only spend a little time looking
at it if you discover that you can't use it.
Almost always, if there's something that can be bought that will do the
job, it's less expensive than creating somethine new and unique for
yourself (only to later discover that it's not so unique, after all).
--
L. M. (Larry) Linson
Access example databases at http://homestead.deja.com/user.accdevel
New: Book reviews, previously published in North Texas PC News
Script execution must be enabled and Windows set to Small Fonts
Sent via Deja.com http://www.deja.com/
Before you buy.
Arvin Meyer
Elaine wrote in message ...
>In order to see what an inventory control module ought to do for
>your business, ask your accountant (Chartered or Certified) to go
>over the accounting theory and the application as applies to your
>business. Take a deep breath, then reconsider whether this
>should be done outside your accounting program. More is involved
>than simply counting.
Even if the accounting program is used, it will never answer enough
questions to make it a good analysis tool. An inventory program needs to
answer even more questions than accounting can possibly begin to. You still
need at least a good inventory querying method that will access the data in
the accounting app.
>For example, I believe that a reply in this thread mistakenly
>asserted that you should avoid negative numbers. That is
>incorrect, from an accounting, and even from a business point of
>view. Hint: BACKORDER.
>
>There are timing issues, cost issues, accounting methods issues,
>etc. etc. Many programmers tell you never to store any
>calculated information. Well, that's absolutely incorrect from
>an accounting standpoint. For example, if your company received
>3000 of Widget B 65 days ago at a cost of .05 monetary units per
>and 3500 today at a cost of .045 monetary units per, what is your
>inventory valuation? What is your inventory valuation for that
>part, assuming a beginning value of 0, if today's physical count
>is 700? That answer would vary according to your inventory
>valuation method, a business (accounting) rule. You have to have
>the historic cost information, *and* the algorithm.
I don't think any competent database programmer has ever suggested that you
should "never* store historical information, in order to do the
calculations. Even the results of the calculations sometimes need to be
stored under circumstances where repeatedly calculating results in poor
performance.
Valuation of inventory is something which accounting systems handle very
poorly. Case in point: A cabinet shop orders 1000 board feet of lumber to
build an order. It uses 800 board feet. The accounting system would have you
believe that there is 200 board feet remaining. In fact, although the lumber
is there, and possibly useable for something, there is 0 board feet
remaining for similar jobs. That's because the remaining lumber will all be
the worst of the grade, and not more than 10 or 20 board feet will be
available unless a far higher labor amount is used to cut around the
additional defects. The remaining lumber may eventually be used, or may not.
Business rules set up in an inventory database can deal with this,
accounting systems do not.
>Also, I wouldn't worry about accounting separately for breakage &
>etc. unless your inventory consists of uniquely identifiable and
>expensive units, like jet plane engines, because the definition
>of units on hand is Beginning Inventory + Purchases - Ending
>Inventory. However it got not to be a part of the ending count
>is irrelevant (except for the kind of inventory noted above, or
>in case of casualty, like fire, earthquake, etc).
Here, I would disagree completely. Knowing what happens to your inventory is
a prerequisite to competent business management decisions. You certainly
need to know breakage levels to determine selling price. You need to know
what's missing, and even when it becomes missing, in order to determine
theft levels.
---
Arvin Meyer
>These are just some of the parameters.
>
>HTH,
>Elaine
>
>On Sat, 25 Mar 2000 12:15:54 +1100, lie...@hotmail.com managed
>to spit out:
>
>>I need to add inventory control to our Access 97 database, but I can't
seem
>>to find a reference as how to do it. The sample NorthWind database
contains
>>stock level, but looks like it needs to be edited manually?
>>
>>What I need to achieve is to record of the stock quantity for each
product.
>>Naturally I would need to be able to stock in and any order processed will
>>deduct the quantity. I may also need to have something for quantity
>>adjustment in case of brokage etc.
>>
>>I am not sure which way to achieve this. Should I treat Qty as a query
>>result or should it be a separate field. If I treat it as a query result,
>>then every time when I need to know the Qty in stock, it would have to sum
>>up all the ins and outs. I am not quite sure how to add or subtract the
Qty
>>if I make it as a field? by VBA/Events?(I only know how to modify existing
>>codes with common sense:))
>>
>>This could be a stupid question, but I have been thinking about this for a
>>while and could not find any reference by searching news posting other
than
>>buying a commercial product.
>
Elaine
The main purpose of what I was saying to the original poster is
this: Inventory Control is more complex than just adding and
subtracting the number of units coming and going. I'm sure we
both agree on that, and on the necessity for the programmer to
understand the business rules (accounting and managerial) as well
as the programming challenges.
Also, I am sure I might have written more completely in my
original posting, or not responded at all, but if everyone here
were to do that not much would be said.
Sorry, but I disagree with some of what you're saying, having set
up some higher-level accounting programs that were equipped to
handle all the information you suggest. Some of that information
is management information, not accounting information, but
anything having to do with $$$ *is* accounting information. And
most medium to higher level accounting programs allow for
non-accounting information and customized reporting.
Inventory valuation is, definitionally and exactly, an accounting
problem. Inventory write-down is why physical counting is
necessary to reconcile the $$$ with the ###. How often this is
performed is a decision based on accounting rules and operations
needs. And that reconciliation would value at zero those
mythical board feet. (Construction accounting is a specialized
area. Construction generally does not have inventory, but a
lumber yard might, and so might your cabinet shop, but a decent
job-costing module has wastage built-in *and* connects correctly
with inventory.)
Breakage control is a factor in job-costing, production
management, and other business areas, but not inventory
accounting, because the definition of inventory used is Beginning
Count + Purchases - Ending Count. (I incorrectly stated it below
as units on hand when I meant inventory depletion, sorry.)
Of course production is analyzed for breakage rates & etc. and
some specific businesses require breakage counts (anything like
breweries or other regulated industries and the individualized
units mentioned above), and all of that information is used to
make business decisions.
Accounting systems are designed to show what financial
transactions happened and when they happened, *and* financial
information is useful for providing in-depth business analysis.
For example, if ending inventory value is lower than expected,
given production and purchases (i.e. the cost of production has
unexpectedly risen), the item is red-flagged and management
investigates what happened. And higher than anticipated breakage
could be a reason.
A lot of managers do not have appropriate skills in numerical
analysis. That is why so many charts and spreadsheets are
generated to simplify data, and this increases managerial costs.
A significant amount of programming is done for this
consumer-group. That does not mean that accounting information
is inadequate. My old (CPA) boss said, "Spreadsheets [and by
extension, charts] are for people who need pictures to understand
numbers." I agree.
Respectfully,
Elaine
PS How do you like Florida?
On Sat, 25 Mar 2000 09:29:05 -0500, Arvin Meyer managed to spit
out:
Arvin Meyer
While I agree with most of what you are saying, I still have to disagree
with the way accounting programs deal with inventory. I think though, as I
re-read your post, you are equating inventory with financial evaluation,
while I am equating it with stock control. True, the more expensive
accounting apps are more sophisticated, but they all deal with inventory in
much the same way, so that they can conform to GAAP. In the case of the
construction industry, they are particularly deficient. Timberline
(certainly no low end program) is, by far, the "standard" accounting system
for the construction industry. It functions exactly as I mentioned in the
last post. I connect to it very frequently with the databases I write.
Another deficient area of accounting programs is BOM. BOM, itself is
extremely complex, and only the highest end systems even begin to deal with
it well.
Taking my earlier post as an example: From a financial point of view, I
would simply have written off the entire amount against the job. But my
inventory control app would have barcoded each remaining board as to size,
overall quality, cutting units (an industry measure), color, texture, grain,
and visual appearance. If this sounds excessive, an experienced lumber
grader can make all those decisions in less than 5 seconds. In fact, it
takes longer to record the info than grade it.
The biggest problem with accounting systems being used for management is
that they are after the fact. I also disagree with your "old boss" about
spreadsheets. True, they can be used graphically to map out after the fact
accounting data, but their real strength is analysis and scenario testing.
Not to say that their charting is for the weak minded either. I challenge
anyone to track 1000's of plot-points by looking at numbers, or even several
dozen when comparing 2 or more different factors.
My degree is in Accounting, but I quickly gave it up (although that was many
years ago) when I realized how poorly accounting deals with management
decisions. Accounting's strength is auditing and financial control,
certainly necessities, but all of it's attempts (even with high end
programs) at management fall well short of giving managers the tools they
need to make quick and competent non-financial decisions.
---
Arvin Meyer
PS: Florida is OK, but it's starting to get too hot for me (mid 80's) They
don't let me out much <g>, so I'll be fine as long as the air conditioning
and cold orange juice hold out.
LARS ÅM
---------
> >>>I need to add inventory control to our Access 97 database,
> >product.
> >>>Naturally I would need to be able to stock in and any order processed
will
> >>>deduct the quantity. I may also need to have something for quantity
> >>>adjustment in case of brokage etc.
> >>>
Different businesses have different needs for systems. The interface to the
accounting system might be completely manually. What you are discussing
seems to be how to set a complete business system and the relations between
goods movements and accounting. I think this must be more than this
newsgroup are intended to discuss.
By the way: Stock on hand can never be negative. Stock available can.
Lars
Please excuse if my English is rusty.
Elaine <perf...@alaska.net> skrev i
meldingsnyheter:VufcOGrgEpIsLL9Cgw2Fkr=W1...@4ax.com...
Elaine
I'm snipping most of the related thread, because it is lengthy.
Anyone else interested please look up this thread.
I do not think that we really disagree on anything material. I
suspect that I have seen different applications with a different
client base than you have seen, so I am biased in those
directions.
From a programmer's point of view, I think there are a view
considerations that have to be emphasized. First, it's all much
more complicated than it appears at first glance, especially for
those unfamiliar with accounting, *even if the inventory/stock
control module is not supposed to integrate directly with
accounting*. At some point the reports or whatever will go to
the financial people.
Also, my point about pictures to understand numbers is partially
this, and the programmer has to be aware of the grave pitfalls
here:
Many times managers do not have an understanding of numerical
analysis sufficient to their level of authority. It is important
to understand what information the data are supposed (in every
sense of the word) to convey to the intended audience when
designing reports, charts, or whatever.
For the last big project I did the data were very simple, but the
reporting tools requested were quite complex. There are only 17
report shells, but the reporting combinations available approach
400, there is an output to Excel form which allows the user to
choose any set or subset of the data and export it, and I built
some forecasting tools tailored for their business model.
The Q&A sessions took a pretty long time, even though I and the
client (1) each are skilled in numerical analysis and (2) had a
common vocabulary with which to begin.
A good management accountant understands how to use the financial
numbers to help manage the other numbers, and vice versa. A
programmer working in business must understand as much as
possible about the business and also, I think, about possible
numerical models for that kind of business. It is hard to keep
up with programming education, let alone anything else, but I do
think that basic courses in financial accounting, cost
accounting, and financial modeling would be very beneficial, if
not necessary for anyone intending to program in those areas.
Elaine
PS Florida would be too hot and humid for me, I think, even
though I'm living in the 'warm' part of Alaska. It rains so much
here, though, that I've been dreaming of Tucson. . . .
On Sat, 25 Mar 2000 19:34:37 -0500, Arvin Meyer managed to spit
out:
>Hi Elaine,
Elaine Nance
Arvin Meyer
Elaine wrote in message ...
>
>I'm snipping most of the related thread, because it is lengthy.
>Anyone else interested please look up this thread.
>
>I do not think that we really disagree on anything material. I
>suspect that I have seen different applications with a different
>client base than you have seen, so I am biased in those
>directions.
<SNIP>
That, true, as am I.
>A good management accountant understands how to use the financial
>numbers to help manage the other numbers, and vice versa. A
>programmer working in business must understand as much as
>possible about the business and also, I think, about possible
>numerical models for that kind of business. It is hard to keep
>up with programming education, let alone anything else, but I do
>think that basic courses in financial accounting, cost
>accounting, and financial modeling would be very beneficial, if
>not necessary for anyone intending to program in those areas.
Agreed, accounting has changed a great deal, I'm sure since my degree (5
years before the formulation of relational database theory)
>PS Florida would be too hot and humid for me, I think, even
>though I'm living in the 'warm' part of Alaska. It rains so much
>here, though, that I've been dreaming of Tucson. . . .
Tucson is way too hot for me (105 in the summer - but it is dry). Florida is
quite pleasant 6 months out of the year. But the other 6 months are high
80's to mid 90's with high humidity.
---
Arvin Meyer