Spreadsheet Environment Research Articles

Bridge Corrosion Cost Model Implementation and Coating Maintenance Model Using Dynamic Programming

This second of two papers studying the minimization of the cost of coating maintenance for steel bridges deals with two main topics: the computer implementation of a life-cycle cost analysis (developed in the first paper) in a spreadsheet environment using equivalent annual costs to compare three single maintenance strategies—spot repair, overcoat, and recoat; and the development of a coating maintenance model using the dynamic programming approach that can optimize combinations of maintenance procedures for minimum cost. The flexibility of the life-cycle cost spreadsheet implementation allows the user to adjust key parameters in order to account for variability in costs and environmental conditions between different regions in British Columbia. The program can be easily adapted for different climatic conditions. In addition, an on-line “Help” feature is provided to reduce the time needed to operate the program. The computer application and demonstrative example are described here and the source code is available on request. The second evolved coating maintenance model using the dynamic programming approach presented here minimizes the sum of the costs resulting from a sequence of rehabilitation choices (a combination of maintenance strategies). It is a more flexible analysis technique than the equivalent annual cost approach and can determine the optimal sequence of rehabilitation activities over the service life of the structure. The procedures developed in this analysis can be easily converted into algorithms for possible computer applications. Based on results from a preliminary analysis of in-service bridges with good coating condition assessments using the life-cycle cost and dynamic programming approaches, spot repair is the most cost-effective rehabilitation method. Overcoating is the second most effective strategy and recoating is usually the most expensive solution. However, the difficulty in obtaining adequate deterioration and cost functions for the coating systems will significantly reduce the accuracy of these analysis techniques. Therefore, a uniform data-collection system should be implemented so that a database will be available for these models.

Spreadsheets and OR/MS Models: An End-User Perspective

In 1986, Bodily stated that practitioners could use spreadsheets to model management science/operations research (OR/MS) problems. We surveyed OR practitioners to determine the extent of implementation of these OR/MS problems in a spreadsheet environment and found that end users are solving OR/MS problems using spreadsheets across many functional areas of business, though in varying degrees. Some areas show higher use than others and spreadsheet models are being used to implement various OR tools in a pattern very similar to their use in the nonspreadsheet environment.

Revisiting decision trees

Explores, using appropriate examples, the ways in which decision trees can be used by the manager to assist in the longitudinal decision‐making process. Since the mathematical concepts associated with decision trees are complex, managers can be reluctant to attempt to use decision tree models. A recognition that such models can be simply developed in a spreadsheet environment, and can then be used for sensitivity analysis using different decision criteria, demonstrates that decision trees can offer valuable insights into the structure of a strategic decision problem.

A microcomputer cashier scheduling system for supermarket stores

Labour scheduling in supermarkets is a difficult task since most employees work various shift lengths instead of standard eight‐hour shifts. With front‐end cashiers, it is even more difficult, since the number of needed cashiers is based on customer volume, which is constantly fluctuating. To create an accurate schedule, a supermarket manager must spend a good deal of time trying to match cashier shifts to customer demand. Presents a shift selection integer linear programming model which has been implemented in stores of a supermarket chain in north‐east USA. The model runs on a personal computer within an electronic spreadsheet environment, making it user‐friendly for managers. The POS data, routinely collected by registers and scanners are used for forecasting cashiers′ demand. The goal in this scheduling system is to maintain an acceptable customer service at the checkout for customer satisfaction and retention.

Data Analysis Techniques for Impact Tests of Composite Materials

Abstract This paper discusses data analysis techniques for instrumented impact tests conducted on glass fiber composite materials. For this study, a drop weight tower has been instrumented with an accelerometer and computer data acquisition system. From the acceleration data obtained, load, energy, velocity, and displacement are calculated in a spreadsheet environment. The resulting data from multiple tests for each material type considered are then averaged producing average plots. The standard deviation is also calculated and plotted for the load data. These curves are then used to characterize the impact behavior of the material. Also introduced is a procedure by which results are normalized by all geometric parameters present in the impact test. This method provides for comparison of test data from three-point bend impact tests having differing geometric parameters.

