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.     

Performance metrics in supply chain management

This survey paper starts with a critical analysis of various performance metrics for supply chain management (SCM), used by a specific manufacturing company. Then it summarizes how economic theory treats multiple performance metrics. Actually, the paper proposes to deal with multiple metrics in SCM via the balanced scorecard — which measures customers, internal processes, innovations, and finance. To forecast how the values of these metrics will change — once a supply chain is redesigned — simulation may be used. This paper distinguishes four simulation types for SCM: (i) spreadsheet simulation, (ii) system dynamics, (iii) discrete-event simulation, and (iv) business games. These simulation types may explain the bullwhip effect, predict fill rate values, and educate and train users. Validation of simulation models requires sensitivity analysis; a statistical methodology is proposed. The paper concludes with suggestions for a possible research agenda in SCM. A list with 50 references for further study is included.     

Validation of decision-aiding spreadsheets: the influence of contingency factors

This paper describes an investigation into the influence of contingency factors on the validation of spreadsheet-based decision support systems (DSS), and develops a methodological framework for validation that takes account of the effect of contingency factors. The research extends and confirms previous research by one of the authors who identified relevant contingency factors. The perceived influence of these contingency factors on validation effort was investigated by an empirical study, and the results of this study were then used to derive contingency-related guidance for validation. This was evaluated by a sample of practitioners and academics with experience in the development of spreadsheet-based DSS, and was then incorporated into a methodological framework for validation. The framework is designed to enable managers, decision-makers, project leaders and other non-OR specialists to identify the extent of validation effort likely to be required to ensure that the spreadsheet models that they build are valid. The research has implications for the validation of other types of OR models.     

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.     

Strongarm arcade games: a case study in mathematical modelling

Two linked cases are described, that have been used many times over many years with MBA students to teach both basic spreadsheet design and mathematical modelling and to act as a framework to stimulate thought about the wider considerations associated with the application of mathematical models. The broad aspects of the mathematical modelling methodology that are taught are first briefly considered. The simpler case together with its more demanding extension are both presented. The students' approaches are then categorised and the common mistakes/weaknesses and the strength of submissions are listed. This paper closes with a consideration of the value of the cases in student teaching. Solutions to both cases, applying the mathematical modelling methodology in detail, are given in Appendices.     

Improved OR education through the use of spreadsheet models

The first and major part of this presentation shows how OR education may be improved through the use of spreadsheets, with several examples:

