Crystal Ball – decision table
This assignment uses the Yellow Define Decision variables. A similar example is given in the text (Chapter 5 in Enterprise risk management models book third edition).
To run decision variables, you need to use the “More Tools…” icon, and select the Decision Table icon off the menu. It will ask you to select the target forecast (select profit), and then it will ask for the decision variables to iterate (ROP and Q).
Model an inventory situation where demand is exponentially distributed with a mean demand of 100 per day (make an integer variable).
A screwy thing about Crystal Ball’s exponential distribution is that you need to input the inverse of the obvious (there is a reason coming from queuing theory).
You don’t have to enter all 30 demands through the green Define Assumption, you can use an Excel (CrystalBall) function “=CB.Exponential(.01)” for a mean exponentially distributed with a mean of 100. You can add an INT after the = sign to make it integer. This function can be copied down the column, saving you a lot of typing.
Starting inventory is 100.
Sales price is $500
Purchase price is $100
Holding cost is $5/item in inventory at the end of each day.
Ordering cost is $200 per order, and what is ordered at the end of each day is received before work the next morning.

Model profit as well as unfilled orders

Compare policies for:
Quantity ordered & Reorder point
Make each a decision variable
Q ranging from 240 to 340 in increments of 20
ROP ranging from 260 to 340 in increments of 20

Simulate 30 days 100 times
Copy and paste the forecast charts for Profit and Shortage (unfilled orders) for the original model with Q=240 and ROP = 260

Develop a decision table for profit for combinations of Q (ranging from 240 to 340 in increments of 20) and ROP (ranging from 260 to 340 in increments of 20).
TO RUN DECISION TABLE:
Select “More Tools” tab, “Decision Table”, select Profit, then Q & ROP, then run.
When you finally get a table of output (in blue), COPY THIS PAGE BEFORE CLOSING and paste into Excel.

Write a report commenting on which policy combination of Q and R appears best, and what shortages might be expected. Include your decision table output.