Using an integrated software package for clinical data analysis on a microcomputer

An integrated software package was used effectively for entering, organizing and analyzing clinical research data on a microcomputer. Both the database and the spreadsheet components of the package were used in the process. The database component enabled a form to be created for entering the data. The spreadsheet component was used in the organization and analysis of data. Macros were written within the spreadsheet environment for the statistical analysis of data. The purpose of this paper is to illustrate how an integrated software like Symphony could offer features beyond simply the use of a spreadsheet for the analysis of research data. Also highlighted are the other useful features of the integrated software that are not directly related to data analysis.

Golden section search as an optimization tool for spreadsheets

This paper outlines the golden section search for determining optimal values of functions described within spreadsheets. Two applications of interest are: (1) to maximize tractive efficiency of a two-wheel drive tractor as a function of wheel slip, and (2) to determine the failure force exerted on a tillage tool as a function of the rupture angle of the soil. Also included are listings of a macro and project file as examples of how this routine can be incorporated into a spreadsheet environment.

A scheduling system for supermarket cashiers

Since customer volume in a supermarket store is constantly fluctuating during a day and through the week, cashier scheduling is not an easy task. To create an accurate schedule, a supermarket manager must spend a good deal of time trying to match cashier shifts to customer demand. In this paper, a shift selection integer linear programming model is presented which has been implemented in Stop & Shop supermarket stores in Boston Massachusetts. The model runs on a personal computer within an electronic spreadsheet environment, making it user friendly for managers. It considers both hourly labor demand constraints and availability of labor constraints for each day of the week.

PC-Based Management Game for Irrigated Farming

: Irrigated farming is a complex activity influenced by a large number of interacting and random factors. A sufficiently realistic gaming simulation model can be used as an effective tool for training in decision making under such a setting. This paper describes a microcomputer-based game model CASIMBOL (Computer Aided Simulation of Irrigation Management Below OutLet) that deals with the problems faced by a typical farmer in a water-deficient region in southern India. The model is built in a spreadsheet environment. Menu-driven macros are developed for automating the gaming session, with provision for selective revision of random variable values. A case-specific synthetic knowledge base is made available for consultation at selected stages during the progress of the game. The advantages, limitations and further possibilities of this model have been brought out during trial sessions involving academic and field personnel. The spreadsheet-based game model linked to a mini expert system demonstrates that this methodology can be adopted for similar situations.

Planning of disposal sites in Dar es Salaam, Tanzania—A decision support system approach

A system is developed aiming at supporting the planners in several aspects of solid waste management in developing countries. The system is tailored especially to planning of disposal sites in Dar es Salaam, Tanzania based on a decision support system framework. Emphasis was placed on: applicability for users without particular computer knowledge, a simple and comprehensible system for solid waste planners to use, stressing environmental impact, and introduction of subjective judgements. As an important element of problem formulation and problem solving, the involved decision processes are elaborated. The entire system is implemented within a Lotus 1-2-3 spreadsheet environment. The developed model illustrates that it is possible to include and emphasize several features that are lacking in many existing commercial computerized models.

Planning of Disposal Sites in Dar Es Salaam, Tanzania—a Decision Support System Approach

A system is developed aiming at supporting the planners in several aspects of solid waste management in developing countries. The system is tailored especially to planning of disposal sites in Dar es Salaam, Tanzania based on a decision support system framework. Emphasis was placed on: applicability for users without particular computer knowledge, a simple and comprehensible system for solid waste planners to use, stressing environmental impact, and introduction of subjective judgements. As an important element of problem formulation and problem solving, the involved decision processes are elaborated. The entire system is implemented within a Lotus 1-2-3 spreadsheet environment. The developed model illustrates that it is possible to include and emphasize several features that are lacking in many existing commercial computerized models.

Analysis of electrical machines using Symphony

