zundappeiro
02-25-2010, 07:56 AM
Hi,
First of all let me say that i'm new in this forum and quite newbie in using vba macros.
I would like to have some advice in how to do what i projected (or if it feasible at all). The problem is as follows:
The company where i'm working gets each week from its client forecasts for each product we produce and send them, for the following 52 weeks. It happens that these forecasts (basically tables with the quantities expected to be bought from us, by article and week) vary a lot for the same product and same week, depending on the week. This means that, for example, the forecast we got this week may have a forecast of the expected quantity for the product x in the week y completely different from the same product x and same week y of the forecast that we got last week, and so forth.
What i would like to do was an analysis of this forecasts based on the historic of the forecasts received, let's say the last 25. This way i would have a basis of comparison and be able to point out the products for which the forecasts would vary less and vice versa, this way improving the information from which the production department would plan the production.
Now the real problem for me is in creating a macro that allows me to do it dynamically. In theory i thought that the macro should have the capacity to go and collect the data (the forecasts) of the last 25 excel files received. But this should be dynamic as next week the last 25 files will then be different (the first of this week will stop taking part as this week's enters the historic).
So what i don't really know is if it is possible to do this, and if so, how it is the best way to do it.
I hope it's not a too long thread for you to analyse and comment (and understandable!) but i would really appreciate whatever idea/help i could get.
Thanks in advance!
First of all let me say that i'm new in this forum and quite newbie in using vba macros.
I would like to have some advice in how to do what i projected (or if it feasible at all). The problem is as follows:
The company where i'm working gets each week from its client forecasts for each product we produce and send them, for the following 52 weeks. It happens that these forecasts (basically tables with the quantities expected to be bought from us, by article and week) vary a lot for the same product and same week, depending on the week. This means that, for example, the forecast we got this week may have a forecast of the expected quantity for the product x in the week y completely different from the same product x and same week y of the forecast that we got last week, and so forth.
What i would like to do was an analysis of this forecasts based on the historic of the forecasts received, let's say the last 25. This way i would have a basis of comparison and be able to point out the products for which the forecasts would vary less and vice versa, this way improving the information from which the production department would plan the production.
Now the real problem for me is in creating a macro that allows me to do it dynamically. In theory i thought that the macro should have the capacity to go and collect the data (the forecasts) of the last 25 excel files received. But this should be dynamic as next week the last 25 files will then be different (the first of this week will stop taking part as this week's enters the historic).
So what i don't really know is if it is possible to do this, and if so, how it is the best way to do it.
I hope it's not a too long thread for you to analyse and comment (and understandable!) but i would really appreciate whatever idea/help i could get.
Thanks in advance!