Energy Performance Analysis: Stochastic Frontier Analysis (SFA) and Data Envelopment Analysis (DES) for Energy Performance Analysis

Abstract

Energy performance analysis in the car manufacturing industry is intriguing. The car manufacturing industry, one of the largest energy consuming industries, has been making a considerable effort to improve its energy intensity by implementing energy efficiency programs, in many cases supported by government research or financial programs. While many car manufacturers claim that they have made substantial progress in energy efficiency improvement over the past years through their energy efficiency programs, the objective measurement of energy efficiency improvement has not been studied due to the lack of suitable quantitative methods. This chapter proposes stochastic and deterministic frontier benchmarking models such as the stochastic frontier analysis (SFA) model and the data envelopment analysis (DEA) model to measure the effectiveness of energy saving initiatives in terms of the technical improvement of energy efficiency for the automotive industry, particularly vehicle assembly plants. Illustrative examples of the application of the proposed models are presented and demonstrate the overall benchmarking process to determine best practice frontier lines and to measure technical improvement based on the magnitude of frontier line shifts over time. Log likelihood ratio and Spearman rank-order correlation coefficient tests are conducted to determine the significance of the SFA model and its consistency with the DEA model. ENERGY STAR^® EPI (Energy Performance Index) are also calculated. This chapter also provides a short instruction to Excel Solver by illustrating three examples: (1) SFA parameters estimation (2) DEA LP problem and (3) traveling compressed air expert problem, with an attempt to help readers learn and use GRG method, Simplex LP method and evolutionary method, respectively.

Appendices

Appendix A: Derivation of the Log Likelihood Function and First-Order Partial Derivatives for Cost Frontier Model

This derivation is for the case in which a SFA model demonstrates half-normal inefficiency and produces a normal measurement error. The derivation of the log likelihood function is modified from Bogetoft and Otto (2011). The product of the densities of a half-normal inefficiency distribution $(u\sim N^{ + } (0,\sigma_{u}^{2} ))$ and a normal measurement error distribution $(v\sim N(0,\sigma_{v}^{2} ))$ is:

$$\begin{aligned} \varphi_{v} (\varepsilon - u)\varphi_{u} (u) & = \frac{1}{{\sqrt {2\pi \sigma_{v}^{2} } }}{\text{e}}^{{ - \frac{1}{2} \frac{{\left( {\varepsilon - u} \right)^{2} }}{{\sigma_{v}^{2} }}}} \frac{2}{{\sqrt {2\pi \sigma_{u}^{2} } }}{\text{e}}^{{ - \frac{1}{2} \frac{{u^{2} }}{{\sigma_{u}^{2} }}}} \\ & = \frac{1}{{\pi \sqrt {\sigma_{u}^{2} \sigma_{v}^{2} } }}{\text{e}}^{{ - \frac{1}{2}\frac{{u^{2} }}{{\sigma_{u}^{2} }} - \frac{1}{2}\frac{{\left( {\varepsilon - u} \right)^{2} }}{{\sigma_{v}^{2} }}}} \\ & = \frac{1}{{\pi \sqrt {\sigma_{u}^{2} \sigma_{v}^{2} } }}{\text{e}}^{{ - \frac{1}{2}\frac{{\left( {\sigma_{u}^{2} + \sigma_{v}^{2} } \right)u^{2} - 2\sigma_{u}^{2} \varepsilon \,u + \sigma_{u}^{2} \varepsilon^{2} }}{{\sigma_{u}^{2} \sigma_{v}^{2} }}}} . \\ \end{aligned}$$

The integration of the aforementioned joint density is given by the following:

