The Cutting Edge

Using the CB.GetForeStatFN function in your Crystal Ball Models.

etorkia@crystalballservices.com — Wed, 25 Aug 2010 20:42:00 GMT

Crystal Ball utlizes several powerful functions and features to extract information and descriptive statistics. We are going to review these techniques and present the CB.GetForeStatFN in detail.

Remembering to pick right learning curve

etorkia@crystalballservices.com — Fri, 18 Jun 2010 00:19:00 GMT

This post presents 2 popular learning curve methods for estimating how a person or organization benefits from repeat learning.

This technique is key for the project risk analyst.

How to build fast business models in Excel for simulation and optimization

etorkia@crystalballservices.com — Sat, 12 Jun 2010 18:14:48 GMT

Overview of techniques to accelerate Excel Models and custom applications.... I have included a few fun user defined functions as examples.

Oracle Crystal Ball’s new features in 11.1.2

etorkia@crystalballservices.com — Fri, 11 Jun 2010 01:19:52 GMT

Oracle Crystal Ball has finally released its latest version of its industry leading Monte-Carlo tool. Essentially 3 features were added to the Fusion Edition.

Collaborative modeling using predictive analytics

etorkia@crystalballservices.com — Fri, 04 Jun 2010 19:49:00 GMT

When building models we are often confronted with assumptions that evolve over time. In most cases it is important to capture these changes to keep our model relevant. Over the last decade, Business Intelligence solutions have created a culture of self-service IS information. Given this democratization and decentralized access to data has created many opportunities and pitfalls for business analysts and decision-makers. We are going to outline some opportunities and pitfalls relating to shared modeling and a few strategies to get started.

This post presents the opportunities and challenges stemming from moving towards a distributed modeling paradigm in the organization. Also presented is a high-level integrated predictive/collaborative planning process.

Building a Risk Analysis Toolset

etorkia@crystalballservices.com — Sat, 29 May 2010 14:33:00 GMT

A brief review of Oracle and Microsoft database/application integration with Excel and Crystal Ball for comprehensive modeling

Optimization Models

etorkia@crystalballservices.com — Tue, 12 May 2009 21:58:00 GMT

Quick overview of the parts of an optimization model in MS Excel.

What is a model?

etorkia@crystalballservices.com — Wed, 18 Feb 2009 01:09:00 GMT

When using tools such as Excel, Crystal Ball or ModelRisk, it is very important to be able to translate a mental model to a mathematical one. Let me illustrate, when you think about your business, you often will think of abstract notions such as profit or margins. These are mental constructs because their are no physical representations of profit or margins (except a pile of cash) only mathematical ones.

Sequential Simulations using Crystal Ball VBA

etorkia@crystalballservices.com — Fri, 12 Dec 2008 19:10:00 GMT

If your objective is to feed one simulation with the results of another, VBA is the way to go. Working on a client assignment, we took the results from a first simulation to configure the parameters of the second. Of course VBA is not the only way, their is also the manual approach.

If all your simulations are in the same workbook, one of the first things that you need to manage is how you will isolate your numbers. For this reason I personally like working with straight values rather than formulas. In crystal ball you can auto-extract most parameters (e.g. mean, median, kurtosis, percentiles, etc.) from a forecast right into your worksheet.

Alternatively, you can use the CB.GetForeData, CB.GetForePercent, CB.GetForeStat formulas to get the information you need from the first simulation and use copy/paste special - values. In either case we are working with values.

What would happen if we used the formulas only? When we would run the second simulation, the parameters would change as the simulation would run, thus potentially skewing the results.

Ok, so what are the best practices to get this up and running?

Define range names for the cells that contain the number of Trials for both Sim 1 and Sim 2
Define range names for all the assumptions and forecasts data that you want to move around - both source and target ranges
Run your first simulation and copy the values to the range/worksheet containing the second simulation. Keep in mind that this step is what freezes your second simulations inputs. This you can code with VBA (as I did below) or you can copy and paste the values only before running your second simlation
The code below allows to set the trials in the worksheet itself without having to change the run preferences each time you want to run a simulation
Do not forget to set the visibility of the forecasts you want and those you do not as they will appear when you launch the simulation
Use form objects such as buttons to launch the code

Sub RunSimulation()
' Obtain inputs for Simulation 2 by running the simulation 1
'Set Trials for 1st sim
    Dim Trial
    Trials = Range("Trials_Sim1").Value
    MsgBox "Number of Trials for Simulation 1: " & Trials

'Setup Crystal Ball by resetting the simulation, setting Extreme Speed in the Run Prefs, run trials f while suppressing charts
    CB.ResetND
    CB.RunPrefsND cbRunMode, cbRunExtremeSpeed
    CB.Simulation Trials, , True, False, False, "Running Probabilistic Reserve Analysis", True

'Obtain final forecast by running the second simulation
    CB.ResetND
    copy_parameters
    Trials = Range("Trials_Sim2").Value
    MsgBox "Number of Trials for Simulation 2 Analysis: " & Trials
    CB.RunPrefsND cbRunMode, cbRunExtremeSpeed
    CB.Simulation Trials, , False, False, False, "Running Sim 2 Analysis", True

End Sub

Sub copy_parameters()
'This routine is to copy the probability parameters for the second simulation

