Spreadsheet Model Research Articles

A Spreadsheet-Based Redox Flow Battery Cell Cycling Model Enabled by Closed-Form Approximations

The complex interplay between numerous parasitic processes—voltage losses, crossover, decay—challenges interpretation of cycling characteristics in redox flow batteries (RFBs). Mathematical models offer a means to predict cell performance prior to testing and to interpret experimentally measured cycling data, however most implementations Require extensive domain expertise, programming knowledge, and/or computational resources. Here, we expand on our previously developed zero-dimensional modeling framework by deriving closed-form expressions for key performance metrics. The resulting closed-form model streamlines the computational structure and allows for spreadsheet modeling of cell cycling behavior, which we highlight by developing a simulation package in Microsoft® Excel®. We then apply this model to analyze previously published experimental data from our group and others, highlighting its utility in numerous diagnostic configurations—bulk electrolysis, compositionally unbalanced symmetric cell cycling, and full cell cycling. Given the accessibility of this modeling toolkit, it has potential to be a widely deployable tool for RFB research and education, aiding in data interpretation and performance prediction.

Design and analysis of a concentrated solar power-based system with hydrogen production for a resilient community

This paper investigates how to improve the utilization of solar energy in concentrated solar power plants for multi-tasked operation, including hydrogen production. In this regard, an integrated energy system with multiple power generation stages is developed to utilize heat from concentrated solar collector with molten salt thermal energy storage system. The two-stage steam Rankine cycle and organic Rankine cycle are used as power generation cycles in order to utilize heat from the molten salt at different temperature levels, which improves source utilization for power generation. Continuous energy supply is considered a crucial challenge for renewables, which can be achieved by diversifying the source or adding energy storage. The current study uses two different energy storage methods to ensure the continuous energy supply to achieve self-sufficiency as well for the community. Hydrogen energy subsystem is the second energy storage within the integrated system, which is the secondary energy storage that is used if heat is not sufficient to drive the power generation cycles. In order to make decisions for a better source utilization, ensure continuous power supply, and facilitate the operations of the components and energy storage subsystems according to the loads and source availability, an operational decision-making strategy is developed and applied within the spreadsheet model. In order to test the proposed system and the developed algorithm, a time-dependent analysis is carried out where hourly community load and hourly source data are employed. The analyses are carried out using the first and second laws of thermodynamics, primarily through energy and exergy aspects. The power cycle can generate power from heat within the temperature range of 160°C and 500°C, in between 6.12% and 37.2% of heat to power energy conversion efficiency. The overall energy and exergy efficiencies of the integrated system are found to be 31.29% and19.71% by considering the average meteorological data.

Using ARIMA Model to Forecast Electricity Load in Jordan

Objectives: This study aims to forecast the daily peak electricity load in Jordan using a dataset of hourly peak load data for the period from January 1, 2010, to December 31, 2022, compiled by the National Electric Power Company (NEPCO). Methods: This study employs the Seasonal Autoregressive Integrated Moving Average (SARIMA) model to make forecasts. The data exhibits an upward trend, seasonality, and non-constant variance. To address these features, the SARIMA model is used to account for the trend and seasonality, while a Box-Cox transformation is applied to manage the non-constant variance. Results: Following the standard Box-Jenkins methodology (identification, estimation, diagnostic checking, and forecasting) and utilizing the “(auto.arima)” function in the RStudio software package, the resulting SARIMA model is ARIMA(1,0,1)(2,1,2)[7]. This model is used to forecast 7 future values of the electricity load. The Mean Absolute Percentage Error (MAPE) and the Root Mean Square Error (RMSE) values, as measures of forecast accuracy, support the precision of our forecasts. Conclusion: Based on empirical results, electricity companies in Jordan are encouraged to use time series models for forecasting electricity loads instead of relying on simple spreadsheet models.

Real-world waste dispersion modelling for benthic integrated multi-trophic aquaculture.