$$\begin{aligned} \varphi_{\varepsilon } \left( \varepsilon \right) & = \mathop \smallint \limits_{0}^{\infty } \varphi_{v} \left( {\varepsilon - u} \right)\varphi_{u} \left( u \right) du \\ & = \frac{1}{{\pi \sqrt {\sigma_{u}^{2} \sigma_{v}^{2} } }}\mathop \smallint \limits_{0}^{\infty } {\text{e}}^{{ - \frac{1}{2}\frac{{\left( {\sigma_{u}^{2} + \sigma_{v}^{2} } \right)u^{2} - 2\sigma_{u}^{2} \varepsilon u + \sigma_{u}^{2} \varepsilon^{2} }}{{\sigma_{u}^{2} \sigma_{v}^{2} }}}} du \\ & = \frac{1}{{\pi \sqrt {\sigma_{u}^{2} \sigma_{v}^{2} } }}\mathop \smallint \limits_{0}^{\infty } {\text{e}}^{{ - \frac{1}{2}\left( {\left( {\frac{1}{{\sigma_{u}^{2} }} + \frac{1}{{\sigma_{v}^{2} }}} \right)u^{2} - \frac{2}{{\sigma_{v}^{2} }}\varepsilon u + \frac{1}{{\sigma_{v}^{2} }}\varepsilon^{2} } \right)}} du \\ & = \frac{1}{{\pi \sqrt {\sigma_{u}^{2} \sigma_{v}^{2} } }}\mathop \smallint \limits_{0}^{\infty } {\text{e}}^{{ - \frac{1}{2}\left( {\frac{1}{{\sigma_{u}^{2} }} + \frac{1}{{\sigma_{v}^{2} }}} \right)\left( {u^{2} - \frac{2\varepsilon u}{{\left( {\frac{1}{{\sigma_{u}^{2} }} + \frac{1}{{\sigma_{v}^{2} }}} \right)\sigma_{v}^{2} }} + \frac{{\varepsilon^{2} }}{{\left( {\left( {\frac{1}{{\sigma_{u}^{2} }} + \frac{1}{{\sigma_{v}^{2} }}} \right)\sigma_{v}^{2} } \right)^{2} }} - \frac{{\varepsilon^{2} }}{{\left( {\left( {\frac{1}{{\sigma_{u}^{2} }} + \frac{1}{{\sigma_{v}^{2} }}} \right)\sigma_{v}^{2} } \right)^{2} }}} \right)}} {\text{e}}^{{ - \frac{{\varepsilon^{2} }}{{2\sigma_{v}^{2} }}}} du \\ & = \frac{1}{{\pi \sqrt {\sigma_{u}^{2} \sigma_{v}^{2} } }}\mathop \smallint \limits_{0}^{\infty } {\text{e}}^{{ - \left( {\frac{1}{\sqrt 2 }\sqrt {\frac{1}{{\sigma_{u}^{2} }} + \frac{1}{{\sigma_{v}^{2} }}} \left( {u - \frac{\varepsilon }{{\left( {\frac{1}{{\sigma_{u}^{2} }} + \frac{1}{{\sigma_{v}^{2} }}} \right)\sigma_{v}^{2} }}} \right)} \right)^{2} }} {\text{e}}^{{\left( {\frac{{\varepsilon^{2} }}{{2\left( {\frac{1}{{\sigma_{u}^{2} }} + \frac{1}{{\sigma_{v}^{2} }}} \right)\left( {\sigma_{v}^{2} } \right)^{2} }} - \frac{{\varepsilon^{2} }}{{2\sigma_{v}^{2} }}} \right)}} du. \\ \end{aligned}$$

Let $t = \frac{1}{\sqrt 2 }\sqrt {\frac{1}{{\sigma_{u}^{2} }} + \frac{1}{{\sigma_{v}^{2} }}} \left( {u - \frac{\varepsilon }{{\left( {\frac{1}{{\sigma_{u}^{2} }} + \frac{1}{{\sigma_{v}^{2} }}} \right)\sigma_{v}^{2} }}} \right)$, $u = \frac{\sqrt 2 }{{\sqrt {\frac{1}{{\sigma_{u}^{2} }} + \frac{1}{{\sigma_{v}^{2} }}} }}t + \frac{\varepsilon }{{\left( {\frac{1}{{\sigma_{u}^{2} }} + \frac{1}{{\sigma_{v}^{2} }}} \right)\sigma_{v}^{2} }}$ and $du = \frac{\sqrt 2 }{{\sqrt {\frac{1}{{\sigma_{u}^{2} }} + \frac{1}{{\sigma_{v}^{2} }}} }}dt$. Then, if $u \to \infty$, then $t \to \infty$. If $u = 0$, then $t = \frac{ - \varepsilon }{{\sqrt 2 \sqrt {\frac{1}{{\sigma_{u}^{2} }} + \frac{1}{{\sigma_{v}^{2} }}} \sigma_{v}^{2} }}$ .

