<Insert Picture Here>
Introducing Oracle Crystal Ball Predictor: a new approach to forecasting in MS Excel Environment
Samik Raychaudhuri, Ph. D.
Principal Member of Technical Staff
Oracle Crystal Ball
•
An add-in to Microsoft Excel for performing:
– Monte Carlo simulation – Stochastic optimization – Time series forecasting
•
The focus of the software has been historically on
Monte Carlo simulation, with basic time-series
forecasting capabilities
•
The current version finally provides much-needed
attention to the forecasting capabilities by introducing
an array of features to the tool
Oracle Crystal Ball Predictor Features
• The time-series forecasting tool in Oracle Crystal Ball is called Predictor. We will refer to it henceforth as CB Predictor
• The tool has an interesting set of usability features which sets it apart from other forecasting software:
– Works completely in the familiar Microsoft Excel spreadsheet environment: no data import or result export required
– Officially supported on MS Excel XP, 2003 and 2007. Excel 2010 will be supported soon
– Usual analytical features available (details follow)
– Ease of use, with non-intimidating dialogs and sensible defaults for the uninitiated and providing array of configuration options for power users
– Professional forecasting charts and reports
– Seamlessly integrates with Monte Carlo simulation for conducting risk analysis along with time-series forecasting
CB Predictor Analytical Feature List
•
CB Predictor has an extensive list of features
designed to make the forecasting experience easy
and productive
•
The tool sports a wizard like interface to guide users
through the forecasting process
•
The features can be broadly subdivided into two
categories:
– Data preparation and forecasting – Result analysis and reporting
CB Predictor Analytical Feature List: Data
Preparation and Forecasting
•
Data preparation and forecasting is performed over
four feature-rich screens
– Identifying input data
– Describing data characteristics – Selecting models to run
Data Preparation and Forecasting: The Welcome
Screen
•
CB Predictor starts off at the Welcome screen
Data Preparation and Forecasting: Identifying Input
Data
Data Preparation and Forecasting: Identifying Input
Data (Contd.)
•
The hidden features
– Intelligent data selection
• Select one cell in a contiguous range of series and start CB Predictor
• Identifies the complete range of data, orientation of the data, and the position of header and date ranges if exist – Supports discontinuous data range (e.g., alternate rows or
columns of data)
– Supports logical aligning of data: pre-data gaps
– Automatically identify various type of periods like months in an year, dates, or quarters etc.
Data Preparation and Forecasting: Describing Data
Characteristics
[Planned Screen]
Seasonality detection
Events modeling (New)
Missing value imputation Outlier detection
Data Preparation and Forecasting: Describing Data
Characteristics (Contd.)
• Seasonality detection
– We automatically detect seasonality for input series
– One can override the seasonality of each series or set them to non-seasonal
– The detection algorithm uses threshold-based analysis of autocorrelation and their probabilities at various lags in the data
Data Preparation and Forecasting: Describing Data
Characteristics (Contd.)
• Data screening
– Missing value imputation: CB Predictor can impute the missing values in the dataset
• Uses nearest neighbor interpolation or cubic spline interpolation
• Options to control the interpolation scheme
– Outlier detection: detects outliers specific to each forecasting method
• Suggests replacing values
• Options to control the method and the aggressiveness of the detection algorithm
– In each case, charts are available to ease the decision making process
Data Preparation and Forecasting: Describing Data
Characteristics (Contd.)
•
Events modeling
Data Preparation and Forecasting: Selecting
Forecasting Models
Data Preparation and Forecasting: Selecting
Forecasting Models (Contd.)
•
Non-seasonal Models
– Single moving average
– Single exponential smoothing
– Double moving average
– Double exponential smoothing
•
Seasonal Models
– Seasonal Additive
– Seasonal Multiplicative
– Holt-Winters Seasonal Additive
– Holt-Winters Seasonal Multiplicative
•
Order and other parameters are automatically
detected or can be overridden by the user
Data Preparation and Forecasting: Selecting
Forecasting Models (Contd.)
•
Multiple Linear Regression
– Supports lagged dependent variables – Supports multiple dependent variables
– Stepwise regression (forward and iterative) for choosing important independent variables from a pool
– Performs automatic forecasting of the dependent variable using regression equation and forecasts from independent variable
•
ARIMA
(New)
Data Preparation and Forecasting: Forecasting
Options
CB Predictor Analytical Feature List: Result
Analysis and Reporting
• Features:
– Displays forecast and confidence intervals
– Displays the best method for each series with easy browsing for other series and methods
– Important statistics for each method
– Seasonal bands for visual identification of patterns in the historical and
forecasting horizon
– Adjust forecasting horizon and CI on-the-fly
CB Predictor Analytical Feature List: Result
Analysis and Reporting (Contd.)
•
Manual adjustment of forecasts
CB Predictor Analytical Feature List: Result
Analysis and Reporting (Contd.)
•
Access to reports and data extraction from the results
window menu
•
Integration with CB Monte Carlo simulation
– Forecasts are treated as normal probability distributions with forecast value as the mean and standard error as the
standard deviation
Example: Using CB Predictor for Forecasting and
Risk Analysis: Monica’s Bakery
•
A rapidly-growing boutique bakery in
Taos, New Mexico
•
The owner, Monica, has kept
records of sales of her three main
products: French bread, Italian
bread and pizza
•
Wants to analyze the cash flow of
the business to purchase fixed
assets
Example: Monica’s Bakery (Contd.)
•
Demonstration
Example: Monica’s Bakery (Contd.)
•
Static analysis shows:
•
Doesn’t look good as per the minimum cash target
goes
•
But is it really the true picture?
Example: Monica’s Bakery (Contd.)
• The risk analysis of the cash flow
– Lot of uncertain variables in the spreadsheet model
– The numbers really represent the likely scenario or the “best guess” scenario
• Let’s try to analyze the uncertainty in some of these variables and see the interaction effect
• Set probability distributions on top of input variables (called assumptions)
– COGS
– Overhead
– Financing
– Taxes
• Set the target variables in which we are interested (called forecasts) • Run a Monte Carlo Simulation
– 5000 trials
Example: Monica’s Bakery (Contd.)
•
Use the forecast charts to
find out the probability of
hitting the minimum cash
targets
– Probability for July: 65.37% – Probability for August:
30.97%
– Probability for September: 97.17%
Example: Monica’s Bakery (Contd.)
• How about considering the uncertainty in the forecast values themselves?
• Have those as assumptions as well
• New probability of hitting the minimum cash targets
– Probability for July: 64.42%
– Probability for August: 33.24%
– Probability for September: 96.55%
• These are when you have chosen to use the best forecasting method, which has a reasonably narrow CI
• Choose another forecasting methods having wider forecast CI’s and verify that the risk spread increases for target variables
