Embedded Spreadsheet Modelling

Angela Collins

DOI: https://doi.org/10.48550/arXiv.0908.1191

IF: 6.4588

2009-08-08

Human-Computer Interaction

Abstract:In larger accounting firms, specialist modellers typically sit in separate teams. This paper will look at the advantages of embedding a specialist modeller within a Corporate Finance Team.

What problem does this paper attempt to address?

Spreadsheets Grow Up: Three Spreadsheet Engineering Methodologies for Large Financial Planning Models

Thomas A. Grossman,Ozgur Ozluk

DOI: https://doi.org/10.48550/arXiv.1008.4174

2010-08-24

Software Engineering

Abstract:Many large financial planning models are written in a spreadsheet programming language (usually Microsoft Excel) and deployed as a spreadsheet application. Three groups, FAST Alliance, Operis Group, and BPM Analytics (under the name "Spreadsheet Standards Review Board") have independently promulgated standardized processes for efficiently building such models. These spreadsheet engineering methodologies provide detailed guidance on design, construction process, and quality control. We summarize and compare these methodologies. They share many design practices, and standardized, mechanistic procedures to construct spreadsheets. We learned that a written book or standards document is by itself insufficient to understand a methodology. These methodologies represent a professionalization of spreadsheet programming, and can provide a means to debug a spreadsheet that contains errors. We find credible the assertion that these spreadsheet engineering methodologies provide enhanced productivity, accuracy and maintainability for large financial planning models
Developing a Repeating Model Using the Structured Spreadsheet Modelling and Implementation Methodology

Paul Mireault

DOI: https://doi.org/10.48550/arXiv.1602.06453

2016-02-20

Software Engineering

Abstract:Spreadsheets often have variables and formulas that are similar, differing only by the fact that they refer to different instances of an entity. For example, the calculation of the sales revenues of the South and East regions are Revenues South = Price * Quantity Sold South and Revenues East = Price * Quantity Sold East. In this paper, we present a conceptual modelling approach that takes advantage of these similarities and leads the spreadsheet developer to the formula Revenues = Price * Quantity. We then present simple but strict rules to implement the spreadsheet.
Excel Modelling - Transparency, Auditing and Business Use

Susan Allan

DOI: https://doi.org/10.48550/arXiv.0909.2452

2009-09-14

Abstract:Within Lloyds Banking Group the heritage HBOS Corporate division deals with Corporate loans, and is required to assess these loans for risk in accordance with the Basle Accord regulations. Statistical Risk Rating models are developed by the risk analysts to assess the obligors credit worthiness. It is necessary then to provide the bankers who originated the loan ('Relationship Managers' or RMs) with an assessment tool to generate the loan rating upon which they base their lending decisions. Heritage HBoS Corporate required a new model build system for holding its Risk Rating models in 2006 as a result of more complex models being created to comply with the Basle Accord. The use of Excel was promoted by the IT department for a number of reasons; the Excel solution now in use is reviewed in this paper.

Software Engineering,Human-Computer Interaction
Multidisciplinary Engineering Models: Methodology and Case Study in Spreadsheet Analytics

David Birch,Helen Liang,Paul H J Kelly,Glen Mullineux,Tony Field,Joan Ko,Alvise Simondetti

DOI: https://doi.org/10.48550/arXiv.1401.4582

2014-01-19

Abstract:This paper demonstrates a methodology to help practitioners maximise the utility of complex multidisciplinary engineering models implemented as spreadsheets, an area presenting unique challenges. As motivation we investigate the expanding use of Integrated Resource Management(IRM) models which assess the sustainability of urban masterplan designs. IRM models reflect the inherent complexity of multidisciplinary sustainability analysis by integrating models from many disciplines. This complexity makes their use time-consuming and reduces their adoption. We present a methodology and toolkit for analysing multidisciplinary engineering models implemented as spreadsheets to alleviate such problems and increase their adoption. For a given output a relevant slice of the model is extracted, visualised and analysed by computing model and interdisciplinary metrics. A sensitivity analysis of the extracted model supports engineers in their optimisation efforts. These methods expose, manage and reduce model complexity and risk whilst giving practitioners insight into multidisciplinary model composition. We report application of the methodology to several generations of an industrial IRM model and detail the insight generated, particularly considering model evolution.

