How can we use VBA/Macros Excel for solving this interesting simulation question?
Imagine that we have 100 pens in our box (each numbered from 1 to 100). We mix them and then randomly draw ten pens and put them aside. Afterwards, we put ten new pens numbered from 101-110 into the box, and then again randomly draw other ten pens and put them aside. We continue doing these draws for eight times.
Each time we keep putting new ten pens so that the total number of pens in the box remains equal to 100. For example, after second draw we put new ten pens numbered 111-120; mix the box and randomly choose ten pens and put them aside.
I think we need to have the following in excel.
Pen Number 1. 2. 3. 4. ... 100.
Randomly draw 10 out of 100, and put them aside. Record the number of drawn pens ( e.g. 4, 17, 74, 66, 91, 21, 33, 90, 55, 7). Then, put new ten pens numbered 101-110. Then repeat the process once more. Record the number of drawn pens (e.g. 15, 102, 87, 91, 109, 44, 22, 103, 92, 3). The repeat the process once more. Perform eight draws in total.
We want to know the following:
How many pens that originally were in the box (pens numbered from 1-100) remained in the box after above-mentioned eight draws? Since the draws are random we are going to have different results during each simulation of the process.
How many pens that were put into the box after second draw (pens numbered 101-110) remained in the box after eight draws? Since the draws are random we are going to have different results during each simulation of the process.
How many pens that were put into the box after third (pens numbered 111-120), fourth (pens numbered 121-130) ... eight (pens numbered 171-180) draw remained in the box after eight draws? Since the draws are random we are going to have different results during each simulation of the process.
If we can answer the above-mentioned questions, it means that we can also reveal the following:
How many pens that originally were in the box (pens numbered from 1-100) were drawn during eight draws?
How many pens that originally were in the box after second draw (pens numbered from 101-110) were drawn during eight draws?
How many pens that originally were in the box after third draw (pens numbered from 111-120), fourth (pens numbered 121-130) ... eight (pens numbered 171-180) were drawn during eight draws?
We want to create a VBA/Macros (or any other relevant) tool for performing this tasks in form of simulations. Let's say 1 million simulations and check the frequency distribution for share of pens (for instance numbered 1-100) that were drawn during eight draws and for those which remained in the box.
Can you please help with the solution? I tried to simulate it using VBA could not quite get what I wanted
