Developing a Repeating Model Using the Structured Spreadsheet - PowerPoint PPT Presentation

Developing a Repeating Model Using the Structured Spreadsheet Modelling and Implementation Methodology EuSpRIG 16th Annual Conference, 2015—London, UK Paul Mireault Founder, SSMI International Honorary Professor, HEC Montréal Paul.Mireault@SSMI.International

Presentation Plan ‣ Genesis ‣ SSMI ‣ Repeating Sub-model ‣ Structured Implementation ‣ Characteristics of the SSMI Methodology ‣ Q&A � 2

Genesis ‣ Teaching Decision Support Systems courses (1980’s and 1990’s) ‣ Undergraduates ‣ MBAs ‣ Reference book: Modern Decision Making, Samuel Bodily, 1985 ‣ Implement models using IFPS ‣ Gradually adapted to Lotus 1-2-3 and Excel � 3

Genesis ‣ Teaching the core IS course (HEC Montréal) ‣ MBA students ‣ Undergraduate students ‣ Executives Instead of teaching Excel , I teach how to use Excel. � 4

Typical Process Spreadsheet Analysis, Design and Implementation Logical and mechanical errors User Computer Excel Disk manipulations Physical Model Time � 5

Typical Process Spreadsheet Analysis, Design and Implementation � 6 Source: Hermans, Felienne (2014): Enron Spreadsheets and Emails. figshare. http://dx.doi.org/10.6084/m9.figshare.1221767

Model or Model? � 7

Model or Model? Used for simulation and forecasting; It can be an 
 accounting system ; Or presented as a dashboard . � 8

Model or Model? A spreadsheet is a model of the real world The Formula Diagram and the Formula List are the Conceptual Model of the spreadsheet � 9

Model or Model? In Information Systems, we use a Conceptual Model . Describes what the user needs, without references to the ‣ technology used to implement it. Formula Diagram Formula List � 10

Structured Spreadsheet Modelling and Implementation Logical errors Mechanical Same or errors Computer Developer another Spreadsheet developer Domain knowledge knowledge Spread- sheet file Formula Disk Disk Diagram Excel and manipulations Formula List Physical Model Conceptual and Logical Models Time � 11

Structured Modelling Two categories of variables, with sub-categories: Behind-the-Scene Interface Constants Input Calculated Calculated Output Variable � 12

Example ‣ Marco sells widgets in three regions: ‣ {East, South and North} ‣ Past demand = {48%, 23%, 29%} ‣ Delivery Cost = {50$, 80$, 60$} ‣ Unit manufacturing cost = 120$ ‣ Demand = 367000 × 1.009 − Price � 13

South Region � 14

Other Regions

Other Regions What if… ‣ 10 provinces? ‣ 50 states? ‣ 100 departments? How big?

Repeating Sub-Model � 17

Structured Implementation ‣ Three-tier architecture: single-purpose worksheets ‣ Parameters ‣ Model ‣ Interface ‣ Emulate SE Modules with precise block structure ‣ Extensive use of names � 18

Worksheet for Single-Value Parameters Every cell is named � 19

Worksheet for Multiple-Value Parameters Every row is named � 20

Worksheet for Single-Value Calculated Variables The definition block: Definition formula referencing the cells 
 directly above Simple 
 reference formulas to named variables Every cell containing a definition formula is named � 21

Worksheet for Multiple-Value Calculated Variables The definition block: Definition formula referencing the cells 
 directly above Simple 
 reference formulas to named variables Every row containing a definition formula is named � 22

Worksheet for Multiple-Value Calculated Variables � 23

Worksheet for Multiple-Value Calculated Variables � 24

Calculating a Single-Value Variable from a Multiple-Value Variable � 25

Calculating a Single-Value Variable from a Multiple-Value Variable � 26

Interface Flexibility Original Version SSMI Version � 27

Interface Flexibility SSMI Version Behind-the-Scenes � 28

Characteristics of the SSMI Methodology ‣ Spreadsheet documentation: Formula Diagram and Formula List ‣ Overview of relationships ‣ Facilitates peer review ‣ Facilitates hand-off of the model � 29

Characteristics of the SSMI Methodology ‣ Rule 1: only one mathematical operator or function per formula ‣ References in the definition block are made by name ‣ Easier to understand their meaning ‣ The definition formula uses the cells directly above, making it easier to verify. ‣ There is never any need to use absolute or mixed cell references � 30

Characteristics of the SSMI Methodology ‣ No daisy-chains ‣ Instead of copying many formulas one by one, we copy all the formulas once ‣ Verifying an implementation can be done by re-copying column B and seeing where changes happen � 31

In Development ‣ Repeating sub-model with time periods. ‣ Modelling techniques for special cases. � 32

Q&A � 33 � 33

Thank you! � 34 � 34