Software Engineering
Structured Spreadsheet Modelling and Implementation with Multiple Dimensions - Part 1: Modelling

Paul Mireault

DOI: https://doi.org/10.48550/arXiv.1801.09777

2018-02-08

Abstract:Dimensions are an integral part of many models we use every day. Without thinking about it, we frequently use the time dimension: many financial and accounting spreadsheets have columns representing months or years. Representing a second dimension is often done by repeating blocs of formulas in a worksheet or creating multiple worksheets with the same structure.

Software Engineering
Issues in Strategic Decision Modelling

Paula Jennings

DOI: https://doi.org/10.48550/arXiv.0804.0937

IF: 6.4588

2008-04-06

Human-Computer Interaction

Abstract:[Spreadsheet] Models are invaluable tools for strategic planning. Models help key decision makers develop a shared conceptual understanding of complex decisions, identify sensitivity factors and test management scenarios. Different modelling approaches are specialist areas in themselves. Model development can be onerous, expensive, time consuming, and often bewildering. It is also an iterative process where the true magnitude of the effort, time and data required is often not fully understood until well into the process. This paper explores the traditional approaches to strategic planning modelling commonly used in organisations and considers the application of a real-options approach to match and benefit from the increasing uncertainty in today's rapidly changing world.
Mitigating Spreadsheet Risk in Complex Multi-Dimensional Models in Excel

Steve Litt

DOI: https://doi.org/10.48550/arXiv.1802.01640

2018-02-05

Software Engineering

Abstract:Microsoft Excel is the most ubiquitous analytical tool ever built. Companies around the world leverage it for its power, flexibility and ease of use. However, spreadsheets are manually intensive and prone to error, making it difficult for companies to control spreadsheet risk. The following solution is designed to mitigate spreadsheet risk for a set of problems commonly addressed in a spreadsheet defined as "complex multi-dimensional models". "Complex" referring to certain types of applications that require functionality such as sophisticated algorithms, challenging hierarchies and database write-back (i.e. planning, forecasting, etc.) and "multi-dimensional" referring to providing capabilities such as reporting, data input forms and ad hoc analysis on the different attributes associated with the resulting model. The solution is defined as a "PivotModel" because it works similarly to a PivotTable but is designed to leverage the robust capabilities of the Microsoft Excel platform.
Alternative Spreadsheet Model Designs for an Operations Management Model Embedded in a Periodic Business Process

Thomas A. Grossman,Vijay Mehrotra,Mouwafac Sidaoui

DOI: https://doi.org/10.48550/arXiv.1802.00484

2018-02-02

Abstract:We present a widely-used operations management model used in supply and distribution planning, that is typically embedded in a periodic business process that necessitates model modification and reuse. We consider three alternative spreadsheet implementations, a data-driven design, a canonical (textbook) design, and a novel (table-driven) technical design. We evaluate each regarding suitability for accuracy, modification, analysis, and transfer. We consider the degree of training and technical sophistication required to utilize each design. The data-driven design provides insight into poor spreadsheet practices by naïve modelers. The technical design can be modified for new data and new structural elements without manual writing or editing of cell formulas, thus speeding modification and reducing risk of error. The technical design has potential for use with other classes of models. We identify opportunities for future research.

Software Engineering
Structuring Spreadsheets with the "Lish" Data Model

Alan Hall,Michel Wermelinger,Tony Hirst,Santi Phithakkitnukoon

DOI: https://doi.org/10.48550/arXiv.1801.08603

2018-01-26

Abstract:A spreadsheet is remarkably flexible in representing various forms of structured data, but the individual cells have no knowledge of the larger structures of which they may form a part. This can hamper comprehension and increase formula replication, increasing the risk of error on both scores. We explore a novel data model (called the "lish") that could form an alternative to the traditional grid in a spreadsheet-like environment. Its aim is to capture some of these higher structures while preserving the simplicity that makes a spreadsheet so attractive. It is based on cells organised into nested lists, in each of which the user may optionally employ a template to prototype repeating structures. These template elements can be likened to the marginal "cells" in the borders of a traditional worksheet, but are proper members of the sheet and may themselves contain internal structure. A small demonstration application shows the "lish" in operation.

