1 / 165

Chapter 4

Chapter 4. Linear Programming Models. Introduction. In a recent survey of Fortune 500 firms, 85% of those responding said that they used linear programming .

lulu
Download Presentation

Chapter 4

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 4 Linear Programming Models

  2. 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.

  3. 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.

  4. 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.

  5. Example 4.1:Background information • General Flakes Company advertises a low-fat breakfast cereal in a variety of 30 second television ads placed in a variety of television shows. • The ads in different shows vary by cost and by the type of viewers they are likely to reach. • Viewers have been separated into six mutually exclusive categories by age and gender: males age 18 to 35, males age 36 to 55, males over 55, females age 18 to 35, females age 36 to 55, and females over 55.

  6. Example 4.1 continued:Background information • A rating service can supply data on the numbers of viewers in each of these categories who will watch a 30-second ad on any particular television show. • Each viewer is called an exposure. • The company has determined the required number of exposures it wants to obtain for each group. • It wants to know how many ads to place on each of several television shows to obtain these required exposures at minimum cost. • Data is shown here.

  7. Example 4.1 continued: Background information • The company wants to know how many ads to place on each of several television shows to obtain required exposures at minimum costs. • The problem is straightforward to model. • The variables and constraints are shown here.

  8. Example 4.1 continued:The model • This model is essentially the opposite of the product mix model from Chapter 3. • These two prototype LP models are certainly not the only types of LP models that exist, but they are very common. • Maximizing profit is subject to “less than or equal to” constraints. • Minimizing cost is subject to “greater than or equal to” constraints.

  9. Example 4.1 continued:Advertising 1.xlsx

  10. Example 4.1 continued:Developing the model • Follow these steps to develop the model: • Input values and range names. Enter the inputs in the shaded ranges and name the ranges as shown. • Ads purchased. Enter any values in the Number_ads_purchased range. • Exposures obtained. Enter the formula =SUMPRODUCT(B6:I6,Number_ads_purchased) in cell B23 and copy it down to cell B28. • Total cost. In cell B31 enter the formula =SUMPRODUCT(B14:I14,Number_ads_purchased). • The solution is not one that would be expected.

  11. Example 4.1 continued: Using Solver • The main Solver dialog box appears below.

  12. Example 4.1 continued:Discussion of the solution • The optimal solution is probably not the one you would have guessed. • With a set of ads that cost very different amounts and reach very different mixes of viewers, it is difficult to guess the optimal strategy. • For comparison, however, we calculated the total number of viewers from each type of ad in row 12 and divided the costs in row 14 by the numbers of viewers in row 12 to obtain the cost per million viewers in row 15. • You might expect the ads with low cost per million viewers to be chosen most frequently. However, this is not necessarily the case.

  13. Example 4.1 continued:Sensitivity analysis • Solver’s sensitivity report is enlightening for this solution.

  14. Example 4.1 continued:Dual Objective Extension of the model • This advertising model can be extended in a very natural way. General Flakes has two competing objectives • Obtain as many exposures as possible • Keep the total advertising cost as low as possible • The original model minimized total cost and constrained the exposures to be at least as large as a required level. • An alternative is to maximize the total number of excess exposures and put a budget constraint on total cost. Excess exposures are those above the minimal required cost.

  15. Example 4.1 continued:Advertising 2.xlsx • To implement the alternative only minor modifications to the original are required. • Excess exposures. Enter the formula =B23-D23in cell F23 and copy it down. Then sum these cells in B35 with the SUM function. This cell becomes the new target cell to maximize. • Budget constraint. Calculate total cost but constrain it to be less than or equal to cell D32. • Solver dialog box. Modify the Solver dialog box as shown.

  16. Example 4.1 continued:Dual Objective Extension of the model • For two objective models, one objective must be optimized and a constraint must be put on the other. • The result is a “trade-off curve,” shown below.

  17. Example 4.1 continued:Dual Objective Extension of the model • To create the chart shown above, highlight the numbers in column A and J of the figure shown below (from row 43 down) and insert a line chart.

  18. Example 4.1 continued: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.

  19. Example 4.1 continued: Using integer constraints • 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.

  20. 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.

  21. Example 4.2:Background information • A post office requires different numbers of full-time employees on different days of the week. • The number of full-time employees required each day is given in the table below.

  22. Example 4.2 continued:Background information • Union rules state that each full-time employee must work 5 consecutive days and then receive 2 days off. • For example, an employee who works Monday to Friday must be off Saturday and Sunday. • The post office wants to meet its daily requirements using only full-time employees. • Its objective is to minimize the number of full-time employees that must be hired.

  23. Example 4.2 continued:Objective • To develop an LP model that relates five-day shift schedules to daily numbers of employees available, and to use Solver on this model to find a schedule that uses the fewest number of employees and meets all daily workforce requirements.

  24. Example 4.2 continued:Solution • The trick is to define the decision variables as the numbers of employees working each of the seven possible five-day shifts. • By knowing the values of these decision variables, the other output variables can be calculated. • For example, the number working on Thursday is the sum of those who begin their five-day shifts on Sunday, Monday, Tuesday, Wednesday, and Thursday.

  25. Example 4.2 continued:Solution • To model the Post Office problem with a spreadsheet, we must keep track of the following: • Number of employees starting work on each day of the week • Number of employees working each day • Total number of employees • It is important to keep track of the number of employees starting work each day, because this is the only way to incorporate the fact that workers work 5 consecutive days.

  26. Example 4.2 continued:Worker Scheduling.xlsx • This file shows the spreadsheet model for this problem. • The spreadsheet figure below shows the optimal solution.

  27. Example 4.2 continued:Developing the model • To form this spreadsheet, proceed as follows. • Inputs and range names. Enter the number of employees needed on each day of the week in the shaded range, and create the range names shown. • Employees beginning each day. Enter any trial values for the number of employees beginning work on each day of the week in the Employees_starting range. • Employees on hand each day. The important key to this solution is to realize that the numbers in the Employees_starting range do not represent the number of workers who will show up each day. As an example, the number who start on Monday work Monday through Friday. Therefore, enter the formula =$B$4 in cell B14 and copy it across to cell F14. Proceed similarly for rows 15-20, being careful to take “wrap arounds” into account.

  28. Example 4.2 continued:Developing the model After completing these rows calculate the total number who show up each day by entering the formula =SUM(B14:B20)in cell B23 and copying across to cell H23. • Total employees. Calculate the total number of employees in cell B28 with the formula =SUM(Employees_starting) • At this point, you might want to try rearranging the numbers in the Starting range to see if you can “guess” an optimal solution. It’s not that easy.

  29. Example 4.2 continued:Using Solver • Using the Solver – Now invoke the Solver and complete the main dialog box as shown. Also, check use the Simplex LP method and Assume Non-Negativity options.

  30. Example 4.2 continued:Developing the model • The optimal solution requires the number of employees starting work on some days to be a fraction. • Because part-time employees are not allowed, this solution is unrealistic. • We will now show how to solve the post office model when the number of employees beginning work each day must be an integer. • Integer constraint is added as shown on the previous slide.

  31. Example 4.2 continued:Optimal solution • As we see, the post office needs to hire 23 full-time employees. This solution reveals an aspect of some modeling problems.

  32. Example 4.2 continued:Multiple solutions • 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.

  33. Example 4.2 continued:Sensitivity analysis • The most obvious type of sensitivity analysis involves examining how the work schedule and the total number of employees change as the number of employees required each day changes. • Suppose the number of employees needed each day of the week increases by 2, 4, 6. How does this change the total number of employees needed? • We can answer this by using the SolverTable add-in, but we first have to alter the model slightly as shown on the next slide.

  34. Example 4.2 continued:Sensitivity analysis • The problem is that we want to increase each of the daily minimal required values by the same amount.

  35. Example 4.2 continued:Sensitivity analysis • The trick is to enter the original requirements up to row 12, enter a trial value for the extra number required per day in cell K12, and enter the formula =B12+$K$12 in cell B27, which is then copied across to cell H27. • Now we can use the one-way SolverTable option, using cell K12 as the single input, letting it vary from 0 to 6 in increments of 2, and specifying the Total_employees cell as the single output cell.

  36. Example 4.2 continued:Sensitivity analysis • The results appear below. • When the requirement increases by 2 each day, only 2 extra employees are necessary. However, when the requirement increases by 4 each day, more than 4 extra employees are necessary. The same is true when the requirement increases by 6 each day.

  37. 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. • 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.

  38. 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.

  39. Example 4.3 continued:Background Information • It takes 4 hours of labor and $15 of raw material to produce a pair of shoes. • At the beginning of each month workers can be hired or fired. Each hired worker costs $1600, and each fired worker costs $2000. • At the end of each month, a holding cost of $3 per pair of shoes left in inventory is incurred. Production in a given month can be used to meet that month’s demand. • SureStep wants to us LP to determine its optimal production schedule and labor policy. • Objective: To develop an LP spreadsheet model that relates workforce and production decisions to monthly costs, and to find the minimum-cost solution that meets forecasted demands on time and stays within limits on overtime hours and production capacity.

  40. Example 4.3 continued:Solution • The variables and constraints for this aggregate planning model are shown below.

  41. Example 4.3 continued:Aggregate Planning 1.xlsx • The file shows the spreadsheet model for this problem. • The spreadsheet figure on the next slide shows the model.

  42. Example 4.3 continued:The model

  43. Example 4.3 continued:Developing the model • To develop this model, proceed as follows: • Inputs and range names. Enter the input data and create the range names listed. • Production, hiring and firing plans. Enter any trial values for the number for the number of pairs o shoes produced each month, the overtime hours used each month, the workers hired each month, and the workers fired each month. These four ranges, in rows 18, 19, 23, and 30, comprise the changing cells. • Workers available each month. In cell B17 enter the initial number of workers available with the formula =B5.

  44. Example 4.3 continued: Developing the model Because the number of workers available at the beginning of any other month is equal to the number of workers from the previous month, enter the formula =B20 in cell C17 and copy it to the range D17:E17. Then in cell B20 calculate the number of workers available in month 1 with the formula =B17+B18-B19 and copy this formula to the range C20:E20 for the other months. • Overtime capacity. Because each available worker can work up to 20 hours of overtime in a month, enter the formula =$B$7*B20 in cell B25 and copy it to the range C25:E25.

  45. Example 4.3 continued: Developing the model • Production capacity. Because each worker can work 160 regular-time hours per month, calculate the regular-time hours available in month 1 in cell B22 with the formula =$B$6*B20 and copy it to the range C22:E22 for the other months. Then calculate the total hours available for production in cell B27 with the formula =SUM(B22:B23) and copy it to the range C27:E27 for the other months. Finally, because it takes 4 hours of labor to make a pair of shoes, calculate the production capacity for month 1 by entering the formula =B27/$B$12 in cell B32, and copy it to the range C32:E32.

  46. Example 4.3 continued: Developing the model • Inventory each month. Calculate the inventory after production in month 1 by entering the formula =B4+B30 in cell B34. For any other month, the inventory after production is the previous month’s ending inventory plus tat month’s production, so enter the formula =B37+C30 in cell C34 and copy it to the range D34:E34. Then calculate the month 1 ending inventory in cell B37 with the formula =B34-B36 and copy it to the range C37:E37. • Monthly costs. Calculate the various costs shown in rows 40 through 45 for month 1 by entering the formulas =$B$8*B18, =$B$9*B19, =$B$10*B20, =$B$11*B23, =$B$13*B30, =$B$14*B37 in cells B40 through B45. Then copy the range B40:B45 to the range C40:E45 to calculate these costs for the other months.

  47. Example 4.3 continued: Developing the model 8. Totals. In row 46 and column F, use the SUM function to calculate cost totals, with the value in F46 being the overall total cost to minimize. • The Solver dialog box should appear as shown here. • Note that the changing cells include four separate named ranges. • Also note there are integer constraints.

  48. Example 4.3 continued:The solution • Observe that SureStep should never hire any workers, and it should fire 6 workers in month 1, 1 worker in month 2, and 43 workers in month 3. • 80 hours of overtime are used, but only in month 2. • The company produces more than 3700 pairs of shoes during each of the first 2 months, 2000 pairs in month 3, and 1000 in month 4. A total cost of $692,820 is incurred.

  49. Example 4.3 continued:The solution • Again, we would not force the number of pairs of shoes produced each month to be an integer. It makes little difference whether the company produces 3760 or 3761 pairs of shoes during a month, and forcing each month’s shoe production to be an integer can greatly increase the time the computer needs to find an optimal solution. • On the other hand, it is somewhat more important to ensure that the number of workers hired and fired each month is an integer, given the small number of workers involved.

  50. Example 4.3 continued:The solution • Finally, if you want to ensure that Solver finds the optimal solution in a problem where some or all of the changing cells must be integers, it is a good idea to go into Options, then to Integer Options, and set the tolerance to 0. • Otherwise, Solver might stop when it finds a solution that is close to optimal.

More Related