Inventory Management Design

[dylcon]

Hello, I am currently working on a db that will manage less than 100 raw materials within inventory. It should be a relatively easy upper-level design, but I would like to confirm that I am heading down the right track.



DB ultimate functions:

• use of forms to input inventory adjustment, product info, customer info.
• usage of a "forecasting model" that will provide an alert saying that more product needs to be ordered
• generate a report that can display on-hand quantity for a part

Questions/concerns:

• how to develop a forecasting model based on a part's history of consumption and renewal rates.
  
  • should this be done in excel?

My current relationship setup is attached. If anyone has any input, I would greatly appreciate it, thanks.

[June7]

I expect Access can report out history of usage and averages over periods as well as on-hand balances. If your forecasting involves more analysis than that, Excel might be better tool.

[dylcon]

So if I were to use Excel, would that require that Excel is holding all of the data (this could be a lot over the years) and Access is just what the user will see? Or will the data be held within Access and it just calls a function for calculation from Excel?

[June7]

Data is in Access. Access can export to spreadsheet or Excel can connect to data in Access.

What is an example of a forecast calculation you would want to do?

[dylcon]

This is the part I am not 100% sure about as of now. Hopefully, I will be able to use the consumption rate and order dates to develop a simple slope and y-int that will be calculated from user input on Access forms. The reason I am using Access in the first place is to reduce humanistic error from incorrect entries.

So it would be something along the lines of having the user input every inventory adjustment (depletion, order made, date received) to have a dynamically adjustable slope and y-int that will allow for access to compare with on-hand quantities and send an alert when another order needs to be made.

Does this sound feasible? I am sure there are plenty of templates/already implemented dbs out there, but I haven't found anything that does this exactly.

[orange]

I think you are dealing with 2 separate concepts.
1 --Operational Inventory management database. (Access with Forms, queries, reports)
2 --Forecasting model using transactional data from database. (probably Excel using data extracts from database)

You may also want to research inventory management, reorder points, back order, FIFO...

Just my $.02
Good luck with your project.

[June7]

Most any calculation done in Excel can be programmed in Access. Don't guarantee same results. I have code in Access to do matrix multiplication. The results are not the same as in Excel. Even when I used Excel functions in Access code, the results were different than with those same functions in an Excel spreadsheet.

Access also has a graphing utility but is not as versatile as Excel and harder to configure.

I did not want an Excel component with my db and boss said the Access numbers were good enough so that's what we have.