Speed up a Monte Carlo in Excel

I am running a Monte Carlo in Excel. To represent the distribution of demand for each product SKU during each month, I am using the following formula over 500 times:

=IFERROR(BETA.INV(RAND(),(1+4*(D35-D31)/(D27-D31)),(1+4*(D27-D35)/(D27-D31)),D31,D27),0)

So, that's over 500 RAND() volatile functions at a time.

The output from each iteration of a formula is stored in a table. I have a VBA to handle that:

Sub QARLO_Products()
'runs on QARLO_P!, for forecasting product
    Range("Q7:OC100000").ClearContents
    For Iteration = 1 To Range("G1").Value
        Range("I1").Value = Range("G1").Value - Iteration
        'For QMIN Zinc-Bulk
        Cells(6 + Iteration, 17) = Iteration
        Cells(6 + Iteration, 18) = Cells(39, 4)
        Cells(6 + Iteration, 19) = Cells(39, 5)
        Cells(6 + Iteration, 20) = Cells(39, 6)
        Cells(6 + Iteration, 21) = Cells(39, 7)
        Cells(6 + Iteration, 22) = Cells(39, 8)
        Cells(6 + Iteration, 23) = Cells(39, 9)
        Cells(6 + Iteration, 24) = Cells(39, 10)
        Cells(6 + Iteration, 25) = Cells(39, 11)
        Cells(6 + Iteration, 26) = Cells(39, 12)
        Cells(6 + Iteration, 27) = Cells(39, 13)
        Cells(6 + Iteration, 28) = Cells(39, 14)
        Cells(6 + Iteration, 29) = Cells(39, 15)
        'For QMIN Zinc-Jugs
        Cells(6 + Iteration, 30) = Iteration
        Cells(6 + Iteration, 31) = Cells(40, 4)
.... blah, blah, blah and so on for all products....
        Cells(6 + Iteration, 444) = Cells(561, 14)
        Cells(6 + Iteration, 445) = Cells(561, 15)
   Next Iteration
End Sub

The left-hand side of these statements represent the table location for recording output data, the right-hand side are the outputs from the above mentioned formula.

I notice that this VBA runs through each and every line in series. Each line causes all 500 volatile RAND() functions to recalculate. One iteration of this VBA takes 30 seconds on all 8 cores of a Core i7/ 32GB RAM. I want to run 5,000 iterations on a regular basis.

Can you suggest methods to make this model run more efficiently?

• Make one cell a RAND() and then have all 500 reference it?
• Restructure the VBA?

I have done all the basic/general things to make Excel run more efficiently.