Description of this paper

Loading

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

Description

Step-by-step Instant Solution


Question

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

 

their

 

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

 

strength

 

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

 

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

 

business,

 

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

 

for

 

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

 

Inc.,

 

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

 

lindo.com)

 

for performing linear and nonlinear optimization; Braincel (Jurik Research

 

Software,

 

Inc., jurikres.com) and NeuralTools (Palisade Corp., palisade.com) for artificial

 

neural

 

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

 

Corp.)

 

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

 

very

 


 

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

 

marketplace

 

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

 

functions.

 

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

 

and

 

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

 

imported

 

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

 

cubes;

 

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

 

software

 

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

 

productivity

 

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

 

tools.

 

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

 

simple

 

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

 

immediately

 

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

 

analyzed

 

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

 

affects

 

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

 

is

 


 

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

 

DSS.

 


 

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

 

programming

 


 

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

 

mathematical

 

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

 

Spreadsheet

 

Model

 

Helps

 

Assign

 

Medical

 

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 (remedy.com) 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

 

performance

 

evaluation.

 

?

 

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

 

advising.

 

?

 

Manufacturing. Manufacturing ES include production planning,

 

quality management, product design, plant site selection, and

 

equipment maintenance and

 

repair.

 

?

 

Homeland security. Homeland security ES include terrorist threat

 

assessment

 

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

 

achieved.

 

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

 

benefits.

 

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

 

knowledge

 

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

 

System.

 

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

 

bottleneck

 

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

 

light.

 

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

 

SIPMES.

 

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 : $16
SiteLock