$$\begin{aligned} \varphi_{\varepsilon } \left( \varepsilon \right) & = \frac{1}{{\pi \sqrt {\sigma_{u}^{2} \sigma_{v}^{2} } }}\left( {\int_{{\frac{ - \varepsilon }{{\sqrt 2 \sqrt {\frac{1}{{\sigma_{u}^{2} }} + \frac{1}{{\sigma_{v}^{2} }}} \sigma_{v}^{2} }}}}^{\infty } {e^{{ - t^{2} }} } \frac{\sqrt 2 }{{\sqrt {\frac{1}{{\sigma_{u}^{2} }} + \frac{1}{{\sigma_{v}^{2} }}} }}dt} \right){\text{e}}^{{ - \frac{1}{2}\frac{{\varepsilon^{2} }}{{\left( {\sigma_{u}^{2} + \sigma_{v}^{2} } \right)}}}} \\ & = \frac{1}{{\pi \sqrt {\sigma_{u}^{2} \sigma_{v}^{2} } }}\frac{\sqrt 2 }{{\sqrt {\frac{1}{{\sigma_{u}^{2} }} + \frac{1}{{\sigma_{v}^{2} }}} }}\frac{\sqrt \pi }{2}\left( {\frac{2}{\sqrt \pi }\int_{{\frac{ - \varepsilon }{{\sqrt 2 \sqrt {\frac{1}{{\sigma_{u}^{2} }} + \frac{1}{{\sigma_{v}^{2} }}} \sigma_{v}^{2} }}}}^{\infty } {{\text{e}}^{{ - t^{2} }} dt} } \right){\text{e}}^{{ - \frac{1}{2 }\frac{{\varepsilon^{2} }}{{\left( {\sigma_{u}^{2} + \sigma_{v}^{2} } \right)}}}} \\ & = \frac{1}{{\sqrt {2\pi } \sqrt {\sigma_{u}^{2} + \sigma_{v}^{2} } }}\left( {1 + erf\left( {\frac{\varepsilon }{{\sqrt 2 \sqrt {\frac{1}{{\sigma_{u}^{2} }} + \frac{1}{{\sigma_{v}^{2} }}} \sigma_{v}^{2} }}} \right)} \right){\text{e}}^{{ - \frac{1}{2}\frac{{\varepsilon^{2} }}{{\left( {\sigma_{u}^{2} + \sigma_{v}^{2} } \right)}}}} \\ & = \frac{1}{{\sqrt {2\pi } \sqrt {\sigma_{u}^{2} + \sigma_{v}^{2} } }}\left( {1 + erf\left( {\frac{\varepsilon }{{\sqrt 2 \sqrt {\sigma_{v}^{2} + \sigma_{u}^{2} } }}\sqrt {\frac{{\sigma_{u}^{2} }}{{\sigma_{v}^{2} }}} } \right)} \right){\text{e}}^{{ - \frac{1}{2}\frac{{\varepsilon^{2} }}{{\left( {\sigma_{u}^{2} + \sigma_{v}^{2} } \right)}}}} \\ & \quad ({\text{Set}}\;\sigma^{2} = \sigma_{v}^{2} + \sigma_{u}^{2} \quad {\text{and}}\quad \lambda = \sqrt {\frac{{\sigma_{u}^{2} }}{{\sigma_{v}^{2} }}} ) \\ & = \frac{1}{{\sqrt {2\pi \sigma^{2} } }}\left( {1 + erf\left( {\frac{\varepsilon }{{\sqrt 2 \sqrt {\sigma^{2} } }}\lambda } \right)} \right){\text{e}}^{{ - \frac{1}{2} \frac{{\varepsilon^{2} }}{{\sigma^{2} }}}} \quad (erf(x) = \frac{2}{\sqrt \pi }\mathop \smallint \limits_{0}^{x} {\text{e}}^{{ - x^{2} }} dx) \\ & = \frac{1}{{\sqrt {2\pi \sigma^{2} } }}2\varPhi \left( { + \frac{\lambda \varepsilon }{{\sqrt {\sigma^{2} } }}} \right){\text{e}}^{{ - \frac{1}{2} \frac{{\varepsilon^{2} }}{{\sigma^{2} }}}} \quad (\varPhi \;is\;the\;normal\;cumulative\;density\;function) \\ & = \frac{\sqrt 2 }{{\sqrt {\pi \sigma^{2} } }}\varPhi \left( { + \frac{\lambda \varepsilon }{{\sqrt {\sigma^{2} } }}} \right){\text{e}}^{{ - \frac{1}{2} \frac{{\varepsilon^{2} }}{{\sigma^{2} }}}} . \\ \end{aligned}$$

where $\sigma^{2} = \sigma_{v}^{2} + \sigma_{u}^{2}$, and $= \sqrt {\frac{{\sigma_{u}^{2} }}{{\sigma_{v}^{2} }}}$ . The error function $erf\left( x \right) = \frac{2}{\sqrt \pi }\int_{0}^{x} {{\text{e}}^{{ - t^{2} }} dt}$ has the following property: $erf\left( { - x} \right) = - erf(x)$. Its relationship to the normal distribution is given by $\varPhi \left( x \right) - \frac{1}{2} = \frac{1}{{\sqrt {2\pi } }}\int_{0}^{x} {{\text{e}}^{{ - \frac{1}{2}t^{2} }} } dt = \frac{1}{2}erf\left( {\frac{x}{\sqrt 2 }} \right)$ such that $\varPhi \left( x \right) = \frac{1}{2}\left( {1 + erf\left( {\frac{x}{\sqrt 2 }} \right)} \right)$. Then, the log of this density is:

$$\log \varphi_{\varepsilon } (\varepsilon ) = - \frac{1}{2}\log \left( {\frac{\pi }{2}} \right) - \frac{1}{2}\log \sigma^{2} + \log \varPhi \left( {\frac{\varepsilon \lambda }{{\sqrt {\sigma^{2} } }}} \right) - \frac{1}{2} \frac{{\varepsilon^{2} }}{{\sigma^{2} }}.$$

With K independent observations and K firms, the joint density is $\varphi \left( {\varepsilon_{1} , \ldots ,\varepsilon_{K} } \right) = \prod\nolimits_{k = 1}^{K} {\varphi_{\varepsilon } (\varepsilon_{K} )}$ and the log of the joint density is:

$$\begin{aligned} \log \varphi \left( {\varepsilon_{1} , \ldots ,\varepsilon_{K} } \right) & = \sum\limits_{k = 1}^{K} {\log \varphi_{\varepsilon } (\varepsilon_{k} )} \\ & = - \frac{1}{2}K\log \left( {\frac{\pi }{2}} \right) \\ & \quad - \frac{1}{2}K\log \sigma^{2} + \sum\limits_{k = 1}^{K} {\log \varPhi } \left( {\frac{{\lambda \varepsilon_{k} }}{{\sqrt {\sigma^{2} } }}} \right) - \frac{1}{{2\sigma^{2} }}\sum\limits_{k = 1}^{K} {\varepsilon_{k}^{2} } . \\ \end{aligned}$$

