Shown in Chapters 13 and 14, create your own optimization model in Excel. Think about the following concepts: 1. Decision variables- physical quantities controlled by the decision maker.

03 Oct Shown in Chapters 13 and 14, create your own optimization model in Excel. Think about the following concepts: 1. Decision variables- physical quantities controlled by the decision maker.

Posted at 04:59h in Business & Finance by

Shown in Chapters 13 and 14, create your own optimization model in Excel. Think about the following concepts:

1. Decision variables- physical quantities controlled by the decision maker.

2. Objective function- Defines the criterion (reduce expenses, maximize profits).

3. Constraints- These are restrictions on the numerical values, which are assigned to the

decision variables.

Need the excel file with report and explanations. Include peer-reviewed citations.

Chapter13-14BusinessAnalytics_Albright.pdf

CHAPTER 13 Introduction to Optimization Modeling

INVENTORY OPTIMIZATION AT GM The key to selling automobiles in the United States is the relationship between manufacturers, dealers, and custom- ers. When customers want to make a purchase, they almost always visit a dealer and purchase a vehicle from the lot. If their vehicle of choice is not on the lot, that dealer can make a request from a nearby dealer who might have the requested vehicle. The manufacturer keeps track of pur- chases at dealers and supplies them with new automobiles as necessary.

The article by Inman et al. (2017) describes how General Motors (GM) developed two optimization models

to determine new-vehicle inventory at its dealers. The first model finds the optimal number of vehicles to build for each dealer. The second model finds the optimal vehicle configurations each dealer should stock. These models differ from the traditional way GM had determined the number of vehicles to stock and their configurations. In the past, the standard approach of finding the level of inventory necessary to achieve a given fill rate, such as meeting 98% of customer demand with on-hand inventory, was used to determine the stock level. The configurations to stock were determined by ranking configurations by demand and stocking those with the highest rankings. Inman and his team used a different approach. For the number of vehicles to stock, they maximized variable profit (revenue minus variable cost) minus carrying costs. For the configurations, they used a “set- covering” approach to find a set of configurations that would cover the observed variety of customer demands.

In the first model, determining the optimal number to stock on dealers’ lots helps GM make better production decisions (overtime, assembly line rate, and number of shifts) and marketing decisions (rebates and advertising). To optimize this number, Inman’s team rejected the argument that carrying costs are incurred only by the dealers and therefore should not be a concern of GM management. Instead, they took a total supply chain view- point, with GM and its dealers considered a single entity. This led them to optimizing variable profit minus carrying costs. Besides, they argue that carrying costs go beyond the traditional costs of inventory such as floor space, insurance, and cost of capital. Customers typically want the newest model vehicle, so the longer vehicles remain on the dealers’ lots, the more heavily the dealers must discount their prices to sell them. This type of “carrying cost” hurts both GM and the dealers, and it provides an incentive to hold less inventory. Their model also considers diversions, where if a customer’s first choice is not in stock, the customer might divert to their second choice and hence still purchase a GM vehicle.

The second model, determining the optimal set of configurations to stock, is possibly even more challenging. A “full” configuration specifies every option possible: color, body style, powertrain, and a host of others. Determining which full configurations to stock would not only be virtually impossible (because of the vast number of configurations) but also pointless. Most customers are looking for a few key features, such as color, and they don’t really care about others. Therefore, Inman’s team concentrated on “partial” configurations, the sets of features that appear to be in highest demand at any given dealer. This greatly

N es

to r R

iz hn

ia k/

Sh ut

te rs

to ck

.c om

09953_ch13_ptg01_575-629.indd 576 04/03/19 12:49 PM

Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.

13-2 Introduction to Optimization 5 7 7

limits the number of decision variables in the optimization model. In addition, they rejected the standard procedure of stocking only the partial configurations in highest demand. To them, it made more sense to “cover” the range of configurations customers wanted. As a simple example, even if data indicate that black and white vehicles are most popular, it still doesn’t make sense to stock all black and white vehicles. At least a few customers will want blue, red, or green vehicles, so at least a few of these should be on dealers’ lots.

