<%BANNER%>

Principles of Designing and Developing Spreadsheet-Based Decision Support Systems


PAGE 4

IwouldliketothankRavindraK.Ahujaforbeingmyadvisorandfriendthroughoutthiswork.Iamverygratefulfortheopportunitieshehasgivenmetoresearch,write,andteachthismaterial.IamalsothankfultomyanceOnurSerefforhisconstantloveandencouragement. iv

PAGE 5

page ACKNOWLEDGMENTS ............................. iv LISTOFTABLES ................................. viii LISTOFFIGURES ................................ ix ABSTRACT .................................... xiii CHAPTERS 1INTRODUCTION .............................. 1 1.1AnIntroductiontoDSS ....................... 1 1.2DeningDSS ............................. 3 1.3ExcelSpreadsheets .......................... 6 1.4VBAforExcelProgrammingLanguage ............... 7 1.5TheDSSDevelopmentProcess .................... 8 1.6CaseStudies .............................. 9 2DSSDEVELOPMENTPROCESS ..................... 10 2.1DeningtheDevelopmentProcess .................. 10 2.2ApplicationOverview ......................... 11 2.3Spreadsheets .............................. 13 2.4UserInterface ............................. 18 2.5Procedures ............................... 28 2.6ResolveOptions ............................ 32 3GUIDESIGNANDPROGRAMMINGPRINCIPLES ........... 39 3.1GUIDesign .............................. 39 3.1.1TheTheoryBehindGoodGUIDesign ............ 39 3.1.1.1Users,tasks,andgoals ................. 39 3.1.1.2Clarity .......................... 41 3.1.1.3Consistency ....................... 45 3.1.2GoodandBadGUIDesigns ................. 48 3.1.2.1Buttons ......................... 48 v

PAGE 6

.... 48 3.1.2.3Tabstripsandmultipages ............... 49 3.1.2.4Checkboxesversusoptionbuttons .......... 49 3.1.2.5Frames ......................... 51 3.1.2.6Labelsversustextboxes ................ 51 3.1.2.7Dynamiccontrols .................... 52 3.1.2.8Multipleforms ..................... 52 3.1.2.9Eventprocedures .................... 54 3.2ProgrammingPractices ........................ 54 3.2.1ConsistentStyle ........................ 54 3.2.2Naming ............................. 57 3.2.3Comments ........................... 57 3.2.4Eciency ............................ 58 4WAREHOUSELAYOUT .......................... 61 4.1ApplicationOverview ......................... 61 4.1.1ModelDenitionandAssumptions .............. 61 4.1.2Input .............................. 68 4.1.3Output ............................. 68 4.2Spreadsheets .............................. 69 4.3UserInterface ............................. 72 4.4Procedures ............................... 74 4.5ResolveOptions ............................ 87 5RELIABILITYANALYSIS ......................... 98 5.1ApplicationOverview ......................... 98 5.1.1ModelDenitionandAssumptions .............. 98 5.1.2Input .............................. 99 5.1.3Output ............................. 100 5.2Spreadsheets .............................. 100 5.3UserInterface ............................. 105 5.4Procedures ............................... 109 5.5ResolveOptions ............................ 121 6CONCLUSION ................................ 128 6.1TheImportanceofDSS ........................ 128 6.2Spreadsheet-BasedDSS ........................ 128 6.3DevelopingaDSS ........................... 128 6.4ConclusionandFutureDirection ................... 129 vi

PAGE 7

................................... 130 BIOGRAPHICALSKETCH ............................ 131 vii

PAGE 8

