SpreadsheetBased Decision Support Systems Chapter 22 The DSS
Spreadsheet-Based Decision Support Systems Chapter 22: The DSS Development Process Prof. Name Position University Name name@email. com (123) 456 -7890
Overview v v v v 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 2
Defining the DSS Development Process v 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. v We propose six basic steps for developing a DSS: 1. 2. 3. 4. 5. 6. Application Overview Worksheets User Interface Procedures Resolve Options Testing and Final Packaging 3
Application Overview and Model Development v Input v Model and Calculations v Output v Re-solve Options 4
Application Overview v 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. 5
Worksheets v Welcome Sheet v Input Sheet v Model and Calculation Sheets v Output Sheet 6
Worksheets 1. Welcome Sheet: – Title and description of the DSS – “Start” button 7
Worksheets (cont’d) 2. Input Sheet: – User input – Large data input – May be combined with other sheets 8
Worksheets (cont’d) 3. Calculations Sheet: – Spreadsheet calculations – Simulation results – Usually hidden from user 9
Worksheets (cont’d) 4. Output Sheet: – – Summary tables and reports Graphs, charts or histograms Navigational output buttons “End”, “Resolve”, and “View” buttons. 10
Figure 22. 7 11
Worksheets (cont’d) v An output sheet example using pivot tables and pivot charts. 12
User Interface v Navigational Buttons v Receiving Input – Form Controls on the Worksheet – User Forms v Functional Buttons 13
Navigational Buttons v 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 v 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. v 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 v Form controls can make a worksheet into a dynamic user interface. 15
User Forms v User forms may replace a worksheet interface in a more concise presentation. v We have used two functional buttons to allow users to “Add” and “Remove” stocks to and from their portfolios in the “Portfolio Management and Optimization” case study. 16
Figure 22. 12 (b) v We may choose to create a user form to avoid creating or editing the portfolios multiple times. 17
User Forms (cont’d) v You can also design dynamic user forms. 18
User Forms (cont’d) v You can also place a “floating” form in a worksheet. 19
Functional Buttons v 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. ” 20
Procedures v Receiving Input v Model and Calculations v Output Analysis and Display v Navigational 21
Procedure Outline v Make an outline of what procedures you will need to conduct the flow and execute the calculations. v Make this outline before you begin the details of the implementation. v Should organize code into several smaller procedures which may be called from other main procedures or associated with buttons on the spreadsheet. 22
Initial Procedures and Receiving Input v Main: – Call Clear. Previous procedure – Show input form or take user to input sheet v Clear Previous: – Clear previous ranges of input or solution values – Initialize variables v Receive Input: – Store values from form controls or input cells to corresponding variables – Record these values to appropriate cells in calculation sheet 23
Calculation and Output Procedures v Perform Calculations: – Perform calculations using function procedures – Run simulations using loops – Perform optimization using Risk Solver Platform commands v Generate Output: – Display solution values to report table – Update chart source data – Create histograms 24
Navigational Procedures and Variable Definitions v Navigational: – “End”, “Next”, “Back”, or “View” button functionality – Change Visible property of worksheets v Variables: – All variables are declared – Variables used in more than one procedure are declared as Public variables at the top of the module 25
Resolve Options v 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? v Remember that a DSS is designed to aid a decision maker in making a decision. 26
Figure 22. 16 v The output sheet for the “Reliability Analysis” case study. 27
Figure 22. 17 v This case presents two re-solve options to the users. The first option is to return to the simulation sheet and modify the initial input values in a given table. 28
Figure 22. 18 v The second re-solve option: Making a suggestion to aid the decisionmaker. 29
Resolve Options Examples (cont’d) v After the users fill the input in the user form for the optimization, the resulting optimized investment strategy displays on an output sheet. 30
Testing and Final Packaging v 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. v v v 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. 31
Summary v v v 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. 32
Additional Links v (place links here) 33
- Slides: 33