Solving electronic spreadsheet models by interactive multicriterion optimization: a budgeting illustration

In previous papers a connection between general spreadsheet models and interactive multicriterion optimization has been discussed. The purpose of the present paper is to provide proof of the concept with a detailed example. The main idea is that interactive multicriterion optimization can be viewed as a new way of interacting with spreadsheet models. It offers advantages over the two prevailing ones, namely what-if and goal-seeking. A simplified budgeting example is given to clarify this connection. Directions of improvement are assumed to be provided by the decision maker using one of the several existing methods. One improvement iteration is illustrated and analyzed following the method of Loganathan and Sherali.     

Method of forces for direction finding in interactive multicriterion optimization applications

The main purpose of this paper is to derive, illustrate, and validate a method of direction finding for use in multicriterion interactive optimization applications. A secondary purpose is to consider electronic spreadsheet operation as an instance of interactive multicriterion optimization and to test the new method in a spreadsheet model for aggregate production planning. The method derived here amounts to virtual direct specification of the gradient direction, but does so by an appeal to the simple physical notion of forces applied to the criteria. It is demonstrated that the idea is easily grasped and also gives effective performance in the application tested.The authors are indebted to Professor Carl Langenhop, Mathematics Department, Southern Illinois University at Carbondale for several useful suggestions.     

A spreadsheet decision support optimization model for railcar storage at Canadian Pacific Railway

Canadian railway companies operate in a capital-intensive segment of the transportation industry. In most railway companies, the covered hopper railcar fleet is one of the larger fleets due to its use in moving grain and potash, commodities that move large volumes of product. This railcar fleet is also difficult to manage due to demand seasonality and joint commodity use. This paper demonstrates how an aggregate planning model can be used to support decision making related to optimization of covered hopper railcar storage. Exploratory research prior to model development involved interviews with company personnel. The model was developed through quantitative research and implemented using spreadsheet optimization. The results indicate that using this model can reduce the total cost of storage through effective planning. The model also provided insight to improve railcar storage such as the elimination of excess storage locations and the need to do further investigation. The company is in the process of implementing suggestions from this paper.     

A spreadsheet-based optimization model for the integrated problem of producing and distributing a major weekly newsmagazine

In this paper, we address the problem of producing and distributing the Brazilian newsmagazine Época, a major weekly publication with one of the 10 largest circulations in the world. This real-world problem had been puzzling magazine publishers in Brazil and remained unsolved for many years. We propose an innovative mixed-integer-linear programming model to determine the number and location of the industrial facilities that should produce the magazines, what destinations should be assigned to each selected facility; the production sequencing and the modes of transportation (air or truck). Our model aims to minimize the total cost while adhering to production capacity and time constraints. The model was implemented in an electronic spreadsheet environment and yielded a savings of 7.1% of the total costs. Given that despite their huge popularity, little has been written on the issues of implementing full-scale optimization models in spreadsheets; thus we also provide the details of the model’s implementation in Excel.     

Spreadsheet Modelling Abuse: An Opportunity for OR?

Spreadsheet models are increasingly being used in decision making within organizations. With questions about the quality of these models, an investigation was conducted into the spreadsheet practices in ten firms, with an emphasis on the process of building spreadsheet models. The study showed that spreadsheet models were usually built in an informal, iterative manner, by people from all organizational levels. These people had received very little training in the building of models, which could help explain why at least 25% of the models contained errors. Other problems were also found. It was evident that the spreadsheet practices in the firms were inadequate. There is a need for increased training as well as setting and enforcing organizational spreadsheet standards. This could provide an opportunity for OR workers if carefully handled. Although the study was viewed as exploratory, it indicated a need for further study into the effect of formal design practices on the incidence of errors and model creation time.     

An efficient and simple model for multiple criteria supplier selection problem

Simply looking for vendors offering the lowest prices is not “efficient sourcing” any more. Selection of suppliers is a multiple criteria decision. We propose a weighted linear program for the multi-criteria supplier selection problem. In addition to mathematical formulation, this paper studies a transformation technique which enables our proposed model to be solved without an optimizer. The model for multi-criteria supplier selection problem can be easily implemented with a spreadsheet package. The model can be widely applied to practical situations and does not require the user with any optimization background.     

A framework for building spreadsheet based decision models

Spreadsheet based decision modelling is widely used in business today. The functionality provided within the current integrated packages allows users to develop very complex business models. Unfortunately, most spreadsheet users do not follow any particular methodology when building spreadsheet based models. This in turn leads to many wasted hours of debugging and re-programming and often produces models that look more complex than they need to be and are also tedious to extend or modify. This paper presents an example of the type of framework that can be used when developing spreadsheet based business models. The framework encourages the efficient development of more effective models that are easy to use and easy to modify.     