We can rewrite this equation to emphasize that the error term $\epsilon$ depends on the parameter (vector) $\beta$, such that the log likelihood function is given by:

$$\begin{aligned} l\left( {\beta ,\sigma^{2} ,\lambda } \right) & = \log \varphi_{\varepsilon } \left( {\varepsilon_{1} \left( \beta \right), \ldots ,\varepsilon_{K} \left( \beta \right);\sigma^{2} ,\lambda } \right) \\ & = \log \varphi_{\varepsilon } \left( {y_{1} - f\left( {x_{1} ;\beta } \right), \ldots ,y_{K} - f\left( {x_{K} ;\beta } \right);\sigma^{2} ,\lambda } \right) \\ & = - \frac{1}{2}K\log \left( {\frac{\pi }{2}} \right) \\ & \quad - \frac{1}{2}K\log \sigma^{2} + \sum\limits_{k = 1}^{K} {\log \varPhi } \left( {\frac{{\lambda (y_{k} - f\left( {x_{k} ;\beta } \right))}}{{\sqrt {\sigma^{2} } }}} \right) \\ & \quad - \frac{1}{{2\sigma^{2} }} \sum\limits_{k = 1}^{K} {(y_{k} - f(x_{k} ;\beta ))^{2} } . \\ \end{aligned}$$

The function $l(\beta , \sigma^{2} ,\lambda )$ is the log-likelihood function, which depends on the parameters to be estimated (in this case $\beta$, $\sigma^{2}$, and $\lambda$) and on the data $(x_{1} ,y_{1} ), \ldots ,(x_{K} ,y_{K} )$. Then, the gradient of $l(\beta ,\lambda )$ with respect to $\beta ,\lambda$ is as follows, with $\sigma^{2}$ defined as $\frac{1}{K}\sum\nolimits_{k = 1}^{K} {(y_{k} - f(x_{k} ;\beta ))^{2} }$.

$$\begin{aligned} l\left( {\beta ,\lambda } \right) & = - \frac{k}{2}\log \left( {\frac{\pi }{2}} \right) - \frac{k}{2}\log \left( {\sigma^{2} } \right) + \sum\limits_{k = 1}^{K} {\log \varPhi } \left( {\frac{{\varepsilon_{k} \lambda }}{\sigma }} \right) - \frac{k}{2} \\ & = \frac{k}{2}\log \left( {\frac{\pi }{2}} \right) - k\log \sigma + \sum\limits_{k = 1}^{K} {\log \varPhi } \left( {\frac{{\lambda \varepsilon_{k} }}{\sigma }} \right) - \frac{k}{2}. \\ \end{aligned}$$

Let $\sigma^{{\prime }} = \frac{\partial }{{\partial \beta_{j} }}\left( \sigma \right)$. Then, $\sigma^{{\prime }} = \frac{\partial }{{\partial \beta_{j} }}\left( {\sqrt {\frac{1}{k}\sum\nolimits_{K = 1}^{K} {\varepsilon_{k}^{2} } } } \right) = \frac{{ - 2\sum\nolimits_{k = 1}^{K} {\varepsilon_{k} } X_{jk} }}{{2\sqrt k \sqrt {\mathop \sum \nolimits_{k = 1}^{K} \varepsilon_{k}^{2} } }} = - \frac{{\mathop \sum \nolimits_{k = 1}^{K} \varepsilon_{k} X_{jk} }}{{\sqrt k \sqrt {\mathop \sum \nolimits_{k = 1}^{K} \varepsilon_{k}^{2} } }}$. Similarly, $\varepsilon_{k}^{{\prime }} = - X_{jk}$.

