Adding Optimization to Your Planning Models: Spreadsheets and Beyond

How to Add Optimization to Your Planning Models: Spreadsheets and Beyond Linus Schrage linus@lindo.com www.lindo.com 312-988-7422 Chicago IMPAC 2015 Sept 16, 2015 Keywords: Planning, Optimization

Which flavor of Analytics? Prescriptive How do we make the good things happen? Optimization, how to model discrete go/no-go decisions, modeling uncertainty, multi-criteria, . . . The best way to predict the future is to invent it. –Alan Kay Predictive What will happen next, if . . .? Econometrics, Marketing, Data Science?, . . . Descriptive What happened? Managerial Accounting 101, Statistics 1A, . . . Design of good reporting systems, drill-down, auditing, . . .

Where/How Should a Prescriptive System be Implemented? 1) ERP System, e.g., SAP, MS AX Dynamics, . . . + Seamless integration. That is where the data are. 2) Spreadsheets + Millions of people understand spreadsheets, vs. hundreds for modeling languages. + That is where the data are. + (Over) Rich Function capability, + Excellent report formatting features available, + Good interface capability with other systems such as word processors.

3) Modeling Language System, such as LINGO, etc. + Maintainability and Flexibility of various kinds. Elegance, only one way of representing a feature. + Auditability and visibility--It is easy to see the formulae of a LINGO model in complete, comprehensive form. Truly understanding the model formulae underlying a complex spreadsheet is is like being a detective, except not so easy. + Multiple dimensions are easily represented. A spreadsheet handles 2 dimensions very well, 3 dimensions so-so, and 4 or more dimensions not very well. Examples of dimensions are: Products, Periods, Vendors, Customers, Transport modes, Product qualities, + No Arthritis/Scalability--It is easy to change the size of any set (e.g., add time periods, products, vendors, suppliers, transportation modes, etc.) without having to worry about copying or editing formulae. + Big Data Ready: There is no upper limit of 16,384(as in a spreadsheet) columns, or 1,048,576 rows, or 32767 characters in a formula. + Sparse sets are easily represented. Modeling language systems can easily receive input from, and send results to, spreadsheets, text files, and SQL databases.

The A B C's of Optimization, Spreadsheet style of What’sBest!* A) Identify the Adjustable cells, i.e., the decision variables B) How do we measure Best? i.e., specify an objective function, or criterion function C) What are the Constraints? i.e., the relationships that limit what we can do. Sometimes we are interested in: D) Dual ( so-called Shadow) prices, What is the value/unit of relaxing some constraint? Important in airlines, hotels, car rental for pricing decisions. *Why ask What If, when you can ask What’s Best ?!

A) Typical variables: How much do we buy, produce, ship, carry in inventory; - from a specific vendor of a specific product in a specific period. Frequently variables are 0/1, 0 = No, 1 = Let’s do it. B) Typical objectives: Maximize wealth at end of period T. Minimize cost, e.g., after we have won a contract. C) Typical constraint: sources of a commodity ≥ uses of a commodity, where commodity could be cash, labor, capacity, quantity of product, etc. In a supply chain model, (A) and (C) may be multi-dimensional: product, origin location, destination location, time period.

Staffing and Rostering of Personnel Toll booth staffing at NYC tunnels, -since 1954 Nurses, doctors at a hospital, Inbound telephone call center, crew scheduling at airlines, maintenance personnel at cell phone company, Simplest Staffing Example: DAYS = MON TUE WED THU FRI SAT SUN; REQUIRED = 23 16 13 16 19 14 12; Each staffer works 5 contiguous days, off 2. Decision variables (7): XMN, XTU,..., XSN, How many staffers to start each day of the week.

It is fairly easy to do “What If” analysis: Start 19 on Monday, 14 on Saturday, for a total of 33 staffers.

With Optimization we can ask What’s Best!. See model StaffEtalkWB.xls at www.lindo.com in MODELS library

A More Complex Example: Parking Assignment New apartment condominium conversion almost complete. City fathers require each apartment to be assigned at least one parking spot. How to formulate? Are there likely to be alternate optimal solutions? If there are, what should be done about it? Apartments are in purple. Parking slots are in green.

Apartments Parking From C. H. von Lanzenauer

