SimSpiders

Wine Provisioning On Board

by Felisa J. Vázquez-Abad and Yanick Champoux

Modeling and Programing: SpreadSheet

The following is a pseudo-spreadsheet intended to illustrate how to prepare the simulation of the flight process using spread sheets or other high level programs. You can also take a look at the java/C-like pseudo-code below.

The actual mathematical model is in the Modeling page, where you can see the simulation and the Markov Chain formulation. The goal is to estimate the long term profit per flight.

A FRIENDLY ADVICE: This page is an interactive one, intended to help you through the steps of programming a simulation. Rather than choosing a particular software, we give you here a "logical" spreadsheet that (we hope) will help you understand the basic concepts. Please plan on spending a good half hour going through the details and observing the results when you click the buttons.

Number of passengers

Given our model, we must first determine the stratum indicating the type of flight and then generate the number of passengers according to the chosen type of flight. The next parragraph is thus devoted to the generation of a random variable ranging from 1 to 5 that indicates the strata. We shall divert a little to get you used to generating discrete random variables.

The first important quantity to program into your code is the distribution of the random variable in question. Please choose the probabilities of each of the five strata, and insert them into the cells A1-A5. The spreadsheet will calculate then the distribution in row B. Once you're done, press "Done". If you want to revert to the original data, press "Reset".
    1 2 3 4 5
   A   
B
A1
B1+A2
B2 + A3
B3 + A4
B4 + A5
   
   

Once the probabilities are entered, we are ready to generate the strata . In order to do so, we have entered the following commands for the spreadsheet. In the folowing cells, C1 and C2 are random numbers, uniform in (0,1).

    1 2 3 4 5
  C    RANDOM() RANDOM()      
D IF ((C1 < B1) 1, 0) IF ((B1<C1< B2) 1, 0) IF ((B2<C1< B3) 1, 0) IF ((B3<C1< B4) 1, 0) IF ((B4<C1< 1.0) 1, 0)
E 1*D1 + ... + 5*D5 10 * (E1-1) + 10 * C2      

Only one cell amongst cells D1 to D5 has value 1. All others have value 0. This indicates the chosen strata, reported in cell E1. From the description of the model, passengers will be uniformly distributed between the minimum (10 times the value in cell E1, minus 1) and the minimum plus 10, which is achieved in cell E2. The number of passengers that you have generated is E2:

DIRECTIONS: Click on ``Generate Pax" and see the random numbers generated, in cells C1 and C2. Then try to follow the logic in the above spreadsheet to check that all other cells have the value that they should.

    1 2 3 4 5
C      
D
E      

IF THE ABOVE DOESN'T WORK MAY BE YOU DID NOT PRESS "Done" OR "Reset" BEFORE, as you should have.

Profit per Flight

We now proceed to generating the Profit per Flight. For each flight, we generate the number of passengers as before. Then we generate the total demand per passenger, which gives us the total demand for each type of wine.

In cells A6-A9 and B6-B9 we have placed the data of the problem.
    6 (White) 7 (Red) 8 (Sparkling) 9 10
A
Reorder Level
Reorder Level
Reorder Level
Unit Gain
Trucking Cost
B
Maximum Level
Maximum Level
Maximum Level
Unit Penalty
   

Suppose now that the current inventory level is given in cells F1, F2 and F3. When simulate one flight we generate the number of passengers and their individual demands, which gives us the total demand per type of wine, shown in cells G1 to G3. In cells F6 - F8 and G6 - G8 we keep track of auxiliary quantities that allow us to calculate total gains, total penalties, final inventory levels and net profit for this flight.

    1 (White) 2 (Red) 3 (Sparkling)   6 (White) 7 (Red) 8 (Sparkling)   9
F
Inventory
Inventory
Inventory
 
min(G1,F1)
min(G2,F2)
min(G3,F3)
 
Order
G
Demand
Demand
Demand
 
max(G1-F1,0)
max(G2-F2,0)
max(G3-F3,0)
 
Net Profit
H
New Inv.
New Inv.
New Inv.
           
Cell F9: IF ((F6<A6) OR (F7<A7) OR (F8<A8), 1,0)
Cell G9: A9*(F6+F7+F8) - B9*(G6+G7+G8) - A10*F9
Cells Hn: IF ((Fn-Gn>A[n+5]), (Fn-Gn), B[n+5])

DIRECTIONS: We invite you now to change the initial inventory level in cells F1 - F3 and simulate one flight to check how the different claculations are made. You can also change the reorder levels in cells A6 - A8 to see how different values affect the daily profit.

IF THE ABOVE DOESN'T WORK MAY BE YOU DID NOT PRESS "Done" OR "Reset" BEFORE, as you should have.


Modeling Programing: The Pseudo-code

The following code generates the profit from one flight. This simulation is inserted into a loop that generates a series of flights to follow inventory levels as a dynamic process.

1. Initialize:
P[0]=0, p[j]= initial probabilities of strata.
For j = 1, ..., 5 do
  P[j] = P[j] + p[j]
2. Generate Stratum:
U = random(), j = 0
While (U> P[j]) j = j+1
3. Generate Uniform in chosen stratum:
Pax = 10 * j + 10 * random();
4. Generate Demand per flight:
For wine = 1,2,3 do D[wine] = 0
For n = 1, ... , Pax
U=random(), wine_type = 0
While (U> PrWine[wine_type]) wine_type = wine_type+1
U= random(), bottles = 0
While (U> PrBottles[bottles]) bottles = bottles+1
D[wine_type] = D[wine_type] +1
5. Update Inventory and Profit:
For wine = 1,2,3 do
Inventory[wine] = Inventory[wine] - D[wine]
IF (Inventory[wine] < reorderlevel[wine] ) Order = True
IF (Inventory[wine] < 0 )
Penalty = Penalty - Inventory[wine]
Inventory[wine] = 0
Profit = Profit + CDollars * (D[wine] - Inventory[wine])
ELSE Profit = Profit + D[wine]
IF (Order = True) AND (Flight MOD 2 = 1)
For wine = 1,2,3 do Inventory[wine] = Max[wine]
Profit = Profit - ReorderCost - PenaltyCost * Penalty


Floating Index
SimSpiders Main Page

© Copyright 1998 Felisa J. Vázquez-Abad and Yanick Champoux. All rights reserved.