The result of the team’s models is an inventory-balancing report tool. For each model vehicle the dealer stocks, the report shows a column for each partial configuration. These partial configurations account for all the dealer’s sales. The report provides details familiar to dealers, who can then use their judgment to fine-tune their ordering decisions. After developing the tool, more than 800 dealers piloted it for six months. These dealers aver- aged a three to five percent increase in sales and revenue compared to a control group of about 7000 dealers not using the tool.

Inman’s team’s models have also helped GM to reduce overall retail inventory. Tra- ditionally, GM held more retail inventory than its competitors, but with the help of the optimization models, GM’s 2015 year-end inventory was 61 days-supply (the number of days to deplete supply at typical customer demand rates), down 14% from 2014 and sub- stantially lower than Ford’s 79 days-supply and Fiat Chrysler’s 81 days-supply.

13-1 Introduction In this chapter, we introduce spreadsheet optimization, one of the most powerful and flexible methods of quantitative analysis. The specific type of optimization we will discuss here is linear programming (LP). LP is used in all types of organizations, often on a daily basis, to solve a wide variety of problems. These include problems in labor scheduling, inventory management, selection of advertising media, bond trading, management of cash balances, operation of an electrical utility’s hydroelectric system, routing of delivery vehicles, blend- ing in oil refineries, hospital staffing, and many others. The goal of this chapter is to intro- duce the basic elements of LP: the types of problems it can solve, how LP problems can be modeled in Excel®, and how Excel’s Solver add-in can be used to find optimal solutions. Then in the next chapter we will examine a variety of LP applications, and we will also look at applications of integer and nonlinear programming, two important extensions of LP.

13-2 Introduction to Optimization We first discuss optimization in general. All optimization problems have several common elements. They all have decision variables, the variables whose values the decision maker is allowed to choose. Either directly or indirectly, the values of these variables determine such outputs as total cost, revenue, and profit. Essentially, they are the variables a com- pany or organization must know to function properly; they determine everything else. All optimization problems have an objective function (objective, for short) to be optimized— maximized or minimized. Finally, most optimization problems have constraints that must be satisfied. These are usually physical, logical, or economic restrictions, depending on the nature of the problem. In searching for the values of the decision variables that opti- mize the objective, only those values that satisfy all the constraints are allowed.

Excel uses its own terminology for optimization, and we will use it as well. Excel refers to the decision variables as the decision variable cells.1 These cells must contain numbers that are allowed to change freely; they are not allowed to contain formulas.

1 In Excel 2007 and previous versions, Excel’s Solver add-in referred to these as “changing cells.” Starting with Excel 2010, it refers to them as “decision variable cells” (or simply “variable cells”), so we will use the newer terminology.

09953_ch13_ptg01_575-629.indd 577 04/03/19 12:49 PM

5 7 8 C h a p t e r 1 3 I n t r o d u c t i o n t o O p t i m i z a t i o n M o d e l i n g

Excel refers to the objective as the objective cell. There can be only one objective cell, which could contain profit, total cost, total distance traveled, or others, and it must be related through formulas to the decision variable cells. When the decision variable cells change, the objective cell should change accordingly.

The decision variable cells contain the values that can be changed to optimize the objective. The objective cell contains the quantity to be minimized or maximized. The constraints impose restrictions on the values in the decision variable cells.

Finally, there must be appropriate cell formulas that operationalize the constraints. For example, one constraint might indicate that the amount of labor used can be no more than the amount of labor available. In this case, there must be cells for each of these two quantities, and typically at least one of them (probably the amount of labor used) will be related through formulas to the decision variable cells. Constraints can come in a variety of forms. One very common form is nonnegativity. This type of constraint states that decision variable cells must have nonnegative (zero or positive) values. Nonnegativity constraints are usually included for physical reasons. For example, it is impossible to pro- duce a negative number of automobiles.

Nonnegativity constraints imply that decision variable cells must contain non- negative values.

There are basically two steps in solving an optimization problem. The first step is to develop the model. Here you decide what the decision variables are, what the objective is, which constraints are required, and how everything is related. If you are developing an algebraic model, you must derive the correct algebraic expressions. If you are developing a spreadsheet model, the focus of this book, you must relate all variables with appropriate cell formulas. In particular, you must ensure that your model contains formulas that relate the decision variable cells to the objective cell and formulas that operationalize the con- straints. This model development step is where most of your effort goes.

