Basic Spreadsheet Research Articles

A Simple Model of Flood Peak Attenuation

AbstractA simple analytical model was developed for evaluating the attenuation of flood wave peak discharge. The physically‐based model represents the flood wave along its trajectory, based on the diffusive model. Relative peak discharge decreases along the downstream distance according to a power function. The distance is scaled by the attenuation factor related to river hydrodynamics (flow rating, hydraulic diffusivity, celerity, and floodplain storage) and input hydrograph (initial peak discharge, hydrograph volume, and its relative curvature). It also informs the attenuation length, which is a practical indicator of the river distance in which discharge decreases by a given factor. Sensitivity analyses indicate that initial peak discharge, volume, floodplain storage, and slope are the governing factors of attenuation. Model's validity and accuracy were demonstrated by reproducing data from (a) numerical solutions of the Saint‐Venant equations covering a wide range of conditions, (b) 29 observations from 11 historical dam‐breaks, (c) 15 observations of natural floods in seven rivers and (d) a detailed hydrodynamic model. The model errors were generally lower than 10% and not larger than the typical uncertainty of flood observations. The accuracy is higher than simplified empirical models and analogous to a detailed hydrodynamic model that is representative of current practice. The proposed flood attenuation model can be easily applied using a few common parameters and a simple equation in a basic spreadsheet. It is suitable for practical applications such as first assessments of natural and dam‐break floods, engineering design, and analyses of large river networks supported by remote sensing data.

Renewable Energy Storage in the Republic of South Africa

The objectives were to perform extensive research on the South African Renewable Energy Storage capabilities and business case. The focus was to investigate the advantages it would bring to the country to invest in energy storage solutions to aid in not only meeting the electricity demand forecast for the next 25 to 40 years, but also focusing on the energy dips, known as the “Duck Curve”. This could provide flexibility to the National Grid in parallel with reducing the carbon footprint of the current generating stations and the transmission-and distribution networks by either remodelling them, or to completely replace them with a better solution such as renewable energy with storage. The final expected output of the research would be to develop a basic spreadsheet calculator that is capable of calculating the energy and financial impact it would have when integrating a certain amount of storage into the national grid.

Getting Students Ready for Accounting Spreadsheets: Training for Basic Spreadsheet Skills with Pre/Post Assessments

ABSTRACT This case is intended to impart basic spreadsheet skills to students that do not have them. It trains them to transform data, create formulas (with functions, IF statements, and absolute references), aggregate data, look up values, configure pivot tables, sort data, record macros, and extract spreadsheet tasks from conversation. These skills are needed for subsequent spreadsheet analyses for solving business problems. Suitable for students that can copy cells and develop simple formulas, the case offers pre- and post-assessment questions. The case provides scaffolding resources: flashcards for Excel features, a work plan template, a narrated video for getting started with the work plan, and guidance (scripts with screenshots and narrated videos) for performing the spreadsheet tasks. Using the context of a gradebook application, which is familiar to students, the case minimizes the cognitive load students experience because they are not trying to learn accounting and spreadsheet skills simultaneously.

Pedagogical Practices of Accounting Departments Addressing AACSB Technology Requirements

ABSTRACTTechnology skills are essential for accounting professionals and should be equally important in accounting curricula. Institutions measure technology-related curricular innovations in part through compliance with AACSB Standard A5. We investigate efforts to meet the requirements of Standard A5 by surveying AIS educators from 85 universities to examine efforts to prepare for accreditation review related to technology in accounting curricula. Quantitative results suggest that most institutions feel prepared but also provide some contradictory information that, when combined with qualitative feedback, raises questions about the degree of progress in technology integration. Common issues include widespread lack of increased faculty training, over-reliance on AIS classes to meet accreditation standards, and inclusion of basic spreadsheet and visualization skills as “technology” or “data analytics.” We suggest a pathway to improve accounting pedagogy as it relates to technology through phased curricular changes and issue a call for administrators to support increased accounting faculty technology training.

Application of the common base method to regression and analysis of covariance (ANCOVA) in qPCR experiments and subsequent relative expression calculation

BackgroundQuantitative polymerase chain reaction (qPCR) is the technique of choice for quantifying gene expression. While the technique itself is well established, approaches for the analysis of qPCR data continue to improve.ResultsHere we expand on the common base method to develop procedures for testing linear relationships between gene expression and either a measured dependent variable, independent variable, or expression of another gene. We further develop functions relating variables to a relative expression value and develop calculations for determination of associated confidence intervals.ConclusionsTraditional qPCR analysis methods typically rely on paired designs. The common base method does not require such pairing of samples. It is therefore applicable to other designs within the general linear model such as linear regression and analysis of covariance. The methodology presented here is also simple enough to be performed using basic spreadsheet software.