In real-world situations, marine fish farms accommodate multiple fish species and cohorts within the farm, leading to diverse farm layouts influenced by cage dimensions, configurations, and intricate arrangements. These cage management practices are essential to meet production demands, however, farm-level complexities can impact model predictions of waste deposition and benthic impact near fish cages. This is of particular importance when the cages are used for integrated multi-trophic aquaculture (IMTA) with benthic feeders, where this waste not only affects environmental conditions but also provides a potential food source. The Cage Aquaculture Particulate Output and Transport (CAPOT) model incorporated multiple species, cohorts, and cage arrangements to estimate waste distribution from a commercial fish farm in the Mediterranean between October 2018 and July 2019. This spreadsheet model estimated dispersion for individual fish cages using a grid resolution of 5 m x 5 m. The study categorized discrete production periods for each fish cage every month, aligning with intermittent changes in biomass and food inputs due to different cage management practices throughout production. This approach facilitated the use of detailed input data and enhanced model representativeness by considering variations in cage biomass, food types, settling velocities, and configurations. Model outputs, represented in contour plots, indicated higher deposition directly below fish cages that varied monthly throughout fish production cycles. Deposition footprints reflected changes in cage biomass, food inputs, and farm-level practices reflecting this real-world scenario where aquaculture does not follow a production continuum. Moreover, cohort dynamics and cage movements associated with the cage management practices of the fish farm influenced the quantity and fate of wastes distributed around fish cages, revealing variability in deposition footprints. Clearly, these findings have important implications for the design of benthic IMTA systems, with species such as sea cucumber and polychaetes. Variability in waste deposition creates challenges in identifying where the benthic organisms should be placed to allow optimal uptake of waste to meet their food requirements and increase survivability. Evidently, models have an important role to play and this study emphasizes the need for representative input data to describe actual food inputs, cage biomass changes, and management practices for more representative farm-scale modelling and essentially to improve particulate waste management. To effectively mitigate benthic impacts through IMTA, models must quantify and resolve particulate waste distribution and impact around fish farms to maintain a balanced system with net removal of wastes. Resolving farm-level complexities provides vital information about the variability of food availability and quality for extractive organisms that helps improve recycling of organic wastes in integrated systems, demanding a more representative modelling approach.

The Limitations of a Hypothetical All-Variant COVID-19 Vaccine: A Simulation Study.

This paper simulates a hypothetical pan-coronavirus vaccine that confers immediate sterilizing immunity against all SARS-CoV-2 variants. Simulations used a SEIIS (susceptible, exposed, infective, immune, susceptible) spreadsheet model that ran two parallel subpopulations: one that accepted vaccination and another that refused it. The two subpopulations could transmit infections to one another. Using data from the United States (US), the simulated vaccine was tested against limiting factors such as vaccine hesitancy, slow vaccination distribution, and the development of high-transmission variants. The vaccine was often successful at reducing cases, but high-transmission variants and discontinuation of non-pharmaceutical interventions (NPIs) such as masking greatly elevated cases. A puzzling outcome was that if NPIs were discontinued and high-transmission variants became common, the model predicted consistently higher rates of disease than are actually observed in the US in 2024. However, if cumulative exposure to virus antigens increased the duration of immunity or decreased the infectivity of the virus, the model predictions were brought back into a more realistic range. The major finding was that even when a COVID-19 vaccine always produces sterilizing immunity against every SARS-CoV-2 variant, its ability to control the epidemic can be compromised by multiple common conditions.

Кількісне оцінювання функціональної ефективності мережевих структур за аналізом чутливості оптимізаційної моделі до зовнішніх впливів

The real network organizational structure is constantly under the influence of external influences, which can cause unexpected consequences due to the cascading spread of relevant disturbances directed at critically important parameters. Parametric sensitivity analysis is the latest computational procedure, the results of which provide the researcher with an idea and quantitative assessment of the appropriate response of the structure in the form of an analytical platform as a composition and interaction of scenario analysis, cascade process development and optimization modeling technology. The main task is timely adaptation to expected changes in business analytics, oriented to optimal resources distribution. The generalized minimum cost problem is a typical example of widespread problems of optimal distribution and use of limited, in particular, energy resources, of any nature and purpose, using the scientific management and analysis of decisions, in its network model attention is paid to the potentials of nodes. The maximum flow problem is an example of the active and effective application of methods and models of flows optimization, where the processes and objects used a network organization, the energy systems and complexes investigated, where arc parameters are critical in its model. In the article, for these classic optimization problems, mathematical and spreadsheet models were built on concrete examples. Direct and dual mathematical programming problems were solved, the following stages were carried out - the organization of a computer experiment and the construction of an analytical platform for evaluating the behavior of the network structure under the influence of external influences and disturbances as their consequences. To take into account the specific conditions of real objects with a network structure at the stage of modification, the model supplemented with appropriate restrictions and correction of input data sets. The obtained results should be useful for planning and management personnel and top-management for discussion and decision-making regarding, for example, the optimal placement of energy equipment, the weighted distribution of energy flows in the "source-sink" system or the appointment of executors for work, automated project management using project networks, etc. Keywords: optimization modeling, maximum network flow, minimum cost flows, network organizational structures, decision making, spreadsheet modeling and analytics.