Importance of verifying queue model assumptions before planning with simulation software

This case study uses empirical data gathered at an Australian refinery to verify the assumptions for queue distributions before using special-purpose software to plan the off-road-truck hauling of titanium dioxide to a refinery (n = 773). Easy-to-use spreadsheet software is utilized to verify assumptions for queue models. Managers are able to make decisions based on economic implications of queue models to avoid making costly planning mistakes. Analysts can use nonparametric hypothesis-testing techniques to verify distribution assumptions for optimization without having to write hard-to-maintain and complex algebraic linear equations or nonlinear search routines.     

Managing risks in information systems outsourcing: An approach to analyzing outsourcing risks and structuring incentive contracts

Information systems outsourcing is now almost standard practice for many companies. Outsourcing the information processing activities is a complex issue that entails considerable implications for the strategy of the firm. An important mechanism for managing the performance of outsourcing vendors is incentive contracts. But to develop an outsourcing contract the IS manager must quantify risks and benefits. However methods and tools for analyzing and quantifying outsourcing risks that IS managers have at their disposal are rudimentary. In this paper we offer a method and some mathematical models for analyzing risks and constructing incentive contracts for IS outsourcing. We are aware that most managers do not like to use mathematical models, consequently we have minimized the technical discussion and have illustrated how this model could be implemented using spreadsheet software for ease of use.     

NAMING A COLUMN ON A SPREADSHEET

Spreadsheets use a meaningful algebra-like notation which, research suggests, can support pupils in developing an understanding of variables. This paper discusses the activity of Year 8 pupils who were taught to name a column on a spreadsheet, and who were asked to reflect upon their activity in a stimulated recall interview. More specifically, it considers the pupils' understanding of notation, such as 'A2' and 'm', which they used when constructing spreadsheet formulae. It is suggested that experience of naming columns may help pupils to develop a clearer sense of the notation as a variable, and to make links between their spreadsheet activity and use of standard algebraic notation [1].     

A Case Study in System Dynamics Optimization

This paper presents the use of optimization as a tool for policy analysis and design in system dynamics models, and presents a demonstration of its use on the ‘project model’ developed by G. P. Richardson and A. L. Pugh III in their book Introduction to System Dynamics Modelling with DYNAMO. The use of optimization to design parameters, table functions and new model structure is shown to produce a very significantly improved performance for this model compared to conventional approaches.

     

A clone-based graphical modeler and mathematical model generator for optimal production planning in process industries

This paper outlines a visually interactive graphical modeling approach for process type production systems, with hidden generation of complex optimization models for production planning. The proposed system lets the users build a graphical model of the production system with one-to-one clones of its production units through its interactive visual interface, accepts production-specific data for its components, and finally, internally generates and solves its mathematical programming model without any interaction from the user. This “clone-based” modeling approach allows the continued use of optimization models with minimal mathematical programming understanding, as generation of mathematical model by clones is hidden and automatic, therefore maintenance-free: Updating graphical production system models is enough for renewing internal optimization models. The concept is demonstrated in this paper with a linear programming prototype developed for a petroleum refinery.     

Formulations for a problem of petroleum transportation

Oil tankers play a fundamental role in every offshore petroleum supply chain and due to its high price, it is essential to optimize its use. Since this optimization requires handling detailed operational aspects, complete optimization models are typically intractable. Thus, a usual approach is to solve a tactical level model prior to optimize the operational details. In this case, it is desirable that tactical models are as precise as possible to avoid too severe adjustments in the next optimization level. In this paper, we study tactical models for a crude oil transportation problem by tankers. We did our work on the top of a previous paper found in the literature. The previous model considers inventory capacities and discrete lot sizes to be transported, aiming to meet given demands over a finite time horizon. We compare several formulations for this model using 50 instances from the literature and proposing 25 new harder ones. A column generation-based heuristic is also proposed to find good feasible solutions with less computational burden than the heuristics of the commercial solver used.     

Valuating residential real estate using parametric programming

When the estimation of the single equation multiple linear regression model is looked upon as an optimization problem, we show how the principles and methods of optimization can assist the analyst in finding an attractive prediction model. We illustrate this with the estimation of a linear prediction model for valuating residential property using regression quantiles. We make use of the linear parametric programming formulation to obtain the family of regression quantile models associated with a data set. We use the principle of dominance to reduce the number of models for consideration in the search for the most preferred property valuation model (s). We also provide useful displays that assist the analyst and the decision maker in selecting the final model (s). The approach is an interface between data analysis and operations research.     

The application of an oblique-projected Landweber method to a model of supervised learning

