RAND in Data Tables: Don’t Use!
I’ve been wanting to explore the Monte Carlo simulations in Excel and I finally cracked open some books on the topic. I’ve probably been playing around with some examples since June or July and have seen a few techniques.
My understanding of a Monte Carlo simulation is that a scenario, a setup, a model is run hundreds, thousands of times to generate results and from those results determine some statistics about the set up. In running these simulations, there is something that is set up as some random factor that changes with each runs – it’s random and uncertain.
I’ve been plowing through a couple of examples and one of them stopped me.
By the way, the book I was following was written by a professor so I was sorting of accepting he knew what he was talking about and initially, did not really look at the numbers very closely. Until I looked at the last set of runs (in this example, 1000 simulations were done). The results were way big and stopped me.
Whoa, what were those numbers?
The example was really a simple set-up so the results should not have been so far off.
It’s a simple calculation of revenue and costs, with prices, cost rates and quantity, all fixed. The factor that changes is the exchange rate from A$ to US$: that is the random factor in the simulation. From there, of course, we can calculate profitability.
Note, the US $ revenue will be changing due to the exchange rate changing but the A$ revenue will remain fixed.
The following image shows the Data Table created.
The Data Table on the far left is the upper part of the table while the table on the right is the lower part of the table. Note there are 1000 rows of data in the table. Column F is the random exchange rate, created with the formula:
=NORM.INV(RAND(),0.92,0.02)
Column G is the resulting profitability associated with the random exchange rate.
I’m highlighting all the runs that had .93 as the exchange rate: the associated profitability ranged all over the place.
This is a simple profitability calculation so it shouldn’t come up with different answers for .93.
But once I dug into those numbers, I could explain those strange results.
Still, if I continue to scrutinize these numbers, I still sense something weird is going on. Exchange rates .93, .89, .94 produce profitability of around $126K.
And exchange rates .93 and .94 produced profitability of around $131K.
No, that doesn’t look right.
To verify the Data Table results, I developed a cross-check table where the exchange rates are hard coded in the table (no random RAND function) and the profitability is calculated using formula:
=($C$2*$C$4)/N4-($C$9*$C$2)
Now it’s really easy to eyeball where the numbers are weird in the data table. I highlighted various exchange rates at .92 to illustrate those way off of the calculations in the cross-check table.
Here’s another method of checking the numbers where I used XLOOKUPs to find the lowest and highest range of numbers for the exchange rate. Columns K and L has those XLOOKUP formulas.
For column K: =XLOOKUP($F4,$N$4:$N$22,$O$4:$O$22,”na”,-1)
For column L: =XLOOKUP($F4,$N$4:$N$22,$O$4:$O$22,”na”,1)
Any runs leading to profitability out of the lower and upper range is denoted with “x”. As you can see in the left, most of the runs were marked with “x”.
Hugely problematic.
After much experimenting and trial and error, I decided that at the present time, I cannot use the RAND function as the column or row input in the Data Table tool. It leads to incorrect results.
That doesn’t mean I can’t use the Monte Carlo simulation method; it is just the approach.
Here’s the full slide with a simple presentation of why one should always double check your spreadsheets or calculations, even if you are following an authority on the subject. This PDF viewer is working differently than the others; I think something changed again in WordPress and my usual PDF viewer didn’t work.
You must be logged in to post a comment.