Spreadsheet Modeling for Business Decisions

Spreadsheet Modeling for Business Decisions

by John F Kros

Other Format(New Edition)

$264.00 View All Available Formats & Editions

Temporarily Out of Stock Online

Eligible for FREE SHIPPING


Spreadsheet Modeling for Business Decisions by John F Kros

This new, multi-functional and interactive text integrates the fundamentals of spreadsheet modeling and quantitative business decision-making with up-to-date examples, exercises, case studies and software. A unique suite of pedagogical features encourages the development of critical thinking and effective communication of results. Each chapter builds upon the previous material with numerous conceptual links and integrated examples, so students are able to assemble the information into their own analysis process. The text can serve both undergraduate and graduate students in standard-length and abbreviated courses.

Features: Topics include spreadsheet modeling, data management and modeling, simulation and linear regression modeling, and decision-making under uncertainty. Excel spreadsheets are incorporated throughout the text to model and solve problems. Chapters open with "Business Decision Modeling in Action" segments that immediately require students to be active participants in business decision-making. Other pedagogy includes "Concept Checks: Critical Thinking and Reasoning" sections; case studies and examples; end-of-chapter sample executive summaries of analysis; and detailed instructions with "quick reference," "checklist," and "rules-of-thumb" sections. @Risk and TreePlan[Registered], two Excel add-ins, are extensively covered with a plethora of screenshots. Five interactive cases utilize java applets found on the text Web site. Student CD-Rom contains up-to-date PowerPoint presentations, practice quizzes, data files to enhance student learning, the Decision ToolKit (Academic Version, including RiskSim[Trademark] SensIt[Registered] and TreePlan[Registered] software). Accessto Crystal Ball simulation software is available with the textbook. Premium Solver is bundled with the book.

Product Details

ISBN-13: 9781524939311
Publisher: Kendall/Hunt Publishing Company
Publication date: 07/05/2017
Edition description: New Edition
Product dimensions: 6.00(w) x 1.25(h) x 9.00(d)

Table of Contents

