The Cutting Edge

Modeling Time-Series Forecasts with @RISK

etorkia@crystalballservices.com — Tue, 18 Oct 2011 20:06:00 GMT

Making decisions for the future is becoming harder and harder because of the ever increasing sources and rate of uncertainty that can impact the final outcome of a project or investment. Several tools have proven instrumental in assisting managers and decision makers tackle this: Time Series Forecasting, Judgmental Forecasting and Simulation.

This webinar is going to present these approaches and how they can be combined to improve both tactical and strategic decision making. We will also cover the role of analytics in the organization and how it has evolved over time to give participants strategies to mobilize analytics talent within the firm.

We will discuss these topics as well as present practical models and applications using @RISK.

Tags: Statistics,Crystal Ball,Simulation,analytics,@Risk,Monte-Carlo,Forecasting

The Need for Speed: A performance comparison of Crystal Ball, ModelRisk, @RISK and Risk Solver

etorkia@crystalballservices.com — Tue, 20 Sep 2011 19:24:00 GMT

A detailed comparison of the top Monte-Carlo Simulation Tools for Microsoft Excel

There are very few performance comparisons available when considering the acquisition of an Excel-based Monte Carlo solution. It is with this in mind and a bit of intellectual curiosity that we decided to evaluate Oracle Crystal Ball, Palisade @Risk, Vose ModelRisk and Frontline Risk Solver in terms of speed, accuracy and precision. We ran over 20 individual tests and 64 million trials to prepare comprehensive comparison of the top Monte-Carlo Tools.

Tags: analytics,Statistics,Crystal Ball,Simulation,Simulation ShowDown,@Risk,ModelRisk

Excel Simulation Show-Down Part 3: Correlating Distributions

etorkia@crystalballservices.com — Fri, 19 Aug 2011 17:55:00 GMT

Modeling in Excel or with any other tool for that matter is defined as the visual and/or mathematical representation of a set of relationships. Correlation is about defining the strength of a relationship. Between a model and correlation analysis, we are able to come much closer in replicating the true behavior and potential outcomes of the problem / question we are analyzing. Correlation is the bread and butter of any serious analyst seeking to analyze risk or gain insight into the future.

Given that correlation has such a big impact on the answers and analysis we are conducting, it therefore makes a lot of sense to cover how to apply correlation in the various simulation tools. Correlation is also a key tenement of time series forecasting…but that is another story.

In this article, we are going to build a simple correlated returns model using our usual suspects (Oracle Crystal Ball, Palisade @RISK , Vose ModelRisk and RiskSolver). The objective of the correlated returns model is to take into account the relationship (correlation) of how the selected asset classes move together. Does asset B go up or down when asset A goes up – and by how much? At the end of the day, correlating variables ensures your model will behave correctly and within the realm of the possible.

Tags: Excel,Monte-Carlo,Simulation,ModelRisk,Solver,Distribution Fitting,Crystal Ball,@Risk,Simulation ShowDown,Correlation

Copulas Vs. Correlation

etorkia@crystalballservices.com — Fri, 17 Jun 2011 01:17:00 GMT

Copulas and Rank Order Correlation are two ways to model and/or explain the dependence between 2 or more variables. Historically used in biology and epidemiology, copulas have gained acceptance and prominence in the financial services sector.

In this article we are going to untangle what correlation and copulas are and how they relate to each other. In order to prepare a summary overview, I had to read painfully dry material… but the results is a practical guide to understanding copulas and when you should consider them. I lay no claim to being a stats expert or mathematician… just a risk analysis professional. So my approach to this will be pragmatic. Tools used for the article and demo models are Oracle Crystal Ball 11.1.2.1. and ModelRisk Industrial 4.0

Tags: Correlation,Monte-Carlo,Statistics,Simulation ShowDown

Excel Simulation Show-Down Part 2: Distribution Fitting

etorkia@crystalballservices.com — Sun, 15 May 2011 23:29:00 GMT

One of the cool things about professional Monte-Carlo Simulation tools is that they offer the ability to fit data. Fitting enables a modeler to condensate large data sets into representative distributions by estimating the parameters and shape of the data as well as suggest which distributions (using these estimated parameters) replicates the data set best.

Fitting data is a delicate and very math intensive process, especially when you get into larger data sets. As usual, the presence of automation has made us drop our guard on the seriousness of the process and the implications of a poorly executed fitting process/decision. The other consequence of automating distribution fitting is that the importance of sound judgment when validating and selecting fit recommendations (using the Goodness-of-fit statistics) is forsaken for blind trust in the results of a fitting tool.

Now that I have given you the caveat emptor regarding fitting, we are going to see how each tools offers the support for modelers to make the right decisions. For this reason, we have created a series of videos showing comparing how each tool is used to fit historical data to a model / spreadsheet. Our focus will be on :

The goal of this comparison is to see how each tool handles this critical modeling feature. We have not concerned ourselves with the relative precision of fitting engines because that would lead us down a rabbit hole very quickly – particularly when you want to be empirically fair.

Tags: Excel,Monte-Carlo,Simulation,ModelRisk,Solver,Distribution Fitting,Crystal Ball,@Risk,Simulation ShowDown

Excel Simulation Show-Down: Comparing the top Monte-Carlo Simulation Tools

etorkia@crystalballservices.com — Sat, 07 May 2011 01:13:00 GMT

Over the last 3 months, we have seen 3 of the 4 major players in the Excel Monte-Carlo Simulation arena introduce new releases. We hear a lot of talk about which tool is best and the truth is there is no perfect answer – it’s a personal thing dictated by user skill, preference and need.

For this reason, we have created a series of videos showing comparing how each tool is used to apply Monte-Carlo simulation to a model / spreadsheet. Our focus will be on :