The second step in any optimization model is to optimize. This means that you must systematically choose the values of the decision variables that make the objective as large (for maximization) or small (for minimization) as possible and satisfy all the constraints. Some terminology is useful here. Any set of values of the decision variables that satisfies all of the constraints is called a feasible solution. The set of all feasible solutions is called the feasible region. In contrast, an infeasible solution is a solution that violates at least one constraint. Infeasible solutions are not allowed. The desired feasible solution is the one that provides the best value—minimum for a minimization problem, maximum for a maximization problem—of the objective. This solution is called the optimal solution.

Typically, most of your effort goes into the development of the model.

A feasible solution is a solution that satisfies all the constraints. The feasible region is the set of all feasible solutions. An infeasible solution violates at least one of the constraints and is not allowed. The optimal solution is the feasible solution that optimizes the objective.

Although most of the effort typically goes into the model development step, much of the published research in optimization has been about the optimization step. Algorithms have been devised for searching through the feasible region to find the optimal solution.

09953_ch13_ptg01_575-629.indd 578 04/03/19 12:49 PM

13-3 a two-Variable product Mix Model 5 7 9

One such algorithm is called the simplex method. It is used for linear models. There are other more complex algorithms used for other types of models (those with integer decision variables and/or nonlinearities).

We will not discuss the details of these algorithms. They have been programmed into Excel’s Solver add-in. All you need to do is develop the model and then tell Solver what the objective cell is, what the decision variable cells are, what the constraints are, and what type of model (linear, integer, or nonlinear) you have. Solver then finds the best feasible solution with the appropriate algorithm. You should appreciate that if you used a trial-and-error procedure, even a clever and fast one, it could take hours, weeks, or even years to complete. However, by using the appropriate algorithm, Solver typically finds the optimal solution in a matter of seconds.

There is really a third step in the optimization process: sensitivity analysis. You typi- cally choose values of input variables, such as unit costs, forecasted demands, and resource availabilities, and then find the optimal solution for these particular input values. This pro- vides a single “answer.” However, in any realistic situation, it is wishful thinking to believe that all the input values you use are exactly correct. Therefore, it is useful—indeed, man- datory in most applied studies—to follow up the optimization step with what-if questions. What if the unit costs increased by 5%? What if forecasted demands were 10% lower? What if resource availabilities could be increased by 20%? What effects would such changes have on the optimal solution? This type of sensitivity analysis can be done in an informal manner or it can be highly structured. Fortunately, as with the optimization step itself, good soft- ware allows you to obtain answers to various what-if questions quickly and easily.

13-3 A Two-Variable Product Mix Model We begin with a very simple two-variable example of a product mix problem. This is a type of problem frequently encountered in business where a company must decide its product mix—how much of each of its potential products to produce—to maximize its net profit. You will see how to model this problem algebraically and then how to model it in Excel. You will also see how to find its optimal solution with Solver. Next, because it con- tains only two decision variables, you will see how it can be solved graphically. Although this graphical solution is not practical for most problems, it provides useful insights into general LP models. The final step is then to ask a number of what-if questions about the completed model.

An algorithm is a prescription for carrying out the steps required to achieve some goal, such as finding an optimal solution. An algorithm is typically translated into a computer program that performs the work.

EXAMPLE

13.1 ASSEMBLING AND TESTING COMPUTERS AT PC TECH The PC Tech company assembles and then tests two models of computers, Basic and XP. For the coming month, the company wants to decide how many of each model to assemble and then test. No computers are in inventory from the previous month, and because these models are going to be changed after this month, the company doesn’t want to hold any inventory after this month. It believes the most it can sell this month are 600 Basics and 1200 XPs. Each Basic sells for $300 and each XP sells for $450. The cost of component parts for a Basic is $150; for an XP it is $225. Labor is required for assembly and testing. There are at most 10,000 assembly hours and 3000 testing hours available. Each labor hour for assembling costs $11 and each labor hour for testing costs $15. Each Basic requires five hours for assembling and one hour for testing, and each XP requires six hours for assembling and two hours for testing. PC Tech wants to know how many of each model it should produce (assemble and test) to maximize its net profit, but it cannot use more labor hours than are available, and it does not want to produce more than it can sell.

Objective To use LP to find the best mix of computer models that stays within the company’s labor availability and maximum sales constraints.

