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

📝 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.

💡 Analysis

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.

📄 Content

Proceedings of the EuSpRIG 2015 Conference “Spreadsheet Risk Management” ISBN: 978-1-905404-52-0 Copyright © 2015, European Spreadsheet Risks Interest Group (www.eusprig.org ) & the Author(s) Page 1/13 Developing a Repeating Model Using the Structured Spreadsheet Modelling and Implementation Methodology Paul Mireault Founder, SSMI International Honorary Professor, HEC Montréal Paul.Mireault@SSMI.International 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 = PriceQuantity Sold South and Revenues East =PriceQuantity 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. 1 INTRODUCTION Errors in spreadsheets have caused financial losses for many companies and organizations, as illustrated by the EuSpRIG Horror Stories [EuSpRIG, 2015] web page. Panko [Panko, 2008] cites a study reporting that 95% of spreadsheets have errors. Many authors have identified the following spreadsheet characteristics that can cause errors: • Far references. A formula that references a cell that is not immediately visible and understood is harder to understand [Raffensperger, 2003]. • Transitive references. Formulas that reference a reference of a variable are candidates to maintenance problems. When we introduce a nuance and create a new variable, formulas may refer to one or the other nuance of the variable. In [Mireault, 2015], we introduced the Structured Spreadsheet Modelling and Implementation methodology and illustrated it with a simple problem. The methodology is based on well-established concepts of Computer Science, Software Engineering and Information Systems. The basic idea of the methodology is to develop spreadsheets in two steps, the conceptual model first and the implementation second. One of the important conception rules is to keep formulas as simple as they can be, avoiding having more than one mathematical operator or function in the definition of a variable. For example, the formula Total Cost = Fixed Cost + Unit Cost

• Quantity uses two different mathematical operators, addition and multiplication, and should be replaced with Variable Cost = Unit Cost * Quantity and Total Cost = Fixed Cost + Variable Cost. Such simpler formulas would have a low complexity score according to [Hermans, et al., 2012]. [Mireault, et al., 2015] showed how a model developed with the SSMI methodology can be easily expanded to transform parameters that were entered by user into variables that are calculated from other inputs. Proceedings of the EuSpRIG 2015 Conference “Spreadsheet Risk Management” ISBN: 978-1-905404-52-0 Copyright © 2015, European Spreadsheet Risks Interest Group (www.eusprig.org ) & the Author(s) Page 2/13 In this paper, we present an extension to the SSMI methodology that is used to model cases where a set of formulas and variables is repeated for different instances of an entity. For example, the calculation of the total cost is similar for all our regions, South, East and North.

2 MODELLING A REPEATING SUB-MODEL We are sometimes faced with the situation where sets of variables have formulas that are similar. In such situations, we are also tempted to name the variables we create with the same prefix and differentiate them with a different suffix. For example, we might have variables named Profit Region A, Profit Region B and Profit Region C. If we use the straightforward modeling technique presented in [Mireault, 2015], we will end up with a model that is unwieldy and difficult to modify. But there is a way of keeping the model simple: it consists in identifying variables and formulas that are similar and grouping them in what we will call a repeating sub-model. We will first illustrate the development of a model without the use of a repeating sub-model to illustrate its complexity. The reader should keep in mind that this is not the proper modeling technique. We will then illustrate the proper use of the repeating sub-model and show how it simplifies the Formula Diagram and the Formula List. Let’s consider a small example with Marco’s Widgets. Marco sells his widgets in three different regions: South, East and North. He wants a spreadsheet that will show him the profit per region as well as the total profit. To allocate the demand per region, he tells you that the demand has traditionally been 48%, 23% and 29% respectively for the South, East and North regions. Marco uses a Unit Cost composed of a Manufacturing Cost and a Delivery Cost. The Manufacturing Cost does not depend on the region and is equal to 120 $. The Delivery Co

This content is AI-processed based on ArXiv data.