simple simulation in Excel possible
martin paulissen
J.E. McGimpsey
In article <01c0ee7b$f532d030$aa2a15ac@HKTGN9909301925>,
"martin paulissen" <m.w.pa...@kpn.com> wrote:
> can you do loops and select random variables
--
J.E. McGimpsey ar...@zptvzcfrl.pbz
ROT13 encoding, decode for real mail
martin paulissen
Computer recreation. And i wonder, just for fun, if you could program
things like a "random walk" or "the voting game" in Excel. It sounds like a
great way to enlarge my Excel skills.
J.E. McGimpsey <ar...@zptvzcfrl.pbz> schreef in artikel
<arjf-5252F6.0...@msnews.microsoft.com>...
Tom Ogilvy
Sub RandomWalk()
Randomize
lngrow = Int(Rnd() * 100 + 1)
lngCol = Int(Rnd() * 100 + 1)
On Error GoTo Halt
Do While True
Cells(lngrow, lngCol).Interior.ColorIndex = 5
Cells(lngrow, lngCol).Activate
lngrow = lngrow + 2 - Int(Rnd() * 3 + 1)
lngCol = lngCol + 2 - Int(Rnd() * 3 + 1)
Loop
Exit Sub
Halt:
End Sub
Regards,
Tom Ogilvy
"martin paulissen" <m.w.pa...@kpn.com> wrote in message
news:01c0ee87$b8828110$aa2a15ac@HKTGN9909301925...
martin paulissen
Tom Ogilvy <twog...@msn.com> schreef in artikel
<OCuwFyo7AHA.2008@tkmsftngp03>...
Michael R Middleton
>can you do loops and select random variables<
A common method for Monte Carlo simulation is to use Excel's Data Table
feature for replication (to do the "loops").
There's an example file, RandSamp.xls, on the "Decision Modeling Using
Excel" page of my university web site: www.usfca.edu/~middleton
- Mike Middleton
Norman Harker
Watch it with Monte Carlo Simulation. Most models assume independence of
variables. In many areas this is not the case and your MCS will give a false
impression of volatility.
Sees Ya!
"Michael R Middleton" <midd...@treeplan.com> wrote in message
news:OP1KbGq7AHA.1320@tkmsftngp05...
David J. Braden
The friendly warning to Mike makes me smile a bit. He has had deep training
in the area while getting his PhD at Stanford. From my experience in the
field, I don't think the assumption of probabilistic independance is any
more or less prevalent in Monte Carlo modeling than in other venues.
Regards,
Dave Braden
"Norman Harker" <nha...@ozemail.com.au> wrote in message
news:oCtT6.510$qJ4....@ozemail.com.au...
Norman Harker
Independence is often assumed without justification.
In real estate development analysis as an example, we might assume
independence of the key variables of interest charges, rental values /
capital values on completion and letting up / sale time. In practice an
increase in interest rates might impact significantly on the rental
affordable by industries hit by interest rate rises. Capitalization rates
may change adversely, there is a tendency to increased letting up and
selling times.
The assumption of independence means that on many runs of the simulation the
unlikely good and poor results are combined and thereby offset one another.
In practice, for example, it is unlikely that a fall in rental values will
be accompanied by an improvement in capitalisation rates. By ad-mixing of
good and poor results the impact is to produce a result closer to the mean
and to thereby reduce the standard deviation.
You can avoid this effect by using correlations in the simulation. Or you
can use scenario analysis with or without probabilities against scenarios.
The point to make though is that Monte Carlo simulation is only safe where
it can be objectively demonstrated that the variables are independent. In
many spheres of economics the objective evidence is for dependence of
variables.
Sees Ya!
"David J. Braden" <dbr...@rochester.rr.com> wrote in message
news:OU1l0Zr7AHA.1396@tkmsftngp03...
David J. Braden
You write that
> The point to make though is that Monte Carlo simulation is only safe where
> it can be objectively demonstrated that the variables are independent. In
> many spheres of economics the objective evidence is for dependence of
> variables.
I don't want anyone scared away from MC simulation by your claims.
In a certain sense, you have ruled out MC simulation altogether: no two
variables can be "objectively" demonstrated to have independence; at some
level everything is dependent, given how you define conditioning events
(existence of the universe comes to mind). In the spheres of economics I
work in, we are very careful to describe conditioning events, and to model
stochastic dependence where justifiable from a decision-making point of
view. Gibbs sampling is one important use of MC simulation that was
developed to better understand distributions of dependent variables; note:
MC simulation is used to develop distributions of dependent variables.
You don't have to stick with the rank-correlation approach taken in some
commercial packages; you can model the conditining, and assess distributions
on the conditioning events. Do it all the time, especially in your
industry.
Ah well,
have a great day.
Dave Braden
"Norman Harker" <nha...@ozemail.com.au> wrote in message
news:JLxT6.522$qJ4....@ozemail.com.au...
Norman Harker
I agree that it is extreme to rule out MCS and certainly independence is a
matter of degree.
To determine the range of possible outcomes is certainly a valuable exercise
but the problem is with the common interpretation that is made of the
results: recently demonstrated by a learned paper in one of our real estate
journals.
If there is significant dependence of variables in terms of similar
direction of impact on outcome, the ad-mixture of "poor view" and "good
view" variables, which are rarely like to arise in practice, will be to
cause such runs of the simulation to produce a result close to the mean.
Although range is unaffected, standard deviation isn't. It becomes distorted
by the number of "ad-mixtures" producing results close to the mean.
The classic example will be in analysing real estate investment values.
There is clear evidence that rapid increases in rental income are usually
associated with decreases in capitalization rates. With MCS we might in one
run produce a rapidly increasing rental value and a increase in
capitalization rate. This is a very unlikely event! The mean is unaffected
because other runs will produce rapidly falling rental values and highly
unlikely falls in capitalization rates. Since value is based on V = R/i,
both unusual results tend to produce a result close to the mean. In
aggregate the distribution is squeezed in towards the mean and thus produces
a lower standard deviation.
In fact the range remains pure because the range will be made up of
potentially valid good and poor results. The mass of unlikely results are
those comprised of highly unlikely mixes of good and poor variables.
Summary, a great tool for assessing range but potentially misleading for
assessing risk (i.e. standard deviation) where variables are correlated in
terms of impact upon outcome. Also the overall shape of the distribution is
potentially squeezed in towards the mean.
My preference is for a correlated MCS process which would tend to reduce
these rogue runs to levels more associated with practical possibility.
However, that is much more difficult to achieve and I remain seduced by the
advantages of scenario analysis and probabilistic scenario analysis.
Incidentally these are far easier to achieve than MCS. Generally we set
about 5 views with associated probabilities and set each variable based upon
those views. In property development cases the resulting standard deviation
can be a multiplier of 2 / 3 times the standard deviation found using MCS.
Sees Ya!
"David J. Braden" <dbr...@rochester.rr.com> wrote in message
news:uBfvLaz7AHA.408@tkmsftngp05...