Range("Sim1_Result_1").Copy
Application.Goto Reference:="Sim2_Input_1"
Range("Sim2_Input_1").Select
ActiveSheet.Paste
Range("Sim1_Result_2").Copy
Application.Goto Reference:="Sim1_Result_2"
Range("Sim2_Input_2").Select
ActiveSheet.Paste

End sub

Hopefully, with this script template you will be able to get a sequential set of sim,ulations going in no time.... if you have any questions on how to apply this in your projects, please don't hesitate to drop me a line at etorkia@crystalballservices.com

More ...

Leveraging the power of Crystal Ball using Excel VBA

etorkia@crystalballservices.com — Wed, 16 Jul 2008 16:44:00 GMT

This article provides a highlevel overview of how and why to integrate VBA in Crystal Ball spreadsheet models. Given that Oracle Crystal Ball runs on Excel, it is worth exploring how VBA can create behaviors that are NOT natural to Excel!

Processes, simulation and networks - Building meaningful analysis

etorkia@crystalballservices.com — Thu, 12 Jun 2008 19:58:00 GMT

For almost 15 years we have been witnessing a fundamental shift in how we do business, how we live and how we envision the world. Some have referred to this as a Paradigm Shift (Senge, 1991) brought on by cheaper and more accessible technologies (Ashkenas et al. 1995). As business people, we are constantly faced with solving problems and driving results, but that task is becoming more difficult because the lay of the land has changed and is going to continue to change – for everybody and every industry.

The fact that change and the unknown will have a deep impact on decision makers is not new, in fact Sun Tzu noted this almost 2000 years ago in the Art of War

“We are not fit to lead an army on the march unless we are familiar with the face of the country – its mountains and forests, its pitfalls and precipices, its marshes and swamps.”

Our challenge is to find new ways to understand and model our environment in ways that will provide useful and relevant insight. In this blog we are going to tackle how both Monte-Carlo Simulation/Optimization and Network Analysis can independently or jointly enhance our understanding as well as solve some of today’s toughest management challenges with relative ease.

Social and Organizational Network Analysis (S/ONA)

Over the last decade, we have seen how the internet has transformed our lives progressively and fundamentally. But the internet is not just a tool for collaborating in today’s dynamic environment; it is a model for it (Logan & Stoke, 2002). Marshal McLuhan summarized this idea almost 40 years ago in one simple caption “the Media is the Message”.

What does this mean for decision makers? It means that as the world becomes more inter-connected, understanding how networks form and operate becomes critical success factor in defining and executing relevant strategy and operations.

Examples of social and organizational networking are everywhere – from Facebook to Supply Chain Management, from Google to Cloud Computing, from Microsoft Messenger to Organization Wide Collaboration and Conferencing.

S/ONA has been used by some of the world leading firms to deal with HR (succession planning, compensation analysis, etc.), Knowledge Management as well as Strategic Analysis of suppliers and partners.

In this blog, we are going to explore these ideas and try to make them practical for everyday use.

Monte-Carlo Simulation and Optimization

Monte-Carlo simulation has been around since the 1940’s but has not been easy to apply until recently. Monte-Carlo simulation and optimization enables users to analyze problems in terms of ranges and distributions rather than single point i.e. sales are going to be between 10 and 12 million vs. an 11 million average. Most project managers, executives and analysts tend to model their ideas in terms of single of single point because they are much easier to model than ranges but when pushed to give an answer or a forecast – they will give you a range.

The more you incorporate variance-based variables (i.e. costs, time, effort, complexity, ect.) into your models (i.e. budgets, forecasts, production specs, etc. ), the more you realize that the potential outcome can vary greatly from initial targets.

“Initial cost and schedule estimates for major projects have invariably been over-optimistic. The risk that cost and schedule constraints will not be met and cannot be determined if cost and schedule estimates are given in terms of single points rather than distributions.”

Final Report of the USAF Academy
Risk Analysis Study Team, August 1971

Why is that important? Because through Monte-Carlo simulation and optimization it becomes possible and easier to:

Simulate Outcomes – By looking at 10,000 or even a 1,000,000 variations of the same situation is much more powerful than looking at 3 point estimates (i.e. Best Case, Worst Case, Most Likely)
Assess Risk: Using tools such as sensitivity analysis we can pinpoint what is driving the final answer
Improve Communication and Understanding: Using MC is very powerful to communicate the impact of certain decisions because it is very simple and visual.
Build and Maintain Credibility - Failures have a big impact on managers’ and executives careers. According to the Standish Group’s 2006 Chaos Report “46% of projects are either delivered late, overbudget or both!”
You Incorporate Accuracy - The ability to mathematically assign certainty to an answer will enable you to get the budgets they need from the outset – thus mitigating major gaps between projected and real costs. By providing better estimates for time and money, executives and managers demonstrate their ability to get things done right, on-time and on-budget.

In this column/blog we are going to cover these concepts and techniques as well as develop them further. We feel that both techniques are powerful on their own; however we would also like to explore applications where they overlap.

I hope that you will enjoy this column and I look forward to your ideas and feedback.

Eric Torkia
Executive Partner
Technology Partnerz Ltd - Crystal Ball Services Practice

More ...