Spreadsheet Modeling and Wrangling with Python

A staple of many spreadsheet-based management science courses is the use of Excel for activities such as model building, sensitivity analysis, goal seeking, and Monte-Carlo simulation. What might those things look like if carried out using Python? We describe a teaching module in which Python is used to do typical Excel-based modeling and data-wrangling tasks. In addition, students are exposed to basic software engineering principles, including project folder structures, version control, object-oriented programming, and other more advanced Python skills, creating deployable packages and documentation. The module is supported with Jupyter notebooks, Python scripts, course web pages that include numerous screencasts, and a few GitHub repositories. All of the supporting materials are permissively licensed and freely accessible. Supplemental Material: The supplemental files are available at https://doi.org/10.1287/ited.2023.0047 .

Modeling benefits and tradeoffs of green infrastructure: Evaluating and extending parsimonious models for neighborhood stormwater planning

Green infrastructure is often proposed to complement conventional urban stormwater management systems that are stressed by extreme storms and expanding impervious surfaces. Established hydrological and hydraulic models inform stormwater engineering but are time- and data-intensive or aspatial, rendering them inadequate for rapid exploration of solutions. Simple spreadsheet models support quick site plan assessments but cannot adequately represent spatial interactions beyond a site. The present study builds on the Landscape Green Infrastructure Design (L-GrID) Model, a process-based spatial model that enables rapid development and exploration of green infrastructure scenarios to mitigate neighborhood flooding. We first explored how well L-GrID could replicate flooding reports in a neighborhood in Chicago, Illinois, USA, to evaluate its potential for green infrastructure planning. Although not meant for prediction, L-GrID was able to replicate the flooding reported and helped identify strategies for flood control. Once evaluated for this neighborhood, we extended the model to include water quality through the representation of dispersion and settling mechanisms for two pollutant surrogates—total nitrogen and total suspended solids. With the extended model, Landscape Green Infrastructure Design Model-Water Quality (L-GrID-WQ), we examined benefits, costs, and tradeoffs for different green infrastructure strategies. Bioswales were slightly more effective than other green infrastructure types in reducing flooding extent and downstream runoff and pollution, through increased infiltration and settling capacity. Permeable pavers followed in effectiveness and are suggested where spatial constraints may limit the installation of bioswales. Although green infrastructure supports both flooding and pollution control, small tradeoffs between these functions emerged across spatial layouts: strategies based on only curb-cuts better controlled pollution, while layouts that followed the path of water flow better controlled flooding. By illuminating such tradeoffs, L-GrID-WQ can support green infrastructure planning that prioritizes unique concerns in different areas of a landscape.

An Interactive Modeling Tool for Projecting the Health and Direct Medical Cost Impact of Changes in the Sexually Transmitted Diseases Prevention Program Budgets.

Estimating the return on investment for public health services, tailored to the state level, is critical for demonstrating their value and making resource allocation decisions. However, many health departments have limited staff capacity and expertise to conduct economic analyses in-house. We developed a user-friendly, interactive Excel-based spreadsheet model that health departments can use to estimate the impact of increases or decreases in sexually transmitted infection (STI) prevention funding on the incidence and direct medical costs of chlamydia, gonorrhea, syphilis, and STI-attributable HIV infections. Users tailor results to their jurisdictions by entering the size of their population served; the number of annual STI diagnoses; their prior annual funding amount; and their anticipated new funding amount. The interface was developed using human-centered design principles, including focus groups with 15 model users to collect feedback on an earlier model version and a usability study on the prototype with 6 model users to finalize the interface. The STI Prevention Allocation Consequences Estimator ("SPACE Monkey 2.0") model will be publicly available as a free downloadable tool. In the usability testing of the prototype, participants provided overall positive feedback. They appreciated the clear interpretations, outcomes expressed as direct medical costs, functionalities to interact with the output and copy charts into external applications, visualization designs, and accessible information about the model's assumptions and limitations. Participants provided positive responses to a 10-item usability evaluation survey regarding their experiences with the prototype. Modeling tools that synthesize literature-based estimates and are developed with human-centered design principles have the potential to make evidence-based estimates of budget changes widely accessible to health departments.

Plastic recycling stripped naked - from circular product to circular industry with recycling cascade.