Parking Assignment: What to do? • What are the decision variables? What sort of aggregation/approximation should we do? • How do we measure “Best”? Simple total distance? • What are the constraints? Other questions? How difficult would it be to minimize squared distance traveled?

Parking Problem in Simple Excel, Greedy Left to Right Solution

Parking Problem: Adding the ABC’s with What’sBest! See model ParkingAsgWB.xls at www.lindo.com in MODELS library.

Optimization in an Uncertain World. Can we still do optimization? Is it even more important? What is the value of taking into account uncertainty? e.g., take into account the inaccuracy of forecasts. The two-number approach to forecasts. What is uncertainty costing us? e.g., what is the value of improving our forecasts? Which forecasts are worth improving?

Tornado parametric analysis of AstroCosmo model. The 3-Number approach to forecasts: For each parameter we estimate a Lowest possible value, PLO, a most likely Median/Base value, PMED, and a Highest possible value, PHI. We are interested how the uncertainty in parameter value affects total profit. We are about to produce two products, Astros and Cosmos. There are seven parameters of which we are unsure: 1:2) The profit contribution of each (mainly selling price), 3:4) Production line capacities for each of the two lines, 5:6) Labor usage rate/unit for each product, 7) Total amount of labor available per day. We want to maximize the total profit, subject Not using more labor than is available, Astro production line capacity, Cosmo production line capacity, The Base case has a profit of $2100/day. The analysis identifies for each parameter, the "bottom line" uncertainty resulting from the input uncertainty of each parameter. The Tornado diagram (it looks like a funnel cloud) gives a graphical display, most uncertainty causing parameter at the top, on down of the results.;

The Base case: Max = 20*A + 30*C; [ALCAP] A <= 60; [CLCAP] C <= 50; [LABOR] 1*A + 2*C <= 120; With solution: Global optimal solution found. Objective value: 2100.000 Variable Value Reduced Cost A 60.00000 0.000000 C 30.00000 0.000000 Row Slack or Surplus Dual Price 1 2100.000 1.000000 ALCAP 0.000000 5.000000 CLCAP 20.00000 0.000000 LABOR 0.000000 15.00000 The scenarios: APRFT CPRFT ALCAP CLCAP ALBRUSE CLBRUSE LABORAVAIL; ! The median or base case values for the parameters; PMED = 20 30 60 50 1 2 120; ! Plausible low values for the parameters; PLO = 17 25 55 45 0.8 1.7 110; ! Plausible high values for the parameters; PHI = 25 38 65 60 1.4 2.3 140;

Tornado Diagram for Astro-Cosmo Problem with Uncertainty See model AstroCosMoTrndo.lng at www.lindo.com in MODELS library.

Optimization under Uncertainty, Suppose We Have Probabilities? Suppose mean demand = 80. If we act as if our forecast is perfect, we stock 80 and expect profit to be: 80*(70-30) = 3200. If forecast error in fact has a standard deviation of 20, what will be our expected profit actually be? (Assume Normal) What will the histogram of profits look like?

Expected Value of Perfect Information (=|EVWS-EV|) = 703.68 Expected Value of Modeling Uncertainty (=|EV-EVEM|) = 41.55 What is the distribution of profit? ( Let’s see, Demand is Normal, so…)

See model SP_NewsBoyN2.xls at www.lindo.com in MODELS library.

Other Optimization Applications by Industry

Supply chain management – since 1974 (Dart Industries/Rexall/Kraft). Plant ==> Distribution Center ==> Retailer/Customer. Supply Chain Redesign/DC Location at P&G After merging in several new product lines and distribution centers, Which DC’s should be closed? Where to locate new DC’s? Which plant supplies which DC? Which DC satisfies which customer? taking into account fixed costs, inbound and outbound transportation cost, and response time. Multiperiod Production Planning and Blending at Welch’s Grape: Meet demands each month at locations around the country from sources around the country, taking into account the required quality levels(mainly acidity) at the demand points, and available quality at each supply point.

Plant Configuration Under Uncertainty at GM Which plants to close, which to re-focus, given demand scenarios and their probabilities. Used three scenarios/year over a five year planning horizon. (GM has historically used 3-number forecasts.)

