SpreadsheetBased Decision Support Systems Chapter 22 The DSS

Spreadsheet-Based Decision Support Systems Chapter 22: The DSS Development Process Aslı Sencer MIS 463

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. Application Overview: Welcome sheet, input, model and calculations, output, resolve 2. Worksheets: Welcome sheet, input sheet, calculations sheet, output sheet 3. User Interface: Navigational buttons, receiving input, functional buttons 4. Procedures: Receiving input, model and calculations, output analysis and display, navigational 5. Resolve Options: 6. Testing and Final Packaging 2

STEP 1: Application Overview v v 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 output adequate? – Re-solve Options: Resolve options; modify input; redefine constraints or objectives. 3

STEP 2: Worksheets Design v 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 4

STEP 2: Worksheets Design 1. Welcome Sheet: – Title and description of the DSS – Assumptions, source of model formulation, images, photos – “Start” button 5

STEP 2: Worksheets Design User input to the worksheet 2. Input Sheet: v v v 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 6

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

STEP 2: Worksheets Design Dave's Candies Simulation Selling price $ 12, 00 Cost $ 7, 50 Discount price $ 6, 00 Simulation Model Trial Demand Profit 1 50 $ 195, 00 2 50 $ 195, 00 3 90 $ 315, 00 Simulation Results Bin Range Frequency $ 105, 00 - $ 120, 00 - $ 135, 00 16 4 5 6 7 8 9 10 11 40 80 70 60 40 80 80 40 $ 135, 00 $ 315, 00 $ 255, 00 $ 135, 00 $ 315, 00 $ 135, 00 $ 150, 00 - $ 165, 00 - $ 180, 00 - $ 195, 00 18 $ 210, 00 - $ 225, 00 - $ 240, 00 - $ 255, 00 18 12 50 $ 195, 00 $ 270, 00 - 13 60 $ 255, 00 $ 285, 00 - 14 50 $ 195, 00 $ 300, 00 - 15 90 $ 315, 00 $ 315, 00 48 Random Number Range Demand 16 0 1/6 40 17 1/6 1/3 50 18 1/3 1/2 60 19 1/2 2/3 70 20 2/3 5/6 80 21 70 60 70 40 $ 315, 00 $ 255, 00 $ 315, 00 $ 135, 00 $ 345, 00 - $ 360, 00 - $ 405, 00 - Average Profit $253, 80 Standard Dev. $ 68, 74 Demand 40 50 60 70 80 90 Order Quantity Probability 1/6 1/6 1/6 70 Data Table Analysis Order Quantity $253, 80 40 $180, 00 50 $213, 60 $240, 60 70 $253, 80 $267, 00 90 $269, 40

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

STEP 2: Worksheets Design Outputs for Dave’s Candies Simulation Model Order Quantity 40 50 60 70 80 90 Profit Distribution for Order Quantity=70 Expected Profit $180, 00 $213, 00 $236, 40 $254, 40 $271, 80 $259, 20 40 Expected profit 35 $ 300. 00 25 $ 250. 00 Expected profit 20 15 10 5 $ 150. 00 $ 100. 00 00 0. $ - 36 00 $ 200. 00 $ 50. 00 $ 5. 31 $ 00 00 5. 28 $ 5. $ 25 00 00 5. 22 $ 5. 19 $ 00 00 5. 16 $ 5. 13 $ 10 5. 00 - $ Frequency 30 Profit 10 40 50 60 70 Order Quantity 80 90 100

STEP 2: Worksheets Design v 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 11

Step 3: User Interface v v The aim is to develop a user friendly DSS Types of user interface in spreadsheet based DSS applications: – – v 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 12

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. – Be consistent in the phrases used for exit and end. 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 13

Receiving Input: Form Controls on the Worksheet v v 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 14

Receiving Input: User Forms v 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. v 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 15

Receiving Input: User Forms v You can also design dynamic user forms. 16

Receiving Input: User Forms v 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. 17

Functional Buttons v 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. ” 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 18

Step 4: Procedures v 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. v Make an outline of what procedures you will need to conduct the flow and execute the calculations. – – v 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. 19

Step 4: Procedures Initial Procedures and Receiving Input v Main: Associated with the start button on the welcome sheet – 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 20

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

Step 4: Procedures - Variable Definitions v Variables: – All variables are declared – Variables used in more than one procedure are declared as “public” variables at the top of the module 22

Step 5: Resolve Options v A DSS should provide resolve options for the users! – Change inputs and resolve – Add new constraints, redefine objective function 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? 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 v Remember that a DSS is designed to aid a decision maker in making a decision. 23

Resolve Options Examples v Returning to Simulation or Re-solve options DSS suggests a resolve option for the simulation. Suggestion is made by solving an optimization model 24

Resolve Options Examples (cont’d) v Changing Input or Portfolio options 25

Testing and Final Packaging v 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. v 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. 26

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