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

▶
developing a repeating model using the structured
SMART_READER_LITE
LIVE PREVIEW

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

Dec 05, 2022 312 likes •668 views

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

slide-1
SLIDE 1

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

slide-2
SLIDE 2

Presentation Plan

2

  • Genesis
  • SSMI
  • Repeating Sub-model
  • Structured Implementation
  • Characteristics of the SSMI Methodology
  • Q&A
slide-3
SLIDE 3

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

slide-4
SLIDE 4

Genesis

  • Teaching the core IS course (HEC Montréal)
  • MBA students
  • Undergraduate students
  • Executives

4

Instead of teaching Excel, I teach how to use Excel.

slide-5
SLIDE 5

5

Physical Model Time Disk Excel manipulations User Computer Logical and mechanical errors

Typical Process

Spreadsheet Analysis, Design and Implementation

slide-6
SLIDE 6

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

slide-7
SLIDE 7

Model or Model?

7

slide-8
SLIDE 8

Model or Model?

8

It can be an 
 accounting system; Used for simulation and forecasting; Or presented as a dashboard.

slide-9
SLIDE 9

Model or Model?

9

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

slide-10
SLIDE 10

Model or Model?

10

Formula Diagram Formula List

In Information Systems, we use a Conceptual Model.

  • Describes what the user needs, without references to the

technology used to implement it.

slide-11
SLIDE 11

Structured Spreadsheet Modelling and Implementation

Disk

11

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

slide-12
SLIDE 12

Structured Modelling

Behind-the-Scene Interface Constants Calculated

Input Calculated Variable Output

Two categories of variables, with sub-categories:

12

slide-13
SLIDE 13

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

slide-14
SLIDE 14

South Region

14

slide-15
SLIDE 15

Other Regions

slide-16
SLIDE 16

What if…

  • 10 provinces?
  • 50 states?
  • 100 departments?

How big?

Other Regions

slide-17
SLIDE 17

Repeating Sub-Model

17

slide-18
SLIDE 18

Structured Implementation

  • Three-tier architecture: single-purpose worksheets
  • Parameters
  • Model
  • Interface
  • Emulate SE Modules with precise block structure
  • Extensive use of names

18

slide-19
SLIDE 19

Worksheet for

Single-Value Parameters

19

Every cell is named

slide-20
SLIDE 20

Worksheet for

Multiple-Value Parameters

20

Every row is named

slide-21
SLIDE 21

Worksheet for

Single-Value Calculated Variables

The definition block:

21

Simple 
 reference formulas to named variables Definition formula referencing the cells 
 directly above Every cell containing a definition formula is named

slide-22
SLIDE 22

22

Worksheet for

Multiple-Value Calculated Variables

The definition block:

Simple 
 reference formulas to named variables Definition formula referencing the cells 
 directly above Every row containing a definition formula is named

slide-23
SLIDE 23

23

Worksheet for

Multiple-Value Calculated Variables

slide-24
SLIDE 24

24

Worksheet for

Multiple-Value Calculated Variables

slide-25
SLIDE 25

Calculating a

Single-Value Variable from a Multiple-Value Variable

25

slide-26
SLIDE 26

26

Calculating a

Single-Value Variable from a Multiple-Value Variable

slide-27
SLIDE 27

Interface Flexibility

Original Version SSMI Version

27

slide-28
SLIDE 28

Interface Flexibility

SSMI Version

28

Behind-the-Scenes

slide-29
SLIDE 29

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

slide-30
SLIDE 30

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

slide-31
SLIDE 31

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

slide-32
SLIDE 32

In Development

  • Repeating sub-model with time periods.
  • Modelling techniques for special cases.

32

slide-33
SLIDE 33

Q&A

33 33

slide-34
SLIDE 34

Thank you!

34 34