Structured Spreadsheet Modelling and Implementation: A Methodology for Creating Effective Spreadsheets, Second Edition
Purchase the book:
Conference Papers and Presentations
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.