$$- k\left( {\frac{{\sigma^{'} }}{\sigma }} \right) = - k\left( {\frac{{\frac{1}{{2\sqrt k \sqrt {\mathop \sum \nolimits_{k = 1}^{K} \varepsilon_{k}^{2} } }}\mathop \sum \nolimits_{k = 1}^{K} 2\varepsilon_{k} \left( { - X_{jk} } \right)}}{{\sqrt {\frac{1}{k}\mathop \sum \nolimits_{k = 1}^{K} \varepsilon_{k}^{2} } }}} \right) = k\frac{{\mathop \sum \nolimits_{k = 1}^{K} \varepsilon_{k} X_{jk} }}{{\mathop \sum \nolimits_{k = 1}^{K} \varepsilon_{k}^{2} }},$$

So,

$$\left( {\frac{{\lambda \varepsilon_{k} }}{\sigma }} \right)^{{\prime }} = \frac{{\lambda \varepsilon_{k}^{{\prime }} \sigma - \lambda \varepsilon_{k} \sigma^{{\prime }} }}{{\sigma^{2} }} = \frac{{ - \lambda \sigma X_{jk} + \lambda \varepsilon_{k} \frac{{\mathop \sum \nolimits_{k = 1}^{K} \varepsilon_{k} X_{jk} }}{{\sqrt k \sqrt {\mathop \sum \nolimits_{k = 1}^{K} \varepsilon_{k}^{2} } }}}}{{\sigma^{2} }}.$$

Now, first, the partial derivative of $l(\beta ,\lambda )$ with respect to $\beta$ is:

$$\begin{aligned} \frac{\partial }{{\partial \beta_{j} }}l\left( {\beta ,\lambda } \right) & = - k\left( {\frac{{\sigma^{{\prime }} }}{\sigma }} \right) + \sum\limits_{k = 1}^{K} {\frac{{\phi \left( {\frac{{\lambda \varepsilon_{k} }}{\sigma }} \right)}}{{\varPhi \left( {\frac{{\lambda \varepsilon_{k} }}{\sigma }} \right)}}} \left( {\frac{{\lambda \varepsilon_{k} }}{\sigma }} \right) \\ & = k\frac{{\mathop \sum \nolimits_{k = 1}^{K} \varepsilon_{k} X_{jk} }}{{\mathop \sum \nolimits_{k = 1}^{K} \varepsilon_{k}^{2} }} + \sum\limits_{k = 1}^{K} {\frac{{\phi \left( {\frac{{\lambda \varepsilon_{k} }}{\sigma }} \right)}}{{\varPhi \left( {\frac{{\lambda \varepsilon_{k} }}{\sigma }} \right)}}} \frac{{\lambda \left( { - \sigma X_{jk} + \varepsilon_{K} \frac{{\mathop \sum \nolimits_{k = 1}^{K} \varepsilon_{k} X_{jk} }}{{\sqrt k \sqrt {\mathop \sum \nolimits_{k = 1}^{K} \varepsilon_{k}^{2} } }}} \right)}}{{\sigma^{2} }} \\ & = - \frac{\lambda }{\sigma }\sum\limits_{k = 1}^{K} {\frac{{\phi \left( {\frac{{\lambda \varepsilon_{k} }}{\sigma }} \right)}}{{\varPhi \left( {\frac{{\lambda \varepsilon_{k} }}{\sigma }} \right)}}} X_{jk} + \frac{{\mathop \sum \nolimits_{k = 1}^{K} \varepsilon_{k} X_{jk} }}{{\sigma^{2} }}\left( {1 + \frac{\lambda }{k\sigma }\sum\limits_{k = 1}^{K} {\frac{{\phi \left( {\frac{{\lambda \varepsilon_{k} }}{\sigma }} \right)}}{{\varPhi \left( {\frac{{\lambda \varepsilon_{k} }}{\sigma }} \right)}}\varepsilon_{k} } } \right). \\ \end{aligned}$$

Second, the partial derivative of $l\left( {\beta ,\lambda } \right)$ with respect to $\lambda$ is:

$$\frac{\partial }{\partial \lambda }l\left({\beta ,\lambda } \right) = \sum\limits_{k = 1}^{K} {\frac{{\phi \left({\frac{{\lambda \epsilon_{k} }}{\sigma }} \right)}}{{\varPhi \left({\frac{{\lambda \epsilon_{k} }}{\sigma }} \right)}}\frac{{\epsilon_{k} }}{\sigma }} .$$

Appendix B: Getting Started with Excel Solver for SFA and DEA Analyses

2.2.1 Introducing, Getting and Installing Excel Solver

In this chapter and Chap. 4, this book uses Excel Solver to solve optimization problems such as maximum likelihood estimation for SFA, DEA LP problem, and chance-constrained stochastic programming problem. Although the major PC-based spreadsheets provides built-in optimizers, Excel Solver is considered the most widely used optimization software today in the world because of its simple user interface without a need of knowing that the calculations inside the Excel Solver performs are heavily complex in reality. Excel Solver provides three available solving methods such as Simple LP (Linear Programming) method, GRG (Generalized Reduced Gradient) Nonlinear method, and Evolutionary method. An overview of each solving method is discussed in Fig. 2.6.

Fig. 2.6

Three methods provided by Excel Solver

This Appendix illustrates three examples: (1) SFA parameters estimation (2) DEA LP problem and (3) traveling compressed air expert problem, with an attempt to use GRG method, Simplex LP method and evolutionary method, respectively. However, this Appendix is not an introduction to all of Excel Solver, but to the selected parts of Excel Solver used in the book. For further instructions, see An Introduction to Spreadsheet Optimization Using Excel Solver, which is available on http://www.meiss.com/download/Spreadsheet-Optimization-Solver.pdf. Another useful introduction is Step-By-Step Optimization with Excel Solver, which is available on http://excelmasterseries.com/D-_Loads/New_Manuals/Step-By-Step_Optimization_S.pdf and provides a detailed introduction for beginners to successfully implement Excel Solver, in particular, by separating a problem solving path into 6 steps such as (1) Step 1—Determine the objective (2) Step 2—Determine the decision variables (3) Step 3—Build the Excel equations that combine the objective with all decision variables (4) Step 4—List all constraints (5) Step 5—Test the Excel Spreadsheet (6) Step 6—Insert all data into the Solver dialog box.

Excel Solver is available in the Analysis group on the Data tab in Excel as Fig. 2.7. If the Data tab does not have the choice Solver available, then the following installation procedure should be taken. Note that this guide is based on Microsoft Excel 2013 version.

• 1. Open Office Button|Excel Options to see if the Add-Ins option appears

• 2. If the Add-Ins option appears, select Excel Add-Ins from the drop down dialog box

• 3. Click Go then Solver is enabled.

• 4. If the Add-Ins option does not appear, run the Setup program again to install it

Fig. 2.7

Excel Solver in data tab|analysis group

Once Excel Solver is installed, it can be used.

2.2.2 SFA Parameters Estimation Using GRG Method

This example is made to provide an opportunity to learn how to use the generalized reduced gradient (GRG) method of Excel Solver. This method aims to solve nonlinear optimization problem with smooth functions—however, the solution may be a global optimal solution or only locally optimal solutions. In case that any of the functions or constraints are nonconvex, it may obtain locally optimal solutions. If all function and all constraints are convex, it is likely to obtain globally optimal solution. Problems including maximum likelihood estimation or non-linear regression can be solvable with GRG method. This example will use the SFA analysis example discussed in this chapter.

2.2.2.1 Define Problem, Objective and Decision Variables

This example problem aims to determine and to measure the effectiveness of energy reduction initiatives in terms of a technical improvement that corresponds to a certain structural change in industry-wide energy efficiency between two distinct time periods by proposing a benchmarking model: SFA (stochastic frontier analysis) model based on Hicksian neutral technological change concept.

The proposed SFA model for electricity is:

$$\begin{aligned} E_{i} /Y_{i} & = A +\upbeta_{1} WBASE_{i} +\upbeta_{2} HDD_{i} +\upbeta_{3} HDD_{i}^{2} \\ & \quad +\upbeta_{4} CDD_{i} +\upbeta_{5} CDD_{i}^{2} +\upbeta_{6} Util_{i} +\upbeta_{7} Year_{i} + u_{i} - {\text{v}}_{i} \\ \end{aligned}$$

where:

• $E_{i}$: Total site electricity use at plant i in kWh;

• $Y_{i}$: Number of vehicles produced;

• $WBASE_{i}$: Wheelbase (the distance between its front and rear wheels) of the largest vehicle produced in the plant in inch;

• $HDD_{i}$: Thousand heating degree days for the plant location and year;

• $HDD_{i}^{2}$: $HDD_{i}$ squared;

• $CDD_{i}$: Thousand cooling degree days for the plant location and year;

• $CDD_{i}^{2}$: $CDD_{i}$ squared;

• $Util_{i}$: Plant utilization rate, defined as output/capacity, where the denominator, capacity is a normalized capacity defined as equal to capacity line rate (or job per hour) × 235 working days × 16 working hours per day;

• $Year_{i}$: t and $t +\Delta t$ where $\Delta t$ is the time period at which a significant technical improvement in energy efficiency is observed; and

• β: Vector of parameters to be estimated.

The SFA model above requires several parameters to be estimated, such as β, $\upsigma_{v}^{2}$ and $\upsigma_{u}^{2}$. This work uses the maximum likelihood method for parameter estimation.

The log likelihood function can be expressed as:

$$\begin{aligned} l\left( {\beta ,\sigma^{2} ,\lambda } \right) & = - \frac{1}{2}N\log \left( {\frac{\pi }{2}} \right) \\ & \quad - \frac{1}{2}N\log \sigma^{2} + \sum\limits_{i = 1}^{N} {\log \varPhi } \left( {\frac{{\lambda (y_{i} - f(x_{i} ;\beta ))}}{{\sqrt {\sigma^{2} } }}} \right) \\ & \quad - \frac{1}{{2\sigma^{2} }}\sum\limits_{i = 1}^{N} {(y_{i} - f(x_{i} ;\beta ))^{2} } . \\ \end{aligned}$$

The objective of this example is to estimate parameters (decision variables) β, $\upsigma^{2}$ and λ on the data $(x_{1} ,y_{1} ), \ldots ,(x_{N} ,y_{N} )$ in such a way that maximizes $l(\upbeta,\upsigma^{2} ,\uplambda)$ that is the log-likelihood function. The data set are shown below.

2.2.2.2 Build Excel Equations Associating the Objective and Decision Variables

Decision variable cells are in cells C4 to J4 representing $\upbeta$ and D28 representing $\uplambda$. The formula of each cell is as follows.

Similar to formulas in Column K , columns L , M , L , O , and P contains pertinent formulas as follows:

• L: $\varepsilon = y_{i} - f\left( {x_{i} ;\upbeta} \right)$

• M: $\varepsilon^{2}$

• N: $\log \varPhi \left( {\frac{{\lambda (y_{i} - f(x_{i} ;\beta ))}}{{\sqrt {\sigma^{2} } }}} \right)$ where $\upsigma^{2}$ replaced with $\frac{1}{N}\sum\nolimits_{i = 1}^{N} {(y_{i} - f(x_{i} ;\upbeta))^{2} }$

• O: $u = y_{i} - f\left( {x_{i} ;\upbeta} \right)$

• P: $\begin{aligned} EPI & = probability\left( {energy\;inefficiency \ge E_{i} /Y_{i} - f\left( {X;\upbeta} \right) + {\text{v}}_{i} } \right) \\ & = 1 - F(E_{i} /Y_{i} - f\left( {X;\upbeta} \right) + {\text{v}}_{i} ) \end{aligned}$

.

Other cells used to estimate decision variables are as follows:

2.2.2.3 Find Initial Decision Variables Using OLS (Ordinary Least Square)

Decision variable cells require initial values. Cells C4 to J4 representing $\upbeta$ take values found by ordinary linear regression as their initial values while and D28 representing $\uplambda$ is set to 1 as its initial value. Therefore, the formula of each cell is as follows.

2.2.2.4 Insert All Data into Excel Solver Box

The objective is set to F28 representing $l(\upbeta,\upsigma^{2} ,\uplambda)$ with a maximization option chosen and variable cells are set to C4 to J4 representing β and D28 representing λ. This problem does not need any constraint. Note that s solving method is set to GRG Nonlinear .

2.2.2.5 Understand the Results from Excel Solver

Once the Solve button in the previous Excel Solver Box is hit, the solution appears as shown below. Cells from C4 to J4 shows the final parameters for β.

Note that the one-sided likelihood-ratio test value (LR) in cell K28 for this model reveals that the model is adequate at the 99.5 % significance level $( 8. 1 7 > \chi_{1 - 2 \times 0.005}^{2} (1) = 6. 6 3 5)$ and that the model has very little error attributable to random noise, with most departures attributable to inefficiency. Therefore, the null-hypothesis, $H_{0}{:}\gamma = \frac{{\upsigma_{u}^{2} }}{{(\upsigma_{\text{v}}^{2} +\upsigma_{u}^{2} )}} = 0$, is rejected, and the alternative hypothesis $H_{1}{:}\upgamma > 0$ with technical inefficiency effect is accepted for this model.

The GRG method generates Answer Report. The Answer Report tells an important information that is how long Solver took to solve the problem. In this case, the total run time was just 0.03 s.

The Answer Report also provides the information about object, variable cells and constraints as below:

2.2.3 DEA LP Problem Solving Using Simplex LP

This example is made to provide an opportunity to learn how to use the Simplex method of Excel Solver. This method aims to solve LP (Linear Programming) optimization problems and find a globally optimal solution. The best possibly solution meets all constraints globally to be an optimal solution at the point where 2 or more Constraints intersect because of Karush–Kuhn–Tucker conditions. Data Envelopment Analysis and Knapsack Problem can be solvable with Simplex method. This example will use the DEA example discussed in this chapter.

2.2.3.1 Define Problem, Objective and Decision Variables

This example problem aims to calculate the efficiency of the t-th time period relative to t-th time period technology, that is, $D_{I}^{t} (Z_{t} ,E_{t} /Y_{t} )$, which is identical to the following DEA model

$$\begin{aligned} & D_{I}^{t} (Z_{t} ,E_{t} /Y_{t} ) = { \hbox{min} }_{{\upphi,\uplambda}}\upphi \\ s.t.\quad & {-}Z_{it} + Z_{t}\uplambda \ge 0, \\ & {-}\upphi\left( {E_{it} /Y_{it} } \right) + \left( {E_{t} /Y_{t} } \right)\uplambda \le 0 \\ &\uplambda \ge 0 \\ \end{aligned}$$

Note that this LP is input-oriented and CRS (Constant Return Scale) is assumed. The dataset for this example is shown below. 10 DMUs (decision making units), each with input (energy intensity) and outputs (HDD, plant utilization, wheelbase) are provided.

2.2.3.2 Build Excel Equations Associating the Objective and Decision Variables

The objective of this example is to determine λ and ϕ in such a way to minimize ϕ (technical efficiency). The objective cell is H25 represenging ϕ. The decision variable cells are from H4 to H13 representing λ. Note that this constraint cells use the Excel INDEX function which is used to locate the data which corresponds to the pertinent DMU that appears in the column H25 . The INDEX function has a formula, that is  =   INDEX (range, row number, column number) .

2.2.3.3 Insert All Data into Excel Solver Box

This example uses VBA in Excel and automates iterations for solving multiple linear programing models with the Simplex method. Briefly, with respect to automation logic, the tool uses “For” loop to automate iterations of solving multiple linear programing models in which Excel Solver with the “Simplex” optimization option calculates the efficiency for each DMU and the results are recorded in a table using the copy/paste function (note: the three major functions used in the loop statement of the VBA programing are as follows: (1) “SolverOk”—defines the objective function and the decision variables; (2) “SolverAdd”—defines model constraints; and (3) “SolverSolv”—runs Solver). The detailed codes are shown below:

Whenever SolverRun() is called, the solver toolbox is filled and run invisibly.

2.2.3.4 Understand the Results from Excel Solver

Once the RUN DEAs button in the spreadsheet is hit, the solution appears as shown below. Cells from I4 to I13 shows each DMU’s technical efficiency representing ϕ. Meanwhile, cells from J4 to S4 shows λ when 1st DMU is considered as a target comparing DMU. Similarly, cells from J to S associating with each row from 5 to 13 rows shows λ for each DMU.

2.2.4 Traveling Compressed Air Expert Problem Using Evolutionary Method

This example is made to provide an opportunity to learn how to use the evolutionary method of Excel Solver. The evolutionary method is used the objective contains any cells holding non-smooth or discontinuous formulas. Excel functions such as INDEX , LOOKUP are common discontinuous functions while MIN , MAX and ABS are common non-smooth Excel functions. This example is modified from the traveling salesman problem located in Step-By-Step Optimization with Excel Solver in the context.

2.2.4.1 Define Problem, Objective and Decision Variables

Assume that a compressed air expert must visit 5 GM engine plants located in US and Canada in order to audit plant’s compressed air use practice. He must pick the shortest path that will reach every plants and bring him back to his starting point. In this example, the evolutionary method is used because the objective contains INDEX and LOOKUP Excel functions, which are discontinuous functions. In addition, this example illustrates how to use Alldifferent constrain when solving the problem.

The problem in this example is formally defined as follows: a compressed air expert must make stops in 5 cities where GM engine plants are located: Flint (MI, USA), Spring Hill (TN, USA), St. Catherines (ON, Canada), Romulus (MI, USA), Tonawanda (NY, USA) in such as way that the total length of the trip is minimized. See the distance chart above to refer to the distance between cities in mile.

In this case, the objective is to minimize the total distance travelled when traveling between all 5 cities. The evolutionary method is used to minimize the objective. The decision variables are the order of cities to visit. To specify the order, each city is designated by the row that they appear in the distance chart. For example, Flint appears in the 1st row of the distance chart and therefore, Flint is designated with a “1”. Similar to Flint, other cities will be designated by its pertinent row number. At the end, Excel solver will determine the order of cities to visit to minimize the total miles travelled.

2.2.4.2 Build Excel Equations Associating the Objective and Decision Variables

The decision variables are in cells B11 to B15 . The order of the decision variables shown below (1, 2, 3, 4, 5) indicates that the expert will visit the cities in this order: Flint (row 1 in the distance chart) → Spring Hill (row 2 in the distance chart) → St. Catherines (row 3 in the distance chart) → Romulus (row 4 in the distance chart) → Tonawanda (row 5 in the distance chart) → Flint.

The objective is in cell D17 where D17   =   SUM(D11:D15) .

The formula of each cell is as follows. Note that the Excel INDEX function is used to locate the city which corresponds to the Distance Chart row number that appears in the column B . The INDEX function has a formula, that is  =   INDEX (range, row number, column number) .

2.2.4.3 Insert All Data into Excel Solver Box

This problem uses Alldifferent constraint because the problem requires that the expert must visit each city only once without repeating. Alldifferent constraint ensures that each city will be visited only once and that all cities will be visited by grouping B11:B15 cells simultaneously in which the five cells hold the integers 1–5 and no 2 cells in this group will be assigned the same number. Excel Solver Box shown above is set to have D17 as objective cell and B11:B15 as decision variables cells. Note that the evolutionary method is set to its solving method.

2.2.4.4 Understand the Results from Excel Solver

Once the Solve button in the previous Excel Solver Box is hit, the solution appears as shown above. The solution could be interpreted as follows: The compressed air experts starts in Spring Hill. He then visits Flint, St. Catherine, Tonawanda, and finally back to Spring Hill in that order. The total miles travelled on this route are 1,656 miles. This is the shortest route that will cover all 5 cities starting and ending in Spring Hill. This solution improves the original solution by 23.7 % (=(2,171 − 1,656)/2,171).

The evolutionary method generates two reports: Answer Report and Population Report. The Answer Report tells an important information that is how long Solver took to solve the problem. Especially it is important because the evolutionary method can controlled by the Option settings where the options such as the maximum allowable run time, iterations, or subproblems are available for control. In this case, the total run time was 57 s.

The Answer Report also provides the information about variable cells and constraints. Note the difference of decision variables between their original and final values. Also, note that the unique Alldifferent constraint is bound meaning that no slack is still available.

The Population Report gives useful information about the entire population of candidate solutions maintained by the Evolutionary Solving method at the end of the solution process. With the Population Report, a modeler gets some insight into the performance of the Evolutionary method and can decide whether additional runs of the Evolutionary method are likely to yield even better solutions. For each variable and constraint, the Population Report shows the best value found by the Evolutionary method, and the mean (average) value, standard deviation, maximum value, and minimum value of that variable or constraint across the entire population of candidate solutions at the end of the solution process as shown above. These values gives an idea of the diversity of solutions represented by the population. From the sense, the way of interpreting the Population Report is important. For example, if the Best Values are similar from run to run (i.e., the Standard Deviations are small), this may be reason for the high confidence that the final solution is close to the global optimum. However, if the Best Values vary from run to run (i.e., the Standard Deviations are large) might indicate a lack of diversity in the population, suggesting that the modeler should increase the Mutation Rate and run Excel Solver again.