Spreadsheet Model Research Articles

Application of stochastic approach based on Monte Carlo (MC) simulation for life cycle inventory (LCI) to the steel process chain: Case study

The purpose of the paper is to present the results of application of stochastic approach based on Monte Carlo (MC) simulation for life cycle inventory (LCI) data of Mittal Steel Poland (MSP) complex in Kraków, Poland. In order to assess the uncertainty, the software CrystalBall® (CB), which is associated with Microsoft® Excel spreadsheet model, is used. The framework of the study was originally carried out for 2005. The total production of steel, coke, pig iron, sinter, slabs from continuous steel casting (CSC), sheets from hot rolling mill (HRM) and blast furnace gas, collected in 2005 from MSP was analyzed and used for MC simulation of the LCI model. In order to describe random nature of all main products used in this study, normal distribution has been applied. The results of the simulation (10,000 trials) performed with the use of CB consist of frequency charts and statistical reports. The results of this study can be used as the first step in performing a full LCA analysis in the steel industry. Further, it is concluded that the stochastic approach is a powerful method for quantifying parameter uncertainty in LCA/LCI studies and it can be applied to any steel industry. The results obtained from this study can help practitioners and decision-makers in the steel production management.

Policy analytics need more than a spreadsheet: a case study in funding formulae

This article presents two case studies, concerning the allocation of £Billions by a mechanism communicated via spreadsheet models. It argues that technical analytic skills as well as policy development skills are a vital component of governance. In the UK, Central Government uses funding formulae to distribute money to local service providers. One commonly stated goal of such formulae is equity of service provision. However, given the complexity of public services, together with variations in need, delivery style and the exercise of stakeholder judgement as to which needs should be met and how, such formulae frequently obscure the process by which equity has been taken into account. One policy ‘solution’ to managing such tensions is to seek ‘transparency’. With respect to funding formulae, this commonly involves publishing the underlying data and formulae in spreadsheets. This paper extends the argument that such ‘transparency’ requires an audience that understands the policy assumptions (and related conceptualisations), data sources, methodological approaches and interpretation of results. It demonstrates how the search for policy ‘transparency’ is also met by the technical quality assurance goals that the operational research community would recognise as best practice in the development both of software generally and spreadsheet models specifically. Illustrative examples of complex formulae acting to subvert equity are drawn from the English Fire and Rescue Service and Police Service allocation formulae. In the former, an increase in the amount of deprivation, as measured by one of six indicators, has the perverse effect of decreasing the financial allocation. In the latter, metropolitan areas such as London are found to gain most from the inclusion of variables measuring sparsity. The conclusion from these scenarios is that the steps needed to for technical quality assurance and policy transparency are mutually reinforcing goals, with policy analysts urged to make greater use of technical analytic skills in software development.

Evaluation of the economics of conversion to compressed natural gas for a municipal bus fleet

AbstractDomestic natural gas production has increased markedly in the United States, and now compressed natural gas (CNG) has the potential to become a cleaner and less expensive energy source than diesel fuel for use in the public transportation sector, especially for city bus fleets. This paper provides an economic analysis of possible CNG conversion for Lafayette, IN CityBus Corporation. It uses benefit–cost analysis to compare the total cost of three potential options for bus replacement: standard diesel, hybrid diesel‐electric, and CNG. A spreadsheet model was used to estimate the total cost of these three fleet options over a 15‐year project horizon. Results suggest that the CNG option has the lowest net present value (NPV) cost, and that cost savings would be larger if the corporation could obtain a grant for the CNG fueling station or if the project life span could expand to 20 years. From the environmental perspective, the CNG option would reduce greenhouse gas and particulate emissions particularly in comparison with the diesel option. Monte Carlo simulation was used to examine the inherent riskiness of the three fleet options. CNG is always lower cost than hybrid diesel‐electric. Depending on assumptions regarding the underlying price distributions, the CNG option has a 51–100% chance of being lower cost than the standard diesel option.

A passive cold storage device economic model to evaluate selected immunization location scenarios

