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
Recommend
More recommend
