무제 문서

Use RISKOptimizer with Product Mix Decisions

The Product Mix Problem
from Decision Making Under Uncertainty with RISKOptimizer by Wayne Winston

Virtually every management science book begins the study of linear programming with the classical product mix problem. The object is to determine a mix of products to produce that maximises profit subject to limited resources and known demand for each product. The problem with this setup is, of course, that many parameters of the problem are not known with certainty. Of course, demand is always unknown. The amount of each resource used by a product may be unknown. The price of each product may even be unknown. Despite this uncertainty companies must determine what to produce. RISKOptimizer lets us determine a production schedule that is "best" (maximises expected profit in the presence of multiple sources of uncertainty). In this section we use RISKOptimizer to optimise the product mix in the face of uncertainty.

To work through this case yourself you will need the two example Excel files and RISKOptimizer installed on your system.

Click here to download the example files. (PRODMIX.ZIP, 17KB)

Or, if you don't have an unzip utility, download the files separately below. You may have to right-click and
    select "Save Target As..." to save the files to your desktop.
     prodmix.xls (26KB)
     prodmix2.xls (29KB)

Click here to open a new window and download a trial version of RISKOptimizer Industrial.

Solution
Example 8.1
Drugco produces four drugs. Each drug uses the amount of raw material, labor, and machine time given in Figure 8.1. For example, Drug 1 uses 4 units of raw material, 3 units of labor, and 2 units of machine time. The sales price of each drug is also given. Demand for each drug is unknown, but best case, worst case and most likely demand for each product is also given in Figure 8.1. During the current month 8000 units of raw material, 10,000 units of labor and 14,000 units of machine time are available. How can we schedule this month's production to maximise expected profit? The drugs will spoil at the end of the month, so leftover units of each drug have no value.

Our work is in the file prodmix.xls. See Figure 8.1.

We proceed as follows:

Step by Step

Step 1: In cells B4:E4 we enter trial values for the number of units produced of each drug.

Step 2: In cells B10:E10 we compute the actual demand (assuming a triangular distribution) for each drug. We copy the demand for Drug 1 which is computed in cell B10 with the formula

=RiskTriang(B11,B12,B13)

from cell B10 to C10:E10.

Figure 8.1

Step 3: In cells B5:E5 we compute the number sold of each product by copying from B5 to C5:E5 the formula

=MIN(B4,B10).

Step 4: In cell G2 we compute our total revenue with the formula

=SUMPRODUCT(B5:E5,B6:E6).

Step 5: In cells G13:G15 we compute the usage of each resource. In G13 we compute the usage of raw material with the formula

=SUMPRODUCT($B$4:$E$4,B7:E7).

Copying this formula to G14:G15 computes the usage of labor and machine time.

Step 6: We are now ready to use RISKOptimizer to determine a product mix that maximises expected profit. Figure 8.2 displays our RISKOptimizer settings.

Our goal is to maximise expected profit (G2). Our Adjustable cells are the number of units produced of each drug (cells B4:E4). We add the constraints G13<=I13, G14<=I14, and G15<=I15 as constraints to be satisfied at the end of each simulation. This ensures that we do not use more of any resource than we have available.

Figure 8.2

From Figure 8.1, RISKOptimizer indicates that the maximum expected profit we can obtain is $24,665. We should plan on producing 303 units of Drug 1, 1541 units of Drug 2, 774 units of Drug 3, and 205 units of Drug 4.

What if Resource Usage is Uncertain?

Let's suppose that we do not know exactly how much of each resource will be needed to produce the drugs. More specifically, let's assume that the usage per unit produced of each drug is normally distributed and has a standard deviation of .3. For example, this would imply that the amount of labor used per unit of Drug 1 produced would be normally distributed with a mean of 3 and standard deviation of .3. Now let's suppose we want to have only a 5% chance of running short of any resource during the month. How can we maximise expected profit? Our work is in file prodmix2.xls. See Figure 8.3.

Figure 8.3

We modify our previous formulation as follows:

Step by Step

Step 1: In B7:E9 insert RiskNormal formulas to ensure that resource usage is random. For example, the formula

=RiskNormal(4,0.3)

in cell B7 ensures that raw material usage per unit of Drug 1 produced is normal with mean 4 and standard deviation of .3.

Step 2: In H13:H15 determine if a shortage occurs for each resource by copying from H13 to H14:H15 the formula

=IF(G13>I13,1,0).

A "1" indicates we have used more of the resource than is available.

Step 3: In cell H16 determine if any shortage has occurred with the formula

=MAX(H13:H15).

A "1" in this cell indicates that a shortage has occurred.

Step 4: In cell H17 compute the fraction of all iterations of a simulation that yield a shortage with the formula

=RiskMean(H16).

Step 5: We are now ready to use RISKOptimizer. See Figure 8.4 for the settings.

Figure 8.4

We choose to maximise mean profit (G2) by adjusting the production of each drug (B4:E4). We constrain production of each drug to be an integer between 0 and 5000. To ensure that the probability of a shortage for any resource does not exceed 5% we add the (simulation) constraint H17<=.05.

As shown in Figure 3, RISKOptimizer finds maximum expected profit of $24,477 is obtained by producing 646 units of Drug 1, 529 units of Drug 2, 412 units of Drug 3, and 817 units of Drug 4. Note that introducing the uncertainty in resource usage has slightly reduced our profit. If we wanted to be, say 99.9% sure that we did not run out of any resource, we would suffer a much greater profit reduction.