BackgroundThe challenge of keeping vaccines cold at health posts given the unreliability of power sources in many low- and middle-income countries and the expense and maintenance requirements of solar refrigerators has motivated the development of passive cold storage devices (PCDs), containers that keep vaccines cold without using an active energy source. With different PCDs under development, manufacturers, policymakers and funders need guidance on how varying different PCD characteristics may affect the devices’ cost and utility. MethodsWe developed an economic spreadsheet model representing the lowest two levels of a typical Expanded Program on Immunization (EPI) vaccine supply chain: a district store, the immunization locations that the district store serves, and the transport vehicles that operate between the district store and the immunization locations. The model compares the use of three vaccine storage device options [(1) portable PCDs, (2) stationary PCDs, or (3) solar refrigerators] and allows the user to vary different device (e.g., size and cost) and scenario characteristics (e.g., catchment area population size and vaccine schedule). ResultsFor a sample set of select scenarios and equipment specification, we found the portable PCD to generally be better suited to populations of 5,000 or less. The stationary PCD replenished once per month can be a robust design especially with a 35L capacity and a cost of $2,500 or less. The solar device was generally a reasonable alternative for most of the scenarios explored if the cost was $2,100 or less (including installation). No one device type dominated over all explored circumstances. Therefore, the best device may vary from country-to-country and location-to-location within a country. ConclusionsThis study introduces a quantitative model to help guide PCD development. Although our selected set of explored scenarios and device designs was not exhaustive, future explorations can further alter model input values to represent additional scenarios and device designs.

Spreadsheet Inventory Simulation and Optimization Models and Their Application in a National Pharmacy Chain

This paper presents a spreadsheet model for the solution of inventory management problems that arise in pharmacy chain stores. Though there have been abundant spreadsheet models for inventory management, most of these models focus on the use of embedded functions to compute complicated formulas. The spreadsheet model presented in this paper, however, incorporates a simulation of the ordering process and an iterative procedure to search for near-optimal solutions, and is easy to understand by students and practitioners. The methodology has been successfully implemented in a national chain of pharmacies; and the spreadsheet model integrated into the curriculum of several engineering courses including inventory management, simulation, and optimization and it has provided an interactive environment for students to experience real-life inventory decision making. This paper is dedicated to the memory of Dr. Paul A. Jensen, whose widely adopted Excel add-ins have inspired our education and research in spreadsheet models and provided the foundation for our work with pharmacy inventory systems.

Integrating Math & Computer Skills in the Biology Classroom

Students often enter biology programs deficient in the math and computational skills that would enhance their attainment of a deeper understanding of the discipline. To address some of these concerns, I developed a series of spreadsheet simulation exercises that focus on some of the mathematical foundations of scientific inquiry and the benefits of mathematical modeling in understanding some of the fundamental concepts of ecology, including topics such as population growth, selection, competition, predation, and sustainability. In the example presented here, students collect data from a computer-generated study site and then use basic spreadsheet skills to calculate, graphically visualize, and compare sample means (± SE) to effectively discover why random and transect sampling are superior to biased sampling, and to explore the effects of sample size on the standard error of the mean. Pre- and post-exercise questionnaires suggest that spreadsheet models such as this one are effective in improving students’ attainment of the target principles. Spreadsheet simulations can be used as a low-cost, easily accessible way to leverage technology to supplement lecture or laboratory content while fostering vital math and computer skills.

Effects of morphological fractions on estimated milk yields in forage maize depending on growing site and plant maturity

This study was undertaken to determine the effects of the morphological fractions of forage maize for milk production as predicted by the spread sheet model called MILK 2006. The plants were harvested at increasing maturities at three different sites in Sweden and separated into stems, leaves, kernels and cobs. Chemical and in vitro analyses of the plant fractions as specified in the MILK 2006 model for predicting estimated milk yields Milk Mg−1 dry matter (DM) and Milk ha−1 were undertaken. Partial least squares regression analysis was used to evaluate the impact of the proportions and qualities of the fractions on Milk Mg−1 DM and Milk ha−1. Estimated milk yields could not be predicted based on the proportions of the fractions and their nutritional values. The most important parameters for determining Milk ha−1 was the DM yield, and for the estimated Milk Mg−1 were the concentrations of starch and the fibre digestibility of the whole crop.

A stepwise multi-assignment spreadsheet model to select student teams

Student projects play a big role in engineering and management education. Most projects require students to work in teams, which allow students to practice their technical knowledge, foster the development of personal leadership skills, and teach the value of team accomplishments. One of the challenges of administering team projects is to create the best team compositions that give all teams the equal chance of success while maximizing interaction between students. The instructor should strive to balance each team so the team capabilities are as even as possible. Instructors also should take into account the performance of each student throughout the semester, when recreating teams for upcoming assignments. This paper presents a stepwise optimization that continues throughout the semester as the teams evolve from one assignment to another. Results show that this method can expose students to approximately 50% of their classmates within four team assignments and reasonable team grade variance.

An Active Learning Exercise for Introducing Agent-Based Modeling