General Motors: Putting a Constraint on Downside Loss (based on 243 scenarios) Effect of putting a constraint on Downside Risk ∑s Probs * penaltys≤ tolerance; Constraining downside loss caused only a modest decrease in expected profit.

Resource Extraction: Petroleum and Mining (Precious Metals) Planning Horizon is 10 to 60 years. Complications (Value – cost of extraction) varies over the life of a field/mine. Manage a portfolio of oil fields, or mines. In mining, may have to spend a lot of time in early years removing mediocre stuff before getting to the good stuff. In petroleum crude oil production, just the opposite, easy stuff in a field comes early, then the value goes down and the cost/bbl goes up. Given a portfolio of mines/fields, how much should be invested in development in each field in each year so as to have: a) steady refined product output (if desired), b) ability to quickly increase production if price goes up.

Blending: General problem: Given slate of ingredients available and their various qualities, How much to mix of each so as to achieve specified quality targets, at lowest cost. Stigler’s Diet Problem – 1945 How much each of apple, beans, liver, …(77 total) should be eaten/day to get at least 3,000 Calories, 70 grams Protein, 1.8 milligrams Vitamin B1…(9 total) at minimum cost? Gasoline blending – since 1965 Quality targets: octane, volatility, and vapor pressure, and other targets. Volatility and vapor pressure targets increase with decreases in temperature, Octane targets depend upon the altitude. Prices of various ingredients vary from week to week. Feed blending for Cattle– since 1965 Quality targets for protein, carb, vitamins, trace minerals, etc. , given this week’s cost of various ingredients. Metal blending Quality/percentage targets for: C, Fe, Mn, Ni, Cr, Ag, Pb, Cu, Zn, As, Bi, Au, Find a minimum cost blend, given current prices and qualities of various scrap materials, pure materials, etc.

Financial Portfolio Composition of Markowitz Type. For a given expected return target, what portfolio mix minimizes the variance/risk. Key idea: Take into account the covariance between investments so as to achieve true diversification. Variations: Index matching, restricted to a modest number of stocks, R&D portfolios. Insurance company portfolios

Revenue Management/Maximization – circa 1990 at U.S. airlines, later at hotels and car rental firms, etc. +How many “seats” to hold back for customers who show up at last minute, willing to pay a lot. +Choose between one connecting passenger vs. two locals. (5 competing itineraries below) How much to overbook a full flight. PROS, Sabre The demise of People Express. Room Scheduling at Disney CLE ORD STL LHR

Airline crew scheduling – circa 1980 at United and American airlines. What flight legs a pilot should fly, not violating various upper limits on duty time.

Sports Scheduling MLB was scheduled “by hand” from 1981 until 2004 by the husband/wife team: Henry and Holly Stephenson. From 2005 onward, most major sports leagues are scheduled by “off-the-shelf” optimizers plus a few “Tricks”. Michael did a better job of avoiding "semi-repeaters," i.e., same teams play in back-to-back series at home, then away, Minimize travel costs important in MLB, not so much in NFL. Lots of little complications: White Sox and Cubs should not have home games at same time. (Why??) Cincinnati plays at home on opening day. Political conventions in town, the Pope visits Yankee Stadium, etc. Recent new features: Interleague Play. How much harder does this make the problem?

NFL Scheduling 32 teams play 256 games in a 17 week broadcast schedule. Some objectives/soft constraints: No “Bye” weeks early in the schedule. First at home game for each team should be in first or second week. Should not be away from home for more than three weeks in a row. Opponent spacing ≥ 6 weeks. Share market teams should not be home same week, e.g. NYJ  NYG OAK  SNF Travel expenses not important. Q: How much money can you make scheduling the NFL?

Electrical Generator Unit Commitment, at GE and elsewhere: Given forecasted demand over next 24 hours, week, etc., and cost structure of each generator, which generators should be run in each hour? Nuclear Coal Natural Gas Hydro Wind Solar Tidal Pumped storage

Cutting stock: Given length(width) of master or jumbo, and amount needed of the smaller f.g., lengths(widths), what cutting patterns should be used? paper rolls at a paper company, Metal bars in metal product fabricating industry, Fabric at a clothing manufacturer, Cable cutting at Anixter. Suppose one of the raw material widths available is 72 in. How many different ways are there to cut it? . . .

