Modeling Discreet Distributions in Excel

1
Modeling Discreet Distributions in Excel

• Inventory Planning
• Airline Overbooking
• Competitive Marketing

2
Inventory Planning Exercise

• Step 1 See how to simulate Discreet distribution
  using Nested IF, LOOKUP, HLOOKUP, DISCRINV(.)
• Step 2 Simulate demand and Calculate Average
  Profits.
• Step 3 Optimize Average Profits
• Step 4 How well we did?

3
Look-Up Functions

• LOOKUP(Value, Look-up Array, Result Array)

2
8
10
14
17
7
A
B
C
D
E
F
HLOOKUP(Value, Table, Row index number)
HLOOKUP(5,Mytable,2) A HLOOKUP(5,Mytable,3) P
HLOOKUP searches a value in the top row of a
table and reports the corresponding value in the
specified row!
4
Discreet Inverse Function from Simtools
DISCRINV(RAND(), X values, P(X))
Range of cells that have the values of the random
variable X
Range of Cells that have the probabilities
associated with each X. NOT THE CUMMULATIVE!
PROBABILITIES!
5
Airline Overbooking Exercise

• Simulation using Binomial Inverse Distribution.
• BINOMINV(RAND(),N,Prob. of Success)
• Optimize Average Profits by choosing the number
  of tickets sold.

6
Competitive Marketing Exercise

• Goal is to use Data Table to simulate and
  optimize a function simultaneously.

Possible values for your decision variable
Random profits function
2-WAY DATA TABLE
Blank Column to replicate the simulations.. See
class 1 on simulation
7
Simulation Optimization

• If your decision variable is an input to the
  probability distribution , then prefer to use
• Copy function
• Or Data tables to replicate simulation.
• Simtools/Simulation Table would fixed values for
  the decision variable therefore the simulation
  output would not change by changing the decision
  variable.