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]