Operations Research Using Excel: A Case Study Approach

Description

The field of operations research provides a scientific approach to managerial decision making. In a contemporary, hypercompetitive ever-changing business world, a manager needs quantitative and factual ways of solving problems related to optimal allocation of resources, profit/loss, maximization/minimization etc.  In this endeavor, the subject of doing research on how to manage and make operations efficient is termed as Operations Research. The reference text provides conceptual and analytical knowledge for various operations research techniques. Readers, especially students of this subject, are skeptic in dealing with the subject because of its emphasis on mathematics. However, this book has tried to remove such doubts by focusing on the application part of OR techniques with minimal usage of mathematics. The attempt was to make students comfortable with some complicated topics of the subject. It covers important concepts including sensitivity analysis, duality theory, transportation solution method, Hungarian algorithm, program evaluation and review technique and periodic review system. Aimed at senior undergraduate and graduate students in the fields of mechanical engineering, civil engineering, industrial engineering and production engineering, this book: • Discusses extensive use of Microsoft Excel spreadsheets and formulas in solving operations research problems • Provides case studies and unsolved exercises at the end of each chapter • Covers industrial applications of various operations research techniques in a comprehensive manner • Discusses creating spreadsheets and using different Excel formulas in an easy-to-understand manner • Covers problem-solving procedures for techniques including linear programming, transportation model and game theory Cover Half Title Title Page Copyright Page Table of Contents Preface Author Chapter 1 Operations Research: An Introduction 1.1 Introduction 1.1.1 Genesis of OR 1.2 Decision-Making Process in OR 1.2.1 Problem Formulation 1.2.2 Problem Analysis 1.3 Model Development 1.3.1 Case: Adidas AG 1.4 Model Solution 1.5 Unconstrained Optimization 1.5.1 Case 1: University Press 1.5.1.1 Problem Formulation 1.5.1.2 Model Development 1.5.1.3 Model Solution 1.5.2 Case 2: Adidas AG 1.5.2.1 Problem Formulation 1.5.2.2 Model Development 1.5.2.3 Model Solution 1.6 Summary 1.7 Case: Suzuki Motor Corporation 1.8 Glossary 1.9 Model Questions Chapter 2 Linear Programming 2.1 Introduction 2.2 Meaning of Linear Programming (LP) 2.3 Assumptions 2.4 Applications of LP 2.4.1 Marketing Research 2.4.2 Media Selection 2.4.3 Financial Planning 2.4.4 Product-Mix Problem 2.4.5 Vendor Selection 2.4.6 Make or Buy Problem 2.4.7 Diet Problem 2.4.8 Blending Problem 2.4.9 Workforce Assignment 2.5 Graphical Method 2.5.1 Illustration of Maximization: Adidas AG Retail Stores 2.5.2 Illustration of Minimization: Rose’s Luxury Restaurant 2.6 Summary 2.7 Case 1: Federal Mogul Corporation 2.8 Case 2: Toyota Motors 2.9 Glossary 2.10 Model Questions Chapter 3 Linear Programming: Simplex Method 3.1 Introduction 3.2 Illustration of Maximization 3.2.1 Case: Woodland Biomass Power (US) 3.2.1.1 Simplex Method 3.2.2 Case: Adidas AG Retail Stores 3.2.2.1 Simplex Method 3.3 An Illustration of Minimization 3.3.1 Case: Federal-Mogul 3.3.1.1 Simplex Method 3.3.2 Case: Rose’s Luxury Restaurant 3.3.2.1 Simplex Method 3.4 Illustration of Maximization Problem with Greater Than Equal to Constraints 3.4.1 Simplex Method 3.5 Special Versions of LPP Solved by Simplex Method 3.5.1 Degeneracy 3.5.2 Unbounded 3.5.3 Infeasibility 3.5.4 Multiple Optimal Solutions 3.6 Summary 3.7 Case Study: Johnson Controls 3.8 Glossary 3.9 Model Questions Chapter 4 Sensitivity Analysis and Duality Theory 4.1 Introduction 4.2 Fundamental Nature of Sensitivity Analysis 4.3 Applying Sensitivity Analysis 4.3.1 Change in RIGHT-HAND SIDE (RHS) Values of Constraint Functions (b[sub(i)]) 4.3.2 Allowable Range of RHS Values (b[sub(i)]) 4.3.3 Change in Objective Function Coefficient (Non-basic Variable) 4.3.4 Change in Objective Function Coefficient (Basic Variable) 4.4 Duality 4.4.1 Construction of Dual Problem 4.4.2 Relationship between Primal and Dual Problem 4.4.3 Dual Problem of Standard LPP 4.4.4 Dual Problem of Non-standard LPP 4.5 Solved Illustrations 4.5.1 Illustration of sensitivity analysis 4.5.2 Illustration of Duality 4.6 Summary 4.7 Case Study: Modern Foods India Limited 4.8 Glossary 4.9 Model Questions Chapter 5 Network Model I: Transportation Model 5.1 Introduction 5.2 Structure of Transportation Model 5.3 Assumptions of Transportation Problems 5.4 Transportation Problem 5.4.1 Case: Musashi Auto Parts Michigan, Inc 5.5 Transportation Solution Methods 5.5.1 Formulation of model: 5.5.2 Initial solution: 5.5.2.1 Least Cost Method 5.5.2.2 North-West Corner method 5.5.2.3 Vogel’s approximation method (VAM) 5.5.3 Optimality test 5.6 Unbalanced Transportation Model 5.6.1 Scenario 1: Supply is More than Demand 5.6.2 Scenario 2: Demand is More than Supply 5.7 Degeneracy 5.7.1 Scenario 1: With a11 as New Basic Variable 5.7.2 Scenario 2: With a23 as New Basic Variable 5.8 Maximization 5.8.1 Linear Programming Formulation 5.9 Unacceptable Routes 5.10 Transshipment Problem: Theory 5.10.1 Case: Food Corporation of India 5.11 Summary 5.12 Case Study 1: Norland Plastics Co 5.13 Case Study 2: Honda Motors 5.14 Glossary 5.15 Model Questions Chapter 6 Network Model II: Assignment Model 6.1 Introduction 6.2 Assignment Problem: Construction of Model 6.2.1 Case: MarketOne International LLP 6.3 Assumptions 6.4 Comparison with Transportation Model 6.5 Hungarian Algorithm 6.6 Variations of Assignment Problem 6.6.1 Unbalanced Assignment Problem 6.6.1.1 Case 1: Number of employees is more than the number of jobs: 6.6.1.2 Case 2: Number of employees is less than number of jobs: 6.6.2 Maximization Problem 6.6.3 Unacceptable Assignment 6.7 Crew Assignment 6.7.1 Case: Star Airlines 6.7.1.1 Case 1: assignment of different types of airplanes to different routes 6.7.1.2 Case 2: assignment of crew members to routes 6.8 Summary 6.9 Case 1: Assigning Workers to Processes 6.10 Case 2: Assigning Swings to Kids 6.11 Glossary 6.12 Model Questions Chapter 7 Network Model III: Travelling Salesman, Vehicle Routing and Shortest Path Problem 7.1 Introduction 7.2 Travelling Salesman Problem 7.2.1 Branch and Bound Method 7.3 Vehicle Routing Problem 7.3.1 Clark–Wright Savings Algorithm 7.4 Shortest Path Problem: Dijkstra’s Algorithm 7.5 Summary 7.6 Glossary 7.7 Case study: JTEKT Corporation 7.8 Model Questions Chapter 8 Project Scheduling: PERT and CPM 8.1 Introduction 8.2 Network Planning 8.2.1 Rules for Construction of Network Diagrams 8.3 Critical Path Method 8.3.1 Case: Mattel Inc 8.4 Program Evaluation and Review Technique 8.4.1 Case: East Fork Roofing 8.5 Crashing: Time – Cost Trade-Offs 8.5.1 An Illustration of Crashing 8.6 Resource Planning 8.6.1 Resource Limited Scheduling 8.6.2 Resource Levelling 8.7 Summary 8.8 Glossary 8.9 Case Study: Polyplastics Industries India Pvt. Ltd 8.10 Model Questions Chapter 9 Game Theory 9.1 Introduction 9.2 Characteristics of Game Theory 9.3 Elements of Game Theory 9.4 Solving Games: 5G technology 9.4.1 Explanation of Payoff Table 9.5 Games with Saddle Point 9.5.1 Principle of Dominance 9.6 Games with Mixed Strategies 9.7 Graphical Method 9.7.1 For 2*m Games 9.7.2 Algebraic Method for 2*m Games 9.7.3 For m*2 Games 9.7.4 Algebraic Method for m*2 Games 9.8 Linear Programming Formulation 9.9 Summary 9.10 Glossary 9.11 Case Study: Vendor–Retailer Relationship 9.12 Model Questions Index