Well, I find a way of how to determine efficient frontier of optimal portfolios
minimizing "Conditional Value at Risk", with a little of help of my friends.  If
anybody is interested ask me how.  Bye


--- In cbug2@yahoogroups.com, "Huybert Groenendaal" <huybert@...> wrote:
>
> Hi Edgar,
>
>
>
> Thank you for your email and updates.
>
>
>
> In the previous email I outlined the general way (conceptually) how to go
> about building a model for a CVar efficient portfolio.
>
> To do this within CB without using VBA is however not very easy and I
> currently don't see how to do this.
>
> Therefore, I hope that maybe others on the list have ideas/suggestions.
>
>
>
> Best wishes,
>
> Huybert
>
>
>
> From: cbug2@yahoogroups.com [mailto:cbug2@yahoogroups.com] On Behalf Of
> eyunda
> Sent: Tuesday, November 03, 2009 10:09 PM
> To: cbug2@yahoogroups.com
> Subject: Re: [CBUG] Conditional Value at Risk as criteria for Optimal
> Allocation
>
>
>
>
>
> Huybert:
>
> I read your instructions and tried them but how to say to OptQuest that,
> what I want is a CVaR efficient portfolio...
>
> About your instructions:
>
> 1.- For your First Method: I applied:
>
> Define Forecast > Forecast Window > Fit a probability distribution to the
> forecast>
>
> But, how to calculate the "integral" of the forecast distribution?
> how to determine the left side of the percentile of interest? (it would
> require other simulation... how to obtain it "on the fly")
> and then, how I can say to OptQuest: I want the CVaR efficient portfolio
> defining the respective requirement to the objective function.
>
> 2. Your Second Method: "Extract all the values of the forecast distribution
> and take the average of all the values on the left size of the percentile of
> interest."
>
> Are you suggesting to extract the values of the forecast distribution, for
> example applying:
>
> =CB.GetForeDataFN(U8;1)
>
> many times (10000 times for 10000 trials)?.
>
> How to do it automatically in Excel? Do you suggest "loops"? This means to
> write Visual Basic code.. (really I would prefer only a Crystal Ball
> solution.. I have not programmed VB yet.. though I learned C, Fortran and
> Quick Basic in the University, you know).
>
> Finally I would really appreciate if you could prepare an easy example (a
> portfolio of assets) where Optquest delivers optimal portfolios and build a
> CVaR efficient frontier.
>
> On the other hand, let me insist that I would like a CB solution since I
> have already obtained CVaR efficient frontiers using other software.
> Consider that I have rebuilded the same results for the CVaR.XLS example of
> Zenios' book "Practical Financial Optimization" using GAMS, and also using
> "What'sBest 10.0", and even applying Uryasev's "Portfolio Safeguard" student
> version. But, I believe that Crystal Ball on the spreadsheet really is very
> useful and a practical tool, that it is worth to spend a little more of time
> to achieve these results, as many other people would appreciate this in due
> course.
>
> Yours faithfully,
>
> Edgar
>
> --- In cbug2@yahoogroups.com <mailto:cbug2%40yahoogroups.com> , "Huybert
> Groenendaal" <huybert@> wrote:
> >
> > Hi Edgar,
> >
> >
> >
> > Your question is an interesting and somewhat difficult one.
> >
> >
> >
> > For others on this list, CVaR, also known as `expected shortfall',
> basically
> > is mean of all the returns of the portfolio within a certain timeframe and
> > below (or above) a certain percentile. For example, the 1 year 1%-CVaR are
> > the expected losses of a portfolio in a year period if the portfolio
> returns
> > are below the 1% of the return distribution.
> >
> >
> >
> > In order to determine a portfolio in which you can use the CVaR to
> determine
> > an efficient frontier with Crystal Ball, you would have to determine the
> > CVaR of the portfolio each time OptQuest will come up with an alternative
> > (and potentially better) asset allocation of your "n" stocks. The
> difficult
> > part is to determine the CVaR "on the fly" for each new asset allocation.
> > There are a number of ways of doing this, including:
> >
> > 1. Fit a flexible distribution, ideally one that has a long tail, to
> > the forecast distribution of your return and calculate the integral of the
> > forecast distribution on the left side of the percentile of interest (in
> the
> > above example, below the 1%-ile;
> >
> > 2. Extract all the values of the forecast distribution and take the
> > average of all the values on the left size of the percentile of interest.
> >
> > I'd expect with the use of VBA the second approach can be implemented in
> CB
> > but I'd be interested to see if other members have alternative and maybe
> > more efficient approaches. In any of the approaches, it will be important
> to
> > run a lot of iterations since you are interested in the tails of the
> > forecast distribution.
> >
> >
> >
> > Best wishes,
> >
> > Huybert
> >
> >
> >
> > From: cbug2@yahoogroups.com <mailto:cbug2%40yahoogroups.com>
> [mailto:cbug2@yahoogroups.com <mailto:cbug2%40yahoogroups.com> ] On Behalf
> Of
> > eyunda
> > Sent: Monday, October 12, 2009 6:43 PM
> > To: cbug2@yahoogroups.com <mailto:cbug2%40yahoogroups.com>
> > Subject: [CBUG] Conditional Value at Risk as criteria for Optimal
> Allocation
> >
> >
> >
> >
> >
> > Please could you advise a Crystal Ball procedure for the following
> problem:
> >
> > Suppose that a portfolio contains "n" common stocks, and we are interested
> > in recommend the optimal investment.
> >
> > But taking into account that this problem has to be solved now at least,
> > with a coherent risk measure such as Conditional Value at Risk "CVaR" (it
> > would be even better to apply more measures or risk functionals such as
> > polyhedral risk measures as Eichhorn and Römisch of Humbolt Uni did)
> >
> > Then, what would be the Crystal Ball procedures to determine optimal
> > portfolios (efficient frontier) by minimizing CVaR for a fixed return or
> by
> > maximizing return for a given CVaR?
> >
> > Comments:
> >
> > Markowitz found optimal portfolios that could be located on an efficient
> > frontier determined by minimizing variance when the return is fixed, or by
> > maximizing return when the variance is given.
> >
> > Some year later, Value at Risk (VaR) appeared as Basilea requirement.
> >
> > However, neither mean-variance nor VaR are appropiate. In fact, Artzner
> and
> > then Römisch and Pflug, indicated that mean-variance and VaR are not
> > coherent risk measures, since diversification is not assured with them,
> even
> > more when you have return distributions with fat tails.
> >
> > (I am preparing my Master Thesis about "Stochastic Optimization of
> > Electricity Purchasing Contracts for Distribution Companies" in the
> National
> > Polytechnic School of Quito, Ecuador, South America, with the help of the
> > Math Department, and we are insisting that CB has to be useful to easily
> > solve this problem inside Excel. But anyway, we are also working in GAMS
> > (more difficult and programming codes) with CPLEX for MIP using scenario
> > trees reduction to manage incertainty and we have certainly obtained the
> > solution for this problem after some months of hard work)
> >
> > I would really appreciate your comments or even if you could prepare an
> > example of efficient frontier for optimal portfolios determined with CVaR,
> > in order to keep fighting until solving this problem, and then if we had a
> > worked and clear example, we could share it to other interested in this
> > subject.
> >
> > I am writing from my locally learned English, so please if you need more
> > explanations, please do not hesitate in writing me. Anyway, eternally
> > grateful.
> >
> > Yours faithfully,
> >
> > Edgar Yunda
> > eyunda@ <mailto:eyunda%40yahoo.es>
> >
> >
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>
>
>
>
>
> [Non-text portions of this message have been removed]
>

Wilfred . . . There are a couple ways to address your problem.  I assume you
really want to capture your forecasts in a way to represent a P10 reserves case,
P50 reserves case and a P90 reserves case (I call this a horizontal slicing)
rather than actually sampling the ranges for individual months (I call this
vertical slicing).  If you "vertically" slice the data you will end up with
reserves less than your P1 on one end and greater than P99 on the other end when
you add up your months.  Vertically slicing the data is good to understand the
potential range within a particular month, but not for representing a full life
of production.

For horizontal slicing, you can create a forecast for each month.  Your forecast
should be set up such that each month's production is dependent upon the prior
month forecast.  Build a table of your results and sort the data based on
reserves.  You will probably want to average a range of rows rather than use an
individual row of results as this will tend to filter out noise that you might
experience in using an individual row.  Pick your rows based on your P10, P50
and P90 reserve values.  While there may be some over simplification here for
the pure statistician, I think this will give you a valid representation of what
you are trying to present.  The variation for each month will be much smaller
using this horizontal approach rather than using a vertical approach.

I hope this makes sense.

Regards,
Steve Anderson
Senior Consultant
Decision Strategies
Westbridge One
10260 Westheimer, Suite 250
Houston, Texas 77042
281.857.6410 Office & Mobile | 281.857.6531 Fax
http://www.decisionstrategies.com

Decision Strategies - Confidence Through Clarity
________________________________
From: cbug2@yahoogroups.com [cbug2@yahoogroups.com] On Behalf Of Wilfred Harper
[wdrskh@...]
Sent: Monday, November 30, 2009 3:12 AM
To: cbug2@yahoogroups.com
Subject: [CBUG] Fw: Using Crystal Ball in Decline Curve Analysis for estimating
Oil and Gas reserves



I am resending my earlier mail since I did not receive an acknowledgement.
Please advise

Regards
wdrskh
--- On Thu, 11/12/09, Wilfred Harper
<wdrskh@...<mailto:wdrskh%40yahoo.com>> wrote:

From: Wilfred Harper <wdrskh@...<mailto:wdrskh%40yahoo.com>>
Subject: Using Crystal Ball in Decline Curve Analysis for estimating Oil and Gas
reserves
To: cbug2@yahoogroups.com<mailto:cbug2%40yahoogroups.com>
Date: Thursday, November 12, 2009, 2:11 AM

I am using Crystal Ball 2000 standard edition v 5.2 to do decline curve
analysis. This requires applying a decline factor by month to an initial
production rate over the life time of a well which can be 20 to 25 years. I need
to develop the trend by months over the lifetime of the well and export the P10,
P50 and P90 data (by months) for charting in Excel. How can this be done easily?
I have attached a simple spreadsheet as an example.

Hope to get a solution.
Regards
wdrskh

[Non-text portions of this message have been removed]





[Non-text portions of this message have been removed]

I am resending my earlier mail since I did not receive an acknowledgement.
Please advise
 
Regards
wdrskh
--- On Thu, 11/12/09, Wilfred Harper <wdrskh@...> wrote:


From: Wilfred Harper <wdrskh@...>
Subject: Using Crystal Ball in Decline Curve Analysis for estimating Oil and Gas
reserves
To: cbug2@yahoogroups.com
Date: Thursday, November 12, 2009, 2:11 AM







I am using Crystal Ball 2000 standard edition v 5.2 to do decline curve
analysis. This requires applying a decline factor by month to an initial
production rate over the life time of a well which can be 20 to 25 years. I need
to develop the trend by months over the lifetime of the well and export the P10,
P50 and P90 data (by months) for charting in Excel. How can this be done
easily? I have attached a simple spreadsheet as an example.
 
Hope to get a solution.
Regards
wdrskh





[Non-text portions of this message have been removed]

Hi - I am building some low-end models that contain formulae that I want to
protect and keep hidden.  the models are run with VBA that unprotects the
sheets, runs CB, extracts the results and then reprotects the sheets.

With CB7/11 I get an obnoxious notice when I attempt to save the file, telling
me to consider unprotecting the sheets before saving.  When I click OK it seems
to save fine.

Is there any way to suppress this notice screen?  This did not happen with older
versions of CB

thanks-
Jeff in Tennessee (hi, Pat and Bill)

Bill,



This isn't a direct answer to your question but might be of relevance.



I'm running off a Sun Ultra40 M2 multi-processor/multi-core machine with
20Gb Ram  under Win7 x64 with Excel 2010 x64 (beta) and the performance of
Excel x64 is stunning compared with the x86 version: It now runs appreciably
faster than even SAS JMP which I also use.



Presently I've had to stop using CB because it doesn't support Win7 x64
(does run though) and can't support Excel x64 but there seems every reason
to suppose that CB will gain a similar performance increment under x64
whenever it gets released in x64 form.



(In case anyone asks, I have tried installing theCB x86 image but though it
does run under Win7 x64 - but with page faults on termination - it will not
run under Excel x64 though the installation reports that it has installed
correctly.)



Best regards,

David



  <http://www.davidjromano.com/> cid:image001.gif@...

Phone: +44 (0) 2071 937843
Mobile: +44 (0) 7948 308868
Skype: David.J.Romano



  <http://www.linkedin.com/in/davidjromano> View David J. Romano's profile on
LinkedIn



www.davidjromano.com <http://www.davidjromano.com/>





[Non-text portions of this message have been removed]

Hello,

I am contemplating the specifications of a new laptop computer which is on
my "to buy" list.

Does anyone have any experience with running Crystal Ball on the new ""Quad
Core" processors now being offered either with or without a Windows 64 bit
operating system.

I am particularly interested to see whether these new processors offer a
significant  increase in computing speed when running large CB models with
Excel.

Looking forward to your responses.

Thanks,

Bill Rapp



[Non-text portions of this message have been removed]

Hi,

I've just upgraded my computer to Win 7 Ultimate x64 with Office 2010 x64 (beta)
on a Sun Ultra 40 M2 with 20Gb of RAM (.NET 3.5 appears to be native to Win 7
and I also have .NET 4.2 (beta) installed - filename dotNetFx40_Full_x86_x64).

Has anyone tried, or better still, succeeded in getting CB to run under Win 7
with Excel 2010 x64? (It seems fine under Win 7 with Excel 2007 (x32) and I
havent tried Excel 2010 x86).

Any help would be appreciated, thanks, david

Hi Dave,
I understood the problem you are seeing. The reason is that, our excel
CB functions are not tied to the seed you specify in the run options.
The seed is only used if you are using CB assumptions. The excel
functions assume new seed every time. You can test that if you extract
the trial values for the assumption in the cell D7. For successive runs,
the trial values obtained from this assumption remains same (if the same
seed is used), but the trials obtained from Excel functions are different.
Thanks.
-Samik

On 11/10/2009 11:07 AM, Dave Winiarski wrote:
>
>
> Just to continue... the file that I posted shows three cells with a
> custom distribution defined as an excel function in the testing tab,
> cells b4, c4, d4, all pointing to the same data.
> Forecasts cells reflecting this data is in b5, c5, d5. In addition
> there is an assumption cell d7 which also points to the same data.
>
> The issue is that anytime you go into the cells b4, c4, or d4 and hit
> a return, you effectively redefine the distribution I think. This
> could also be done using a macro.
>
> You reset the simulation, which appears to reset the distribution
> sequence in b4, c4, and d4 as well. However when you run, the final
> sequence that you get is dependent on which cell you last hit enter
> in. Thus it appears like the sequence for the distribution will change
> if you are just working with the spreadsheet but not changing
> anything. You can see this in the data tabs shown with the workbook.
>
> Ideally excel/CB would have figured out these distributions without
> regard to the order in which the cells with the distributions were
> last accessed.
>
> --- In cbug2@yahoogroups.com <mailto:cbug2%40yahoogroups.com>, "Dave
> Winiarski" <david.winiarski@...> wrote:
> >
> > Posted an example. (VolatileCustomDistributions.xls) I may be using
> the terminology incorrectly. Basically, what I noted was the order in
> which you define the custom distributions in a CB spreadsheet seems to
> result in a different set of values being selected for the forecast.
> The problem was even starting with the same seed value, we were
> getting different sequences of results. However we were using a macro
> to redefine the custom distributions to point to different cells in
> one case, and back to the original distribution in another case. In
> going back and forth between the options, we were losing any
> consistency in the series of values selected.
> >
> > In the simple example I attached, which all point to the same
> distribution, you can see that the sequence of results moves from one
> forecast to the other forecast just dependent on which was last
> "created" (by going into the defined assumption cell b4, c4, d4 and
> hitting enter)
> >
> > This may simply be a known "feature" of how CB works internally.
> However if so, a workaround will be needed.
> >
> > d
> >
> > --- In cbug2@yahoogroups.com <mailto:cbug2%40yahoogroups.com>, Samik
> Raychaudhuri <samikr@> wrote:
> > >
> > > How are you defining your "volatile" custom distribution? Can you
> post a
> > > small example?
> > > -Samik
> > >
> > > On 11/9/2009 3:19 PM, Dave Winiarski wrote:
> > > >
> > > > Is there some rule of thumb for how one should employ "volatile"
> > > > custom distributions?
> > > >
> > > > I have a situation where the parameter range referred to in a
> custom
> > > > distribution is changing (the range of values used varies in
> terms of
> > > > values/probability as well as the number of values in the
> > > > distribution). Upon opening the spreadsheet, the user creates the
> > > > custom distribution on the fly.
> > > >
> > > > We can't seem to get a repeatable string of results even using a
> fixed
> > > > seed value (999). Looking at a small spreadsheet, example it looks
> > > > like simply redefining a custom distribution (going into the
> cell and
> > > > hitting return) commonly changes the results almost as if in
> memory it
> > > > put the newly defined distribution somewhere else in the
> sequence of
> > > > random values selected. Simply saving the spreadsheet and
> pulling it
> > > > up again (where the macro redefines the custom distribution with
> the
> > > > same values), appears to result in a new sequence of final CB
> results.
> > > >
> > > > Any suggestions of a way to avoid this other than recoding the
> > > > spreadsheet to use "fixed" vs "volatile" custom distributions?
> > > >
> > > >
> > >
> >
>
>

Thanks, David



To: cbug2@yahoogroups.com
From: djb@...
Date: Fri, 13 Nov 2009 14:41:06 +0000
Subject: [CBUG] Re: Crystal Ball 2000 in Excel 2007





Jim,

Only Crystal Ball 7.3 and later are supported with Excel 2007. You could run
into functional problems, including issues with the way that your Crystal Ball
data is stored in the Excel workbook, if you run with an older version of
Crystal Ball.

David Blankinship
Manager, Quality Assurance
Crystal Ball Application
Oracle

--- In cbug2@yahoogroups.com, Jim Marks <jim_marks@...> wrote:
>
>
> I am trying to run crystal ball 2000 in Excel 2007.
>
>
>
> When I install, I get a type mismatch error from Visual Basic.
>
>
>
> Has anyone encountered this? Does anyone know how to set up Crystal Ball in
Excel 2007?
>
>
>
> thanks in advance
>
>
>
> Jim
>
> __________________________________________________________
> Eligible CDN College & University students can upgrade to Windows 7 before Jan
3 for only $39.99. Upgrade now!
> http://go.microsoft.com/?linkid=9691819
>
> [Non-text portions of this message have been removed]
>





_________________________________________________________________
Windows Live: Friends get your Flickr, Yelp, and Digg updates when they e-mail
you.
http://go.microsoft.com/?linkid=9691817

[Non-text portions of this message have been removed]

Jim,

Only Crystal Ball 7.3 and later are supported with Excel 2007.  You could run
into functional problems, including issues with the way that your Crystal Ball
data is stored in the Excel workbook, if you run with an older version of
Crystal Ball.

David Blankinship
Manager, Quality Assurance
Crystal Ball Application
Oracle

--- In cbug2@yahoogroups.com, Jim Marks <jim_marks@...> wrote:
>
>
> I am trying to run crystal ball 2000 in Excel 2007.
>
>
>
> When I install, I get a type mismatch error from Visual Basic.
>
>
>
> Has anyone encountered this? Does anyone know how to set up Crystal Ball in
Excel 2007?
>
>
>
> thanks in advance
>
>
>
> Jim
>
> _________________________________________________________________
> Eligible CDN College & University students can upgrade to Windows 7 before Jan
3 for only $39.99. Upgrade now!
> http://go.microsoft.com/?linkid=9691819
>
> [Non-text portions of this message have been removed]
>

I agree with Pat.

I would use the CB Excel functions and be very careful on how I present the
results.

As
far as I know, there is no definition for 'percentile' for sets of
values that have to remain linked. By 'sets of values that have to
remain linked' I mean, e.g. each of the resulting production profiles
calculated for each trial, which should follow. As another possible
example (something I was looking at a couple of weeks ago) you can
consider the 'probabilistic IPRs'. I had a look at some results we
received and quickly built the example described in the attached
document to show that they were presented in a misleading way. I hope
that further illustrates Pat's point.

As a general rule I would
say: make sure all your calculations make physical / logical sense, and
don't report statistics of your results as if they represented a
physical / logical relationship.

Other than that, there's
still the question of ranking sets of values. My only asnwer to that is
define an indicator which reflects what you are looking for in your
sets of data. E.g. cumulative over the life of the well/field, average
rate for a certain period, time until a certain cumulative is
achieved... you name it.

I would be interested in hearing what
people are doing about this, and what people in other disciplines are
doing with similar problems.

Best regards,
Andres




[Non-text portions of this message have been removed]

I don't believe CB 5.2 had the "auto-extract statistics" feature that we now
have, so the only way I know of to do this would be:



1)      Turn every month's production into a Forecast cell

2)      Let's assume your production forecasts run downward in your
spreadsheet row by row, and your first month is in cell A3.  In B3, put
"=CB.GetForePercentFN(A3,10)".  This will pull out the P10 value of the
forecast in A3.  Likewise, in columns C3 and D3, you put the same formula,
but the second arguments are 50 and 90, respectively, to pull out the P50
and P90.

3)      Copy these down for the life of your production.



These cells will show errors until you run CB, after which you will have a
string of  P10 values in column B, the P50s in column C, and the P90s in
column D.



But be careful what you do with this data.  These curves will reflect the
P10 - P90 range you can expect in any given month, but you do NOT have a
one-in-ten chance of achieving or exceeding the string of P90 values!  In
other words, you cannot play connect-the-dots with these data points and get
anything meaningful.  There is no such thing as a "P90 production profile".
You can derive a "typical production profile associated with the P90
cumulative production scenario," but that is a very different beast (and
trickier to get) than the simple string of P90 values you'll have here.



Does this make sense?



Pat Leach



From: cbug2@yahoogroups.com [mailto:cbug2@yahoogroups.com] On Behalf Of
Wilfred Harper
Sent: Thursday, November 12, 2009 4:12 AM
To: cbug2@yahoogroups.com
Subject: [CBUG] Using Crystal Ball in Decline Curve Analysis for estimating
Oil and Gas reserves





I am using Crystal Ball 2000 standard edition v 5.2 to do decline curve
analysis. This requires applying a decline factor by month to an initial
production rate over the life time of a well which can be 20 to 25 years. I
need to develop the trend by months over the lifetime of the well and export
the P10, P50 and P90 data (by months) for charting in Excel. How can this be
done easily? I have attached a simple spreadsheet as an example.

Hope to get a solution.
Regards
wdrskh

[Non-text portions of this message have been removed]





[Non-text portions of this message have been removed]

I am trying to run crystal ball 2000 in Excel 2007.



When I install, I get a type mismatch error from Visual Basic.



Has anyone encountered this? Does anyone know how to set up Crystal Ball in
Excel 2007?



thanks in advance



Jim

_________________________________________________________________
Eligible CDN College & University students can upgrade to Windows 7 before Jan 3
for only $39.99. Upgrade now!
http://go.microsoft.com/?linkid=9691819

[Non-text portions of this message have been removed]

I am using Crystal Ball 2000 standard edition v 5.2 to do decline curve
analysis. This requires applying a decline factor by month to an initial
production rate over the life time of a well which can be 20 to 25 years. I need
to develop the trend by months over the lifetime of the well and export the P10,
P50 and P90 data (by months) for charting in Excel. How can this be done
easily? I have attached a simple spreadsheet as an example.
 
Hope to get a solution.
Regards
wdrskh




[Non-text portions of this message have been removed]

Just to continue... the file that I posted shows three cells with a custom
distribution defined as an excel function in the testing tab, cells b4, c4, d4,
all pointing to the same data.
Forecasts cells reflecting this data is in b5, c5, d5.  In addition there is an
assumption cell d7 which also points to the same data.

The issue is that anytime you go into the cells b4, c4, or d4 and hit a return,
you effectively redefine the distribution I think.  This could also be done
using a macro.

You reset the simulation, which appears to reset the distribution sequence in
b4, c4, and d4 as well.  However when you run, the final sequence that you get
is dependent on which cell you last hit enter in.  Thus it appears like the
sequence for the distribution will change if you are just working with the
spreadsheet but not changing anything.  You can see this in the data tabs shown
with the workbook.

Ideally excel/CB would have figured out these distributions without regard to
the order in which the cells with the distributions were last accessed.

--- In cbug2@yahoogroups.com, "Dave Winiarski" <david.winiarski@...> wrote:
>
> Posted an example.  (VolatileCustomDistributions.xls) I may be using the
terminology incorrectly.  Basically, what I noted was the order in which you
define the custom distributions in a CB spreadsheet seems to result in a
different set of values being selected for the forecast.  The problem was even
starting with the same seed value, we were getting different sequences of
results.  However we were using a macro to redefine the custom distributions to
point to different cells in one case, and back to the original distribution in
another case.  In going back and forth between the options, we were losing any
consistency in the series of values selected.
>
> In the simple example I attached, which all point to the same distribution,
you can see that the sequence of results moves from one forecast to the other
forecast just dependent on which was last "created" (by going into the defined
assumption cell b4, c4, d4 and hitting enter)
>
> This may simply be a known "feature" of how CB works internally.  However if
so, a workaround will be needed.
>
> d
>
> --- In cbug2@yahoogroups.com, Samik Raychaudhuri <samikr@> wrote:
> >
> > How are you defining your "volatile" custom distribution? Can you post a
> > small example?
> > -Samik
> >
> > On 11/9/2009 3:19 PM, Dave Winiarski wrote:
> > >
> > > Is there some rule of thumb for how one should employ "volatile"
> > > custom distributions?
> > >
> > > I have a situation where the parameter range referred to in a custom
> > > distribution is changing (the range of values used varies in terms of
> > > values/probability as well as the number of values in the
> > > distribution). Upon opening the spreadsheet, the user creates the
> > > custom distribution on the fly.
> > >
> > > We can't seem to get a repeatable string of results even using a fixed
> > > seed value (999). Looking at a small spreadsheet, example it looks
> > > like simply redefining a custom distribution (going into the cell and
> > > hitting return) commonly changes the results almost as if in memory it
> > > put the newly defined distribution somewhere else in the sequence of
> > > random values selected. Simply saving the spreadsheet and pulling it
> > > up again (where the macro redefines the custom distribution with the
> > > same values), appears to result in a new sequence of final CB results.
> > >
> > > Any suggestions of a way to avoid this other than recoding the
> > > spreadsheet to use "fixed" vs "volatile" custom distributions?
> > >
> > >
> >
>

Posted an example.  (VolatileCustomDistributions.xls) I may be using the
terminology incorrectly.  Basically, what I noted was the order in which you
define the custom distributions in a CB spreadsheet seems to result in a
different set of values being selected for the forecast.  The problem was even
starting with the same seed value, we were getting different sequences of
results.  However we were using a macro to redefine the custom distributions to
point to different cells in one case, and back to the original distribution in
another case.  In going back and forth between the options, we were losing any
consistency in the series of values selected.

In the simple example I attached, which all point to the same distribution, you
can see that the sequence of results moves from one forecast to the other
forecast just dependent on which was last "created" (by going into the defined
assumption cell b4, c4, d4 and hitting enter)

This may simply be a known "feature" of how CB works internally.  However if so,
a workaround will be needed.

d

--- In cbug2@yahoogroups.com, Samik Raychaudhuri <samikr@...> wrote:
>
> How are you defining your "volatile" custom distribution? Can you post a
> small example?
> -Samik
>
> On 11/9/2009 3:19 PM, Dave Winiarski wrote:
> >
> > Is there some rule of thumb for how one should employ "volatile"
> > custom distributions?
> >
> > I have a situation where the parameter range referred to in a custom
> > distribution is changing (the range of values used varies in terms of
> > values/probability as well as the number of values in the
> > distribution). Upon opening the spreadsheet, the user creates the
> > custom distribution on the fly.
> >
> > We can't seem to get a repeatable string of results even using a fixed
> > seed value (999). Looking at a small spreadsheet, example it looks
> > like simply redefining a custom distribution (going into the cell and
> > hitting return) commonly changes the results almost as if in memory it
> > put the newly defined distribution somewhere else in the sequence of
> > random values selected. Simply saving the spreadsheet and pulling it
> > up again (where the macro redefines the custom distribution with the
> > same values), appears to result in a new sequence of final CB results.
> >
> > Any suggestions of a way to avoid this other than recoding the
> > spreadsheet to use "fixed" vs "volatile" custom distributions?
> >
> >
>

How are you defining your "volatile" custom distribution? Can you post a
small example?
-Samik

On 11/9/2009 3:19 PM, Dave Winiarski wrote:
>
> Is there some rule of thumb for how one should employ "volatile"
> custom distributions?
>
> I have a situation where the parameter range referred to in a custom
> distribution is changing (the range of values used varies in terms of
> values/probability as well as the number of values in the
> distribution). Upon opening the spreadsheet, the user creates the
> custom distribution on the fly.
>
> We can't seem to get a repeatable string of results even using a fixed
> seed value (999). Looking at a small spreadsheet, example it looks
> like simply redefining a custom distribution (going into the cell and
> hitting return) commonly changes the results almost as if in memory it
> put the newly defined distribution somewhere else in the sequence of
> random values selected. Simply saving the spreadsheet and pulling it
> up again (where the macro redefines the custom distribution with the
> same values), appears to result in a new sequence of final CB results.
>
> Any suggestions of a way to avoid this other than recoding the
> spreadsheet to use "fixed" vs "volatile" custom distributions?
>
>

Is there some rule of thumb for how one should employ "volatile" custom
distributions?

I have a situation where the parameter range referred to in a custom
distribution is changing (the range of values used varies in terms of
values/probability as well as the number of values in the distribution).  Upon
opening the spreadsheet, the user creates the custom distribution on the fly.

We can't seem to get a repeatable string of results even using a fixed seed
value (999).  Looking at a small spreadsheet, example it looks like simply
redefining a custom distribution (going into the cell and hitting return)
commonly changes the results  almost as if in memory it put the newly defined
distribution somewhere else in the sequence of random values selected.  Simply
saving the spreadsheet and pulling it up again (where the macro redefines the
custom distribution with the same values), appears to result in a new sequence
of final CB results.

Any suggestions of a way to avoid this other than recoding the spreadsheet to
use "fixed" vs "volatile" custom distributions?