روش شناسی حمایت از تصمیم گیری برای برنامه ریزی خطی تصادفی چندمعیاره با استفاده از صفحات گسترده

In recent years, tools for solving optimization problems have become widely available through the integration of optimization software (or solvers) with all major spreadsheet packages. These solvers are highly effective on traditional linear programming (LP) problems with known, deterministic parameters. However, thoughtful analysts may rightly question the quality and robustness of optimal solutions to problems where point estimates are substituted for model parameters that are stochastic in nature. Additionally, while many LP problems implicitly involve multiple objectives, current spreadsheet solvers provide no convenient facility for dealing with more than one objective. This paper introduces a decision support methodology for identifying robust solutions to LP problems involving stochastic parameters and multiple criteria using spreadsheets.

Linear programming (LP) is a mathematical programming technique designed to optimize a (single) linear objective function subject to a linear constraint set where all model parameters are assumed to be known with certainty. Over the past several decades, numerous applications for LP have been proposed for improving the efficiency of business operations [26]. Unfortunately, uncertainty in such things as costs, demand, interest rates, production yields, and equipment reliability is the practical reality faced by business decision makers (DMs) on a daily basis. As a result, some degree of randomness or uncertainty is likely to impact the parameter estimates used in medium- to long-term LP planning models and may also affect short-term models [6]. When one or more parameters in an LP problem are represented by a random variable, a stochastic LP problem results [26]. Effectively modeling the uncertainties in a stochastic LP model is a challenging yet necessary step in evaluating the robustness of a potential “optimal” solution to simultaneous changes in estimated parameter values. This is complicated by the fact that today's information-rich business environment imposes numerous competitive, political, financial, environmental, and societal pressures on DMs [4]. The inherent non-commensurate and conflicting nature of these pressures often makes it difficult or impossible to formulate a single objective function for many real-world decision problems. In these situations, multi-criteria optimization techniques must be used to allow DMs to properly model and analyze the trade-offs inherent with multiple conflicting objectives [29]. As managerial DMs become increasingly aware of the availability and purpose of the solver optimization software built into today's spreadsheets, questions about how to use this software with decision problems involving stochastic parameters and multiple criteria will likely emerge. A significant amount of research has been directed at developing techniques for solving stochastic programming problems. A similar level of effort has been devoted to challenging problems in multi-criteria optimization. Unfortunately, most of the resulting solution techniques are not easily understood by, available to, or implemented by practitioners in the business world. Additionally, relatively little work has focused on devising general solution procedures for optimization problems that are both stochastic and multi-criteria in nature. The objective of this paper is to develop an easily understood methodology for solving stochastic, multi-criteria LP problems in spreadsheets. We begin with a review of the research literature followed by a description of our proposed methodology. We then illustrate this methodology using a stochastic, multi-criteria production planning problem implemented in Microsoft Excel. Finally, we highlight the benefits of our methodology and offer some closing comments.

This paper introduces a methodology for solving stochastic multi-criteria LP problems and provides an example of how Microsoft Excel can be used to implement this methodology. The flexibility provided by VBA allows developers and practitioners to write code directly in Excel modules and worksheets using the Visual Basic editor included with Excel. VBA statements can be written to perform both complex and simple mathematical functions and formatting within an Excel workbook. There are a number of benefits associated with using Excel to solve stochastic, multi-criteria LP problems: • Excel is the most widely distributed spreadsheet package in the world [10], • Excel provides a user-friendly environment for setting up and solving various optimization problems, and • Excel provides a robust set of built-in data analysis tools and features that can be used to sort, summarize, and display important information used for decision making. A motivating factor for this paper is to provide users with a methodology that is easy to understand, flexible, and allows them to take advantage of numerous built-in features associated with a readily available software package. Through the use of dynamic scenario generation using a random number generator, a collection of possible candidate solutions can be considered for any stochastic LP problem. The candidate solution generated for each scenario is re-evaluated against all of the other randomly generated scenarios in the model. The user can compare and contrast numerous alternatives and view key metrics associated with each candidate solution, such as average cost, the maximum value of a particular variable across a range of possible scenarios, and the average level of service. Using Excel, these results can easily be presented in both graphical and tabular formats. The user is then able to select the most robust solution based on how each individual candidate solution is expected to perform over a wide range of possible parameter values.