Spreadsheet-Based Decision Support Systems
Chapter 22: The DSS Development Process
Prof. Name email@example.com
Position (123) 456-7890
22.1 Defining the DSS Development Process
22.2 Application Overview and Model Development
22.4 User Interface
22.6 Re-solve Options
22.7 Testing and Final Packaging
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
We propose six basic steps for developing a DSS:
1. Application Overview
3. User Interface
5. Resolve Options
6. Testing and Final Packaging
Application Overview and Model Development
Model and Calculations
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
– Input: Provided by the user via a set of forms, an input worksheet, or Input
– Model Calculations: Formulation of objectives and necessary input; decide
if computing simple calculations, performing an optimization, or running a
– Output: A driving force in why the users are using the DSS.
– Re-solve Options: Resolve options; modify input; redefine constraints or
Model and Calculation Sheets
1. Welcome Sheet:
– Title and description of the DSS
– “Start” button
2. Input Sheet:
– User input
– Large data input
– May be combined with other
3. Calculations Sheet:
– Spreadsheet calculations
– Simulation results
– Usually hidden from user
4. Output Sheet:
– Summary tables and reports
– Graphs, charts or histograms
– Navigational output buttons
– “End”, “Resolve”, and “View” buttons.
An output sheet example using pivot tables and pivot charts
– Form Controls on the Worksheet
– User Forms
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.
Form Controls on the Worksheet
Form controls can make a worksheet into a dynamic user interface
User forms may replace a worksheet interface in a more concise
User Forms (cont’d)
You can also design dynamic user forms.
User Forms (cont’d)
You can also place a “floating” form in a worksheet.
In the case in which input, calculations, and/or output are combined, we
may also include some functional buttons on the worksheet, such as
Model and Calculations
Output Analysis and Display
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
Initial Procedures and Receiving Input
– Call ClearPrevious procedure
– Show input form or take user to input sheet
– Clear previous ranges of input or solution values
– Initialize variables
– Store values from form controls or input cells to corresponding variables
– Record these values to appropriate cells in calculation sheet
Calculation and Output Procedures
– Perform calculations using function procedures
– Run simulations using loops
– Perform optimization using Solver commands
– Display solution values to report table
– Update chart source data
– Create histograms
Navigational Procedures and Variable Definitions
– “End”, “Next”, “Back”, or “View” button functionality
– Change Visible property of worksheets
– All variables are declared
– Variables used in more than one procedure are declared as Public variables
at the top of the module
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
Resolve Options Examples
Returning to Simulation or Re-solve options
Resolve Options Examples (cont’d)
Changing Input or Portfolio options
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
– 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
Consider having an unbiased user test your program as well.
The final packaging should ensure that our DSS has a professional
– All user interface should have clear instructions and be nicely and
There are six basic steps to develop a DSS application: Application
Overview, Worksheets, User Interface, Procedures, and DSS
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.
(place links here)