This perspective combines various expertise to develop and analyse the concept of technology cascade for recycling waste plastics with the goal of displacing as much fossil crude oil as possible. It thereby presents archetype recycling technologies with their strengths and weaknesses. It then combines them in various cascades to process a representative plastic mix, and determines how much (fossil) naphtha could be displaced and at which energy consumption. The cascades rely on a limited number of parameters that are fully reported in supplementary information and that were used in a simple and transparent spreadsheet model. The calculated results bust several common myths in plastic recycling, e. g. by prioritizing here recycled volume over recycling efficiency, and prioritizing circular industry over circular products . It unravels the energy cost of solvent-based recycling processes, shows the key role of gasification and the possibility to displace up to 70 % of the fossil feedstock with recycled carbon, a recycling rate that compares well with that aluminium, steel or paper. It suggests that deeper naphtha displacement would require exorbitant amount of energy. It therefore argues for the need to complement recycling with the use of renewable carbon, e. g. based on biomass, to fully defossilise the plastic industry.

Визначення граничних величин споживання електричної енергії засобами оптимізаційного моделювання

In the heating season of 2022/2023, as a result of the military aggression of the Russian Federation and targeted attacks on electric power facilities, a significant shortage of electric energy occurred in the Integrated power system of Ukraine. Restriction schedules and emergency disconnection of consumers were forcibly applied. In the conditions of such an unprecedented violation of the security of electricity supply, the problem of determining the limit values of electric energy and power consumption has gained new relevance for the electricity market participants. In this study, the problem of determining the optimal limit values of electricity consumption based on the criterion of maximum income from its sale to three groups of consumers was set and solved: "Critical infrastructure", "Households" and "Non-household consumers". The formation of the three specified groups is related to price discrimination in the electricity market and the need to supply critical infrastructure facilities without limits. The decision variables in the optimization model are proposed to be specified in fractions of the provision of electrical energy needs (unit fractions), which allows for establishing equal conditions regarding restrictions for all distribution system operators. To take into account the social significance of electric energy as a commodity, an additional constraint was included to the basic optimization model: the lower limit of the decision variable for the "Households" group. Approbation of the proposed optimization model was carried out using spreadsheet modeling in MS Excel on reported data on the results of electrical load measurements on regime days. The results of computational experiments are presented for the conditions of different values of the deficit of electric energy in the Integrated power system of Ukraine and different values of the additional constraint for the «Households» group, in particular, quantitative estimates of the consequences of the included constraints for the functioning of the electricity market. The proposed method of determining the limit values of electric energy consumption allows to formalize the procedure for decision-making according to predetermined rules and thereby achieve a fair distribution of limited resources. Keywords: electric energy consumption, shortage, limit value, optimization modelling, spreadsheet model.

An Interactive Spreadsheet Model for Teaching Classification Using Logistic Regression

We present an interactive spreadsheet that supports teaching essential concepts in classification using the logistic regression (LoR) model for binary classification. The interactive spreadsheet demonstrates the capabilities of LoR by integrating computation with visualization. Students will reinforce concepts like probabilities, maximum likelihood estimation (MLE), and the use of likelihoods to optimize parameters for the LoR. We then discuss using LoR for classifications while adjusting its decision boundary (DB), demonstrating how to convert assigned likelihoods into classification using the DB; impact classification outcome by varying DBs; designate predictions as true positive, true negative, false positive, or false negative; and determine the classification accuracy. We use a variety of performance measures, including sensitivity, specificity, precision, negative predictive value, F1 and F2 scores, the receiver operating characteristics curve, and lift/decile charts. These measures are dynamically adjusted when the DB changes. We also reiterate the usage of these measures in the context of crossvalidation and imbalanced data sets. We provide a case study that implements LoR and an option for teaching the details behind MLE. We discuss the pedagogical aspects of this spreadsheet based on a survey of the 2022 student cohort in the Master of Management Analytics Program at the Rotman School of Management.

THE OPTIMUM DECISION OF OIL AND GAS PRODUCTION SPREADSHEET MODELLING

