Linear and Nonlinear Optimization using Spreadsheets

The following sections are included:

• Preface
• About the Authors
• Contents

The following sections are included:

• What is Optimization?
• What this Book is Not
• What this Book is
• Types of Optimization Problems
• Optimization Using Excel
• Organization of the Remainder of the Book

There are a variety of different types of production planning applications of linear and nonlinear optimization. In this chapter, we focus on three categories of problems. In Section 2.1, we consider standard, single-period product mix and blending examples. Section 2.2 focuses on multiperiod aggregate planning examples in a production (or manufacturing) context. Here, the goal is to establish production, inventory and (possibly) backorder levels for a planning horizon of six months with the goal of minimizing costs.

In Section 2.3, we focus on shorter-term production scheduling examples where the goal is to generate a production schedule for a multiweek planning horizon. Examples in this section will capture real-world issues such as the costs of 'changing over' the production line to produce different contexts, as well as 'cutting stock' problems that arise in the paper and textile industries.

Workforce planning applications of linear and nonlinear optimization problems can take many different forms. In this chapter, we focus on examples that can broadly be defined by two categories: (i) aggregate planning and (ii) scheduling. In Section 3.1, we consider aggregate planning examples, whereby the goal is to plan workforce staffing levels for a planning horizon of six months. These examples are based on nurse staff planning problems that have been studied for several decades (Kao & Queyranne, 1985; Brusco & Showalter, 1993). The goal is to establish regular-time and overtime nurse staffing hours for multiple nursing units over the planning horizon, so as to satisfy requirements at minimum cost. Supplemental sources of staffing, such as flex pools and float are also considered in the examples…

In this chapter, we focus on continuous facility location problems. The chapter is divided into two main sections: (i) single-source continuous location and (ii) multisource continuous location. The principal focus in the single-source section is the Weber problem, which is arguably the most fundamental problem in continuous location theory. The center-of-gravity problem, which is commonly covered in operations and supply-chain management textbooks and is closely related to the Weber problem (see Morrison, 2010), is also discussed. In the multisource section, we emphasize the generalization of the Weber problem to the case of locating multiple supply sources.

Chapter 4 focused on problems where source facilities could be located anywhere in the continuous two-dimensional space to service demand sites with explicit coordinates in the plane. In Chapter 5, the locations of source facilities are limited to discrete candidates (see Daskin, 2013 for a review of discrete location problems). In Section 5.1, we first describe the classic transportation problem (Hitchcock, 1941), where the goal is to minimize variable distribution costs. Although source facilities are predetermined in the basic transportation problem, we also consider the fixed-charge transportation problem. This problem introduces binary variables for candidate source facilities and seeks to trade off fixed and variable costs. Section 5.1 also considers a transshipment problem, whereby there are two stages of distribution (e.g., plants to regional distribution centers and distribution centers to local warehouses). These two-echelon problems are common in real-world settings (Camm et al., 1997; Robinson et al., 1993)…

Whereas Chapter 5 considered some network problems related to location and distribution, our focus in Chapter 6 is on a couple of important types of routing problems. Section 6.1 examines the shortest route problem, where the goal is to find the shortest route (or path) between two nodes in a network. Although the linear programming approach used to solve this problem is not necessarily the most efficient method, it is conceptually easy to understand and some aspects of the formulation are relevant to other applications…

In this chapter, we focus on examples related to facility layout. Two major classes of layout problems are considered: (i) product layout and (ii) process layout.

In product layout, the layout is based on a sequence of tasks that are necessary to produce a highly standardized output. The time to complete each task is a key input to product layout. A precedence relationship is also assumed to be available for the tasks, indicating which tasks must be completed before others can begin. More specifically, the immediate predecessors for each task are identified. The tasks are to be assigned to a series of workstations along a production assembly line. When assigning the tasks, the precedence relationships must be preserved. That is, a task cannot be assigned to an earlier workstation than any of its predecessors. (Note: A task can be assigned to the same station as its predecessors, just not to a station before them)…

Time/cost trade-offs in project networks have been studied for nearly six decades (Fulkerson, 1961; Kelley, 1961). Although there are different versions of time/cost trade-off problems, the general principle is to shorten the completion time of some (or all) of the project activities so as to either (1) complete the project at minimum cost subject to a project completion time constraint or (2) complete the project in the minimum amount of time subject to a budget constraint. Shortening the completion time of an activity is known as crashing. For each activity, there is a normal completion time that represents the time for the activity under ‘normal’ conditions as well as a crash time that represents the minimum possible completion time for the activity if additional resources are committed. It is commonly assumed that the time/cost trade-off for each activity is a continuous linear function over the interval from crash to normal time; however, discrete (nonlinear) time/cost trade-offs can also be modeled (De et al., 1995). In this chapter, we will examine models for both linear and nonlinear cost structures.

