Spreadsheet optimizer

For examples that use the Excel Solver, see Chapters 7,9, and 10. For a description of the design and use of the Excel Solver, see Fylstra, et al. (1998). An enhanced version of the Excel Solver, which can handle larger problems, is faster, and includes enhanced solvers is available from Frontline Systems—see www.frontsys.com. This website contains a wealth of information on spreadsheet optimization. 

Optimizers for Stand-Alone Operation or Embedded Applications / 8.9.2 Spreadsheet Optimizers / 8.9.3 Algebraic Modeling Systems... 

With respect to computation, for limited numbers of assets (small /), solutions are easily obtained (although not necessarily efficiently) using standard spreadsheet optimizers. This works for the vast majority of allocation problems because most applications typically include no more than a dozen assets. More specialized optimizers are sometimes necessary when there are many assets. For example, if MV is applied to select a stock portfolio, there may be hundreds of securities used as admissible assets. ... 

Spreadsheet Optimization with Excel http //www.youtube.com/watch v=S-W eNsRiZO Linear Programming with Excel Solver http //www.youtube.com/watch v=RicajFzoenk... 

Spreadsheet Applications. The types of appHcations handled with spreadsheets are a microcosm of the types of problems and situations handled with fuU-blown appHcation programs that are mn on microcomputers, minis, and mainframes and include engineering computations, process simulation, equipment design and rating, process optimization, reactor kinetics—design, cost estimation, feedback control, data analysis, and unsteady-state simulation (eg, batch distillation optimization). 

A non-linear regression analysis is employed using die Solver in Microsoft Excel spreadsheet to determine die values of and in die following examples. Example 1-5 (Chapter 1) involves the enzymatic reaction in the conversion of urea to ammonia and carbon dioxide and Example 11-1 deals with the interconversion of D-glyceraldehyde 3-Phosphate and dihydroxyacetone phosphate. The Solver (EXAMPLEll-l.xls and EXAMPLEll-3.xls) uses the Michaehs-Menten (MM) formula to compute v i- The residual sums of squares between Vg(,j, and v j is then calculated. Using guessed values of and the Solver uses a search optimization technique to determine MM parameters. The values of and in Example 11-1 are ... 

As will be seen later, these techniques will prove to be useful when solving design problems in general-purpose software, such as spreadsheets. Many of the numerical problems associated with optimization can be avoided by appropriate formulation of the model. Further details of model building can be found elsewhere12. 

Chapter 3 treats the most common type of objective function, the cost or revenue function. Historically, the majority of optimization applications have involved trade-offs between capital costs and operating costs. The nature of the trade-off depends on a number of assumptions such as the desired rate of return on investment, service life, depreciation method, and so on. While an objective function based on net present value is preferred for the purposes of optimization, discounted cash flow based on spreadsheet analysis can be employed as well. 

If/(x) has a simple closed-form expression, analytical methods yield an exact solution, a closed form expression for the optimal x, x. Iff(x) is more complex, for example, if it requires several steps to compute, then a numerical approach must be used. Software for nonlinear optimization is now so widely available that the numerical approach is almost always used. For example, the Solver in the Microsoft Excel spreadsheet solves linear and nonlinear optimization problems, and many FORTRAN and C optimizers are available as well. General optimization software is discussed in Section 8.9. 

Another type of widely used modeling system is the spreadsheet solver. Microsoft Excel contains a module called the Excel Solver, which allows the user to enter the decision variables, constraints, and objective of an optimization problem into the cells of a spreadsheet and then invoke an LP, MILP, or NLP solver. Other spreadsheets contain similar solvers. For examples using the Excel Solver, see Section 7.8, and Chapters 8 and 9. 

Figure 7.3 displays a Microsoft Excel spreadsheet containing the formulas and data for an LP transportation problem. This spreadsheet is one of six optimization examples included with Microsoft Excel 97. With a standard installation of Microsoft Office, the Excel workbook containing all six examples is in the file... 

GRG2. This code is presently the most widely distributed for the generalized reduced gradient and its operation is explained in Section 8.7. In addition to its use as a stand-alone system, it is the optimizer employed by the Solver optimization options within the spreadsheet programs Microsoft Excel, Novell s Quattro Pro, Lotus 1-2-3, and the GINO interactive solver. 

The Excel Solver. Microsoft Excel, beginning with version 3.0 in 1991, incorporates an NLP solver that operates on the values and formulas of a spreadsheet model. Versions 4.0 and later include an LP solver and mixed-integer programming (MIP) capability for both linear and nonlinear problems. The user specifies a set of cell addresses to be independently adjusted (the decision variables), a set of formula cells whose values are to be constrained (the constraints), and a formula cell designated as the optimization objective. The solver uses the spreadsheet interpreter to evaluate the constraint and objective functions, and approximates derivatives, using finite differences. The NLP solution engine for the Excel Solver is GRG2 (see Section 8.7). 

Crystal Ball can deal with spreadsheets that contain no random variables, and OPTQUEST can be applied to deterministic optimization problems arising from such spreadsheets. Table 10.11 shows the performance of OPTQUEST applied to the two-variable, one-constraint problem defined in Equations (10.7), which was solved by an evolutionary algorithm in Section 10.5 to six-digit accuracy in 1000 iterations. OPTQUEST finds the same solution with similar effort. 

In step 3, a multiline-fitting program was run to optimize the pK a values to minimize the sum of residual squares between calculated and observed mobilities from Eq. (17). Figure 2 shows an example of the MS Excel spreadsheet for pK a calculation. The solver function of MS Excel could be used to perform the multiline-fitting analysis. 

Provides equations for optimizing isothermal gas chromatography with a spreadsheet. 

Satisfactory convergence has been obtained, somewhat faster than with the false position method. Usnally the Newton-Raphson method is indeed the one that converges faster, bnt in some cases it can diverge in the early iterations. It may be helpful to use the false position method for the first one or two iterations, and then switch over to the Newton-Raphson method for further iterations leading to satisfactory convergence. This, and other root-finding procednres, are commonly used by the Solver or Optimizer operation of spreadsheet programs, whose nse is described in Chapter III. 

---