The optimum revenue of an oil and gas industry is subject to the production volume deliverability and relatively volatile product market price which depends on demand and supply level. Naturally, mature production wells will be dominantly producing water instead of oil and gas, meanwhile, the central processing facilities are having their limited capacity to handle it. This study intends to gain the optimum oil and gas production in mature fields by deciding the opening of well choke valves and considering process facilities limitations as constraints. The model is developed using a simplex LP-based spreadsheet. Three wellhead facilities with a total of forty-seven active producing wells gas, oil, and water data are taken as input parameters. The developed model is validated using the last ten days’ actual production data to identify the error margin consistency, a total of 1,410 data combinations were used. It results in an average error margin of 4.57%-gas and 0.9%-oil. The model could be used as an operator decision reference in opening the well’s choke valve to get the optimum production rate, a minor adjustment might need considering the dynamic process parameter condition.
 
 Keywords: optimal production, mature wells, choke valve opening, spreadsheet modeling

An exploration of quantitative models and algorithms for vehicle routing optimization and traveling salesman problems

This study presents optimization models for large vehicle routing problems using a spreadsheet solver and Python programming language with extended graphic card boosting computing power. Near optimality is feasible and attainable with spreadsheet tools and models for solving real-life problems. However, increasing the availability of additional computing power through graphics processing and visualization is now a viable option for decision-makers and problem-solvers. This study shows that decision-makers can solve vehicle routing optimization problems with limited access to high-end optimization tools. This study shows managers and decision-makers can use vehicle routing optimization even with limited access to sophisticated optimization tools.

Pasture growth curve impacts the economic merit of extended lactation: a simulation study

Voluntarily increasing the calving interval beyond 12-months (extended lactation; EL) has possible benefits in reducing labour requirements, herd replacement rate, and surplus calf numbers. With limited information on feed balance and economic effects with EL, reductions in profitability were hypothesised. A MS Excel spreadsheet model was developed to compare calving systems: spring calving annually (Base), two herds between spring and autumn calving with 18-month calving intervals (EL18), or two herds calving in alternating springs with 24-month intervals (EL24). Weekly feed balances using pasture growth curves for four regions (Northland, Waikato, Canterbury, Southland) and partial budget analysis informed estimations of supplement costs and net income (NI). The EL24 system had lower supplement costs (-$240 /ha) and higher NI (+$439 /ha) than the Base system for Northland only. The EL18 systemalways had the highest annual milk production and total revenue, but highest supplement costs and lowest NI in the four regions. Replacement, calving, and mating costs were lower for EL systems than Base; however, costs for milking and repairs and maintenance were higher. This preliminary study indicates there is merit in investigating EL systems, particularly for Northland. Further data from EL systems are needed to increase confidence in the system’s potential.

Application of COVIDTracer to Inform Public Health Workforce Planning in Alaska During the COVID-19 Pandemic.

The COVID-19 pandemic has caused social and economic disruption worldwide and spurred numerous mitigation strategies, including state investments in training a large contact tracing and case investigation workforce. A team at the University of Alaska Anchorage evaluated implementation of the COVID-19 contact tracing and case investigation program of the State of Alaska Department of Health and Social Services, Division of Public Health, Section of Public Health Nursing. As part of that evaluation, the team used COVIDTracer, a spreadsheet modeling tool. COVIDTracer generated projections of COVID-19 case counts that informed estimates of workforce needs and case prioritization strategies. Case count projections approximated the reported epidemiologic curve with a median 7% difference in the first month. The accuracy of case count predictions declined after 1 month with a median difference of 80% in the second month. COVIDTracer inputs included previous case counts, the average length of time for telephone calls to cases and outreach to identified contacts, and the average number of contacts per case. As each variable increased, so too did estimated workforce needs. Decreasing the average time from exposure to outreach from 10 to 5 days reduced case counts estimated by COVIDTracer by approximately 93% during a 5-month period. COVIDTracer estimates informed Alaska's workforce planning and decisions about prioritizing case investigation during the pandemic. Lessons learned included the importance of being able to rapidly scale up and scale down workforce to adjust to a dynamic crisis and the limitations of prediction modeling (eg, that COVIDTracer was accurate for only about 1 month into the future). These findings may be useful for future pandemic preparedness planning and other public health emergency response activities.

Heat recovery opportunities from electrical substation transformers

