Transcript Document
DECISION MODELING WITH MICROSOFT EXCEL
Chapter 5 LINEAR OPTIMIZATION: APPLICATIONS
Part 1 Copyright 2001 Prentice Hall Publishers and Ardith E. Baker
Introduction
Several specific models (which can be used as templates for real-life problems) will be examined in this chapter. These models include: TRANSPORTATION MODEL Management must determine how to send products from various sources to various destinations in order to satisfy requirements at the lowest possible cost.
ASSIGNMENT MODEL Allows management to investigate allocating fixed-sized resources to determine the optimal assignment of salespeople to districts, jobs to machines, tasks to computers …
MEDIA SELECTION MODEL This model is concerned with designing an effective advertising campaign. DYNAMIC (MULTIPERIOD) MODEL These are models in which coordinated decision making must occur over more than one time period.
FINANCIAL AND PRODUCTION PLANNING These business models illustrate the joint optimization of both production and financial resources.
NETWORK MODELS These models involve the movement or assignment of physical entities (e.g., money).
The Transportation Model
In this example, the AutoPower Company makes a variety of battery and motorized uninterruptible electric power supplies (UPS’s). AutoPower has 4 final assembly plants in Europe and the diesel motors used by the UPS’s are produced in the US, shipped to 3 harbors and then sent to the assembly plants.
Production plans for the third quarter (July – Sept.) have been set. The requirements (demand at the destination) and the available number of motors at harbors (supply at origins) are shown on the next slide:
Assembly Plant (1) Leipzig (2) Nancy (3) Liege (4) Tilburg Harbor (A) Amsterdam (B) Antwerp (C) Le Havre
Demand
No. of Motors Required 400 900 200 500 2000
Supply
No. of Motors Available 500 700 800 2000
Graphical presentation of Supply and Demand: 500 Amsterdam (A) 700 Antwerp (B) 500 Tilburg (4) 400 Leipzig (1) Liege (3) 200 800 Le Havre (C) 900 Nancy (2)
The Transportation Model
AutoPower must decide how many motors to send from each harbor (supply) to each plant (demand). The cost ($, on a per motor basis) of shipping is given below. FROM ORIGIN (A) Amsterdam (B) Antwerp (C) Le Havre TO DESTINATION Leipzig Nancy Liege Tilburg (1) (2) (3) (4) 120 130 41 59.50 61 40 100 110 102.50 90 122 42
The goal is to minimize total transportation cost.
Since the costs in the previous table are on a per unit basis, we can calculate total cost based on the following matrix (where x
ij
represents the number of units that will be transported from Origin i to Destination j): FROM ORIGIN TO DESTINATION 1 2 3 4 A 120x A1 130x A2 41x A3 59.50x
A4 B 61x B1 40x B2 100x B3 110x B4 C 102.50x
C1 90x C2 122x C3 42x C4 Total Transportation Cost = 120x A1 + 130x A2 + 41x A3 + … + 122x C3 + 42x C4
The model has two general types of constraints.
1.
The number of items shipped from a harbor cannot exceed the number of items available.
A constraint is required for each origin that describes the total number of units that can be shipped. For Amsterdam: For Antwerp: x A1 + x A2 + x A3 + x A4 < 500 x B1 + x B2 + x B3 + x B4 < 700 For Le Havre: x C1 + x C2 + x C3 + x C4 < 800 Note: We could have used an “=“ instead of “<“ since supply and demand are balanced for this model. However, the supply inequality constraints will be binding at optimality giving the same effect.
2.
Demand at each plant must be satisfied. A constraint is required for each destination that describes the total number of units demanded. For Leipzig: x A1 + x B1 + x C1 > 400 For Nancy: x A2 + x B2 + x C2 > 900 For Liege: For Tilburg: x A3 + x B3 + x C3 > 200 x A4 + x B4 + x C4 > 500 Note: We could have used an “=“ instead of “>“ since supply and demand are balanced for this model. However, the demand inequality constraints will be binding at optimality giving the same effect.
Here is the spreadsheet model using Excel and solved with Solver: =SUM(C9:C11) =SUM (C9:F9) = C4*C9 =SUM(C16:C18) =SUM (C16:F16)
Here is the Sensitivity Report from Solver for the Transportation Model:
Variations on the Transportation Model
Solving Max Transportation Models Suppose we now want to maximize the value of the objective function instead of minimizing it.
In this case, we would use the same model, but now the objective function coefficients define the contribution margins (i.e., unit returns) instead of unit costs.
In the Solver dialog, you would check the Max radio button before solving the problem. Additionally, your interpretation of Solver’s Sensitivity Report would reflect the maximization of the objective function.
Variations on the Transportation Model
When Supply and Demand Differ When supply and demand are not equal, then the problem is unbalanced. There are two situations: When supply is greater than demand: In this case, when all demand is satisfied, the remaining supply that was not allocated at each origin would appear as slack in the supply constraint for that origin.
Using inequalities in the constraints (as in the previous example) would not cause any problems in Solver.
Variations on the Transportation Model
When demand is greater than supply: In this case, the LP model has no feasible solution. However, there are two approaches to solving this problem: 1.
Rewrite the supply constraints to be equalities and rewrite the demand constraints to be < . Unfulfilled demand will appear as slack on each of the demand constraints when Solver optimizes the model.
Variations on the Transportation Model
2.
Revise the model to append a placeholder origin, called a dummy origin, with supply equal to the difference between total demand and total supply.
The purpose of the dummy origin is to make the problem balanced (total supply = total demand) so that Solver can solve it.
The cost of supplying any destination from this origin is zero.
Once solved, any supply allocated from this origin to a destination is interpreted as unfilled demand.
Variations on the Transportation Model
Eliminating Unacceptable Routes Certain routes in a transportation model may be unacceptable due to regional restrictions, delivery time, etc.
In this case, you can assign an arbitrarily large unit cost number (identified as M) to that route. Choose M such that it will be larger than any other unit cost number in the model.
This will force Solver to eliminate the use of that route since the cost of using it would be much larger than that of any other feasible alternative.
Variations on the Transportation Model
Integer Valued Solutions Generally, LP models do not produce integer solutions. The exception to this is the Transportation model. In general: If all of the supplies and demands in a transportation model have integer values, the optimal values of the decision variables will also have integer values.
Variations on the Transportation Model
Using Alternative Optima to Achieve Multiple Objectives Zeros in the Allowable Increase/Decrease columns for objective coefficients in the Sensitivity Report indicate that there are alternative optimal solutions. Using the AutoPower example, examine the effects of such occurrences. Suppose that due to a potential trucker’s strike, you need to find a cheaper transportation schedule that also minimizes the cost of shipping motors out of Le Havre harbor. You would need to shift costs away from Le Havre to reduce AutoPower’s risk.
In this case, the presence of alternative optima would help avoid some of the risk without increasing total costs.
From the previous solution, we find that there are an infinite number of alternative optima that produce a minimal cost of $121,450. So, the original objective can then be recast as an additional total cost constraint, thereby allowing Solver to be given a new OV to minimize.
Here is the modified spreadsheet model. Note the additional constraint $G$19 < $H$19.
Note that the new solution provides feasible alternatives (no more costly than the original solution), while minimizing Le Havre’s total costs (a shift of $18,000 to other routes).
The Assignment Model
In general, the Assignment model is the problem of determining the optimal assignment of n “indivisible” agents or objects to n tasks.
For example, you might want to assign Salespeople to sales territories Service representatives to service calls Consultants to clients Lawyers to cases Computers to networks Commercial artists to advertising copy The important constraint is that each person or machine be assigned to one and only one task.
The Assignment Model
AutoPower Europe’s Auditing Problem We will use the AutoPower example to illustrate Assignment problems.
AutoPower’s European headquarters is in Brussels. This year, each of the four corporate vice-presidents will visit and audit one of the assembly plants in June. The plants are located in: Leipzig, Germany Nancy, France Liege, Belgium Tilburg, the Netherlands
The issues to consider in assigning the different vice-presidents to the plants are: 1.
Matching the vice presidents’ areas of expertise with the importance of specific problem areas in a plant.
2.
The time the management audit will require and the other demands on each vice president during the two-week interval.
3.
Matching the language ability of a vice president with the plant’s dominant language.
Keeping these issues in mind, first estimate the (opportunity) cost to AutoPower of sending each vice-president to each plant.
The following table lists the assignment costs in $000s for every vice-president/plant combination.
PLANT Leipzig Nancy Liege Tilburg V.P. (1) (2) (3) (4) Finance (F) Marketing (M) Operations (O) Personnel (P) 24 10 21 11 14 22 10 15 15 17 20 19 11 19 14 13
To determine total cost, make the assignment and then add up the costs associated with the assignment. For example, consider the following assignment: PLANT Leipzig Nancy Liege Tilburg V.P. (1) (2) (3) (4) Finance (F) Marketing (M) 24 10 21 11 14 22 10 15 Operations (O) 15 17 20 19 Personnel (P) 11 19 14 13 Total cost = 24 + 22 + 20 + 13 = 79 The question is, is this the least cost assignment?
The Assignment Model
Solving by Complete Enumeration Complete enumeration is the calculation of the total cost of each feasible assignment pattern in order to pick the assignment with the lowest total cost. This is not a problem when there are only a few rows and columns (e.g., vice-presidents and plants). However, complete enumeration can quickly become burdensome as the model grows large.
For example, determine the number of alternatives in the AutoPower (4x4) model. Consider assigning the vice-presidents in the order F, M, O, P.
1.
F can be assigned to any of the 4 plants.
2.
Once F is assigned, M can be assigned to any of the remaining 3 plants.
3.
Now O can be assigned to any of the remaining 2 plants.
4.
P must be assigned to the only remaining plant.
There are 4 x 3 x 2 x 1 = 24 possible solutions. In general, if there are n rows and n columns, then there would be n(n-1)(n-2)(n-
3)…(2)(1) = n!
(n factorial) solutions. As n increases, n! increases rapidly. Therefore, this may not be the best method.
The Assignment Model
The LP Formulation and Solution For this model, let
x ij
= number of V.P’s of type i assigned to plant j where i = F, M, O, P j = 1, 2, 3, 4 Notice that this model is balanced since the total number of V.P.’s is equal to the total number of plants.
Remember, only one V.P. (supply) is needed at each plant (demand).
Here is the spreadsheet model using Excel and solved with Solver: =SUM(C10:C13) =SUM (C10:F10) = C4*C10 =SUM (C18:F18) =SUM(C18:C21)
As a result, the optimal assignment is: PLANT Leipzig Nancy Liege Tilburg V.P. (1) (2) (3) (4) Finance (F) Marketing (M) 24 10 21 11 14 22 10 15 Operations (O) Personnel (P) 15 17 20 19 11 19 14 13 Total Cost ($000’s) = 10 + 10 + 15 + 13 = 48
The Assignment Model
Relation to the Transportation Model The Assignment model is similar to the Transportation model with the exception that supply cannot be distributed to more than one destination.
In the Assignment model, all supplies and demands are one, and hence integers. Thus, Solver will not produce any fractional allocations.
As a result, in the Solver solution, each decision variable cell will either contain a 0 (no assignment) or a 1 (assignment made).
In general, the assignment model can be formulated as a transportation model in which the supply at each origin and the demand at each destination = 1.
The Assignment Model
Unequal Supply and Demand: The Auditing Problem Reconsidered Case 1: Supply Exceeds Demand In this example, suppose the company President decides to audit the plant in Tilburg. Now there are 4 V.P.’s to assign to 3 plants. Here is the cost (in $000s) matrix for this scenario: V.P.
F M O P No. of V.P.s Required 1 24 14 15 11 1 PLANT 2 10 22 17 19 3 21 10 20 14 1 1 NUMBER OF V.P.s
AVAILABLE 1 1 1 1 4 3
To formulate this model, simply drop the constraint that required a V.P. at plant 4 and Solve: Note that one of the V.P.s has not been assigned to a plant.
The Assignment Model
Unequal Supply and Demand: The Auditing Problem Reconsidered Case 2: Demand Exceeds Supply In this example, assume that the V.P. of Personnel is unable to participate in the European audit. Now the cost matrix is as follows: V.P.
F M O No. of V.P.s Required 1 24 14 15 1 PLANT 2 3 10 22 17 21 10 20 1 1 4 11 15 19 1 NUMBER OF V.P.s
AVAILABLE 1 1 1 3 4
Demand > Supply: Adding a Dummy V.P.
In this form, the model is infeasible. To fix this, you can 1.
2.
Modify the inequalities in the constraints (similar to the Transportation example) Add a dummy V.P. as a placeholder to the cost matrix (shown below). V.P.
F M O Dummy No. of V.P.s Required 1 24 14 15 0 1 PLANT 2 10 22 17 1 0 Zero cost to assign the dummy 3 21 10 20 0 1 4 11 15 19 0 NUMBER OF V.P.s
AVAILABLE 1 1 1 1 4 1 4 Dummy supply; now supply = demand
In the solution, the dummy V.P. would be assigned to a plant. In reality, this plant would not be audited.
The Assignment Model
Maximization Models In this Assignment model, the response from each assignment is a profit rather than a cost. For example, AutoPower must now assign four new salespeople to three territories in order to maximize profit. The effect of assigning any salesperson to a territory is measured by the anticipated marginal increase in profit contribution due to the assignment.
Here is the profit matrix for this model. SALESPERSON A B C D No. of Salespeople Required 1 TERRITORY 2 NUMBER OF SALESPEOPLE 3 AVAILABLE 40 18 12 25 30 28 16 24 20 22 20 27 1 1 1 1 4 1 1 1 3 This value represents the profit contribution if A is assigned to Territory 3.
Here is the spreadsheet model using Excel and solved with Solver: =SUM(C10:C13) =SUM (C10:E10) = C4*C10 =SUM (C18:E18) =SUM(C18:C21)
The Assignment Model
Situations with Unacceptable Assignments Certain assignments in the model may be unacceptable for various reasons. In this case, you can assign an arbitrarily large unit cost (or small unit profit) number to that assignment. This will force Solver to eliminate the use of that assignment since, for example, the cost of making that assignment would be much larger than that of any other feasible alternative.
The Media Selection Model
Advertising agencies use Media Selection models to develop effective advertising campaigns.
The basic question that they try to answer is: How many “insertions” (ads) should the firm purchase in each of several possible media (e.g., radio, TV, newspapers, magazines, and Internet Web pages)?
Constraints on the decision maker are typically: advertising budget the number of ads in each media other “rules of thumb” from management
The Media Selection Model
The law of diminishing returns may also influence the Media Selection decision. In other words, the effectiveness of an ad decreases as the number of exposures in a medium increases during a specified period of time.
The objective function of this model is unusual. Conceptually, the model should find the advertising campaign that maximizes demand and satisfies the budget and other constraints.
However, the approach most often used is to measure the response to an ad in a medium in terms of exposure units.
The Media Selection Model
An exposure unit is a subjective measure based on: The quality of the ad The desirability of the potential market . . .
In other words, it is an arbitrary measure of the “goodness” of an ad.
An exposure unit can be thought of as a kind of economic utility.
So the goal is to maximize the total exposure units, taking into account other properties of the model.
The Media Selection Model
Example: Promoting a New Product The RollOn company has decided to start a new product line of motorcycle-like machines with three oversized tires. An advertising campaign with a budget of $72,000 is planned for the introductory month. RollOn decides to use daytime radio, evening TV, and daily newspaper ads in its advertising campaign. The cost per ad in each media are given below: ADVERTISING MEDIUM NUMBER OF PURCHASING UNITS REACHED PER AD COST PER AD ($) Daytime Radio Evening TV Daily Newspaper 30,000 60,000 45,000 1700 2800 1200
The Media Selection Model
Example: Promoting a New Product RollOn arbitrarily selects a scale from 0 to 100 for each ad offering.
1200 1000 800 600 400 200 0
Total Exposures vs. Num ber of Radio Ads
0
Slope = 60
5 10
Slope = 40
15
Num ber of Ads
20 25
It is assumed that each of the first 10 radio ads has a value of 60 exposure units, and each radio ad after the first 10 is rated as having 40 exposures.
The previous graph shows that radio adds suffer from diminishing returns (as evidenced by the change in slope from 60 to 40). RollOn subjectively determines that the first radio adds are more effective than later ones. In addition, they feel that the same situation will occur with TV and newspaper ads. The exposures per ad for each medium are given below: ADVERTISING MEDIUM Daytime Radio Evening TV Daily Newspaper FIRST 10 ADS 60 80 70 ALL FOLLOWING ADS 40 55 35
Here is a plot of the total exposures as a function of the number of ads in each medium.
1400 1200 1000 800 600 400 200 0 0
Total Exposures vs. Num ber of Ads TV 55 Newspaper 35 Radio 40 80 70 60
10 20
Num ber of Ads
30
RollOn want to ensure that the advertising campaign will satisfy the following important criteria: 1.
No more than 25 ads per medium 2.
A total of 1,800,000 purchasing units must 3.
be reached across all media At least ¼ of the ads must appear on TV (blending requirement) Now, to model this Media Selection model as an LP model, let x 1 y 1 x 2 y 2 x 3 = no. of daytime radio ads up to the first 10 = no. of daytime radio ads after the first 10 = no. of evening TV ads up to the first 10 = no. of evening TV ads after the first 10 = no. of newspaper ads up to the first 10 y 3 = no. of newspaper ads after the first 10
The objective function is: Max 60x 1 + 40y 1 + 80x 2 + 55y 2 + 70x 3 + 35y 3 To determine the constraints, remember: x 1 + y 1 = total radio ads No. exposures x 2 + y 2 = total TV ads x 3 + y 3 = total newspaper ads Also remember that the total advertising expenditure cannot exceed $72,000 and the cost of each radio ad is $1700, each TV ad is $2800 and each newspaper ad is $1200. Therefore, the total expenditure constraint is: 1700x 1 + 1700y 1 + 2800x 2 + 2800y 2 1200y 3 < 72,000 + 1200x 3 +
The constraints are: Cost per ad Total advertising expenditure less than $72,000: 1700x 1 + 1700y 1 + 2800x 2 + 2800y 2 1200y 3 < 72,000 + 1200x 3 + No more than 25 ads in a single medium: x 1 x 2 x 3 + y 1 < 25 + y 2 < 25 + y 3 < 25 The entire campaign must reach at least 1,800,000 purchasing units: 30,000x 1 + 30,000y 1 + 60,000x 2 45,000x 3 + 45,000y 3 + 60,000y > 1,800,000 2 + No. purchasing units per ad
Blending Constraint (at least ¼ of the ads must appear on event TV) : x 2 + y 2 x 1 + y 1 + x 2 + y 2 + x 3 + y 3 > ¼ Using this constraint in Excel will produce a Solver “Conditions for Assume Linear Model are
not Satisfied
” error message. You can make this constraint linear by multiplying out the denominator: x 2 + y 2 > .25(x 1 + y 1 + x 2 + y 2 + x 3 + y 3 )
Here is the Excel spreadsheet model after Solving: = M3*F5 = C3*C9 = C3*I9