Recent developments in agent-based modeling as a method of systems analysis and optimization indicate that students in business analytics need an introduction to the terminology, concepts, and framework of agent-based modeling. This article presents an active learning exercise for MBA students in business analytics that demonstrates agent-based modeling by solving a knapsack optimization problem. For the activity, students act as naive agents by using dice to randomly selecting items for a finite capacity knapsack to maximize the value of the knapsack. Students then design a greedy heuristic to skew the probability of selection item. These pencil-and-paper models are then implemented in a spreadsheet model to demonstrate the effects of altering the agents’ behavior. Finally, a binary integer programming model is examined to contrast agent-based modeling with traditional mathematical programming formulations. This exercise is innovative because it combines student engagement via active learning with an innovative, individual-based, modeling methodology.

Comparison of Stakeholder Metrics for Traditional and Adaptive Development and Licensing Approaches to Drug Development.

This study evaluates whether an adaptive development and licensing approach to drug development, compared with approaches widely used today, might have tangible advantages across stakeholder groups, thereby facilitating the future adoption. Details involving actual and modeled clinical development and licensing programs for 3 case studies were used as inputs into a discounted cash flow spreadsheet model. Outputs included net present value and expected net present value, which are metrics considered as key incentives for pharmaceutical developers, and change in patient access over the product life and numbers of appropriately and inappropriately treated patients, which are metrics considered as key incentives for regulators, patients, and prescribers. Actual and modeled development programs were compared using an "adaptiveness" scoring algorithm. Generally, the more adaptive programs correlated with more favorable stakeholder outcomes. However, favorable outcomes may be overwhelmed in some cases, and the causative conditions and stakeholder reactions need to be defined.

CARMA’s integrative modeling: historical background of modeling caribou and reindeer biology relevant to development of an energy/protein model

-

Financial implications of installing air filtration systems to prevent PRRSV infection in large sow herds

Air filtration systems implemented in large sow herds have been demonstrated to decrease the probability of having a porcine reproductive and respiratory syndrome virus (PRRSV) outbreak. However, implementation of air filtration represents a considerable capital investment, and does not eliminate the risk of new virus introductions. The specific objectives of the study were: 1) to determine productivity differences between a cohort of filtered and non-filtered sow farms; and 2) to employ those productivity differences to model the profitability of filtration system investments in a hypothetical 3000 sow farm. Variables included in the study were production variables (quarterly) from respective herds; air filtration status; number of pig sites within 4.7km of the farm; occurrence of a PRRSV outbreak in a quarter, and season. For the investment analyses, three Scenarios were compared in a deterministic spreadsheet model of weaned pig cost: (1) control, (2) filtered conventional attic, and (3) filtered tunnel ventilation. Model outputs indicated that a filtered farm produced 5927 more pigs than unfiltered farms. The payback periods for the investments, were estimated to be 5.35 years for Scenario 2 and 7.13 years for Scenario 3 based solely on sow herd productivity. Payback period sensitivity analyses were performed for both biological and financial inputs. The payback period was most influenced by the premium for weaned pig sales price for PRRSV-negative pigs, and the relative proportions of time that filtered vs. unfiltered farms produced PRRSV-negative pigs. A premium of $5 per pig for PRRS-negative weaned pigs reduced the estimated payback periods to 2.1 years for Scenario 2 and 2.8 years for Scenario 3.

Modelling ventilation rate, balance temperature and supplemental heat need in alternative vs. conventional laying-hen housing systems

An Excel-based spreadsheet model has been developed to delineate ventilation rate (VR), supplemental heat need ( H s ), balance temperature ( t bal , outdoor temperature below which H s is required to maintain the desired indoor temperature), energy use and cost for H s in alternative (aviary and enriched colony) vs. conventional cage laying-hen houses. The model was applied to the Midwestern U.S. housing characteristics (same land footprint) and winter weather conditions (−30 °C to 5 °C ambient temperature, and a constant relative humidity or RH of 70%). Effects of hen stocking density, target indoor temperature and RH ( t i , RH i ), building insulation, and light period vs. dark period on VR, t bal and H s were examined. For the housing characteristics considered and target indoor condition of 25 °C and 60% RH, t bal for the alternative housing systems was found to be 2.0–2.6 °C higher than that for the conventional cage counterpart. The supplemental heater capacity would need to be 20.5–22.0 kW per 10,000 hens for the aviary houses (107,000-hen capacity) and 17.6 kW per 10,000 hens for the enriched colony house (124,000-hen capacity). Annual H s was estimated to be 0.09 and 0.12 MJ [kg egg] −1 for the enriched and aviary houses, respectively. The corresponding H s cost (in US dollars) would be, respectively, 0.11 and 0.15 US cent [kg egg] −1 at a wholesale liquid propane (LP) fuel price of $0.32 l −1 ($1.21 gal −1 ) or 0.26 and 0.35 US cent [kg egg] −1 at a retail LP fuel price of $0.75 l −1 ($2.84 gal −1 ). Among all the influencing factors considered, t i and RH i setpoints had more pronounced impacts on t bal and H s . The analysis indicated that H s energy cost for the alternative housing systems in the Midwestern USA is less than 0.3% of the total production cost. The simulation model was validated with measured heating energy use by a commercial aviary house in northern Iowa and the difference between the predicted and field-measured H s values was less than 5%. This interactive model can be readily used for analysis of other laying-hen housing, climatic conditions, and/or management scenarios.