Table page 2{1Summary:ApplicationOverview ..................... 12 2{2Summary:Spreadsheets .......................... 19 2{3Summary:UserInterface ......................... 29 2{4Summary:Procedures ........................... 32 2{5Summary:ResolveOptions ........................ 37 3{1Summary:Users,Tasks,andGoals .................... 41 3{2Summary:Clarity ............................. 45 3{3Summary:Consistency .......................... 48 3{4Summary:GUIDesign .......................... 55 3{5Summary:ProgrammingPrinciples ................... 60 4{1Algorithm ................................. 68 4{2Summary:Spreadsheets .......................... 71 4{3Summary:UserInterface ......................... 74 4{4Summary:Procedures ........................... 90 4{5Summary:ResolveOptions ........................ 95 5{1Summary:Spreadsheets .......................... 105 5{2Summary:UserInterface ......................... 109 5{3Summary:Procedures ........................... 120 5{4Summary:ResolveOptions ........................ 123 viii

PAGE 9

Figure page 1{1Aschematicviewofadecisionsupportsystem. ............. 4 2{1Anexampleofa\Welcome"sheet. ................... 13 2{2Anexampleofusingspreadsheetstotakeinputfromtheuser. .... 14 2{3Anexampleofalargesetofdataimportedfromatextle. ...... 15 2{4Anexampleofhavinginput,calculations,andoutputonthesamesheet. .................................. 15 2{5Anexampleofacomplicatedcalculationssheet. ............ 16 2{6Anexampleofusingagraphtoillustrateresultsinanoutputsheet. 18 2{7Anexampleofhistogramsintheoutputsheetofasimulation-basedDSS. ................................... 19 2{8Anexampleofanavigationaloutputsheet. ............... 20 2{9Anpivottablereportsheetisoneoftheoutputsheets. ........ 20 2{10Thecorrespondingpivotchartisanotherreportsheet. ......... 21 2{11Anexampleofbuttonsonthespreadsheettoworkwithinputandcalculations. ............................... 23 2{12Anexampleofdynamicformcontrolsonthespreadsheet. ....... 25 2{13Anexampleofcontrolsonaformandspreadsheet. .......... 26 2{14Anexampleofdynamicformcontrols. ................. 27 2{15Anexampleofa\oating"form. .................... 28 2{16TheoutputsheetfortheReliabilityAnalysiscasestudy. ....... 34 2{17Therstresolveoption:modifyinputintableandrerunsimulation. 35 2{18Thesecondresolveoption:suggestionismadetoaiddecisionmaker. 36 2{19Two\Modify"buttonsgivetheuserdierentresolveoptions. ..... 38 3{1Clearinstructionsanddescriptionsoneachsheetandform. ...... 42 ix

PAGE 10

42 3{3Labelsclearlydesignatefunctionalityofcontrols. ............ 43 3{4Controltipsclarifycontrolfunctionality. ................ 43 3{5Formattingguidelines. .......................... 44 3{6Clearformattinganddefaultvalues. ................... 45 3{7Thenavigationalbuttonsaretogetherandconsistentinthesheet. .. 46 3{8Consistentformattingandclearconstructions. ............. 47 3{9Comboboxesreduceusermemorizationandchanceforerrors. .... 49 3{10Tabstripsandmultipagescanbereplacediftoomanytabsareneeded. 50 3{11Optionbuttonsareusedformutuallyexclusiveoptionsandcheckboxesareusedforotheroptions. ................... 51 3{12Frameshavemorethanonecontroleach. ................ 52 3{13Labelsareusedfornon-changeablevalues. ............... 53 3{14Somefunctionsareactiveandsomeareinactive. ............ 53 4{1Anexamplewarehouselayout. ...................... 62 4{2Thewarehouseareaisdiscretizedintobayareasofvalue1. ...... 66 4{3Thenalwarehouselayoutforveproductsandtwodocks. ...... 67 4{4Thewelcomesheet. ............................ 69 4{5Therstinputsheetfordockinformation. ............... 70 4{6Thesecondinputsheetforproductinformation. ............ 71 4{7Theoutputsheetwithitsnavigationalbuttonsandresolveoptions. 72 4{8Theuserformasksfortherstinputvalues. .............. 73 4{9TheMainprocedureandpublicvariabledeclarations. ......... 75 4{10TheClearPreviousprocedureclearsvaluesandformattingonallsheets;italsoinitializessomevariables. .................... 76 4{11ThecmdOK Clickprocedureassignstheinputvaluestotheircorre-spondingvariables. ........................... 77 4{12ThebeginningoftheNumberingBayprocedure. ............ 79 x

PAGE 11

................ 80 4{14TheSelectionChangeeventprocedureenablestheusertoclickonthesheettoplacethedocks. ........................ 81 4{15TheDockInfoprocedurerecordsthedockinformation. ........ 82 4{16TheFinalStepsprocedureperformsthemaincalculationsandcalltheprocedureswhichexecutethealgorithm. ............. 83 4{17TheComputeFprocedurecomputestheweighteddistancesbasedonthedockfrequencies. .......................... 84 4{18TheSortItemsprocedurecalculatestheproducttypeweightsandsortsthem. .................................. 85 4{19TheSortBaysalgorithmsortsthebaysinascendingorderoftheirdistanceweights. ............................ 86 4{20ThebeginningoftheAssignprocedure. ................. 88 4{21TheendoftheAssignprocedure. .................... 89 4{22Thenavigationalprocedures. ....................... 89 4{23Therstinputsheetisrevisitedandsomeofthedockinformationischanged. ................................. 90 4{24Thesecondinputsheetisrevisitedandtheproducttypeinformationischanged. ............................... 91 4{25Thenewlayoutisdisplayedafterpressingthe\Resolve"button. ... 91 4{26Theresolveoptionsallowtheusertospecifyaparticularproduct'slayoutontheResolveLayoutgrid. .................. 92 4{27Thelayouthasbeenresolvedwiththeuser'sspecicationsenforced. 93 4{28Bayassignmentsformultipleproducttypescanbeenforced. ..... 93 4{29Thenallayoutismodiedtohonortheenforcedbayassignments. 94 4{30TheSelectChangeeventprocedureallowstheusertoenforceparticu-larbayassignmentsforselectedproducttypes. ........... 96 4{31TheResolveprocedurerecordschangesmadetoinputvaluesandhon-orsenforcedbayassignments. ..................... 97 5{1Anexampleofaparallelserialsystem. ................. 99 5{2Thewelcomesheet. ............................ 101 xi

PAGE 12

..... 102 5{4Thehiddencalculationsheetforthesimulationdata. ......... 103 5{5Thesimulationsheetwiththeanimationlayoutandinputtable. ... 104 5{6Thethirdcalculationsheetfortheresultsofthesimulationruns. ... 104 5{7Thetophalfoftheoutputsheet. .................... 106 5{8Thebottomhalfoftheoutputsheet. .................. 107 5{9Theinputformwiththedynamiclabelvalueformachinetype\A." 108 5{10TheInputBox. .............................. 108 5{11TheMessageBox. ............................. 109 5{12TheMainprocedureandvariabledeclarations. ............. 111 5{13TheClearPrevprocedure. ........................ 112 5{14ThecmdOK Clickeventprocedure. ................... 113 5{15TheCalcWeibullprocedure. ....................... 114 5{16ThePrepSimprocedure. ......................... 115 5{17ThebeginningoftheStartSimprocedure. ................ 116 5{18TheendoftheStartSimprocedure. ................... 117 5{19TheCreateDataprocedureandWeibullInvfunction. .......... 118 5{20TheAnalysisPrepprocedure. ....................... 119 5{21Thenavigationalprocedures. ....................... 120 5{22Anexampleoftherstresolveoption. ................. 121 5{23Updatedanalysisfromtherstresolveoption. ............. 122 5{24TheResolveprocedure. .......................... 124 5{25Theresolveform. ............................. 125 5{26Theinitializationeventprocedurefortheresolveform. ........ 125 5{27TheClickeventprocedureforthe"OK"buttonontheresolveform. 126 5{28Anexampleofthesecondresolveoption. ................ 127 xii

PAGE 13

Adecisionsupportsystem(DSS)isamodel-basedorknowledge-basedsystemintendedtosupportamanagerialdecisionmakinguser.Aspreadsheet-basedDSSusesspreadsheetstoorganizedataandperformsomespreadsheetfunctions.Itusesabasicprogramminglanguagetodesignuserinterfaceandimplementmodelalgorithmsandcalculations.ADSSshouldalsooertheusersomeoptionstoresolvehisproblemforacomparativeanalysiswhichmayenhancethedecisionmakingprocess.Thisthesisproposesdesignprinciplesandadevelopmentprocessforbuildingaspreadsheet-baseddecisionsupportsystem. xiii

PAGE 14

1.1 AnIntroductiontoDSS EquippedwiththemodelingandalgorithmicskillstaughtinthestandardOperationsResearch(OR)curriculum,manyindustrialengineeringgraduatesfeelreadytosolvereal-worldproblems.Withaknowledgeandunderstandingoftheoryandapplicationsofmathematicalprogramming,simulationtechniques,andsupply-chainmanagement,theyarereadytohelptheircompaniessolveanydistribution,forecasting,orplanningproblems.Butthen,astheyinteractmorewithcoworkersandmanagers,theyrealizethatthemodelstheyhavelearnedcannotbeappliedeasilytomanyoftherealwordproblemstheynowface. Manyoftheseproblemsaredecision-makingproblemswhichrequiresimplesolutionswithoutthedetailsofthemathematicalmodelsusedtosolvethem.Mostmanagerswouldpreferasoftwaresolutionforsuchdecisionproblems.However,mostindustrialengineeringgraduatesknowtherightmodelbutnothowtopackageamodelandpresentitwithafriendlygraphicaluserinterface(GUI).Themanagersneedtobeabletoeasilyuseit,seetheresultswithgraphsorcharts,andmodifyinputstoanalyzedierentbusinessscenarios.Thedesiredsoftwareprogramsshouldalsobeabletopulldatafromlargerdatabasesandmanipulateitappropriately. ThisisawidelyprevalentproblemwhichisnotaddressedinthecurrentORcurriculum.Modelsneeddatawhichismostlyavailableinspreadsheetsordatabases.Hence,ORgraduatesneedtoknowhowtoextractdatafromthesedatasources.Theyneedtoknowhowtocheckdataintegrityandperformdataanalysisanddatamanipulation.AsORpractitioners,ORgraduatesaresupport 1

PAGE 15

stamembersandarerequiredtobuildsystemsfornon-ORusers.TheymustknowhowtopackageORmodelssothattheycanbecomfortablyusedbytopmanagersandotherco-workers.Real-lifedecisionmakingoftenrequiresbuildinginteractivesystems,whichORgraduatesmustknowhowtodesignandimplement.Tosummarize,ORgraduatesmustlearnsucientinformationtechnologyskillstothattheycanbuildintelligentinformationsystems,alternatively,calleddecisionsupportsystems,whichcanrunsophisticatedmodelsattheback-end,butarefriendlyenoughatthefrontendtobeusedcomfortablybyanyuser. Adecisionsupportsystem(DSS)givesitsusersaccesstoavarietyofdatasources,modelingtechniques,andstoreddomainknowledgeviaaneasytouseGUI.Forexample,aDSScanusethedataresidinginspreadsheets,prepareamathematicalmodelusingthisdata,solveitoranalyzeitusingproblem-specicmethodologies,andassiststheuserinthedecision-makingprocessthroughagraphicaluserinterface.TheimportanceofDSSdevelopmentskillshasbecomewellnotedintheliterature:\Giventhegrowingcomplexityanduncertaintyinmanydecisionsituations,helpingmanagersusequantitativemodelstosupporttheirdecisionmakingandplanningisanimportantresearchtopic"([ 1 ]).DSSapplicationsareusuallyintendedtobedesignedfornontechnicaluserspresentedwithaneasytouseinterface. ORgraduatesarefrequentlybeingemployedinpositionsthatrequiredevel-opingDSSwhicharegainingwidespreadpopularity.Asmoreandmorecompaniesinstallenterpriseresourceplanning(ERP)packagesandinvestinbuildingdatawarehouses,thosewhoareabletocreatedecisiontechnologiesdrivenapplicationsthatinterfacewiththesesystemsandanalyzethedatatheyprovidewillbecomeincreasinglyvaluable.Indeed,impartingDSSdevelopmentskills,whichcombineORskillswithITskills,willmakegraduateshighlysoughtafterinthemodernworkplace.

PAGE 16

DevelopingcoursesthatteachORstudentshowtobuildDSShasbeenachallengingtasksofarsinceitrequirestheavailabilityofplatformswhichallowedtheintegrationofvarioustechnologies(data,models,codes,etc.).However,inthepastfewyears,severalplatformshavebecomeavailablewhichallowssuchintegration.OnesuchplatformisMicrosoftExcel.Excel,whichisthemostwidelyusedspreadsheetpackageamongmanagersandengineers,allowsdatastorageandmodelbuilding.Excelalsohasmanybuilt-inprogramaswellasmanyadd-onprogramsavailablethatwallowoptimizationandsimulationofvariousmodelsbuiltinExcel.Excelalsohasamacroprogramminglanguage,VisualBasicforApplications(VBA),whichallowsbuildingGUIsandmanipulatingExcelobjects.Thus,ExcelprovidesaplatformusingwhichfairlysophisticatedDSSapplicationscanbebuilt. 1.2 DeningDSS Adecisionsupportsystem(DSS)isamodel-basedorknowledge-basedsystemintendedtosupportmanagerialdecisionmaking.ADSSisnotmeanttoreplaceadecisionmaker,buttoextendhis/herdecisionmakingcapabilities.Itusesdata,providesaclearuserinterface,andcanincorporatethedecisionmaker'sowninsights.SomeofthemajorDSScapabilitiesarethefollowing. 1. ADSSbringstogetherhumanjudgmentandcomputerizedinformationforsemi-structureddecisionsituations.Suchproblemscannotbeconvenientlysolvedbystandardquantitativetechniquesorcomputerizedsystems. 2. ADSSisdesignedtobeeasytouse.Userfriendliness,graphicalcapabilities,andaninteractivehuman-machineinterfacegreatlyincreasetheeectivenessofaDSS. 3. ADSSusuallyusesmodelsforanalyzingdecision-makingsituationsandmayalsoincludeaknowledgecomponent. 4. ADSSattemptstoimprovetheeectivenessofdecisionmakingratherthanitseciency.

PAGE 17

Figure1{1: Aschematicviewofadecisionsupportsystem. 5. ADSSprovidessupportforvariousmanageriallevelsfromlinemangerstotopexecutives.Itprovidessupporttoindividualsaswellasgroups.ItcanbePC-basedor ADSSapplicationcontainsvecomponents:database,modelbase,knowledgebase,GUI,anduser(seeFigure1.1).Thedatabasestoresthedata,modelandknowledgebasesstorethecollectionsofmodelsandknowledge,respectively,andtheGUIallowstheusertointeractwiththedatabase,modelbaseandknowledgebase.Wenowpresentamoredetailedlookateachofthesecomponents.

PAGE 18

optimizationmodelsincludelinearprogramming,integerprogramming,andnonlinearprogramming.TheDSSallowstheabilitytoinvoke,run,andchangeanymodelorcombinemultiplemodels.Anexampleofamodelbasewouldbeanintegerprogrammingmodelusedtosolveacapitalbudgetingproblem.MostcommonDSSapplicationsareprimarilymodeldriven.AkeyDSScomponentisitsresolveoptions.Ausershouldbeabletomanipulatetheirinputvaluestocomparemultipleresultsforscenarioanalysis.TheDSSshouldbedesignedforrepeatedusetoaidinarecurringdecisionsituation.Itshouldbedynamicenoughtohandlevariousproblemsizes,inputvalues,andobjectives.

PAGE 19

TheuserinterfaceisperhapsthemostimportantcomponentofaDSSbecausemuchoftheposer,exibility,andeaseofuseofaDSSarederivedfromthiscomponent. ADSSshouldbedistinguishedfrommorecommonmanagementinformationsystems(MIS).AnMIScanbeviewedasaninformationsystemthatcangeneratestandardandexceptionreportsandsummariesformanagers,provideanswerstoqueries,andhelpinmonitoringtheperformanceofasystemusingsimpledataprocessing.ADSScanbeviewedasamoresophisticatedMISwhereweallowtheuseofmodelsandknowledgebasestoprocessthedataandperformanalysis. 1.3 ExcelSpreadsheets MicrosoftExcelspreadsheetshavebecomeoneofthemostpopularsoftwarepackagesinthebusinessworld,somuchsothatbusinessschoolshavedevelopedseveralpopularExcelbasedcourses.Aspreadsheetapplicationhasfunctionalityforstoringandorganizingdata,performingvariouscalculations,andusingadditionalpackages,calledAdd-Ins,formoreadvancedproblemsolvingandanalysis.Excelspreadsheetsareeasyforausertointeractwithandeasyforastudenttouse

PAGE 20

whiledevelopingtheDSS.WeconsidertwoaspectsofExceltobeimportantindevelopingaDSS:basicfunctionalityandextendedfunctionality. Excelbasicfunctionalityincludesreferencingandnames,functionsandformulas,charts,andpivottables.Thesearestandardtoolsthatmaybecommontomostspreadsheetusers.Excelextendedfunctionalityincludesstatisticalanalysis,theSolverandmodeling,simulation,andqueryinglargedata.Thesetoolsareespeciallyimportantforbuildingadecisionsupportsystem.TheabilitytomodelaproblemandsolveitorsimulateitaddsthemodelbasecomponentoftheDSSwearebuilding.ItisimportantthataDSSdeveloperbecomefamiliarwiththecapabilitiesofExcelsothattheyknowwhattheycanoertheuserwhendevelopingadecisionsupportsystem. 1.4 VBAforExcelProgrammingLanguage VBAforExcelisaprogramminglanguagethatallowsforfurthermanipulationoftheExcelfunctionalities.VBAforExcelalsoallowsthedevelopertocreatedynamicapplicationswhichcanreceiveuserinputforthemodelbasecomponentoftheDSS.VBAallowsuserswithoutknowledgeofExceltobeabletousespreadsheet-basedDSSapplications.ThereareseveralimportantfeaturesofVBAforExcel. Someofthesefeaturesincluderecordingmacrosandworkingwithvariables,procedures,programmingstructures,andarraysinVBA.VBAforExcelisaneasytounderstandprogramminglanguage.Evenifastudenthasnotprogrammed

PAGE 21

before,theyshouldbeabletoprogramseveraltypesofapplicationsafterabasicintroductiontoVBA. ADSSdevelopercanalsocreateauserinterfaceinVBA.Thesefeaturesincludesbuildinguserforms,workingwithseveraldierentformcontrols,usingnavigationalfunctions,anddesigningaclearandprofessionalapplication.VBAisbenecialasitplacesallofthecomplicatedspreadsheetcalculationsandanyotheranalysisinthebackgroundofauser-friendlysystem. SomeoftheextendedExcelfunctionalitytopicscanbefurtherenhancedbyusingVBA.Themodeling,simulation,andqueryfeaturesofExcelcanbecomedynamicusingVBAcommands.ThesetechniquesareespeciallyimportanttounderstandinordertobuildcompleteDSSapplications. 1.5 TheDSSDevelopmentProcess WepresentachapterontheDSSdevelopmentprocesstoexplainhowtheExcelspreadsheetfunctionalityandVBAprogrammingfeaturescanbecombinedtodevelopacompleteDSSapplication.Weproposevebasicstepsforthisdevelopmentprocess:i)outliningtheapplication,itsmodelandassumption;ii)determininghowmanyspreadsheetswillbeneededandforwhatpurposes;iii)constructingagenerallayoutoftheuserinterfacefeatures;iv)outliningtheprogrammingproceduresneeded;andv)ensuringthatresolveoptionswillbeintegratedintotheDSS.Wedescribethesestepsindetailandgiveseveralexamplesinthischapter.

PAGE 22

WealsopresentachapteronGUIdesignandprogrammingprinciples.ThereismuchliteratureonthesetwotopicswhichareimportantindevelopinganyDSS.Wesummarizetheissuesthataremostrelevanttodevelopingspreadsheet-DSSapplicationsinthischapter. 1.6 CaseStudies Wepresenttwocasestudiestoillustratetherelevanceandimportanceofdecisionsupportsystemsintheeldsofindustrialandsystemsengineeringandbusiness.WestrivetoaccomplishthisbyshowinghowtodevelopDSSapplicationswhichintegratedatabases,models,methodologies,anduserinterfaces. ThesecasestudiesconsistofdevelopingacompletedecisionsupportsystemandarebasedonanimportantapplicationofIE/ORorbusiness.Throughcasestudies,graduateswilllearnhowIE/ORandbusinesstechniquesapplytoreal-lifedecisionproblemsandhowthosetechniquescanbeeectivelyusedtobuildDSSapplications. ThesecasestudiesarejustsomeofthenumerouscasestudieswedevelopinordertoillustratehowDSSapplicationscanbedevelopedbycombininginforma-tiontechnologytoolswithoperationsresearchandbusinesstoolstosolveimportantdecisionproblems.

PAGE 23

2.1 DeningtheDevelopmentProcess Nowthatwehavediscussedingreatdetailthecomponentsofaspreadsheet-baseddecisionsupportsystem(DSS),weneedtolearntheprocessofputtingthesecomponentstogethertobuildacompleteDSSapplication.BeforeenteringformulasintoExcelorcodingsubproceduresinVBA,itisnecessarytoconstructanoveralllayoutfortheDSSandgivesomethoughttothedesignandimplementationoftheapplication.WeproposevebasicstepsfordevelopingaDSS:i)ApplicationOverview:createalayoutoftheentireapplicationtounderstandtheowfromtheuserinputtothemodelcalculationstotheoutput,ii)Spreadsheets:determinehowmanyspreadsheetsyouwillneedtobesthandleinput,calculations,andoutput,iii)UserInterface:outlinewhatinterfaceyouwillneedtoreceiveinputfromtheuserandnavigatethemthroughtheapplication,iv)Procedures:outlinewhatsuband/orfunctionproceduresyouwillneedinyourcodetoreceivetheinput,performthecalculations,anddisplaytheoutput,v)DSSComponents:decidewhatresolveoptionstheusermayhave. Thesestepshavebeenourguidelinestodevelopingdecisionsupportsystems.Wedonotclaimthattheyarenecessarytofollow,butrathersuggestthemasgoodguidelineswhendevelopingaDSSapplication.Inthischapterwegiveseveralexamplesfromcasestudieswehavedevelopedusingtheseproposedsteps.Thefollowingchaptersgiveamoredetailedexplanationofeachcasestudy'sdevelopmentusingthesevesteps.Wewishtoillustratethevariety,andconsistencies,possibleindevelopingDSSapplications. 10

PAGE 24

2.2 ApplicationOverview TheApplicationOverviewisthemostimportantstepindevelopingaDSS.Inthisstep,weconsidertheentireowoftheapplication.Weusuallybeginthisowatthe\Welcome"sheet.A\Welcome"sheetshouldhavethetitleoftheDSSandsomedescriptionofwhattheapplicationdoes.Anyassumptionsornecessarymodelexplanationsmaybegiveninthisdescription.Theremayalsobesomeimagesonthisinitialsheetrelatedtotheapplicationtopic.Thenthereshouldbeonebuttonto\Start"or\Begin"theapplication.Eventhoughthissheetissimple,itisanimportantintroductionfortheusertowhatyourDSSisandhowtheycanbegintouseit. Theusershouldthenencountersomemethodforprovidinginput.Thismayinvolveaformorsetofforms,ortheusermaybebroughttoanewsheetwherefurtherinstructionsareprovided.Decidingwhichmethodormethodstouseisimportantanddependsontheapplication.Forexample,ifyouonlyneedoneortwopiecesofinformationfromtheuser,youmaynotevenneedaformoranentiresheetforinput;instead,youmayuseanInputBox.Insomeapplications,youmayneedlargesetsofdataforyouranalysis.Inthatcase,youmayonlyprompttheusertoimportdatafromatextleordatabasetoaspreadsheet.Onceyouhavedecidedwhichmethodismostappropriateforyourapplication,youmayneedtospendmoretimedesigningtheinterface;however,wewillreturntothisinalaterstep.ItisimportanttocompletetheApplicationOverviewbeforedesigningtheinterfacesothatyouhaveaclearideaofwhattheentireapplicationwillincorporate. Afterreceivingtheinput,themodelshouldbereadyandcalculationscanbeperformed.Itisagoodideaatthispointtohaveanoverviewofwhatisrequiredforyourmodelcalculations.Youmayneedtoknowthemodelformulationbeforeyoucannishdecidingwhattheuserinputwillbe.Therstthingtodecideis

PAGE 25

ifthisDSSwillbecomputingsimplecalculations,performinganoptimization,orrunningasimulation.Thedetailsofthesemodelscanbeoutlinedinalaterstep,butforthepurposeoftheApplicationOverview,youshouldhaveanideaofwhatwillbeinvolved.Thisgeneralmodeloutlinewillhelpyouindeterminingthedetailsofyourspreadsheetdesignandprocedureslater.Onceinputisreceivedandthemodelcalculationsareperformed,weneedtodeterminewhatoutputwillbedisplayedtotheuser.Willtherebechartsorgraphs,orhistogramsortables?Doessomeoftheinputneedtobere-displayedtotheuser?Again,theseoptionswilldependontheapplication.ItisimportanttoconsidertheoutputasitisadrivingforceinwhytheuserisusingtheDSS.Itisagoodchecktoseeifyouarecomputingeverythingtheusermaybeinterestedin. ThelastpartoftheApplicationOverviewisreviewingtheDSScomponents.InChapter1,wedeneindetailwhataDSSiscomprisedof.Theseinclude,themodelbaseanduserinterfacediscussedabove.However,aDSSshouldalsoprovidesomeresolveoptionsfortheuser.Theusershouldbeabletochangesomeoftheirinitialinputvaluesandresolvetheproblem.Theusermayalsowanttoaddsomeconstraintstoanoptimizationorredenetheirobjectivefunction.Wesuggestthattheseresolveoptionsaremadeavailableontheoutputsheet.WewillgivemoreexamplesoftheseDSScomponentsinthefollowingsections. Table2{1: Summary:ApplicationOverview WelcomeSheet Flowbegins;introductiontowhatDSSisandhowtobeginusingit. Input Providedbyuserviasetofforms,inputspreadsheet,orInputBoxes. ModelCalculations Formulationofobjectivesandnecessaryinput;decideifcomputingsimplecalculations,performinganoptimiza-tion,orrunningasimulation. Output AdrivingforceinwhytheuserisusingtheDSS. ResolveOptions Usecanmodifyinput,redeneconstraints,orchangeobjectives.

PAGE 26

Figure2{1: Anexampleofa\Welcome"sheet. 2.3 Spreadsheets TheremaybetwotoseveralsheetsinaDSSapplication.Therstsheetshouldalwaysbethe\Welcome"sheetaswediscussedabove.Forexample,inFigure2.1,weshowthe\Welcome"sheetfromacasestudywedevelopedforaPortfolioManagementandOptimizationDSS.WegiveadescriptionoftheDSSanddescribethemodelassumptions.Wealsoreferencethesourceofourmodelformulation.Wealsohavesomeimagesrelatedtoportfolios.Thenwehavea\Start"buttonwhichtheusercanpresstobegintheapplication. Theremainingsheetsareforinput,calculations,andoutput.Wemayhavetheseasseparatesheetsorsomeelementsmaybecombinedonfewersheets.Sup-poseweneedasheetforinput.Wecanpreparethesheetbyplacingappropriatelabelsfortablesorinputlocations.Wemayalsonamesomerangesatthispointwhichwillhelpuslaterwhencoding.Belowisanexamplefromacasestudywe

PAGE 27

Figure2{2: Anexampleofusingspreadsheetstotakeinputfromtheuser. developedforusingtheCriticalPathMethod(seeFigure2.2).Inthisapplication,wetaketheuserthroughseveralinputsheets.Ineachsheet,wehaveatableforasetofinputvalues.Insomecases,spreadsheetmaybeabetteruserinterfacethanformsforreceivinginput;wediscussthisinmoredetailinthenextsection. Youmayalsohaveanapplicationwhichrequiresalargesetofdata.Thisdatamaybeimportedfromatextleordatabase,orinputbytheuser.InFigure2.3,wehaveaninputsheetfromaStochasticCustomerForecastingcasestudy.Thissheetcontainsthehistoricaldatathatisusedtomakefutureforecasts.Inthisapplicationwegivetheusertheoptiontoenterthisdatamanuallyorimportitfromatextle. Youmaynotneedaninputsheetforeveryapplication.Letusconsiderthecasewheretheinputsheetmaybecombinedwiththecalculationssheetoroutputsheet.Forexample,inthegurebelow,wehaveasheetfromaTechnicalAnalysiscasestudyinwhichtheinput,calculations,andoutputareallononesheet(seeFigure2.4).Heretheusercanmodifytheinputusingspreadsheetcontrolsandpressthe\Resolve"buttontoupdatethecalculationsinthetable.Theoutputissummarizedinasmalltableontherightofthescreen. Again,youmaynotevenuseasheetatallforyourinput.Youmaysimplytakeinputfromauserformandthenusethatdirectlyinacalculationssheetor

PAGE 28

Figure2{3: Anexampleofalargesetofdataimportedfromatextle. Figure2{4: Anexampleofhavinginput,calculations,andoutputonthesamesheet.

PAGE 29

Figure2{5: Anexampleofacomplicatedcalculationssheet. insomecalculationsproceduresandtaketheuserdirectlytotheoutputsheet.Sincewearedevelopingspreadsheet-basedDSSapplications,wewillusuallytakeadvantageofthespreadsheetfeaturestoaidusinperformingcalculations.Forthisreason,wewillusuallyhaveacalculationssheet.Thissheetmaybeviewedbytheuseror,inmostcases,hiddenfromtheuser.Acalculationssheetshouldbehiddeniftheintendedusermaynotbefamiliarwiththedetailsofthecalculationsbutissolelyinterestedintheresults.InFigure2.5,wehaveacomplicatedcalculationssheetfromasimulationperformedinaRetirementPlanningcasestudy.Thereareseveralspreadsheetfunctionsandformulasinthesheetaswellassomeinputcellswhosevalueshavebeenupdatedafterauserhascompletedaninputform.Sincethesheetcalculationsaresomewhatcomplicated,wedonotshowthissheettotheuserduringthenormalowoftheapplication;however,wedogivetheusertheoptiontoviewthecalculationsiftheywantto.Wenormally,taketheuserdirectlyfromtheinputformtotheoutputsheetinthisapplication.

PAGE 30

Youmayhavesomeotherhiddensheetsrelatedtothecalculations.Forexample,insimulationweusuallystoretheresultsoftherunstouserforcreatinghistogramsorothersummaryreports.Thisdetailedsheetshouldbehiddenfromtheuserintheapplicationow,butcanbemadeavailableforviewingiftheuserisinterested.ByusingtheApplication.ScreenUpdatingmethodandWorksheets.Visiblepropertywecanpreventtheuserfromseeingthesecalculationsheetswhiletheyarebeingusedforthemodelcalculations.Probablythemostimportantsheetfortheuseristheoutputsheet.Thissheetshouldsummarizetheresultsofthecalculationsclearlysothattheusercanunderstandthebehaviorofwhateversystemtheyweremodelingoranalyzing.Itisusuallyagoodideatohavesomegraphicalresultsaspartoftheoutputsheet.Forexample,inFigure2.6,wehavetheresultssheetfromanInventoryManagementcasestudy.Herethegraphillustratestheorderingstrategyfoundbythemodelcalculations.Therearealsosometablesusedtosummarizethenumericalresultsofthesolution. InDSSapplicationsusingsimulation,itisusuallygoodtohavesomehis-togramsaspartoftheoutputsheet.InFigure2.7,wehavesomehistogramssummarizingtheresultsofaReliabilityAnalysiscasestudy.Wehaveahistogramshowingthefrequencyofvarioussystemfailuretimevaluesusingabargraphalongwithanoverlaidscatterplottoshowthecumulativeprobabilityofeachvalue.Belowthat,wehaveanotherhistogramrepresentingthefrequencywithwhichdierentmachinetypeshavecausedthesystemfailure;thishistogramisshownasapiechart.Wegivetheuserseveraloptionsfromthisoutputsheetincludingtoreturntoandrerunthesimulationorreturntotheinitialinputphasetoresolvetheproblem. Insomecases,youmayhaveseveralchartsorlargersummarytablesthatmaynottintooneoutputsheet.Inthatcase,werecommendmakinganavigationaloutputsheetwhichwillallowtheusertoviewtheseindividualreports.For

PAGE 31

Figure2{6: Anexampleofusingagraphtoillustrateresultsinanoutputsheet. example,InFigure2.8,weshowanavigationaloutputsheetfromaSupplyChainManagementcasestudy.Thissheetallowstheusertoviewseveraldierentsummarypivottables(seeFigure2.9).Fromthesepivottables,theusercanalsoviewcorrespondingpivotchartsdisplayedasseparatechartsheets(seeFigure2.10).Theusercanalwaysreturntothenavigationaloutputsheetfromanyofthesereports. Whicheverresultsarerelevanttoyourapplication,youshouldensurethattheyarepresentedtoclearlyintheoutputsheet.\End"and\Resolve"optionsshouldalsobefoundintheoutputsheetaswellasoptionsto\View"inputorcalculationsheets. 2.4 UserInterface Designingauserinterfaceisanimportantelementofdevelopingauser-friendlyDSS.Wediscussgoodgraphicaluserinterface(GUI)designinalatersection;fornowwewilldiscusswhatroletheuserinterfaceplaysintheDSSdevelopment.Therearethreemaincategoriesofuserinterfaceinspreadsheet-based

PAGE 32

Figure2{7: Anexampleofhistogramsintheoutputsheetofasimulation-basedDSS. Table2{2: Summary:Spreadsheets WelcomeSheet TitleanddescriptionoftheDSS;images;\Start"but-ton. InputSheet Userinput;largedatainput;canbecombinedwithothersheets. CalculationsSheet Spreadsheetcalculations;simulationresults;usuallyhiddenfromuser. OutputSheet Summarytablesandreports;graphs,chartsorhis-tograms;navigationaloutputbuttons;\End,"\Re-solve,"and\View"buttons.

PAGE 33

Figure2{8: Anexampleofanavigationaloutputsheet. Figure2{9: Anpivottablereportsheetisoneoftheoutputsheets.

PAGE 34

Figure2{10: Thecorrespondingpivotchartisanotherreportsheet. DSSapplications:userforms,formcontrolsonthespreadsheet,andnavigationalbuttonsonthespreadsheet. Letusbeginbydiscussingnavigationalbuttons.Aswehavealreadymen-tioned,therstbuttonyoushouldcreateisthe\Start"buttonwhichislocatedonthe\Welcome"sheet(seeFigure2.1).Thisbuttonshouldbeassignedtoamacrowhichbringstheusertotheinputinterface.Onallothersheets,input,calculation,andoutputsheets,thereshouldatleastbean\End"button.Theusershouldalwayshavetheoptionto\End"or\Exit"theapplication.Note:Whetheryouuse\End"or\Exit"oranyotherphraseforthisaction,besurethatyouareconsistentacrossallsheetsintheapplication.WediscussconsistencyinuserinterfacedesignmoreintheGUIdesignsection. Youmayalsohavesomeothernavigationalbuttonssuchas\Next,"\Con-tinue,"or\Back"ifyouintendfortheusertobeabletostepthroughthesheetsorrevisitsheets.Thisisespeciallyimportantifyouhavehiddenthesheettabsorare

PAGE 35

onlymakingonesheetvisibleatatime;whichwerecommendforamoreprofes-sionalpresentation.Inthecasewhereinput,calculations,oroutputarecombined,youmayalsohavesomefunctionalbuttonsonthespreadsheetsuchas\Solve." Forexample,inFigure2.11,wehaveonesuchsheetinacasestudyontheAnimationoftheKruskalAlgorithm.Inthiscasestudy,wetaketheuserdirectlyfromthe\Welcome"sheettothesheetshowninthegure.Wehighlightthe\CreateTable"buttonasitthenextbuttontheyshouldpress(seeFigure2.11(a)).Whentheypressthisbutton,theyarepromptedtogivethedimensionsoftheirnetwork,andthenatablewiththecorrespondingnumberofrowsiscreated.Afterthetableiscreated,wenowmakeanewbuttonvisiblecalled\Solve"(seeFigure2.11(b)).Weun-highlightthe\CreateTable"buttonandhighlightthe\Solve"buttonsincethisisthenextbuttontheusershouldclick.ThisbuttonwillruntheprocedurewhichanimatesKruskal'salgorithmsandndstheminimumspanningtreesolution. Asidefromusingfunctionalornavigationalbuttonsonthespreadsheet,auserinterfacemayalsouseformcontrolsonthespreadsheet.RefertoFigure2.4toseeanexampleoftextboxesandcomboboxesusedonaspreadsheetinwhichinputwastakenfromtheuseronthesamesheetwherecalculationsandoutputweredisplayed.AnotherexampleisshowninFigure2.12.ThisexampleisfromtheInventoryManagementcasestudy.Herewehavethreeoptionbuttonsrepresentingdierentmethodswhichcanbeusedtondthebestorderstrategy.Theseoptionbuttonsaremutuallyexclusiveandtwoofthemalsohavedynamicfeatures.Thebottomtwobuttonshavesomeassociatedcellsforextrainputwhichareshadeddarkerwhenunmarkedandmadelighterwhenmarked(seeFigure2.12(b)). Formcontrolsonthespreadsheetareusefulwhentherearemanyresolveoptionsintheapplication.Inthiscase,youwanttogivetheusereasyaccesstotheinputinordertobeabletochangeitmultipletimes.Itisimportanttokeepthe

PAGE 36

Figure2{11: Anexampleofbuttonsonthespreadsheettoworkwithinputandcalculations.

PAGE 37

layoutofthespreadsheetclearandunclutteredwhenusingplacingformcontrolsadjacenttootherinputcells,calculations,oroutput.Wediscusstheseinterfacedesignissuesinalatersection. Insomecases,theremaybeaneventradeobetweenusingfunctionalbuttonsorformcontrolsonthespreadsheetversuscreatingauserform.Forexample,inFigure2.13(a),wehaveusedtwofunctionalbuttonstoallowauserto\Add"and\Remove"stockstoandfromtheirportfoliointhePortfolioManagementandOptimizationcasestudy.Inthisparticularcasestudy,wehaveputthisfunctionalityonthespreadsheetbecauseitisafeaturetheusermayuseoften.Theusermaygotoanewsheettoviewstockcomparisonsandthenreturntoedittheirportfolio;theusermayalsogotoanoptimizationsheettoviewinvestmentstrategyresultsandthenreturntoedittheirportfolioandresolve.However,ifthiswerenotthecase,thatisiftheuserdidnotneedtocreateoredittheirportfoliomultipletimes,wemayhavecreatedauserformtoperformthisfunctionality.InFigure2.13(b),weshowanexampleofsuchaformusedinaBetaofStockscasestudy.Inthiscase,theuseronlyselectstheirportfolioonce. InmostDSSapplications,ifthereisalargeenoughsetofinputrequiredfromtheuser,wesuggestcreatinguserforms.Userformscanbeadvantageousinthattherearemanyoptionsforplacingandmanipulatingcontrolsonauserform.Thecontrolscanalsobemoreclearlydisplayedastheyarenotinterferingwithothercellsonthespreadsheet.Anotheradvantageofuserformsisthattheycanbedisplayedtotheuseratanytime;thatis,theyarenotattachedtoaspecicspreadsheet.Thiscanbeespeciallyusefulforresolveoptions. Ifauserwishestoresolvetheproblemandpressesa\Resolve"buttononanoutputsheet,theinputformcanberedisplayedtothemdirectlywithoutevenmovingtoanewsheet.InFigure2.14,wehaveanexampleofauserformfromaRetirementPlanningcasestudy.Thisformisdynamicinthattherstframebelow

PAGE 38

Figure2{12: Anexampleofdynamicformcontrolsonthespreadsheet.

PAGE 39

Figure2{13: Anexampleofcontrolsonaformandspreadsheet.

PAGE 40

Figure2{14: Anexampleofdynamicformcontrols. thetextboxesmaychangedependingonapreviouslyselectedoption.InFigure2.14(a),theuserisprovidingvaluesfor\DesiredSavingsatRetirement"and\CondenceIntervalforReturns"whereasinFigure2.14(b)thisframedischangedtoprompttheuserforthe\AgetoRetire."Thesecondframeonthisform,for\AssetAllocation,"isalsodynamic.InFigure2.14(a)theuserispromptedtoenterthisinformation,butinFigure2.14(b)thetextboxesaregrayedandlockedsincetheinformationisnotrelevantforthisoption. Anotherwaytouseuserformsinasituationwheretheusermayneedtomodifyinputmultipletimesistocreatea\oating"form.Theadvantageofthistypeofuserformisthattheusercanselectormodifycellsinthespreadsheetwithouthavingtoclosetheformrst.InFigure2.15,thereisanexampleofaoatingformfromacasestudyforAnimatingtheSimplexMethod.Thisformisusedtoallowtheusertoselecttheenteringvariableforeachiteration.Theuserselectstheenteringvariablefromthetableauonthespreadsheetandcanthenview

PAGE 41

Figure2{15: Anexampleofa\oating"form. theresultsforthatscenarioontheoatingform.Theformishiddenwhentheusermovestoanothersheet. 2.5 Procedures ThenextstepindevelopingaDSSapplicationistomakeanoutlineofwhatproceduresyouwillneedtoconducttheowandexecutethecalculations.AsdiscussedinChapter15,weencourageyoutoorganizeyourcodeintoseveralsmallerprocedureswhichmaybecalledfromothermainproceduresorassociatedwithbuttonsonthespreadsheet.Werecommendmakinganoutlineoftheseproceduresinyourcodebeforeyoubeginthedetailsoftheimplementation.WealwaysbeginourapplicationswithaMainsubprocedurewhichisassociatedwiththe\Start"buttononthe\Welcome"sheet.FromtheMainprocedure,weusually

PAGE 42

Table2{3: Summary:UserInterface NavigationalBut-tons Thesebuttons,suchas\Start,"\Next,"\Back,"or\End"shouldbeoneverysheettonavigatetheuserthroughtheapplication. FunctionalButtons Thesebuttons,suchas\CreateTable"or\Solve,"maybeusedifmultiplefunctionsoccurononesheet. ControlsontheSpreadsheet Placingcontrolsonthespreadsheetallowsuserstoeasilymodifyinputformultiplesolutioncalculations. UserForms Userformsaremostcommonlyusedwhenlargeamountsofinputareneeded;theyareoftenadvan-tageous. beginbyclearingpreviousdataandinitializingvariables;thiscanalsobedonebycallingaClearPreviousprocedure.Wetheneithertaketheusertoaninputsheetorshowthemaninputform.Considerthefollowingexample. SubClearPrevious()'clearrangesonothersheetsWorksheets(``Calc").Range(``InputValues").ClearContentsWorksheets(``Output").Range(``Results").ClearContents'initializevariablesSetInputRange=Worksheets(``Input").Range(``InputStart")EndSub

PAGE 43

Thereshouldthenbesomeprocedurewhichreceivestheinputfromtheuser.Ifweareusinguserformsastheinterfaceforreceivinginput,thenthiscodewouldbeintheeventproceduresfortheform.Considerthefollowingexample. SubDoOptimization()SolverResetSolverOKSetCell:=,MaxMinVal:=,ByChange:=SolverAddCellRef:=,Relation:=,FormulaText:=

PAGE 45

Table2{4: Summary:Procedures Main CallClearPreviousprocedure.Showinputformortakeusertoinputsheet. ClearPrevious Clearpreviousrangesofinputorsolutionvalues.Ini-tializevariables. ReceiveInput Storevaluesfromformcontrolsorinputcellstocorre-spondingvariables.Recordthesevaluestoappropriatecellsincalculationsheet. PerformCalcula-tions Performcalculationsusingfunctionprocedures,simula-tionsloops,orSolvercommands. GenerateOutput Displaysolutionvaluestoreporttable,updatechartsourcedata,orcreatehistograms. Navigational ChangeVisiblepropertyofworksheetsfor\End,"\Next,"\Back,"or\View"buttonfunctionality. Variables MakesureallvariablesaredeclaredandthatvariablesusedinmorethanoneprocedurearedeclaredasPublicvariablesatthetopofthemodule. 2.6 ResolveOptions ThelastbutmostimportantstepindevelopingaDSSapplicationistoensurethatithasallofthecomponentsofacompletedecisionsupportsystem.ThereshouldbesomeinputtakenfromtheuserviasomeGUI,andthereshouldbesomecalculationsmadebasedonsomemodelbase,database,orknowledgebase.Weconrmtheinputinterfacewhileoutliningtheuserinterface,andweconrmthecalculationsandmodelintheprocedureoutline.TheotherimportantDSSfeaturethatweshouldnowcheckistheresolveoptions. Cantheusereasilymodifytheinputtoresolvetheproblemwithouthavingtore-enterallinputfromscratch?Weshouldensurethatthisispossiblebymakingsuretheuser'sinitialinputvaluesarepreservedwhenre-displayingauserform

PAGE 46

orinputsheet.MakesureyoudonotcallaClearPreviousprocedureunlesstheuserhasindeedrestartedtheentireapplication.Alsoensurethatdefaultvaluesdonotoverwritetheuser'slastinputvalueswhenre-showingauserform.Thisallowsuserstoquicklymodifyoneorseveralpartsoftheinputandresolvethecalculationstocompareresults. Cantheuserchangeotherpartsofthecalculationsormodelwhenresolving?Thatis,wedonotwanttheusertobelimitedtoonlymodifyinginputvalueswhenresolving.Theusershouldbeabletochangesomeconstraintsorobjectivesaswell.Trytokeepyourapplicationdynamicsothatausercanexperimentwithdierentproblemdimensions.ThismaynotbepossibleorapplicableforeveryDSS,butifitis,itshouldbemadeavailabletotheuser.Ifsomedynamicoptionsarenotavailabletotheuser,stateyourassumptionsclearlyonthe\Welcome"sheettoexplainthis. Withresolveoptions,youmaywanttoprovidetheuserwithawaytocomparevariousresultsorscenarios.Youmaywanttostoremultiplesolutionsforthiscomparisonorsensitivityanalysis.AskyourselfwhattheuserisreallyinterestedinlearningfromtheDSS.RememberthataDSSisdesignedtoaidadecisionmakerinmakingadecision.Checkthattheresultsoftheapplicationareindeedhelpfultothisdecisionmakingprocess. Wewillgiveafewexamplesfromourcasestudies;adetaileddescriptionoftheDSScomponentscanbefoundineachcasestudychapterinthispartofthebook.LetusrstconsidertheReliabilityAnalysiscasestudy.Inthisstudy,auserisanalyzingaparallelseriessystemofthreemachinetypes.Afterprovidingthenecessaryinput,asimulationisruntodeterminethemeanfailuretimeofthesystemandhowoftenaparticularmachinetypecausedthesystemfailure.Figure2.16showstheoutputsheetforthisstudy.

PAGE 47

Figure2{16: TheoutputsheetfortheReliabilityAnalysiscasestudy.

PAGE 48

Figure2{17: Therstresolveoption:modifyinputintableandrerunsimulation. Inthiscase,wehavetworesolveoptionsfortheuser.Therstoptionisfortheusertoreturntothesimulationsheetandmodifytheinitialinputvaluesinagiventable(seeFigure2.17).Theycanthenre-runthesimulationandviewtheupdatedresults. Thesecondoptionisfortheusertoimprovethesystembyaddingonemachineofaparticularmachinetype.Toaidtheuser,ordecisionmaker,indecidingwhichmachinetypetheyshouldaddamachineto,werstrunanoptimizationinthebackgroundandsuggesttothemtheoptimalchoice.Wedonotenforcethisdecision,butinsteadtrytoaidthedecisionmaker.Thisinformationispresentedtotheuseronauserform(seeFigure2.18). Afteramachinetypeisselected,onemachineisaddedtothistypeandthesimulationisrerun.Theupdatedresultsarethenshown.

PAGE 49

Figure2{18: Thesecondresolveoption:suggestionismadetoaiddecisionmaker. AnotherexampleisfromtheInventoryManagementcasestudy(refertoFigure2.12).Inthiscase,theusercanactuallychangethemodelbasealongwiththeinputeachtimetheapplicationisresolved.Theusercandecidewhichinventorymodeltouse:StandardEOQ,Backorders,orReorderPoint.Thereisalsoaninputtablewhichcanbemodiedonthesamesheet. AnotherexampleisfromthePortfolioManagementcasestudy(refertoFigure2.13).Inthiscase,aftertheuserhascreatedtheirportfolio,theycanoptimizetheirinvestmentstrategybyminimizingrisk(seeFigure2.19). Afterllingtheinputintheuserformfortheoptimization(Figure2.19(a)),theresultingoptimizedinvestmentstrategyisdisplayedonanoutputsheet(Figure2.19(b)).However,iftheoptimizationwasinfeasible,oriftheuserwantstoexperimentwithdierentvalues,theycaneitherreturntotheinputformtoexperimentwithdierentvalues(bypressingthe\ModifyInput"button),orreturntotheportfoliosheettomodifytheirstockselection(bypressingthe\Modify

PAGE 50

Portfolio"button).Anextensiontothiscasestudymaybetoallowtheusertomodifytheirobjectiveinoptimizingtheirinvestmentstrategy;currentlyweassumethatweminimizerisk,buttheusermayalsowanttomaximizereturns. ResolveoptionsareanimportantDSScomponent.EnsurethattheDSSisaidingthedecisionmakerbyallowingtheusertomodifyinputsorcalculationoptions. Table2{5: Summary:ResolveOptions ResolveOptions Modifyinputs,calculationoptions,constraints,objec-tives;aiddecisionmakerinmakingthebestdecision.

PAGE 51

Figure2{19: Two\Modify"buttonsgivetheuserdierentresolveoptions.

PAGE 52

3.1 GUIDesign Agraphicaluserinterface(GUI)isthe\graphicalrepresentationof,andinteractionwith,programs,data,andobjectsonacomputerscreen."(Mandel[ 2 ])Itpresentsavisualdisplayofinformationandobjectswhichcanpresentvisualfeedbacktoauser.PartofthedenitionofaDSSis:ADSSisdesignedtobeeasytouse;userfriendliness,graphicalcapabilities,andaninteractivehuman-machineinterfacegreatlyincreasetheeectivenessofaDSS(refertoChapter1).Thus,itisveryimportanttodesigntheuserinterfacesuchthatitiseasyfortheusertounderstandanduse.Iftheuserinterfaceisnotdesignedwell,thentheapplication'sfunctionalitywillnotbeappreciated.Inthissection,wewilldiscusssometheorybehindgoodGUIdesignandgivesomeexamplesofgoodandbaduserinterfaces. 3.1.1 TheTheoryBehindGoodGUIDesign TherearemanyGUIdesignbookswhichlistsseveraldierentprinciplesandguidelinesforgoodGUIdesign.WepresenthereasummarizedversionofwhatwefeelarethemostimportanttheoreticalpointsforgoodGUIdesigninspreadsheet-basedDSSapplications.Theseare:knowingtheuser,theuser'stasksandgoals;maintainingclarity,andstayingconsistent. 3.1.1.1 Users,tasks,andgoals Itisimportanttoknowwhotheusersofyourapplicationwillbe.Aretheymanagers?Ifso,howdeepistheirunderstandingoftheproblem?Dotheyknowthemodeloralgorithmsbeingusedtoperformthecalculations?Whatterminologydotheyusetodiscusstheproblem?Iftheuserdoesnothaveahighlytechnicalunderstandingofthetopicoftheapplication,thentrytoavoiddescribingthe 39

PAGE 53

detailsofthemodelorcalculations.Thiswouldbeacasewhenthecalculationsheetmayremainhidden.Tryalsotogiveinstructionsandlabelinputwithoutusingtechnicalterminology.Forexample,insteadoflabelinginputasCorD,givemeaningfuldescriptionssuchas\AnnualCost"or\AnnualDemand."Inthecasethatyourusersdohaveamoretechnicalunderstanding,youshouldshowandexplainthecalculationsandassumptions.Youmayalsowanttogivemoredetailsusingtheterminologytheyarefamiliarwith. Keepinmindthattheuserisusingthisapplicationtocompletesometasksandachieveagoal.ItisimportanttoensurethattheuserinterfaceisanaidtotheuserincompletingthesetaskssothattheuserfeelsthattheDSShasindeedbecomeahelpfultoolinincreasingtheeciencyofachievingtheirgoal.Theuser'staskdomainincludes\thedatathatusersmanipulate,themannerinwhichthatdataisdivided,andthenatureofthemanipulationthatusersperformonthedata."(Johnson[ 3 ])Rememberthattheuser'stasksarealreadynecessarywithoutthehelpofaDSS;therefore,ensurethatyourDSSapplicationaidsthemincompletingthesetasksinthesamedomaintheyarefamiliarwith.Thesetasksshouldbeorganizedonsomepriorityorhierarchybaseinordertocreateaowfortheapplication.Thisowinuencestheoutlineoftheentireapplicationaswediscussedintherstsection.Yourinterfaceshouldguidetheusersothattheycanworkwiththedataintheirtaskdomainintheorderinwhichitneedstobecompleted. Forexample,letussupposethattheuser'staskdomaininvolveslookingatsomehistoricaldata,thencomputingameanandstandarddeviationofthisdata,andthenenteringthesevaluesintoaforecastingmodel.Basedontheresultofthemodel,theuserhastotaketheforecastdemandforthenextmonthandplaceanorderofthatsize.WhenconstructingtheinterfaceforaforecastingDSS,ensurethatthesetasksarepresentedtotheuserinthesameorder.First,ask

PAGE 54

themtoenterthehistoricaldata.Iftheyusuallygetthisdataasatextlefromacoworker,thendonotaskthemtoenteritmanually,insteadpromptthemtoimportthetextle.Youcanthenautomaticallycalculatethemeanandstandarddeviationtodisplayforthem.Afterwardsyoumayaskforsomeextrainputfortheforecastingmodel,buttrynottogettotechnical.Youmaythendisplayclearlytothemwhattheirorderamountshouldbebasedonthisforecast.DonottrytoreorganizetheirtasksastheywillndtheDSShardtolearn.Keepthepresentationofthetaskssimplesothatthereisanelementoffamiliarityforthem. Table3{1: Summary:Users,Tasks,andGoals Whatistheuser'sknowledgeoftheproblemandtechnicalunderstandingofthemodelcalculations? Whatterminologyistheuserfamiliarwith? Denetheuser'staskdomaintodeterminetheapplicationow. 3.1.1.2 Clarity Auserinterfaceisthecommunicationbetweentheuserandtheapplication;therefore,ifyouwanttheusertousetheapplicationcorrectly,youmustcommuni-cateclearlytothemwhattheyshoulddotouseit.Firstandforemost,makesurethereisacleardescriptionofwhatisinvolvedoneveryspreadsheetandeveryform.Forexample,inFigure3.1,weshowthecalculationsheetfromaSalesForceAllo-cationcasestudy.Weasktheusertoentersomeboundvaluesfortheoptimizationconstraints.Wethengivethemtwocalculationoptions.Weexplaintheuser'stasksinatextboxatthetopofthesheet.Wehaveboldedthebuttonnamesandcolumnnamesinthetexttohelptheuserquicklyidentifythelocationofthetasksonthesheet. Thefunctionalityofanybuttonorcontrolshouldbecleartotheuser.Onspreadsheets,trytomakesomeseparationbetweennavigationalbuttonsandfunctionalbuttons.Forexample,ifonaninputsheetyouhavethebuttons\End,"

PAGE 55

Figure3{1: Clearinstructionsanddescriptionsoneachsheetandform. Figure3{2: Buttonsareclearlyseparatedintonavigationandcalculationgroups. \Back"and\Solve,"itisbettertokeepthenavigationalbuttons\End"and\Back"togetherandplacethe\Solve"buttonsomewhereelseonthesheet. Likewise,onuserforms,ensurethatfunctionalbuttonsareseparatefromthe\OK"and\Cancel"buttons. Asidefrombuttonsandcommandbuttoncontrols,allothercontrolsshouldalsobeclearlylabeledsothattheirfunctionalityisunderstood.Neverletatextboxbeunlabeledandassumetheuserknowswhattoenter.Alsoensurethatlistboxesandcomboboxesarelabeledsothattheuserknowswhatthelistcontains.Framescontaininggroupeditemsshouldalsobelabeledtosignifythegrouping.Theclearerthecontrolsare,thequickertheusercanlearntheirfunctionalityand

PAGE 56

Figure3{3: Labelsclearlydesignatefunctionalityofcontrols. Figure3{4: Controltipsclarifycontrolfunctionality. theeasieritisforthemtousetheapplication.Forexample,compareFigure3.3(a)andFigure3.3(b);withoutclearcontrollabels,userswillhavetohesitateandguesswhatinformationyouareaskingfor. Anotherwaytoclarifycontrolfunctionalityisbycreatingcontroltipmessages.Thisisagoodwaytoprovidemoredetailedinstructionstotheuserwithoutclutteringtheform.Forexample,inFigure3.4,thereisacontroltipforthecombobox.Whentheuserplacesthecursoroverthecombobox,thetext\Thislistcontainsallproductsinthesystem"appears. Anotherbenettoclarifyingyouruserinterfacefunctionalityisthatitmayreducetheerrorsencounteredbytheuser.Themostfrequentusererrorsinvolveinputtingvaluesinanincorrectformatorofanincorrecttypeorchoosinga

PAGE 57

Figure3{5: Formattingguidelines. selectionorcommandbuttonataninappropriatetime.Eventhougherrorcheckingcanbedone,asdiscussedinChapter22,havingabetter-designeduserinterfacecanreducethisextracoding.Asidefromclearlylabelingcontrols,youmayalsogivedefaultvaluesasanexampleoftheinputtheusershouldenter.Youmayalsoguidetheuserforproperformattingissues. Forexample,referringtoFigure3.3(b),iftheuserissupposedtoenteracost,theymayenter\$20,000"or\20,000"or\20000."Ifyoudonotwanttheusertoenter\$"or\,"punctuationmarks,thenyoushouldclarifythistothemontheinterfacedesign.Eitherwritemorespecicinstructions,orguidethemwithdefaultvalues;otherwise,youwillhavetodosomeerrorcheckinginyourcodetoensurethatadatatypeerrordoesnotoccurwhenyoutrytoperformanoperationontheirinputvalue(seeFigure3.5). Someothercommonformattingexamplesarefornumericalinputsuchassocialsecuritynumbersortelephonenumbers.InFigure3.6(a)weshowthattheusermayinputthesevalueswithvariousformatting.Thismaycauseerrorswhenstoring,searchingfor,orperformingoperationswiththedata.Figure3.6(b)hasclariedtheformattingissuessothattheuserisonlyenteringnumericalvalueswithoutextrapunctuation.

PAGE 58

Figure3{6: Clearformattinganddefaultvalues. Ifthereisstillausererrorwhileusinganinputinterface,makesurethataclearerrormessageisgiventotheuser.Theusershouldunderstandwhattheydidwrongandwhattheyneedtodotocorrecttheproblem.Forexample,theerrormessage\Incorrectinput!"isnothelpfultoauser.However,amessagesuchas\Youmaynotenternegativenumbers.Pleaseenterapositivenumber."redirectstheusertocorrecttheerror.Errorsshouldbehardtomakeandeasytocorrect. Overall,clarityisveryimportantingoodGUIdesign.Itisimportanttochecksheetandforminstructions,controllabels,anddatainputguidelinestoensurethattheusercanclearlyunderstandwhattodo. Table3{2: Summary:Clarity Giveclearinstructionsatthetopofeachspreadsheetandeachform. Labelcontrolsclearlysothattheirfunctionalityisunderstood. Controltipscanbeusedtoadddetailtofunctionalitydescriptionswithoutclutteringtheform. Givedefaultvaluestoclarifyhowdatashouldbeinput. Makeformattingissuesclear. ClearGUIdesigncanhelptheuseravoidmakingerrors. Ifusererrorsaremade,giveclearerrormessagestoredirecttheusertocorrecttheirerror. 3.1.1.3 Consistency ThethirdtheoreticalpointforgoodGUIdesignisconsistency.Auserwillbeinclinedtointeractwithaninterfaceaccordingtohowtheyareexpecting

PAGE 59

Figure3{7: Thenavigationalbuttonsaretogetherandconsistentinthesheet. ittobe.Thatis,theymayexpectsomeinputprompt,buttonlocations,andviewableoptionsbasedontheirfamiliaritywithworkingwiththeproblemorwithotherinterfaces.Itisimportantthatwithinyourapplication,oracrosssimilarapplications,somefeaturesoftheuserinterfaceareconsistent. Therstplacethereshouldbeconsistencyisonthespreadsheets.Trytokeepthetitleandsheetdescriptionandinstructionsinthesamelocationforeachsheetintheapplication.Thisway,iftheuserislookingforanexplanationofwhatisincludedonaparticularsheet,theycanalwayslookatthesamelocationonthesheet.Wetendtokeepsheettitlesanddescriptionsatthetopleftofeachsheetlayout.Alsoensurethatthenavigationalbuttons,especiallythe\End"button,isinthesamelocationoneachsheet.Theusershouldnothavetosearchthroughthesheettotrytoexittheapplication.ComparetheformspresentedinFigure3.7.Youshouldalsoconsiderconsistencyinthesheetlayoutforinputcellsandcharts.Forexample,ifyouhavemultipleoutputsheets,eachwithachart,thechartsshouldallbeinthesamepositiononeachsheet. Indesigninguserforms,consistencycanbeenforcedinseveralways.Firstofall,aswithsheets,ensurethatsomedescriptionlabelisalwaysatthetopoftheform.Alsokeepnavigationalcommandbuttons,like\OK"and\Cancel,"inthesamepositiononallforms.If\OK"isonthebottomrightofaformand\Cancel"

PAGE 60

Figure3{8: Consistentformattingandclearconstructions. isonthebottomleft,donotswitchthemforsubsequentforms.Theusershouldnotfeeltrickedintopressingthewrongbutton. Regardingformcontrols,usingthealignmentandsizefeaturescanalsoimprovetheformlayout.Trytokeeptextboxsizesthesamethroughouttheform;theyshouldalsoallbealignedequally.Keepallbuttonsthesamesizeaswell.Trytoalsobeconsistentwithpunctuation,suchastheuseofsemicolons.ComparetheformspresentedinFigure3.8. Ifyourforminstructionlabelandcommandbuttonsareconsistentfortheformsinyourapplication,youcansaveinterfacedevelopmenttimebysavingtherstformyoucreateandimportingitmultipletimes.(RefertoChapter18fordetailedinstructionsonhowthisisdone.) Anotherfeatureofformconsistencythatcanaidtheuserinmovingquicklythroughtheinterfaceisthetaborderoftheform.Ensurethatthetabordertaketheuserfromthetopoftheformdowntothe\OK"buttonandnallythe\Cancel"button.Again,theideaistohelptheuserfeelthattheuserinterface

PAGE 61

iseasytolearnandeasytouse.Keepingtheformsconsistentthroughoutyourapplicationwillhelptheuseraccomplishtheirtasksmorequicklyandeciently. Table3{3: Summary:Consistency Consistencyisimportantinhelpingtheusermovequicklythroughtheuserinterface. Keepsheettitlesandinstructionsinthesamelocation. Keepnavigationalbuttons,especiallythe\End"button,inthesameloca-tionpersheet. Keepinputcellsandchartsinthesamelocationsforsimilarsheets. Keepforminstructionsandcommandbuttonsinthesamelocationforeachform. Makecontrolsconsistentbyusingalignandsamesizefeatures. Settabordertotakeuserfromtopofformto\OK"and\Cancel"buttons. 3.1.2 GoodandBadGUIDesigns WewouldnowliketoprovideseveralexamplesofgoodandbadGUIdesigns.Wehavegroupedtheseexamplesbydierentcontroltypesandafewmoregeneralcategorieswhichapplytotheentireuserform. 3.1.2.1 Buttons Buttonsshouldalwaysbeofthesamesizeandshape.Trytoalsokeepbuttonsthesamecolorunlesshighlightingaparticularfunctionbuttontoguidetheuser.Groupfunctionalbuttonstogetherandnavigationalbuttonstogether.WehaveseenexamplesofthisinFigures2.21and2.26 3.1.2.2 Textboxesversuslistboxesandcomboboxes Reducethememorizationrequirementsoftheuserbyreplacingtextboxeswithlistboxesorcomboboxeswhenpossible.Thisalsoreducespossibilityoferrors.Forexample,ifuserisaskedtoenterastudentnamefortheirclassgrades,usingacomboboxwouldpreventtheuserfromenteringthenameofastudentwhichisnotintheirclass,ormisspellingthestudent'sname.ComparetheformsinFigure3.9.

PAGE 62

Figure3{9: Comboboxesreduceusermemorizationandchanceforerrors. 3.1.2.3 Tabstripsandmultipages Whenusingtabstripsormultipages,trytominimizethenumberoftabs.Toomanytabscancausetabpositionstoshiftwhenclicked;theusermaynotseeallofthetabsandleavesomeinputblank.Trytoreorganizeyourinputneedssothatyoucanusemultipleformsorcombinetabinformationtoreducethenumberoftabs.ComparetheformsinFigure3.10. 3.1.2.4 Checkboxesversusoptionbuttons Onlyoptionbuttonsshouldbeusedformutuallyexclusiveinput.Eventhoughoptionbuttonscanalsobeusedfornon-mutuallyexclusiveinputiftheyarenotgroupedinaframe,werecommendusingcheckboxesforthispurposeinstead.Beconsistentinyouruseofcheckboxesandoptionbuttonsfortheserespectivepurposes.ComparetheformsinFigure3.11. Foroptionbuttons,youshouldalwayshavemorethanonegroupedinaframe.Ifthereisonlyoneoption,thentreatitasan\on/o"optionanduseacheckbox.Anotherwaytomodifyonlyoneoptionistocreateanotheroptionwithanoppositevalue.Forexample,insteadofjustgivingtheuseran\on/o"optionfor\Assumenon-negativevalues,"youcouldcreateanotheroptionsuchas\Donotassumenon-negativevalues"or\Allowpositivevalues."

PAGE 63

Figure3{10: Tabstripsandmultipagescanbereplacediftoomanytabsareneeded.

PAGE 64

Figure3{11: Optionbuttonsareusedformutuallyexclusiveoptionsandcheckboxesareusedforotheroptions. 3.1.2.5 Frames Framescanbeusedtogroupsimilaritems.Youshouldthereforealwayshaveatleasttwocontrolsinaframe.However,ifyouhavemorethanoneframeonaformorsheettoseparatedierentgroupsofcontrols,youmayendupwithonlyonecontrolinoneoftheframes.Youshouldstillavoidhavingallframeswithonlyonecontroloronlyoneframewithonlyonecontrol.ComparetheformsinFigure3.12.Touseframeswithcontrolsonthespreadsheet,youcanuseshapeboxes. 3.1.2.6 Labelsversustextboxes Labelsshouldbeusedforread-onlyinformation.Donotuseatextboxtopresentinformationtoauserwhentheyshouldnotbeabletomodifyit.Forexample,supposewehaveaformtoreceiveinputforthreemachinetypesinaproductionsystem.Wemayusealooptoshowthesameformtotheuserthreetimestoreceivetheinputforeachmachinetype.Wemaydisplaythemachinetype

PAGE 65

Figure3{12: Frameshavemorethanonecontroleach. numbertotheuser,butwedonotwantthemtomodifyit;therefore,weshouldusealabelnotatextbox(seeFigure3.13) 3.1.2.7 Dynamiccontrols Fordynamiccontrols,youmaybemakingsomecontrolsvisibleornotvisibleoryoumaykeepthemvisiblebutmaketheminactive.Tomakeacontrolinactive,youmustgrayitouttoanextentthatthereisnoconfusionfromtheuseronwhetherornottheycanchangethevalueintheinactivecontrol(seeFigure3.14).Youshouldalsolockinactivecontrolssothattheycannotbemodied.Ifacontrolbecomesinactive,youshouldalsosettheTabStoppropertytoFalse. 3.1.2.8 Multipleforms Whenusingmultipleforms,makesuretohideorunloadsequentialformssothatthereisnotalayeringonthescreen.Theonlytimealayeringmayoccurisifthereisasubformwhichmustbelledbeforeamainformcanbecompleted.However,trytoavoidsuchsituations.

PAGE 66

Figure3{13: Labelsareusedfornon-changeablevalues. Figure3{14: Somefunctionsareactiveandsomeareinactive.

PAGE 67

3.1.2.9 Eventprocedures Associatingactionswithsomeeventprocedurescanbeconfusingtotheuser.Forexample,whenusingcommandbuttons,itisbettertousetheClickeventratherthantheMouseDownevent.Theusermaypressthemousedownandnotseearesultthinkingthattheformisnotworking. Fortextboxes,itisbettertousetheAfterUpdateeventratherthantheChangeevent.TheChangeeventmaycauseerrorsifsomeformattingorvalueshavebeenenforced.Forexample,supposethereisanerrorchecktoensurethatanumberhasbeenenteredinatextbox.Iftheuserisdeletingapreviousvaluetoenteranewone,whenthetextboxchangesfromonevaluetoempty,theerrorwillbecaused.InsteadusetheAfterUpdateeventsothatyouonlycheckthevalueofthetextboxoncethenewvalueiscompletelyentered. Forcheckboxes,werecommendthatyouusetheChangeeventinsteadoftheClickevent.However,beawarethatwithmutuallyexclusiveoptionbuttons,theChangeeventmaybeover-triggered,andsotheClickeventmaybebetter. 3.2 ProgrammingPractices Therearemanybooksonprogrammingpracticesandcodingstandards.WesummarizeherewhatwefeelaresomeimportantissueswhencodinginVBAforspreadsheet-basedDSSdevelopment.Wecategorizetheseissuesasfollows:codingwithaconsistentstyle,usingnamingstandards,havingclearcomments,andincreasingcodingeciency. 3.2.1 ConsistentStyle Yourcodeshouldreectaconsistentstyle;thatis,itshouldappearthatthesamepersonhasdevelopedallofthecode.Spacing,indenting,linelengthandotherformattingshouldbeconsistent.Wewouldrecommendkeepingproceduresandfunctionsspacedapartenoughsothatitiseasytoscanthroughthecode.

PAGE 68

Table3{4: Summary:GUIDesign CommandButtons Similarshapes,sizes,colors,andlocations.Separatefunctionalandnavigationalbuttons. TextBoxesver-susListBoxesandComboBoxes Minimizeusermemorizationbyusinglistboxesorcomboboxesinsteadoftextboxes.Thismayalsominimizeusererror. TabStripsandMultiPages Onlyuseafewtabs.Avoidhidingsometabs. CheckBoxesversusOptionButtons Optionbuttonsshouldbeusedformutuallyexclusiveoptions;usecheckboxesotherwise.Usecheckboxesfor\on/o"optionsorcreateanoppositeoptiontouseoptionbuttons. Frames Trytohavemorethanonecontrolperframeandmorethanoneframeperform. LabelsversusTextBoxes Labelsshouldbeusedforread-onlyinformation. DynamicControls Makeinactivecontrolsgrayedandlocked.Alsochangetheirtaborder. MultipleForms Makesuretoclosesequentialforms.Avoidlayeringifpossible. EventProcedures Commandbuttons:ClickbetterthanMouseDown;Textboxes:AfterUpdatebetterthanChange;Checkboxes:ChangebetterthanClick;Optionbuttons:ClickbetterthanChange

PAGE 69

Wealsorecommendusingindentingtosignifythebeginningandendofalooporlogicalstructure.Forexample,considerthetwoexamplesbelow. Anotherareawherestyleshouldbeconsistentisinerrorchecking.Ensurethaterrorcheckingisdoneconsistently.Thatis,donotcheckinputfromoneformbutnotanother;ordonotcheckinputfromaMessageBoxbutnotaform.Whatevermethodsyouuseforerrorchecking,trytousetheminallofyourerrorcheckingroutinesifpossible.Ingeneral,whenyouareoutliningyourprocedures,ensurethatthereissomeconsistencytoyourcodingapproach.

PAGE 70

3.2.2 Naming Namingstandardsshouldbeappliedtobothvariablesandprocedures.Avoidredundanciesinthesetwodierentareas.Thatis,ifyouhaveaprocedurenamed\MinProt,"trynottodeneavariablenamed\MinProf."Itisgoodtouseabbreviationsinnames,butmakesuretheyareclearandnotconfusablewithmultiplemeanings.Forexample,thevariablename\NumFac"mayrepresent\numberoffacilities,"\numberoffaculty,"or\numberoffactories."Whenusingmorethanonewordinaname,wesuggestcapitalizingthebeginningofeachword.Trytomakenamesdescriptivebutnottolengthy;forexample:\MinVal,"\SumProt,"\MaxPrice."Variableusedforloops,asidefrom\i"or\j"orothersmallindices,maybedescriptiveoftheloopcount.Forexample,youmayuse\iter"tocountthenumberofiterationsinaloop;or\run"tocountthenumberofrunsinasimulation. Someothercommonnamingstandardsrefertocontrolnames.WegivealistofthestartingnamevaluesforvariouscontrolsinChapter18.Forexample,thenameofatextboxshouldbeginwith\txt";thenameofaformshouldbeginwith\frm."ForBooleanvariables,werecommendstartingthevariablenamewith\Is"or\Do."Forexample,\IsDone"or\DoAnimation."Forconstants,werecommendwritingthenamesinalluppercase,suchas:\PI"or\INFINITY." Forprocedurenames,trynottomakethemtovague.Forexample,insteadof\Calc,"useamoredescriptivenamesuchas\CalcReturn."Forfunctionprocedures,youmayalsousenameswhichsignifythereturnedvalue.Forexample,\FindMinCost"or\GetUserInfo."Ingeneral,usenameswhichwillnotbeeasilyconfused.Thiswillhelpavoidcodingerrorsandeasedebugging. 3.2.3 Comments Commentingisagoodhabittohaveasacoder.Commentshelpclarifywhatyouhavedoneandwhyyouhavedoneit.Theywouldalsobenetanother

PAGE 71

programmerwhomaylookthroughyourcodelater.Keepyourcommentsuptodateasyoumakechangesorupdatestoyourcode. Youshouldhaveacommentatthebeginningofeachproceduretogiveanoverviewofwhatthesuborfunctionwilldo.Avoidabbreviationsinthesecom-mentsasyouwanttoensurethattheyareclear.Youshouldalsohavecommentsbeforeloopsorlogicalcheckstoexplaintheowofthecode. Usecommentstoorganizeyourcodeandmakeiteasiertoread.Donotoverdoitthoughasyoudonotwanttohaveunnecessarycommentswhichonlyaddlengthtoyourcode. 3.2.4 Eciency Themostimportantprogrammingissueiscodeeciency.Youshouldcon-stantlybetryingtoimproveyourcodebyreducingthecomplexityofthelogicortimerequired.Ifyouhaveseveralnestedloops,seeifyoucansimplifythisstructure;maybeaSelect,CasestructurewouldbebetterthanmultiplelevelsofIf,Then.Checkifyouarerepeatingsomeactionsunnecessarily.Areyouclearingvaluesbeforeoverwritingthem?Areyoureformattingarangeunnecessarily?Areyourepeatingacalculationthathaspreviouslybeencomputed?Asyouarewritingcommentstoexplainyourcode,checkifyoucouldimproveitrst. Anotherwaytoimprovecodeeciencyistoensurethatextramemoryisnotbeingused.Forexample,thereisnoneedtodeclareacountingvariable\i"asaDouble,itonlyrequiresthememoryofanIntegerdatatype.Ensurethatarraysarealsodimensionedfortheneededstoragespace.YoucanusetheReDimPreservestatementtodothis. Youshouldalsowriteyourcodetobedynamicforfutureextensionsorupdates.Forexample,forboundvariables,arraysizes,orrangevalues,youmaywanttousevariablesinsteadofvalues.Considerthefollowingexamples.

PAGE 73

Forspreadsheet-basedDSSapplications,youcanalsoimprovecodeeciencybybalancingwhatneedstobedoneincodewithwhatcanbedoneinthespread-sheet.Forexample,somecalculationscanbepreparedonaspreadsheetusingExcelfunctionsratherthancomputingthemwithafunctionprocedureorloopinthecode.Likewise,muchformattingcanbedoneinthespreadsheetinsteadofinthecode.Therewillbeatradeobetweenfunctionalityandfancinessoftheprogram;itismoreimportantthattheapplicationmodelperformscorrectly.Thebetterthecoding,thebetterthequalityandperformanceoftheapplication. Table3{5: Summary:ProgrammingPrinciples ConsistentStyle Useaconsistentstyleforformatting,organizing,andcommentingyourcode. Naming Usenamingstandardsforvariablesandprocedures.Namingstandardsalsoapplytocontrolnamesandspecicdatatypes,suchasBoolean. Comments Makecommentsclearthroughoutthecode.Describeprocedurefunctionalityandloopandlogicalow. Eciency Alwayslookforwaystoimproveyourcodingeciency.Avoidredundanciesandunnecessarycode.

PAGE 74

4.1 ApplicationOverview ThiscasestudyisaDSSapplicationofthewarehouselayoutproblem.Thewarehouselayoutproblemistosubdividetheoorareaofawarehouseintostorageareasforseveralproducttypessothatthetotalmaterialhandlingcostbetweenthestorageareasandthewarehousedocksisminimum. 4.1.1 ModelDenitionandAssumptions Forsimplicity,wediscretizethewarehouseoorareaintoAunitgrids,calledbays;hereArepresentsthetotalwarehousearea.Weareseekingtoallocatethesebaystovariousproductstypes.Wewillnowdescribethisproblemingreaterdetail.However,wewillpresentsomenotationsrstforthethreeentitiesintheproblem:thewarehousebays,docks,andproducttypes. Bays:WedecomposethewarehouseoorareaintoAbays,indexedbyk=1,2,,A. Docks:Thewarehousehasndocksindexedbyj=1,2,,n.Productsarebroughtintothewarehouseandtraveloutofthewarehousethroughthesedocks.Werepresentthedistancebetweeneachbayandeachdockasdkj. Producttypes:Thewarehouselayoutproblemasmproducttypesindexedbyi=1,2,,m.AproductirequiresanareaofAibaysinthewarehouse.Weassumethatthewarehousehasenoughoorareatostorealloftheproducttypes. InFigure4.1weconstructanexamplewithveproducttypesandtwodocks. 61

PAGE 75

Figure4{1: Anexamplewarehouselayout. EachproducttypeihasademandDi.Weassumethatthedemandforeachproducttypeiisuniformlydividedovertheentirestoragearearequiredbyfortheproducttype.SinceproducttypeiisstoredonAibays,thetotaldemandofproductiperstoragebayisDi=Ai. Eachproducttypealsointeractswiththedockswithadierentfrequency.Thefrequencyisdenedasthefractionofthedemandthatwilltravelinoroutofthewarehousethroughaparticulardock.WedenethesefrequenciesasFij.Thesumofthefrequenciesoveralldocksforeachproducttypeshouldbe1;thatis. Weareseekingtoassignproducttypestothewarehouseareasuchthattheproducttypeswiththehighestdemandperstoragebayaresituatedclosesttothedockswithwhichtheyhavethehighestfrequency.Toenforcethispolicyduringassignment,wedeneaweightWijtorepresenttheamountofaparticularproducttypeiperstoragebaythattravelstoandfromaparticulardockj.Theseweightsaredenedasfollows.

PAGE 76

Therefore,ifproducttypeiisstoredatbayk,thenthetotalmaterialhandlingcostwithdockjduetothisstorageisdkiWij.Wedenetheassignmentdecisionvariablesxkiasbinaryvariablestorepresentwhetherornotproducttypeiisassignedtobayk. Thewarehouselayoutproblemcannowbeformulatedasthefollowingintegerprogramming(IP)problem. minmXi=1nXj=1AXk=1xkidkjWij(4.5) Subjectto: (4.6) Thisobjectiveseekstominimizethematerialhandlingcostbetweentheassignedstorageareasandthewarehousedocksoverallproducttypes.Theconstraint 4.7 statesthatonlyAibaysareassignedforeachproducttypei.Constraint 4.8 statesthatonlyoneproducttypecanbeassignedperbay. ThisformulationisanIPproblemandcanbesolvedbyanIPalgorithm.(Formoredetails,seeFrancis[ 4 ])Inthisapplication,however,wewillconsideraspecialcaseoftheobjectivefunction 4.5 thatcanbesolvedveryecientlyusingagreedymethod.

PAGE 77

RecallthatWijrepresenttheamountofaparticularproducttypeiperstoragebaythattravelstoandfromaparticulardockj.WeassumethatthembynmatrixW=fWijgfactors;thatis,thereexistnumbersiandjsuchthat. WemaypointoutthatnoteverymatrixWfactors,onlysomedo.WhenamatrixWfactors,wecangiveanintuitiveexplanationtothefactorsiandj.Letidenotethetotaldemandofproducttypeiperstoragebayoveralldocks,andjdenotethefrequencywithwhicheachdockjisused. Wenowhavethefollowing. Thefactoringassumptionimpliesthateachproducttypewillhavethesamedockfrequencyforadockjasanyotherproducttype.Thisfrequency,formerlynotedasFij,isnownotedasFjorj.Forexample,iftherearetwodockswithfrequencies1=0.3and2=0.6,thenthefactoringassumptionimpliesthateachproducttypewillsend30%ofitsdemandperstorageareathroughdock1and60%throughdock2. Inthiscase,whenthematrixWfactors,wecanrestatetheobjectivefunctionofthewarehouselayoutproblemasfollows. minmXi=1AXk=1xkii(nXj=1dkjj)(4.14)

PAGE 78

Wecannowobservethatsinceidenotesthetotaldemandofproducttypeiperstoragebayoveralldocks,thegreaterthevalueofaiforanyproducttype,thegreateristheinteractionthisproductwillhavewiththedocks.Therefore,wewanttoensurethattheseproducttypeswiththehighestaivaluesareassignedtothebayswiththeminimumdistancetothedocks. Wealsoobservethatforanybayk,Pnj=1dkjjdenotestheaveragedistancetraveledperunitdemandforanyproducttypestoredatthatbay;thatis,withprobabilityjtheassignedproducttypewilltraveltodockjincurringthedistancedkjWenowdenethevaluektodenotetheweighteddistancesbetweeneachbaykandeachdockjasfollows. Itshouldnowbeintuitivelyclearthattominimizethetotalmaterialhandlingcost,producttypeswithhighpriorityweightvaluesshouldbeassignedtobayswithsmalldistanceweights.Thisintuitionsuggeststhefollowinggreedyalgorithmforthewarehouselayoutproblem. 1. Sorttheweighteddistanceskinascendingorderforeachbayk. 2. Sorttheproducttypeweightsiindescendingorderforeachproducttypei. 3. AssignthehighestweightedproducttypeitotherstAibaysfromthesortedweighteddistancelist. Thisalgorithmensuresthattheproducttypeswiththehighestweightareassignedtothebayswiththesmallestdistanceweights.Thus,theoveralldistancetraveledbyeachproducttypetoeachdockshouldbeminimized. Letusnowillustratethisalgorithmwithasmallnumericalexample.ConsiderawarehouseofareaA=100andtwodocks(seeFigure4.2).

PAGE 79

Figure4{2: Thewarehouseareaisdiscretizedintobayareasofvalue1. Weassumethereareveproducttypes.Theproducttypearearequirementsanddemandsareasfollows. ProductType:i 10 3002 15 2503 20 4004 25 5005 30 450 Assumingthatdockfrequenciesarethesameforallproducttypes,weusethefollowingfrequencyvaluesforthetwodocks. F1=35% F2=65% Withthesevalues,wecannowcalculatetheivaluesforeachproducttypeiandthekvaluesforeachbayk.Ifwesorttheproducttypesindescendingorderoftheirivaluesthebaysinascendingorderoftheirkvalues,applyingthegreedyalgorithmwouldyieldthefollowingbayassignments.

PAGE 80

Figure4{3: Thenalwarehouselayoutforveproductsandtwodocks. i Ai 300/10=30 2,5,7,9,12,15,18,19,23,28 10 3 400/20=20 30,32,34,36,38,39,40,43,44,46,48,49,50,54,57,59,62,64,67,68 20 4 500/25=20 73,76,78,79,80,83,85,87,88,89,92,93,95,97,101,104,107,109,111,114,115,116,118,119,122 25 5 450/25=18 125,127,129,131,135,137,138,139,142,143,145,147,148,152,156,157,159,160,163,164,167,168,169,173,178,179,182,184,187,189 30 2 250/15=16.67 191,193,194,197,198,201,204,205,206,208,210,214,215,217,218 15 NoticethatthetotalnumberofbaysassignedtoeachproducttypeisequaltotheproducttypeareasAi.SeeFigure25.3forthenallayoutforthisexample.

PAGE 81

Table4{1: Algorithm 1.Sorttheweighteddistancesinascendingorderforeachbay. 2.Sorttheproducttypeweightsindescendingorderforeachproducttype. 3.Assignthehighestweightedproducttypetotherstbaysfromthesortedweighteddistancelistuntiltheproducttypearearequirementissatised. 4.1.2 Input Usingthemodeldescribedintheabovesection,wecandenethefollowinginput. Weconsidertheareaofthewarehouseasthetotalnumberofbaysavailableforassignment.Thearearequiredperproducttypeisbasicallythenumberofbaystoassignperproducttype. Weuseoneuserformandtwoinputsheetsinthisapplicationtoreceivetheseinputvaluesfromtheuser.Wedonotplaceanyboundsontheseinputs.Wedogivedefaultvaluesforawarehouseareaof30,3producttypes,and2docks;howeverthesevaluesmaybechangedbytheuser. 4.1.3 Output Ourmainoutputisthewarehouselayout.Wepresentthisoutputtotheuserusingdierentcolorcellstorepresentthedierentproducttypes.Theproducttypecolorrepresentationissummarizedinalegendnexttothewarehousegrid.Thedocksarealsoshownintheirspeciedlocationsadjacenttothegrid.Wehave

PAGE 82

Figure4{4: Thewelcomesheet. severalnavigationalbuttonsaswellassomeresolveoptions;wediscusstheseinmoredetaillater. 4.2 Spreadsheets Weusefourspreadsheetsinthisapplication:awelcomesheet,twoinputsheets,andanoutputsheet.Inthewelcomesheetwedescribethewarehouselayoutproblemandgiveanoverviewofwhattheuserwillinputandwhatoutputtheywillseedisplayed(seeFigure4.4).Wehavesomeimagesofawarehouseanda\Start"button.The\Start"buttonisassignedtoourMainsubprocedurewhichwewilldiscusslater. Thenextspreadsheettheuserseesistherstoftwoinputsheets.Beforetheuserarrivestothissheet,theywillcompleteauserformwiththeareaofthewarehouse,numberofproducttypes,andnumberofdocks(wediscusstheforminmoredetailinthenextsection).Fromthisinformation,wecreateagridrepresentingthewarehouseareaontherstinputsheet.Therstinputsheetallowstheusertoplacethedocksadjacenttothewarehousegrid.Therearesomeeventproceduresassociatedwiththespreadsheetwhichallowstheusertosimply

PAGE 83

Figure4{5: Therstinputsheetfordockinformation. clickonthelocationofadockinorderforthedocknumberandformattingtoappear.Wewilldiscussthedetailsoftheseprocedureslater. Wealsocreateatableforthedockinformationwhichtheusermustcomplete.Basedontheplacementofthedocksadjacenttothewarehousegrid,theusermustenterthenumberofthebayeachdockisadjacentto.Thiswillhelpusdeterminethedistancefromeachbaytoeachdock.Theusermustalsoenterthedockfrequenciesatthispoint.Thefrequenciesshouldsumto1.InFigure4.5,weshowanexamplewithtwodockslocatednearbays1and19withfrequencies20%and80%respectively. Theuserthenpressesthe\Continue"buttontonavigatetothenextinputsheet.Thesecondinputsheetisforinformationontheproducttypes(seeFigure4.6).Wecreateatablewheretheuserentersthearearequirement(numberofbays)anddemandforeachproducttype.Thesumofthenumberofbaysrequiredoverallproducttypesmustbelessthanorequaltotheavailablenumberofbaysinthewarehouse. InFigure4.6,wecontinuetheexampleshowninFigure4.5.Herewehavethreeproducttypeswitharearequirementsof2,4,and7baysrespectively.Theproducttypedemandsare300,500,and600respectively.Wemaynotehere

PAGE 84

Figure4{6: Thesecondinputsheetforproductinformation. thattheweightsforeachproducttypewouldbe150,125,and85.7respectively(i=Di=Ai).Therefore,producttype1hasthehighestpriority,thenproducttype2,thenproducttype3. Theuserthenpressesthe\Solve"buttontorunthemaincalculationproce-duresandperformthegreedyalgorithm.Theoutputsheetwillthenappear(seeFigure4.7).Theoutputsheetshowsthenallayoutforalloftheproducttypesinthewarehousearea.Wecoloreachproducttypedierentlyandgivealegendforthisrepresentation.InFigure4.7,wecanobservethatproducttype1(whichhadthehighestpriority)wasassignedtothebaysclosestotheseconddock(whichhadthehighestfrequency). Therearesomeresolveoptionsandnavigationalbuttonsherealso.Wewilldiscusstheseinmoredetailinalatersection. Table4{2: Summary:Spreadsheets Welcomesheet Problemdescriptionand\Start"button. Firstinputsheet Dockinputtable,initiallayoutfordockplacements,\End"and\Continue"buttons. Secondinputsheet Producttypeinputtable,\End"and\Solve"buttons. Outputsheet Finallayoutwithproducttypebayassignment,legendforproducttypecolors,resolvelayoutarea,\End,"\Resolve,"\ViewProductInfo"and\ViewDockInfo"buttons.

PAGE 85

Figure4{7: Theoutputsheetwithitsnavigationalbuttonsandresolveoptions. 4.3 UserInterface Forthisapplication,wehaveseveralnavigationalbuttons,somefunctionalbuttons,oneuserform,andtwoinputsheets.Webegintheapplicationbydis-playingtheuserformafterthe\Start"buttonispressedfromthewelcomesheet.Theformpromptstheuserforthenumberofproducttypes,numberofdocks,andareaofthewarehouse.Weasktheusertodenetheareaofthewarehousebyitsdimensions;thatis,bythenumberofrowsandcolumnsneededtoconstructthewarehouseareaassumingeachbayisrepresentedbyonerowbyonecolumn.TheformisshowninFigure4.8. Weusetwoframestogroupsimilartextboxestogether.Therstframecontainsthetextboxesforthenumberofproducttypesandnumberofdocks.Thesecondframecontainsthewarehousedimensionsvalues:numberofrowsandnumberofcolumns.Wehaveentereddefaultvaluesforthreeproducttypes,twodocks,andawarehouseareaof30bays.The\Cancel"buttonsimplyunloadstheform,leavingtheuseratthewelcomesheet.The\OK"buttonperformssomeerrorcheckingandassignstheinputvaluestocorrespondingvariables.Itthentakestheusertotherstinputsheet.

PAGE 86

Figure4{8: Theuserformasksfortherstinputvalues. Therstinputsheet,discussedintheprevioussection,hastwobuttons:\End"and\Continue"(seeFigure4.5).The\End"buttonclosesthesheetandreturnstheusertothewelcomesheet.The\Continue"buttonperformssomeerrorchecking,recordsthedockinformationtheuserhasenteredinthetable,closesthesheet,andtakestheusertothenextinputsheet. Onthesecondinputsheet,alsodiscussedintheprevioussection,therearealsotwobuttons.\End"and\Solve"(seeFigure4.6).The\End"buttonhasthesamefunctionalityhereasintheprevioussheet.The\Solve"buttonperformssomeerrorchecking,recordstheproducttypeinformationenteredbytheuserinthetable,andthencallsthemainprocedurestoperformthegreedyalgorithm.Itthenclosesthesheetandtakestheusertotheoutputsheet. Ontheoutputsheethasafewmorebuttons(seeFigure4.7).The\End"buttonisagainthesameastheothers.Therearethentwonavigationalbuttonswhichallowtheusertorevisittheinputsheets:\ViewProductInfo"and\ViewDockInfo."Thesebuttonssimplyclosetheoutputsheetandtaketheusertothe

PAGE 87

respectiveinputsheet.Whentheuserrevisitsaninputsheet,anewbuttonwillappearto\GoBack"totheoutputsheet.This\GoBack"buttonreplacesthe\Continue"and\Solve"buttons. Thereisalsoa\Resolve"buttonontheoutputsheet.Thisbuttonisusedtorecordanychangestheuserhasmadetoeitheroftheinputsheetsorthe\ResolveLayout"andthenresolvestheproblem.Wewilldiscussthemeaningofthe\ResolveLayout"andotherresolvefunctionsinalatersection. Table4{3: Summary:UserInterface InputForm Numberofproducts,numberofdocks,warehousedi-mensionsinnumberofrowsandcolumns. Firstinputsheet Docklocationintermsofadjacentbay,dockfrequen-cies. Secondinputsheet Requiredareaanddemandperproducttype. Navigationalbuttons \End,"\ViewProductInfo,"\ViewDockInfo,"\GoBack." Functionalbuttons \Start,"\Continue,"\Solve,"\Resolve." 4.4 Procedures Wewillnowoutlinetheproceduresforthisapplication.WebeginwiththeMainsubprocedureandvariabledenitions(seeFigure4.8).Wedeneseveralvariablesaspublicvariablessincetheywillbeusedinmultipleprocedures.Wehavevariablestorepresenttheproblemdimensions,suchasthenumberofproducttypesandnumberofdocks,somecountingvariablesforloops,severalarraystobeusedinthepreparationcalculations,andseveralvariablestobeusedinthealgorithm. TheMainprocedurebeginsbycallingtheClearPreviousprocedure(seeFigure4.9).TheClearPreviousprocedureclearsthecellvaluesandformattingofallofthesheetsintheapplication.Italsoensuresthattheoriginalbuttonsontheinputsheetsarevisibleandhidesthe\GoBack"buttons.Lastly,itinitializessomevariables.

PAGE 88

Figure4{9: TheMainprocedureandpublicvariabledeclarations.

PAGE 89

Figure4{10: TheClearPreviousprocedureclearsvaluesandformattingonallsheets;italsoinitializessomevariables. TheMainprocedurethenshowstheinputform(refertoFigure4.8).ThemaincodeassociatedwiththisformisintheClickeventprocedureofthe\OK"button(seeFigure4.10).Thisprocedureperformssomeerrorcheckingtoensurethatalloftheinputvalueshavebeengiven.Itthenassignstheinputvaluestotheircorrespondingvariables.Atthispointwenowknowthenumberofproducttypes,numberofdocks,andareaofthewarehouse.Werefertotheareaofthewarehousebythenumberofbays,whichisequaltothenumberofrowsmultipliedbythenumberofcolumnsprovidedbytheuser. TheMainprocedurecontinuesbyresettingsomearraysusedfortheresolveoptionsandthenassigningcolorstotheproducttypes.Then,theNumberingBayprocedureiscalled.ThisprocedurenumbersthewarehousegridandcomputestheXandYcoordinatesofeachbay(seeFigures4.12and4.13).

PAGE 90

Figure4{11: ThecmdOK Clickprocedureassignstheinputvaluestotheircorre-spondingvariables.

PAGE 91

Theprocedurebeginsbycreatingtheinitialwarehouselayout.Wemovefromrow1tothenumberofrowsandfromcolumn1tothenumberofcolumnsandthenbacktocolumn1andsoforth,labelingeachbayinthewarehousearea.WesettheXandYcoordinatesequaltotherowandcolumnvalueofeachcreatedbay.Thesecoordinatesarestoredintwoarrays. TheNumberingBayprocedurecontinuesbypreparingtheinputtablesforthedocksandproducttypesbasedontheinputprovidedbytheuserintheinputform.Thedocktableiscreatedontherstinputsheet.Theproducttypetableiscreatedonthesecondinputsheet.Thetotalnumberofbayareasisalsorecordedonthesecondinputsheet.ReturningtotheMainprocedure,wenowsimplytaketheusertotherstinputsheet. Onthissheettheuserisabletoplacethedocksaroundthewarehouseareasimplybyclickingonacell.Toenablethisfeature,wehavewrittenaneventprocedurefortheSelectionChangeeventoftheworksheet(seeFigure4.14).Werstcheckiftheactivecellisinarangewherethedocksareallowedtobeplaced.Thisrangecanbedenedastheintersectionofthewarehouseareawithanadditionaloneunitcircumferenceandallothercells.WedenethisintersectionbyusingalogicalcheckandtwoUnionworksheetfunctions. Onceweensurethattheuserhasclickedacellintheallowabledocklocationarea,wecheckthattheyhavenotalreadyplacedallofthedocks.Ifthisconditionisfalse,thenwelabelthecellwith\D"andthecurrentdocknumber. ThenextprocedureistheDockInfoprocedure(seeFigure4.15).Thisproce-dureisassignedtothe\Continue"buttonontherstinputsheet.Theprocedurebeginswithtwoerrorchecks:toensurethatthedocktablehasbeencompletelylledandtocheckthatthedockfrequenciessumtozero.WethendeterminetheXandYcoordinatesforeachdock.

PAGE 92

Figure4{12: ThebeginningoftheNumberingBayprocedure.

PAGE 93

Figure4{13: TheendoftheNumberingBayprocedure. Knowingthatthedocksareplacedadjacenttothewarehouseareaandgiventhebaynumberstowhicheachdockisadjacent,wecandeterminethedockcoordinatesbydeterminingwhichbordertheadjacentbayison.Iftheadjacentbayisonthetoporbottomborderofthewarehousearea(thatis,ontherstorlastrow),thentheXcoordinateshouldbezerooronemorethanthenumberofrows,respectively.TheYcoordinatewillbethesameastheYcoordinateoftheadjacentbay.Iftheadjacentbayisontheleftorrightborderofthewarehousearea(thatis,ontherstorlastcolumn),thentheYcoordinatewillbezerooronemorethanthenumberofcolumns,respectively.TheXcoordinatewillbethesameasthatoftheadjacentbay. Wealsorecordthedockfrequenciesintoanarray.Finally,wetaketheusertothesecondinputsheet.

PAGE 94

Figure4{14: TheSelectionChangeeventprocedureenablestheusertoclickonthesheettoplacethedocks. Thenextprocedureperformsthemaincalculationsandcallstheprocedureswhichexecutethealgorithm.ThisistheFinalStepsprocedure(seeFigure4.16).Thisprocedureisassignedtothe\Solve"buttononthesecondinputsheet.Theprocedurebeginswithtwoerrorcheckstoensurethattheproducttypetablehasbeencompletelylledandthatthesumoftherequiredbaysislessthanorequaltothetotalnumberofbaysinthewarehouse. Next,theComputeFprocedureiscalled(seeFigure4.17).Thisprocedurecomputestheweighteddistancesusingtherecordeddockfrequencies.Thesedistanceweightsareequivalenttothe?kvaluesdescribedinthemodel.Wedenek=Pnj=1dkjFjandsocomputethesevaluesusingaloopovereachbaywiththeirrespectiveXandYcoordinatesandasubloopovereachdockwiththeirrespectivefrequencyvaluesandXandYcoordinates.

PAGE 95

Figure4{15: TheDockInfoprocedurerecordsthedockinformation.

PAGE 96

Figure4{16: TheFinalStepsprocedureperformsthemaincalculationsandcalltheprocedureswhichexecutethealgorithm.

PAGE 97

Figure4{17: TheComputeFprocedurecomputestheweighteddistancesbasedonthedockfrequencies. ThenextprocedurecalledfromtheFinalStepsprocedureistheSortItemsprocedure(seeFigure4.18).Thisistherststepofthealgorithm.Thisprocedurewillcalculatetheproducttypeweightsusethesevaluestosorttheproducttypesindescendingorderofimportance.Theproducttypeweightsareequivalenttotheaivaluesdescribedinthemodel.Wedenei=Di=Aiandsocomputethesevaluesusingtherecordeddemandandareavalues;theweightsarestoredinaRatioarray. WethensorttheproducttypesaccordingtotheseRatiovalues.WeperformthesortonanarraycalledItemOrder.WeinitializethisarraysuchthateachproducttypeiasanItemOrdervaluei.WethensearchforthelargestRatiovalueandmovethisproducttypetothefrontofthelist;thatis,weexchangeitsItemOrdervaluewiththeproducttypewhichhasItemOrdervalueequalto1.Wecontinuethisprocessbutexamineonelessvalueeachtime.Eventually,theItemOrderarraywillsignifythesortedorderoftheproducttypes. ThenextprocedurecalledistheSortBaysprocedure(seeFigure4.19).Thisisthesecondstepofthealgorithm.ThisproceduresortsthebaysindescendingorderoftheirdistanceweightscomputedintheComputeFprocedure.Thesortingisdoneinthesamemannerinwhichtheproducttypesweresorted.Thistime

PAGE 98

Figure4{18: TheSortItemsprocedurecalculatestheproducttypeweightsandsortsthem.

PAGE 99

Figure4{19: TheSortBaysalgorithmsortsthebaysinascendingorderoftheirdistanceweights. however,wesearchfortheminimumweightvalueineachpass.WeuseanarraycalledBayOrdertostorethebayorder. ThenalprocedurecalledfromtheFinalStepsprocedureistheAssignprocedure(seeFigure4.20).Thisisthelaststepofthealgorithm.Thisprocedurecompletestheassignmentofbaystoeachproducttypeandcreatesthenallayout. Theprocedurebeginswithsomeformattingforthenallayout.Thereisthenasmallsectionofcodewhichisrelatedtotheresolveoptions;wewilldiscussthisinmoredetailinthenextsection.Theassignmentloopthenbegins.WeloopthroughthesortedlistofproducttypesinthedescendingorderoftheirweightvaluesfoundintheSortItemsprocedure.Wethenassignbaystoeachproductby

PAGE 100

loopingthroughthelistofsortedbaysuntilthearearequirementfortheproducttypehasbeenmet.Toreectthatanassignmenthasbeenmade,weformattheassignedbaywiththecoloroftheproducttype.Whentheassignmentisnishedfortheproducttype,wealsoupdateourlegendwiththeproducttype'scolorandindex. Thelastpartofthisproceduresimplyformatstheresultinglayoutandcreatesa\ResolveLayout"whichwediscussinthenextsection.WenowreturntotheFinalStepsprocedurewhichtakestheusertotheoutputsheet. Theonlyremainingproceduresareforthenavigationalbuttons(see4.22).Theseincludethe\End"button,\ViewProductInfo"button,\ViewDockInfo"button,and\GoBack"buttons.Eachproceduresimplyhidesandshowstheappropriatesheets. 4.5 ResolveOptions TherearetwomainresolveoptionsforthisDSS.Therstoptionallowstheusertorevisittheinputsheetsandchangepreviouslyenteredvalues.Forthisoption,theuserwouldusethenavigationalbuttonsontheoutputsheettoreturntoeitheroftheinputsheets.Suppose,forexample,thattheuserreturnstotherstinputsheet(seeFigure4.23).Heretheymaychangethelocationofthedocksorthefrequenciesofthedocks.InFigure4.23,wehavechangedthefrequenciesfrom20%and80%to50%and50%respectively.Theusercanthenpressthe\GoBack"buttontoreturntotheoutputsheet. Atthispoint,theusercanpressthe\Resolve"buttontoseethenewlayoutwiththechangeddockinformation,ortheycanchoosetheothernavigationalbuttontorevisitthesecondinputsheet.Letussupposetheuseralsorevisitsthesecondinputsheettomodifytheproducttypeinformation.InFigure4.24,wehavechangedthearearequirementsfortheproducttypesfrom2,4,and7to4,10,and

PAGE 101

Figure4{20: ThebeginningoftheAssignprocedure.

PAGE 102

Figure4{21: TheendoftheAssignprocedure. Figure4{22: Thenavigationalprocedures.

PAGE 103

Table4{4: Summary:Procedures Main Initializesapplication,displaysinputform,callsClearPreviousandNumberingBays,takesusertorstinputsheet. ClearPrevious Clearsvaluesandformattingonallsheet,initializesvariables. cmdOK Click Doessomeerrorchecking,assignsinputvaluestocorre-spondingvariables. NumberingBays Createsinitiallayout,determinesXandYcoordinatesforallbays,createsdockandproducttypetables. Worksheet Selec-tionChange Allowsusertoplacedocksadjacenttowarehousearea,keepstrackofnumberofdocksadded. DockInfo Doessomeerrorchecking,determinesXandYcoor-dinatesforthedocksbasedonadjacentbays,recordsdockfrequencies. FinalSteps Doessomeerrorchecking,recordsproducttypeareasanddemands,callsComputeF,SortItems,SortBays,andAssign,takesusertooutputsheet. ComputeF Computesbaydistanceweightsbasedondockfrequen-ciesandbayXandYcoordinates. SortItems Computesproducttypeweightsbasedontheratiobe-tweentheirdemandsandareas,sortstheproducttypesindescendingorderoftheseweights. SortBays Sortsthebaysinascendingorderoftheirdistanceweights. Assign Createsnallayoutbyassigningproductstobaysintheirsortedordersuntilallproducttypearearequire-mentsaresatised. Figure4{23: Therstinputsheetisrevisitedandsomeofthedockinformationischanged.

PAGE 104

Figure4{24: Thesecondinputsheetisrevisitedandtheproducttypeinformationischanged. Figure4{25: Thenewlayoutisdisplayedafterpressingthe\Resolve"button. 10respectively.Wehavealsochangedthedemandvaluesfrom300,500,and600to500,600,and800respectively.Theusercanagainusethe\GoBack"buttontoreturntotheoutputsheet. Iftheusernowpressesthe\Resolve"button,themainprocedureswillbererunandanewlayoutwillbedisplayed.InFigure4.25,weshowtheresultoftheresolvedlayoutaftermakingtheabovechangestothedockandproducttypeinformation.

PAGE 105

Figure4{26: Theresolveoptionsallowtheusertospecifyaparticularproduct'slayoutontheResolveLayoutgrid. Thesecondresolveoptionallowstheusertoenforcebayassignmentsforanyoftheproducttypes.Todothis,theusershouldselecttheproducttypetheywishtoenforcefromthelegend.Theycanthenclickonthedesiredbayassignmentsinthe\ResolveLayout."Forexample,supposetheuserwantstoenforcethebayassignmentsforallofthebaysrequiredforproducttype2.Theuserwouldclickonthe\P2"cellinthelegendandthenclickonthedesiredassignmentbaysinthe\ResolveLayout"(seeFigure4.26). Theusercanthenpressthe\Resolve"buttontoseetheupdatedlayoutwiththeseenforcedassignments.InFigure4.27,weshowthemodiedlayoutafterenforcingthebayassignmentsforproducttype2.Theotherassignmentsarenowmadegiventhatproducttype2willbeassignedtothespeciedbays. Notalloftherequiredbaysforanyproducttypeneedtobeenforced.Forexample,insteadofenforcingallfouroftherequiredbaysforproduct2,theusercouldhaveonlyenforcedtheassignmentoftwoofthebays.Multipleproducttypescanalsobeenforcedatonce.InFigure4.28,weenforcethesamebayassignmentsforproducttype2aswellassixofthesevenrequiredbaysforproducttype3.

PAGE 106

Figure4{27: Thelayouthasbeenresolvedwiththeuser'sspecicationsenforced. Figure4{28: Bayassignmentsformultipleproducttypescanbeenforced.

PAGE 107

Figure4{29: Thenallayoutismodiedtohonortheenforcedbayassignments. TheresultinglayoutisshowninFigure4.29afterthe\Resolve"buttonhasbeenpressed. Toallowtheusertoenforcebayassignmentswiththisinterface,wehavewrittenaSelectChangeeventprocedureassociatedwiththeoutputsheet(seeFigure4.30).ThisprocedureissimilartotheSelectChangeeventprocedureassociatedwiththerstinputsheetinthatwehavetorstcheckthelocationoftheselectedcellusingUnionfunctions. Werstcheckiftheuserhasclickedinthelegendarea.Ifso,thenwerecordwhichproducttypehasbeenselected.Onceaproducttypeisselected,theusermayclickinthe\ResolveLayout"areatospecifytheenforcedassignments.Wecheckthatthenextcellclickedisindeedinthislayoutareaandreformattheselectedcellwiththeproducttype'scolor. Wealsorecordthattheselectedbayhasbeenxed;thisisrecordedinaBooleanarraycalledFixedBay.Wemustalsoensurethattheuserdoesnotspecifymorebayassignmentsthantherequirednumberofbaysfortheselectedproduct

PAGE 108

type.WedothisbykeepingtrackofthenumberofbayswhichhavebeenassignedfortheselectedproducttypeinaFixedProdarray. WecannowdiscusstheResolveprocedurewhichre-performsthecalculationsandre-runsthealgorithmforeitherofthesetworesolveoptions(seeFigure4.31).Sincewehavestoredwhichbayassignmentsarexed,wewillskipthosebayswhenwereachtheAssignprocedure.Therefore,webegintheprocedurebycopyingthe\ResolveLayout"tocapturetheassignmentsmadebytheuser. WethencleartheoutputsheetandrecalltheDockInfoandFinalStepsprocedures.Theseprocedureswillre-recordtheinformationfromthedocktableandproducttypetableontheinputsheetstocaptureanychangesmadebytheusertotheinputvalues.Theassignmentalgorithmwillthenbeexecuted,thistimeignoringthebayswhichhavealreadybeenxed.Theresultinglayoutwillreecttheuser'senforcedassignments.WethenresettheFixedProdandFixedBayarrays. Table4{5: Summary:ResolveOptions Firstresolveoption Useviewbuttonstoreturntoinputsheetsandmodifyvalues;thenpress\Resolve"buttontoseenewlayout. Secondresolveop-tion Enforcesomeproducttypebayassignmentsbyclick-ingonaproducttypefromthelegendandspecifyingenforcedassignmentson\ResolveLayout";thenpress\Resolve"buttontoseenewlayout. Worksheet Selec-tionChange Eventproceduretoallowusertomakeassignmenten-forcements;itrecordswhichbaysarexedandhowmanybayshavebeenxedforeachproducttype. Resolve Copiesthe\ResolveLayout"andrecallstheDockInfoandFinalStepsprocedures;reassignsbaysignoringthosethatwerexedbytheuser.

PAGE 109

Figure4{30: TheSelectChangeeventprocedureallowstheusertoenforceparticu-larbayassignmentsforselectedproducttypes.

PAGE 110

Figure4{31: TheResolveprocedurerecordschangesmadetoinputvaluesandhonorsenforcedbayassignments.

PAGE 111

5.1 ApplicationOverview ThiscasestudyisaDSSapplicationofthereliabilityanalysisproblem.Thereliabilityanalysisproblemestimatesthesystemfailuretimeofasystemofmachines. 5.1.1 ModelDenitionandAssumptions Inthisapplication,weconsideraseriesparallelsystemofmachinesinwhichtherearethreemachinetypeseachworkinginparallelandconnectedserially(seeFigure5.1). Todeterminefailure,weconsidereachmachinetypetohaveakoutofnsystem.Giventherearenmachinesinamachinetype,anytimekofthesenmachinesfail,theentiremachinetypefails.Wethenconsidertheentiresystemofallmachinetypestoalsobeakoutofnsystem.Inthisapplication,weassumen=3machinetypesandk=1.Thatis,anytimeonemachinetypefails,theentiresystemfails. WeusetheWeibulldistributiontoestimatethefailuretimesofthemachinesineachmachinetype.Weibullrandomvariablesarethemostcommonrandomvariablesusedtomodelfailuretimesofmachines(Formoredetails,seeWinston[ 5 ]).ThecumulativedistributionfunctionoftheWeibulldistributionisasfollows. 98

PAGE 112

Figure5{1: Anexampleofaparallelserialsystem. Thus,theinversefunctionfortheWeibulldistributionis. WherepistheprobabilitythatXisintheWeibulldistribution,andXistheWeibullrandomvariable.WewillusethisfunctiontogeneraterandomWeibullvaluesforthesimulation.However,werstneedtondtheparametersand.WeoptimizetheWeibullparametersforeachmachinetypebasedontheirgivenmeansandstandarddeviationsoftimetofailure.WesimulatethesystemfailuretimeusingtheseWeibullparameters. Forthisapplication,weanalyzethedistributionofthesystemfailuretime.Wealsotrytoidentifywhichmachinetypecausesthemostsystemfailure.Wewishtocorrectthebottleneckmachinetypeandimprovetheoverallperformanceofthesystem.Theoutputsheetforthisanalysisincludesahistogramofthesystemfailuretimewiththemeansystemfailuretimeaswellasahistogramofthefrequencywithwhichaparticularmachinetypecausedthesystemfailure. 5.1.2 Input Usingthemodeldescribedintheabovesection,wecandenethefollowinginput.

PAGE 113

Weuseoneuserformtoreceivetherstfourinputvaluesfromtheuser.WealsouseoneInputBoxtorecordthenumberofrunstoperform.Wekeeptheinputvaluesonatableinthesimulationsheettoenabletheusertomodifythematanytime. 5.1.3 Output Ouroutputcanbedenedasfollows. Theoutputisshownontheoutputsheet.Therearealsosomeimportantresolveoptionswhichwediscussinalatersection. 5.2 Spreadsheets Weusesixspreadsheetsinthisapplication:thewelcomesheet,acalculationsheetforoptimizingtheWeibullparameters,ahiddencalculationsheetforprepar-ingthesimulationdata,asimulationsheet,anothercalculationsheetforrecordingtheresultsofthesimulationruns,andtheoutputsheet.Thewelcomesheetgivesadescriptionoftheapplicationandhasa\Start"buttonassignedtotheMainprocedure(seeFigure5.2).

PAGE 114

Figure5{2: Thewelcomesheet. TherstcalculationsheetisusedtooptimizetheWeibullparametersforeachmachinetype(seeFigure5.3).TheoptimizationisperformedusingtheSolver;therefore,thesheetisorganizedwithrangesforthedecisionvariables,constraints,andobjectivefunction.Theinputcellsforthisoptimizationarethemeanandstandarddeviationfortimetofailureforeachmachinetype.Eachmachinetype'sWeibullparametersareoptimizedoneatatime. ThedecisionvariablesaretheWeibullparametersalphaandbeta.Bothofthesehavetheupperandlowerboundsshownadjacenttotheircells.Theseboundsaretheonlyconstraints.Wethencalculateameanandstandarddeviationtimetofailureusingthesealphaandbetatondthesquareerrorcomparedtotheuser'sinputforthesevalues.Theobjectivefunctionisthereforetominimizethesumofthesesquareerrors. Therearetwobuttonsonthissheet:\End"and\Back."The\End"buttonreturnstheusertothewelcomesheetandthe\Back"buttonisusedtoreturntotheoutputsheetoncetheapplicationiscompleted.

PAGE 115

Figure5{3: ThecalculationsheetforoptimizingtheWeibullparameters. Wethenuseahiddencalculationsheettocreateatimelineoffailuretimesforeachrunofthesimulation(seeFigure5.4).Thetimetofailureforeachmachineonallmachinetypesisrecorded.Themachinenumberandmachinetypewillbeusedintheanimation. Thenextsheettheuserseesisthesimulationsheet.Thesimulationsheetcontainsaninputtableandthesimulationanimationlayout(seeFigure5.5).Theinputtablesummarizesalloftheinputvaluesgivenbytheuserinaninitialinputform.ItalsoreportstheWeibullparametersoptimizedintheprevioussheet.Theusercanmodifythesevaluesatanytimeandtheywillberecordedbeforethesimulationisrun. Theanimationlayoutshowsallofthemachinesforeachmachinetype.Asthesimulationisrun,afailedmachinewillchangetoredintheanimation.Ifaparticularmachinetypereachesitsrespectiveknumberoffailedmachines,thenasystemfailureoccurs.Theword\Failed"appearsabovethemachinetypewhich

PAGE 116

Figure5{4: Thehiddencalculationsheetforthesimulationdata. causedthesystemfailure.InFigure5.4,machinetypeAhascausedthesystemfailuresincesixofitsmachineshavefailed;asyoucanseeintheinputtable,sixofthetenmachinesarerequiredtocauseafailureformachinetypeA. Therearetwobuttonsplusahiddenbuttononthissheet.The\End"buttonbringstheusertothewelcomesheetandthe\StartSimulation"buttoncallstheprocedurewhichbeginsthesimulation.Thehiddenbuttonsisthe\ViewAnalysis"buttonwhichismadevisibleoncethesimulationhasbeencompleted.Ittakestheusertotheoutputsheet. Thenextsheetisanothercalculationsheetwhichcontainstheresultsofeachrunofthesimulation(seeFigure5.6).Thetimeuntilthesystemfailedisrecordedalongwiththemachinetypewhichcausedthesystemfailure.Thetableforbinvaluesisusedlaterincreatingthehistogramforthemachinetypes. Therearetwobuttonsonthissheet:\End"and\Back."Theyhavethesamefunctionalitiesasthebuttonsontherstcalculationsheet. Thenalsheetistheoutputsheet(seeFigures5.7and5.8).Thissheetdisplaysthemeansystemfailuretime,ahistogramofthesystemtimefailuresfrom

PAGE 117

Figure5{5: Thesimulationsheetwiththeanimationlayoutandinputtable. Figure5{6: Thethirdcalculationsheetfortheresultsofthesimulationruns.

PAGE 118

thesimulation,andahistogramofthefrequencieswithwhicheachmachinetypecausedthesystemfailure.Thisisthemostimportantinformationfortheusertoanalyzetodeterminehowthesystemisbehaving. Thereareseveralbuttonsonthissheet.The\End"buttonreturnstheusertothewelcomesheet.The\ViewDetails"buttontakestheusertothethirdcalculationsheetinwhichtheresultsofthesimulationrunsarerecorded.The\ViewWeibull"buttontakestheusertotherstcalculationsheetinwhichtheWeibullparameterswereoptimized.Thelasttwobuttons,\ReturntoSimulation"and\Resolve"areusedwiththeresolveoptionswhichwediscussinalatersection. Table5{1: Summary:Spreadsheets Welcomesheet Applicationdescriptionand\Start"button. Firstcalculationsheet OptimizestheWeibullparametersusingtheSolver.\End"and\Back"buttons. Hiddencalculationsheet Createsatimelineoffailuretimesforthesimulationanimation. Simulationsheet Inputtableandanimationlayout.\End,"\StartSimu-lation,"and\ViewAnalysis"buttons. Thirdcalculationsheet Resultsofallsimulationruns.\End"and\Back"but-tons. Outputsheet Meansystemfailuretime,histogramforsystemfail-uretimes,andhistogramformachinetypefailures.\End,"\ViewDetails,"\ViewWeibull,"\ReturntoSimulation"and\Resolve"buttons. 5.3 UserInterface Forthisapplication,wehaveoneuserform,oneinputtable,severalnaviga-tionalbuttons,andafewfunctionalbuttons.WealsouseoneInputBoxandoneMessageBox.Theuserformcontainsinputforeachmachinetype(seeFigure5.9).Itpromptstheuserforthenumberofmachines,numberofmachineswhichcausefailure,costpermachine,andthemeanandstandarddeviationoftimetofailureforthemachinetype.Weusetwoframestogroupsimilartextboxes.The\OK"buttonhasanassociatedClickprocedurewhichwedescribelater.

PAGE 119

Figure5{7: Thetophalfoftheoutputsheet.

PAGE 120

Figure5{8: Thebottomhalfoftheoutputsheet. Thisformisshowntotheuserthreetimesinordertoreceivetheinputforeachmachinetype.Wehaveadynamiclabelatthetopofthesheetwhichshowsthenameofthecurrentmachinetypetheinputwillbeassociatedwith.Noticethatweusealabelandnotatextboxforthisvaluesincetheusershouldnevermodifyitsvalue. Theinputtableonthesimulationsheethasbeendescribedintheprevioussection.ItsimplysummarizestheinputprovidedbytheuserintheuserformaswellastheoptimizedWeibullparameters.Theusercanchangethisinputbeforethesimulationisrun.Thenavigationalbuttonsandfunctionalbuttonswerealsodiscussedintheprevioussectionwitheachcorrespondingworksheet. TheInputBoxisusedtoprompttheuserforthenumberofrunsforwhichthesimulationshouldberun(seeFigure5.10).AtitleisgiventotheInputBoxaswellasadefaultvalueof25runs. TheMessageBoxisusedtoinformthereaderthattheWeibullparameteroptimizationhasbeencompleted(seeFigure5.11).ThecorrespondingcalculationsheetappearsbehindtheMessageBox.Wetaketheuserdirectlytothesimulationsheetnotallowingthemtopauseonthecalculationsheet.Theycanhowever

PAGE 121

Figure5{9: Theinputformwiththedynamiclabelvalueformachinetype\A." Figure5{10: TheInputBox.

PAGE 122

Figure5{11: TheMessageBox. revisittheWeibullparameteroptimizationcalculationsoncethesimulationiscomplete. Table5{2: Summary:UserInterface InputForm Receivestheinitialinputfromtheuser. Inputtableonsimu-lationsheet SummarizestheuserinputanddisplaystheoptimizedWeibullparametersforeachmachinetype. InputBox Promptstheuserforthenumberofrunsforthesimula-tion. MessageBox InformstheuserthattheWeibullparametershavebeenoptimized. NavigationalBut-tons \End,"\Back,"\ViewDetails,"\ViewWeibull,"\Re-turntoSimulation" FunctionalButtons \Start,"\StartSimulation,"\Resolve" 5.4 Procedures Wewillnowoutlinetheproceduresforthisapplication.WebeginwiththeMainsubprocedureandvariabledenitions(seeFigure5.12).Thevariablesshowndenedaspublicvariablesareusedinmultipleprocedures.Allothervariablesaredenedwithintheproceduretheyareused. TheMainprocedurebeginsbycallingtheClearPrevprocedure.TheClearPrevprocedureisusedtoinitializevariablesandclearpreviousworksheetvalues(seeFigure5.13).Theformattingfortheanimationlayoutisalsocleared. Next,theMainprocedureshowstheusertheinputform.Aloopisusedtodothissothattheinputformcanbeshownforeachmachinetype.Thedynamiclabel

PAGE 123

isadjustedbeforetheformisshowntodisplaythenameofeachmachinetype.Theformvaluesarerecordedintoarraysusingthesameindexintheloop.TheClickeventprocedureofthe\OK"commandbuttonontheinputformperformssomeerrorcheckingandthenassignstheinputvaluestothecorrespondingarrayvariables(seeFigure5.14). TheInputBoxforthenumberofrunsisthenshown.Someerrorcheckingisdonetocheckiftheuserpressedthe"Cancel"buttonontheInputBox. TheCalcWeibullprocedureisthencalled.TheCalcWeibullprocedureusestheSolvercommandstondtheoptimumWeibullparametersforeachmachinetype(seeFigure5.15).TheSolverispreparedbysettingtheobjectivefunctioncells,decisionvariablecells,andconstraints.Thenaloopisusedtochangetheinputcells,whichcontainthemeanandstandarddeviationoftimetofailure,foreachmachinetype.WithinthislooptheSolverisexecutedandthedecisionvariablecellvalues,thealphaandbetavalues,arerecordedforeachmachinetype. OncewereturntotheMainprocedure,theMessageBoxisthendisplayedtoinformtheuserthattheoptimalWeibullparametershavebeenfoundforeachmachinetype.ThePrepSimprocedureisthencalled.ThePrepSimprocedurererecordsthevaluesfromtheinputtableonthesimulationsheetandthenpreparestheanimationlayout(seeFigure5.16).Theinputvaluesarererecordedsothatatalatertime,iftheuserchangestheinputvalues,thesimulationandanimationwillbeupdated.ThisfeaturebecomesmorerelevantwhenthePrepSimprocedureiscalledfromthemainsimulationprocedure. TheMainprocedurethentakestheusertothesimulationsheet.Onthesimulationsheet,the\StartSimulation"buttonisusedtocalltheStartSimprocedure;thisisthemainsimulationprocedure.TheStartSimprocedurewillinitializetheanimation,createthesimulationdata,andperformthesimulationand

PAGE 124

Figure5{12: TheMainprocedureandvariabledeclarations.

PAGE 125

Figure5{13: TheClearPrevprocedure.

PAGE 126

Figure5{14: ThecmdOK Clickeventprocedure. animation(seeFigures5.17and5.18).TheStartSimprocedurebeginsbycallingthePrepSimprocedureasexplainedabove. Itthenbeginsaloop,forthenumberofrunsgivenbytheuser,inwhichdataiscreated,theanimationisperformed,andtheresultsofthesimulationrunarerecorded.Tocreatethesimulationdata,theCreateDataprocedureiscalled.TheCreateDataproceduregeneratesfailuretimesforeachmachineineachmachinetype(seeFigure5.19).ThesevaluesaregeneratedusingtheWeibullInvfunctionprocedure(seeFigure5.20).ThisfunctionprocedureusestheinversefunctiongiveninthemodeldescriptiontoproducearandomWeibullvariablevalueusingarandomprobabilityvaluefromtheRnd()function.Thefailuretimesarestoredinthehiddencalculationsheetwiththeircorrespondingmachinenumberandmachinetypename.Thevaluesarethensortedbasedonthefailuretimetoformthetimelinethatwillbeusedtoperformtheanimation.

PAGE 127

Figure5{15: TheCalcWeibullprocedure.

PAGE 128

Figure5{16: ThePrepSimprocedure.

PAGE 129

Figure5{17: ThebeginningoftheStartSimprocedure. TheStartSimprocedurecanthenbegintheanimationprocess.Itreadsthroughthetimevaluesinthetimelinejustcreatedandcheckswhichmachinenumberofwhichmachinetypehasjustfailed.Itthenchangestheformattingoftherespectivecellontheanimationlayouttoreecttotheuserthatamachinehasfailed.Italsocheckswhetherornotenoughmachinesofaparticularmachinetypehavefailedtocausesystemfailure.Ifso,thentheword\Failed"iswrittenovertheappropriatemachinetypecolumnintheanimationlayout.Thetimeofthesystemfailureisrecordedtothethirdcalculationsheetalongwiththemachinetypewhichcausedthefailure. TheanimationispausedaftereachsystemfailureusingtheApplication.Waitfunction.TheAnalysisPrepprocedureisthencalled. TheAnalysisPrepprocedureusestheresultsrecordedforeachrunonthethirdcalculationsheettocreatethehistogramsforthesystemfailureandmachinetypefailures(seeFigure5.20).Themeansystemfailuretimeisalsoreported.

PAGE 130

Figure5{18: TheendoftheStartSimprocedure.

PAGE 131

Figure5{19: TheCreateDataprocedureandWeibullInvfunction.

PAGE 132

Figure5{20: TheAnalysisPrepprocedure. TheStartSimprocedurethendisplaysthe"ViewAnalysis"buttononthesimulationsheet. Theonlyotherproceduresarethenavigationalprocedures(seeFigure5.21).Theseareforthe\End,"\Back,"\ViewAnalysis,"\ViewDetails,"\ViewWeibull,"and\ReturntoSimulation"buttons.

PAGE 133

Figure5{21: Thenavigationalprocedures. Table5{3: Summary:Procedures Main Initializesapplicationandretrievesinitialinputfromuser. ClearPrev Initializesvariables,clearspreviousvalues,andclearsanimationlayoutformatting. cmdOK Click Errorchecking,assignsinputvaluestocorrespondingarrayvariables. CalcWeibull EnvokesSolvertondoptimalWeibullparametersforeachmachinetype. StartSim Runsthesimulationandperformstheanimation. PrepSim Rerecordsinputtocaptureanychangesmadebytheuser,preparestheanimationlayout. CreateData Createsatimelineofmachinefailuresforallmachinesineachmachinetype. WeibullInv GeneratesWeibullrandomvariablevalues. PrepAnalysis Createshistogramsanddisplaysmeansystemfailuretime.NavigationalFornavigationalbuttons.

PAGE 134

Figure5{22: Anexampleoftherstresolveoption. 5.5 ResolveOptions TherearetwomainresolveoptionsforthisDSS.Therstoptionallowstheusertoreturntothesimulationsheetandmodifytheinputtabletorerunthesimulation.Thisisdonebypressingthe\ReturntoSimulation"buttonontheoutputsheet. Forexample,inFigure5.22,wehavereturnedtothesimulationsheetaftertheinitialsimulationwasrun.Wehavechangedthevaluesforthe\NumberofMachinestoCauseFailure"intheinputtablefrom6foreachmachinetypeto3,6,and9.Wethenpressthe\StartSimulation"buttonagaintorestartthesimulation.Wecanseethatthenewinputhasbeenrecordedastherstmachinetypehascausedthesystemfailurewhen3ofitsmachinesfailed.

PAGE 135

Figure5{23: Updatedanalysisfromtherstresolveoption. Wecanthenviewtheoutputsheetagainbypressingthe\ViewAnalysis"buttontoseethatindeedtherstmachinetypehascausedthemajorityofthesystemfailures(seeFigure5.23). Thesecondresolveoptionenablestheusertodeterminethesystembottleneckmachinetypeandaddonemachinetothismachinetypetoimprovesystemperformance;thatis,toincreasethemeansystemfailuretime.ThisoptionisperformedbyaResolveprocedurethatiscalledwhenthe\Resolve"buttonontheoutputsheetispressed(seeFigure5.24). TheResolveprocedurewillshowtheuseraresolveforminwhichtheycanchoosewhichmachinetypetoaddamachineto.However,beforedoingthat,wewanttosuggesttotheuserwhichmachinetypewouldbethebestchoice.Todothat,theResolveprocedurebeginsbyperformingsometrialstodeterminethisbestchoice.Foreachmachinetype,onemachineisaddedandthesimulationisrerun.Wethenrecordtheimprovementinthemeansystemfailuretime.Afterthatisdoneforallthreemachinetypes,wecomputearatiooftheseimprovementstothemachinecostforeachmachinetype.Thebestchoicewillbethemachinetypewith

PAGE 136

thehighestratio;thatis,itismostbenecialtoaddamachinewhichwillcausethemostimprovementinsystemfailuretimeattheleastcost. Theresolveformisthenshown(seeFigure5.25).Whentheformisinitialized,wedisplaytheimprovementsinsystemfailuretime,machinecosts,andratiovaluesforeachmachineonatableintheform(seeFigure5.26).Wealsoupdateadynamiclabeltodisplaythebestchoicethatwesuggesttotheuserandselectthecorrespondingoptionbutton. Aframewiththeseoptionbuttonsisusedtoretrievetheuser'sselectionofwhichmachinetypetheywanttoaddamachineto.The"OK"buttonwillrecordthisselectionandincreasethenumberofmachinesforthismachinetypebyone(seeFigure5.27).ItwillthenupdatetheinputtableandanimationlayoutandrecalltheStartSimprocedure. Theusercanthenobservethenewsimulationandanimation.Forexample,inFigure5.28,wehaveaddedonemachinetotherstmachinetype.Noticethatthismodicationisreectedintheinputtableandtheanimationlayout.Theusercanthenreviewtheanalysistoseeifthemeansystemtimehasindeedimproved.Thisprocessmayberepeatedasmanytimesastheuserdesires. Table5{4: Summary:ResolveOptions Firstresolveoption Userpressesthe\ReturntoSimulation"buttononoutputsheettoreturntosimulationsheet;theycanmodifytheinputtableandrerunthesimulation;theanalysisisupdatedwiththenewsimulationresults. Secondresolveop-tion Userpressesthe\Resolve"buttonontheoutputsheettoviewtheresolveform;theresolveformenablesthemtoaddonemachinetoonemachinetype;thebestchoiceissuggestedtothereaderafterrunningtrialsimulationsintheResolveprocedure.

PAGE 137

Figure5{24: TheResolveprocedure.

PAGE 138

Figure5{25: Theresolveform. Figure5{26: Theinitializationeventprocedurefortheresolveform.

PAGE 139

Figure5{27: TheClickeventprocedureforthe"OK"buttonontheresolveform.

PAGE 140

Figure5{28: Anexampleofthesecondresolveoption.

PAGE 141

6.1 TheImportanceofDSS Adecisionsupportsystem(DSS)isamodel-basedorknowledge-basedsystemintendedtosupportmanagerialdecisionmaking.ADSSisnotmeanttoreplaceadecisionmaker,buttoextendhis/herdecisionmakingcapabilities.Itusesdata,providesaclearuserinterface,andcanincorporatethedecisionmaker'sowninsights. ORgraduatesarefrequentlybeingemployedinpositionsthatrequiredevel-opingDSSwhicharegainingwidespreadpopularity.ImpartingDSSdevelopmentskills,whichcombineORskillswithITskills,willmakegraduateshighlysoughtafterinthemodernworkplace. 6.2 Spreadsheet-BasedDSS Inthepastfewyears,severalplatformshavebecomeavailablewhichallowstheintegrationofDSSdevelopmentandITskillsintotheORcurriculum.Forspreadsheet-basedDSS,werecommendusingExcelsinceitisthemostwidelyusedspreadsheetpackageamongmanagersandengineers,allowsdatastorageandmodelbuilding.Excelalsohasmanybuilt-inprogramaswellasmanyadd-onprogramsavailablethatwallowoptimizationandsimulationofvariousmodelsbuiltinExcel. Excelalsohasamacroprogramminglanguage,VisualBasicforApplications(VBA),whichallowsbuildingGUIsandmanipulatingExcelobjects.Thus,ExcelprovidesaplatformusingwhichfairlysophisticatedDSSapplicationscanbebuilt. 6.3 DevelopingaDSS WeproposevebasicstepsfordevelopingaDSS:i)ApplicationOverview:createalayoutoftheentireapplicationtounderstandtheowfromtheuserinput 128

PAGE 142

tothemodelcalculationstotheoutput,ii)Spreadsheets:determinehowmanyspreadsheetsyouwillneedtobesthandleinput,calculations,andoutput,iii)UserInterface:outlinewhatinterfaceyouwillneedtoreceiveinputfromtheuserandnavigatethemthroughtheapplication,iv)Procedures:outlinewhatsuband/orfunctionproceduresyouwillneedinyourcodetoreceivetheinput,performthecalculations,anddisplaytheoutput,v)DSSComponents:decidewhatresolveoptionstheusermayhave.Thesestepshavebeenourguidelinestodevelopingdecisionsupportsystems. 6.4 ConclusionandFutureDirection UsinggoodGUIdesignandprogrammingprinciplesalongwithaclearoutlineofthepurposeandimplementationoftheapplication,aDSScanbecomeaverypowerfultoolinaidingdecisionmaking.Wefocushereonspreadsheet-basedDSSapplicationssincespreadsheetsarecommonlyusedamongdecisionmakersinORandbusiness.WearemakingeortstodevelopatextbookwhichcanteachExcelskills,VBAprogramming,andDSSapplicationdevelopmenttoindustrialengineering,OR,andbusinessstudents.[ 6 ]Wefeelthatthiswillaidinbetterpreparinggraduatesforreallifeproblems. DSSapplicationscanbefurtherdevelopedtobeappliedonlargerscalerealworldproblemsaswell.Thecasestudieswedemonstrateherearesimpliedversionsofsuchrealworldproblems.Futureworkincludesndinglargerscaleapplications,relatedtoresearchorindustrytopicsinOR,anddevelopingadvancedDSSforthem.TheseDSSmayusemoreadvancedobject-orientedprogramminglanguagessuchasC++orC#,andmayalsoreferenceotheroptimizationsoftwaresuchasCPLEX.ThedevelopmentprocessweproposeisstillanimportantfactorindevelopingaDSSwithafriendlyuserinterfaceandecientimplementationofmodelsandalgorithmssothatthedecisionmakingprocessisindeedenhanced.

PAGE 143

[1] D.PowerandR.Sharda.\Model-DrivenDecisionSupportSystems:ResearchNeedsandDirections."(InPress)ManagementScience. [2] T.Mandel.ElementsofUserInterfaceDesign.NewYork:JohnWiley&Sons,1997. [3] J.Johnson.GUIBloopers.SanFrancisco:MorganKaufmann,2000. [4] R.Francis,L.McGinnis,andJ.White.FacilitiesLayoutandLocation:AnAnalyticalApproach.NewYork:PrenticeHall,1998. [5] W.Winston.IntroductiontoProbabilityModels.Belmont:Brooks/Cole,2004. [6] M.Hanna,R.Ahuja,andW.Winston.DevelopingSpreadsheet-BasedDecisionSupportSystems.(InPress)DuxburyPress. 130

PAGE 144

MichelleHannagraduatedfromtheUniversityofFloridawithaBachelorofSciencedegreeinindustrialandsystemsengineeringinMay2002.ShethenjoinedthedoctoralprogramthereinindustrialandsystemsengineeringinAugust2002.Aftertakingseveraldoctorallevelcourses,MichelledecidedtoswitchtothedoctoralprogramindecisioninformationsciencesintheWarringtonSchoolofBusiness.Sheisnowcompletinghermaster'sdegreeinindustrialandsystemsengineering.Michelleisveryinterestedinthedevelopmentandapplicationofdecisionsupportsystems.ShehaswrittenatextbookinDevelopingSpreadsheet-BasedDecisionSupportSystemswithcoauthorsRavindraK.AhujaandWayneL.Win-ston.Shehasalsogivenseverallecturesonthistopicatinternationalconferencesandworkshops.MichelleplanstocontinuetouseherDSSdevelopmentskillsassheworksonherdoctoralresearch.Herdissertationwillbefocusedonapplyingnetworktheoryandalgorithmstologisticsorschedulingproblems.Michelleplanstopursueanacademiccareerinanoperationsmanagementorinformationsciencedepartmentinabusinessschool.Sheplanstoworktowardsatenuredpositionasaprofessorineitheroftheseareas.MichelleisthedaughterofDr.MagdiHannaandMrs.RoblynHanna.Shehasoneyoungerbrother,Timothy.HeranceisOnurSeref.ShewasborninGeorgia,buthaslivedinFloridaforthelast12years.Herinterestsincludelanguages,internationalcultures,andspirituality. 131


Permanent Link: http://ufdc.ufl.edu/UFE0006961/00001

Material Information

Title: Principles of Designing and Developing Spreadsheet-Based Decision Support Systems
Physical Description: Mixed Material
Copyright Date: 2008

Record Information

Source Institution: University of Florida
Holding Location: University of Florida
Rights Management: All rights reserved by the source institution and holding location.
System ID: UFE0006961:00001

Permanent Link: http://ufdc.ufl.edu/UFE0006961/00001

Material Information

Title: Principles of Designing and Developing Spreadsheet-Based Decision Support Systems
Physical Description: Mixed Material
Copyright Date: 2008

Record Information

Source Institution: University of Florida
Holding Location: University of Florida
Rights Management: All rights reserved by the source institution and holding location.
System ID: UFE0006961:00001


This item has the following downloads:


Full Text











PRINCIPLES OF DESIGNING AND DEVELOPING
SPREADSHEET-BASED DECISION SUPPORT SYSTEMSir















By
MICHELLE 31. HANNA


A THESIS PRESENTED TO THE GRADUATE SCHOOL
OF THE UNIVERSITY OF FLORIDA IN PARTIAL FITLFILLMENT
OF THE REQUIREMENTS FOR THE DEGREE OF
MASTER OF SCIENCE

UNIVERSITY OF FLORIDA


2004


































Copyright 2004

by
Michelle M. Hanna

















This document is dedicated to my parents for their continuous love and

support .















ACKNOWLEDGMENTS

I would like to thank Ravindra K(. AlM)l I for being my advisor and friend

throughout this work. I am very grateful for the opportunities he has given me to

research, write, and teach this material. I am also thankful to my fiance Onur Seref

for his constant love and encouragement.


















TABLE OF CONTENTS
page

ACK(NOWLEDGMENTS .......... .. .. iv

LIST OF TABLES ......... .. .. viii

LIST OF FIGURES ......... .. .. ix

ABSTRACT ............ ........... xiii

CHAPTERS

1 INTRODUCTION ..... .. 1

1.1 An Introduction to DSS . ...... .. 1
1.2 DefiningfDSS ........... ....... :3
1.3 Excel Spreadsheets ....... .. 6
1.4 VBA for Excel Programming Language . . 7
1.5 The DSS Development Process ..... .. 8
1.6 Case Studies ........ .. .. 9




2 DSS DEVELOPMENT PROCESS ..... .. 10

2.1 Definingf the Development Process .... .. .. .. 10
2.2 Application Overview ... .. .. .. 11
2.3 Spreadsheets ........ ... 1:3
2.4 User Interface ......... .. 18
2.5 Procedures ......... .. 28
2.6 Resolve Options ......... .. :32




:3 GUI DESIGN AND PROGRAMMING PRINCIPLES .. .. .. ._ 39

:3.1 GI IDesign ............. ..... .... :39
:3.1.1 The Theory Behind Good GUI Design.. .. . .. :39
:3.1.1.1 Users, tasks, and goals .... .... .. :39
:3.1.1.2 Clarity ........ .. .. 41
:3.1.1.3 Consistency . ..... .. 45
:3.1.2 Good and Bad GUI Designs .... ... .. 48
:3.1.2.1 Buttons ........ .. .. 48










3.1.2.2 Text boxes versus list boxes and combo boxes.
3.1.2.3 Tab strips and multi pages .
3.1.2.4 Check boxes versus option buttons


3.1.2.5
3.1.2.6
3.1.2.7
3.1.2.8
3.1.2.9


;us text boxes.
controls.
,rms
edures.


Frames
Labels verse
Dynamic cl
Multiple fo
Event proc


3.2 Progframmingf Practices


Consistent Style
Naming.
Comments
Efficiency.


3.2.1
3.2.2
3.2.3
3.2.4


4 WAREHOUSE LAYOUT


4. 1 Application Overview.
4.1.1 Model Definition
4.1.2 Input.
4.1.3 Output.
4.2 Spreadsheets.
4.3 User Interface.
4.4 Procedures.
4.5 Resolve Options.


and Assumptions


5 RELIABILITY ANALYSIS

5.1 Application Overview.
5.1.1 Model Definition
5.1.2 Input ...
5.1.3 Output ...
5.2 Spreadsheets ...
5.3 User Interface.
5.4 Procedures ....
5.5 Resolve Options ...


and Assumptions


6 CONCLUSION.


6.1 The Importance of DSS.
6.2 Spreadsheet-Based DSS.
6.3 Developing a DSS.
6.4 Conclusion and Future Direction .












REFERENCES ......... . .. .. 130

BIOGRAPHICAL SK(ETCH ....... ... .. 131
















LIST OF TABLES


Table


pagfe


2-1 Summary: Application Overview


Summary :

Summary :

Summary :

Summary :

Summary :

Summary :

Summary :

Summary :

Summary :

Algforithm

Summary :

Summary :

Summary :

Summary :

Summary :

Summary :

Summary :


Spreadsheets.

U~ser Interface

Procedures.

Resolve Options.

Users, Tasks, and Goals

Clarity.

Consistency

GUI Design

Programming Principles



Spreadsheets.

U~ser Interface

Procedures.

Resolve Options.

Spreadsheets.

User Interface

Procedures.


5-4 Summary: Resolve Options.

















LIST OF FIGURES
Figure page

1-1 A schematic view of a decision support system. .. .. .. 4

2-1 An example of a W.~ I ~!!..n." sheet. .... .. .. 1:3

2-2 An example of using spreadsheets to take input front the user. .. 14

2-3 An example of a large set of data imported front a text file.. .. .. 15

2-4 An example of having input, calculations, and output on the same
sheet. ....... ..... .......... 15

2-5 An example of a complicated calculations sheet. .. .. 16

2-6 An example of using a graph to illustrate results in an output sheet. 18

2-7 An example of histograms in the output sheet of a siniulation-based
DSS. ............ ......... 19

2-8 An example of a navigational output sheet. .. .. .. 20

2-9 An pivot table report sheet is one of the output sheets. .. .. .. 20

2-10 The corresponding pivot chart is another report sheet. .. .. .. .. 21

2-11 An example of buttons on the spreadsheet to work with input and
calculations. ......... ... 2:3

2-12 An example of dynamic form controls on the spreadsheet. .. .. .. 25

2-13 An example of controls on a form and spreadsheet. .. .. .. 26

2-14 An example of dynamic form controls. .... .. 27

2-15 An example of a "floatingt form. .... ... 28

2-16 The output sheet for the Reliability Analysis case study. .. .. .. :34

2-17 The first resolve option: modify input in table and rerun simulation. :35

2-18 The second resolve option: el__o----, mu is made to aid decision maker. :36

2-19 Two "1\odify" buttons give the user different resolve options. .. ... :38

:31 Clear instructions and descriptions on each sheet and form. .. .. 42











:32 Buttons are clearly separated into navigation and calculation groups. 42

:33 Labels clearly designate functionality of controls. .. .. .. 4:3

:34 Control tips clarify control functionality. ... .. .. 4:3

:35 Formatting guidelines. ......... .. 44

:36 Clear formatting and default values. ..... .. 45

:37 The navigational buttons are together and consistent in the sheet. 46

:38 Consistent formatting and clear constructions. ... .. .. 47

:39 Combo boxes reduce user nienorization and chance for errors. .. 49

:310 Tah strips and multi pages can he replaced if too many tabs are needed. 50

:311 Option buttons are used for mutually exclusive options and check
hoxes are used for other options. ..... .. 51

:312 Fr-ames have more than one control each. ... ... .. 52

:313 Labels are used for non-changeable values. .. .. .. 5:3

:314 Some functions are active and some are inactive. .. .. .. 5:3

4-1 An example warehouse layout. . .... .. 62

4-2 The warehouse area is discretized into '? w areas of value 1.. .. .. 66

4-3 The final warehouse layout for five products and two docks.. .. .. 67

4-4 The welcome sheet. .. ... ... 69

4-5 The first input sheet for dock information. .. .. .. 70

4-6 The second input sheet for product information. .. .. .. 71

4-7 The output sheet with its navigational buttons and resolve options. .72

4-8 The user form asks for the first input values. .. .. .. 7:3

4-9 The 1\ain procedure and public variable declarations. .. .. .. .. 75

4-10 The ClearPrevious procedure clears values and formatting on all sheets;
it also initializes some variables. .... ... 76

4-11 The cmdOK(_Click procedure assigns the input values to their corre-
sponding variables. .. ... .. 77

4-12 The beginning of the NumberingBay procedure. .. .. 79











4-13 The end of the NumberingBay procedure. ... .. .. .. 80

4-14 The Selection(l'!s Ing- event procedure enables the user to click on the
sheet to place the docks. . ...... .. 81

4-15 The DockInfo procedure records the dock information. .. .. .. 82

4-16 The FinalSteps procedure performs the main calculations and call
the procedures which execute the algorithm. ... .. .. 83

4-17 The ComputeF procedure computes the weighted distances based on
the dock frequencies. ......... .. 84

4-18 The SortItems procedure calculates the product type weights and sorts
them. ....... ..... .......... 85

4-19 The SortB .1-< algorithm sorts the '.1- distance weights. ......... ... 86

4-20 The beginning of the Assign procedure. .... .. 88

4-21 The end of the Assign procedure. ..... .. 89

4-22 The navigational procedures. . ..... 89

4-23 The first input sheet is revisited and some of the dock information is
changed. ......... . 90

4-24 The second input sheet is revisited and the product type information
is changed. ......... . 91

4-25 The new layout is d';1 i-phi I after pressing the "Resolve" button. .. 91

4-26 The resolve options allow the user to specify a particular product's
layout on the Resolve Layout grid. .... .. 92

4-27 The layout has been resolved with the user's specifications enforced. .93

4-28 Bay assignments for multiple product types can be enforced. .. .. 93

4-29 The final layout is modified to honor the enforced '??i- assignments. .94

4-30 The Select(l'!s Ing- event procedure allows the user to enforce particu-
lar '??-- assignments for selected product types. .. .. .. 96

4-31 The Resolve procedure records changes made to input values and hon-
ors enforced '??i- assignments. ...... .. 97

5-1 An example of a parallel serial system. .... .. 99

5-2 The welcome sheet. ......... ... .. 101










The calculation sheet for optimizing the Weibull parameters.

The hidden calculation sheet for the simulation data.

The simulation sheet with the animation layout and input table.

The third calculation sheet for the results of the simulation runs..


. .. 106


-8 The bottom half of the output sheet.

-9 The input form with the dynamic label value for ma

-10 The Input Box.

-11 The Messagfe Box..

-12 The Alain procedure and variable declarations.

-13 The ClearPrev procedure.

-14 The cmdOK _Click event procedure.

-15 The CalcWeibull procedure.

-16 The PrepSim procedure.

-17 The begfinningf of the StartSim procedure..

-18 The end of the StartSim procedure.

-19 The CreateData procedure and WeibullIny function.

-20 The AnalysisPrep procedure..

-21 The navigational procedures..

-22 An example of the first resolve option.

-23 Updated analysis from the first resolve option.

-24 The Resolve procedure.


c


:hine type "A."


-25 The resolve form.

-26 The initialization event procedure for the resolve form.

-27 The Click event procedure for the OK" button on the resolve form..

-28 An example of the second resolve option.


5-7 The top half of the output sheet.















Abstract of Thesis Presented to the Graduate School
of the University of Florida in Partial Fulfillment of the
Requirements for the Degree of Master of Science

PRINCIPLES OF DESIGNING AND DEVELOPING
SPREADSHEET-BASED DECISION SUPPORT SYSTEMSir

By

Michelle 31. Hanna

August 2004

C'I I!r: Ravi K(. AlMli I
II li1 .r Department: Industrial and Systems Engineering

A decision support system (DSS) is a model-based or knowledge-based system

intended to support a managerial decision making user. A spreadsheet-hased DSS

uses spreadsheets to organize data and perform some spreadsheet functions. It

uses a basic programming language to design user interface and implement model

algorithms and calculations. A DSS should also offer the user some options to

resolve his problem for a comparative analysis which may enhance the decision

making process. This thesis proposes design principles and a development process

for building a spreadsheet-hased decision support system.















CHAPTER 1
INTRODUCTION

1.1 An Introduction to DSS

Equipped with the modeling and algorithmic skills taught in the standard

Operations Research (OR) curriculum, many industrial engineering graduates feel

ready to solve real-world problems. With a knowledge and understanding of theory

and applications of niathentatical progranining, simulation techniques, and supply-

chain nianagenient, they are ready to help their companies solve any distribution,

fa..~ I In-r; or planning problems. But then, as they interact more with coworkers

and managers, they realize that the models they have learned cannot he applied

easily to many of the real word problems they now face.

Alany of these problems are decision-nmaking problems which require simple

solutions without the details of the niathentatical models used to solve them. Most

managers would prefer a software solution for such decision problems. However,

most industrial engineering graduates know the right model but not how to

package a model and present it with a friendly graphical user interface (GUI). The

managers need to be able to easily use it, see the results with graphs or charts,

and modify inputs to analyze different business scenarios. The desired software

programs should also be able to pull data front larger databases and manipulate it

appropriately.

This is a widely prevalent problem which is not addressed in the current

OR curriculunt. Models need data which is mostly available in spreadsheets or

databases. Hence, OR graduates need to know how to extract data front these

data sources. They need to know how to check data integrity and perform data

analysis and data manipulation. As OR practitioners, OR graduates are support










staff members and are required to build systems for non-OR users. They must

know how to package OR models so that they can be comfortably used by top

managers and other co-workers. Real-life decision making often requires building

interactive systems, which OR graduates must know how to design and implement.

To summarize, OR graduates must learn sufficient information technology skills to

that they can build intelligent information systems, alternatively, called decision

support systems, which can run sophisticated models at the back-end, but are

friendly enough at the front end to be used comfortably by any user.

A decision support system (DSS) gives its users access to a variety of data

sources, modeling techniques, and stored domain knowledge via an easy to use

GUI. For example, a DSS can use the data residing in spreadsheets, prepare a

mathematical model using this data, solve it or analyze it using problem-specific

methodologies, and assists the user in the decision-making process through a

graphical user interface. The importance of DSS development skills has become

well noted in the literature: "Given the growing complexity and uncertainty in

many decision situations, helping managers use quantitative models to support

their decision making and planning is an important research topic" ( [1]). DSS

applications are usually intended to be designed for non technical users presented

with an easy to use interface.

OR graduates are frequently being emploi-v I in positions that require devel-

oping DSS which are gaining widespread popularity. As more and more companies

install enterprise resource planning (ERP) packages and invest in building data

warehouses, those who are able to create decision technologies driven applications

that interface with these systems and analyze the data they provide will become

increasingly valuable. Indeed, imparting DSS development skills, which combine

OR skills with IT skills, will make graduates highly sought after in the modern

workplace .










Developing courses that teach OR students how to build DSS has been a

challenging task so far since it requires the availability of platforms which allowed

the integration of various technologies (data, models, codes, etc.). However, in

the past few years, several platforms have become available which allows such

integration. One such platform is 1\icrosoft Excel. Excel, which is the most widely

used spreadsheet package among managers and engineers, allows data storage and

model building. Excel also has many built-in program as well as many add-on

programs available that wallow optimization and simulation of various models

built in Excel. Excel also has a macro programming language, Visual Basic for

Applications (VBA), which allows building GUls and manipulating Excel objects.

Thus, Excel provides a platform using which fairly sophisticated DSS applications

can he built.

1.2 Definingf DSS

A decision support system (DSS) is a model-based or knowledge-based system

intended to support managerial decision making. A DSS is not meant to replace a

decision maker, but to extend his/her decision making capabilities. It uses data,

provides a clear user interface, and can incorporate the decision maker's own

insights. Some of the 1 in r~ DSS capabilities are the following.

1. A DSS brings together human judgment and computerized information for
semi-structured decision situations. Such problems cannot he conveniently
solved by standard quantitative techniques or computerized systems.

2. A DSS is designed to be easy to use. User friendliness, graphical capabilities,
and an interactive human-machine interface greatly increase the effectiveness
of a DSS.

r3. A DSS usually uses models for analyzing decision-making situations and may
also include a knowledge component.

4. A DSS attempts to improve the effectiveness of decision making rather than
its efficiency.

























Figure 1-1: A schematic view of a decision support system.


5. A DSS provides support for various managerial levels front line mangers to
top executives. It provides support to individuals as well as groups. It can he
PC-based or

A DSS application contains five components: database, model base, knowledge

base, GITI, and user (see Figure 1.1). The database stores the data, model and

knowledge bases store the collections of models and knowledge, respectively, and

the GUI allows the user to interact with the database, model base and knowledge

base. We now present a more detailed look at each of these components.


Database The database provides the data with which decisions are made. The

data may reside in spreadsheets or a data warehouse, a repository for

corporate relevant decision-nmaking data. The database allows a user to

access, manipulate, and query data. Some examples of databases would

include a spreadsheet containing personal banking account information or a

data warehouse containing shipment records of various products.

Model Base A model base contains statistical, financial, optimization, or sinmu-

lation models that provide the analysis capabilities in a DSS. Some popular










optimization models include linear programming, integer programming, and

nonlinear programming. The DSS allows the ability to invoke, run, and

change any model or combine multiple models. An example of a model base

would be an integer programming model used to solve a capital budgeting

problem. Most common DSS applications are primarily model driven. A key

DSS component is its resolve options. A user should be able to manipulate

their input values to compare multiple results for scenario analysis. The DSS

should be designed for repeated use to aid in a recurring decision situation. It

should be dynamic enough to handle various problem sizes, input values, and

objectives.

Knowledge Base ?1 I.ny managerial decision making problems are so complex

that they require special expertise for their solution. The knowledge base

part of a DSS allows this expertise to be stored and accessed to enhance the

operation of other DSS components. For example, credit card companies

use a DSS to identify credit card thefts. They store in their knowledge base

the spending patterns that usually follow credit card thefts; any abnormal

activity in an account would tr~i c-;r checking for the presence of those patters

and a possible suspension of the account.

GUI The graphical user interface (GUI) covers all aspects of communication

between a user and a DSS application. The user interface interacts with the

database, model base, and knowledge base. It allows the user to enter data

or update data, run the chosen model, view the results of the model, and

possible rerun the application with different data and/or model combination.










The user interface is perhaps the most important component of a DSS

because much of the poser, flexibility, and ease of use of a DSS are derived

front this component.

User The person which use the DSS to support the decision making process is

called the user, or decision maker. A DSS has two broad classes of users:

managers and staff specialists, or engineers. When designing a DSS, it is

important to know for which class of users the DSS is being designed. In

general, managers expect a DSS to be more user-friendly than do staff

specialists.


A DSS should be distinguished front more coninon nianagenient information

systems (!lS). An MIS can he viewed as an information system that can generate

standard and exception reports and suninaries for managers, provide answers to

queries, and help in monitoring the performance of a system using simple data

processing. A DSS can he viewed as a more sophisticated MIS where we allow the

use of models and knowledge bases to process the data and perform analysis.

1.3 Excel Spreadsheets

Microsoft Excel spreadsheets have become one of the most popular software

packages in the business world, so much so that business schools have developed

several popular Excel based courses. A spreadsheet application has functionality for

storing and organizing data, performing various calculations, and using additional

packages, called Add-Ins, for more advanced problem solving and analysis. Excel

spreadsheets are easy for a user to interact with and easy for a student to use










while developing the DSS. We consider two aspects of Excel to be important in

developing a DSS: hasic functionality and extended functionality.

Excel basic functionality includes referencing and names, functions and

formulas, charts, and pivot tables. These are standard tools that may be common

to most spreadsheet users. Excel extended functionality includes statistical

analysis, the Solver and modeling, simulation, and querying large data. These

tools are especially important for building a decision support system. The ability

to model a problem and solve it or simulate it adds the model base component of

the DSS we are building. It is important that a DSS developer become familiar

with the capabilities of Excel so that they know what they can offer the user when

developing a decision support system.

1.4 VBA for Excel Programming Language

VBA for Excel is a programming language that allows for further manipulation

of the Excel functionalities. VBA for Excel also allows the developer to create

dynamic applications which can receive user input for the model base component

of the DSS. VBA allows users without knowledge of Excel to be able to use

spreadsheet-hased DSS applications. There are several important features of VBA

for Excel.

Some of these features include recording macros and working with variables,

procedures, programming structures, and arrays in VBA. VBA for Excel is an

easy to understand programming language. Even if a student has not programmed










before, they should be able to program several types of applications after a basic

introduction to VBA.

A DSS developer can also create a user interface in VBA. These features

includes building user forms, working with several different form controls, using

navigational functions, and designing a clear and professional application. VBA is

beneficial as it places all of the complicated spreadsheet calculations and any other

analysis in the background of a user-friendly system.

Some of the extended Excel functionality topics can he further enhanced hv

using VBA. The modeling, simulation, and query features of Excel can become

dynamic using VBA coninands. These techniques are especially important to

understand in order to build complete DSS applications.

1.5 The DSS Development Process

We present a chapter on the DSS development process to explain how the

Excel spreadsheet functionality and VBA progranining features can he combined

to develop a complete DSS application. We propose five basic steps for this

development process: i) outlining the application, its model and assumption;

ii) determining how many spreadsheets will be needed and for what purposes;

iii) constructing a general layout of the user interface features; iv) outlining

the progranining procedures needed; and v) ensuring that resolve options will

be integrated into the DSS. We describe these steps in detail and give several

examples in this chapter.










We also present a chapter on GUI design and progranining principles. There

is much literature on these two topics which are important in developing any DSS.

We suninarize the issues that are most relevant to developing spreadsheet-DSS

applications in this chapter.

1.6 Case Studies

We present two case studies to illustrate the relevance and importance of

decision support systems in the fields of industrial and systems engineering and

business. We strive to accomplish this by showing how to develop DSS applications

which integrate databases, models, methodologfies, and user interfaces.

These case studies consist of developing a complete decision support system

and are based on an important application of IE/OR or business. Through case

studies, graduates will learn how IE/OR and business techniques apply to real-life

decision problems and how those techniques can he effectively used to build DSS

applications .

These case studies are just some of the numerous case studies we develop in

order to illustrate how DSS applications can he developed by combining infornia-

tion technology tools with operations research and business tools to solve important

decision problems.















CHAPTER 2
DSS DEVELOPMENT PROCESS

2.1 Definingf the 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. Before entering formulas

into Excel or coding sub procedures in VBA, it is necessary to construct an overall

layout for the DSS and give some thought to the design and implementation of

the application. We propose five basic steps for developing a DSS: i) Application

Overview: create a layout of the entire application to understand the flow from the

user input to the model calculations to the output, ii) Spreadsheets: determine how

many spreadsheets you will need to best handle input, calculations, and output,

iii) User Interface: outline what interface you will need to receive input from the

user and navigate them through the application, iv) Procedures: outline what sub

and/or function procedures you will need in your code to receive the input, perform

the calculations, and display the output, v) DSS Components: decide what resolve

options the user may have.

These steps have been our guidelines to developing decision support systems.

We do not claim that they are necessary to follow, but rather -II__- -r them as

good guidelines when developing a DSS application. In this chapter we give

several examples from case studies we have developed using these proposed

steps. The following chapters give a more detailed explanation of each case

study's development using these five steps. We wish to illustrate the Two'~ iv, and

consistencies, possible in developing DSS applications.










2.2 Application Overview

The Application Overview is the most important step in developing a DSS.

In this step, we consider the entire flow of the application. We usually begin this

flow at the \\Y ~~ !!..n." sheet. A \\Y ~~ !!..n." sheet should have the title of the DSS

and some description of what the application does. Any assumptions or necessary

model explanations may be given in this description. There may also be some

images on this initial sheet related to the application topic. Then there should be

one button to "SI I11 or "B. asis the application. Even though this sheet is simple,

it is an important introduction for the user to what your DSS is and how they can

begin to use it.

The user should then encounter some method for providing input. This may

involve a form or set of forms, or the user may be brought to a new sheet where

further instructions are provided. Deciding which method or methods to use is

important and depends on the application. For example, if you only need one or

two pieces of information from the user, you may not even need a form or an entire

sheet for input; instead, you may use an Input Box. In some applications, you may

need large sets of data for your analysis. In that case, you may only prompt the

user to import data from a text file or database to a spreadsheet. Once you have

decided which method is most appropriate for your application, you may need

to spend more time designing the interface; however, we will return to this in a

later step. It is important to complete the Application Overview before designing

the interface so that you have a clear idea of what the entire application will

incorporate.

After receiving the input, the model should be ready and calculations can be

performed. It is a good idea at this point to have an overview of what is required

for your model calculations. You may need to know the model formulation before

you can finish deciding what the user input will be. The first thing to decide is










if this DSS will be computing simple calculations, performing an optimization,

or running a simulation. The details of these models can be outlined in a later

step, but for the purpose of the Application Overview, you should have an idea

of what will be involved. This general model outline will help you in determining

the details of your spreadsheet design and procedures later. Once input is received

and the model calculations are performed, we need to determine what output will

be di;11 li-c 4I to the user. Will there be charts or graphs, or histograms or tables?

Does some of the input need to be re-di11 li-c 4I to the user? Again, these options

will depend on the application. It is important to consider the output as it is a

driving force in why the user is using the DSS. It is a good check to see if you are

computing everything the user may be interested in.

The last part of the Application Overview is reviewing the DSS components.

In C'!s Ilter 1, we define in detail what a DSS is comprised of. These include, the

model base and user interface discussed above. However, a DSS should also provide

some resolve options for the user. The user should be able to change some of their

initial input values and resolve the problem. The user may also want to add some

constraints to an optimization or redefine their objective function. We so-----~ -r that

these resolve options are made available on the output sheet. We will give more

examples of these DSS components in the following sections.

Th'ble 2 1: St. .. Application Overview

Welcome i i Flow begfins; introduction to wha~t D is axnd how to
b-egin using it.
Inpurt Providledl by uiser via set of forms, ': put spreadlsh~eet: or
B~oxes.
Model Clalcurlations Foirmulilation of ob-jec~tives andi necessary inpult; diecidie if
compu~rtingf simplIle calculations, performing axn op~timlizax-
tion, or runningf a stimulation.
O ii A dirivingf force in whyl~ the urser is ursingf the D.
R-esolve Options Use can modify input, redlefine constraints, or .
obj ec~tives.



































Figure 2-1: An example of a \\Y ~!!..n." sheet.


2.3 Spreadsheets

There may be two to several sheets in a DSS application. The first sheet

should ah-li-w be the \\Y. !...in. sheet as we discussed above. For example, in

Figure 2.1, we show the \\Y ~ !!..n." sheet from a case study we developed for a

Portfolio Management and Optimization DSS. We give a description of the DSS

and describe the model assumptions. We also reference the source of our model

formulation. We also have some images related to portfolios. Then we have a

"Start" button which the user can press to begin the application.

The remaining sheets are for input, calculations, and output. We may have

these as separate sheets or some elements may be combined on fewer sheets. Sup-

pose we need a sheet for input. We can prepare the sheet by placing appropriate

labels for tables or input locations. We may also name some ranges at this point

which will help us later when coding. Below is an example from a case study we











Achvity Tablea
-_1 =M~e c xs-!:Gplio for ea:: n 3:: trl. r :-s d ool ~nchirxe us Aivr y rr unl ~er See Example



I~~~~V~ NNtdy1 2 $1.0 0

11 2 1 unly ~2 5 3 $9 00 5 0
31 Aermrly3 B 5 513X $800
11 4 1 i~vily 4 5 2 St)0 5 00
1'i A trviy T d 1 $19 00 54 00
10. 7 rkmlyd 7 E, 3 $13iX 00I $6 00-v~


Figure 2-2: An example of using spreadsheets to take input from the user.


developed for using the Critical Path 1\ethod (see Figure 2.2). In this application,

we take the user through several input sheets. In each sheet, we have a table for a

set of input values. In some cases, spreadsheet may be a better user interface than

forms for receiving input; we discuss this in more detail in the next section.

You may also have an application which requires a large set of data. This data

may be imported from a text file or database, or input by the user. In Figure 2.3,

we have an input sheet from a Stochastic Customer Forecasting case study. This

sheet contains the historical data that is used to make future forecasts. In this

application we give the user the option to enter this data manually or import it

from a text file.

You may not need an input sheet for every application. Let us consider the

case where the input sheet may be combined with the calculations sheet or output

sheet. For example, in the figure below, we have a sheet from a Technical Analysis

case study in which the input, calculations, and output are all on one sheet (see

Figure 2.4). Here the user can modify the input using spreadsheet controls and

press the "R. -u~li.- hutton to update the calculations in the table. The output is

summarized in a small table on the right of the screen.

Again, you may not even use a sheet at all for your input. You may simply

take input from a user form and then use that directly in a calculations sheet or














A B C D E F G H 1 J


You may vary the Risk Rate and Time Florizon to view possible profits wilth trading strategies
vyngbetween the given lower and upper bounds.
O(= Buying Condition (# months). Y = Selling Condition (# months).)

Yo rnay change the Risk Rate and/or Time Horizon values and press RESOLVE.


0.05 Year Month


Calendar Year 2001


Month WekDay
(Jan= 1. (Mon= 1. Number of
Calendar DyDec= 1)Fn = 5 Customers
2 1 2 1825
3 1 3 1257
4 1 4 969
5 1 5 1672
8 1 1 1098
9 1 2 691
10 1 3 672
11 1 4 754
12 1 6 972
15 1 1 816
16 1 2 717
17 1 3 728
18 1 4 711
19 1 5 1545
22 1 1 87 3
23 1 2 713
24 1 3 626


48
4B


SE |F G | H


20 $10.233.7r9 57,079.46 56 473.93 595.934.60 562.661 52 579,071.97
21 $8.563.46 57,657.79 57 411.76 IBB~~ Il~$66,421.09 595,071.75
22 5$6,447.52 56,015.40 $55570.67 574.798.37 546,452.35 559.905.42
23 I 6.282.46 $55864.61 $55547.53 574,487.71 549,01322 563.208.94
24 57,222.25 57.105.53 55,552.11 592.524.69 560,874.79 580.833.86
25 t $7,203.04 57,518.56 $6,884.64 585,653.75 557,997.05 587.176.47
28


Figure 2-4: An example of having input, calculations, and output on the same
sheet.


Figure 2-3: An example of a large set of data imported from a text file.


Al 8 C | D

Output for Option A


From: 12
To: -99


Jan
I Jan -


I"t=--~













Simulation Ca culab~ons
trIL r, EIACK
Current Savingsl 520.000.00
Current Salarl ~54.000.00
Salary Increasel 5.00%b
Percent Salary to Investl 10.00%

Asset t Allocation Luin-rai llvesmentw
T. Bills Bonds Stocks T. Bills Bonds Stocks
0.50 0.30 0.20 $2,250.00 $1,350.00 $900.00


14 35 1 0 0312 -0 0559 0 0658 52,320 20 $1.274 64 5959 04 $4,553.78 9
15 36 2 0.0351 0.0967 -0.3503 54,802.23 $3.024.64 5950.09 $8,776.95 9
16 37 3 0.08 -0.0263 0.2141 $7.418.63 54,570.37 $3.270.97 $15,259.98 9
17 38 4 0.0182 0.1002 -0.0491 510,420.92 58,414.75 54.366.71 523,192.38 9
18 39 5 0.0035 D.D568 0.3242 514.381.24 511.896.44 59,123.58 $,401.25 9
Jan 1 1,1,1 I I 1 1,i 1,, 1 1 IJ 1 4 (. : I O .'i .,*
4 1 I1 ,I::,:: 1,,- < 1 2 l_ 1:- 1 1 1I, f i
21 42 8 0.0772 0.1811 0.2402 $42,881.03 540,071.75 526,592.68 $119,545.47 9
2243 9 0.0616 D.0967 0.1681 561.675.78 $555 156.05 537,341.40 $154,173.24 9
23 44 it0 00818 3 0553 -0 085 580,722 16 574 319 61 $41.101 50 $196,143.2E6 9
26 45 11 00035 -00122 02402 5102.09272 588,27373 573,81721 $261,1113.66 9
25 46 12 0.0157 -0.0531 0.1847 5136,550.80 5112.384.84 590,006 58 $338,942.23 9
26 47 13 0.006 0.2444 -0.1159 5174.379.35 5191.309.33 587,115 40 $452,804.08 9
27 |48 14 0.0476 0.0618 0.0401 $241.623.44 $217,705.79 $136.843.61 $55172.85 9
28 49 15 -0.0002 -0.0026 0.1245 5302,480.76 $288,913.24 5194.715.51 $766,109.51 9
29 50 16 0.0182 -0.0394 -0.1078 $394.789.07 32,14.15 $198.472.04 $9575.26 9



Figure 2-5: An example of a complicated calculations sheet.



in some calculations procedures and take the user directly to the output sheet.


Since we are developing spreadsheet-based DSS applications, we will usually take


advantage of the spreadsheet features to aid us in performing calculations. For this


reason, we will usually have a calculations sheet. This sheet may be viewed by the


user or, in most cases, hidden from the user. A calculations sheet should be hidden


if the intended user may not be familiar with the details of the calculations but is


solely interested in the results. In Figure 2.5, we have a complicated calculations


sheet from a simulation performed in a Retirement Planning case study. There are


several spreadsheet functions and formulas in the sheet as well as some input cells


whose values have been updated after a user has completed an input form. Since


the sheet calculations are somewhat complicated, we do not show this sheet to the


user during the normal flow of the application, however, we do give the user the


option to view the calculations if they want to. We normally, take the user directly

from the input form to the output sheet in this application.


1
2
3

5
6


9
18










You may have some other hidden sheets related to the calculations. For

example, in simulation we usually store the results of the runs to user for creating

histograms or other suninary reports. This detailed sheet should be hidden

front the user in the application flow, but can he made available for viewing if

the user is interested. By using the Application. ScreenUp dating method and

Worksheets.Visible property we can prevent the user from seeing these calculation

sheets while they are being used for the model calculations. Probably the most

important sheet for the user is the output sheet. This sheet should suninarize the

results of the calculations clearly so that the user can understand the behavior of

whatever system they were modeling or analyzing. It is usually a good idea to have

some graphical results as part of the output sheet. For example, in Figure 2.6, we

have the results sheet front an Inventory 1\anagenient case study. Here the graph

illustrates the ordering strategy found by the model calculations. There are also

some tables used to suninarize the numerical results of the solution.

In DSS applications using simulation, it is usually good to have some his-

togrants as part of the output sheet. In Figure 2.7, we have some histograms

suninarizing the results of a Reliability Analysis case study. We have a histogram

showing the frequency of various system failure time values using a bar graph along

with an overlaid scatter plot to show the cumulative probability of each value.

Below that, we have another histogram representing the frequency with which

different machine types have caused the system failure; this histogram is shown as

a pie chart. We give the user several options front this output sheet including to

return to and rerun the simulation or return to the initial input phase to resolve

the problem.

In some cases, you may have several charts or larger suninary tables that may

not fit into one output sheet. In that case, we reconinend making a navigational

output sheet which will allow the user to view these individual reports. For













ResuIlS


m~m


ordering Strategy
100 @*1



- 1 II





0 00 1 0B 2 00 3 00 4.00 5 00 6 D
Tirne monthsh)


$6728.01
ST28.01




145.60
14.56
0.07
61.00


Al B |C |D E FIG H 1|1 K


Figure 2-6: An example of using a graph to illustrate results in an output sheet.


example, In Figure 2.8, we show a navigational output sheet from a Supply C'I !a.

management case study. This sheet allows the user to view several different

summary pivot tables (see Figure 2.9). From these pivot tables, the user can also

view corresponding pivot charts di;11l li-. I as separate chart sheets (see Figure

2.10). The user can .ll.h- li <; return to the navigational output sheet from any of

these reports.

Whichever results are relevant to your application, you should ensure that they

are presented to clearly in the output sheet. "End" and "R. -u~li.- options should

also be found in the output sheet as well as options to "Vi. i.-- input or calculation

sheets.



2.4 User Interface


Designing a user interface is an important element of developing a user-

friendly DSS. We discuss good graphical user interface (GUI) design in a later

section; for now we will discuss what role the user interface pIIlai in the DSS

development. There are three main categories of user interface in spreadsheet-hased



















































I'
Fhstogram


Table 2-2: Summary: Spreadsheets


Welcome Sheet Title and description of the DSS: images; "Start" but-
ton.

Input Sheet U~ser input; large data input; can he combined with
other sheets.

Calculations Sheet Spreadsheet calculations; simulation results; usually
hidden from user.

Output Sheet Summary tables and reports; graphs, charts or his-

tograms; navigational output buttons; "End," "Re-
solve," and "Vi~ .- buttons.


IA I I C | E | F G | H 1 | J |C L M N



5 1 i M ran Failulre Time 0 ..r. I *I. *-.r I; -





18



4~I. 41.25 1 12.0


27 M ore 100.00 8



18~ *.12
*1.Bin


Ems Frequer y
A 9
B 11
C 5


7: An example of histogframs in the output sheet of a simulation-based


41

3 ;


6







57


Figure 2
DSS.


I




















SReport I





Number to Produce


Number to Ship


Shipping Costs

Holding Costs

Production Costs


Poducll |Fcinglrv -I 1 2 3 Grand Tol
1 1 I I: 0~ 00 ~ 10i 00l 10 00 iii 00!! 00
2 17! i ) ) 1 3 :







Grand~ Toa 5169.8 51.79.7 51,99.8 55.459.40! r !


i: n


I


Figure 2-8: An example of a navigational output sheet.


Shipping Costs


01 E


Told Shipping Cosi


c~usiorner I


Figure 2-9: An pivot table report sheet is one of the output sheets.


|View Model n

View Details Resolve


|Return to Report


eiV w Chart
































Figurre 2 10: Ti -- ... : ondling pivot chart is another: sheet.


DSS applications: user forms, form controls on the spreadsheet, and navigational

buttons on the spreadsheet.

Let us begin by discussing navigational buttons. As we have already men-

tioned, the first button you should create is the "SI I11 button which is located on

the W.~II~~!...in" sheet (see Figure 2.1). This button should be assigned to a macro

which brings the user to the input interface. On all other sheets, input, calculation,

and output sheets, there should at least be an "End" button. The user should

ahr-l- .- have the option to "End" or "Exit" the application. Note: Whether you use

"End" or "Exit" or any other phrase for this action, be sure that you are consistent

across all sheets in the application. We discuss consistency in user interface design

more in the GUI design section.

You may also have some other navigational buttons such as N.::1," "Con-

tinue," or "B II I: if you intend for the user to be able to step through the sheets or

revisit sheets. This is especially important if you have hidden the sheet tabs or are










only making one sheet visible at a time; which we recommend for a more profes-

sional presentation. In the case where input, calculations, or output are combined,

you may also have some functional buttons on the spreadsheet such as "Solve."

For example, in Figure 2.11, we have one such sheet in a case study on the

Animation of the K~ruskal Algorithm. In this case study, we take the user directly

from the W.~ !...in.-:" sheet to the sheet shown in the figure. We highlight the

"Create Table" button as it the next button they should press (see Figure 2.11(a)).

When they press this button, they are prompted to give the dimensions of their

network, and then a table with the corresponding number of rows is created. After

the table is created, we now make a new button visible called "Solve" (see Figure

2.11(b)). We un-highlight the "Create Table" button and highlight the "Solve"

button since this is the next button the user should click. This button will run the

procedure which animates K~ruskal's algorithms and finds the minimum spanning

tree solution.

Aside from using functional or navigational buttons on the spreadsheet, a

user interface may also use form controls on the spreadsheet. Refer to Figure 2.4

to see an example of text boxes and combo boxes used on a spreadsheet in which

input was taken from the user on the same sheet where calculations and output

were diI11 l-, Il. Another example is shown in Figure 2.12. This example is from the

Inventory management case study. Here we have three option buttons representing

different methods which can he used to find the best order strategy. These option

buttons are mutually exclusive and two of them also have dynamic features. The

bottom two buttons have some associated cells for extra input which are shaded

darker when unmarked and made lighter when marked (see Figure 2.12(b)).

Form controls on the spreadsheet are useful when there are many resolve

options in the application. In this case, you want to give the user easy access to the

input in order to be able to change it multiple times. It is important to keep the

































































































1115 E
32B


.-l va lI wrutruggir dag ri a rra m.g~igrme ..L Ift il Ili 0


O


I; r ir p ? ;(-T i..- r ?: .. !ir.0- 1r I bc f.L .. nr ;[ ;.1 .-F. c..I In rn-T


.el. 11 *1 Ir.- r car- ; it.- ~l ~ e 1 r i l:r.-1l y l 1:.l ~ Ihh2IlT: 2 1,11
n- n : ne ab i L H s 4r- E 0 L : 1 1
1.,> ,| The cinin a .. ..c


Jh


l Ilr. 11
E=44ri JO 4 ( g (a in)


T.I elf(FI


. It ( Jim PHe MIL I KI I III lit 11s edib Jils it Jamiliti l.*

,,an.C In C.,1 all r .-2 Trr-....I I [. II Fl.-&(.-
L ,p p., -fr1 L on .L sc.@ .vr .L ..L. 10. u..T..rl.."l:IA T. lear.. T..r..
nal rr n .1 nr 1 "... 1 -1
n-.. r.n n pr-I r,7T... I .. .)Iral 1r ..1-]
3 un I Till r]r.II.r II .nI rr 1
fl ( I .. L 1 3. I r .c 10 r..r 3 a .1 I I.a.=..T
111 n r Ell* 4 GLn il*-*.30 s 5 ID ar.| c ; I P.T a .1, r.rr T 5)...4 Firkli billure..it l| gll .I al. 1 I
r ., aI
FI| .. .. In c nr..Isr, ..rpr.gir.. 3. I .nrr I;*I| _.* .|7 I.,I.n.t.1r .grastg.*S
FI-

1
~e~FU~B
~-~-~I
Salic rible
II ~nb*rib* I










-!:5~1


I
O
s


*ll I1.*1.0 I


lIr T.0Ml
8 1, I~ *si 6 Id)


CI .l1


IIIit. ILi


i.


1=:i.=e Ill= y


Figure 2-11: An example of buttons on the spreadsheet to work with input and

calculations.










layout of the spreadsheet clear and uncluttered when using placing form controls

.Il1i Il-ent to other input cells, calculations, or output. We discuss these interface

design issues in a later section.

In some cases, there may be an even tradeoff between using functional buttons

or form controls on the spreadsheet versus creating a user form. For example,

in Figure 2.13(a), we have used two functional buttons to allow a user to "Add"

and "Rtemlus t stocks to and from their portfolio in the Portfolio management

and Optimization case study. In this particular case study, we have put this

functionality on the spreadsheet because it is a feature the user may use often. The

user may go to a new sheet to view stock comparisons and then return to edit their

portfolio; the user may also go to an optimization sheet to view investment strategy

results and then return to edit their portfolio and resolve. However, if this were not

the case, that is if the user did not need to create or edit their portfolio multiple

times, we may have created a user form to perform this functionality. In Figure

2.13(b), we show an example of such a form used in a Beta of Stocks case study. In

this case, the user only selects their portfolio once.

In most DSS applications, if there is a large enough set of input required

from the user, we so----- 0 creating user forms. User forms can he advantageous in

that there are many options for placing and manipulating controls on a user form.

The controls can also be more clearly di;11 li-- I1 as they are not interfering with

other cells on the spreadsheet. Another advantage of user forms is that they can

he di;111lai- I1 to the user at any time; that is, they are not attached to a specific

spreadsheet. This can he especially useful for resolve options.

If a user wishes to resolve the problem and presses a "Resolve" button on an

output sheet, the input form can he relli-pllai-. .1to them directly without even

moving to a new sheet. In Figure 2.14, we have an example of a user form from a

Retirement Planning case study. This form is dynamic in that the first frame below





















SA r


B C |0 E F G IHi

Input


$50.00



2120


A | B C D E F G IH


HIER Hw~
Determinisic:
SStarninrd EOm


c Backrders


Stochastic:
C Reorder Pont


Input


~m


fW~i~'





r;m~rrmAnrsma


11
12
13


16



17


L1I.0. I
$15.(K)

ns.w I

61 5.00

586.52 1


Figure 2-12: An example of dynamic form controls on the spreadsheet.


]Yi


Deterministic:
r Standard EOC


r Backorders


Storchastic:
6 corner Point












































Create Your Portfolio j
Please select which stocks you would Hike to place in your
portfoiio for a nalysis.
Select a stock from the All Stocks: list to add it to your portfolio.
Select a stock from the Portfolio list to remove it from your portfolio.


\UI


AIIl Stocks
Cin
Dell

INC
usFr


Por~to~io

Del
MSFT


.. I


Figure 2-13: An example of controls on a


form and spreadsheet.


Canael I












Please center the following input

Current Age 3 5
CurrentSavings 2~0,000 $
Annual Salry 45 ggg $
Annual% IBncrease in Salary 5
% of Salaryto Invest per Year 10

Desired Savings at Retirement 1 1, age00
Co~nfidence Interval for Returns (%)J g0


Please enter the followuing input

Current Age Ij
Current Savings I ,~,,,;,
Annual Salary -!,nt;, I
Annual% bInrerse in Salary
%j of Salary to, Invest per Vear I ,,


Age to Retire 60


-Asset Allocation (%})
T.Bills Bonds Stocks


OK Cace 2


SY II II(h)

Figure 2-14: An example of dynamic form controls.


the text boxes may change depending on a previously selected option. In Figure

2. 14(a), the user is providing values for "Desired Savings at Retiln is.. !I and

"Confidence Interval for Returns" whereas in Figure 2.14(b) this framed is changed

to prompt the user for the "Age to Retire." The second frame on this form, for

"Asset Allocation," is also dynamic. In Figure 2.14(a) the user is prompted to

enter this information, but in Figure 2.14(b) the textboxes are gl li-. I and locked

since the information is not relevant for this option.

Another way to use user forms in a situation where the user may need to

modify input multiple times is to create a "floatingt form. The advantage of this

type of user form is that the user can select or modify cells in the spreadsheet

without having to close the form first. In Figure 2.15, there is an example of a

floating form from a case study for Animating the Simplex Method. This form is

used to allow the user to select the entering variable for each iteration. The user

selects the entering variable from the tableau on the spreadsheet and can then view


OK Cane I













Iblry l-r -5.r~ld KFlh U-tr Tl C da l.@-ir PIU r~enalrmpr.atinm
-.I .~~~I-~~i I i1.. .i _1 il; I' I-.elemust be given below In a speclicformat To see an example of the format, click on See
E ~Iuirut -II; In, s, Il yr.In ...I II., I r .3 ei theirr click on Show Each Ieration or Show Fim[! Solulion In either case, you will be asked to
... o c .1~~~.I Iw oul-. a.11 .! ......Le ofoonstraints aridwhethervouwish tomaximize orminimizethe objectiefunction Onceyou
:-~. r,i r;- .rral- la'l:-a. :I.c IC[1i l- szre wll beconstructedforyou whereyou can entertle data ntherequiredformat

Pi- 3: CII I II 1.11...< : HPi I Sr. n:I I .IlljE*iE *drnrr. 10,1- -I ;Il.:.n d: Ir.;-.l-~r.1-ur1..i=* Ei ri..i~ YOUcHQSe~letafffferefi
-li.-r.=.ravi r ~ l~'~~II .q u-sIiav-.m-.uIwF'11-iller


Inital Tableau
; >2 r: hr ,5 *i Xj )7 RHlS

>5; I 1 ? 4 -1 1 0 0 E
ar. 2 1 -1 1 0) 1 0) 1
r7 0 1 0 1 1 0r 0 I J



11erall.)r 1
I Il x2 *' 14 15 If6 >7 RMtS
S 1 -1* 0 25 05 0 0 ?

xT2 0 0 1 O -0 (1 0 5





z xl d2 x4lh *S x6 i7 RHS
r 1 | D O -1 4 05 0 ,15 |9


51Iow Reart nratrar


Figure 2-15: An example of a "floatingt form.



the results for that scenario on the floatingf form. The form is hidden when the user


moves to another sheet.



2.5 Procedures


The next step in developing a DSS application is to make an outline of what


procedures you will need to conduct the flow and execute the calculations. As


discussed in ('! .pter 15, we encourage you to organize your code into several


smaller procedures which may be called from other main procedures or associated


with buttons on the spreadsheet. We recommend making an outline of these


procedures in your code before you begin the details of the implementation. We


ah .--4 hegin our applications with a Alain sub procedure which is associated with


the "Start" button on the \\U.I~~!!..s-" sheet. From the Alain procedure, we usually


See Exampkl


>1 0I 1


1 s










Talthe 2 3: Summall~ ry: U~ser Inter~face

S-igfational But- 71. buttonssuh, suc a t" i," ""Back," or
tons i : i": should be on ----- sheet to navigate thre user
through the application.
Functional Butt~ons T'.butt~ons, such as ("C~rcate T~Iable": or lve,'?
be usei if m~ult~iple : : cu on(C' 1 one sT(Gheet.
Controls on the P'` :. controls on the spreadshleet allows users to
SI- I : et i : 'i m~oti: input fo~r m~ultiple solution calculations.
User ~or~ms U.ser form~s arel most, comml~only usedi wheln large
amounts < inpu t are: neededt; H.ar tn dvn
t ageous .


begin by clearing previous data and initializing variables; this can also be done by

calling a ClearPrevious procedure. We then either take the user to an input sheet

or show them an input form. Consider the following example.

Sub Main()

Call ClearPrevious

f rmInput .Show

Worksheets (' Input ") .Visible = True

Worksheets("Welcome").Visible = False

End Sub


Sub ClearPrevious()

clear ranges on other sheets

Worksheets (''Calc ") .Range (' Input Values").Ceronnt

Worksheets (' Output"').Range (' Results").Ceronnt

'initialize variables

Set Input Range = Worksheets (' Input ") .Range (' Input Start "

End Sub










There should then be some procedure which receives the input from the user.

If we are using user forms as the interface for receiving input, then this code would

be in the event procedures for the form. Consider the following example.


Sub cmd0K_Click()

'set variables equal to control values

NumRuns = t xtNumRuns .Value

InputSize = txtInputSize.Value

ReDim Input Array (Input Size)

Unload Me

End Sub


Once the input is received, the calculations should be ready to perform. These

calculations may involve running a simulation with some loop structure or evoking

the solver with the Solver commands. The calculation procedures may be called

when the "OK(" button is clicked on a user form or they may be assigned to a

"Solve" or "Continue" button on an input spreadsheet. Consider the following

example.

Sub DOSimulation()

For i = 1 to NumRuns

create random values

'perform calculations

Next i

End Sub


Sub Do~ptimization()

SolverReset

SolverOK SetCell:= MaxMinVal:= ByChange:=

SolverAdd CellRef := Relation:=, FormulaText:=










SolverOptions AssumeNonNeg:=True

SolverSolve UserFinish:=True

End Sub


The final procedure to be outlined is related to displaying the solution on the

output sheet. If there is a chart, you may need to update the source data. If there

was a simulation, you may want to create some histograms. In any case, you want

to put the solution values in some report table on the output sheet. The procedure

to create the output may be called from the calculation procedures or from another

functional or navigational button on the calculation spreadsheet. Consider the

followingf example.

Sub CreateReport ()

'place solutions in report table

'update chart source data

ActiveSheet.Chart~bjects(1) .Select

ActiveChart.SetSourceData Source:=

'create histogram

Application.Run "ATPVBAEN.XLA!Histogram," Input, Output, Bin, Labels,

Pareto, Cumulative, Chart

Worksheets ( "Output ") .Visible = True

End Sub


These procedures should outline the overall flow of the application from user

input to calculations to output. Aside from these, there should also be any needed

navigational procedures for "End" buttons or N. ::1 "Back," or "Vi~ e buttons.

Consider the following example.


Sub EndProgram()

Worksheets ("Welcome") .Visible = True










ActiveSheet.Visible = False

End Sub


Also ensure that all variables are declared and that any variables used in

multiple procedures are declared as Public variables at the top of the module.

"DIbt-le 2 41: Summary:: Proceduires

Main? Call C Prervious pr~ocedcure. input or takke
user to input shoot.
('i : Previous ('i : previous rlanges of input or solution? vatlues. Ini-
tialize variables.
Roccive T:. at Stor: values fr~om fo~rm con~r~ols or input cells to cor~re-
sponding var~iables. Record these: values t~o appropriate
cells in calculation shoot.
Periiarmt Calcula- Periiarmt c~alc~ulaion~s using ::: i: :: procedures, simnula-
tions tions loopos, or Solverl commands.
Ccncr~ate On1: at D' i" solution values to o>rt table, update chartl
source d-at~a: or c~relate histogr~ams.
.-igfational ( 'i .. e Visible: -;1 ; r of wlorkshleets for "
"I "Back,";.I or ""V burtton furnctionality.
Var~iabjles Make sure all va~riabi-les ar~e dlciaredi andi- thrat va~riables
ursedi in more than one : edurrre ar~e dleclairedl as Public
variables at the of the mnodurle.



2.6 Resolve Options

The last but most important step in developing a DSS application is to ensure

that it has all of the components of a complete decision support system. There

should be some input taken from the user via some GUI, and there should be some

calculations made based on some model base, database, or knowledge base. We

confirm the input interface while outlining the user interface, and we confirm the

calculations and model in the procedure outline. The other important DSS feature

that we should now check is the resolve options.

Can the user easily modify the input to resolve the problem without having to

re-enter all input from scratch? We should ensure that this is possible by making

sure the user's initial input values are preserved when re-displaying a user form










or input sheet. 1\ake sure you do not call a ClearPrevious procedure unless the

user has indeed restarted the entire application. Also ensure that default values

do not overwrite the user's last input values when re-showing a user form. This

allows users to quickly modify one or several parts of the input and resolve the

calculations to compare results.

Can the user change other parts of the calculations or model when resolving?

That is, we do not want the user to be limited to only modifying input values when

resolving. The user should be able to change some constraints or objectives as well.

Try to keep your application dynamic so that a user can experiment with different

problem dimensions. This may not he possible or applicable for every DSS, but

if it is, it should be made available to the user. If some dynamic options are not

available to the user, state your assumptions clearly on the W.~ II n.-~" sheet to

explain this.

With resolve options, you may want to provide the user with a way to compare

various results or scenarios. You may want to store multiple solutions for this

comparison or sensitivity analysis. Ask yourself what the user is really interested in

learning front the DSS. Renienter that a DSS is designed to aid a decision maker

in making a decision. C'I. I 1: that the results of the application are indeed helpful to

this decision making process.

We will give a few examples front our case studies; a detailed description of

the DSS components can he found in each case study chapter in this part of the

book. Let us first consider the Reliability Analysis case study. In this study, a

user is analyzing a parallel series system of three machine types. After providing

the necessary input, a simulation is run to determine the mean failure time of the

system and how often a particular machine type caused the system failure. Figure

2.16 shows the output sheet for this study.































Anal y


Lst-ogram
12 In r r.

10 n.





4-



SI I ""'u:


Bin


F.1 FI


I


Histogram








448

InA uB oCI


iii iii_


I


L


Mean Failure Time


| -'





1-




17
18

19-


Ii | .: | i


E | F | *. | 14 | 1


I I | I.


i Mean Fallure Time :1~ ~~.lr~: :~in ~- .I :
"la.*:i I.T r 'I.- Sysem FIIdu re I: il.ll- II- u .rl ~: L L .1 1. I I.- :l. TI .' .1 :: *.cr. .I-


.~: . ~ I.- a:SIM ULAT IOP F l... *.1 ..;-~~.r~ .. .

.. 1 .... i :: Ir BM I ro T 1 RE OL E .i cr..I ,: I r : .i I- I.Il-'Ii l1 i I I-:II .....-.ne

.- I ...- I


S stem Failure Analysis

37 04 1 4 002.
39.15 1 8 00%
41.25 1 12 00z
43 35 4 28 002.
45.48 10 88.002
More 8 100.00%


A 3
B 11
C 5


Figure 2-16: The output sheet for the Reliability Analysis case study.











I I I I I I I I I


B C D I a E


SF I G H I 1 -.


IA


IPress the START SIMULATION button to beamn thlefadure

: ~Fail~ed!


10 10 10 10 10 10

11 ~ lrxr 4 4 4 9 9

12 1 IIII~25 25 25 8 8

13 1 50 50 5 7 7 ;

14 1 ~r~10 10 10 6 6

15 1 rrnilWl5.797 5.797 5.797 5 5

16 1 9ilm9lt5.999 53.999 53.999 4 4

171 3 3

181 2 2

191 1 1 1



Figure 2-17: The first resolve option: modify input in table and rerun simulation.


In this case, we have two resolve options for the user. The first option is for

the user to return to the simulation sheet and modify the initial input values in a

given table (see Figure 2.17). They can then re-run the simulation and view the

updated results.

The second option is for the user to improve the system by adding one

machine of a particular machine type. To aid the user, or decision maker, in

deciding which machine type they should add a machine to, we first run an

optimization in the background and -II__- -r to them the optimal choice. We do not

enforce this decision, but instead try to aid the decision maker. This information is

presented to the user on a user form (see Figure 2.18).

After a machine type is selected, one machine is added to this type and the

simulation is rerun. The updated results are then shown.












To resolve for best system improvenent, select for which
machne type to add a machine.

You maV use the information below to gldde Vou in making this
decision.

Chane i H~n Machine Cost Ratio
R~.s e m Failure Time 17

B 45.10 25 1.80)
C 43.6~7 25 1 .75

We suggest adding a machine of machine type: C


A B rC


Add /Resolve Ca ncel



Figure 2-18: The second resolve option: 0..... -1; H, o is made to aid decision maker.


Another example is from the Inventory Management case study (refer to

Figure 2.12). In this case, the user can actually change the model base along

with the input each time the application is resolved. The user can decide which

inventory model to use: Standard EOQ, Backorders, or Reorder Point. There is

also an input table which can be modified on the same sheet.

Another example is from the Portfolio Management case study (refer to Figure

2.13). In this case, after the user has created their portfolio, they can optimize

their investment strategy by minimizing risk (see Figure 2.19).

After filling the input in the user form for the optimization (Figure 2.19(a)),

the resulting optimized investment strategy is di;pts, li4I on an output sheet

(Figure 2.19(b)). However, if the optimization was infeasible, or if the user wants

to experiment with different values, they can either return to the input form to

experiment with different values (by pressing the "Modify Input" button), or return

to the portfolio sheet to modify their stock selection (by pressing the "Modify










Portfolio" button). An extension to this case study may be to allow the user to

modify their objective in optimizing their investment strategy; currently we assume

that we nminintize risk, but the user may also want to nmaxintize returns.

Resolve options are an important DSS component. Ensure that the DSS is

aiding the decision maker by allowing the user to modify inputs or calculation

options.

Table 2-5: Summra~ry: i ivc Optlions

Resolve OLi' .0 : 4 1\ldilj input s, < al ul action <. .1 : I, coust r~aint s. ob i
tives: aid dcicision mrakicr in making I~he bes.i eC~iSiOn?.

















Please enter the input data below to optimize your
portfolio investments.

Total cash to invest: I oom(

Minimum desired return on portfolio: 1 a %

Mlaxiimum amount per investment: 1 1 %


OK Cancel


Figure 2-19: Two 1\lodify" buttons give the user different resolve options.















CHAPTER 3
GUI DESIGN AND PROGRAMMING PRINCIPLES

:3.1 GUI Design

A graphical user interface (GUI) is the ,i 11pha! 11 representation of, and

interaction with, programs, data, and objects on a computer screen." (11 limb [2])

It presents a visual display of information and objects which can present visual

feedback to a user. Part of the definition of a DSS is: A DSS is designed to be easy

to use; user friendliness, graphical capabilities, and an interactive huntan-machine

interface greatly increase the effectiveness of a DSS (refer to ChI Ilpter 1). Thus, it

is very important to design the user interface such that it is easy for the user to

understand and use. If the user interface is not designed well, then the application's

functionality will not he appreciated. In this section, we will discuss some theory

behind good GUI design and give some examples of good and had user interfaces.

:3.1.1 The Theory Behind Good GUI Design

There are ]?r lr:y GUI design books which lists several different principles and

guidelines for good GUI design. We present here a suninarized version of what we

feel are the most important theoretical points for good GUI design in spreadsheet-

hased DSS applications. These are: knowing the user, the user's tasks and goals;

maintaining clarity, and cr lite;~! consistent.

:3.1.1.1 Users, tasks, and goals

It is important to know who the users of your application will be. Are they

managers'? If so, how deep is their understanding of the problem'? Do they know

the model or algorithms being used to perform the calculations'? What terminology

do they use to discuss the problem'? If the user does not have a highly technical

understanding of the topic of the application, then try to avoid describing the










details of the model or calculations. This would be a case when the calculation

sheet may remain hidden. Try also to give instructions and label input without

using technical terminology. For example, instead of labeling input as C or D, give

meaningful descriptions such as "Annual C<~-l or "Annual Demand." In the case

that your users do have a more technical undersr lllidin you should show and

explain the calculations and assumptions. You may also want to give more details

using the terminology they are familiar with.

Keep in mind that the user is using this application to complete some tasks

and achieve a goal. It is important to ensure that the user interface is an aid to

the user in completing these tasks so that the user feels that the DSS has indeed

become a helpful tool in increasing the efficiency of achieving their goal. The

user's task domain includes "the data that users manipulate, the manner in which

that data is divided and the nature of the manipulation that users perform on

the data." (Johnson [3]) Remember that the user's tasks are already necessary

without the help of a DSS, therefore, ensure that your DSS application aids them

in completing these tasks in the same domain they are familiar with. These tasks

should be organized on some priority or hierarchy base in order to create a flow

for the application. This flow influences the outline of the entire application as

we discussed in the first section. Your interface should guide the user so that they

can work with the data in their task domain in the order in which it needs to be

completed.

For example, let us suppose that the user's task domain involves looking

at some historical data, then computing a mean and standard deviation of this

data, and then entering these values into a forecasting model. Based on the result

of the model, the user has to take the forecast demand for the next month and

place an order of that size. When constructing the interface for a forecasting DSS,

ensure that these tasks are presented to the user in the same order. First, ask










them to enter the historical data. If they usually get this data as a text file front

a coworker, then do not ask them to enter it manually, instead prompt them to

import the text file. You can then automatically calculate the mean and standard

deviation to display for them. Afterwards you may ask for some extra input for

the forecasting model, but try not to get to technical. You may then display

clearly to them what their order amount should be based on this forecast. Do not

try to reorganize their tasks as they will find the DSS hard to learn. K~eep the

presentation of the tasks simple so that there is an element of familiarity for them.

Tabile 3-1 Sul~mnutry: Users. Ta~SkS, andr Coals1~

Wlhat is the: user'ls knowledge of the: problem andi technical under~st .:: :: of
the: model calculations?
Wlhat te: : : 1 is the user : :: : ::- with?)
Define the urserts task diomain to determine the ...F.:ation flow.



3.1.1.2 Clarity

A user interface is the coninunication between the user and the application;

therefore, if you want the user to use the application correctly, you must coninuni-

cate clearly to them what they should do to use it. First and foremost, make sure

there is a clear description of what is involved on every spreadsheet and every form.

For example, in Figure 3.1, we show the calculation sheet front a Sales Force Allo-

cation case study. We ask the user to enter some bound values for the optimization

constraints. We then give them two calculation options. We explain the user's

tasks in a text box at the top of the sheet. We have folded the button names and

colunin names in the text to help the user quickly identify the location of the tasks

on the sheet.

The functionality of any button or control should be clear to the user. On

spreadsheets, try to make some separation between navigational buttons and

functional buttons. For example, if on an input sheet you have the buttons "End,"









I I I I


-iA

4
5
6
7

u ~
11
12



21


22


Ir~F;PTCli~lii.~ll.c


B CD I E l F G' 11 I I .I I K


Please enter the Upper Bound on the Total Number of Calls and a LowEer Bound and Upper Bound for the Number of
Cals for each customer.
Afer you havre entered this information. press Optrnize to determine the best Number of Calls to make per customer in
odrto maximize Total Overall Revenue
If you do not kniow the best input value for the Upper Bound on the Total Number of Calls. you may pressViewv Trade Off
Curve to view a trade off of different Upper Bound values.


Uoper Bound on Total Number of Calls


M


M


101


Figure 3-1: Clear instructions and descriptions on each sheet and form.


Figure 3-2: Buttons are clearly separated into navigation and calculation groups.

"B II I: and "Solve," it is better to keep the navigational buttons "End" and

"B II I: together and place the "Solve" button somewhere else on the sheet.

Likewise, on user forms, ensure that functional buttons are separate from the

"OK(" and "Cancel" buttons.

Aside from buttons and command button controls, all other controls should

also be clearly labeled so that their functionality is understood. Never let a text

box be unlabeled and assume the user knows what to enter. Also ensure that list

boxes and combo boxes are labeled so that the user knows what the list contains.

Fr-ames containing grouped items should also be labeled to signify the grouping.

The clearer the controls are, the quicker the user can learn their functionality and










Input Fon _
Filin the folloing: (anoel I

x2 I
Select one:


(a)


43


Input Form
Please provide input values for all of the following:
Annual Cost
Annual Demand
Please select one of the
following produPCts from the list: II


cancel I (h)


Figure 3-3: Labels clearly designate functionality of controls.



Please provide input values for all of the following:

Annual Oast
Annual Demnan~d

Please select one! of the
follwying products from the li~st:






Figure 3-4: Control tips clarify control functionality.


the easier it is for them to use the application. For example, compare Figure 3.3(a)

and Figure 3.3(b), without clear control labels, users will have to hesitate and guess

what information you are asking for.

Another way to clarify control functionality is by creating control tip messages.

This is a good way to provide more detailed instructions to the user without

cluttering the form. For example, in Figure 3.4, there is a control tip for the combo

box. When the user places the cursor over the combo box, the text "This list

contains all products in the system" appears.

Another benefit to clarifying your user interface functionality is that it may

reduce the errors encountered by the user. The most frequent user errors involve

inputting values in an incorrect format or of an incorrect type or choosing a










Input Form
Please provide input values forp all of~ the folknuing:

Annual Cst 2I~0 $
Annual Demand 5000 units

Please select one of the II
following products from the list:





Figure 3-5: Formatting guidelines.


selection or command button at an inappropriate time. Even though error checking

can be done, as discussed in C'!s Ilter 22, having a better-designed user interface

can reduce this extra coding. Aside from clearly labeling controls, you may also

give default values as an example of the input the user should enter. You may also

guide the user for proper formatting issues.

For example, referring to Figure 3.3(b), if the user is supposed to enter a cost,

they may enter "$20,000" or "20,000" or "20000." If you do not want the user to

enter "$" or "," punctuation marks, then you should clarify this to them on the

interface design. Either write more specific instructions, or guide them with default

values; otherwise, you will have to do some error checking in your code to ensure

that a data type error does not occur when you try to perform an operation on

their input value (see Figure 3.5).

Some other common formatting examples are for numerical input such as

social security numbers or telephone numbers. In Figure 3.6(a) we show that the

user may input these values with various formatting. This may cause errors when

storing, searching for, or performing operations with the data. Figure 3.6(b) has

clarified the formatting issues so that the user is only entering numerical values

without extra punctuation.










Personal Input
Please enter Vaur phone mnbner and SSN:

Phone Nlumber: 399 99 j52 55-34

SSH: 1123-45-6789


Cancel OK D


Please enter your phone manber (wvith area
code) and SSN:
Phone lmnber: (135 )15 -- 23
SSN: -- -- ~15~ l~7


Canel OK(I


|1 II|a I | Ilth)

Figure :36: Clear formatting and default values.


If there is still a user error while using an input interface, make sure that a

clear error message is given to the user. The user should understand what they did

wrong and what they need to do to correct the problem. For example, the error

message "Incorrect input!" is not helpful to a user. However, a message such as

"You may not enter negative numbers. Please enter a positive number." redirects

the user to correct the error. Errors should be hard to make and easy to correct.

Overall, clarity is very important in good GUI design. It is important to check

sheet and form instructions, control labels, and data input guidelines to ensure that

the user can clearly understand what to do.

Table :32: Suninary: Clarity

Give clear instructions at the top of each spreadsheet and each form.
Label controls clearly so that their functionality is understood.
Control tips can he used to add detail to functionality descriptions without
cluttering the form.
Give default values to clarify how data should be input.
1\ake formatting issues clear.
Clear GUI design can help the user avoid making errors.
If user errors are made, give clear error messages to redirect the user to
correct their error.



:3.1.1.3 Consistency

The third theoretical point for good GUI design is consistency. A user will

be inclined to interact with an interface according to how they are expecting






















(a) (b)

Figure 3-,7 7: T navigational buttons are: together and consistent: in t~he shot..


it to be. That is, they may expect some input prompt, button locations, and

viewable options based on their familiarity with working with the problem or with

other interfaces. It is important that within your application, or across similar

applications, some features of the user interface are consistent.

The first place there should be consistency is on the spreadsheets. Try to keep

the title and sheet description and instructions in the same location for each sheet

in the application. This way, if the user is looking for an explanation of what is

included on a particular sheet, they can ahr-l- .- look at the same location on the

sheet. We tend to keep sheet titles and descriptions at the top left of each sheet

layout. Also ensure that the navigational buttons, especially the "End" button, is

in the same location on each sheet. The user should not have to search through the

sheet to try to exit the application. Compare the forms presented in Figure 3.7.

You should also consider consistency in the sheet layout for input cells and charts.

For example, if you have multiple output sheets, each with a chart, the charts

should all be in the same position on each sheet.

In designing user forms, consistency can be enforced in several v- .1-<. First of

all, as with sheets, ensure that some description label is ak- .1-< at the top of the

form. Also keep navigational command buttons, like "OK(" and "Cancel," in the

same position on all forms. If "OK(" is on the bottom right of a form and "Cancel"










Input Form
Inpu For --Please enter the information brelowu.

Enter the vales below: ae

wnme: AgIne:
age I Please list the names of the last twHo
I educational institutes attended:

Education: I 1.


= I OK



Figure 3-8: Consistent formatting and clear constructions.

is on the bottom left, do not switch them for subsequent forms. The user should

not feel tricked into pressing the wrong button.

Regarding form controls, using the alignment and size features can also

improve the form layout. Try to keep text box sizes the same throughout the form,

they should also all be aligned equally. Keep all buttons the same size as well. Try

to also be consistent with punctuation, such as the use of semicolons. Compare the

forms presented in Figure 3.8.

If your form instruction label and command buttons are consistent for the

forms in your application, you can save interface development time by saving the

first form you create and importing it multiple times. (Refer to C'! l oter 18 for

detailed instructions on how this is done.)

Another feature of form consistency that can aid the user in moving quickly

through the interface is the tab order of the form. Ensure that the tab order

take the user from the top of the form down to the "OK(" button and finally the

"Cancel" button. Again, the idea is to help the user feel that the user interface










is easy to learn and easy to use. Keeping the forms consistent throughout your

application will help the user accomplish their tasks more quickly and efficiently.

Ta~ble 3-3: Sum~mar:: C~onsistcenc

Consistency is important in 1 .' r: != the: user' move Io = i. 1y through the urser

Kieep, sheet titles andi instrucritionls inl the: same locattion1.
K~eep navigational buttons, i -i :: ly the i :: button, in thie sam~e loca-
tion sheet.
Keecp input c~ells and chlarts in the sam~e locations similar sheets.
Keep 1 instructions a~nd command buttons in the same location fo~r each

Mi~ake controls consistent byI using align andl~ samelc size featurres.
Set tab: ordier to taxke user 1: : top> of form to :"OK1 aindl :"C .: i burttons.



3.1.2 Good and Bad GUI Designs

We would now like to provide several examples of good and bad GUI designs.

We have grouped these examples by different control types and a few more general

categories which apply to the entire user form.

3.1.2.1 Buttons

Buttons should alr-ws- be of the same size and shape. Try to also keep buttons

the same color unless highlighting a particular function button to guide the user.

Group functional buttons together and navigational buttons together. We have

seen examples of this in Figures 2.21 and 2.26

3.1.2.2 Text boxes versus list boxes and combo boxes

Reduce the memorization requirements of the user by replacing text boxes

with list boxes or combo boxes when possible. This also reduces possibility of

errors. For example, if user is asked to enter a student name for their class grades,

using a combo box would prevent the user from entering the name of a student

which is not in their class, or misspelling the student's name. Compare the forms in

Figure 3.9.










rtudent Form at Iturn F rm
Enter a student's name to view their grades. I Please select a student from the lis to view
their grades.

Student Hame: III IStudent Hame: I


Ca ncl OKI Ca ncael ON
(8) II hI)

Figure :39: Combo boxes reduce user memorization and chance for errors.


:3.1.2.3 Tah strips and multi pages

When using tab strips or multi pages, try to minimize the number of tabs. Too

many tabs can cause tab positions to shift when clicked; the user may not see all

of the tabs and leave some input blank. Try to reorganize your input needs so that

you can use multiple forms or combine tab information to reduce the number of

tabs. Compare the forms in Figure :3.10.

:3.1.2.4 Clo1 : hoxes versus option buttons

Only option buttons should be used for mutually exclusive input. Even though

option buttons can also be used for non-mutually exclusive input if they are not

grouped in a frame, we recommend using check hoxes for this purpose instead.

Be consistent in your use of check hoxes and option buttons for these respective

purposes. Compare the forms in Figure :3.11.

For option buttons, you should ahr-l-w have more than one grouped in a frame.

If there is only one option, then treat it as an "on/off" option and use a check

hox. Another way to modify only one option is to create another option with an

opposite value. For example, instead of just giving the user an "on/off' option for

"Assume non-negative values," you could create another option such as "Do not

assume non-negative values" or "Allow positive values."























Options Form
Please enter all the data in each tab.

Home Ino Work Info Emergena l

Address: I



Phone: I



Ulsernamse and Password Address! rl
Street: 1


Options Form
Please enter the following information:

Username:
Passwoerd:

-~ Adress




City, State:
-Phone Numbers

Home
Work

I



(a) (


City, State:


Cancel I


OK 1


Figure 3-10: Tab strips and multi pages can be replaced if too many tabs are
needed.











Options,, Fom
Please select which options you want to
use to solve the problem.

-Objective
r Maxinke Returns

r Mininke: Risk


Assu~mptions:
r At least 10% return should be met.

AHl stocks should have some
investment.

r Output a chart with the investment
strategy.


cana~18 o


Options Form
Please select which optins you w~nant to
use to solve the problem.

-Objective
& Maxinke Returnsi

0 Minimhe: Risk

-Assumptions
r At least 10% return should be met.

SAll stocks should have some
inv~estment.

r Output a chart with the investment
strategy.


cana~I ac


Figure 3-11: Option buttons are used for mutually exclusive options and check
boxes are used for other options.


3.1.2.5 Frames

Frames can be used to group similar items. You should therefore ak- ai- have

at least two controls in a frame. However, if you have more than one frame on a

form or sheet to separate different groups of controls, you may end up with only

one control in one of the frames. You should still avoid having all frames with only

one control or only one frame with only one control. Compare the forms in Figure

3.12. To use frames with controls on the spreadsheet, you can use shape boxes.

3.1.2.6 Labels versus text boxes

Labels should be used for read-only information. Do not use a text box to

present information to a user when they should not be able to modify it. For

example, suppose we have a form to receive input for three machine types in a

production system. We may use a loop to show the same form to the user three

times to receive the input for each machine type. We may di pl w~ the machine type









Input Form
Please input the f~ollowFing values:
Produt Namne:
SI
Costs
Set Up Cost:
Va ria ble Cost: I
Order Cost: I
Holding Cost: I

Produrct Demand


Iac~I o


Input Form
Please input the following values:
-Produc Info~
Name:
Demand:



Set Up Cost:
Variable Cost:
Order Cost:
Holding Cost:


Figure :312: Frames have more than one control each.

number to the user, but we do not want them to modify it; therefore, we should use

a label not a text box (see Figure 3.1:3)

:3.1.2.7 Dynamic controls

For dynamic controls, you may be making some controls visible or not visible

or you may keep them visible but make them inactive. To make a control inactive,

you must gray it out to an extent that there is no confusion from the user on
whether or not they can change the value in the inactive control (see Figure :3.14).

You should also lock inactive controls so that they cannot he modified. If a control

becomes inactive, you should also set the Tah Stop property to False.

:3.1.2.8 1\ultiple forms

When using multiple forms, make sure to hide or unload sequential forms so

that there is not a1.xc;-iing on the screen. The only time a lli-~ lin may occur is

if there is a sub form which must he filled before a main form can he completed.

However, try to avoid such situations.
















Machine Input

Please input the following information:

Machine # r~
System = Parallel

Mean Failure Ilme: I

NMnber currently I
in system:


Ca neel OK o


Machine Input

Please input the following information:

Miachtine # I

System = Parallel

Mean Failure Ilme:

NMnber currently
in system:


Ca neel OK


\UI


Input Form B
Please enter the follwving log in information:
User Type
Nc ew User r Returning User





Email Address:l

Cancel OK 0


Input Form
Please enter the following log in information:
Ulser Type
r Nlew User Returning user


Username:
Password:



Cancel OK


\UI


Figure :313: Labels are used for non-changeable values.


Figure :314: Some functions are active and some are inactive.










3.1.2.9 Event procedures

Associating actions with some event procedures can be confusing to the user.

For example, when using command buttons, it is better to use the Click event

rather than the MouseDown event. The user may press the mouse down and not

see a result thinking that the form is not working.

For text boxes, it is better to use the AfterUpdate event rather than the

C'!s lIII event. The ('I! IIIe event may cause errors if some formatting or values

have been enforced. For example, suppose there is an error check to ensure that a

number has been entered in a text box. If the user is deleting a previous value to

enter a new one, when the text box changes from one value to empty, the error will

be caused. Instead use the AfterUpdate event so that you only check the value of

the text box once the new value is completely entered.

For check boxes, we recommend that you use the Change event instead of the

Click event. However, be aware that with mutually exclusive option buttons, the

C'I! Ia!,- event may be over-tlric~1 re, and so the Click event may be better.


3.2 Programming Practices

There are ]rn Ilny books on programming practices and coding standards. We

summarize here what we feel are some important issues when coding in VBA

for spreadsheet-based DSS development. We categorize these issues as follows:

coding with a consistent style, using naming standards, having clear comments, and

increasing coding efficiency.

3.2.1 Consistent Style

Your code should reflect a consistent style, that is, it should appear that the

same person has developed all of the code. Sp1 .II -! indenting, line length and

other formatting should be consistent. We would recommend keeping procedures

and functions spaced apart enough so that it is easy to scan through the code.























Table: 3-4: Summnar~y: ( i i Design

Commranld Buttons Simnilar sh-apes, sizes, colors, and locations. Separate
func~tiona~l and na~vig~ationali buttons.
Textl Boxes ver- Minimize usetr memor~izatlion by usin= `: i boxes or
sus TList B~oxes andi combo b-ox-es in~stead- of t~ext b-oxes. Th~is :----- also
C~ombo B~oxes minimize user crrtor.
Ta S: .and Only use a tabs. Avoid hlidin~g some tabs.
M'ulti P~ages
(l. i Boxes ver~sus O3 .1 buttons should be used fotr mutually exclusive
C Ho'In Buttions options; user check~ boxes otherwise. U~se Crchck boxes
fo~r "'on/ < 1~ options or cr~eate an < i o>site option to, use
option buttons.
F~ramres -- to have mlor~e than one: control ; .frame and more
thlan one 1: :: per form.
iabi-els vlr~sus Texx t iabi-els should be: used fo~r read!- i ... .= :n.
Boxes
Dyntam~ic Co~tr~ols Mk nciecnrs -----" and loeckd. Also chang- e
their tab order.
I! 1 1.F~ormIs M'akie suire to close : :i ." for~ms. Avoidl layeringf if
possible .
Eve~nt Proc~eduires Coman buttons ( I:k b:etter than MourseDow-n:
Te~xt boxes: A 1 : Updtate better than C : =:.~ ; C
b-oxes: ( i. ... e~ better than Ci i : Option burttons: C I:k
better than C i










We also recommend using indenting to signify the beginning and end of a loop or

logical structure. For example, consider the two examples below.

Indenting not clear:

If A is True Then

'actions 1

Elself B is True Then

'actions 2

End If



Clearer indenting style:

If A is True Then

'actions 1

Elself B is True Then

'actions 2

End If


We also recommend being consistent with the line length of your code. It is

better not to have to scroll back and forth through your module to read various

lines of code. Try to break lines at logical places, but be as consistent in line

length as possible. Coding style can also be observed in the naming standards and

commenting style, which we discuss in later sections.

Another area where style should be consistent is in error checking. Ensure that

error checking is done consistently. That is, do not check input from one form but

not another, or do not check input from a Message Box but not a form. Whatever

methods you use for error checking, try to use them in all of your error checking

routines if possible. In general, when you are outlining your procedures, ensure that

there is some consistency to your coding approach.










:3.2.2 Naming

Naming standards should be applied to both variables and procedures. Avoid

redundancies in these two different areas. That is, if you have a procedure named

\hi.Plrofit," try not to define a variable named illi.Prof." It is good to use

abbreviations in names, but make sure they are clear and not confusable with

multiple meanings. For example, the variable name "NunlF II may represent

"number of facilities," "number of faculty," or "number of factories." When using

more than one word in a name, we -II---- -1 capitalizing the beginning of each

word. Try to make names descriptive but not to lengthy; for example: jli.Val,"

"SuntProfit," \! l.::Price." Variable used for loops, aside front "i" or "j" or other

small indices, may be descriptive of the loop count. For example, you may use

"iter" to count the number of iterations in a loop; or "run" to count the number of

runs in a simulation.

Some other coninon naming standards refer to control names. We give a list

of the starting name values for various controls in C'!. Ilter 18. For example, the
name of a text box should begin with "txt"; the name of a form should begin witfh

"frnt." For Boolean variables, we reconinend starting the variable name with "Is"

or ((Do." For example, "IsDone" or "DoAniniation." For constants, we reconinend

writing the names in all uppercase, such as: "PI" or "INFINITY."

For procedure names, try not to make them to vague. For example, instead

of "Calc," use a more descriptive name such as "CalcReturn." For function

procedures, you may also use names which signify the returned value. For example,

"Findl~inCo -I or "GetUserInfo." In general, use names which will not he easily

confused. This will help avoid coding errors and ease debugging.

:3.2.3 Coninents

Coninenting is a good habit to have as a coder. Coninents help clarify

what you have done and why you have done it. They would also benefit another










programmer who may look through your code later. Keep your comments up to

date as you make changes or updates to your code.

You should have a comment at the beginning of each procedure to give an

overview of what the sub or function will do. Avoid abbreviations in these com-

ments as you want to ensure that they are clear. You should also have comments

before loops or logical checks to explain the flow of the code.

U~se comments to organize your code and make it easier to read. Do not overdo

it though as you do not want to have unnecessary comments which only add length

to your code.

3.2.4 Efficiency

The most important programming issue is code efficiency. You should con-

stantly be trying to improve your code by reducing the complexity of the logic

or time required. If you have several nested loops, see if you can simplify this

structure, maybe a Select, Case structure would be better than multiple levels of

If, Then. Clo.1I : if you are repeating some actions unnecessarily. Are you clearing

values before overwriting them? Are you reformatting a range unnecessarily? Are

you repeating a calculation that has previously been computed? As you are writing

comments to explain your code, check if you could improve it first.

Another way to improve code efficiency is to ensure that extra memory is

not being used. For example, there is no need to declare a counting variable "i"

as a Double, it only requires the memory of an Integer data type. Ensure that

arrays are also dimensioned for the needed storage space. You can use the ReDim

Preserve statement to do this.

You should also write your code to be dynamic for future extensions or

updates. For example, for bound variables, array sizes, or range values, you may

want to use variables instead of values. Consider the following examples.


Static structure:










For i = 1to 10

'do actions

Next i



Dynamic structure:

For i = 1 to NumProducts

'do actions

Next i


Static structure:

Dim Cost Array (10) as Double



Dynamic structure:

Dim CostArray() As Double, CostSize As Integer

ReDim CostArray(CostSize)







Static structure:

'paste in output table

Range(' Ai") .PasteSpecial



Dynamic structure:

Dim OutputTable As Range

Set OutputTable = Range(''Ai")












In the dynamic structures in the above examples, it is easy to modify the code

by assigning a new value to the extra variable; otherwise, modifications would have

to be made multiple times throughout the code.

For spreadsheet-based DSS applications, you can also improve code efficiency

by balancing what needs to be done in code with what can be done in the spread-

sheet. For example, some calculations can be prepared on a spreadsheet using

Excel functions rather than computing them with a function procedure or loop

in the code. Likewise, much formatting can be done in the spreadsheet instead of

in the code. There will be a tradeoff between functionality and fanciness of the

program; it is more important that the application model performs correctly. The

better the coding, the better the quality and performance of the application.

Tabic 3-5: Summar~y: Programming Principles

Consistent Style Uise a consistent style for formatting, or ; :; andi
c~omment~ing your code.
nama~ingf standards for var~iables and 1 -:edurres.
~ standards also alpply to control names a~nd
'1 :data types, such? as B~oolean.
CYomentc~l s Make commelcnt~s clear thrroughjout, thec codel. Describe
procedure fiunc : .`: and loop alnd logical low;.
:: ~ ~ A 1---: look -1 --to improve your coding effic~iecy.~~
Avoid rledundancies and unnececssaryv code.


OutputTable.PasteSpecial















CHAPTER 4
WAREHOUSE LAYOUT

4.1 Application Overview

This case study is a DSS application of the warehouse layout problem. The

warehouse layout problem is to subdivide the floor area of a warehouse into storage

areas for several product types so that the total material handling cost between the

storage areas and the warehouse docks is minimum.

4.1.1 Model Definition and Assumptions

For simplicity, we discretize the warehouse floor area into A unit grids, called

.,1- : here A represents the total warehouse area. We are seeking to allocate these

.1-
However, we will present some notations first for the three entities in the problem:

the warehouse '.1-
B .1- We decompose the warehouse floor area into Ab .1-~, indexed by k = 1,

2, A.

Docks: The warehouse has a docks indexed by j = 1, 2, n. Products are

brought into the warehouse and travel out of the warehouse through these docks.

We represent the distance between each '??i- and each dock as dkj.

Product types: The warehouse layout problem as m product types indexed by

i = 1, 2, m. A product i requires an area of Ai '.1-
that the warehouse has enough floor area to store all of the product types.



~A = A (4.1)
i= 1
In Figure 4.1 we construct an example with five product types and two docks.

























Each product type i has a demand Di. We assume that the demand for each

product type i is uniformly divided over the entire storage area required by for

the product type. Since product type i is stored on Ai '-1-,- the total demand of

product i per storage '??-,- is Di/Ai.

Each product type also interacts with the docks with a different frequency.

The frequency is defined as the fraction of the demand that will travel in or out of

the warehouse through a particular dock. We define these frequencies as Fij. The

sum of the frequencies over all docks for each product type should be 1; that is.



E Fay 1 Vi(4.2)
j=1
We are seeking to assign product types to the warehouse area such that the

product types with the highest demand per storage '??i- are situated closest to the

docks with which they have the highest frequency. To enforce this policy during

assignment, we define a weight Wij to represent the amount of a particular product

type i per storage '??i- that travels to and from a particular dock j. These weights

are defined as follows.


Di
Wij = Fij c (4.3)


Figure 4-1: An example warehouse layout.










Therefore, if product type i is stored at '?wi k, then the total material handling

cost with dock j due to this storage is dki Wij. We define the assignment decision

variableS Zki aS binary variables to represent whether or not product type i is

assigned to Swi k.



Zkci ,if aSSignment was made, (4.4)
0,if no assignment was made.

The warehouse layout problem can now be formulated as the following integer

programming (IP) problem.

mn A
min xkC zi dkjl i~j (41.5)
i= 1 j= 1 k= 1



Subject to: (4.6)

ki=Ai Vi (4.7)


Zki 1 V (4.8)
i= 1



Zki {0,1} V, k(4.9)

This objective seeks to minimize the material handling cost between the

assigned storage areas and the warehouse docks over all product types. The

constraint 4.7 states that only Ai 1'aws~ are assigned for each product type i.

Constraint 4.8 states that only one product type can be assigned per '??w.

This formulation is an IP problem and can be solved by an IP algorithm. (For

more details, see Francis [4]) In this application, however, we will consider a special

case of the objective function 4.5 that can be solved very efficiently using a greedy

method.










Recall that Wij represent the amount of a particular product type i per

storage '??i that travels to and from a particular dock j. We assume that the m by

a matrix W = { Wa } factors, that is, there exist numbers asi and pj such that.



Way as Sy i, j(4. 10)

We may point out that not every matrix W factors, only some do. When a

matrix W factors, we can give an intuitive explanation to the factors asi and pj.

Let asi denote the total demand of product type i per storage bay over all docks,

and pj denote the frequency with which each dock j is used.



asi = -If (4. 11)



pj = Fj (4. 12)

We now have the following.


Di
Wij = asi + pj = Fj (4. 13)

The factoring assumption implies that each product type will have the same

dock frequency for a dock j as any other product type. This frequency, formerly

noted as Fij, is now noted as Fj or pj. For example, if there are two docks with

frequencies pr = 0.3 and P2 = 0.6, then the factoring assumption implies that each

product type will send 311l' of its demand per storage area through dock 1 and Iall'

through dock 2.

In this case, when the matrix W factors, we can restate the objective function

of the warehouse layout problem as follows.

mA n
minl x ki + CIi~ dkj ~j3) (.
i=1 k=1 j=1










We can now observe that since asi denotes the total demand of product type i

per storage '??i- over all docks, the greater the value of ai for any product type, the

greater is the interaction this product will have with the docks. Therefore, we want

to ensure that these product types with the highest ai values are assigned to the

. 1-
We also observe that for any '??-,- k, C=, dkji j~ denotes the average distance

traveled per unit demand for any product type stored at that '??i-, that is, with

probability pj the assigned product type will travel to dock j incurring the distance

dkj We now define the value yrk to denote the weighted distances between each '??i-

k and each dock j as follows.



7k kj j Vk(4.15)
j=1
It should now be intuitively clear that to minimize the total material handling

cost, product types with high priority weight values should be assigned to '..17-<

with small distance weights. This intuition -II__- -; the following greedy algorithm

for the warehouse layout problem.

1. Sort the weighted distanceS yk in aScending order for each '-.ns- k.

2. Sort the product type weights asi in descending order for each product type i.

3. Assign the highest weighted product type i to the first Ai b .1-< from the

sorted weighted distance list.

This algorithm ensures that the product types with the highest weight are

assigned to the '.1-
traveled by each product type to each dock should be minimized.

Let us now illustrate this algorithm with a small numerical example. Consider

a warehouse of area A = 100 and two docks (see Figure 4.2).





I I I I I I I I I


I I I I I I I I I


I I I I I I I I I


Product Type: i Area: As, Demand: De


Figure 4-2: The warehouse area is discretized into bay areas of value 1.


We assume there are five product types.

and demands are as follows.


The product type area requirements


Assuming that dock frequencies are the same for all product types, we use the

following frequency values for the two docks.


F1 = 35' F2 = I17.' .


With these values, we can now calculate the asi values for each product type i

and the yk ValueS for each '??w k. If we sort the product types in descending order

of their asi values the b we~ in ascending order of their yk ValueS, applying the greedy

algorithm would yield the following '?-w assignments.











2
4




1
3


Figure 4-3: The final warehouse layout for five products and two docks.


yk foT aSSigned 1have~ k

2, 5, 7,9,12, 15, 18, 19, 23, 28

30, 32, 34, 36, 38, 39, 40, 43, 44, 46,

48, 49, 50, 54, 57, 59, 62, 64, 67, 68

73, 76, 78, 79, 80, 83, 85, 87, 88, 89,

92, 93, 95, 97, 101, 104, 107, 109,

111, 114, 115, 116, 118, 119, 122

125, 127, 129, 131, 135, 137, 138, 139,

142, 143, 145, 147, 148, 152, 156, 157,

159, 160, 163, 164, 167, 168, 169, 173,

178, 179, 182, 184, 187, 189

191, 193, 194, 197, 198, 201, 204, 205,

206, 208, 210, 214, 215, 217, 218


300 / 10

400 / 20


500 / 25 == 20


5 450 / 25 == 18


250 / 15


16.67


Notice that the total number of 1are~~assigned to each product type is equal to

the product type areas Ai. See Figure 25.3 for the final layout for this example.










Talthe 4 1: Algforithm

1. Sort the weigrhted distances in ascending or~der_ 1 eachl "
2. Sorlt the product type werighlts in? descending orlderl 1-: eachl products type.
3. Assign the highest wveightled p-roduct type: to the first ". f rom thle sorted
we;-igh~ted distance list until the product typec arlea requilrement is satisfied.


4.1.2 Input

Using the model described in the above section, we can define the following

input .

Area of the warehouse

Number of docks

*, Dock locations

*, Dock frequencies

Number of product types

Area required per product type

Demand per product type

We consider the area of the warehouse as the total number of '.1-
for assignment. The area required per product type is basically the number of '..17-

to assign per product type.

We use one user form and two input sheets in this application to receive these

input values from the user. We do not place any bounds on these inputs. We

do give default values for a warehouse area of 30, 3 product types, and 2 docks,

however these values may be changed by the user.

4.1.3 Output

Our main output is the warehouse layout. We present this output to the user

using different color cells to represent the different product types. The product

type color representation is summarized in a legend next to the warehouse grid.

The docks are also shown in their specified locations .Il11 Il:ent to the grid. We have











*11. ?1== rao=.I $=2099ultn *


SWarehouse Layout

Tewarehouse layout problem involves allocating several different
product types to certain areas of a warehouse so thatthe cost of
material handling from docks of the warehouse is minimized.
In our program, the user specifies the area of the warehouse in number 1
of bays, places the docks around the warehouse and gives the
fqunyof their usage, and provides the demand and area required
per product type. We then solve to find the best locations of each
product type for minimum material handling cost.
For filore details, please see "Oiscrete Locallon aixI Layout Problems^ try
Francis.







Figure 4-4: The welcome sheet.


several navigational buttons as well as some resolve options, we discuss these in

more detail later.

4.2 Spreadsheets

We use four spreadsheets in this application: a welcome sheet, two input

sheets, and an output sheet. In the welcome sheet we describe the warehouse

layout problem and give an overview of what the user will input and what output

they will see di1 li-r d (see Figure 4.4). We have some images of a warehouse and

a "SI .11 button. The "Start" button is assigned to our Main sub procedure which

we will discuss later.

The next spreadsheet the user sees is the first of two input sheets. Before

the user arrives to this sheet, they will complete a user form with the area of

the warehouse, number of product types, and number of docks (we discuss the

form in more detail in the next section). From this information, we create a grid

representing the warehouse area on the first input sheet. The first input sheet

allows the user to place the docks .Il11 Il:ent to the warehouse grid. There are some

event procedures associated with the spreadsheet which allows the user to simply












Warehouse Layout: Bays and Docks Crie
TIs s the entire area of the warehouse; each urnt Is a bay. You will nowv need to pace each of your docks
around the perimeter of the warehouse area.
To pace your docks. click on a cell adjacent to one of the bays. Then complete the dock table by specifying
wchbay the dock is adjacent to and the frequency worth which this dock is used (these frequencies should surn
to100%). When you are done. click Continue.

D1
1 2 3 4 5 6 N djn Feuec
12 11 10 9 8 7 Nlumber
13 14r 15 16 17 18 D 1 1 20%/
24 23 22 21 20 19 D 2 D 2 19 80%/
25 26 27 28 29 30



Figure 4-5: The first input sheet for dock information.


click on the location of a dock in order for the dock number and formatting to

appear. We will discuss the details of these procedures later.

We also create a table for the dock information which the user must complete.

Based on the placement of the docks .Il11 Il:ent to the warehouse grid, the user

must enter the number of the '??i- each dock is ...1.1 Il:ent to. This will help us

determine the distance from each '??i- to each dock. The user must also enter the

dock frequencies at this point. The frequencies should sum to 1. In Figure 4.5, we

show an example with two docks located near '.1-
and NI I' respectively.

The user then presses the "Continue" button to navigate to the next input

sheet. The second input sheet is for information on the product types (see Figure

4.6). We create a table where the user enters the area requirement (number of

a.1-<) and demand for each product type. The sum of the number of 1-
over all product types must be less than or equal to the available number of b .1-< in

the warehouse.

In Figure 4.6, we continue the example shown in Figure 4.5. Here we have

three product types with area requirements of 2, 4, and 7 .1-< respectively. The

product type demands are 300, 500, and 600 respectively. We may note here











SProduct Information End II Solve
For each product type. enter the area required (number of bays), and the daily demand. This
dally demand signifies the priority of this product type; rt Is a key value in determining the
location of product types to reduce rnatenal handling cost.
When you are done, click Solve to see the final warehouse layout.
Product Area Required Daily I Area Available
Number (Num of Bays) Demand (Num of Bays)
1 2 300 || 30 [1
2 4 500
3 7 600




Figure 4-6: The second input sheet for product information.


that the weights for each product type would be 150, 125, and 85.7 respectively

(asi = Di/Ai). Therefore, product type 1 has the highest priority, then product type

2, then product type 3.

The user then presses the "Solve" button to run the main calculation proce-

dures and perform the greedy algorithm. The output sheet will then appear (see

Figure 4.7). The output sheet shows the final layout for all of the product types in

the warehouse area. We color each product type differently and give a legend for

this representation. In Figure 4.7, we can observe that product type 1 (which had

the highest priority) was assigned to the b we~ closes to the second dock (which had

the highest frequency).

There are some resolve options and navigational buttons here also. We will

discuss these in more detail in a later section.

Table 4-2: Summary: Spreadsheets

Welcome sheet Problem description and "Start" button.
First input sheet Dock input table, initial layout for dock placements,
"End" and "Continue" buttons.
Second input sheet Product type input table, "End" and "Solve" buttons.
Output sheet Final layout with product type **w- assignment, legend
for product type colors, resolve layout area, "End,"
"Resolve," "View Product Info" and "View Dock Info"
buttons.












Final Warehouse Layout Hesn E~ Rolve Viw rhodut nflo
Thsis the Oinal warehouse layout. Each product type representedd by a dlferent color) is assigned to Vien Docke Info
anarea of bays. Note that product types with highest demand are closest to docks of highest*

You can Resolve the problem. Klyou want to force some product type assignments before resoulvng,
chkon a product type frarn the legend and then clack the bay In the Res~olve Layout gnd. You can
alochange Dock Info or Product Info before resolrng
FINAL. LAYOUT RESOLVE LAYOUT
D1 d 0 1
D1 23 123456
12 11 10 12 11 10 9 B 7
13 i~14 13 14 15 16 17 18
24 23 D1 02 24 23 22 21 20 19 D 2
25 28 27 Il 25 26 27 28 29 30



Figure 4-7: The output sheet with its navigational buttons and resolve options.


4.3 User Interface

For this application, we have several navigational buttons, some functional

buttons, one user form, and two input sheets. We begin the application by dis-

pll li-ing the user form after the "S1 I. button is pressed from the welcome sheet.

The form prompts the user for the number of product types, number of docks, and

area of the warehouse. We ask the user to define the area of the warehouse by its

dimensions, that is, by the number of rows and columns needed to construct the

warehouse area assuming each '- or is represented by one row by one column. The

form is shown in Figure 4.8.

We use two frames to group similar text boxes together. The first frame

contains the text boxes for the number of product types and number of docks.

The second frame contains the warehouse dimensions values: number of rows and

number of columns. We have entered default values for three product types, two

docks, and a warehouse area of 30 '-17,- The "Cancel" button simply unloads the

form, leaving the user at the welcome sheet. The "OK(" button performs some error

checking and assigns the input values to corresponding variables. It then takes the

user to the first input sheet.











Please enter values for the following data:

Number of Product Typ~es: ~3

Number of Docks: 12


- Warehouse Drimensions:
{1 square unit = 1 row by 1 column):

Number of Rowns: 5
Number of Columns: 16


Cancel OK-~


Figure 4-8: The user form asks for the first input values.

The first input sheet, discussed in the previous section, has two buttons:

"End" and "Continue" (see Figure 4.5). The "End" button closes the sheet and

returns the user to the welcome sheet. The "Continue" button performs some error

checking, records the dock information the user has entered in the table, closes the

sheet, and takes the user to the next input sheet.

On the second input sheet, also discussed in the previous section, there are

also two buttons. "End" and "Solve" (see Figure 4.6). The "End" button has the

same functionality here as in the previous sheet. The "Solve" button performs

some error checking, records the product type information entered by the user in

the table, and then calls the main procedures to perform the greedy algorithm. It

then closes the sheet and takes the user to the output sheet.

On the output sheet has a few more buttons (see Figure 4.7). The "End"

button is again the same as the others. There are then two navigational buttons

which allow the user to revisit the input sheets: "View Product Info" and "View

Dock Info." These buttons simply close the output sheet and take the user to the










respective input sheet. When the user revisits an input sheet, a new button will

appear to "Go B II I: to the output sheet. This "Go B II I: button replaces the

"Continue" and "Solve" buttons.

There is also a "Resolve" button on the output sheet. This button is used

to record any changes the user has made to either of the input sheets or the

"Resolve Layout" and then resolves the problem. We will discuss the meaning of

the "Resolve Layout" and other resolve functions in a later section.

Table 4-3: Summrary: User i:

Inpurt librm nr: -. *~ of I: :h1ICts, numbrller of doickis: warehouse IE
mlensions in numbller of rows and ~olum1-ns.
First inpurt sheet DockE location in termlls of ..\. .:ent b ,--- doc:k frequien-

Second-:: :! sheet R:equrired area and d~emand- per produlrct type.
igational buittons i i i," "V~iew- Produrct Info," "iView D~ock Inlfo,"f "GCo
Bac Furnctional buttons 'i :t,??" Con-tiu" --- -: ve,' "" "Resolve. ?



4.4 Procedures

We will now outline the procedures for this application. We begin with the

Main sub procedure and variable definitions (see Figure 4.8). We define several

variables as public variables since they will be used in multiple procedures. We

have variables to represent the problem dimensions, such as the number of product

types and number of docks, some counting variables for loops, several arrays to

be used in the preparation calculations, and several variables to be used in the

algorithm.

The Main procedure begins by calling the ClearPrevious procedure (see Figure

4.9). The ClearPrevious procedure clears the cell values and formatting of all of

the sheets in the application. It also ensures that the original buttons on the input

sheets are visible and hides the "Go B II I: buttons. Lastly, it initializes some

vari ables .

































































































;--- =


































Figure 49: The MCain procedure andf public: variable delarationls.


i






: ...

i'


































Sigure 4 10: i : C ::ireviours procedure clears valures and formnattingf onl all
sheets: it ailso initializes some: variables.


The Main procedure then shows the input form (refer to Figure 4.8). The

main code associated with this form is in the Click event procedure of the "OK("

button (see Figure 4.10). This procedure performs some error checking to ensure

that all of the input values have been given. It then assigns the input values to

their corresponding variables. At this point we now know the number of product

types, number of docks, and area of the warehouse. We refer to the area of the

warehouse by the number of' 'nv which is equal to the number of rows multiplied

by the number of columns provided by the user.

The Main procedure continues by resetting some arrays used for the resolve

options and then assigning colors to the product types. Then, the NumberingBay

procedure is called. This procedure numbers the warehouse grid and computes the

X and Y coordinates of each '--- (see Figures 4.12 and 4.13).
















































Figure 4-11: T. cmdOKI( k procedure assigns thie inpnut values to their cor~re-
spondling vatriabhles.










The procedure begins by creating the initial warehouse layout. We move from

row 1 to the number of rows and from column 1 to the number of columns and

then back to column 1 and so forth, labeling each '??-,- in the warehouse area. We

set the X and Y coordinates equal to the row and column value of each created

'??--. These coordinates are stored in two arrays.

The NumberingBay procedure continues by preparing the input tables for

the docks and product types based on the input provided hv the user in the input

form. The dock table is created on the first input sheet. The product type table is

created on the second input sheet. The total number of '??-- areas is also recorded

on the second input sheet. Returning to the 1\ain procedure, we now simply take

the user to the first input sheet.

On this sheet the user is able to place the docks around the warehouse area

simply by clicking on a cell. To enable this feature, we have written an event

procedure for the SelectionChange event of the worksheet (see Figure 4.14). We

first check if the active cell is in a range where the docks are allowed to be placed.

This range can he defined as the intersection of the warehouse area with an

additional one unit circumference and all other cells. We define this intersection by

using a logical check and two Union worksheet functions.

Once we ensure that the user has clicked a cell in the allowable dock location

area, we check that they have not already placed all of the docks. If this condition

is false, then we label the cell with "D" and the current dock number.

The next procedure is the DockInfo procedure (see Figure 4.15). This proce-

dure is assigned to the "Continue" button on the first input sheet. The procedure

begins with two error checks: to ensure that the dock table has been completely

filled and to check that the dock frequencies sum to zero. We then determine the X

and Y coordinates for each dock.






















































I---



































--









;...1.1...























L L- I L


--
:- ..:.i


.IIi-ll .rl
---


F~igurle 4 12: Ii i beghirdrlig of th-e : I-:: d r







80O




























Figure 4-13: 7i end of thle ". '. ... : ,W~-- pnrcecdure.


K~nowingf that the docks are placed .Il11 Il-ent to the warehouse area and given

the '?ix- numbers to which each dock is .Il1. ..-ent, we can determine the dock

coordinates hv determining which order the .Ill1 ..-ent '?ix- is on. If the .Il1. ..-ent

'- ix- is on the top or bottom border of the warehouse area (that is, on the first or

last row), then the X coordinate should be zero or one more than the number of

rows, respectively. The Y coordinate will be the same as the Y coordinate of the

.Ill1 .l-ent 'six-. If the .Il1. ..-ent '---- is on the left or right border of the warehouse

area (that is, on the first or last column), then the Y coordinate will be zero or one

more than the number of columns, respectively. The X coordinate will be the same

as that of the .Ill1 ..-ent '?ix-.

We also record the dock frequencies into an array. Finally, we take the user to

the second input sheet.







81i














Figre 1: T. eletin~hng evntproedre nalesth usr o cic onth













Thfiie next 7 proedurioChae performietiie nals the mi aclain n calse the~lc p oedures



which execute the algorithm. This is the FinalSteps procedure (see Figure 4.16).

This procedure is assigned to the "Solve" button on the second input sheet. The

procedure begins with two error checks to ensure that the product type table has

been completely filled and that the sum of the required '.1-
the total number of '.1-
Next, the ComputeF procedure is called (see Figure 4.17). This procedure

computes the weighted distances using the recorded dock frequencies. These

distance weights are equivalent to the ?k values described in the model. We define

yik = =1 dkj- Fj and so compute these values using a loop over each "- with

their respective X and Y coordinates and a sub loop over each dock with their

respective frequency values and X and Y coordinates.





ill I-i


1. I:~ ._II i I r


~igrure 4-15: 'i i Do< i i a procedure records thec dock i



















to button cn second shee
'records in~f-rmatiio; -nlls -ther calcuati-n



errr 'th,2t r;tale uwas filledi
5 -: ..- ifi:-C 1).Value = '"" Or -i: .-_:.. .:ii ff-ae '""Ii
-; : Yu have not completeely filled out this sheet."





erro h,2t t3tal i Ere; does n"t exceed total number -
Sum = 0
For i = 1 To NProds



If SumT > N~iays Then
Msq~ox '"The sum of p~roduct areas exceeds the total number f bay's integrhue'
'"Please correct your area data and press Solve again."






Area iNF~rds) Demand NPro~ds
For i = 1 To N~FroCds

Area i) = -5-- -:. .. "f ic 1 .V value
li.et i = -.. P du t" -:. 2 lu






Call ScrtItems
Call ScrtBays
Call Assign


.. -- -e- I-CLayout'" ).visible = True
::?ee--::.l:...-: .Visible = False
.1-:e .-. .Select
.4:""aE. = True





Figure 4 16: 'T: FinatlSteps procedulre : .r : the rniain cali~culaitio ns aindl call the
procedures which execute the algorithm.


























Figure 4-17: i C~ompu~eF; pr~ocecdure: comnput~es t~he welight~ed distances based- on
the dlockr i ? :


The next procedure called from the FinalSteps procedure is the SortItems

procedure (see Figure 4.18). This is the first step of the algorithm. This procedure

will calculate the product type weights use these values to sort the product types in

descending order of importance. The product type weights are equivalent to the ai

values described in the model. We define asi = Di/Ai and so compute these values

using the recorded demand and area values; the weights are stored in a Ratio array.

We then sort the product types according to these Ratio values. We perform

the sort on an array called ItemOrder. We initialize this array such that each

product type i as an ItemOrder value i. We then search for the largest Ratio

value and move this product type to the front of the list; that is, we exchange its

ItemOrder value with the product type which has ItemOrder value equal to 1.

We continue this process but examine one less value each time. Eventually, the

ItemOrder array will signify the sorted order of the product types.

The next procedure called is the SortB .1-< procedure (see Figure 4.19). This

is the second step of the algorithm. This procedure sorts the \1-
order of their distance weights computed in the ComputeF procedure. The sorting

is done in the same manner in which the product types were sorted. This time














































For i = 1 To N~irads- 1
Fore~idr qi = iToNod

MaxI = .-.':: 1.e. -.:: C
Maxende =






MaxIrdler = I.-


Max = ~


= I~er~rderii:i
hl;i~3rSer


Fig~ure 4-18: T=: Sorthmcrs -ocedur~e calc~ulatels t~he products
sorts t~hem.


weiights andi









































Figuire 119): T~ SortBay~s algforitinnl sorts the 1..;-- in ascendiing order of their
:: weigh~ts.


however, we search for the minimum weight value in each pass. We use an array

called BayOrder to store the '??i- order.

The final procedure called from the FinalSteps procedure is the Assign

procedure (see Figure 4.20). This is the last step of the algorithm. This procedure

completes the assignment of '.1-
The procedure begins with some formatting for the final layout. There is then

a small section of code which is related to the resolve options; we will discuss this

in more detail in the next section. The assignment loop then begins. We loop

through the sorted list of product types in the descending order of their weight

values found in the Sort Items procedure. We then assign 1. .1-< to each product by










looping through the list of sorted 1' 0;< until the area requirement for the product

type has been met. To reflect that an assignment has been made, we format the

assigned my- with the color of the product type. When the assignment is finished

for the product type, we also update our legend with the product type's color and

index.

The last part of this procedure simply formats the resulting layout and creates

a "Resolve Layout" which we discuss in the next section. We now return to the

FinalSteps procedure which takes the user to the output sheet.

The only remaining procedures are for the navigational buttons (see 4.22).

These include the "End" button, "View Product Info" button, "View Dock Info"

button, and "Go B II I: buttons. Each procedure simply hides and shows the

appropriate sheets.


4.5 Resolve Options

There are two main resolve options for this DSS. The first option allows the

user to revisit the input sheets and change previously entered values. For this

option, the user would use the navigational buttons on the output sheet to return

to either of the input sheets. Suppose, for example, that the user returns to the

first input sheet (see Figure 4.23). Here they may change the location of the docks

or the frequencies of the docks. In Figure 4.23, we have changed the frequencies

from 211' and >II' to 50I' and 501' respectively. The user can then press the "Go

B II I: button to return to the output sheet.

At this point, the user can press the "R. -~l..10 button to see the new layout

with the changed dock information, or they can choose the other navigational

button to revisit the second input sheet. Let us suppose the user also revisits the

second input sheet to modify the product type information. In Figure 4.24, we have

changed the area requirements for the product types from 2, 4, and 7 to 4, 10, and