Description of this paper


9.5 Decision moDeling with spread sheets Models can be developed-(Answered)


Step-by-step Instant Solution


Course: ISEM 565-90-2016/Semester V (Late Spring) - Bus Intelligence/Dec Support Sys

    • hi?i?need?help?with?my?assignment?Answer?the?questions?Applications?9.5?(Spreadsheet?Model?Helps????Medical?Residents),?11.5(Clinical?decision?support?system?for????tendon?injuries)?its?from?the?textbook ?business intelligence and analytics systems for decision support 10th edition by sharda ramesh you need the text book to answer the above asked applications if you can get the same textbook its fine or i can send only the screen shots of the above asked applications only i asked you this?question because you did my last assignment really nice?thank you so much?

9.5 Decision moDeling with spread sheets


Models can be developed and implemented in a variety of programming languages


and systems. These range from third-, fourth-, and fifth-generation programming


languages to computer-aided software engineering (CASE) systems and other


systems that


automatically generate usable software. We focus primarily on spreadsheets (with




add-ins), modeling languages, and transparent data analysis tools. With their




and flexibility, spreadsheet packages were quickly recognized as easy-to-use


implementation software for the development of a wide range of applications in




engineering, mathematics, and science. Spreadsheets include extensive statistical,


forecasting, and other modeling and database management capabilities, functions,


and routines. As spread sheet packages evolved, add-ins were developed for


structuring and


solving specific model classes. Among the add-in packages, many were developed