Quantitative model of record stratospheric freefall

Before the record stratospheric jump of Felix Baumgartner last October, the study of high-altitude freefall was hampered by the lack of sufficient or reliable experimental data. His feat and the organizers' publishing of GPS data have provided us with a nice experiment to study on an introductory physics course. A quantitative approach to freefall dynamics requires only a spreadsheet model incorporating air properties calculated with the standard atmosphere model and a speed-dependent drag force in a transonic–supersonic regime through a variable CDA product, where CD is the drag coefficient and A is the maximum skydiver cross-section area.

Should Israel screen all mothers-to-be to prevent early-onset of neonatal group B streptococcal disease? A cost-utility analysis

BackgroundIn Israel, an average of 37 children are born each year with sepsis and another four with meningitis as a result of Group B Streptococcal (GBS) disease. Israel currently only screens mothers with defined risk factors (around 15% of all pregnancies) in order to identify candidates for Intrapartum Antiobiotic Prophyhlaxis (IAP) of GBS. This paper presents a cost-utility analysis of implementing an alternative strategy, which would expand the current protocol to one aiming to screen all pregnant women at 35–37 weeks gestation based on taking a vaginal culture for GBS.MethodsA spreadsheet model was built incorporating technical, epidemiological, health service costs, demographic and economic data based primarily on Israeli sources.ResultsThe intervention of universal screening (compared with the current scenario) would increase screening costs from 580,000 NIS to 3,278,000 million NIS. In addition, the intervention would also increase penicillin costs from 39,000 NIS to 221,000 NIS. Current culture screening of approximately 15% of mothers-to-be with high risk factors resulted in 42 GBS births in 2008-9 (0.253/1000 births). Expanding culture screening to 85% of mothers-to-be, will decrease the number of GBS births to 17.3 (0.104/1000 births). The initial 2.9 million NIS incremental intervention costs are offset by decreased treatment costs of 1.9 million NIS and work productivity gains of 811,000 NIS as a result of a decrease in neurological sequelae from GBS caused meningitis. Thus the resultant net cost of the intervention is only around 134,000 NIS. Culture based screening will reduce the burden of disease by 12.6 discounted Quality Adjusted Life Years (QALYS), giving a very cost effective baseline incremental cost per QALY (cf. risk factor screening) of 10,641 NIS per QALY. The data was very sensitive to rates of anaphylactic shock and changes in the percentage of meningitis cases that had associated long term-sequelae.ConclusionIt is recommended that Israel adopt universal culture-based GBS screening.

Using wastewater and high-rate algal ponds for nutrient removal and the production of bioenergy and biofuels

This paper projects a positive outcome for large-scale algal biofuel and energy production when wastewater treatment is the primary goal. Such a view arises partly from a recent change in emphasis in wastewater treatment technology, from simply oxidising the organic matter in the waste (i.e. removing the biological oxygen demand) to removing the nutrients - specifically nitrogen and phosphorus - which are the root cause of eutrophication of inland waterways and coastal zones. A growing need for nutrient removal greatly improves the prospects for using new algal ponds in wastewater treatment, since microalgae are particularly efficient in capturing and removing such nutrients. Using a spreadsheet model, four scenarios combining algae biomass production with the making of biodiesel, biogas and other products were assessed for two of Australia's largest wastewater treatment plants. The results showed that super critical water reactors and anaerobic digesters could be attractive pathway options, the latter providing significant savings in greenhouse gas emissions. Combining anaerobic digestion with oil extraction and the internal economies derived from cheap land and recycling of water and nutrients on-site could allow algal oil to be produced for less than US$1 per litre.

Decision support system for AS/RS investments: real benefits out of Monte Carlo simulation

Warehousing plays an important role in the efficiency of today's international supply chains. Optimal configuration of warehouse activities depends on several design criteria. Basic alterative system designs are based either on manual labour or automated systems. While manual systems typically require smaller investments, automated systems in turn enable increased efficiency in space utilisation, advanced flexibility, as well as reduction in spoilage and administration cost. In this research, we combine static spreadsheet modelling with Monte Carlo simulation to estimate the payback time of two suggested automatic storage and retrieval system (AS/RS) investments as well as for identifying the main factors affecting their economic feasibility. By offering a rapid tool for evaluating the potential of an AS/RS, detailed time consuming analyses need to be performed only for the most potential configurations.