This paper brings together a novel information representation model for use in signal processing and computer vision problems, with a particular algorithmic development of the Landweber iterative algorithm. The information representation model allows a representation of multiple values for a variable as well as an expression for confidence. Both properties are important for effective computation using multi-level models, where a choice between models will be implementable as part of the optimization process. It is shown that in this way the algorithm can deal with a class of high-dimensional, sparse, and constrained least-squares problems, which arise in various computer vision learning tasks, such as object recognition and object pose estimation. While the algorithm has been applied to the solution of such problems, it has so far been used heuristically. In this paper we describe the properties and some of the peculiarities of the channel representation and optimization, and put them on firm mathematical ground. We consider the optimization a convexly constrained weighted least-squares problem and propose for its solution a projected Landweber method which employs oblique projections onto the closed convex constraint set. We formulate the problem, present the algorithm and work out its convergence properties, including a rate-of-convergence result. The results are put in perspective with currently available projected Landweber methods. An application to supervised learning is described, and the method is evaluated in an experiment involving function approximation, as well as application to transient signals.     

Synergies from spreadsheet LP used with the theory of constraints—a case study

This paper describes a case study relating to a food-processing plant. The methods used to address the case include spreadsheets combined with Goldratt's Theory of Constraints (TOC), both of which are accessible to practising managers. The paper demonstrates how standard spreadsheet optimisation tools can be used in combination with a TOC framework to provide effective decision aids. The results from the case study indicate real productivity improvements are possible from even small models of a situation. The paper explores the interrelationships and complementaries between Linear Programming and Theory of Constraints frameworks, and details the steps involved in using them in combination. We argue that traditional LP practice should be modified in light of the ease with which LPs can be solved, and suggest that the Theory of Constraints approach provides a useful framework to guide LP use. We share some of the insights gained both by the analysts and by the company.     

No-arbitrage conditions,scenario trees,and multi-asset financial optimization

Many numerical optimization methods use scenario trees as a discrete approximation for the true (multi-dimensional) probability distributions of the problem’s random variables. Realistic specifications in financial optimization models can lead to tree sizes that quickly become computationally intractable. In this paper we focus on the two main approaches proposed in the literature to deal with this problem: scenario reduction and state aggregation. We first state necessary conditions for the node structure of a tree to rule out arbitrage. However, currently available scenario reduction algorithms do not take these conditions explicitly into account. State aggregation excludes arbitrage opportunities by relying on the risk-neutral measure. This is, however, only appropriate for pricing purposes but not for optimization. Both limitations are illustrated by numerical examples. We conclude that neither of these methods is suitable to solve financial optimization models in asset–liability or portfolio management.     

Decision support for strategic energy planning: A robust optimization framework

Optimization models for long-term energy planning often feature many uncertain inputs, which can be handled using robust optimization. However, uncertainty is seldom accounted for in the energy planning practice, and robust optimization applications in this field normally consider only a few uncertain parameters. A reason for this gap between energy practice and stochastic modeling is that large-scale energy models often present features—such as multiplied uncertain parameters in the objective and many uncertainties in the constraints—which make it difficult to develop generalized and tractable robust formulations. In this paper, we address these limiting features to provide a complete robust optimization framework allowing the consideration of all uncertain parameters in energy models. We also introduce an original approach to make use of the obtained robust formulations for decision support and provide a case study of a national energy system for validation.     

Experiences in using a contingency factor-based validation methodology for spreadsheet DSS

The use of spreadsheets has become routine in all aspects of business with usage growing across a range of functional areas and a continuing trend towards end user spreadsheet development. However, several studies have raised concerns about the accuracy of spreadsheet models in general, and of end user developed applications in particular, raising the risk element for users. High error rates have been discovered, even though the users/developers were confident that their spreadsheets were correct. The lack of an easy to use, context-sensitive validation methodology has been highlighted as a significant contributor to the problems of accuracy. This paper describes experiences in using a practical, contingency factor-based methodology for validation of spreadsheet-based DSS. Because the end user is often both the system developer and a stakeholder, the contingency factor-based validation methodology may need to be used in more than one way. The methodology can also be extended to encompass other DSS.     

Investment timing and optimal capacity choice for small hydropower projects

This paper presents a method for assessing small hydropower projects that are subject to uncertain electricity prices. We present a real options-based method with continuous scaling, and we find that there is a unique price limit for initiating the project. If the current electricity price is below this limit it is never optimal to invest, but above this limit investment is made according to the function for optimal size. The connection between the real option and the physical properties of a small hydropower plant is dealt with using a spreadsheet model that performs a technical simulation of the production in a plant, based on all the important choices for such a plant. The main results of the spreadsheet are simulated production size and the investment costs, which are in turn used for finding the value of the real option and the price limit. The method is illustrated on three different Norwegian small hydropower projects.