Crystal Ball
Crystal Ball
APPENDIX A
CRYSTAL BALL TUTORIAL
OPR 9721
Written by Robert Buckley
December 2004
Preliminary Version
To begin using Crystal Ball, click on the Crystal Ball icon, located on the desktop (shown below).
This will open an Excel spreadsheet. Before the spreadsheet opens completely, a question box will appear, prompting you to either “Disable Macros” or “Enable Macros”. Select “Enable Macros”, and “Accept” the licensing agreement that follows. This will allow Crystal Ball to load properly in Excel.
In the snapshot below, notice that the Crystal Ball toolbar has been added to the Excel worksheet, just below the usual Excel toolbar. Most of the work performed in Crystal Ball will be done using this toolbar.
Opening a file for use with Crystal Ball is the same as opening a file with Excel. In fact, all of the standard Excel procedures, i.e. opening/closing/saving files, implementing functions in cells, etc., are all Excel procedures. Crystal Ball is an Excel “add-in” used for simulation, forecasting, and other similar procedures.
Perhaps the easiest and most efficient way to learn a new software application is to try an example. This tutorial contains a very simple example. It is designed to show how to use Crystal Ball as an Excel add-in, and to acquaint the reader with some of the basic tools Crystal Ball has to offer (i.e. simulation and forecasting). Most of the Crystal Ball functions used in this tutorial will be implemented via the Crystal Ball toolbar. Therefore, the Crystal Ball toolbar is displayed below, detailing the various components used here:
1 2 3 4 5 6 7 8 9 10
1 – Define Assumption
2 – Define Decision
3 – Define Forecast
4 – Select Assumptions
5 – Select Decisions
6 – Select Forecasts
7 – Run Preferences
8 – Start Simulation
9 – Stop Simulation
10 – Reset Simulation
There are just a few basic steps involved when solving problems with Crystal Ball. First, the problem statement must be defined. Next, an Excel worksheet must be created to define the given variables and unknowns. Finally, the proper steps must be taken to set-up and apply the Crystal Ball solver.
Problem Statement: The Newsvendor Problem
A newsvendor sells copies of a given newspaper each day. Sales average between 40 and 70 copies per day, uniformly distributed. The vendor pays the newspaper company $1.50 per copy. The vendor sells the copies to his customers for $2.50 per copy. Any newspapers not sold at the end of each day are submitted back to the newspaper company for a $0.50 refund. The vendor needs to determine the optimal number of newspapers to order each day, so that there are as few leftover papers as possible.
The objective then, is to maximize average daily profits; i.e. z = max E[profits].
Setting up: The Excel Spreadsheet
First, we must extract the parameters and variables from the problem statement, and enter them in the worksheet:
The given parameters are: Vendors average daily sales (i.e. Actual Demand, taken from the Distribution Parameters: Minimum 40 and Maximum 70), vendors cost per copy (i.e. Purchase Price per Unit: $1.50), vendors revenue per copy (i.e. Sales Price per Unit: $2.50), and vendors refund per copy (i.e. Refund per Unit: $0.50).
The unknown variables are: Vendors average order quantity (i.e. Order Quantity), average daily demand for copies (i.e. Actual Demand ) , vendors average total daily revenue