• •in decision theory, where the drawing capabilities of Excel are used to represent decision trees and where window switching and macros allow the educator to animate his presentation;
• •in linear programming, where the solver is used (remarks are made relating to the adequacy of the solver's solutions), and where the spreadsheet's graphing capabilities are used both to represent the feasible set and the objective function, and to interactively animate the objective function or constraint movements;
• •in dynamic programming, deterministic or stochastic, where it is shown that several standard spreadsheet functions facilitate the development of specialized models;
• •in simulation, where it will be seen how stochastic simulation may be facilitated through the use of an add-in, and how a decision support system may thus be built from scratch in less than 10 minutes.

A creative use of the spreadsheet in business OR models is presented in the second part of this presentation, with an example in process simulation: airport luggage traffic simulation. A short third part presents the possibilities offered by spreadsheet add-ins.     

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.     

Interactive Optimization Aspects of Electronic Spreadsheet Models for Design and Planning

This paper extends the discussion of the relationship between optimization and electronic spreadsheet modelling. Previous authors have described two connections. These are, first, the use of spreadsheets to facilitate optimization projects and, second, the use of optimization within the spreadsheet model itself such as for cell value computation. In this paper we argue that use of the spreadsheet model for planning and design problems often may actually be viewed as interactive multi-criterion optimization per se. This provides a third connection between optimization and spreadsheet models. We base our arguments on a simplified example arising in insurance consulting work and note other examples as well. We also propose a method for incorporating this observation into existing spreadsheet models and indicate desirable innovations in spreadsheet software.     

Optimacros: Optimization with spreadsheet macros

Macro programs for linear and dynamic programming using only commonly available spreadsheet software are introduced. They enable users of spreadsheets, the number of whom is ever growing in academia, business, industry and government, to model and solve LP's and DP's with single keystrokes or mouseclicks. OptiMacros are available for Lotus 1-2-3 (Release 2) on IBM-PC and compatibles, and for Microsoft Excel on the Apple Macintosh.     

A survey of business failures with an emphasis on prediction methods and industrial applications

The considerable interest in the prediction of business failures is reflected in the large number of studies presented in the literature. Various methods have been used to construct prediction models. This paper provides a review of the literature and a framework for the presentation of this information. Articles can be classified according to the country, industrial sector and period of data, as well as the financial ratios and models or methods employed. Relationships and research trends in the prediction of business failure are discussed.     

Spreadsheets and Bulgarian goats

We consider a problem appearing in an Australian Mathematics Challenge in 2003. This article considers whether a spreadsheet might be used to model this problem, thus allowing students to explore its structure within the spreadsheet environment. It then goes on to reflect on some general principles of problem decomposition when the final goal is a successful and lucid spreadsheet implementation.     

Simple expressions for finding recovery system inventory control parameter values

In recent years considerable effort has been devoted to the development of inventory control models for joint manufacturing and remanufacturing. Optimality of control policies is analyzed and algorithms for the determination of parameter values have been developed. However, there is still a lack of formulae or algorithms that allow for an easy computation of optimal or near optimal policy parameter values. This paper addresses the problem of computing the produce-up-to level S and the remanufacture-up-to level M in a periodic review inventory control model. We provide simple formulae for the policy parameter values, which can easily be implemented within spreadsheet applications. The approach is to derive news-vendor-type formulae that are based on underage and overage cost considerations. We propose different formulae depending on whether lead times for production and remanufacturing are identical or not. A numerical study shows that the obtained solutions provide relatively small cost deviations compared to the optimal solution within the investigated class of inventory control policies.     

Assessing the of potential of e-business models: towards a framework for assisting decision-makers

Decision makers are faced with an enormous range of electronic business models from which to choose. The process of fully researching each of these models can prove daunting. Such research is a feature of what has been termed the “intelligence phase” of decision making. This phase is important as options excluded at this stage do not get considered at a later stage. This paper develops a prerequisites framework for use at the intelligence phase to exclude models that are incompatible with prevailing organisational and supply chain characteristics. The framework assesses the following characteristics: economic control, supply chain integration, functional integration, innovation and input sourcing. The paper utilises a series of five point Likert scales to operationalise these characteristics so that they can be used by decision makers to efficiently manage “intelligence phase” activities.     

Discrete and continuous time representations and mathematical models for large production scheduling problems: A case study from the pharmaceutical industry

The underlying time framework used is one of the major differences in the basic structure of mathematical programming formulations used for production scheduling problems. The models are either based on continuous or discrete time representations. In the literature there is no general agreement on which is better or more suitable for different types of production or business environments. In this paper we study a large real-world scheduling problem from a pharmaceutical company. The problem is at least NP-hard and cannot be solved with standard solution methods. We therefore decompose the problem into two parts and compare discrete and continuous time representations for solving the individual parts. Our results show pros and cons of each model. The continuous formulation can be used to solve larger test cases and it is also more accurate for the problem under consideration.     

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.     

Multi-period models for analyzing the dynamics of process improvement activities

Managing knowledge based resource capabilities has become very important in recent years and during a finite horizon it seems to be reasonable to develop the capabilities intensively at the beginning as one can utilize those over a longer period of time. With the help of multi-period models we check the validity of this idea and characterize the dynamics of development activities. The paper identifies the factors that shape these dynamics and from the behavior of these factors we conclude when the dynamics can be increasing or decreasing. We point out that in stable environment there is tendency for decreasing dynamics but future expectations can significantly modify this outcome. Relationships between the successful or less successful implementation of a business strategy and the dynamics of improvement activities are highlighted as well. For specific model structures explicit solutions are derived.     

The versatile spreadsheet

This paper considers how traditional topics in mathematics can be recast and given new insight by the use of spreadsheets. The concepts are taken from an undergraduate course in business mathematics and modelling. An example involving the study of a problem by differentials and the spreadsheet is given. The material developed is relevant to mathematics courses and to service‐type     

Measuring overall efficiency and effectiveness using DEA

This paper presents a framework where data envelopment analysis (DEA) is used to measure overall efficiency and show how to apply this framework to assess effectiveness for more general behavioral goals. The relationships between various cone-ratio DEA models and models to measure overall efficiency are clarified. Specifically it is shown that as multiplier cones tighten, the cone-ratio DEA models converge to measures of overall efficiency. Furthermore, it is argued that multiplier cone and cone-ratio model selection must be consistent with the behavioral goals assigned or assumed for purposes of analysis. Consistent with this reasoning, two new models are introduced to measure effectiveness when value measures are represented by separable or linked cones, where the latter can be used to analyze profit-maximizing effectiveness.     

A unified treatment of single component replacement models

In this paper we discuss a general framework for single component replacement models. This framework is based on the regenerative structure of these models and by using results from renewal theory a unified presentation of the discounted and average finite and infinite horizon cost models is given. Finally, some well-known replacement models are discussed, and making use of the previous results an easy derivation of their cost functions is presented.