Report

Chapter 1: Introduction to Managerial Decision Modeling Jason C. H. Chen, Ph.D. Professor of MIS School of Business Administration Gonzaga University Spokane, WA 99223 [email protected] What is Decision Modeling? A scientific approach to managerial decision making • The development of a (mathematical) model of a real-world scenario • The model provides insight into the solution of the managerial problem Dr. Chen, Decision Support Systems 2 Types of Decision Models • Deterministic Models Where all the input data value are known with complete certainty • Probabilistic Models Where some input data values are uncertain Dr. Chen, Decision Support Systems 3 Quantitative vs. Qualitative Data The modeling process begins with data • Quantitative Data Numerical factors such as costs and revenues • Qualitative Data Factors that effect the environment which are difficult to quantify Dr. Chen, Decision Support Systems 4 Spreadsheets in Decision Making • Computers are used to create and solve models • Spreadsheets are a convenient alternative to specialized software • Microsoft Excel has extensive modeling capability via the use “add-ins” Dr. Chen, Decision Support Systems 5 Steps in Decision Modeling 1. Formulation Translating a problem scenario from words to a mathematical model 2. Solution Solving the model to obtain the optimal solution 3. Interpretation and Sensitivity Analysis Analyzing results and implementing a solution Dr. Chen, Decision Support Systems 6 Steps in Modeling Dr. Chen, Decision Support Systems 7 Example Model: Tax Computation Self employed couple must estimate and pay quarterly income tax (joint return) • Income amount is uncertain • 5% of income to retirement account, up to $4000 max • Personal exemption = 2 x $3200 = $6400 • Standard deduction = $10,000 • No other deductions Dr. Chen, Decision Support Systems 8 Tax Brackets Taxable Income up to $14,600 $14,601 to $59,400 $59,401 to $119,950 Percent of Taxable Income 10% 15% 25% Go to file 1-1.xls Dr. Chen, Decision Support Systems 9 Millers' Tax Computation Known Parameters Retirement Savings % 0.05 Maximum savings 4000 Personal exemption 3200 Standard deduction 10000 Tax rates per person 0.1 $1 to 14600 0.15 $14,601 to 59400 0.25 $59,401 to 119950 Variables Variables Sue's estimated income Sue's estimated income $45,000.00 Rob's estimated income Rob's estimated income $40,000.00 Tax Computation Tax Computation Total income =B13+B14 Total income Retirement savings =MIN(B4*B17,B5) Retirement savings $4,000.00 Personal exemptions =2*B6 Personal exemptions $6,400.00 Standard deduction =B7 Standard deduction $10,000.00 Taxable income =MAX(0,B17-SUM(B18:B20)) Taxable income $64,600.00 Tax @ 10% rate =B8*MIN(B21,D8) Tax @ 10% rate $1,460.00 Tax @ 15% rate =IF(B21>D8,B9*(MIN(B21,D9)-D8),0) Tax @ 15% rate $6,720.00 Tax @ 25% rate =IF(B21>D9,B10*(MIN(B21,D10)-D9),0) Tax @ 25% rate $1,300.00 Total tax =SUM(B22:B24) Total tax $9,480.00 Estimated tax per quarter =B25/4 Estimated tax per quarter $2,370.00 Dr. Chen, Decision Support Systems file 1-1.xls $85,000.00 10 Example Model: Break-Even Analysis Profit = Revenue – Costs Revenue = (Selling price) x (Num. units) Costs = (Fixed cost) + (Cost per unit) x (Num. units) Dr. Chen, Decision Support Systems 11 The Break Even Point (BEP) is the number of units where; Profit = 0, so Revenue = Costs BEP = Fixed cost (Selling price) – (Cost per unit) Go to file 1-2.xls Dr. Chen, Decision Support Systems 12 Bill Pritchett's Shop Known Parameters Bill Pritchett's Shop Bill Pritchett's Shop Known Parameters Known Parameters Selling price per unit 10 Selling price per unit Fixed cost 1000 Fixed cost Variable cost per unit 5 Variable cost per unit Variables Variables Number of units, X Number of units, X Results Results Total revenue =B4*B9 Total revenue Fixed cost =B5 Fixed cost Total variable cost =B6*B9 Total cost Profit $10.00 $1,000.00 $5.00 Selling price per unit Fixed cost Variable cost per unit $10.00 $1,000.00 $5.00 Variables 1000 Number of units, X 200 Results $10,000.00 Total revenue $2,000.00 $1,000.00 Fixed cost $1,000.00 Total variable cost $5,000.00 Total variable cost $1,000.00 =B13+B14 Total cost $6,000.00 Total cost $2,000.00 =B12-B15 Profit $4,000.00 Profit $0.00 Go to file 1-2.xls Dr. Chen, Decision Support Systems 13 Possible Problems in Developing Decision Models Defining the Problem • Conflicting viewpoints • Impact on other departments • Beginning assumptions • Solution outdated Dr. Chen, Decision Support Systems 14 Possible Problems in Developing Decision Models Developing a Model • Fitting the textbook models • Understanding the model Acquiring Input Data • Using accounting data • Validity of data Dr. Chen, Decision Support Systems 15 Possible Problems in Developing Decision Models Developing a Solution • Hard to understand mathematics • Limitations of only one answer Testing the Solution Analyzing the Results Implementation Dr. Chen, Decision Support Systems 16