Solving a Big Tire Production Scheduling Problem at Firestone/Bridgestone: Given daily demand schedule for tire types, and which combinations of tires can feasibly be produced/cured together in which heaters, which tire combinations should actually be run in which heaters? A heater can cure from 2 to 4 tires at once, takes from ½ to 2 hours.

Routing, “Full Truck Load” FTL), The NetJets Problem

Air Taxi Routing Problem How many aircraft are required if a) No dead-heading allowed, b) Dead-heading is allowed.

Aircraft Fuel Ferrying/Tankering There can be a large difference in cost of fuel at various airports, e.g., almost factor of 2. Should we buy a lot of fuel at a cheap airport and tanker it to a more expensive airport? Considerations: Amount of fuel burned in going from A to B increases with more fuel carried from A to B. Some airports have ramp fee that may be waived if you buy a specified minimum of fuel. Aircraft tank size limits amount of fuel at takeoff. Runway length, altitude, temperature limits amount of fuel at takeoff. Runway length limits amount of fuel on board at landing. End of planning horizon condition is tricky. If last known stop in plan is at a cheap airport, then land with low tank level. If last known stop in plan is at an expensive airport, not so clear we want to land with a full tank. It depends upon where and how close is the next, as yet unknown stop.

Auctions and Other Market Clearing Optimizations Market Matching Problems ( So-called “Stable Marriage” problem. Customized Optimization): Assigning + Medical Residents to Hospitals, (NRMP, “The Match)”, National Resident Matching Program) + Students to High schools in NYC Medical Residents in the U.S. (Last major update in 1997) Each March, ~40,000 applicants for ~26,000 positions. High schools in NYC ~100,000 students each apply to (a subset of) ~ 300 schools Basic inputs: Each applicant rates the positions to which applying, say one dozen. Each organization ranks the applicants (that are interested). Solve a “Stable Marriage” optimization giving a solution in which there is no un-matched “couple” that is motivated to “have an affair”. Econ Nobel prize 2012 for Al Roth and Lloyd Shapley. NYC High schools Before switch to “Stable Marriage” method: 63% of students successfully matched. 8.5% switched after the fact. After switch to “Stable Marriage” in 2004: 89% of students successfully matched. 6.4% switched after the fact.

Auctions Examples Electricity Transmission Capacity in a U.S. state: Maximize the value of awards, subject to not selling more capacity than is available. Interesting feature: a bidder may bid on a combination of lines, e.g., if in series. Dual/Shadow prices are the clearing prices. Gas Pipeline capacity auction (Midwestern U.S.) Given pipeline capacity requested over what interval of days, and amount bid, which bids should be awarded, so as to maximize sales revenue and not exceed daily pipeline capacity. Gas contract selection under uncertainty at Peoples Gas ( since 1980’s): Which gas contracts to buy when, how much gas to store, when to draw it out, in the face of uncertainty(represented by about a various scenarios of possible weather and spot prices).

Stable Marriage Problem There is always a stable solution. Example data: M1 prefers W2 W1 W3 M2 prefers W3 W2 W1 M3 prefers W1 W3 W2 W1 prefers M2 M1 M3 W2 prefers M3 M2 M1 W3 prefers M1 M3 M2 There are three stable solutions: 1) M1  W2, M2  W3, M3  W1 (The M’s do well) 2) M1  W1, M2  W2, M3  W3 (Everybody gets 2nd choice) 3) M1  W3, M2  W1, M3  W2 (The W’s do well)

Stable Roommates Problem – since 1967, Stanford, HBS. There may, but need not, be a stable solution to the Roommate Matching Problem. Negative Example: 4 people to be paired into two rooms : A prefers B to C to D B prefers C to A to D C prefers A to B to D D prefers A to B to C There are three possible matchings, none are stable. E.g., if match (A with B) and (C with D), then B prefers C to A and C prefers B to D, so B and C run off together, etc.

The Future . . . New sources of big data, thanks to the web, more personal devices, internet of things… Continued substantial improvement in solver capabilities, - sort of a Moore’s Law of improvement. So should be lots of opportunities. Thank you for your attention.

Adding Optimization to Your Planning Models: Spreadsheets and Beyond