Thank you Huybert. That is exactly what I did, even though I may not
have explained myself very well.

Todd.


--- In cbug2@yahoogroups.com, "Huybert Groenendaal" <huybert@...>
wrote:
>
> Hi Debra/Todd,
>
>
>
> From reading your email, I think you need a way to incorporate the
variation
> in the probabilities from year to year is to have one cell in your
> spreadsheet.
>
> A possible way of doing this is to have the Lognormal distribution
in one
> cell (say cell A1) and then have next to each 'loan' a Yes/No
distribution
> with a probability that refers to cell A1. This way, the
probability of
> default varies in each iteration and applies to every 'loan'.
>
>
>
> Good luck!
>
> Huybert
>
>
>
> From: cbug2@yahoogroups.com [mailto:cbug2@yahoogroups.com] On
Behalf Of
> Debra and Todd Miller
> Sent: Thursday, November 06, 2008 7:16 PM
> To: cbug2@yahoogroups.com
> Subject: Re: [CBUG] Using Yes-No Distribution combined with another
> distribution
>
>
>
> Huybert, you have hit on my problem, and even tried to address the
> second part of forecast model. I will explain here, and then again,
> below among your comments.
>
> Over the 20 prior years of data, the probability of default has
> averaged 0.39%, a median value 0.21% and a standard deviation
> of .32%. High/low values ranged from 1.21% to 0.14%. Crystal Ball
> distribution fitting tells says the best distrubution to model this
> pattern is a lognormal distribution with a mean of 0.00512, std dev
> of 0.01564, and location of 0.00132.
>
> This is the shape of the distribution my defaults (yes-no) have to
> take. How can I do this in crystal ball?
>
> A second part of the problem is there is a cost associated with a
> default, loss given a default. I have very good data on that part
of
> the problem. Once I can determine if a member of the popultation
> defaults, I can easily define assumptions and forecast the costs of
a
> failure. What I'm having difficultly with is the first part,
> determining if a specific member of the popultation defaults.
>
> > Hi Debra/Todd,
> >
> >
> > While it is not 100% clear from your email how exactly you did
it,
> I think
> > the approach you took during your first attempt is correct (but
> needs some
> > further refinement, see below) where the second approach is
> incorrect.
> >
> > The reason that the second approach is incorrect is that (I
> believe) you
> > applied the same 'loss' distributions (aka severity
distributions)
> to all
> > defaults. That would also be the reason that the standard
deviation
> using
> > that approach is much higher than what you have seen historically
>
> My second design attempt was still trying to get to the first part
of
> my problem, determining if a member of the population defaulted. I
> will try to explain better what I tried to do.
>
> Assume column A rows 10 through 100 contains my population. I have
> to determine which specific members of this population defaults in
a
> single simulation. In cell B1, I defined a lognormal assumption
> mentioned in the first paragraph.
>
> In rows B10-100 I placed a seperate yes/no assumption, but
> probability of default of default was an absolute reference to cell
> $B$1.
>
>
> My question was directly specifically at determined the default
> factor. The second part of my model deal with the severity
> distribution. I have good data on this and plan to define a
seperate
> dollar loss assumption for each member of the population. I plan to
> use an if then statement to determine is the loss is included in
the
> final forecast. (If yes/no result = 1, loss severity X unit size,
> esle 0).
>
> >
> >
> > With regards to your first approach, what is missing in your
> current model
> > (which is likely one of the reasons why your standard deviation
is
> too low
> > in that attempt) is that the probability of default likely varies
> from year
> > to year. It may not be the economy that is driving this
variation,
> but I
> > assume that the probability of 'default' wouldn't be constant
over
> time.
> > Therefore, I would review if the probability of default indeed
has
> varied
> > over time, and include this into the model. Including this year-
to-
> year
> > variability would increase the standard deviation of your total
> losses.
>
> This is exactly what I'm trying to model. I just need a line on the
> mechanics of how to do this in Crystal Ball.
>
> >
> > Hope this helps!
> >
> > Huybert
> >
> >
> >
> > From: cbug2@yahoogroups.com <mailto:cbug2%40yahoogroups.com>
> [mailto:cbug2@yahoogroups.com <mailto:cbug2%40yahoogroups.com> ] On
> Behalf Of
> > Debra and Todd Miller
> > Sent: Wednesday, November 05, 2008 10:40 PM
> > To: cbug2@yahoogroups.com <mailto:cbug2%40yahoogroups.com>
> > Subject: [CBUG] Using Yes-No Distribution combined with another
> distribution
> >
> >
> >
> > I'm a new user to Crystal Ball. By training I earned degrees in
> > accounting and information systems back in the mid 80's and my
> > statistical knowledge is just enough to make me somewhat
dangerous.
> >
> > I'm facing a a problem that is similar to predicting the
> probability
> > of default on loan, and then predicting potential costs given a
> > default. This is not a loan portfolio however, and the reasons
for
> > default vary from economic conditions, systemic issues, some
fraud
> > and some poor management decisions. Many years of study by my
> > employer has concludes that reasons for default are not tied to
any
> > specific pattern, so the history is what I have to build the
model
> on.
> >
> > I have a 20 year history of failure or default if you will.
Crystal
> > ball tells me that one part of the population is a lognormal
> > distribution and one part is Max Extreme distribution. At first,
I
> > attempted to model the default assumption with a yes/no
> distribution
> > on the entire 8,500 population. The results around the mean are
> > okay, but the standard deviation of the defaults only end up
being
> > about half of the historical standard deviation. Common sense
tells
> > me this does not help in predicting potential costs.
> >
> > For my second attempt, I defined a single cell assumption using
> > Crystal ball's recommendation for modeling the default
assumption.
> I
> > then used this cell as the probability of yes in a Yes-No
> > distribution applied to each individual member of the entire
> > population. Once again the results around the mean appear
> > reasonable, but the forecasted standard deviations are
> significantly
> > higher than the historical data. (I'm only running 1,000
> simulations
> > on a 1,000 population sample during testing before I build the
full
> > model). I've ran 4 tests on this subset of data.
> >
> > Aside from the higher than expected deviation, forecast results
out
> > to the 98th percentile appear plausible, with one extreme flyer
in
> > each of the 4 1,000 simulation tests I ran.
> >
> > Before proceeding further, is what I'm doing appropriate, or is
> > mixing the two distributions statistically invalid/potentially
> > dangerous. I'm somewhat troubled by the fact that standard
> deviation
> > in the forecasts are much higher (in one case double) than the
> > historical data.
> >
> > Is there a better way to handle my problem with-in Crystal Ball?
> >
> >
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>
>
>
>
>
> [Non-text portions of this message have been removed]
>