Uncertainty makes strategic planning complex. Removing, or even mitigating, uncertainty can create unlimited business value. However, most companies lack the strategic planning infrastructure to rise above the unknown.
My previous blog have illustrated how OLAP tools support management processes and provide key stakeholders with relevant, actionable insight for speed-of-thought analysis. Yet while understanding the past through historic data is necessary for sound decision-making, is it sufficient for forward-looking estimates? Making the right decisions requires you to anticipate and plan for possible changes in the future. A common approach to anticipate these changes is to first assess the company's current position by analyzing historical information to understand the company's past. Occasionally, however, there might be very little or no historical precedent. And even if you do have historical data, it might not extrapolate correctly into the future. For example, let's assume your company wanted to launch a new product or compete in a different market. Your company's past and present state can be a good indicator of future performance, but does not guarantee it. The linkage to making the right actionable decisions still requires additional analysis.
To anticipate possible changes in the future, you must start addressing questions about the future possible outcomes, specifically the following:
- Which outcomes are most likely?
- What are the key risk drivers for those outcomes?
To answer these questions and plan for the right decisions, you must first ascertain the potential range of future results and the probability of different outcomes actually occurring. This type of analysis generally tends to be a limited exercise, concentrating on three common scenarios: best case, worst case, and something in between. Typically worked up in a spreadsheet, these scenarios tend to flex one or two inputs (assumptions) at a time and provide little insight into probable outcomes or drivers, which in the real world; reflect the collective influence of many variables (good and bad) coming together at the same time. Powerful applications such as Planning can use OLAP data to generate and store multiple scenarios, but the process remains slow and manual.
Fortunately, solutions such as Crystal Ball extend traditional OLAP capabilities to help organizations overcome uncertainty and achieve results. Excel-based, Crystal Ball can be used as a stand-alone desktop tool or integrated with Oracle's OLAP technology.
Using the tools of predictive modeling, simulation, and optimization, Crystal Ball gives you insight into the critical factors affecting risk, and calculates the likelihood that you will reach your objectives under even the most uncertain conditions.
Crystal Ball and Monte Carlo Simulation Methods
Monte Carlo simulation, founded upon stochastic analysis, offers the ability to quickly create thousands of what-if scenarios. The ability of Monte Carlo modeling to reflect all outcomes—particularly those dismissed as too unlikely to consider— makes it an objective and vital resource for assessing risk and the effects of uncertainty. These results give the user not only a comprehensive, realistic picture of future possibilities, but also their associated probabilities.
As an example, a company attempting to calculate a certain metric, such as earnings per share for the upcoming quarter, would first input data for various parameters. It would then run an algorithm that uses these inputs to generate outputs of interest, including earnings per share. Monte Carlo simulation is crucial when the exact values for input parameters are unknown or uncertain.
Crystal Ball, using Monte Carlo methods, accommodates uncertainty by allowing the user to define ranges of values (probability distributions) for variable inputs. The program then chooses a value for each assumption according to the given range, and repeats this process as many times as needed—even into the thousands. The result is a rank-ordered list of outcomes from which statistics are calculated. Because values are chosen randomly and the process is repeated many times, the output is statistically significant—a major improvement over a handful of subjective scenarios. Figure 1 shows how the output of the simulation would be
FIGURE 1. Crystal Ball generates a full range of outcomes with associated probabilities.
thousands of values (scenarios) for earnings per share, from which the user could understand the likelihood of, for instance, meeting or exceeding some target value.
Crystal Ball, using Monte Carlo simulation, calculates the probability of achieving any given strategic goal and offers the ability to develop plans that reflect realistic risk and reward. Monte Carlo simulation helps decision makers think about investment and long-term planning, for instance, as a continuum of probabilities rather than a certainty. Therefore, they grow accustomed to responding to change, rather than hoping it will not happen.
Crystal Ball Analysis
Four steps are involved in a Crystal Ball analysis:
- Develop a model of the problem.
- Assign ranges of values to the inputs.
- Calculate the ranges/probabilities of the outputs.
- Analyze and share results.
This section uses the example of a healthcare provider estimating patient revenues to illustrate the workflow of building a Crystal Ball analysis.
Developing a Model of the Problem
Essbase and Excel are both excellent general-purpose, model-building platforms. In the context of integrating Crystal Ball with OLAP technology, we will use an example that takes advantage of the strengths of each tool. Sharing traditional spreadsheet models in an enterprise environment is difficult at best. Broken links, cryptic formulas, and inconsistent application of business rules are just a few of the problems. In Figure 2, an Essbase application has been created that defines the relationships (business rules) between inputs in order to calculate reimbursement revenue. Throughout our analysis, our model will remain consistent and secure.
Assigning Ranges of Values to the Inputs
The next step in the modeling process is to assign ranges of values to uncertain inputs. With Smart View, you can access data from a variety of Oracle products for OLAP, such as Essbase and Planning, and bring that data into Excel for further analysis. This OLAP data can be examined either independently using the forecasting tools of Crystal Ball or linked into existing Crystal Ball simulation and optimization models.
Figure 2. Essbase cube calculating net patient revenue based on a variety of inputs
In Figure 3, we have opened an ad hoc query of our Essbase application using Smart View.
We are now ready to apply Crystal Ball. We select a cell containing one of the uncertain inputs (Crystal Ball assumptions). For example, Figure 4 shows a selected cell representing the Occupancy % Input for January in the Forecast scenario. Clicking the Define Assumption button on the Crystal Ball ribbon opens a gallery of distributions from which to choose, as shown in Figure 5. From the gallery, you can select a distribution type and open a dialog box in which you can specify the parameters, as shown in Figure 6.
Crystal Ball offers a great deal of flexibility in assigning distributions, or ranges, to inputs. If there is sufficient historic data, the user can fit a probability distribution
Figure 3. Crystal Ball assumption created on top of a Smart View ad hoc query
Figure 4. Select a cell where uncertainty or variability exists.
Figure 5. Choose Define Assumption from the Crystal Ball ribbon.
Figure 6. Crystal Ball distribution gallery (left) and associated parameters dialog box (right)
to those values. Range definitions are also available for cases where limited data exist, or where expert opinion might be more appropriate.
In our example, the planner estimates a minimum (0.4), most likely (0.65), and maximum (0.75) value for Occupancy based on expert opinion, and enters these estimates as parameters for the Triangular distribution. Ranges are assigned for the rest of the uncertain inputs in the forecast scenario, and the outputs (Crystal Ball forecasts)—or values targeted for analysis—are identified. By default, assumptions are assigned a green background, and forecasts are assigned a blue background. In this case, we are interested in the Net Patient Revenue for each month as well as Qtr1. The model is now ready to run. Figure 7 shows the output.
Calculating the Ranges/Probabilities of the Outputs
Running the model generates 500 what-if scenarios (the number of scenarios can be defined by the user). For each trial, a new value within the defined range is selected for each assumption. These values are submitted back to Essbase, where the Essbase database is recalculated according to the business rules. New results for the outputs are then refreshed in Excel and stored by Crystal Ball for analysis. The histograms in Figures 8 and 9 represent the range and likelihood of the outcomes for net patient revenue. The higher the bar, the more outcomes were observed in the given range. The boxes at the bottom of the forecast chart can be used to evaluate the certainty of any range of values.
Figure 7. Crystal Ball model with outputs and uncertain variables defined
Figure 8. The likelihood of exceeding the baseline estimate for net revenue is only about 30%.
Figure 9. A value of $6.65 M provides 80% certainty—a more confident forecast!
Analyzing and Sharing Results
The chart in Figure 8 shows the likelihood of exceeding the baseline estimate of $7.28 million, which is only about 30 percent. We would like to be more confident in our forecast. The chart in Figure 9 shows the same forecast, but now identifies that a value of $6.65 million gives 80 percent certainty, which is a more realistic estimate. Note that key metrics such as mean, 80 percent, and 20 percent confidence levels are members of the Essbase application. Once the simulation is complete, these values are extracted by Crystal Ball and stored in Essbase, giving more meaningful insight to the outcomes used in the Forecast scenario.
The forecast charts answered the question about which outcomes are most likely. A Crystal Ball sensitivity chart identifies which inputs are most significant. It is much more realistic than a tornado chart, because it varies all of the assumptions simultaneously, and each assumption varies according to its own distribution.
Figure 10 illustrates that the uncertainty around occupancy contributes most to the variability in net revenue. Any "where to focus" discussions for reducing uncertainty and improving forecast accuracy should begin with the sensitivity chart.
In addition to integrating Crystal Ball results directly into OLAP applications, as illustrated in this example, EPM Workspace enables secure sharing of Crystal Ball workbooks and analysis across the enterprise.
FIGURE 10. Uncertainty around occupancy has the highest impact on net revenue.
Crystal Ball with Planning Models
Crystal Ball is also a great extension for Planning models. For example, you can apply Crystal Ball to historic sales data and create future sales predictions by analyzing the levels, trends, and cycles within your data through time-series forecasting and regression methods. These predictions can act as inputs to financial models, which can be simulated and optimized to increase the probability of making your forecasts and reduce the potentials risks in planning assumptions.
Postanalysis of the forecast results can be saved back into your Planning model as scenarios and forecasts. The result is a more accurate forecast with increased confidence.
Crystal Ball Decision Optimizer
Crystal Ball Decision Optimizer adds optimization and substantial processing speed to the power of Monte Carlo simulation. With Decision Optimizer, you can automatically search for your optimal solution to business, finance, and operational spreadsheet models.
Decision Optimizer is easy to use. A wizard guides you through the process of specifying your objectives and defining your constraints and requirements. After you define the optimization parameters, Decision Optimizer quickly pinpoints the set of inputs that best meet your goals, and transfers this to your spreadsheet model. Examples of optimization include finding the asset allocation that maximizes return for a given level of risk, or choosing staffing levels that minimize cost while maintaining required service levels.
This concludes our discussion of Crystal Ball. With this flexible, integrated tool, you can solve problems in which uncertainty and variability have traditionally distorted forecasts and make better decisions to impact the bottom line.