Preface     xiii
Acknowledgments     xv
About the Author     xvii
The Art and Science of Becoming a More Effective and Efficient Problem Solver     1
Business Decision Modeling in Action-Algorithm? What the Heck is an Algorithm?     2
The Art and Science of Using Business Decision Modeling to Become a More Effective and Efficient Problem Solver     2
Definition of Quantitative Business Decision Making     3
Areas of Business Decision Modeling Application     3
Companies Using Business Decision Modeling Concepts     3
The Business Decision Modeling Process     4
Business Decision Modeling Throughout the Ages: A Brief History     5
The Business Decision Modeling Process     5
Problem Identification     6
Problem Definition     6
Problem Modeling     6
Initial Model Results     7
Review and Iteration     7
Implementation     8
Business Decision Modeling Behind the Scenes: The RAND Corporation     8
Basic Financial Models     8
Three Parts of the General Profit Equation     8
Cost and Volume Models     9
Revenue and Volume Models     9
Profit and VolumeModels-Putting It All Together     9
Break-Even Analysis     10
Computer-Generated Solutions Using Spreadsheets     11
Solving Complex Problems Using Spreadsheets     11
Spreadsheet Solution far Break-Even Analysis Problem     12
Writing Business Decision Modeling Reports for Business     13
General Rules of Thumb     13
Creating a Structure     13
Executive Summary     14
Introduction-Background     14
Problem Statement-Purpose     14
Analysis     14
Conclusions and Recommendations     14
Executive Summary Example for Surfboard Inc.     14
Optimal Information Area     16
References     16
Problems     16
Introduction to Spreadsheet Modeling     19
Business Decision Modeling in Action: Using Spreadsheet Models     20
Introduction     21
Background     21
Categories of Models     22
Models versus Modeling     23
Process for Modeling     23
Why a Process for Modeling?     23
The Problem-Solving Process     23
Goals in Spreadsheet Design     25
Basic Spreadsheet Modeling Concepts     25
Layout of a Spreadsheet     25
Reference Cells and Ranges     26
Relative References     26
Copying Formulas     26
Absolute References     26
Mixed References     27
Business Decision Modeling Throughout the Ages: A Brief History of Spreadsheet Modeling     27
Applied Spreadsheet Modeling-Goal Seek     28
Example 1     28
Applying Number Format to Cell     28
Naming Cells     29
Creating Data Tables     31
Creating Charts Using Chart Wizard     33
Sensitivity Analysis     34
Using Goal Seek     35
Basic Excel Functions     36
Mathematical Operators     36
Built-in Functions     37
Statistical Functions     39
Logical Functions     40
Using Lookup Functions     41
Applied Spreadsheet Modeling-Using Basic Functions     44
Example 2     44
Using MIN Function     45
Using IF Function     46
Using VLOOKUP Function     46
Sensitivity Analysis Using Data Table     46
Expected Profit from Demand Probabilities     46
Using SUMPRODUCT     47
Applied Spreadsheet Modeling-Using Solver     48
Example 3     48
Building the Spreadsheet Model     49
Using Excel's Solver     51
Analysis of Results     53
Modification of Constraints and Rerun of Solver     53
Applied Spreadsheet Modeling-Curve Fitting     54
Example 3     54
Business Decision Modeling Behind the Scenes: Spreadsheets in Real Business Situations     55
Linear function y = a + bx     56
Power Function y - ax[superscript b]     57
Exponential Function y - ae[superscript bx]     57
Mean Absolute Percentage Error (MAPE)     58
Summary     60
Case Study-John Woo's Cellular Connections     62
Interactive Case: Break-Even Analysis at SportsExchange     62
Summary of Key Excel Terms     63
Optimal Information Area     64
References     64
Problems     64
Probability and Statistics-A Foundation for Becoming a More Effective and Efficient Problem Solver     66
Business Decision Modeling in Action-A Probabilistic Medical Testing Problem     67
Descriptive Statistics: Graphical and Numerical Methods for Describing Data Sets     69
Graphical Methods of Data Description     69
Histograms     69
Relative Frequency Diagrams     70
Numerical Methods of Data Description     71
Measures of Central Tendency     71
Simple Mean     71
Median     72
Comparison of Mean and Median     73
The Most Common Measure of Central Tendency     73
Measures of Dispersion     73
Range     74
Definition of Interquartile Range (IQR)     74
Definition of Variance     74
Definition of Standard Deviation     75
Painting the Full Picture-A Classroom Example     75
Using Central Tendency and Dispersion     76
Excel Tutorial on Using Histogram Tool Function     76
Frequency Distributions     77
Installing Excel's Data Analysis Add-In     77
Example: Using Excel's Data Analysis Add-In     78
Steps for Determining Classes/Bins and Class Width     78
Excels Histogram Tool     79
Use of Numerical and Graphical Methods in Business Decision Modeling Reporting     80
Probability Concepts     80
Subjective Probability      81
Intuition, Luck, and Subjective Probability Assessment     82
Guessing and Subjective Probability Assessment     82
Objective Probabilities     82
Cards, Coins, and Dice: Examples of Objective Probabilities     83
Business Decision Modeling Objective Probabilities Defined As Relative Frequencies     83
Business Decision Modeling Throughout the Ages: Gambling and Probability     83
Rules of Probability     84
Analyzing an Event Space     85
The Language of Probability: Understanding the Terminology     86
Marginal Probability     86
Mutual Exclusivity     86
Venn Diagram of Mutual Exclusivity     87
Joint Probability     87
Venn Diagram of Non-Mutually Exclusive Events     88
Putting it All Together Using the Business Decision Modeling Process     88
Putting it All Together Using A Deck of Cards     88
Independence     89
Statistically Independent Events     90
Dependent Events     90
Conditional Probabilities     90
General Rule of Multiplication     91
Independent versus Dependent Events     91
Bayes' Rule     91
Business Decision Modeling Behind the Scenes: The Story of Bayes     92
Law of Total Probability     92
Probability Distributions     93
Discrete Probability Distributions     93
Expected Values     94
Probability Distributions and Standard Deviation     95
Standard Deviation Calculation for a Probability Distribution     95
Continuous Probability Distributions     95
Alternative Distributions Used by Managers     96
Lack of Symmetry     96
Sampling Outside of the High and Low Value Ranges     96
Triangular Distribution     96
Summary     97
Case Study: Slicky Lube Oil Products     99
Optimal Information Area     100
References     100
Problems     100
Probability and Statistics Review     104
Decision Analysis: Building the Structure for Solving the Problem     129
Business Decision Modeling in Action: How Legal Decision Makers Use Decision Models     130
Decision Analysis: Building the Structure for Solving the Problem     131
Importance and Relevance of Decision Analysis and Theory     131
Linking Probability and Statistics to Decision Making     131
Framing the Decision Problem      132
Components of a Decision-Making Problem     132
States of Nature     132
Decision Alternatives     132
Outcomes     133
Outcomes versus Payoffs     133
PayoffTables     133
Business Decision Modeling Throughout the Ages: A Brief History of Decision Making     134
Decision-Making Criteria without Probability Assessments     134
Spreadsheet Solution for Payoff Table Decision Problem     134
Steve's Mutual Fund Decision Problem     135
Maximax Criterion     135
Maximin Criterion     136
Minimax Regret     137
Management Science Definition of Regret     137
Economics Definition of Regret-Opportunity Cost     137
Marketing Definition of Regret-Buyer's Remorse     137
Psychological Definition of Regret-Cognitive Dissonance     138
Minimax Regret Criterion Process     138
Regret As a Measure of Risk     139
Equal Likelihood Criterion-LaPlace and Simple Weighted Averages 139 Hurwicz Criterion     140
Summary of Decision Criteria Results     141
Decision-Making Criteria with Probability Assessments     141
Expected Value of Perfect Information      141
Confusion over Multiple "Good" Choices     142
Three Areas of Sensitivity Analysis     142
Sensitivity Analysis     143
Importance of Sensitivity Analysis     144
Sensitivity Analysis: Steve's Mutual Fund Example     144
"What if" Analysis     144
How to Construct a Sensitivity Graph in Excel     145
Analyzing the Sensitivity Graph     148
Risk Defined and Quantified by Regret     149
The Risk-Return Trade-off     150
Modeling, Decision Trees, and Influence Diagrams     150
Definition of Modeling     150
Why Model?     150
Five Main Reasons for Modeling     150
Structuring Decision Problems     151
Identifying and Defining Variables and Outcomes of Interest     151
Organizing Variables and Outcomes into a Logical Framework     151
Verification and Refinement of the Framework     152
Decision Trees     152
Decision Nodes, Chance Nodes, and Decision Trees     152
Building Decision Trees     152
Folding Back the Tree: Calculating Expected Values     154
Example of Folding Back a Decision Tree     154
Multistage Decision Trees      154
Inadequacies of Decision Tree Structures     155
Influence Diagrams     156
Influence Diagram Symbols     156
Influence Diagrams and Depicting Influence     156
Random Variables     157
Order, Precedence, and/or Influence Diagram Structure     158
Two-Way and Loop Influence     158
Influence Diagrams to Measure Time     159
Building the Influence Diagram Structure     160
Putting it All Together: A Real Estate Influence Diagram     160
Converting a Decision Tree to an Influence Diagram     160
Influence Diagrams and Decision Trees: A Comparison     160
Advantages of Influence Diagrams and Areas of Consideration     161
The Completed Real Estate Influence Diagram     162
Using TreePlan to Develop Decision Trees in Excel     163
Loading and Accessing TreePlan     163
Creating an Initial Decision Tree in TreePlan     163
Adding, Changing, and/or Modifying a Decision Tree in
TreePlan     164Adding Chance Nodes     164
Modifying Decision Nodes     165
Business Decision Modeling Behind the Scenes: The Story of John von Neumann     168
Summary     168
Case Study: Ibanez Produce      169
Optimal Information Area     170
References     170
Problems     170
Simulation Modeling     178
Business Decision Modeling in Action: The Super Flush Simulation     179
Simulation Modeling     180
Background     180
Computer Spreadsheet Simulation     180
What Is Simulation? And Why Are We Using It?     180
Real-World Simulation Examples     180
Simulation Affecting Managers     181
Types of Data     181
Numerical Data-Discrete versus Continuous     181
Simulation and the Link to Earlier Chapters     181
Simulation, Spreadsheet Modeling, and Model Verification and Validity     181
Simulation Verification     181
Model Validity     181
Business Decision Modeling Throughout the Ages: Monte Carlo Simulation     182
Random Number Generation     182
Random Number Example     182
Random Number Generation Techniques     182
Developing Random Number Generation Techniques     183
Generating Random Numbers by Spreadsheets     183
Simulation and Currency Exchange Rates     183
Cumulative Probability Distributions and Random Number Intervals     184
Generating Random Numbers and Simulating Currency Values     184
Excel Spreadsheet Simulation of Currency Value Example     184
Simulation of a Queuing System     185
Single-Server Waiting Line System     185
Business Decision Modeling Behind the Scenes: The Major Players and the Story Behind the Development of Monte Carlo Simulation     186
Where Customers Come from-The Calling Population     186
The Order in which Customers Are Served-The Queuing Discipline     186
How Often Customers Arrive at the Queue-The Arrival Rate     187
How Fast Customers Are Served-The Service Time     187
Lunch Wagon Simulation Example: Arrivals and Profit Determination     187
Customer Spending Habits     187
Origination of Probability Distributions     188
Random Number and Normal Probability Generation     188
Computing Relevant Simulation Statistics and Distributions     189
Lunch Wagon Waiting Line Simulation     189
Interarrival Times     189
Customer Service Times     189
Simulation's Real Payoff-The Sensitivity/What if Analysis     191
@Risk Tutorial     191
Running @Risk in Excel     191
What @Risk Will Do for the Modeler     192
User Responsibilities     192
Getting Started     192
Input Cells     193
The Triangular Distribution     193
The Normal Distribution     194
Output Cells     194
Summary of Inputs and Outputs     195
@Risk Window     195
Running a Risk Analysis     195
Simulation Settings     195
Simulation Statistics     197
Data Window and @Risk Report Command     197
Sensitivity Command     198
Scenario Command     199
Graphing the Results     200
Graphing: Understanding the Data More Clearly     200
Using @Risk to Create a Histogram of NPV     200
Using @Risk to Create a Smooth Bell-Shaped Curve of NPV     200
Using @Risk to Create a Cumulative Distribution Graph of NPV     200
Cumulative Distribution Outline Graph     201
Summary Output Graphs     202
Interpretation of the Summary Graph     202
Sensitivity Analysis and the Tornado Diagram     203
Graphing in Excel     204
The End     204
Summary     204
Business Decision Modeling Communication: Executive Summary for Simulation Modeling     204
Case Study: Steve's Solar System     206
Interactive Case: Serving the Customers at Schenck's     207
Optimal Information Area     208
References     208
Problems     208
Linear Regression Modeling     212
Business Decision Modeling in Action-Linear Regression Analysis at General Motors     213
Linear Regression Modeling     214
Forecasting Technique     214
Areas of Statistics     214
Data Sources     214
Statistical Software     216
Population versus Sample     216
Sample Size     216
Scatter Plots     216
Business Decision Modeling Throughout the Ages: History of Regression     218
Hypothesis Testing     218
Simple Linear Regression Equation     219
Business Decision Modeling Behind the Scenes: The Scientists of Regression     221
Multiple Regression Model     221
Performance Measures     223
t Statistic     224
F Statistic     224
p Value     224
Confidence Level     225
Multicollinearity      226
Autocorrelation     227
Alternative Method for Computing the Durbin-Watson Statistic     229
Heteroscedasticity     229
Measuring Accuracy     230
Lagged Variables     232
MS Excel Tutorial: Using Add-ins     233
Installing Excel's Data Analysis ToolPak     234
Excel Tutorial: Regression     234
Excel Tutorial: Correlation     239
Excel Tutorial: Scatter Plot     239
The Leading Causes of Job Creation in Information Technology: A Regression Analysis     240
Background     242
Determining the Relationship: Dependent and Independent Variables     242
Data     242
Model Selection     242
Relative Effectiveness of Models     243
The Regression Line Equation     244
Dependent Variable versus Independent Variable     244
Type of Data     244
Regression Results     245
Regression Line Equation     245
Multiple R     245
R Square     245
Adjusted R Square     245
Standard Error of Estimates     245
t Test     246
F Statistic     247
p Value      247
Multicollinearity     247
Autocorrelation     247
Heteroscedasticity     247
Residual Plots     248
Line Fit Plots     248
Normal Probability Plot     248
Measuring Forecasting Errors     248
Forecasting     251
Conclusion     251
Case Study: Kealoha's Labor Lobby Regression     253
Interactive Case: Plotting Linear Trend for Ellis' DVD Service Demand     253
Optimal Information Area     254
References     254
Problems     255
Introduction to Forecasting     259
Business Decision Modeling in Action: Using Forecasting and Time Series in Stock Market Technical Analysis     259
Analyzing Time Series Data     261
Two Goals: Identify and Forecast     261
Identifying Patterns in Time Series Data     261
Linear Time Series     261
Nonlinear Time Series     262
Seasonal Time Series     262
Cyclical Time Series     263
Irregular Time Series     264
General Forms of Time Series Models     264
Analyzing Patterns in Time Series Data     264
Latest Period or Naive Method      264
Trend Analysis     264
Smoothing     264
Exponential Smoothing     268
Choosing a Smoothing Constant     270
Autocorrelation     270
Identifying Autocorrelation     270
Autocorrelation and Seasonality     270
Seasonal Adjustments     272
Linear Regression Forecast and Seasonal Adjustment     272
Business Decision Modeling Behind the Scenes: Business Forecasting-More Art Than Science?     274
Summary     274
Case Study: Jaqui's Import Beers Sales Seasonality     276
Interactive Case: Forecasting Using Exponential Smoothing for Bill's Brew Threw     277
Optimal Information Area     278
Problems     278
Introduction to Optimization Models     281
Business Decision Modeling in Action: Spreadsheet Optimization Models in Use, or Why Use Spreadsheets to Optimize?     282
Introduction to Optimization Models     283
How Does an Optimization Model Find an Optimal Solution?     283
Descriptive Models: The Foundation for Optimization Models     283
Transforming a Descriptive Model into an Optimization Model     284
Classic Descriptive Economic Order Quantity Model     284
EOQ Spreadsheet Optimization Model     285
Finding the Optimal EOQ     285
Excel's Solver     286
Setting up Solver in Excel     286
Using Solver to Find Solutions to a Spreadsheet Optimization Model     287
Mathematical Programming     292
Linear Programming Models     292
Properties of LP     292
Activity Scaling and LP Models     293
Modeling a Real Problem and the LP Assumptions     293
Three Parts of an LP Model     293
Definition of LP Terminology     293
The Objective Function     294
The Constraints     294
The Non-Negativity Assumptions     295
Putting It all Together-Steps in Formulating an LP Model     295
Putting It all Together-Modified Standard Form     295
Business Decision Modeling Throughout the Ages: The History of Mathematical Programming     296
Mark's Bats LP Example-The Story     297
LP Example-The Formulation Process     297
Identifying Decision Variables     297
Objective Function Formulation     297
Constraint Formulation     298
Non-Negativity Assumptions     299
Modified Standard Form-Mark's Bat Production Problem     299
Methods of Solving LP Problems     300
The Graphical Method     300
Steps to Implementing the Graphical Method     300
Using Spreadsheets to Model LP Problems     300
Creating the LP Spreadsheet Model     300
Using Solver to Find Solutions to a Spreadsheet Optimization Model     302
Comparison of Constraints and Sensitivity Analysis     304
Binding and Nonbinding Constraints     304
Results and Sensitivity Analysis in Excel     305
Answer Report in Excel     305
Sensitivity Report in Excel     307
A Brief Discussion of the Simplex Method     309
Simplex and Slack Variables     310
How the Simplex Method Finds the Optimal Solution     310
Summary     310
Classic Linear Optimization Problems     310
Network Flow/Transportation Problems     310
The Transportation Problem: Pirate Logistics     311
Characteristics of the Transportation LP     311
The General LP Formulation for the Transportation Problem     312
Setting Up the Transportation LP in a Spreadsheet     312
Description of the Spreadsheet     313
Decision Variables      313
Supply and Demand Constraints     313
The Objective Function     313
Non-Negativity Assumptions     313
Setting Up Solver to Find Solutions to the Pirate Logistics LP Problem     314
Answer Report in Excel     316
Sensitivity Report in Excel     317
Constraints Section     319
Summary of Pirate Logistics LP Problem     320
Integer Linear Programming     320
Stating Integer Properties and Solving ILPs     320
Basics of Binary ILP Problems     320
The Capital Budgeting Problem     320
Business Decision Modeling Behind the Scenes: The Development of Spreadsheet Optimization Programs     324
Executive Summary     325
Case Study: Chris's Capital Budgeting Ballyhoo     327
Optimal Information Area     328
Review of Graphical Solutions to Optimization
Problems     334
Project Management: PERT/CPM     345
Business Decision Modeling in Action-CPM, Project Scheduling, and Claims Litigation     346
Project Management: PERT/CPM     348
Introduction     348
Definition of a Project     348
Use of Spreadsheets for Project Management     348
Planning, Scheduling, and Control     348
Project Management Questions and PERT/CPM     349
A Framework for PERT and CPM     349
Business Decision Modeling Throughout the Ages: The History of PERT/CPM     349
A PERT/CPM Example     350
Defining the Network and Developing Precedence Relationships     350
Finding the Critical Path     351
The Forward Pass     351
Earliest Start and Earliest Finish Times     351
The Backward Pass     352
Latest Start and Latest Finish Times     352
The Critical Path and Slack     353
Gantt Charts and Excel     354
Drawbacks to Gantt Charts     355
Creating Gantt Charts in Excel     355
Developing a Probabilistic PERT Network     356
Estimating Activity Completion Times and Distributions     357
PERT and the Beta Distribution     357
Critical Assumptions about PERT     358
Statistical Questions about Expected Completion Times     358
Trade-offs within CPM: Project Crashing     359
Project Crashing within CPM     359
Business Decision Modeling Behind the Scenes: PERT, Production Scheduling, and Filmmaking (No Pun Intended)     361
Sample Executive Summary      362
Case Study: Jennifer's Prototype Palpitations     363
Interactive Case: Critical Path Scheduling for Samantha's Custom Ceramics     364
Problems     364
Introduction to Visual Basic Programming     369
Business Decision Modeling in Action: Real-World VBA project     369
Introduction: What is Visual Basic Programming?     370
Why Visual Basic? Why Excel?     370
What is a Macro?     371
Excel's Macro Recorder     371
Excel Macro Recorder Example     371
How to Record a Macro for Formatting Cells     371
Use the Macro You Created     373
Creating a Toolbar or Assigning a Keystroke for a Macro     373
Recording a Macro by Using Relative References     374
Managing Macros with the Visual Basic Editor     374
What is the Visual Basic Editor?     374
Viewing and Editing Excel Visual Basic Macros     375
Creating a Simple Macro in Visual Basic Editor     377
Working with VB Code: An Introduction to Procedures     378
What is a Subroutine?     379
What Does the Code Mean?     379
How Does the Code Work?     380
Business Decision Modeling Throughout the Ages: The Chronology of Visual Basic     382
Business Decision Modeling Behind the Scenes: The History and Origin of VB     383
Managing Modules and Projects     383
Sharing Macros with Others     383
Technical Writing for the VB Programmer     384
Summary     385
Optimal Information Area     386
Problems     386
Useful Information     389
Standard Normal Distribution Table     390
Student's t Table     391
The F Distribution (Upper 5 Percent Points)     392
Durbin-Watson Test Statistic Table     393
Writing Guide for Business Decision Modeling     394

Customer Reviews

Most Helpful Customer Reviews

See All Customer Reviews