Exact and heuristic approaches for the automated design of medical trainees rotation schedules

This article addresses the problem of designing rotation schedules for the training of medical students in a healthcare institution in Colombia. Rotations are carried out along the course of a six months time period during which students rotate through different services both during one or multiple fortnightly periods. The hospital receives trainees from different universities that present heterogeneous requirements, and must accomplish different rotations constraints even in those cases when they come from the same university. Two different approaches are proposed to tackle the problem. First, a mathematical programming model is considered. Secondly, a metaheuristic approach based on Variable Neighborhood Search is adapted to solve the problem in an efficient manner. In both cases the objective function is proposed to be either a linear or a quadratic function that seeks a stable allocation of personnel to the services all along the considered time horizon. The proposed techniques are implemented as a part of a basic spreadsheet that helps personnel in charge generate, validate and fix the proposed plans as needed. The effectiveness of the methodology is tested through ex-post analysis, comparing it against schedules generated by the personnel in charge at the hospital, and over simulated data. The results indicate that the applied metaheuristic is competitive against specialized optimization software and facilitates the work of optimization for the health professionals in charge, reducing the planning tasks from several days to minutes.

Surveying industry advisors to select data analytics topics for all business majors

In an effort to expand data analytics instruction, universities have launched data analytics majors and graduate programs in data analytics. While this effort meets the need for developing data analytics specialists, an equally important need is to improve the data competencies of undergraduate business students who do not major in analytics but still need to have competencies with analytics. However, business students not majoring in data analytics have limited credit hours available for data analytics. Therefore, it is necessary to select data analytics topics that meet employers' needs. We hypothesized that surveying industry advisors would help us revise the current curriculum to incorporate data analytics learning objectives that are both necessary and sufficient. The results showed that the most important data competencies are basic spreadsheet skills (86%), intermediate spreadsheet skills (82%), retrieving relevant data (86%), documenting data (92%), and presenting data (96%). The least important area is teaching software programming to non-analytics majors (14.5%). As a result of this study, we were able to develop a new curriculum to meet employer needs by revising previous courses without increasing required credit hours.

Teaching basic spreadsheet skills with peer tutoring

PurposeKnowledge of spreadsheet tools like Microsoft Excel is a valuable skill to have in today’s job market. The preliminary assessment of a group of business school students shows that most of them struggle to perform simple tasks in a spreadsheet. The purpose of this paper is to propose using student tutors to teach these skills.Design/methodology/approachThe authors identify students proficient in Excel as tutors and organize one-on-one peer tutoring lessons. The authors compare the Excel competency level of students prior to and after the tutoring sessions.FindingsThe results suggest that most students with minimal Excel skills significantly improve their competency level after tutoring.Originality/valueThe proposed hands-on approach appears to be effective in helping students acquire basic Excel capabilities.

Scaling Analysis of a Moving Point Heat Source in Steady-State on a Semi-Infinite Solid

This paper presents a systematic scaling analysis of the point heat source in steady-state on a semi-infinite solid. It is shown that all characteristic values related to an isotherm can be reduced to a dimensionless expression dependent only on the Rykalin number (Ry). The maximum width of an isotherm and its location are determined for the first time in explicit form for the whole range of Ry, with an error below 2% from the exact solution. The methodology employed involves normalization, dimensional analysis, asymptotic analysis, and blending techniques. The expressions developed can be calculated using a handheld calculator or a basic spreadsheet to estimate, for example, the width of a weld or the size of zone affected by the heat source in a number of processes. These expressions are also useful to verify numerical models.

Novel Spreadsheet Direct Method for Optimal Control Problems

We devise a simple yet highly effective technique for solving general optimal control problems in Excel spreadsheets. The technique exploits Excel’s native nonlinear programming (NLP) Solver Command, in conjunction with two calculus worksheet functions, namely, an initial value problem solver and a discrete data integrator, in a direct solution paradigm adapted to the spreadsheet. The technique is tested on several highly nonlinear constrained multivariable control problems with remarkable results in terms of reliability, consistency with pseudo-spectral reported answers, and computing times in the order of seconds. The technique requires no more than defining a few analogous formulas to the problem mathematical equations using basic spreadsheet operations, and no programming skills are needed. It introduces an alternative, simpler tool for solving optimal control problems in social and natural science disciplines.