The application of Symphony spreadsheet programs to electrical machine analysis is described. Seven typical examples are presented, namely steady-state performance of a DC shunt generator, speed control of a DC shunt motor, magnetizing current waveform of a transformer, steady-state performance of a three-phase induction machine, frequency control of a three-phase induction motor, induction motor run-up, and swing curves of a synchronous generator. Advantage is taken of the spreadsheet environment for such operations as tabular calculation, table lookup, numerical integration, and what-if analysis. Extensive use is made of the integrated graphics facilities of Symphony for producing good quality screen displays on a personal computer, which proves very convenient and useful for computer-aided instruction. Certain features of Symphony are described in the context of the applications described.< <ETX xmlns:mml="http://www.w3.org/1998/Math/MathML" xmlns:xlink="http://www.w3.org/1999/xlink">&gt;</ETX>

An integrated management support and production control system for hardwood forest products

Spreadsheet and simulation models are tools which enable users to analyze a large number of variables affecting harwood material utilization and profit in a systematic fashion. This paper describes two spreadsheet models, seasaw and seain, and a hardwood sawmill simulator. seasaw is designed to estimate the amount of conversion from timber to lumber, while seain is a log inventory module. The two spreadsheet modules are currently being interfaced and linked to an integrated system which will be optimized under the same spreadsheet environment. The simulator has the capability to describe in real time, the system performance of a hardwood sawmill under any specified operating conditions.

Transport Selection: Computer Modelling in a Spreadsheet Environment

Trade‐offs among logistics cost and service elements often make transport selection analysis difficult, especially when demand is uncertain and carriers offer different rates and services. The spreadsheet environment offers a powerful, manager‐friendly medium for analysing these trade‐offs. This microcomputer application exploits some of the recent advances in spreadsheet technology to provide transport buyers with a tool for evaluating such complex logistical trade‐offs. This tool demonstrates how the emerging spreadsheet technology makes it relatively easy to develop a computer‐based model that can capture a rich level of detail that is beyond the scope of the current transport selection models using analytical approaches.

Probabilistic optimization spreadsheets: Props

Props is a set of models which solves a broad range of probabilistic operations research problems. It is a new development in that the models are integrated into the spreadsheet environment, representing an extension of the spreadsheet commands. All of the capabilities and power of the spreadsheet are retained and are used to input data and to analyze, print and graph the results. The user is not required to learn a new language or system

Solutions to Industrial Engineering problems using integrated software environments

The development of word processing systems and the spreadsheet environment marked the beginning of the professional computing movement. For the first time, programs were available that were both highly functional and highly usable, even by non-computer people. The next group of “hot” programs in the business market were “integrated” packages providing a combination of word processing, spreadsheet, database, graphics, and telecommunications. Unfortunately, these integrated packages have not displaced such single purpose programs as Lotus 1-2-3, dBase and WordPerfect as the dominant forces in desktop computing. Integrated packages are generally perceived as complex, cumbersome, and difficult to master. An important point to note, however, is that high proficiency is not necessary; successful users of commercial software seldom know or use more that 40% of a programs functionality. This paper addresses the use of integrated software packages to solve Industrial Engineering problems. More specifically, examples are presented demonstrating the use of Ashton Tate's Framework II to solve production and operations management problems. Sample applications include forecasting, after tax economic analysis, and data storage and retrieval, demonstrating the advantages of linking word processing, spreadsheets, and databases.

Model management in organizations

The premise that the personal computer/spreadsheet explosion will result in the evolution of model management within organizations is explored. The authors use Nolan's stage model of organizational data processing activity as a basis for discussing the nature of change in organizations as local computing capability proliferates. The mainframe era resulted in the recognition of data as a resource and gave rise to data administration. The authors expect that the personal computer era and the accompanying spreadsheet explosion will lead to the recognition of models as a valuable and manageable resource. The role of model administration within organizations is discussed as are software tools for supporting this functional activity. The information resource encyclopedia, an extension of the traditional data dictionary concept, and the model management system are introduced as integral components for supporting model administration. An example is presented to suggest an integrative approach for implementing an MMS in a spreadsheet environment.