09953_ch13_ptg01_575-629.indd 579 04/03/19 12:49 PM

Solution The essence of spreadsheet modeling is transforming a “story problem” into an Excel model. Based on our teaching experience, a “bridge” between the two is often needed, especially for complex models. Therefore, in the next few chapters, most examples in the book will start with a “big picture” diagram to help you understand the model—what the key elements are and how they are related—and get you ready for the eventual spreadsheet model.2 Each diagram is in its own Excel file, such as Product Mix 1 Big Picture.xlsx for this example. (These big picture files are available, just like the example files.) A screenshot of this big picture appears in Figure 13.1.

2 We have created these diagrams with Palisade’s BigPicture add-in, part of the DecisionTools Suite.

5 8 0 C h a p t e r 1 3 I n t r o d u c t i o n t o O p t i m i z a t i o n M o d e l i n g

Figure 13.1 Big Picture for Product Mix Model

Labor hours per unit

Labor hours used

Labor hours available

Cost per labor hour

Selling price

Maximum sales

Maximize profit

Number produced <=

Cost of component parts

Unit margin

Playing a slide show

If you load the BigPicture add-in (from the Palisade group of programs) and then open the big picture file, you can see more than this static diagram. First, each of the shapes in the diagram can have a “note,” much like an Excel cell comment. When you move the cursor over the shape, the note appears. Second, the software allows you to create slide shows. We have done this for all of the big pictures in the book. This lets you see how the model “evolves,” and each slide is accompanied by a “pop-up” text box explanation to help you understand the model even better. To run the slide show, click the Play button on the BigPicture ribbon and then the Next Slide button for each new slide. When you are finished, click the Stop button.

BigPicture Tip

We have adopted a color-coding/shape convention for these big pictures.

Our Big Picture Conventions • Blue rectangles indicate given inputs. • Red ovals indicate decision variables. • Green rectangles with rounded tops indicate uncertain quantities (relevant for Chapters 15 and 16). • Yellow rounded rectangles indicate calculated quantities. • Shapes with thin gray borders indicate bottom line outputs or quantities to optimize. • Arrows indicate that one quantity helps determine another. However, if an arrow includes an inequality or equality sign, as

you will often see in the optimization chapters, the arrow indicates a constraint.

The decision variables in this product mix model are straightforward. The company must decide how many Basics to produce and how many XPs to produce. Once these are known, they can be used with the problem inputs to calculate the num- ber of computers sold, the labor used, and the revenue and cost. However, as you will see with other models in this chapter and the next chapter, determining the decision variables is not always this obvious.

Pictures such as this one bridge the gap between the problem statement and the ultimate spreadsheet (or algebraic) model.

09953_ch13_ptg01_575-629.indd 580 04/03/19 12:49 PM

Algebraic Model In the traditional algebraic solution method, you first identify the decision variables.3 In this small problem they are the num- bers of computers to produce. We label these x1 and x2, although any other labels would do. The next step is to write expres- sions for the total net profit and the constraints in terms of the x’s. Finally, because only nonnegative amounts can be produced, explicit constraints are added to ensure that the x’s are nonnegative. The resulting algebraic model is

Maximize 80×1 1 129×2

subject to: 5×1 1 6×2 # 10000

x1 1 2×2 # 3000

x1 # 600

x2 # 1200

x1, x2 $ 0

To understand this model, consider the objective first. Each Basic produced sells for $300, and the total cost of producing it, including component parts and labor, is 150 1 5(11) 1 1(15) 5 $220, so the profit margin is $80. Similarly, the profit margin for an XP is $129. Each profit margin is multiplied by the number of computers produced, and these products are then summed over the two computer models to obtain the total net profit.

The first two constraints are similar. For example, each Basic requires five hours for assembling and each XP requires six hours for assembling, so the first constraint says that the total hours required for assembling is no more than the number avail- able, 10,000. The third and fourth constraints are the maximum sales constraints for Basics and XPs. Finally, negative amounts cannot be produced, so nonnegativity constraints on x1 and x2 are included.