PRM68 - Markov Models For Health Economic Evaluation Modelling In R With The Heemod Package

Most Markov models are built using basic spreadsheet software, which has drawbacks: analyses are hard to reproduce and lack of transparency, errors are difficult to spot, track and correct, and graphic capabilities are lacking. The R language can overcome these issues through script-based approaches, but programming models is not a simple matter, which has limited its use in this field. Our main objective was to develop a free and open-source R package for Markov models focused on reproducibility and ease of use. We developed an R package to compute the models described in the reference textbook “Decision Modelling for Health Economic Evaluation” by Briggs et al. We aimed to facilitate easy and transparent model writing that ensured security and reproducibility. To facilitate model building, a graphical user interface was developed, allowing output of model scripts for peer review. The finalized package, heemod, and the graphical user interface were made available for free on CRAN, the public and open-source R package repository. We reproduced in a concise and readable format all the results of the analyses described in “Decision Modelling for Health Economic Evaluation”, such as homogeneous and non-homogeneous (with time-varying properties) Markov models, as well as sensitivity and probabilistic uncertainty analysis (where it is possible to specify arbitrary distributions and correlation structures between parameters). This work shows that it is possible to develop complex Markov models easily in the R language without sacrificing transparency, reproducibility or mathematical exactitude. The free and open-source license facilitates code review and improvement of the package by third-party experts. We hope the availability of this package will facilitate the use of script-based approaches to health evaluation modelling and help improve the overall quality and reproducibility of studies in this domain.

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.

Enhancing Computational Thinking with Spreadsheet and Fractal Geometry: Part 1

For most primary and high school level students, computer is a game-playing tool. They might have taught word processing and PowerPoint presentation tool and very basic spreadsheet usage for computing, but, one of the essential skill required for survival in modern technological society is “Computational thinking” that combines power of human intelligence and computing agents for solving complex problems facing the society. It is found that this skill is not imparted to the primary and high school level students. In this context, the concept of computational thinking, its need and the attempts that are being made world over to impart this skill at various levels of education is discussed in the part -1 (out of four) of this article. It is proposed that concept of fractal and its implementation in spreadsheet can be one of the starting points at high school level to induce students into computational thinking. Also it is shown how to create various kinds of fractals in spreadsheets without using any programming. Different fractals require different computational strategies to implement in a spreadsheet. It is hypothesized that practice in the development of such strategies improve the ‘abstraction’ and computational thinking capabilities of the students.

Cylindrical shell bending theory for orthotropic shells under general axisymmetric pressure distributions

The axisymmetric linear bending theory of shells is treated for thin-walled orthotropic cylindrical shells under any smooth axial distribution of normal and shear pressures. The equations are developed, solved and explored in this paper. The derivation is presented in terms of a generalised Hooke’s Law with coupling between the axial membrane stress resultant and axial bending moment. This formulation permits the shell to be alternatively treated as a composite isotropic cylinder with axial stiffeners, rendering it useful for many practical problems. A linear kinematic relationship is assumed between the generalised strains and displacements. Expressions for the linear axial bending half-wavelength are presented for special cases of the stiffness matrix.The equations developed here are simple enough to be applied to the analysis of anisotropic thin-walled cylindrical shells using basic spreadsheet tools, removing the need to perform an onerous finite element analysis. Engineering applications potentially include corrugated metal, axially-stiffened or reinforced concrete silos under granular solid pressures, tanks under hydrostatic pressures, tubular piles under earth pressures, gas-filled cisterns and chimneys.

Assessment of exposure to bovine spongiform encephalopathy in an hypothetical country

The authors present a basic quantitative spreadsheet model to evaluate the risk of bovine spongiform encephalopathy (BSE) within a national setting. The model is based on information from BSE risk assessments undertaken in Latin American countries. The analysis focuses on the level of regulatory implementation and its impact over different time periods and estimates the potential impact if one BSE-infected animal is introduced into the production cycle. The information is consolidated so that the results of the evaluation can be presented for a hypothetical country, 'Country X'. Evaluating the BSE cycle within a country may help in making decisions on where preventive or control measures should be placed and/or enforced. Such an evaluation may also be used as the exposure assessment of a risk assessment, recommended by the World Organisation for Animal Health to determine the BSE risk status of a given country.

Whirlpool Corporation Global Procurement

