Hi all,
I've been thinking about how to implement a database to assist some of my coworkers in running cycle counts. Currently, the inventory system we use does not perform this very well so we do it manually via excel sheets. This is incredibly time consuming/tedious. This database idea has been difficult for me to wrap my head around. Input/ideas is welcome!
Current process:
The inventory manager creates a schedule at the beginning of the year. A worksheet is generated in excel with the scheduled part numbers. It is split up into print sections. There are populated fields and blank fields to fill in. Someone is given the printed worksheet and fills in the blank fields. They return it to the manager and they enter the data into the excel sheet and compare to the stock quantities. If the count is off, they calculate the inventory dollar amount. If it is over a certain amount, the personnel will need to recount and record that data. Adjustment Qty is listed if needed.
Printed section
Populated fields: Part Number, Part Description, Pick Sequence, Unit Cost
Blank Fields: Lot# (could be multiple), Count Qty, Recount Qty (if needed)
Section Not printed
Populated fields: Count$, System Qty (quantity in stock), System$
Blank Fields: Adjustment Qty (+ or - value)
There are some curve balls to this.
The database would ideally be able to handle the following:
I need some brainstorming on how to accomplish all of this (if some of this is even possible/worth the trouble). Does anyone have any thoughts/opinions?
I've been thinking about how to implement a database to assist some of my coworkers in running cycle counts. Currently, the inventory system we use does not perform this very well so we do it manually via excel sheets. This is incredibly time consuming/tedious. This database idea has been difficult for me to wrap my head around. Input/ideas is welcome!
Current process:
The inventory manager creates a schedule at the beginning of the year. A worksheet is generated in excel with the scheduled part numbers. It is split up into print sections. There are populated fields and blank fields to fill in. Someone is given the printed worksheet and fills in the blank fields. They return it to the manager and they enter the data into the excel sheet and compare to the stock quantities. If the count is off, they calculate the inventory dollar amount. If it is over a certain amount, the personnel will need to recount and record that data. Adjustment Qty is listed if needed.
Printed section
Populated fields: Part Number, Part Description, Pick Sequence, Unit Cost
Blank Fields: Lot# (could be multiple), Count Qty, Recount Qty (if needed)
Section Not printed
Populated fields: Count$, System Qty (quantity in stock), System$
Blank Fields: Adjustment Qty (+ or - value)
There are some curve balls to this.
- We cycle count on a weekly basis.
- There are two departments that manage their own cycle counts.
- We use an ABC approach to cycle counting, meaning each part is evaluated for critical/cost/inventory dollars on a yearly basis.
- A = finished goods, $20,000 or greater inventory dollars. Need to be counted 4x a year.
- B = Between $10,000 and $19,999 inventory dollars. Need to be counted 2x a year.
- C = Less than $9,999 inventory dollars. Need to be counted 1x a year.
- We are regularly adding parts throughout the year. New parts are automatically entered into the system as 'B's.
The database would ideally be able to handle the following:
- List of part numbers/info - easy
- A way to import (excel report) stock quantity for the yearly category review/schedule creation and for the weekly cycle count
- For the yearly category review/schedule creation, ideally be able to assign cycle categories based on imported inventory dollars or whether or not the part is a raw material/sub assembly or finished goods. It would be favorable to have the ability to compare previous year vs suggested values and accept/deny the suggestions. Reasoning for this is perhaps we are backorded on a part in January when we run the report but know we have very large orders scheduled for the rest of the year.
- Save a historical record for cycle category changes.
- Generate a schedule for the two Locations.... basically determine how many counts need to be made in the year (A*4)+(B*2)+(C*1)/52 and then equally distribute random week number for when the part should be counted. Ideally keep historical data on this as well.
- The ability to track new parts added throughout the year and if necessary, manually add them to a cycle count. Sometimes its a few hundred parts... this year it was around 3k parts that were added throughout the year.
- Form that can query selected year/week number.
- Generate a cycle count report for for personnel to fill in.
- Save historical records for each cycle count.
I need some brainstorming on how to accomplish all of this (if some of this is even possible/worth the trouble). Does anyone have any thoughts/opinions?