Software Engineering
Excelsior: Bringing the Benefits of Modularisation to Excel

Jocelyn Paine

DOI: https://doi.org/10.48550/arXiv.0803.2027

2008-03-14

Abstract:Excel lacks features for modular design. Had it such features, as do most programming languages, they would save time, avoid unneeded programming, make mistakes less likely, make code-control easier, help organisations adopt a uniform house style, and open business opportunities in buying and selling spreadsheet modules. I present Excelsior, a system for bringing these benefits to Excel.

Software Engineering
Transforming Critical Spreadsheets into Web Applications at Zurich Financial

Sebastian Dewhurst

DOI: https://doi.org/10.48550/arXiv.1009.1407

2010-09-08

Abstract:In the insurance industry, spreadsheets have emerged as an invaluable tool to for product pricing, because it is relatively straightforward to create and maintain complex pricing models using Excel. In fact, Excel is often preferred to "hard-code" whenever there are frequent changes to the calculations and business logic which under-pin the pricing of an insurance product. However, problems arise as soon as spreadsheets are deployed to end-users: version control, security of intellectual property, and ensuring correct usage are obvious issues; frequently, integration with other systems is also a requirement. Zurich Financial Services Group is a leading financial services provider; several possible solutions to these problems have been evaluated, and EASA has been selected as the preferred technology. Other spreadsheet collaboration approaches which were considered include Excel Services, and/or custom-built software; however, EASA has provided clear benefits over these strategies.

Software Engineering,Computers and Society
Qtier-Rapor: Managing Spreadsheet Systems & Improving Corporate Performance, Compliance and Governance

Keith Bishop

DOI: https://doi.org/10.48550/arXiv.0803.0011

2008-02-29

Other Computer Science

Abstract:Much of what EuSpRIG discusses is concerned with the integrity of individual spreadsheets. In businesses, interlocking spreadsheets are regularly used to fill functional gaps in core administrative systems. The growth and deployment of such integrated spreadsheet SYSTEMS raises the scale of issues to a whole new level. The correct management of spreadsheet systems is necessary to ensure that the business achieves its goals of improved performance and good corporate governance, within the constraints of legislative compliance - poor management will deliver the opposite. This paper is an anatomy of the real-life issues of the commercial use of spreadsheets in business, and demonstrates how Qtier-Rapor has been used to instil best practice in the use of integrated commercial spreadsheet systems.
Excel best practices

Robert J. Balik

DOI: https://doi.org/10.1108/03074350910949781

2009-04-10

Managerial Finance

Abstract:Purpose Currently many jobs for undergraduate finance majors require that the student demonstrate advanced Excel modeling skills. The purpose of this paper is to illustrate and explain the Excel Best Practices which should enhance their financial modeling efficiency. Design/methodology/approach The focus is a way to teach the Excel Best Practices when teaching financial modeling with Excel 2007. It uses a chronological modeling procedure that is consistent with current learning theory and the way students should use these Excel Best Practices. A capital budgeting replacement problem is used to illustrate many of the Excel Best Practices. Findings It was found that using a chronological modeling procedure is consistent with current learning theory. Originality/value Using the procedures mentioned in this paper should result in efficient financial modeling. Efficient models are created in less time, have fewer errors, if any, and are designed for ease of use.

English Else
Will Dynamic Arrays finally change the way Models are built?

Peter Bartholomew

2024-02-05

Abstract:Spreadsheets offer a supremely successful and intuitive means of processing and exchanging numerical content. Its intuitive ad-hoc nature makes it hugely popular for use in diverse areas including business and engineering, yet these very same characteristics make it extraordinarily error-prone; many would question whether it is suitable for serious analysis or modelling tasks. A previous EuSpRIG paper examined the role of Names in increasing solution transparency and providing a readable notation to forge links with the problem domain. Extensive use was made of CSE array formulas, but it is acknowledged that their use makes spreadsheet development a distinctly cumbersome task. Since that time, the new dynamic arrays have been introduced and array calculation is now the default mode of operation for Excel. This paper examines the thesis that their adoption within a more professional development environment could replace traditional techniques where solution integrity is important. A major advantage of fully dynamic models is that they require less manual intervention to keep them updated and so have the potential to reduce the attendant errors and risk.