The transformation of voltages in electrical substations leads to energy losses in the form of waste heat; the quantity of which depends on transformer size and electrical loading. This paper investigates how a novel waste heat source, namely transformer waste heat could be harvested and distributed via district heating networks. Firstly, the investigation considered nameplate heat loss factors to quantify the theoretical waste heat potential from electrical substation transformers in England, Wales and Northern Ireland, which varied from 3.0 to 5.4 TWh a−1, equivalent to between 0.7 and 1.25% of annual heat demand for these countries, depending on loading assumptions. A number of heat recovery approaches which could be integrated with existing transformer cooling systems were then proposed. A spreadsheet model was then developed to simulate heat recovery from a transformer, together with the upgrade of the recovered heat using a heat pump prior to delivery via district heating. The model was used to evaluate the merits of capturing transformer waste heat losses, estimated using industry supplied electrical loading data, to meet different heat network demands based on an existing network, compared to conventional heating technologies. Findings suggest that the system proposed can achieve levelised costs that are up to 17% lower than the running costs of air-source heat pumps, whilst reducing emissions by almost 80% when displacing gas boilers. The methodology hereby described can also be used to evaluate the feasibility of recovering transformer waste heat in other countries.

Techno-Economic Assessment of the Viability of Commercial Solar PV System in Port Harcourt, Rivers State, Nigeria

Supermarkets in Port Harcourt (PH) city, Nigeria, predominantly rely on diesel electricity generation due to grid instability, leading to high electricity prices. Although solar photovoltaic (PV) systems have been proposed as an alternative, these supermarkets have yet to adopt them, mainly due to high investment costs and a lack of awareness of the long-term financial and environmental benefits. This paper examines the technical and economic practicality of a PV system for these supermarkets using the PVsyst software and a spreadsheet model. Solar resources showed that PH has a daily average solar radiation and temperature of 4.21 kWh/m2/day and 25.73 °C, respectively. Market Square, the supermarket with the highest peak power demand of 59.8 kW and a 561 kWh/day load profile, was chosen as a case study. A proposed PV system with a power capacity of 232 kW, battery storage capacity of 34,021 Ah, a charge controller size of 100 A/560 V, and an inverter with a power rating of 60 V/75 kW has been designed to meet the load demand. The economic analysis showed a $266,936 life cycle cost, $0.14 per kWh levelized cost of electricity (LCOE), a 4-year simple payback time, and a 20.5% internal rate of return (IRR). The PV system is feasible due to its positive net present value (NPV) of $165,322 and carbon savings of 582 tCO2/year.

A Method for Teaching Overinvestment and Underinvestment Problems in Corporate Finance Courses

One of the most important concepts in corporate finance is agency theory. When the financiers of a firm are not also the managers of the firm, the incentives of different financiers (lenders and owners) often conflict. One of the biggest problems in agency theory is that of overinvestment and underinvestment due to misalignment of shareholder and bondholder incentives. While this problem has many real applications, the underlying concepts frequently seem quite abstract to business students. This paper presents an implementation of a simple numerical spreadsheet model that can clearly illustrate the conflicting incentives of lenders and owners that lead to overinvestment and underinvestment.

Stoichiometric model of a fully closed bioregenerative life support system for autonomous long-duration space missions

Bioregenerative life support systems (BLSS) are vital for long-duration and remote space missions to increase mission sustainability. These systems break down human waste materials into nutrients and CO2 for plants and other edible organisms, which in turn provide food, fresh water, and oxygen for astronauts. The central idea is to create a materially closed loop, which can significantly reduce mission mass and volume by cutting down or even eliminating disposable waste. In most BLSS studies only a fraction of the resources, such as food, are provided by the system itself, with the rest taken on board at departure or provided through resupply missions. However, for autonomous long-duration space missions without any possibility of resupply, a BLSS that generates all resources with minimal or no material loss, is essential. The goal of this study is to develop a stoichiometric model of a conceptually fully closed BLSS that provides all the metabolic needs of the crew and organisms. The MELiSSA concept of the European Space Agency is used as reference system, consisting of five interconnected compartments, each inhabited by different types of organisms. A detailed review of publicly available MELiSSA literature from 1989 to 2022 revealed that no existing stoichiometric model met the study’s requirements. Therefore, a new stoichiometric model was developed to describe the cycling of the elements C, H, O, and N through all five MELiSSA compartments and one auxiliary compartment. A compact set of chemical equations with fixed coefficients was established for this purpose. A spreadsheet model simulates the flow of all relevant compounds for a crew of six. By balancing the dimensions of the different compartments, a high degree of closure is attained at steady state, with 12 out of 14 compounds exhibiting zero loss, and oxygen and CO2 displaying only minor losses between iterations. This is the first stoichiometric model of a MELiSSA-inspired BLSS that describes a continuous provision of 100% of the food and oxygen needs of the crew. The stoichiometry serves as the foundation of an agent-based model of the MELiSSA loop, as part of the Evolving Asteroid Starships (E|A|S) research project.