Chapter 22: The DSS Development Process

1
Spreadsheet-Based Decision Support Systems
Chapter 22 The DSS Development Process
Prof. Name
name_at_email.com Position
[phone] University Name
2
Overview

• 22.1 Defining the DSS Development Process
• 22.2 Application Overview and Model Development
• 22.3 Worksheets
• 22.4 User Interface
• 22.5 Procedures
• 22.6 Re-solve Options
• 22.7 Testing and Final Packaging
• 22.8 Summary

3
Defining the DSS Development Process

• Now that we have discussed in great detail the
  components of a spreadsheet-based decision
  support system (DSS), we need to learn the
  process of putting these components together to
  build a complete DSS application.
• We propose six basic steps for developing a DSS
• Application Overview
• Worksheets
• User Interface
• Procedures
• Resolve Options
• Testing and Final Packaging

4
Application Overview and Model Development

• Input
• Model and Calculations
• Output
• Re-solve Options

5
Application Overview

• Create a layout of the entire application to
  understand the flow from the user input to the
  model calculations to the output
• Welcome Sheet Flow begins introduction to what
  DSS is and how to begin using it.
• Input Provided by the user via a set of forms,
  an input worksheet, or Input Boxes.
• Model Calculations Formulation of objectives and
  necessary input decide if computing simple
  calculations, performing an optimization, or
  running a simulation.
• Output A driving force in why the users are
  using the DSS.
• Re-solve Options Resolve options modify input
  redefine constraints or objectives.

6
Worksheets

• Welcome Sheet
• Input Sheet
• Model and Calculation Sheets
• Output Sheet

7
Worksheets

• Welcome Sheet
• Title and description of the DSS
• Start button

8
Worksheets (cont)

• 2. Input Sheet
• User input
• Large data input
• May be combined with other sheets

9
Worksheets (cont)

• 3. Calculations Sheet
• Spreadsheet calculations
• Simulation results
• Usually hidden from user

10
Worksheets (cont)

• 4. Output Sheet
• Summary tables and reports
• Graphs, charts or histograms
• Navigational output buttons
• End, Resolve, and View buttons.

11
Worksheets (cont)

• An output sheet example using pivot tables and
  pivot charts

12
User Interface

• Navigational Buttons
• Receiving Input
• Form Controls on the Worksheet
• User Forms
• Functional Buttons

13
Navigational Buttons

• The first navigational button we should create is
  the Start button, which is located on the
  Welcome sheet.
• Assign this button to a macro that brings the
  users to the input interface
• On all other sheets (input, calculation, and
  output sheets) there should at least be an End
  or Exit button.
• The users should always have the option to quit
  the application and return to the Welcome sheet.
• We may also include navigational buttons such as
  Next, Continue, or Back if we intend for
  the users to be able to step through the sheets
  or to revisit sheets.

Start
14
Form Controls on the Worksheet

• Form controls can make a worksheet into a dynamic
  user interface

15
User Forms

• User forms may replace a worksheet interface in a
  more concise presentation.

16
User Forms (contd)

• You can also design dynamic user forms.

(
17
User Forms (contd)

• You can also place a floating form in a
  worksheet.

18
Functional Buttons

• In the case in which input, calculations, and/or
  output are combined, we may also include some
  functional buttons on the worksheet, such as
  Solve.

19
Procedures

• Receiving Input
• Model and Calculations
• Output Analysis and Display
• Navigational

20
Procedure Outline

• Make an outline of what procedures you will need
  to conduct the flow and execute the calculations.
• Make this outline before you begin the details of
  the implementation.
• Should organize code into several smaller
  procedures which may be called from other main
  procedures or associated with buttons on the
  spreadsheet.

21
Initial Procedures and Receiving Input

• Main
• Call ClearPrevious procedure
• Show input form or take user to input sheet
• Clear Previous
• Clear previous ranges of input or solution values
• Initialize variables
• Receive Input
• Store values from form controls or input cells to
  corresponding variables
• Record these values to appropriate cells in
  calculation sheet

22
Calculation and Output Procedures

• Perform Calculations
• Perform calculations using function procedures
• Run simulations using loops
• Perform optimization using Solver commands
• Generate Output
• Display solution values to report table
• Update chart source data
• Create histograms

23
Navigational Procedures and Variable Definitions

• Navigational
• End, Next, Back, or View button
  functionality
• Change Visible property of worksheets
• Variables
• All variables are declared
• Variables used in more than one procedure are
  declared as Public variables at the top of the
  module

24
Resolve Options

• In developing the re-solve options, we may ask
  the following questions
• Can the user easily modify the input to resolve
  the problem without having to re-enter all input
  from scratch?
• Can the user change other parts of the
  calculations or model when resolving?
• What the user is really interested in learning
  from the DSS?
• Remember that a DSS is designed to aid a decision
  maker in making a decision.

25
Resolve Options Examples

• Returning to Simulation or Re-solve options

26
Resolve Options Examples (contd)

• Changing Input or Portfolio options

27
Testing and Final Packaging

• Testing is an important final step which ensures
  that the DSS performs as intended. We should test
  for smooth navigation as well as for calculation
  correctness.
• To test navigation, we can simply check all
  navigational options in each step of the
  application.
• To test calculation correctness, we use a simple
  set of input values and check the calculation
  results.
• You may also use debugging techniques (see
  Appendix B) and error checking methods.
• Consider having an unbiased user test your
  program as well.
• The final packaging should ensure that our DSS
  has a professional appearance.
• All user interface should have clear instructions
  and be nicely and consistently formatted.

28
Summary

• There are six basic steps to develop a DSS
  application Application Overview, Worksheets,
  User Interface, Procedures, and DSS Components.
• The outline of the worksheets in the DSS should
  include Welcome Sheet, Input sheet, Calculations
  sheet, and Output sheet.
• The worksheet user interface includes
  Navigational Buttons, Functional Buttons,
  Controls on the Worksheet, and User Forms.
• The outline of the procedures in the application
  should include a Main procedure, a Clear Previous
  procedure, a procedure to receive input, some
  procedures and functions to perform calculations,
  and a procedure to generate output.
• In designing the re-solve options, the developers
  should check that the users are able to modify
  inputs, calculation options, constraints, and
  objectives. Aid the decision-makers in making the
  best decision.
• The testing and final packaging step ensures that
  the DSS application works correctly and has a
  professional appearance for the end users.

29
Additional Links

• (place links here)