66,99 €
Mehr erfahren.
- Herausgeber: John Wiley & Sons
- Kategorie: Fachliteratur
- Serie: Wiley Series in Operations Research and Management Science
- Sprache: Englisch
Explore the fundamentals of Multi-Criteria Decision Analysis with help from Excel® and R In Smart Decisions: A Structured Approach to Decision Analysis using MCDA, a distinguished team of decision-making specialists delivers a comprehensive and insightful exploration of the fundamentals of Multi-Criteria Decision Analysis methods. The book offers guidance on modeling decision problems using some of the most powerful methods in operations research. Each chapter introduces a core MCDA method and guides the reader through a step-by-step approach to the implementation of the method using Microsoft® Excel® and then using R, a popular analytical language. The book also includes: * A thorough, step-by-step guide to Multi-Criteria Decision Analysis methods and the application of these methods in Microsoft Excel and R * Extensive illustrations, R code, and software screenshots to aid the reader's understanding of the concepts discussed within * A starter's guide to Excel and R programming Perfect for graduate students in MBA programs and business schools, Smart Decisions: A Structured Approach to Decision Analysis Using MCDA is also an ideal resource for practitioners who apply MCDA in business, finance, applied mathematics, and engineering.
Sie lesen das E-Book in den Legimi-Apps auf:
Seitenzahl: 200
Veröffentlichungsjahr: 2024
Ähnliche
Wiley Series inOPERATIONS RESEARCH AND MANAGEMENT SCIENCE
Founding Series EditorJames J. Cochran, University of Alabama
Operations Research and Management Science (ORMS) is a broad, interdisciplinary branch of applied mathematics concerned with improving the quality of decisions and processes and is a major component of the global modern movement towards the use of advanced analytics in industry and scientific research. The Wiley Series in Operations Research and Management Science features a broad collection of books that meet the varied needs of researchers, practitioners, policy makers, and students who use or need to improve their use of analytics. Reflecting the wide range of current research within the ORMS community, the Series encompasses application, methodology, and theory and provides coverage of both classical and cutting edge ORMS concepts and developments. Written by recognized international experts in the field, this collection is appropriate for students as well as professionals from private and public sectors including industry, government, and nonprofit organization who are interested in ORMS at a technical level. The Series is comprised of four sections: Analytics; Decision and Risk Analysis; Optimization Models; and Stochastic Models.
Smart Decisions
A Structured Approach to Decision Analysis Using MCDA
Dr. Richard Edgar Hodgett
University of LeedsLeeds, UK
Dr. Sajid Siraj
University of LeedsLeeds, UK
Dr. Ellen Louise Hogg
University of LeedsLeeds, UK
This edition first published 2024
© 2024 John Wiley and Sons Ltd
All rights reserved. No part of this publication may be reproduced, stored in a retrieval system, or transmitted, in any form or by any means, electronic, mechanical, photocopying, recording or otherwise, except as permitted by law. Advice on how to obtain permission to reuse material from this title is available at http://www.wiley.com/go/permissions.
The right of Richard E. Hodgett, Sajid Siraj, and Ellen Louise Hogg to be identified as the authors of this work has been asserted in accordance with law.
Registered Office(s)
John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, USA
John Wiley & Sons Ltd, The Atrium, Southern Gate, Chichester, West Sussex, PO19 8SQ, UK
For details of our global editorial offices, customer services, and more information about Wiley products visit us at www.wiley.com.
Wiley also publishes its books in a variety of electronic formats and by print-on-demand. Some content that appears in standard print versions of this book may not be available in other formats.
Trademarks: Wiley and the Wiley logo are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affiliates in the United States and other countries and may not be used without written permission. All other trademarks are the property of their respective owners. John Wiley & Sons, Inc. is not associated with any product or vendor mentioned in this book.
Limit of Liability/Disclaimer of Warranty
While the publisher and authors have used their best efforts in preparing this work, they make no representations or warranties with respect to the accuracy or completeness of the contents of this work and specifically disclaim all warranties, including without limitation any implied warranties of merchantability or fitness for a particular purpose. No warranty may be created or extended by sales representatives, written sales materials or promotional statements for this work. This work is sold with the understanding that the publisher is not engaged in rendering professional services. The advice and strategies contained herein may not be suitable for your situation. You should consult with a specialist where appropriate. The fact that an organization, website, or product is referred to in this work as a citation and/or potential source of further information does not mean that the publisher and authors endorse the information or services the organization, website, or product may provide or recommendations it may make. Further, readers should be aware that websites listed in this work may have changed or disappeared between when this work was written and when it is read. Neither the publisher nor authors shall be liable for any loss of profit or any other commercial damages, including but not limited to special, incidental, consequential, or other damages.
A catalogue record for this book is available from the Library of Congress
Hardback ISBN: 9781119309338; ePub ISBN: 9781119309321; ePDF ISBN: 9781119309352
Cover image: © sorbetto/Getty Images
Cover design by Wiley
Set in 9.5/12.5pt STIXTwo Text by Integra Software Services, Pondicherry, India
Contents
Cover
Series Page
Title Page
Copyright Page
Preface
Acknowledgements
Chapter 1 – Introduction
1.1 How to Structure Your Decisions?
1.2 Different Stages in Decision-making
Chapter 2 – Get Started with Excel and R
2.1 Get Started with R
2.1.1 Types of Data in R
2.1.2 Work with Data in R
2.2 Get Started with Excel
2.2.1 Freeze Panes
2.2.2 Excel Formulas
2.2.3 Formatting Cells
2.2.4 Conditional Formatting
2.2.5 IF Statements
2.2.6 Charts
2.2.7 Searching and Sorting
2.2.8 Pivot Tables
2.2.9 Excel Solver
Chapter 3 – Direct Rating Methods
3.1 Select a House Using a Direct Rating Method in Excel
3.2 Select a House Using a Direct Rating Method in R
3.3 Further Problems to Test Your Direct Rating Method Skills
3.3.1 Purchase a New Laptop Using a Direct Rating Method
3.3.2 Select Manufacturing Equipment Using a Direct Rating Method
Chapter 4 – Pairwise Comparison Methods
4.1 Select a Candidate to Hire Using AHP in Excel
4.2 Select a Candidate to Hire Using AHP in R
4.3 Further Problems to Test Your AHP Skills
4.3.1 Select Equipment to Build Using AHP
4.3.2 Select a Server to Buy Using AHP
Chapter 5 – Ideal Point Methods
5.1 Select a Car to Purchase Using TOPSIS in Excel
5.2 Select a Car to Purchase Using TOPSIS in R
5.3 Further Problems to Test Your TOPSIS Skills
5.3.1 Select Office Space Using TOPSIS
5.3.2 Select Venue for Lunch Using TOPSIS
Chapter 6 – Outranking Methods
6.1 Introduction to ELECTRE
6.1.1 Concordance and Discordance
6.1.2 Credibility
6.1.3 Distillation
6.2 ELECTRE in Microsoft Excel
6.3 ELECTRE in R
6.4 Further Problems to Test Your ELECTRE Skills
Chapter 7 – PROMETHEE
7.1 Theme Park
7.2 PROMETHEE in Excel
7.3 PROMETHEE in R
7.4 Further Problems to Test Your PROMETHEE Skills
Chapter 8 – Goal Programming
8.1 Select How Many Products to Manufacture Using Goal Programming in Excel
8.2 Select How Many Products to Manufacture Using Goal Programming in R
8.3 Further Problems to Test Your Goal Programming Skills
Chapter 9 – Evolutionary Optimisation
9.1 European Road Trip
9.2 Model the European Road Trip in Excel
9.3 Model the European Road Trip in R
9.4 Further Problems to Test Your Evolutionary Optimisation Skills
Chapter 10 – Dealing with Uncertainty
10.1 Select the Best Way to Cut Down a Tree in Excel
10.2 Select the Best Way to Cut Down a Tree in R
10.3 Further Problems to Test Your SURE Skills
10.3.1 Project Planning
10.3.2 Taking a Job
Index
End User License Agreement
List of Tables
CHAPTER 01
Table 1.1 Examples of evaluation...
Table 1.2 The typical...
Table 1.3 Example of...
CHAPTER 03
Table 3.1 A table of...
Table 3.3 Common ways...
Table 3.4 Decision table...
Table 3.5 Decision table...
CHAPTER 04
Table 4.1 Values for RCI.
Table 4.2 Pairwise criteria...
Table 4.3 Pairwise comparisons...
CHAPTER 05
Table 5.1 Decision table...
CHAPTER 06
Table 6.1 Performance...
CHAPTER 07
Table 7.1 Positive...
Table 7.2 Summary...
Table 7.3 Expert...
Table 7.4 Preference...
CHAPTER 09
Table 9.1 Explanation...
CHAPTER 10
Table 10.1 Decision table...
List of Illustrations
CHAPTER 01
Figure 1.1 Steps in structured...
CHAPTER 02
Figure 2.1 The R Studio...
Figure 2.2 Types of data...
Figure 2.3 The mtcars data...
Figure 2.4 Save the mtcars...
Figure 2.5 Select Freeze...
Figure 2.6 Select Format...
Figure 2.7 Set the cell as...
Figure 2.8 Add a new conditional...
Figure 2.9 Set a new conditional...
Figure 2.10 Add different...
Figure 2.11 Drag from...
Figure 2.12 Create a 2D...
Figure 2.13 Select Axis...
Figure 2.14 Select Axis...
Figure 2.15 Final plot of...
Figure 2.16 Select Custom Sort...
Figure 2.17 Select PivotTable...
Figure 2.18 Create PivotTable...
Figure 2.19 Find the menu...
Figure 2.20 Solver added...
Figure 2.21 Formatted for...
Figure 2.22 Add a Solver...
Figure 2.23 Solver Parameters...
Figure 2.24 Add an integer...
CHAPTER 03
Figure 3.1 Representation...
Figure 3.2 Decision table...
Figure 3.3 Decision table...
Figure 3.4 Decision table...
Figure 3.5 Decision table...
Figure 3.6 Weighted sum...
Figure 3.7 Quick access...
Figure 3.8 Add a vertical...
Figure 3.9 Weighted sum...
Figure 3.10 Real values...
Figure 3.11 Real values...
Figure 3.12 Weighted sum...
CHAPTER 04
Figure 4.1 Overview of AHP...
Figure 4.2 Overview of storing...
Figure 4.3 Pairwise comparison...
Figure 4.4 Names of the five...
Figure 4.5 Criteria weights...
Figure 4.6 Squared criteria...
Figure 4.7 Adding cell...
Figure 4.8 Calculating...
Figure 4.9 Calculating...
Figure 4.10 Calculating...
Figure 4.11 Calculating...
Figure 4.12 Format of the...
Figure 4.13 Complete tables...
Figure 4.14 Results of the AHP...
Figure 4.15 Results of the AHP...
CHAPTER 05
Figure 5.1 Chebyshev, Euclidean...
Figure 5.2 Example of how...
Figure 5.3 Decision table...
Figure 5.4 Decision table...
Figure 5.5 Normalised...
Figure 5.6 Weighted normalised...
Figure 5.7 Add a new conditional...
Figure 5.8 Add the formatting...
Figure 5.9 Add the second...
Figure 5.10 Weighted normalised...
Figure 5.11 Conditional...
Figure 5.12 Weighted normalised...
Figure 5.13 Separation...
Figure 5.14 Separation...
Figure 5.15 Separations...
Figure 5.16 TOPSIS results...
Figure 5.17 Stephen made...
CHAPTER 06
Figure 6.1 Visualisation...
Figure 6.2 Visual representation...
Figure 6.3 Visual illustration...
Figure 6.4 Descending...
Figure 6.5 Schools...
Figure 6.6 Linear equation...
Figure 6.7 Calculating...
Figure 6.8 Calculating...
Figure 6.9 Calculating...
Figure 6.10 Calculating...
Figure 6.11 Calculating...
Figure 6.12 Pre-processing...
Figure 6.13 Descending...
Figure 6.14 Descending...
Figure 6.15 Ascending...
CHAPTER 07
Figure 7.1 Preference...
Figure 7.2 Understanding...
Figure 7.3 Data entry...
Figure 7.4 Calculating...
Figure 7.5 Calculating...
Figure 7.6 Adding...
Figure 7.7 Calculating...
Figure 7.8 Calculating...
CHAPTER 08
Figure 8.1 Three different...
Figure 8.2 How to navigate...
Figure 8.3 How to enable...
Figure 8.4 Data menu...
Figure 8.5 Spreadsheet...
Figure 8.6 Spreadsheet...
Figure 8.7 Completed Solver...
Figure 8.8 Solution for...
Figure 8.9 Spreadsheet...
Figure 8.10 Solution for...
Figure 8.11 Spreadsheet...
Figure 8.12 Solution for...
Figure 8.13 Spreadsheet...
Figure 8.14 Solver parameters...
Figure 8.15 Solution for...
Figure 8.16 Spreadsheet...
Figure 8.17 Solver parameters...
Figure 8.18 Solution for...
Figure 8.19 Spreadsheet...
Figure 8.20 Solution for...
CHAPTER 09
Figure 9.0 Guinness...
Figure 9.1 Distance...
Figure 9.2 Identifying...
Figure 9.3 Completed...
Figure 9.4 Distance matrix...
Figure 9.5 Constraints...
Figure 9.6 Solver settings.
Figure 9.7 Evolutionary...
Figure 9.8 Solver results...
Figure 9.9 Updated travel...
Figure 9.10 Solution route...
Figure 9.11 Solution found...
CHAPTER 10
Figure 10.1 Example of...
Figure 10.2 Overview...
Figure 10.3 Decision...
Figure 10.4 Table with...
Figure 10.5 VBA code...
Figure 10.6 Table for...
Figure 10.7 Simulations for...
Figure 10.8 Workbook with...
Figure 10.9 Normalising...
Figure 10.10 Creating a...
Figure 10.11 The results...
Figure 10.12 SURE results...
Figure 10.13 SURE results...
Figure 10.14 The output of...
Figure 10.15 Decision data...
Figure 10.16 Decision data...
Guide
Cover
Series Page
Title Page
Copyright Page
Table of Contents
Preface
Acknowledgements
Begin Reading
Index
End User License Agreement
Pages
i
ii
iii
iv
v
vi
vii
viii
ix
x
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
Preface
Decision-making is part of daily life, from choosing what to have for breakfast to choosing the places and route of your holiday next year. Most decisions are made through intuition without a second thought, but some decisions require a more structured approach as their outcomes could affect your life, your family’s lives or the people you work with. The most important decisions in life require you to consider multiple criteria, objectives or goals to identify the best alternative(s) available to you. This is not straightforward as criteria, objectives and goals are often conflicting and have different levels of importance. Luckily, many different Multi-Criteria Decision Analysis (MCDA) methods have emerged to help rank, sort and evaluate decision problems. These methods have been discussed extensively in books and journal articles over the past 50 years but often in a way that is difficult to understand or implement in practice. Much of the content in this book originated in the authors’ teaching material which has been very well received by current and former students on the MSc in Business Analytics and Decision Science program at Leeds University Business School. Many of our former students have gone on to use the methods in practice, helping many companies in different industries and sectors all around the world.
This book introduces the reader to several different MCDA approaches, explaining how they work and what unique features they have. It then presents step-by-step instructions of how to model a commonly faced decision such as choosing a car to purchase or selecting a school for your son or daughter to attend using Microsoft Excel and R. Naturally specialist software is available for the majority of MCDA methods, but software often gets superseded with new software and it doesn’t teach you what’s going on behind the buttons you press. Microsoft Excel is one of the most used spreadsheet software packages in business which has functionality for making calculations and producing graphical outputs – a perfect environment for modelling MCDA problems. That being said, modelling decisions in Microsoft Excel can take some time and when time is a constraint it’s also nice to know how to quickly model a MCDA problem in R. R is a free, open source programming language that has gained massively in popularity in recent years. It allows for the use of many external packages which includes the MCDA package for R that contains functionality for many popular MCDA methods. This means that you will only need to learn to use one software interface to quickly model most MCDA problems and will consequently learn to use one of the most popular1 analytical programming languages and highly paid2 IT skills today.
Notes
1
According to the IEEE, R was the 6th most popular programming language in 2020.
2
DICE named R as one of the highest-paying tech skills earning a salary of $126,249 in 2015.
Acknowledgements
The authors would like to thank the Centre for Decision Research (CDR) at Leeds University Business School (LUBS) for hiring three great members of staff to join their team of esteemed academics. There are so many wonderful people in CDR and at LUBS but the authors would like to specially thank Professor Barbara Summers and Professor Alan Pearman for their time, guidance and wisdom.
Dr Richard Hodgett would like to dedicate this book to his mother Susan, his father Stephen, his sister Sarah and his wonderful wife Louise for making him the man he is today. He would also like to make a special dedication to his wonderful daughter Sophie and son Thomas.
Dr Sajid Siraj would like to dedicate this book to his mother Zamurrad, his father Siraj, and his life partner Hina. He would also like to dedicate this work to his children Arham, Istafa and Abeeha, who fill his life with joy, fun, and sometimes misery.
Dr Louise Hogg would like to dedicate this book to her parents Lynn and Ian, sister Sarah, husband Richard and the most amazing children, Sophie and Tom.
1 Introduction
Welcome to Smart Decisions where you will learn about many ways to model and solve complex decision problems using Multi-Criteria Decision Analysis (MCDA). Each chapter will take you through a simple and common decision problem that most people face in their lifetimes, showing you how to solve it with a structured decision-making methodology in both Microsoft Excel and R. Microsoft Excel is the primary tool used in business and education for making spreadsheets and interactive formulaic templates while R is a popular tool used by specialists in analytics, data science and statistics. If you are not familiar with either Excel or R, don’t worry! Chapter 2 is dedicated to telling you all about these software packages and how to use them. Trust me you will be an Excel and R whizz-kid in no time. That being said, this is not the primary aim of this book. The core of this book is to teach you about all of the wonderful and powerful decision-making methodologies out there which will help you justify and make better decisions at work and at home.
Let’s start by discussing a hypothetical decision problem. Imagine you are a student who has just graduated and after years of partying and hard work you now have to decide what to do with your life. You are considering three distinct options; (1) find a job, (2) do further studies, or (3) start your own business. Your parents tell you to find a job related to your degree but your university professor suggests that you should do further studies before starting a professional career. To make matters more difficult, your friend, a recently established entrepreneur, has a great business idea and suggests you become a partner in their business. How do you decide what to do?
There are lots of interesting behavioural aspects to consider here, such as do you unconsciously favour the first option you discussed with your friend (this is referred to as anchoring) or do you prefer to stay in education as you haven’t worked or started a business before (referred to as familiarity bias)? Although it is very important to acknowledge and understand the behavioural aspects of human decision-making, this will be rarely discussed in this book as the focus here is on the process of decision-making and not on the psychology of decision-making. That being said, it is important to understand both areas of decision-making and therefore if you want to read more about behavioural decision-making we recommend reading Thinking Fast and Slow by Daniel Kahneman, Psychology of Judgment and Decision Making by Scott Plous and Preference, Belief, and Similarity by Amos Tversky.
Most people who study decision-making now agree that there are two systems of decision-making, system 1 which is based on intuition or gut, and system 2 which is controlled, conscious and requires considerable effort (and time). We will be focusing on the methods and techniques that can be used for important and complex decisions that fit within system 2, or as we like to call it methods for structured decision-making.
Going back to the example we discussed about the recent graduate choosing what to do with their life. This is a particular problem where alternative options are already known to the decision-maker and the decision problem is to evaluate each of these options in order to select one of these alternatives based on several different criteria. This is referred to as an evaluation problem where the ultimate goal is to obtain a ranking or rating for each alternative. Other examples of such problems would be a regulatory authority wishing to publish the ranking of all universities in a country or a food agency seeking to rate all food shops from 1 (inadequate) to 5 (outstanding).
Another category of decision problems would be those where we search for a feasible solution which is not explicitly known to us. This category of problems can be labelled as design problems, for example, product pricing or time-tabling problems. A product can be priced with any value on a continuum but the decision-maker is interested in finding the most feasible price on this continuum. Similarly, one may find it difficult to manually schedule a timetable with no conflicts whatsoever, and therefore seek to find a feasible solution. In these two examples, the former one involves selection of a single value while the latter can be visualised as the selection of multiple values. The problems involving multiple values (sometimes called multivariate) can also be referred to as allocation problems. Table 1.1 summarises these categories of decision-making problems along with an example for each possible type of problems.
Table 1.1 Examples of evaluation and design decision problems.
Evaluation
Design
Selection
Hiring a candidate
Product pricing
Ranking
Ranking universities
Rating
Rating hotels
Allocation
Allocate money and possessions in a will
Time-tabling classrooms
Another important categorisation is to consider the number of decision-makers. In the case of two or more decision-makers, conflicts may occur, usually due to different preferences, interests, or each person’s level of knowledge. By contrast, those decisions involving a single decision-maker may not face these kinds of conflicts, although conflicts among different objectives/criteria may still occur, for example, searching to buy the cheapest house but wanting to live in a nice area.
1.1 How to Structure Your Decisions?
Whilst there can be many ways to structure a decision problem, Hammond J.S., Keeney R.L., and Raiffa H. (2002) proposed a very nice and simple framework known as PrOACT, which is an acronym for Problem, Objectives, Alternatives, Consequences, and Trade-offs.
Problem
