Book

Purchase the book:

Conference Papers and Presentations

Implementation Strategies for Multidimensional Spreadsheets

2019-07-11 European Spreadsheet Risks Interest Group 20th Annual Conference, London UK

ABSTRACT: Seasoned Excel developers were invited to participate in a challenge to implement a spreadsheet with multi-dimensional variables. We analyzed their spreadsheet to see the different implement strategies employed. We identified two strategies: most participants used a projection of three or four-dimensional variables on the two-dimensional plane used by Excel. A few participants used a database approach where the multi-dimensional variables are presented in the form of a dataset table with the appropriate primary key. This approach leads to simpler formulas.

Implementing WHERE and ORDER BY as spreadsheet formulas

2018-10-01 Workshop on Software Engineering Methods for Spreadsheets, Lisbon PT

ABSTRACT: The WHERE and ORDER BY clauses of the SQL SELECT statement select a subset of rows in the result of a database query and present the result in the specified order. In a spreadsheet program like Microsoft Excel, one could use the filter and sort buttons, or use its Query or its Pivot Table tools to achieve a similar effect. The disadvantage of using those tools is that they don't react automatically to changes in the calculated values of the spreadsheet. In this paper, we develop spreadsheet formulas that implement SQL's WHERE and ORDER BY clauses.

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

2018-07-05 European Spreadsheet Risks Interest Group 19th Annual Conference, London UK

ABSTRACT: In Part 1, we showed how to develop a conceptual model of a problem involving variables of multiple dimensions, like Products, Regions, Sectors and Months. The conceptual model is presented as a Formula Diagram, giving a global view of the interaction between all the variables, and a Formula List, giving a precise view of the interaction between the variables. In this paper, we present precise steps to implement a multi-dimensional problem in a way that will produce a spreadsheet that is easy to maintain

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

2017-07-06 European Spreadsheet Risks Interest Group 18th Annual Conference, London UK

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 of creating multiple worksheets with the same structure.

Implementing GROUP BY Calculations as Spreadsheet Formulas

2017-06-13 Workshop on Software Engineering Methods for Spreadsheets, Eindhoven NL

ABSTRACT: A GROUP BY is an operator of the SQL language that allows aggregate calculations to be performed on a set of rows in a database. In a spreadsheet program like Microsoft Excel, one could program aggregate calculations in VBA, its program- ming language, or use its Query or its Pivot Table tools. Excel functions like SUMIF and AVERAGEIF perform their calculations on one level of aggregation, and func- tions like SUMIFS and AVERAGEIFS can work on more levels but become un- wieldly quickly. In this paper, we present similarities and differences between SQL’s GROUP BY and spreadsheet aggregating formulas. We also present preparation for- mulas that allow the developer to always use the simple SUMIF and AVERAGEIF, no matter how many levels of aggregation are needed. Finally, we also provide model management formulas to help the spreadsheet developer ensure that his spreadsheet model covers all the possible groups.

Characteristics of Spreadsheets Developed with the SSMI Methodology

2016-07-07 European Spreadsheet Risks Interest Group 17th Annual Conference, London UK

ABSTRACT: The SSMI methodology was developed using concepts from Computer Science, Software Engineering and Information Systems and has been taught to undergraduate and MBA students and in Executive training seminars. In this paper, we describe the major characteristics of the spreadsheets developed using the methodology and show how they contribute to reduce many error causing factors.

Implementing Nested FOR Loops as Spreadsheet Formulas

2016-07-04 Workshop on Software Engineering Methods for Spreadsheets, Vienna AT

ABSTRACT: A FOR loop is a computing structure that allows a set of calculations to be made repeatedly for each iteration of the loop where the number of iterations is known in advance. A nested loop happens when a loop is inside another loop. In a spreadsheet program like Microsoft Excel, one can program loops in VBA, its programming language. Spreadsheet developers who do not know how to program in VBA usually implement the equivalent of loops with static values (e.g. region codes and product types are typed as constants) or with formulas (e.g. the region code is the previous region code + 1). In this paper, we present similarities and differences between programming loops and spreadsheet formulas loops. We also present a set of formulas that implement nested loops for 1, 2 or 3 nested levels, along with a generalization for deeper nesting levels. We also provide model management formulas to help the spreadsheet developer ensure that his spreadsheet model covers all the iterations.

Teaching Structured Spreadsheets Modelling and Implementation

2016-03-08 INTED2016, Valencia SP

ABSTRACT: Consequences of spreadsheet errors are abundant in the trade press, and academic research demonstrated that about 94% percent of spreadsheets contain at least one error. This is understandable since spreadsheets are being developed by users who have little expertise or ability to do so. Developing a spreadsheet is rarely one’s full occupation: it’s an occasional activity amidst all the other job-related tasks.
Teaching spreadsheets has mostly been centered on teaching the use of a particular program, with its menus, buttons, functions and options. This approach has the undeclared objective of trying to make a spreadsheet expert out of the student. Research has shown that most spreadsheets use only but a few functions or features.
We present an overview of the Structured Spreadsheet Modelling and Implementation methodology, explain how it was developed to reduce the possibility of making errors and illustrate how it can be included in a course curriculum. We also discuss different teaching strategies that have already been used, such as traditional lectures and flipped classrooms.

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

2015-07-09 European Spreadsheet Risks Interest Group 16th Annual Conference, London UK

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.

Governance and Structured Spreadsheets - Your Spreadsheets Don't Need To Be Black Boxes

2015-06-08 5th Annual Conference on Accounting and Finance, Singapore SG

ABSTRACT: The verification of spreadsheets continues to be tedious and challenging within corporate governance processes. This is primarily due to the fact that spreadsheets are often hard, if not impossible, to understand. We present a development methodology that produces spreadsheets which are easier to understand and also highly maintainable, regardless of who designed and implemented the model. By following the methodology, the developer also produces the model's documentation, which can be handed off to another developer and can be used in an audit

Structured Spreadsheet Modeling and Implementation

2015-05-18 Workshop on Software Engineering Methods for Spreadsheets, Florence IT

ABSTRACT: Developing an error-free spreadsheet has been a problem since the beginning of end-user computing. In this paper, we present a methodology that separates the modeling from the implementation. Using proven techniques from Information Systems and Software Engineering, we present strict, but simple, rules governing the implementation from the model. The resulting spreadsheet should be easier to understand, audit and maintain.