The DSS Development Process

1
Spreadsheet-Based Decision Support
Systems Chapter 22

• The DSS Development Process

Asli Sencer Erdem
MIS 463
2
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 Welcome sheet, input, model
  and calculations, output, resolve
• Worksheets Welcome sheet, input sheet,
  calculations sheet, output sheet
• User Interface Navigational buttons, receiving
  input, functional buttons
• Procedures Receiving input, model and
  calculations, output analysis and display,
  navigational
• Resolve Options
• Testing and Final Packaging

3
STEP 1 Application Overview

• Most important step in DSS development!
• 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
• Type of input sheet, forms, or input box.
• Model and Calculations
• Formulation of objectives and necessary input
  Type of model Performing an optimization, or
  running a simulation. Formulations and functions
  Calculate by Excel or dynamically in code
• Output A driving force in why the users are
  using the DSS.
• Type of output Charts, graphs, histograms,
  tables Ask Is it necessary to redisplay some
  inputs? Is the the output adequate?
• Re-solve Options Resolve options modify input
  redefine constraints or objectives.

4
STEP 2 Worksheets Design

• How many sheets will be used, with which
  features?Use mostly Excel features use VBA for
  more complicated, dynamic tasks!Worksheet design
  should be completed before code-based interface!
• Welcome Sheet
• Input Sheet
• Model and Calculation Sheets
• Output Sheet

5
STEP 2 Worksheets Design

• Welcome Sheet
• Title and description of the DSS
• Assumptions, source of model formulation, images,
  photos
• Start button

6
STEP 2 Worksheets Design
User input to the worksheet

• 2. Input Sheet
• User input/Large data input/may be combined with
  other sheets
• If not use a sheet for input, may use a form or
  input box
• Appropriate labels, input locations

Input calculations and output are on the same
worksheet
Large data imported from a text file or database
7
STEP 2 Worksheets Design

• 3. Calculations Sheet
• Spreadsheet calculations by a solver
• Simulation results
• Usually hidden from user user is not familiar
  with the details, only interested in results.
  Might be optional to view.

8
STEP 2 Worksheets Design
9
STEP 2 Worksheets Design

• 4. Output Sheet
• Most important sheet for users.
• Should clearly summarize results and
  calculations, so the user can understand the
  behavior of the system.
• Summary tables and reports
• Graphs, charts or histograms
• Navigational output buttons
• End, Resolve, and View buttons.

10
STEP 2 Worksheets Design
Outputs for Daves Candies Simulation Model
11
STEP 2 Worksheets Design

• An output sheet example using pivot tables and
  pivot charts

Naviagational output sheet
Pivot tables are used to transform large amount
of data from a table or database into an
organized summary report
12
Step 3 User Interface

• The aim is to develop a user friendly DSS
• Types of user interface in spreadsheet based DSS
  applications
• Navigational Buttons
• Form Controls on the Worksheet
• User Forms
• Functional Buttons
• During development of the DSS use these options
  to perform
• Navigate the users through the application
• Receive input
• Allow users to input parameters of the model
• Allow users to begin a calculation
• Repeat a calculation or resolve a model

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.
• Be consistent in the phrases used for exit and
  end.
• 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
Receiving InputForm Controls on the Worksheet

• Form controls can make a worksheet into a dynamic
  user interface
• Useful when there are many resolve options. In
  this case easy access to input data is needed.

A DSS for Inventory Management
Extra inputs are asked if backorders or reorder
point model is selected. This botten is not
highlighted if standard eoq model is selected
15
Receiving Input User Forms

• Forms should be used when
• the back and forth movement between the input and
  output worksheets is not very frequent.
• there is large set of input required from the
  user.
• There are many control options on forms
• control buttons are more clearly displayed do
  not interfere with other cells on the worksheet,
• can be displayed to the users at any time, since
  they are not attached to a worksheet. (useful for
  resolve options)
• Possible to design dynamic user forms

Functional buttons on a form
Functional buttons on a worksheet
16
Receiving Input User Forms

• You can also design dynamic user forms.

(
17
Receiving Input User Forms

• You can also place a floating form in a
  worksheet. Should be used when the user needs to
  modify input multiple times the users can modify
  entries without any need to close the form. The
  form is hidden when the users move to another
  sheet.

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.
• Quite advantageous, mostly used when large
  amounts of input are needed.

• Initially Create table is higlighted.
• When the users press it, they are prompted to
  give the dimensions.
• The table with corresponding number of rows is
  created
• Then solve button becomes visible

19
Step 4 Procedures

• It is recommended to organize the code into
  several smaller procedures that may be called
  from other main procedures or associated with
  buttons on the worksheet.
• Make an outline of what procedures you will need
  to conduct the flow and execute the calculations.
• Receiving Input
• Model and Calculations
• Output Analysis and Display
• Navigational
• Make this outline before you begin the details of
  the implementation.
• Draw up an outline of these procedures via
  comments in the code like procedure names,
  documentation, tasks performed, etc.

20
Step 4 Procedures Initial Procedures and
Receiving Input

• Main Associated with the start button on the
  welcome sheet
• 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

21
Step 4 Procedures

• Perform Model Calculations
• Perform calculations using function procedures
• Run simulations using loops
• Perform optimization using Solver commands
• Perform Output Analysis and Generate Display
• Display solution values to an output report
  table
• Update source data of the chart or histogram
• Navigational Procedures
• End, Next, Back, or View button
  functionality
• Change Visible property of worksheets

22
Step 4 Procedures - Variable Definitions

• Variables
• All variables are declared
• Variables used in more than one procedure are
  declared as public variables at the top of the
  module

23
Step 5 Resolve Options

• A DSS should provide resolve options for the
  users!
• Change inputs and resolve
• Add new constraints, redefine objective function
• 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?
• Keep the last saved inputs, do not clear inputs
  unless the application is restarted
• Can the user change other parts of the
  calculations or model when resolving?
• Develop a dynamic application, so that different
  problem sizes are applicable.
• If not, indicate in the welcome sheet.
• What the user is really interested in learning
  from the DSS?
• Store multiple solutions for senario comparison
  and sensitivity analysis
• Remember that a DSS is designed to aid a decision
  maker in making a decision.

24
Resolve Options Examples

• Returning to Simulation or Re-solve options

DSS suggests a resolve option for the
simulation. Suggestion is made by solving an
optimization model
25
Resolve Options Examples (contd)

• Changing Input or Portfolio options

26
Testing and Final Packaging

• Testing is an important final step which ensures
  that the DSS performs as intended.
• Test Navigation Simply check all navigational
  options in each step of the application. Consider
  having an unbiased user test your program as
  well.
• Test Calculation Correctness 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.
• 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.

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