To keep the playing field level, we have used a simple additive model, which is simply defining a series of distributions (i.e. costs, budget items…), summing them up and analyzing the resulting sensitivity analysis. We have kept things simple, so we are not correlating any of the variables nor using any fancy math.

As you will see, there are definite differences AND similarities regarding how these packages tackle building a model. We are going to focus on those relating to inserting and copying input distributions as well as defining and analyzing model outputs. The objective is to compare the ease, usability and efficiency of each tool and give people the opportunity to choose for themselves which tool reflects their needs and preferences better.

Tags: Excel,Monte-Carlo,Simulation,ModelRisk,Solver,Crystal Ball,@Risk,Simulation ShowDown

The Virtual Organization and Information Technology (Part 5/5)

etorkia@crystalballservices.com — Sun, 06 Mar 2011 21:38:00 GMT

Organizations seeking to develop a virtual business model must also be in a position to effectively implement it on a business level and on a technological level. (Venkatraman, 1994; Venkatraman & Henderson, 1993,1998).

One of today’s hottest IT topics is how to cheaply and effectively inter-connect processes. Collaboration emerged out of the relative cheapness and ubiquity of Internet technologies. Champy (2002) states ”E-business is a natural reaction to today’s competitive environment”[i]. But e-business means a lot of things to a lot of people. In current literature, e-business has taken on several definitions over time i.e.:

· Strategic approach

· A set of enabling technologies (Porter, 2001),

Since technology is a critical success factor to any virtual organizing strategy, the analysis of e-business is interesting due to its business focus and its ability to flexibly and rapidly support changing business needs and requirements. In essence, e-business is a composite of the above-mentioned perspectives and whose definition can be used inter-changeably with virtual organizing because of its open technologies and collaborative strategies.

Tags: Collaboration,Strategy,Management,EPM

The Virtual Organization and Processes (Part 4/5)

etorkia@crystalballservices.com — Wed, 02 Feb 2011 23:26:00 GMT

How should you look at processes when designing a virtual organization?

In order to effectively manage activities across organizations' it becomes critical to have certain processes in place to manage as well as measure performance and quality (Hammer, 2001; Champy, 2002; P-CMM v2.00, July 2001). According to Venkatraman (1994), organizations seeking to effectively integrate with business partners must first get their house in order through the use of BPR – Business Process Redesign/Re-engineering. Hammer (2001) says that for those who have re-engineered their internal business processes and extracted most of the value available internally, must now look at integrating and re-engineering externally to yield the next gains in value and profitability.

Tags: Collaboration,Processes,Strategy,Management

The Virtual Organization and Strategies (Part 3/5)

etorkia@crystalballservices.com — Wed, 02 Feb 2011 23:15:00 GMT

This article highlights and discusses Venkatraman and Henderson's take on the various vectors of Virtual Strategy.

What is a virtual strategy? What does it cover within the company? Who is affected? Who benefits? Venkatraman & Henderson (1998) reflect these questions in a 3 vector and 3 stages model (presented in Figure 5) that is aligned in spirit with Chesbrough & Teece's view that no one formally defined structure will ensure the success of a virtual organization.

Tags: Strategy,Collaboration,Management

The virtual organization – Competencies and Resources (Part 2/5)

etorkia@crystalballservices.com — Wed, 02 Feb 2011 23:07:00 GMT

This article covers the competencies, resources and assets that must be developped when making the transition to virtual organizing

A variety of authors have generated lists of firm capabilities and resources that may enable firms to conceive of and implement value-creating strategies (Barney, 1991; Bharadwaj, 2000).

In order to better define Venkatraman & Henderson's virtual organization, we have broken it down into components that are inspired by Barneys' (1991) three generic resource types and Nitin Nohria's (2003) model of organizational competencies; competencies that have characterized firms who performed exceptionally and developed or maintained a leadership position over the last 10 years. Nohria also suggests that to achieve overall success you need to excel in all 4 primary competencies and at least 2 of the secondary (See Figure 3). However Nohria emphasizes execution as primary means of success and differentiation.

Tags: Collaboration,Strategy,Management

Defining the Virtual Organization (Part 1/5)

etorkia@crystalballservices.com — Wed, 02 Feb 2011 20:41:00 GMT

Traditional definitions of the Virtual Organization have mostly taken a commodity-based, view of the interactions among partners (Kanter, 1994; Chesborough & Teece, 1996). One of the most notable examples of this type of virtual strategy to produce and deliver a product is the IBM PC. The early success of this venture was based on the same principals as those presented by Chesbrough & Teece's (1996) definition of a virtual organization:

Tags: Collaboration,Strategy,Management

Overview and objectives of collaboration

etorkia@crystalballservices.com — Wed, 02 Feb 2011 20:15:00 GMT

In this blog entry, we look at the ideas and forces shaping modern collaboration. The new success factors are presented as well as diffentent schools of thought regarding collaboration and alliances.

Tags: Collaboration,Management,Strategy

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.

Tags: Excel VBA,Simulation,Crystal Ball

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:00 GMT

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

Tags: Excel VBA,Simulation,project management,Risk Analysis

Oracle Crystal Ball’s new features in 11.1.2

etorkia@crystalballservices.com — Fri, 11 Jun 2010 01:19:00 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.

Tags: Crystal Ball,EPM

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.

Tags: Crystal Ball,EPM,Collaboration,analytics,Risk Analysis

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

Tags: Risk Analysis,EPM,Excel

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.

Tags: Optimization,Simulation,analytics,Risk Analysis

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.

Tags: analytics,Risk Analysis,Monte-Carlo,Statistics

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!

Tags: Crystal Ball,Excel VBA,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.

Tags: Collaboration,Strategy,Management