In this chapter, we consider examples related to the field of marketing. Several of the examples pertain to the interface between marketing and operations, yet we frame the chapter in terms of marketing. The first two examples pertain to advertising. The first of these two examples is a general example that focuses on advertisement planning where effectiveness varies by days of the week and time of the day. The second example is specific to a real-world application pertaining to the placement of advertisements on web pages and is based on the work of Chickering and Heckerman (2003)…

In this chapter, we focus on financial examples of linear and nonlinear optimization. Three categories of problems are considered: (i) payout models, (ii) portfolio optimization, and (iii) productivity measurement.

Payout models pertain to situations where payments must be made over several years to satisfy requirements. For example, payouts might be necessary to satisfy a retirement program or to meet the conditions of a lawsuit. An initial payout value is made at the beginning of the planning horizon and the funds are invested in various securities, such that the proceeds from the investments can help make necessary payments in subsequent years…

In this chapter, we consider several examples within the context of sports management. The first example pertains to league scheduling. In these problems, the goal is to determine when and where pairs of teams will play against one another during a season. Real-world applications of such problems abound in the literature and include the scheduling of a Chilean soccer league (Alarcôn et al., 2017), a German basketball league (Westphal, 2014), and an Italian volleyball league (Cocchi, 2018). Although much smaller than these realworld applications, our example conveys some of the fundamental considerations that arise in such problems…

In this chapter, we focus on optimization issues that pertain to ordinary least squares (OLS) regression. In the first section, we focus on the nonlinear optimization problem underlying OLS regression and the derivation of the normal equation for estimating the regression coefficients. In the second section, using an example pertaining to the measurement of customer satisfaction with a restaurant experience (Brusco, 2019), we demonstrate how regression analysis can be accomplished in Excel using only a few basic commands. This example includes not only the estimation of the regression coefficients but also the decomposition of the sum of squares and the significance testing of the regression coefficients…

Logistic regression is one of the most popular methodological tools in predictive analytics. Although logistic regression, broadly defined, encompasses a variety of models that allow for multiple (possibly ordinal) categories of a dependent variable, attention here is restricted to the case of a binary dependent variable. Thus, the term logistic regression when used in this chapter refers to what is sometimes called ‘binary logistic regression’. Applications of the method abound in business-related contexts and include the prediction of bank failure (Zaghdoudi, 2013), loan default (Agbemava et al., 2016), item purchase (Bejaei et al., 2015), complaint behavior (Salamah & Ramayanti 2018), word-of-mouth communication regarding products (Alboqami et al., 2015), and conversion of point differentials or point spreads into win–loss probabilities (Huggins et al., 2020; Kvam & Sokol, 2004). Like ordinary least squares (OLS) regression in the case of a continuous dependent variable, logistic regression can be used for purely predictive purposes, for purely explanatory purposes to identify the key independent variables that drive the dependent variable, or for both predictive and explanatory purposes…

This chapter focuses on Fisher’s (1936, 1938) method for linear discriminant analysis (LDA). Although other sophisticated classification methods have been proposed over the past several decades, LDA remains an important tool for both two-group and multigroup (i.e., three or more groups) classification problems.

We begin by describing the underlying optimization problem and mechanics associated with LDA. Although some presentations begin with a description of the special case of the two-group problem and then generalize to more than two groups, we have elected to work with the general formulation. Our presentation closely follows the structure used by Welling (2006).

Following the section on mechanics, we present a two-group example of LDA. In particular, we revisit the insurance brand example that we used to illustrate logistic regression in Chapter 13. In this section, we also address the simplified computations that are available for the two-group case. We finish the chapter with a multigroup example pertaining to the classic Fisher's iris dataset (Anderson, 1935; Fisher, 1936, 1938).

Factor analysis encompasses a broad range of methods that seek to reduce the dimensionality of data. That is, rather than dealing with a large number of variables, the goal is to identify a small number of factors that explain most of the variation in the dataset. One important basis for categorizing factor analysis is whether it is confirmatory or exploratory…

Like the preceding chapter on factor analysis, the input data for this chapter include an n × p matrix (X) that contains measurements for n observations or cases (e.g., individuals, firms, or plants) on each of p variables. In factor analysis, the focus was on reducing the dimensionality of the columns of X, that is, identifying a few (q << p) linear combinations of the p variables that explain a large proportion of the variation in the full dataset. In cluster analysis, the goal is to use the measurements on the p variables to establish a small number (K <<< n) of clusters or groups of homogeneous observations. Two popular approaches to cluster analysis are considered in this chapter…

The following sections are included:

• References
• Index