Download
1 / 28
280 likes | 292 Views
Explore various linear programming models used in real-world scenarios such as television advertising, worker scheduling, and aggregate planning. Learn how to build optimization models in Excel and apply integer constraints for more accurate results.
E N D
Chapter 4 Linear Programming Models
Introduction • In a recent survey of Fortune 500 firms, 85% of those responding said that they used linear programming. • In this chapter, we discuss some of the LP models that are most often applied to real applications. In this chapter’s examples, you will discover how to build optimization models to • purchase television ads • schedule postal workers • create an aggregate labor and production plan at a shoe company • create a blending plan to transform crude oils into end products, etc.
Introduction continued • The two basic goals of this chapter are to illustrate the wide range of real applications that can take advantage of LP and to increase your facility in modeling LP problems in Excel. • We present a few principles that will help you model a wide variety of problems. • The best way to learn, however, is to see many examples and work through numerous problems. • Remember that all of the models in this chapter are linear models as described in the previous chapter. This means that the target cell is ultimately a sum of products of constants and changing cells, where a constant is defined by the fact that it does not depend on changing cells.
Advertising models • Many companies spend enormous amounts of money to advertise their products. They want to ensure that they are spending their money wisely. • Typically, they want to reach large numbers of various groups of potential customers and keep their advertising costs as low as possible. • The following example illustrates a simple model - and a reasonable extension of this model - for a company that purchases television ads. • A typical advertising model is presented in Example 4.1
Using integer constraints • To this point, the advertising models have allowed noninteger values in the changing cells. In reality, this is not allowed. • To force the changing cells to have integer values, you simply add another constraint in the Solver dialog box. • Be aware that Solver must do a lot more work to solve problems with integer constraints.
Using integer constraints continued • Consider the following about this integer solution: • The total cost in the target cell is now worse (larger) than before. • The optimal integer solution is not the rounded noninteger solution. • When there are integer constraints, Solver uses an algorithm - called branch and bound - that is significantly different from the simplex method. • Integer-constrained models are typically much harder to solve than models without any integer constraints. • If the model is linear except for the integer constraints, that is, it satisfies the proportionality and additivity assumptions of linear models, you should still select the Simplex LP method.
Worker scheduling models • Many organizations must determine how to schedule employees to provide adequate service. • The following example illustrates how LP can be used to schedule employees. • A typical model is presented in Example 4.2.
Multiple solutions • Ocassionally, you may get a different schedule that is still optimal – a solution that uses all 23 employees and meets all constraints. This is a case of multiple optimal solutions. • One other comment about integer constraints concerns Solver’s Tolerance setting. • As Solver searches for the best integer solution, it is often able to find “good” solutions fairly quickly, but it often has to spend a lot of time finding slightly better solutions. • A nonzero tolerance setting allows it to quit early. The default tolerance setting is 0.05. This means that if Solver finds a feasible solution that is guaranteed to have an objective value no more than 5% from the optimal value, it will quit and report this “good” solution.
Aggregate planning models • In this section, the production planning model discussed in Example 3.3 of the previous chapter is extended to include a situation where the number of workers available influences the possible production levels. • Example 4.3 is typical. • The workforce level is allowed to change each period through the hiring and firing of workers. • Such models, where we determine workforce levels and production schedules for a multiperiod time horizon, are called aggregate planning models.
Example 4.3: Background information • During the next four months the SureStep Company must meet (on time) the following demands for pairs of shoes: 3,000 in month 1; 5,000 in month 2; 2,000 in month 3; and 1,000 in month 4. • At the beginning of month 1, 500 pairs of shoes are on hand, and SureStep has 100 workers. • A worker is paid $1,500 per month. Each worker can work up to 160 hours a month before he or she receives overtime. • A worker can work up to 20 hours of overtime per month and is paid $13 per hour for overtime labor.
The rolling planning horizon approach • In reality, an aggregate planning model is usually implemented via a rolling planning horizon. • To illustrate, we assume that SureStep works with a 4-month planning horizon. • To implement the SureStep model in the rolling planning horizon context, we view the “demands” as forecasts and solve a 4-month model with these forecasts. • However, we implement only the month 1 production and work scheduling recommendation. • Example 4.4 is typical.
Model with backlogging allowed • In many situations backlogging is allowed - that is, customer demand can be met later than it occurs. • We’ll modify this example to include the option of backlogged demand. • We assume that at the end of each month a cost of $20 is incurred for each unit of demand that remains unsatisfied at the end of the month. • This is easily modeled by allowing a month’s ending inventory to be negative. The last month, month 4, should be nonnegative. This also ensures that all demand will eventually be met by the end of the four-month horizon.
Model with backlogging allowed continued • We now need to modify the monthly cost computations to incorporate the costs due to shortages. • There are actually several approaches to this backlogging problem. • The most “natural” is shown on the next slide.
Model with backlogging allowed continued • When certain functions, including IF, MIN, MAX, and ABS, are used to relate the objective cell to the changing cells, the resulting model becomes not only nonlinear but nonsmooth. • Essentially, nonsmooth functions can have sharp edges or discontinuities. Solver’s GRG nonlinear algorithm can handle “smooth” nonlinearities, but it has trouble with nonsmooth functions. • The moral is that you should avoid the non-smooth functions in optimization models.
Model with backlogging allowed continued • If you do use nonsmooth functions, then you must run Solver several times, stating from different initial solutions. • Alternatively, non-smooth functions can be handled with a totally different kind of algorithm called a genetic algorithm. • Alternatively, you can use Frontline System’s Evolutionary Solver, which became available in Excel’s Solver in Excel 2010.
Linearizing the backlogging model • Although this nonlinear model with IF functions is “natural”, the fact that we cannot guarantee it to find the optimal solution is disturbing. • We can, however, handle shortages and maintain a linear formulation. • This method is illustrated in Example 4.3.
Blending models • In many situations, various inputs must be blended together to produce desired outputs. • In many of these situations, linear programming can find the optimal combination of outputs as well as the mix of inputs that are used to produce the desired outputs. • Some examples of blending problems are given in the table below. • See Example 4.4
Production process models • LP is often used to determine the optimal method of operating a production process. • In particular, many oil refineries use LP to manage their production operations. • The models are often characterized by the fact that some of the products produced are inputs to the production of other products. • Example 4.5 is typical.
Financial models • The majority of optimization examples described in management science textbooks are in the area of operations: scheduling, blending, logistics, aggregate planning, and others. • This is probably warranted, because many of the most successful management science applications in the real world have been in these areas. • However, optimization and other management science methods have also been applied successfully in a number of financial areas, and they deserve recognition.
Financial models continued • Several of these applications are discussed throughout this book. In this section, we begin the discussion with two typical applications of LP in finance. • The first involves investment strategy. The second involves pension fund management. • This type of model is demonstrated in Example 4.6.
Payments due in the future • Example 4.7 illustrates a common situation where fixed payments are due in the future and current funds must be allocated and invested so that their returns are sufficient to make the payments. • We place this in a pension fund context.
Data envelopment analysis • The data envelopment analysis (DEA) method can be used to determine whether a university, hospital, restaurant, or other business is operating efficiently. • Specifically, DEA can be used by inefficient organizations to benchmark efficient and best-practice organizations. • The following example illustrates DEA and is based on Callen (1991). • DEA is demonstrated in Example 4.8.
Conclusion • In this chapter, we have presented LP spreadsheet models of many diverse situations. • There are several keys you should use with most spreadsheet optimization models: • Determine the changing cells, the cells that contain the values of the decision variables. These cells should contain the values the decision maker has direct control over, and they should determine all other outputs, either directly or indirectly.
Conclusion continued • Set up the spreadsheet model so that you can easily calculate what you want to maximize or minimize (usually profit or cost). For example, in the aggregate planning model, a good way to compute total cost is to compute the monthly cost of operation in each row. • Set up the spreadsheet model so that the relationships between the cells in the spreadsheet and the problem constraints are readily apparent.
Conclusion continued • Make your spreadsheet readable. Use descriptive labels, use range names, use cell comments and text boxes for explanations, and plan your model layout before you dive in. This might not be too important for small, straightforward models, but it is crucial for large, complex models. Just remember that other people are likely to be examining your spreadsheet models. • Keep in mind that LP models tend to fall into categories, but they are definitely not all alike. For example, a problem might involve a combination of the ideas discussed in the worker scheduling, blending, and production process examples of this chapter.