For many years, all LP problems were modeled this way in textbooks. In fact, many com- mercial LP computer packages are still written to accept LP problems in essentially this for- mat. Since around 1990, however, a more intuitive method of expressing LP problems has become popular. This method takes advantage of the power and flexibility of spreadsheets. Actually, LP problems could always be modeled in spreadsheets, but now with the addition of Excel’s Solver add-in, spreadsheets have the ability to solve—that is, optimize—LP problems as well. We use Excel’s Solver for all examples in this book.4

Graphical Solution When there are only two decision variables in an LP model, as there are in this product mix model, you can solve the problem graphically. Although this graphical solution approach is not practical in most realistic optimization models—where there are many more than two decision variables—the graphical procedure illustrated here still yields important insights for general LP models.

In general, if the two decision variables are labeled x1 and x2, then the steps of the method are to express the constraints and the objective in terms of x1 and x2, graph the constraints to find the feasible region [the set of all pairs (x1, x2) satisfying the constraints, where x1 is on the horizontal axis and x2 is on the vertical axis], and then move the objective through the feasible region until it is optimized.

To do this for the product mix problem, note that the constraint on assembling labor hours can be expressed as 5×1 1 6×2 # 10000. To graph this, consider the associated equality (replacing # with 5 ) and find where the associated line crosses the axes. Specifically, when x1 5 0, then x2 5 10000>6 5 1666.7; and when x2 5 0, then x1 5 10000>5 5 2000. This produces the line labeled “assembling hour constraint” in Figure 13.2. It has slope 25>6 5 20.83. The set of all points that satisfy the assembling hour constraint includes the points on this line plus the points below it, as indicated by the arrow drawn from the line. [The feasible points are below the line because the point (0, 0) is obviously below the line, and (0, 0) clearly satisfies the assembly hour constraint.] Similarly, the testing hour and maximum sales constraints are shown in the figure. The points that satisfy all three of these constraints and are nonnegative comprise the feasible region, which is below the heavier lines in the figure.

13-3 a two-Variable product Mix Model 5 8 1

3 This is not a book about algebraic models; the main focus is on spreadsheet modeling. However, we present algebraic models of the examples in this chapter for comparison with the corresponding spreadsheet models. 4 The Solver add-in built into Excel was developed by a third-party software company, Frontline Systems. This company develops much more powerful versions of Solver for commercial sales, but its standard version built into Microsoft Excel suffices for us. More information about Solver software offered by Frontline can be found at www.solver.com.

Many commercial optimi- zation packages require, as input, an algebraic model of a problem. If you ever use one of these packages, you will have to think algebraically.

This graphical approach works only for problems with two decision variables. Recall from algebra that any line of the form ax1 + bx2 = c has slope −a/b. This is because it can be put into the slope − intercept form x2 = c/b − (a/b)x1.

09953_ch13_ptg01_575-629.indd 581 04/03/19 12:49 PM

5 8 2 C h a p t e r 1 3 I n t r o d u c t i o n t o O p t i m i z a t i o n M o d e l i n g

Figure 13.2 Graphical Solution to Two-Variable Product Mix Problem

30002000

1500

1666.7

600

1200

Feasible region (below dark lines)

Testing hour constraint

Assembling hour constraint

Basic sales constraint

XP sales

Optimal solution

constraint Isoprofit lines (dotted)

XPs produced

Basics produced

To the left and below the dark line is the feasible region. As the dotted objective line is pushed as far up to the right as possible, the last feasible point it hits is the one shown. In general, the corner point that is optimal depends on the relative slopes of the lines.

To see which feasible point maximizes the objective, it is useful to draw a sequence of lines where, for each, the objective is constant. A typical line is of the form 80×1 1 129×2 5 c, where c is a constant. Any such line has slope 280>129 5 20.620, regardless of the value of c. This line is steeper than the testing hour constraint line (slope 20.5), but not as steep as the assem- bling hour constraint line (slope 20.83). Then the idea is to move a line with this slope up and to the right, making c larger, until it just barely touches the feasible region. The last feasible point it touches is the optimal point.

Several lines with slope 20.620 are shown in Figure 13.2. The middle dotted line is the one with the largest net profit that still touches the feasible region. The associated optimal poi

03 Oct Shown in Chapters 13 and 14, create your own optimization model in Excel. Think about the following concepts: 1. Decision variables- physical quantities controlled by the decision maker.

Related Tags

Who We Are

Some Categories

More Links

We Accept