This case introduces a framework for cost modeling. Two data sets (one for injection-molded plastic parts and another for compressors) allow students to apply the cost-driver framework in conjunction with basic spreadsheet and regression analyses. Although obviously applicable in a course on supply chain management, the case can also be used to teach competitive cost analysis for strategic decision making.

Chemometric analysis for comparison of heparan sulphate oligosaccharides

Heparan sulphate (HS) is a glycosaminoglycan present in all metazoan organisms. It is an unbranched chain made up of repeating disaccharide units of uronic acid and glucosamine sugars, and is present in both cells and the extracellular matrix. It is one of the most structurally diverse biological molecules and its biosynthesis involves a variety of enzymic modification steps. Unlike the genome and the transcriptome, HS synthesis is not template driven. Nevertheless, the HS structure and function are highly regulated with modification steps occurring in discrete regions of the polysaccharide chain to give rise to diverse structures interacting with, and regulating, many different proteins. The resulting variation leads to diverse biological roles of HS. To study this structural diversity, rapid isolation and characterization of HS from small amounts of tissues, followed by digestion with bacterially derived enzymes (heparitinases) and chromatography techniques can be used to separate HS oligosaccharides of different size and charge. However, this leads to complex datasets where comparison of just a few samples leads to difficulties in data analysis. Using automatically integrated peak data obtained from chromatographic software, one can apply the effective disc technique to the data points to obtain the centre of mass in each dataset, for example from different murine tissues. This allows facile comparative analysis of different datasets. When the cloud of points displays some preferential direction (anisotropy), it is preferable to compute its effective ellipse. Analysis of the dynamics of the cloud of points for repeated experiments allows the quantification of their reproducibility through evaluation of an average Lyapunov exponent characterizing the area-preserving nature of a sequence of effective ellipses. These basic mathematical approaches allow a more systematic comparison of datasets derived from structural analysis using basic spreadsheet software calculations and contribute to the development of system biology strategies for tackling biocomplexity of HS polysaccharides.

Pilot Model for Estimating Pedestrian Intersection Crossing Volumes

Better data on pedestrian volumes are needed to improve the safety, comfort, and convenience of pedestrian movement. This data collection requires more carefully developed methodologies for counting pedestrians as well as improved methods of modeling pedestrian volumes. The methodology used to create a simple pilot model of pedestrian intersection crossing volumes in Alameda County, California, is described. The model is based on weekly pedestrian volumes at a sample of 50 intersections with a wide variety of surrounding land uses, transportation system attributes, and neighborhood socioeconomic characteristics. Three alternative model structures were considered, and the final recommended model has a good overall fit (adjusted R2 = .897). Statistically significant factors in the model include the total population within a 0.5-mi radius, number of jobs within a 0.25-mi radius, number of commercial retail properties within a 0.25-mi radius, and the presence of a regional transit station within a 0.1-mi radius of an intersection. The model has a simple structure, and it can be implemented by practitioners using geographic information systems and a basic spreadsheet program. Because the study is based on a relatively small number of intersections in one urban area, additional research is needed to refine the model and determine its applicability in other areas.

Active learning in an undergraduate management science course through the use of a mobile computer lab

Our business school's undergraduate degree program includes a required spreadsheet management science course taught at the third-year level. Employers, faculty and students consistently indicated that this course was not successful in teaching management science or even basic spreadsheet modelling skills. To improve students' understanding and retention of the course content, we purchased and implemented a 'mobile computer lab' that could be set up in a regular classroom. We discuss how the lab supported a change to active learning, in which informal student groups would 'discover' management science techniques, and we provide some examples of the exercises we have incorporated in the course. For instructors who are interested in implementing a mobile lab, we also provide details on the infrastructure of the lab, costs, software and hardware security, and classroom logistics.

Active learning in an undergraduate management science course through the use of a mobile computer lab

Our business school's undergraduate degree program includes a required spreadsheet management science course taught at the third-year level. Employers, faculty and students consistently indicated that this course was not successful in teaching management science or even basic spreadsheet modelling skills. To improve students' understanding and retention of the course content, we purchased and implemented a 'mobile computer lab' that could be set up in a regular classroom. We discuss how the lab supported a change to active learning, in which informal student groups would 'discover' management science techniques, and we provide some examples of the exercises we have incorporated in the course. For instructors who are interested in implementing a mobile lab, we also provide details on the infrastructure of the lab, costs, software and hardware security, and classroom logistics.