Modeling Municipal Solid Waste Management in Africa: Case Study of Matadi, the Democratic Republic of Congo

The purpose of this article is to present the key elements for best performance and profitability of Municipal Solid Waste (MSW) management in a low-income city. The research provides an overview of methods and models for integrated planning of a two-phase program: MSW collection and transportation, and MSW treatment. We present the case study of Matadi (the Democratic Republic of Congo) that has a low level of the MSW management compared to other African cities. We develop a spreadsheet model for collection and transportation of MSW which is relevant for low-income cities and enables determining the waste collection fee. A CDM decay model is used to predict the GHG emissions in disposal site. The MSW treatment plant in case of Matadi is evaluated. For the anaerobic digestion technology selected as appropriate for this plant, the key factors that ensure profitability of the plant are as follows: tipping fee from the municipality (19% of total revenue), amount of carbon credits which can sum up to 16% of the total revenue, expansion of waste collection range from 25 to 50 km. The methods of this study can be used for solving waste problem in other low-income cities where the budget for municipal services is scanty, particularly when starting from a very low level of MSW management.

Calcium silicate hydrate (C-S-H) gel dissolution and pH buffering in a cementitious near field

AbstractA cementitious backfill has been proposed in many geological disposal concepts for intermediate-level waste and low-level waste in the UK and elsewhere. In this paper, the main features of the chemical evolution of backfill and the associated changes in the near-field pH are illustrated with results from recent work. For example, interaction of the groundwater with calcium silicate hydrate (C-S-H) phases in a backfill is expected to play an important role in the long-term pH-buffering behaviour. Existing experimental data for the dissolution of C-S-H gels are compared with recent experimental results from leach tests on gels of a lower calcium to silicon ratio (C/S) to provide a consistent set of data across the full C/S range. The results confirm that a congruent dissolution point around C/S = 0.8 is approached by leaching from below (i.e. for gels with 0.29 < C/S < 0.8), as well as from above, as reported elsewhere. In addition, a spreadsheet model has been developed to calculate the volume of backfill required at the vault scale to meet specified pH performance criteria. This model includes the major reactions of the backfill with the groundwater, waste encapsulants and waste components. It can also consider the effects of specific waste packages on local pH performance to allow comparison with the vault-scale calculations.

Prospects for cost reductions from relaxing additional cross-border measures related to livestock trade

Compared with the domestic trade in livestock, intra-communal trade across the European Union (EU) is subject to costly, additional veterinary measures. Short-distance transportation just across a border requires more measures than long-distance domestic transportation, while the need for such additional cross-border measures can be questioned.This study examined the prospects for cost reductions from relaxing additional cross-border measures related to trade within the cross-border region of the Netherlands (NL) and Germany (GER); that is, North Rhine Westphalia and Lower Saxony.The study constructed a deterministic spread-sheet cost model to calculate the costs of both routine veterinary measures (standard measures that apply to both domestic and cross-border transport) and additional cross-border measures (extra measures that only apply to cross-border transport) as applied in 2010. This model determined costs by stakeholder, region and livestock sector, and studied the prospects for cost reduction by calculating the costs after the relaxation of additional cross-border measures. The selection criteria for relaxing these measures were (1) a low expected added value on preventing contagious livestock diseases, (2) no expected additional veterinary risks in case of relaxation of measures and (3) reasonable cost-saving possibilities.The total cost of routine veterinary measures and additional cross-border measures for the cross-border region was €22.1 million, 58% (€12.7 million) of which came from additional cross-border measures. Two-thirds of this €12.7 million resulted from the trade in slaughter animals. The main cost items were veterinary checks on animals (twice in the case of slaughter animals), export certification and control of export documentation. Four additional cross-border measures met the selection criteria for relaxation. The relaxation of these measures could save €8.2 million (€5.0 million for NL and €3.2 million for GER) annually. Farmers would experience the greatest savings (99%), and most savings resulted from relaxing additional cross-border measures related to poultry (48%), mainly slaughter broilers (GER), and pigs (48%), mainly slaughter pigs (NL).In particular, the trade in slaughter animals (dead-end hosts) is subject to measures, such as veterinary checks on both sides of the border that might not contribute to preventing contagious livestock diseases. Therefore, this study concludes that there are several possibilities for reducing the costs of additional cross-border measures in both countries.