Introduction to Management Science, Modeling, and Excel Spreadsheets

Chapter 1Introduction to Management Science, Modeling, and Excel SpreadsheetsLearning ObjectivesDescribe the importance of management science.Describe the advantages of a quantitative approach to problem solving.List some of the applications and use of management science models.Discuss the types of models most useful in management science.Demonstrate the basic building blocks and components of Excel.After completing this chapter, you should be able to:2Copyright © 2007 The McGraw-Hill Companies. All rights reserved. Learning Objectives (cont’d)Describe the basic nature and usefulness of break-even analysis.List and briefly explain each of the components of break-even analysis.Solve typical break-even problems manually and with Excel.After completing this chapter, you should be able to:3Copyright © 2007 The McGraw-Hill Companies. All rights reserved. The Importance of Management ScienceManagement scienceThe discipline of applying advanced analytical methods to help make better decisions.Devoted to solving managerial-type problems using quantitative modelsApplications of management scienceForecasting, capital budgeting, portfolio analysis, capacity planning, scheduling, marketing, inventory management, project management, and production planning.4Copyright © 2007 The McGraw-Hill Companies. All rights reserved. Table 1–2 Successful Applications of Management Science5Copyright © 2007 The McGraw-Hill Companies. All rights reserved. Table 1–2 Successful Applications of Management Science (cont’d)6Copyright © 2007 The McGraw-Hill Companies. All rights reserved. Problem Solving ApproachesManagers tend to use a qualitative approach to problem solving whenThe problem is fairly simple.The problem is familiar.The costs involved are not great.Managers tend to use a quantitative approach whenThe problem is complex.The problem is not familiar.The costs involved are substantial.Enough time is available to analyze the problem.7Copyright © 2007 The McGraw-Hill Companies. All rights reserved. Advantages of the Quantitative ApproachDirects attention to the essence of an analysis: to solve a specific problem.Improves planning which helps prevent future problemsResults in more objective decisions than purely qualitative analysis.Incorporates advances in computational technologies to managerial problem-solving.8Copyright © 2007 The McGraw-Hill Companies. All rights reserved. ModelsA ModelAn abstraction of reality. It is a simplified, and often idealized, representation of reality. Examples : an equation, an outline, a diagram, and a mapBy its very nature a model is incomplete.Provides an alternative to working with realitySymbolic modelsUse numbers and algebraic symbols Mathematical modelsDecision variablesUncontrollable variables9Copyright © 2007 The McGraw-Hill Companies. All rights reserved. Deterministic versus Probabilistic ModelsDeterministic modelsUsed for problems in which information is known with a high degree of certainty.Used to determine an optimal solution to the problem.Probabilistic modelsUsed when it cannot be determined precisely what values (requiring probabilities) will occur (usually in the future).10Copyright © 2007 The McGraw-Hill Companies. All rights reserved. Figure 1–1 The Management Science Approach11Copyright © 2007 The McGraw-Hill Companies. All rights reserved. Figure 1–2 DSS FrameworkSource: E. Turban, Jay Aronson, and Ting-Peng Liang, Decision Support Systems and Intelligence Systems, 7th ed. (Upper Saddle River, NJ: Prentice Hall, 2005), p. 109.12Copyright © 2007 The McGraw-Hill Companies. All rights reserved. Exhibit 1-1 Excel Spreadsheet13Copyright © 2007 The McGraw-Hill Companies. All rights reserved. Exhibit 1-2 Functions Screen14Copyright © 2007 The McGraw-Hill Companies. All rights reserved. Exhibit 1–3 Add-in Options15Copyright © 2007 The McGraw-Hill Companies. All rights reserved. Breakeven AnalysisBreakeven analysis (cost-volume analysis)Is concerned with the interrelationship of costs, volume (quantity of output or sales), and profit.The Break-Even Point (BEP)The volume for which total revenue and total cost are equal.The dividing line between profit and loss; sales higher than the break-even point will result in a profit, while sales that is lower than the break-even point will result in a loss.Where you get “out of the red.”16Copyright © 2007 The McGraw-Hill Companies. All rights reserved. Breakeven AnalysisBreakeven analysis (cost-volume analysis)Is concerned with the interrelationship of costs, volume (quantity of output or sales), and profit.Components of Break-Even AnalysisVolume: the level of output of a machine, department, or organization, or the quantity of sales.Revenue: the income generated by the sale of a product. Total revenue = revenue per unit (selling price per unit) multiplied by units (volume) sold.Costs: costs that must be taken into accountFixed costs are not related to the volume of output.Variable costs increase and decrease with output.17Copyright © 2007 The McGraw-Hill Companies. All rights reserved. Assumptions of Break-Even AnalysisThe revenue per unit is the same for all volumes.The variable cost per unit is the same for all volumes.Fixed cost is the same for all levels of volume.Only one product is involved.All output is sold.All relevant costs are accounted for, and correctly assigned to either the fixed cost category or the variable cost category.18Copyright © 2007 The McGraw-Hill Companies. All rights reserved. Figure 1–3 Total Revenue Increases Linearly as Volume Increases19Copyright © 2007 The McGraw-Hill Companies. All rights reserved. Figure 1–4 Fixed Costs20Copyright © 2007 The McGraw-Hill Companies. All rights reserved. Figure 1–5 Total Variable Cost21Copyright © 2007 The McGraw-Hill Companies. All rights reserved. Figure 1–6 Total Cost22Copyright © 2007 The McGraw-Hill Companies. All rights reserved. Figure 1–7 Profit and the Break-Even PointProfit23Copyright © 2007 The McGraw-Hill Companies. All rights reserved. Example 1–124Copyright © 2007 The McGraw-Hill Companies. All rights reserved. Exhibit 1–4 Break-Even Analysis25Copyright © 2007 The McGraw-Hill Companies. All rights reserved. Exhibit 1–5 Goal Seek Input ScreenExhibit 1–6 Goal Seek Output Screen26Copyright © 2007 The McGraw-Hill Companies. All rights reserved.