Software Engineering
Spreadsheets in Financial Departments: An Automated Analysis of 65,000 Spreadsheets using the Luminous Technology

Kevin McDaid,Ronan MacRuairi,Neil Clynch,Kevin Logue,Cian Clancy,Shane Hayes

DOI: https://doi.org/10.48550/arXiv.1111.6866

2011-11-29

Software Engineering

Abstract:Spreadsheet technology is a cornerstone of IT systems in most organisations. It is often the glue that binds more structured transaction-based systems together. Financial operations are a case in point where spreadsheets fill the gaps left by dedicated accounting systems, particularly covering reporting and business process operations. However, little is understood as to the nature of spreadsheet usage in organisations and the contents and structure of these spreadsheets as they relate to key business functions with few, if any, comprehensive analyses of spreadsheet repositories in real organisations. As such this paper represents an important attempt at profiling real and substantial spreadsheet repositories. Using the Luminous technology an analysis of 65,000 spreadsheets for the financial departments of both a government and a private commercial organisation was conducted. This provides an important insight into the nature and structure of these spreadsheets, the links between them, the existence and nature of macros and the level of repetitive processes performed through the spreadsheets. Furthermore it highlights the organisational dependence on spreadsheets and the range and number of spreadsheets dealt with by individuals on a daily basis. In so doing, this paper prompts important questions that can frame future research in the domain.
Simulating an Object-Oriented Financial System in a Functional Language

Lee Braine,Keith Haviland,Owen Smith-Jaynes,Andy Vautier,Chris Clack

DOI: https://doi.org/10.48550/arXiv.2011.11593

2020-12-03

Abstract:This paper summarises a successful application of functional programming within a commercial environment. We report on experience at Accenture's Financial Services Solution Centre in London with simulating an object-oriented financial system in order to assist analysis and design. The work was part of a large IT project for an international investment bank and provides a pragmatic case study.

Computational Engineering, Finance, and Science
A Spreadsheet Auditing Tool Evaluated in an Industrial Context

Markus Clermont,Christian Hanin,Roland T. Mittermeir

DOI: https://doi.org/10.48550/arXiv.0805.1741

2008-05-13

Abstract:Amongst the large number of write-and-throw-away spreadsheets developed for one-time use there is a rather neglected proportion of spreadsheets that are huge, periodically used, and submitted to regular update-cycles like any conventionally evolving valuable legacy application software. However, due to the very nature of spreadsheets, their evolution is particularly tricky and therefore error-prone. In our strive to develop tools and methodologies to improve spreadsheet quality, we analysed consolidation spreadsheets of an internationally operating company for the errors they contain. The paper presents the results of the field audit, involving 78 spreadsheets with 60,446 non-empty cells. As a by-product, the study performed was also to validate our analysis tools in an industrial context. The evaluated auditing tool offers the auditor a new view on the formula structure of the spreadsheet by grouping similar formulas into equivalence classes. Our auditing approach defines three similarity criteria between formulae, namely copy, logical and structural equivalence. To improve the visualization of large spreadsheets, equivalences and data dependencies are displayed in separated windows that are interlinked with the spreadsheet. The auditing approach helps to find irregularities in the geometrical pattern of similar formulas.

Human-Computer Interaction
Consensus-Free Spreadsheet Integration

Brandon Baylor,Eric Daimler,James Hansen,Esteban Montero,Ryan Wisnesky

DOI: https://doi.org/10.48550/arXiv.2209.14457

2022-09-28

Databases

