Hi
I'm looking to build a simple stock forecasting system, and NOT a complex stock management model, and need some help on the design. The volume of data excludes Excel - where my real expertise is.
Data
1.Products - circa 6000 (categorized into many groups)
2.Time period - 52 weeks.
3.Forecast Sales Demand (product by week)
4.Forecast purchases (product by week)
I'm looking to build a simple model that effectively calculates Closing stock (by product, by week):
ie Opening stock + purchases - sales = Closing stock.
Many of the examples I have seen state you should not store the stock quantities (as they are dependent variables), and instead calculate when required.
However, I'm not trying to build a stock management model, Im trying to build a forecasting model. The data is going to be relatively static, tweeked now and again. When tweaked, I plan to re-run the process to update the stock table.
I need a method of calculating available stock. What is your advice on creating such a model?
In Excel, I would use VBA to loop through the products, calculating the c/stk, (with week n+1 o/stk being Week n's c/stk). I'm not at all sure on how to do this in Access!
With limited experience of stock models & Access, I'm rather stuck! My current thinking:
For each product, and for each week.
Calculate: O/Stk + purchases - sales.
I'm ok with week 1 stock, as it will be the opening stock value. But how do you link week n+1 to week n's closing stock? (for each product, and each week)
Is there a better way of doing this?
As you can see, I need some Access Help!!!!!
Any help will be greatly appreciated.
Thx
I'm looking to build a simple stock forecasting system, and NOT a complex stock management model, and need some help on the design. The volume of data excludes Excel - where my real expertise is.
Data
1.Products - circa 6000 (categorized into many groups)
2.Time period - 52 weeks.
3.Forecast Sales Demand (product by week)
4.Forecast purchases (product by week)
I'm looking to build a simple model that effectively calculates Closing stock (by product, by week):
ie Opening stock + purchases - sales = Closing stock.
Many of the examples I have seen state you should not store the stock quantities (as they are dependent variables), and instead calculate when required.
However, I'm not trying to build a stock management model, Im trying to build a forecasting model. The data is going to be relatively static, tweeked now and again. When tweaked, I plan to re-run the process to update the stock table.
I need a method of calculating available stock. What is your advice on creating such a model?
In Excel, I would use VBA to loop through the products, calculating the c/stk, (with week n+1 o/stk being Week n's c/stk). I'm not at all sure on how to do this in Access!
With limited experience of stock models & Access, I'm rather stuck! My current thinking:
For each product, and for each week.
Calculate: O/Stk + purchases - sales.
I'm ok with week 1 stock, as it will be the opening stock value. But how do you link week n+1 to week n's closing stock? (for each product, and each week)
Is there a better way of doing this?
As you can see, I need some Access Help!!!!!
Any help will be greatly appreciated.
Thx
