• The Gnumeric Manual, version 1.12
• Advanced Analysis
• Simulation Analysis

The remainder of this chapter will illustrate use of the simulation tool using an example from Banks et. al. ¹  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:

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.  

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.

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.

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

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:

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.