# Setting up the simulation model

The remainder of this chapter will illustrate use of the simulation tool using an example from Banks et. al. 1  A classic inventory problem is the newsvendor problem.  A newsvendor buys papers for 33 cents each and sells for 50 cents.  Newspapers not sold are sold as scrap (recycled) for 5 cents.  Newspapers are purchased by the paper seller in bundles of 10.  Demand for newspapers can be categorized as “good,” “fair,” or “poor” with probability 0.35, 0.45 and 0.20 respectively, with each day's demand being independent of prior days.  The problem for the newsvendor is to determine the optimal number of papers to purchase when the day's demand is not yet known.

The daily profit equation for the newsvendor is:

Profit = [(Sale revenue) - (Cost) - (Scrap value)]

To set up the model, this example will use two tabs in Gnumeric, a tab labeled 'Profit' to calculate profit, and a tab labeled 'Demand Tables' to store the various tables needed to calculate the demand for any given sampling.

For the Profit tab, set up the profit tab as in Figure 6-1.

At the top of the Profit' tab, the Profit table will be entered .  There are three variables: Sale revenue, Cost and Scrap value, and they take the per unit coefficients of 0.5, 0.33 and 0.05 respectively.  Enter the coefficients in cells B13 through D13.  In cells B12 through D12, enter the equations for sale revenue, cost and Scrap value that are in the list below. In cell E12, enter the equation for Profit

Next, we add the values for the decision variable, which is the amount purchased, and the amount sold.

• B12: =B13*min(B16,B20)
• C12: =C13*B16
• D12: =D13*max(0,B16-B20)
• E12: =B12-C12+D12
• B13: 0.5
• C13: 0.33
• D13: 0.05
• B16: 50
Figure 6-1Profit table for newsvendor example
Using SIMTABLE for parameter values.

Sometimes, there is a need to try a number of different values for a single parameter. In Section 6.4.5 ― Using SIMTABLE the SIMTABLE function will be used to automate the use of a set of values for a parameter such as purchase quantity.  For now, set the purchase quantity to 50 in cell C16.

Next, create the demand tables from which the demand will be generated.  In the tab 'Demand Tables' enter the values of the probability in cells B4 through B6 (B4: 0.35; B5: 0.45; B6: 0.2).  In cells C4, C5 and C6 enter the cumulative probability values (C4: 0.35; C5: 0.8; C6: 1) as shown in Figure 6-2.

• B4: 0.35
• B5: 0.45
• B6: 0.2
• C4: 0.35
• C5: 0.8
• C6: 1.0
Figure 6-2Probability distribution for type of newsday

The next table is the daily demand for newspapers based on the type of news day.  The table Distribution of Newspapers Demanded is in cells A11 through D18 of the Demand Tables worksheet as shown in Table 6-1 and contains the daily demand distribution values.  The cumulative distribution tables in cells A21 through G29, shown in Table 6-2 are derived values from the Distribution of Newspapers Demanded using values in the top Distribution of Newspapers demanded table.

Table 6-1Daily newspaper demand distribution table in Demand Tables worksheet
 A B C D 11 Demand Good Fair Poor 12 40 0.03 0.1 0.44 13 50 0.05 0.18 0.22 14 60 0.15 0.4 0.16 15 70 0.2 0.2 0.16 16 80 0.35 0.08 0.06 17 90 0.15 0.04 0 18 100 0.07 0 0
Table 6-2Cumulative demand distribution table in Demand Tables worksheet
 A B C D E F G 21 Cumulative Distribution Values 22 Demand Good Fair Poor Good Fair Poor 23 40 0.03 0.1 0.44 0 0 0 24 50 0.08 0.28 0.66 0.03 0.1 0.44 25 60 0.23 0.68 0.82 0.08 0.28 0.66 26 70 0.43 0.88 0.94 0.23 0.68 0.82 27 80 0.78 0.96 1 0.43 0.88 0.94 28 90 0.93 1 0.78 0.96 1 29 100 1 0.93 1

When these values are entered, the final results will look like Figure 6-3.

Figure 6-3Completed probability distribution tables in Demand Tables worksheet

Finally, back in the Profit tab, the demand data will be filled in through the use of references to the Demand Tables tab as shown in Figure 6-4.

In the following cells, enter the equations below in the 'Profit' tab:

• B17: =rand()
• C17: =if(B17<'Demand Tables'!C4,"Good",if(C19<'Demand Tables'!C5,"Fair","Poor"))
• B18: =rand()
• B20: =lookup(\$C17,\$B23:\$D23,\$B24:\$D24)
• B21: =E12
• B24: =lookup(Profit!\$B18,'Demand Tables'!E23:E29,'Demand Tables'!\$A23:\$A29)
• C24: =lookup(Profit!\$B18,'Demand Tables'!F23:F29,'Demand Tables'!\$A23:\$A29)
• D24: =lookup(Profit!\$B18,'Demand Tables'!G23:G29,'Demand Tables'!\$A23:\$A29)
Figure 6-4Profit table for newsvendor example

When done, the Profit spreadsheet will be setup with a profit equation, decision variables, and random events as shown in Figure 6-4.  The rand() functions in cells C17 and C18 return a random value between 0 and 1, which are used by the lookup() functions in cells B20, B24, C24 and D24 to calculate a randomly determined daily demand.  Next, this sheet will be used for analysis through the use of simulation.

1

Adapted from Banks, Carson, Nelson and Nicol (2001), Discrete-Event System Simulation, 3rd ed. pp. 42-45.