DSS development. These DSS-related add-ins include Solver (Frontline Systems


Inc., and What?sBest! (a version of Lindo, from Lindo Systems, Inc.,


for performing linear and nonlinear optimization; Braincel (Jurik Research




Inc., and NeuralTools (Palisade Corp., for artificial




networks; Evolver (Palisade Corp.) for genetic algorithms; and @RISK (Palisade




for performing simulation studies. Comparable add-ins are available for free or at a





low cost. (Conduct a Web search to find them; new ones are added to the




on a regular basis.)


The spreadsheet is clearly the most popular end-user modeling tool because it


incorporates many powerful financial, statistical, mathematical, and other




Spreadsheets can perform model solution tasks such as linear programming and


regression analysis. The spreadsheet has evolved into an important tool for


analysis, planning,


and modeling (see Farasyn et al., 2008; Hurley and Balez, 2008; and Ovchinnikov




Milner, 2008). Application Case 9.4 describes an interesting application of a


spreadsheetbased optimization model in a small business.



Application Case 9.4


Showcase Scheduling at Fred Astaire East Side Dance


StudioThe Fred Astaire East Side Dance Studio in New


York City presents two ballroom showcases a year. The


studio wanted a cheap, user-friendly, and quick computer


program to create schedules for its showcases that


involved heats lasting around 75 seconds and solos


lasting around 3 minutes. The program was created using


an integer programming optimization model in Visual


Basic and Excel. The employees just have to enter the


students? names, the types of dances the students want


to participate in, the teachers the students want to dance


with, how many times the students want to do each type


of dance, what times the students are unavailable, and



what times the teachers are unavailable. This is entered


into an Excel spreadsheet. The program then uses


guidelines provided by the business to design the


schedule. The guidelines include a dance type not being


performed twice in a row if possible, a student


participating in each quarter of the showcase in order to


keep him/her active throughout, all participants in each


heat performing the same type of dance (with a


maximum of seven couples per heat), eliminating as


many one-couple heats as possible, each student and


teacher only being scheduled once per heat, and


allowing students and teachers to dance multiple times


per dance type if desired. A two-step heuristic method


wasused to help minimize the number of one-couple


heats. In the end, the program cut down the time the


employees spent creating the schedule and allowed for


changes to be calculated and made quickly as compared


to when made manually. For the summer 2007 showcase,


the system scheduled 583 heat entries, 19 dance types,


18 solo entries, 28 students, and 8 teachers. This


combination of Microsoft Excel and Visual Basic enabled


the studio to use a model-based decision support system


for a problem that could be time-consuming to solve.



Other important spreadsheet features include what-if analysis, goal seeking, data


management, and programmability (i.e., macros). With a spreadsheet, it is easy to


change a cell?s value and immediately see the result. Goal seeking is performed by



indicating a target cell, its desired value, and a changing cell. Extensive database


management can be performed with small data sets, or parts of a database can be




for analysis (which is essentially how OLAP works with multidimensional data




in fact, most OLAP systems have the look and feel of advanced spreadsheet




after the data are loaded). Templates, macros, and other tools enhance the




of building DSS.


Most spreadsheet packages provide fairly seamless integration because they read


and write common file structures and easily interface with databases and other




Microsoft Excel is the most popular spreadsheet package. In Figure 9.3, we show a




loan calculation model in which the boxes on the spreadsheet describe the


contents of


the cells, which contain formulas. A change in the interest rate in cell E7 is




reflected in the monthly payment in cell E13. The results can be observed and




immediately. If we require a specific monthly payment, we can use goal seeking to


determine an appropriate interest rate or loan amount.


Static or dynamic models can be built in a spreadsheet. For example, the monthly


loan calculation spreadsheet shown in Figure 9.3 is static. Although the problem




the borrower over time, the model indicates a single month?s performance, which





replicated. A dynamic model, in contrast, represents behavior over time. The loan


calculations in the spreadsheet shown in Figure 9.4 indicate the effect of prepayment on the principal


over time. Risk analysis can be incorporated into spreadsheets


by using built-in random-number generators to develop simulation models (see the


next chapter).


Spreadsheet applications for models are reported regularly. We will learn how to


use a spreadsheet-based optimization model in the next section.



section 9.5 revieW QuestiOns


1. What is a spreadsheet?


2. What is a spreadsheet add-in? How can add-ins help in DSS creation


and use?


3. Explain why a spreadsheet is so conducive to the development of





Part IV?Prescriptive Analytics diagram



figure 9.3 Excel Spreadsheet Static Model Example of a Simple Loan


Calculation of Monthly Payments.



figure 9.4 Excel Spreadsheet Dynamic Model Example of a Simple Loan


Calculation of Monthly Payments and the Effects


9.6 mathematical programming optimization


The basic idea of optimization was introduced in Chapter 2. Linear





(LP) is the best-known technique in a family of optimization tools called




programming; in LP, all relationships among the variables are linear. It is


used extensively in DSS (see Application Case 9.5). LP models have


many important applications in practice. These include supply chain


management, product mix decisions, routing, and so on. Special forms


of the models can be used for specific applications. For example,


Application Case 9.5 describes a spreadsheet model that was used to


create a schedule for medical interns.


Application Case 9.5












ResidentsFletcher Allen Health Care (FAHC) is a teaching


hospital that works with the University of Vermont?s


College of Medicine. In this particular case, FAHC


employs 15 residents with hopes of adding 5 more in the


diagnostic radiology program. Each year the chief


radiology resident is required to make a yearlong


schedule for all of the residents in radiology. This is a


time-consuming process to do manually because there


are many limitations on when each resident is and is not


allowed to work. During the weekday working hours, the


residents work with certified radiologists, but nights,


weekends, and holidays are all staffed by residents only.


The residents are also required to take the ?emergency


rotations,? which involve taking care of the radiology


needs of the emergency room, which is often the busiest


on weekends. The radiology program is a 4-year program,



and there are different rules for the work schedules of


the residents for each year they are there. For example,


first- and fourthyear residents cannot be on call on


holidays, second-year residents cannot be on call or


assigned ER shifts during 13-week blocks when they are


assigned to work in Boston, and third-year residents


must work one ER rotation during only one of the major


winter holidays (Thanksgiving or Christmas/New Year?s).


Also, first-year residents cannot be on call until after


January 1, and fourth-year residents cannot be on call


after December 31, and so on. The goal that the various


chief residents have each year is to give each person the


maximum number of days between on-call days as is


possible. Manually, only 3 days between on-call days was


the most a chief resident had been able to accomplish.In


order to create a more efficient method of creating a


schedule, the chief resident worked with an MS class of


MBA students to develop a spreadsheet model to create


the schedule. To solve this multiple-objective decisionmaking problem, the class used a constraint method


made up of two stages. The first stage was to use the


spreadsheet created in Excel as a calculator and to not


use it for optimizing. This allowed the creators ?to


measure the key metrics of the residents? assignments,


such as the number of days worked in each category.?


The second stage was an optimization model, which was


layered on the calculator spreadsheet. Assignment


constraints and the objective were added. The Solver


engine in Excel was then invoked to find a feasible


solution. The developers used Premium Solver by



Frontline and the Xpress MP Solver engine by Dash


Optimization to solve the yearlong model. Finally, using


Excel functions, the developers converted the solution


for a yearlong schedule from zeros and ones to an easyto-read format for the residents. In the end, the program


could solve the problem of a schedule with 3 to 4 days in


between on calls instantly and with 5 days in between on


calls (which was never accomplished manually). Source:


Based on A. Ovchinnikov and J. Milner, ?Spreadsheet


Model Helps to Assign Medical Residents at the


University of


Vermont?s College of Medicine,? Interfaces, Vol. 38, No.


4, July/


August 2008, pp. 311?323.



Sharda, Ramesh; Delen, Dursun; Turban, Efraim;


Aronson, Janine; Liang, Ting Peng. Business Intelligence


and Analytics: Systems for Decision Support (Page 407).


Pearson Education. Kindle Edition.



11.5 ApplicAtions oF expert systems



ES have been applied to many business and technological areas to


support decision making. Application Case 11.3 shows a recent realworld application of ES. Table 11.2 shows some representative ES and


their application domains.



classical Applications of es



Early ES applications, such as DENDRAL for molecular structure


identification and MYCIN


for medical diagnosis, were primarily in the science domain.


XCON for configuration of the VAX computer system at Digital


Equipment Corp. (a major producer of minicomputers around


1990 that was later taken over by Compaq) was a successful


example in business.dendrAl The DENDRAL project was initiated


by Edward Feigenbaum in 1965. It used a set of knowledge- or


rule-based reasoning commands to deduce the likely molecular


structure of organic chemical compounds from known chemical


analyses and mass spectrometry data.



determine a proper credit line. Rules in the knowledge base can also


help assess risk and risk-management policies. These kinds of systems


are used in over one-third of the top 100 commercial banks in the


United States and Canada.


pension Fund Advisors Nestl? Foods Corporation has developed an ES


that provides information on an employee?s pension fund status. The


system maintains an up-to-date knowledge base to give participants


advice concerning the impact of regulation changes and conformance


with new standards. A system offered on the Internet at the Pingtung


Teacher?s College in Taiwan has functions that allow participants to plan


their retirement through a what-if analysis that calculates their pension


benefits under different scenarios.


AutomAted help desks BMC Remedy ( offers HelpDeskIQ,


a rule-based help desk solution for small businesses. This browserbased tool enables small businesses to deal with customer requests


more efficiently. Incoming e-mails automatically pass into HelpDeskIQ?s


business rule engine. The messages are sent to the proper technician,


based on defined priority and status. The solution assists help desk


technicians in resolving problems and tracking issues more effectively.


Areas for es Applications


As indicated in the preceding examples, ES have been applied


commercially in a number


of areas, including the following:




Finance. Finance ES include insurance evaluation, credit analysis,


tax planning,



fraud prevention, financial report analysis, financial planning, and








Data processing. Data processing ES include system planning,


equipment selection, equipment maintenance, vendor evaluation, and


network management.




Marketing. Marketing ES include customer relationship


management, market


analysis, product planning, and market planning.




Human resources. Examples of human resources ES are human


resources planning, performance evaluation, staff scheduling, pension


management, and legal






Manufacturing. Manufacturing ES include production planning,


quality management, product design, plant site selection, and


equipment maintenance and






Homeland security. Homeland security ES include terrorist threat




and terrorist finance detection.




Business process automation. ES have been developed for help


desk automation, call center management, and regulation enforcement.




Healthcare management. ES have been developed for


bioinformatics and other


healthcare management issues.



Now that you are familiar with a variety of different ES applications, it is


time to


look at the internal structure of an ES and how the goals of the ES are




sectiOn 11.5 revieW QuestiOns


1. What is MYCIN?s problem domain?


2. Name two applications of ES in finance and describe their benefits.


3. Name two applications of ES in marketing and describe their




4. Name two applications of ES in homeland security and describe


their benefits.


11.6 structure oF expert systems


ES can be viewed as having two environments: the development


environment and the


consultation environment (see Figure 11.4). An ES builder uses the


development environment to build the necessary components of the


ES and to populate the knowledge


base with appropriate representation of the expert knowledge. A


nonexpert uses the


consultation environment to obtain advice and to solve problems using


the expert


knowledge embedded into the system. These two environments can be


separated at the


end of the system development process.



The three major components that appear in virtually every ES are the




base, the inference engine, and the user interface. In general, though,


an ES that interacts


with the user can contain the following additional components:





Knowledge acquisition subsystem






Blackboard (workplace)






Explanation subsystem (justifier)






Knowledge-refining system



Currently, most ES do not contain the knowledge refinement


component. A brief


description of each of these components follows.


knowledge Acquisition subsystem


Knowledge acquisition is the accumulation, transfer, and


transformation of problem-solving expertise from experts or


documented knowledge sources to a computer



Figure 11.4 Structure/Architecture of an Expert




program for constructing or expanding the knowledge base. Potential sources of


knowledge include human experts, textbooks, multimedia documents, databases


(public and private), special research reports, and information available on the


Web.Currently, most organizations have collected a large volume of data, but the



organization and management of organizational knowledge are limited.


Knowledge acquisition deals with issues such as making tacit knowledge explicit


and integrating knowledge from multiple sources.


Acquiring knowledge from experts is a complex task that often creates a




in ES construction. In building large systems, a knowledge engineer, or knowledge


elicitation expert, needs to interact with one or more human experts in building


the knowledge base. Typically, the knowledge engineer helps the expert structure


the problem area by interpreting and integrating human answers to questions,


drawing analogies, posing counterexamples, and bringing conceptual difficulties to




knowledge Base


The knowledge base is the foundation of an ES. It contains the relevant


knowledge necessary for understanding, formulating, and solving problems. A


typical knowledge base may include two basic elements: (1) facts that describe


the characteristics of a specific problem situation (or fact base) and the theory of


the problem area and (2) special heuristics or rules (or knowledge nuggets) that


represent the deep expert knowledge to solve specific problems in a particular


domain. Additionally, the inference engine can include general-purpose problemsolving and decision-making rules (or meta-rules?rules about how to process


production rules).It is important to differentiate between the knowledge base of


an ES and the knowledge base of an organization. The knowledge stored in the


knowledge base of an ES is often represented in a special format so that it can be


used by a software program (i.e., an expert system shell) to help users solve a


particular problem. The organizational knowledge base, however, contains various


kinds of knowledge in different formats (most of which is represented in a way


that it can be consumed by people) and may be stored in different places. The


knowledge base of an ES is a special case and only a very small subset of an


organization?s knowledge base.


inference engine


The ?brain? of an ES is the inference engine, also known as the control structure


or the rule interpreter (in rule-based ES). This component is essentially a



computer program that provides a methodology for reasoning about information


in the knowledge base and on the blackboard to formulate appropriate


conclusions. The inference engine provides directions about how to use the


system?s knowledge by developing the agenda that organizesorganizes and


controls the steps taken to solve problems whenever a consultation takes place.


It is further discussed in Section 11.7.


user interface


An ES contains a language processor for friendly, problem-oriented


communication between the user and the computer, known as the user interface.


This communication can best be carried out in a natural language. Due to


technological constraints, most existing systems use the graphical or textual


question-and-answer approach to interact with the user.


Blackboard (Workplace)


The blackboard is an area of working memory set aside as a database for


description of the current problem, as characterized by the input data. It is also


used for recording intermediate results, hypotheses, and decisions. Three types of


decisions can be recorded on the blackboard: a plan (i.e., how to attack the


problem), an agenda (i.e., potential actions


awaiting execution), and a solution (i.e., candidate hypotheses and alternative


courses of


action that the system has generated thus far).


Consider this example. When your car fails to start, you can enter the symptoms


of the failure into a computer for storage in the blackboard. As the result of an


intermediate hypothesis developed in the blackboard, the computer may then


suggest that you do some additional checks (e.g., see whether your battery is


connected properly) and ask you to report the results. This information is also


recorded in the blackboard. Such an iterative process of populating the


blackboard with values of hypotheses and facts continues until the reason for the


failure is identified.


explanation subsystem (Justifier)



The ability to trace responsibility for conclusions to their sources is crucial both in


the transfer


of expertise and in problem solving. The explanation subsystem can trace such


responsibility and explain the ES behavior by interactively answering questions


such as these:





Why was a certain question asked by the ES?






How was a certain conclusion reached?






Why was a certain alternative rejected?





What is the complete plan of decisions to be made in reaching the


conclusion? For


example, what remains to be known before a final diagnosis can be determined?


In most ES, the first two questions (why and how) are answered by showing the


rule that required asking a specific question and showing the sequence of rules


that were used (fired) to derive the specific recommendations,


respectively.knowledge-refining systemHuman experts have a knowledge-refining


system; that is, they can analyze their own knowledge and its effectiveness, learn


from it, and improve on it for future consultations. Similarly, such evaluation is


necessary in expert systems so that a program can analyze the reasons for its


success or failure, which could lead to improvements resulting in a more accurate


knowledge base and more effective reasoning.The critical component of a


knowledge refinement system is the self-learning mechanism that allows it to


adjust its knowledge base and its processing of knowledge based on the


evaluation of its recent past performances. Such an intelligent component is not


yet mature enough to appear in many commercial ES tools. Application Case 11.4


illustrates another application of expert systems in healthcare.



Application Case 11.4


Diagnosing Heart Diseases by Signal Processing Auscultation is


the science of listening to the sounds of internal body organs, in



this case the heart. Skilled experts can make diagnoses using this


technique. It is a noninvasive screening method of providing


valuable information about the conditions of the heart and its


valves, but it is highly subjective and depends on the skills and


experience of the listener. Researchers from the Department of


Electrical & Electronic Engineering at Universiti Teknologi


Petronas have developed an Exsys Corvid expert system, SIPMES


(Signal Processing Module Integrated Expert System) to analyze


digitally processed heart sound.The system utilizes digitized heart


sound algorithms to diagnose various conditions of the heart.


Heart sounds are effectively acquired using a digital electronic


stethoscope. The heart sounds were collected from the Institut


Jantung Negara (National Heart Institute) in Kuala Lumpur and


the Fatimah Ipoh Hospital in Malaysia. A total of 40 patients age


16 to 79 years old with various pathologies were used as the


control group, and to test the validity of the system using their


abnormal heart sound samples and other patient medical


data.The heart sounds are transmitted using a wireless link to a


nearby workstation that hosts the Signal Processing Module


(SPM). The SPM has the capability to segment the stored heart


sounds into individual cycles and identifies the important cardiac


events.The SPM data was then integrated with the Exsys Corvid


knowledge automation expert system. The rules in the system


use expert physician reasoning knowledge, combined with


information acquired from medical journals, medical textbooks,


and other noted publications on cardiovascular diseases (CVD).


The system provides the diagnosis and generates a list of diseases


arranged in descending order of their probability of


occurrence.SIPMES was designed to diagnose all types of


cardiovascular heart diseases. The system can help general


physicians diagnose heart diseases at the earliest possible stages


under emergency situations where expert cardiologists and


advanced medical facilities are not readily available.The diagnosis


made by the system has been counterchecked by senior



cardiologists, and the results coincide with these heart experts. A


high coincidence factor of 74 percent has been achieved using




Questions for Discussion


1. List the major components involved in building


SIPMES and briefly comment on them.


2. Do expert systems like SIPMES eliminate the


need for human decision making?


3. How often do you think that the existing expert


systems, once built, should be changed?


What We can Learn from this application caseMany expert


systems are prominently being used in the field of medicine.


Many traditional diagnostic procedures are now being built into


logical rulebased systems, which can readily assist the medical


staff in quickly diagnosing the patient?s condition of disease.


These expert systems can help in saving the v...


Paper#9256247 | Written in 27-Jul-2016

Price : $22