Abstract:We describe a method for merging multiple spreadsheets into one sheet, and/or exchanging data among the sheets, by expressing each sheet's formulae as an algebraic (equational) theory and each sheet's values as a model of its theory, expressing the overlap between the sheets as theory and model morphisms, and then performing colimit, lifting, and Kan-extension constructions from category theory to compute a canonically universal integrated theory and model, which can then be expressed as a spreadsheet. Our motivation is to find methods of merging engineering models that do not require consensus (agreement) among the authors of the models being merged, a condition fulfilled by our method because theory and model morphisms are semantics-preserving. We describe a case study of this methodology on a real-world oil and gas calculation at a major energy company, describing the theories and models that arise when integrating two different casing pressure test (MASP) calculation spreadsheets constructed by two non-interacting engineers. We also describe the automated theorem proving burden associated with both verifying the semantics preservation of the overlap mappings as well as verifying the conservativity/consistency of the resulting integrated sheet. We conclude with thoughts on how to apply the methodology to scale engineering efforts across the enterprise.
The Semantic Spreadsheet

Behzad Farokhi,Katharina Dost,Gerald Weber,Jing Sun,Christof Lutteroth

DOI: https://doi.org/10.1109/ICECCS51672.2020.00027

2020-01-01

Abstract:Spreadsheets are one of the most widely used data management tools. Although intuitive and easy to use, they suffer from a number of issues. Numerous publications indicate that most spreadsheets contain errors and that spreadsheet-based data shadow systems lead to problems such as the "spreadmart dilemma" creating inconsistent views on organizational data. In this paper, we describe "The Semantic Spreadsheet", a new data model for a semantically accurate spreadsheet system. Unlike the existing data models, the described model is a presentation independent data model based on the Resource Description Framework (RDF) that avoids the spreadmart dilemma by providing a semantically sound data structure. We describe the set-theoretic and relational algebraic operations on the data model and show how it can improve data integrity.
Now You're Thinking With Structures: A Concept for Structure-based Interactions with Spreadsheets

Patrick Koch

DOI: https://doi.org/10.48550/arXiv.1809.03435

2018-09-11

Abstract:Spreadsheets are the go-to tool for computerized calculation and modelling, but are hard to comprehend and adapt after reaching a certain complexity. In general, cognition of complex systems is facilitated by having a higher order mental model of the system in question to work with. We therefore present a concept for structure-aware understanding of and interaction with spreadsheets that extends previous work on structure inference in the domain. Following this concept, structural information is used to enrich visualizations, reactively enhance traditional user actions, and provide tools to proactively alter the overall spreadsheet makeup instead of individual cells The intended systems should, in first approximation, not replace common spreadsheet tools, but provide an additional layer of functionality alongside the established interface. In ongoing work, we therefore implemented a tool for structure inference and visualization along the common spreadsheet layout. Based on this framework, we plan to introduce the envisioned proactive and reactive interaction mechanics, and finally provide structure-aware unctionality as an add-in for common spreadsheet processors. We believe that providing the tools for thinking about and interacting with spreadsheets in this manner will benefit users both in terms of productivity and overall spreadsheet quality.

Software Engineering

Embedded Spreadsheet Modelling

Spreadsheets Grow Up: Three Spreadsheet Engineering Methodologies for Large Financial Planning Models

Developing a Repeating Model Using the Structured Spreadsheet Modelling and Implementation Methodology

Excel Modelling - Transparency, Auditing and Business Use

Multidisciplinary Engineering Models: Methodology and Case Study in Spreadsheet Analytics

Structured Spreadsheet Modelling and Implementation with Multiple Dimensions - Part 1: Modelling

Issues in Strategic Decision Modelling

Mitigating Spreadsheet Risk in Complex Multi-Dimensional Models in Excel

Alternative Spreadsheet Model Designs for an Operations Management Model Embedded in a Periodic Business Process

Structuring Spreadsheets with the "Lish" Data Model

Excelsior: Bringing the Benefits of Modularisation to Excel

Transforming Critical Spreadsheets into Web Applications at Zurich Financial

Qtier-Rapor: Managing Spreadsheet Systems & Improving Corporate Performance, Compliance and Governance

Excel best practices

Will Dynamic Arrays finally change the way Models are built?

Spreadsheets in Financial Departments: An Automated Analysis of 65,000 Spreadsheets using the Luminous Technology

Simulating an Object-Oriented Financial System in a Functional Language

A Spreadsheet Auditing Tool Evaluated in an Industrial Context

Consensus-Free Spreadsheet Integration

The Semantic Spreadsheet

Now You're Thinking With Structures: A Concept for Structure-based Interactions with Spreadsheets