This post will be my thoughts on writing a set of routines to deal with doing a physical inventory. This is quite simple but I think it would be good to write it down in long form as it usually helps me understand things much more deeply.
The first thing is, what is a physical? Most companies carry inventory and as they sell and buy goods, they are constantly updating inventory. Over the course of year however, you will likely lose things. Things are misplaced or accidentally sent in place of other things. This means that over time a company's inventory will fall out of sync with what is really physically there.
The solution to this problem is to do a count of all the inventory that you currently have. This means manually going to every product and tallying it up. This will give you the most accurate counts of what you currently have. This is called the physical count.
Now that you have counts, you can now enter in adjustments that will correct your inventory counts in the software system. There will be some way of creating and adjusting in transactions so that the inventory in the system matches what was counted.
Companies will often do this on an yearly basis but some industries do physicals more often. There are also physicals where instead of counting everything that the company has, you count specific areas and only update the products in those areas. The core idea of doing a physical is however always the same. You count up what you have and then update the database to match.
With that out of the way, now we can get to the programming.
The core idea is to have a file that contains all of your physicals. This file is a working area to store all of the counts and this file is something we will be reporting against. The reports will be a paper copy of what was counted and this is how we double check that both the counts make sense and that the counts were input into the system correctly. Once the report has been approved, we can use the counts to generate inventory transactions that will bring the system in sync with the physical.
The physical file will hold all the information we gather during a count. Usually a count will be done on paper with real tally marks. You can also do a physical where you scan barcodes and it will automatically update the physical file.
A physical will need contain some key pieces of information. You will need to track the date the physical was done, the product, the count of that product. Those are the required things, you will most likely want to keep track of warehouse, who did the count, who did the data entry into the system, and the unit of measure of the product.
You may also want to set an accounting date. This will be used when generating the transactions so that they can be dated to a specific date. This will require that your system is capable of finding out what the system thought a product's count was on any given date.
The account date field will let you do counts and then backdate them if the approval and going live process takes longer than a day.
You can also add more information that you want to track but these are the core fields you want to populate.
WAREHOUSE
DATE
PRODUCT
QUANTITY
ACCOUNTING.DATE
STOCK.TAKER
ENTERED.BY
Once you have the file created, the next step is to create the data entry routine.
The data entry routine will be a straightforward process. We simply need a way to update each of the fields in the table that we created.
This is also where we can add various checks. For example we can add a check to make sure the product is valid and check if the warehouse is a real warehouse, a work in progress warehouse or a customer warehouse.
This routine should let create a new entry in the PHYSICAL-FILE or update an existing entry. We use the term batch to reference the id of the PHYSICAL-FILE. The id is a sequence number that increments as you add entries, in other words a regular numeric primary key.
The entry routine should let you enter multiple batches, this way you can have multiple entries in the PHYSICAL-FILE that will get consolidated into one larger transaction. This is an additive process. If there 2 products in 2 batches, then the quantities will be added together.
This lets you have multiple people do the entry of the counts into the system without clobbering each other.
Once the data entry screen is done, the next step is write a routine to report on the entries.
This is going to be a listing of the information in the PHYSICAL-FILE entry. Here we can show the data in whatever is most helpful. The most important information that will be on the report are the products, the counted quantities and the quantities in the system. A grand total would also be helpful to do a sanity check.
Batch: 1 - 2
Product... Description..... Counted.... On Hand.... Batch....
ABC 3" x 5" Board 15.00 12.00 1
XYZ 10' Plank 23.00 52.00 2
Total: 38.00 64.00
The core idea of the report is that you can quickly scan it to see what the differences look like and if everything looks reasonable. Once this has been confirmed, we can now generate transactions that will bring the system numbers in sync with what was counted.
The report shows that we want to get a report of 2 batches. These could have been done and entered by 2 different people so they end up in 2 different entries in the PHYSICAL-FILE. However they are part of the same count so we want to report on them together. When we generate the transactions we will create transaction based on the consolidated data of all the batches we want to combine.
The final program that needs to be implemented for a physical count is the routine to adjust the inventory. In our case, we have an inventory register file that contains inventory transactions. Anytime inventory is created, used, sold or lost, this will create a transaction.
If we look at all the transactions for a product, this will give us the most accurate on hand information. It's also very useful to make this date sensitive so we can see what the on hand looks like on any given day.
Once the physical inventory report looks good, we can then generate transactions that will bring the system in line with what was counted.
The first step is ask the user for what batches to use to update the inventory. We then gather those batches and consolidate all the information. This way we have a single transaction that gets created for each product.
One thing to keep in mind here is to decide what to do with products that weren't counted. If a product wasn't counted, should it be left at whatever value was in the system or should it be marked as 0. This can be a prompt from the user as both cases may be desired depending on what is trying to be done.
Once we consolidate the batches, we compare the quantity with what is on hand. The difference will be the adjustment that we will create.
The final thing to do once you have the physicals applied that we won't get into here is that you need to now rebuild your allocations. Now that the inventory has changed, the inventory you have already allocated may need to change and so you need to trigger an allocation update.
This is the basic structure of programming and writing the routines to handle a physical. It is quite straightforward and this is a pattern that is repeated throughout most processes. A file is created to hold the data, a data entry routine is created that will update said file. We will then need to have reports run on the data that was entered. Once that data has been validated, we can then use that data to do things that will affect the global system.
With that we are done! Our inventory should now be truly accurate going forward. By doing physicals more often, you may see drifts happening and begin figuring out where the drifts are coming from. This is a useful thing to track and its an easy place to optimize.