M
MACRE0
Greater Detail still about my quandary.
Based upon history I have the percentage of time Products A-Z were
purchased both by individual client and as a whole. I am seeking to
better the inventory levels by reducing down inventories but still
having this amount be ample enough to accommodate 95% of the future
purchases. I intend to see if it works by performing the independent
random selections of 6 items from Products A-Z. This will be done
10,000 times and stacked atop on another. At the bottom I need to
verify that the percentage selected is near the overall historical
average as a check. The sum of all the times the selection process
fails because of a dearth of inventory will be computed. If this
number is less than 5% (100-95%) then the inventory is sufficient. (I
will manually adjust the levels - not to worry).
If someone has a way that an item can be randomly selected out of a
group, which when done enough times will approximate the historic
statistical likelihood, I would be most appreciative.
Otherwise perhaps someone has an idea to improve my method. I have
written the items Product A say 500 times based upon its historic
average of 5% (ie 10,000 * 5% = 500). The same was done for Products
B-Z for a total of 10,000 items. In the adjacent column I put =Rand()
all the way down. At the bottom I have
=vlookup(Max(b1:b10000),a1:b10000,2,0) to give the corresponding
Product to that high random value. I did this for another 5 columns
(for a total of 6 - which is my average product use and later I intend
to allow this number to vary based upon its historical stats) I value
paste those on the side and created a macro to do this 10000 times.
Then I have a function at the bottom of this table to look up each row
and determine the number of Product A are in the top 6 of the random
choices, product B-Z and so on down for all 60000. I have a sumif at
the bottom of this that determines the number of times the random
samples exceeded the inventory on hand. Those are then summed to see
if there is a 95% future confidence that the are stuffiest products as
a whole.
I'll email anyone my xls sheet that needs this to better understand but
it is several MB's - so perhaps a smaller version. But I really would
like to know if someone has a method of selecting an item from a list
based on its likelihood of being selected so that my spreadsheet
doesn't keep crashing on me.
Based upon history I have the percentage of time Products A-Z were
purchased both by individual client and as a whole. I am seeking to
better the inventory levels by reducing down inventories but still
having this amount be ample enough to accommodate 95% of the future
purchases. I intend to see if it works by performing the independent
random selections of 6 items from Products A-Z. This will be done
10,000 times and stacked atop on another. At the bottom I need to
verify that the percentage selected is near the overall historical
average as a check. The sum of all the times the selection process
fails because of a dearth of inventory will be computed. If this
number is less than 5% (100-95%) then the inventory is sufficient. (I
will manually adjust the levels - not to worry).
If someone has a way that an item can be randomly selected out of a
group, which when done enough times will approximate the historic
statistical likelihood, I would be most appreciative.
Otherwise perhaps someone has an idea to improve my method. I have
written the items Product A say 500 times based upon its historic
average of 5% (ie 10,000 * 5% = 500). The same was done for Products
B-Z for a total of 10,000 items. In the adjacent column I put =Rand()
all the way down. At the bottom I have
=vlookup(Max(b1:b10000),a1:b10000,2,0) to give the corresponding
Product to that high random value. I did this for another 5 columns
(for a total of 6 - which is my average product use and later I intend
to allow this number to vary based upon its historical stats) I value
paste those on the side and created a macro to do this 10000 times.
Then I have a function at the bottom of this table to look up each row
and determine the number of Product A are in the top 6 of the random
choices, product B-Z and so on down for all 60000. I have a sumif at
the bottom of this that determines the number of times the random
samples exceeded the inventory on hand. Those are then summed to see
if there is a 95% future confidence that the are stuffiest products as
a whole.
I'll email anyone my xls sheet that needs this to better understand but
it is several MB's - so perhaps a smaller version. But I really would
like to know if someone has a method of selecting an item from a list
based on its likelihood of being selected so that my spreadsheet
doesn't keep crashing on me.