An Excel Solver Exercise to Introduce Nonlinear Regression

Abstract

Business students taking business analytics courses that have significant predictive modeling components, such as marketing research, data mining, forecasting, and advanced financial modeling, are introduced to nonlinear regression using application software that is a "black box" to the students. Thus, although correct models are estimated, students often do not obtain a thorough understanding of the nonlinear estimation process. The exercise presented in this article was created to demonstrate to students the need for nonlinear regression estimation-rather than using linear transformations and Ordinary Least Squares (OLS) and subsequently demonstrate the nonlinear optimization process to estimate nonlinear regression models. Using the spreadsheet exercise, students can see effects on the fit of the model by changing the model parameters as they change the values of the decision variables. After applying the spreadsheet to further exercises, students have expressed a deep understanding of the linear regression software. This exercise is innovative because the active learning exercise requires the students to make the logical connections between the structure of the model, the model's parameters, and the objective function.

An Active Learning Exercise for Product Design from an Operations Perspective

“…An assessment tool was used to more formally evaluate student impressions of the exercise's effectiveness. The assessment tool was based on a similar tool used by Pinder (,b, ) and was voluntary, with student responses anonymized. Of the 54 students present in class during the two tests of the exercise, 53 submitted a completed assessment, a response rate of 98%.…”

Logistic Regression via Excel Spreadsheets: Mechanics, Model Selection, and Relative Predictor Importance

“…Although these programs are efficient and effective for large-scale applications, the underlying maximum likelihood model for logistic regression and its corresponding estimation are often hidden from students. This observation, which is well articulated by Pinder (2013) to motivate the use of Excel for nonlinear regression applications, is unfortunate for several reasons. First, it means students might be blindly applying a method they do not sufficiently comprehend.…”

“…Excel spreadsheets are an excellent platform for addressing the three problem areas and can improve student comprehension of logistic regression. To address the first problem, it is not difficult to develop a spreadsheet that incorporates the maximum likelihood formulas necessary for estimating the coefficients of a logistic regression model (see, for example, Carlberg 2013, Pinder 2013, Schield 2017, Ragsdale 2018. In most instances, the generalized reduced gradient (GRG) nonlinear engine of the Excel solver can rapidly find the coefficients that maximize likelihood.…”

A Classroom Exercise to Examine the Trade‐off between Mission Capacity and Life Cycle Cost

“…This approach, individual reading and peer discussion (as opposed to instructor direction or individual hands‐on lab experience), has been shown to improve learning transfer (Mayer, Dale, Fraccastoro, & Moss, ). Excel has proved itself many times as a useful tool in conveying advanced quantitative concepts (e.g., Bai, Newsome, & Zhang, ; Cobb, ), perhaps in part because Excel models (with transparent functionality in the cells) help students avoid the sense that the application is a “black box” beyond their comprehension (Pinder, ).…”