Crystal Ball

APPENDIX A

CRYSTAL BALL TUTORIAL

for

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 (i.e. Revenue), vendors average total daily purchasing cost (i.e. Cost), vendors average daily refund for unsold copies (i.e. Refund).

Now, the formulae for determining Revenue, Cost, Refund, and Profit, must be added to the spreadsheet. These are defined in the spreadsheet as follows:

Solution: Using Crystal Ball

There are only a few basic steps needed to solve this problem in Crystal Ball, as outlined below.

Step 1 вЂ" As a first step, let us assume that the vendor will order a fixed number of copies per day, say 60 . This variable is entered into cell B10, Orders. (Note that this number is just a starting point to aid in learning the overall process; at a later time, this variable will become dynamic; i.e. changing with the simulation).

Step 2 вЂ" The next step is to define an “Assumption Cell”. This is how Crystal Ball refers to a cell containing a random input variable. For this project, the only random input variable will be the demand for copies, i.e. cell B15. Note however, that average daily demand has been given as “between 40 and 70” copies per day. This implies that there is no single value that can be used here. It further implies that a probability distribution should be employed when trying to determine the average number of copies demanded on any single day.

For this example, we will sample from a “uniform” distribution. However, the uniform distribution is a continuous distribution, and therefore any random variable can be drawn from this distribution, including random variables with non-integer values. Therefore, we