...Modeling and Solving LP Problems in a Spreadsheet Chapter 3 C.T. Ragsdale. 2008. Spreadsheet Modeling & Decision Analysis, 5th E. Revised, Thompson 1 Section 1 EXCEL SOLVER 2 Introduction • Solving LP problems graphically is only possible when there are two decision variables • Few real-world LP have only two decision variables • Fortunately, we can now use spreadsheets to solve LP problems 3 LP Solvers • Conventional – MPS (IBM) – LINDO, GINO – GAMS – AMPL • Algebraic Language • Spreadsheet Modeling • The company that makes the Solver in Excel, Lotus 12-3, and Quattro Pro is Frontline Systems, Inc. Check out their web site: http://www.solver.com – Frontline Solver, Premium Solver, Risk Solver – What’s Best? 4 Steps in Implementing an LP Model in a Spreadsheet 1. Organize the data for the model on the spreadsheet. 2. Reserve separate cells in the spreadsheet for each decision variable in the model. 3. Create a formula in a cell in the spreadsheet that corresponds to the objective function. 4. For each constraint, create a formula in a separate cell in the spreadsheet that corresponds to the left-hand side (LHS) of the constraint. 5 The Simple Farm Model Again! max π = x1 + 1.5x2 s/t x1 + 2x2 ≤ 160 3x1 + 2x2 ≤ 240 x1 ≥ 0, x2 ≥ 0 6 Implementing the Model See file FarmEx original.xls 7 Cell Labels 8 How Solver Views the Model • Target cell - the cell in the spreadsheet that represents the objective function...
Words: 2610 - Pages: 11
...3 Lab Hours 0 Catalog Description: Examination of quantitative skills useful to managers. Discussions focus on selected algebra topics, mathematics for finance and descriptive statistics and probability. Prerequisite: Knowledge of college algebra, Excel, or other computer spreadsheet applications is important. Prerequisite(s) / Corequisite(s): Knowledge of college algebra, Excel, or other computer spreadsheet applications is important. Text(s): Most current editions of the following: Spreadsheet Modeling & Decision Analysis By Cliff T. Ragsdale, Ed. (Thomson Southwestern) Recommended Course Objectives To understand a variety of quantitative methods. To emphasize presentations, peer review and application of quantitative methods. Measurable Learning Outcomes: Use linear programming for optimization and modeling business applications. Apply the concept of sensitivity analysis. Apply regression analysis for problem solving in business applications. Explain queuing theory. Evaluate risk assessment and project management. Topical Outline: • Optimization linear programming • Modeling LP problems • Sensitivity analysis • Network modeling • Regression analysis • Integer linear programming • Project management Culminating Experience Statement: Material from this course may be tested on the Major Field Test (MFT) administered during the Culminating Experience course for the degree. During this course the ETS...
Words: 401 - Pages: 2
...DECISION MODELING DECISION WITH WITH MICROSOFT EXCEL MICROSOFT Linear Optimization Linear Optimization A constrained optimization model takes the form of a constrained performance measure to be optimized over a range of feasible values of the decision variables. The feasible values of the decision variables are determined by a set of inequality constraints. constraints Values of the decision variables must be chosen such that the inequality constraints are all satisfied while either maximizing or minimizing the desired performance variable. These models can contain tens, hundreds, or thousands of decision variables and constraints. Linear Optimization Very efficient search techniques exist to optimize constrained linear models. constrained These models are historically called linear programs linear (LP). In this chapter we will: 1. Develop techniques for formulating LP models 2. Give some recommended rules for expressing LP models in a spreadsheet that facilitates application of Excel’s Solver 3. Use Solver to optimize spreadsheet LP models Formulating LP Models Every linear programming model has two important features: Objective Function Constraints A single performance measure to be maximized or minimized (e.g., maximize profit, minimize cost) Constraints are limitations or requirements on the set of allowable decisions. Constraints may be further classified into physical, economic, or policy limitations or ...
Words: 4037 - Pages: 17
...Spreadsheet Analysis & Modeling MIS-505 Questions for Final Exam 1. What is name range? How can ranges be named? What are its uses? 2. What is lookup in Excel? What are the different kinds of lookup functions? What are its uses? 3. What is Index function? Write down the procedure of index function. What are its uses? 4. What is Match function? How you can write an Index and Match functions in a nested way? What are its uses? 5. Describe different kinds of text functions in Excel (such as left, right, mid). 6. How can you put date in Excel? What are its uses? 7. What is NETWORKINGDAYS function in Excel? What is NETWORKINGDAYS.INT function in Excel? What are its uses? 8. What is workday and WORKDAY.INT functions in Excel? What are its uses? 9. What is Net Present Value? How do NPV and XNPV function work in Excel? What are its uses? 10. What is internal rate of return (IRR)? How do IRR, XIRR, and MIRR work in Excel? What are its uses? 11. What is present value? How does PV function work? What are its uses? 12. What is Future Value? How does FV function work? What are its uses? 13. Write down the functions of PMT, PPMT, IPMT. How can you use these functions to prepare a loan amortization schedule? 14. Write down the function of IF Statement, AND, OR and XOR. 15. Describe different Time functions. What are its uses? 16. What is What If Analysis? Describe different kind of what if analysis. What are its uses...
Words: 474 - Pages: 2
...Chapter 5 Modeling with Linear Programming 5.1 Introductory Example SilComputers makes quarterly decisions about their product mix. While their full product line includes hundreds of products, we will consider a simpler problem with just two products: notebook computers and desktop computers. SilComputers would like to know how many of each product to produce in order to maximize pro t for the quarter. There are a number of limits on what SilComputers can produce. The major constraints are as follows: 1. Each computer either notebook or desktop requires a Processing Chip. Due to tightness in the market, our supplier has allocated 10,000 such chips to us. 2. Each computer requires memory. Memory comes in 16MB chip sets. A notebook computer has 16MB memory installed so needs 1 chip set while a desktop computer has 32MB so requires 2 chip sets . We received a great deal on chip sets, so have a stock of 15,000 chip sets to use over the next quarter. 3. Each computer requires assembly time. Due to tight tolerances, a notebook computer takes more time to assemble: 4 minutes versus 3 minutes for a desktop. There are 25,000 minutes of assembly time available in the next quarter. Given current market conditions, material cost, and our production system, each notebook computer produced generates $750 pro t, and each desktop produces $1000 pro t. There are many questions SilComputer might ask. The most obvious are such things as How many of each type computer should SilComputer produce...
Words: 8397 - Pages: 34
...Solving the Salinity Control Problem in a Potable Water System⋆ Chiu Wo Choi and Jimmy H.M. Lee Department of Computer Science and Engineering The Chinese University of Hong Kong Shatin, N.T., Hong Kong {cwchoi,jlee}@cse.cuhk.edu.hk Abstract. Salinity is the relative concentration of salts in water. In a city of southern China, the local water supply company pumps water from a nearby river for potable use. During the winter dry season, the intrusion of sea water raises the salinity of the river to a high level and affects approximately the daily life of 450,000 residents of the city. This paper reports the application of constraint programming (CP) to optimize the logistical operations of the raw water system so as to satisfy the daily water consumption requirement of the city and to keep the potable salinity below a desirable level for as many days as possible. CP is the key to the success of the project for its separation of concerns and powerful constraint language that allows for rapid construction of a functional prototype and production system. Flexibility and adaptiveness allow us to deal with our clients’ many changes in the requirements. Deriving good variable and value ordering heuristics, and generating useful implied constraints, we demonstrate that branch-and-bound search with constraint propagation can cope with an optimization problem of large size and great difficulty. 1 Introduction Salinity is the relative concentration of salts in water measured in parts...
Words: 7607 - Pages: 31
...response to the changing market condition and customer needs. A more defined organizational structure, performance metrics and decision support systems (DSS) in SCM is required to create competitive advantages for an organization. Since Information Technology/Information System (IT/IS) is an integral component of SCM, DSS is inevitable in making available the right information at the right time so that managers can make timely and more accurate decisions. However, there is no best known DSS type that is required to effectively manage a supply chain. A properly designed DSS will help decision makers to extract useful information from raw data, documents, personal knowledge and/or business models with the objective of identifying and solving problems, and making decisions. Decision Support Systems (DSS) are a specific class of computerized information system that supports business and organizational decision-making activities. The DSS in supply chain management can be a model, communication, data, document and knowledge driven. Three fundamental components of DSS architecture are: (i) Input data: database or knowledge base, (ii) Analytical Tool: the model (decision context and user criteria) and (iii) use interface, should be given due consideration while developing supply chain decision support systems (Figure1: DSS Components). B. Introduction The Objective of this paper is to define the importance of decision support systems in Supply Chain...
Words: 2843 - Pages: 12
...for regularly checking the Calendar and Course News in Canvas to keep up with ongoing developments during the course. Course Description .Quantitative models and optimization systems used in problemsolving. Studies of decision criteria, statistical decision making, linear programming, inventory control, Project Scheduling/Management Theory, PERT/CPM, simulation, and other techniques used in management. Course Goals and Student Learning Objectives. The course will emphasize basic principles of mathematical modeling, computational algorithms and spreadsheet computer solutions of optimization, decision analysis, simulation and network based methods applied to business problems. I. Students will learn various aspects of Linear Programming How to formulate word problems algebraically How to obtain Graphical Solutions to problems with two variables How to set up Linear Programming problems in the spreadsheet How to use the Excel Solver to solve LP problems in the spreadsheet How to read and interpret the Answer and Sensitivity reports...
Words: 2354 - Pages: 10
...make the most effective use of limited resources Machinery, labor, money, time, warehouse space, raw materials Linear programming (LP) is a widely used mathematical modeling technique designed to help managers in planning and decision making relative to resource allocation Belongs to the broader field of mathematical programming In this sense, programming refers to modeling and solving a problem mathematically © 2009 Prentice-Hall, Inc. 7–2 Requirements of a Linear Programming Problem LP has been applied in many areas over the past 50 years All LP problems have 4 properties in common 1. All problems seek to maximize or minimize some quantity (the objective function) 2. The presence of restrictions or constraints that limit the degree to which we can pursue our objective 3. There must be alternative courses of action to choose from 4. The objective and constraints in problems must be expressed in terms of linear equations or inequalities © 2009 Prentice-Hall, Inc. 7–3 LP Properties and Assumptions PROPERTIES OF LINEAR PROGRAMS 1. One objective function 2. One or more constraints 3. Alternative courses of action 4. Objective function and constraints are linear ASSUMPTIONS OF LP 1. Certainty 2. Proportionality 3. Additivity 4. Divisibility 5. Nonnegative variables Table 1 © 2009 Prentice-Hall, Inc. 7–4 Basic Assumptions of LP We assume conditions of certainty exist and numbers in the objective and constraints are known with certainty and do not...
Words: 6763 - Pages: 28
...Discipline ECONOMICS Estimating sensitivity of demand to price 352–356 Pricing problems 352–366, 422–427 Estimating cost of power 363–366 47–56, Assessing a utility function 554–556 Estimating demand for products 632–638, 649–650, 764–771, 965 Subway token hoarding 792 FINANCE AND ACCOUNTING Collecting on delinquent credit accounts 14–16 Cost projections 29–33 Finding a breakeven point 33–41 Calculating NPV 57–62 Calculating NPV for production capacity decision 58–62 Portfolio management 173–178, 345–346, 387–394, 442–444, 689–691 Pension fund management 178–182 Financial planning 210–214, 676–681, 734–735 Arbitrage opportunities in oil pricing 215–219 Currency trading 220 Capital budgeting 290–295 Estimating stock betas 396–401 Hedging risk with put options 407–408 Stock hedging 407–408 Asset management 409–410 New product development 503–504, 574, 673–676, 715–722 Bidding for a government contract 513–518, 523–533, 653–657 Investing with risk aversion 557–560 Land purchasing decision 575 Risk analysis 582–583 Liquidity risk management 651–653 Estimating warranty costs 657–661 Retirement planning 681–685 Modeling stock prices 685–686 Pricing options 686–689, 691–693 Investing for college 732 Bond investment 733 HUMAN RESOURCES AND HEALTH CARE Fighting HIV/AIDS 23–24 DEA in the hospital industry 184–189 Salesforce allocation problems 454–456 Assigning MBA students to teams 462 Selecting a job 484–492 Selecting a health...
Words: 68271 - Pages: 274
...Pennsylvania 19104 {cohen@wharton.upenn.edu, gans@wharton.upenn.edu} D istribution: To maintain the integrity and usefulness of cases published in ITE, distribution of this teaching note to any other party is prohibited. Please refer interested instructors to ITE for access to the teaching note. Section 1, below, contains the suggested case questions. Section 2 provides some background on the case questions, as well as sample answers. Section 3 systematically walks through HarrahsInstructionSpreadsheet.xls, which is one of the two supporting files1 included in this Teaching Note (TN): HarrahsCaseData.xls—supporting data for the case analysis, which can be distributed to students. HarrahsInstructorSpreadsheet.xls—spreadsheet that includes case data, a bid-price linear program (LP), and a Crystal Ball Simulation that allows the user to simulate revenues associated with a given set of bid prices. 1. weekend and only the rooms in Table TN1 remain free. Now consider the list of 6 reservation requests that came in on August 15, as shown in Table TN2. Using the clearing prices that were computed in Table 8 in the Case, determine which customers should be offered a room and what prices they will...
Words: 8773 - Pages: 36
...Refuelling Schedule for Airports By Kiran R K Jitha Babu Shyam S Sreeraj S CHAPTER 1 INTRODUCTION 1.1 REFUELLING Refuelling is an important aspect with respect to the airports since it determines the ground time of an aero plane. Aircraft fuelling can take up to 90 minutes of ground time. Hence, precise planning and allocation is absolutely essential. Once a flight lands in an airport it has to be refueled for the next flight. The quantity of fuel required depends on the distance it has to travel to reach the next station and type of flight. Aviation fuel is a specialized type of petroleum-based fuel used to power aircraft. It is generally of a higher quality than fuels used in less critical applications such as heating or road transport, and often contains additives to reduce the risk of icing or explosion due to high temperatures, amongst other properties. Aviation fuels consist of blends of over a thousand chemicals, primarily Hydrocarbons (paraffins, olefins, naphthenes, and aromatics) as well as additives such as antioxidants and metal deactivators, and impurities. Principal components include n-octane and isooctane. Like other fuels, blends of Aviation fuel are often described by their...
Words: 9437 - Pages: 38
...Course Code: C201 Cost and Management Accounting (CMA) 1. Objectives: The course intends to equip students with the ability to apply cost concepts in managerial decision making. At the end of the course, they are expected to have learnt the methodology and techniques for application of cost and managerial accounting and information in the formation of policies and in the planning and control of the operations of the organization. The course covers the nature of managerial accounting; activity costing; marginal costing; standard costing, etc. 2. Course Duration The course will have 45 sessions of 60minutes duration. 3. Course Contents Assignment of sessions to the modules of course is as follows: Module No. | Modules/ Sub modules | Sessions | Marks (Uni. Exam) | I | Cost Management and Cost EstimationCost and Management Accounting OverviewVarious Cost Concepts. Cost Estimation approaches, tools and techniques | 9 | 20 | II | Cost allocation and Job/ batch CostingCost Allocation, Activity Based and Target CostingJob and Batch Costing Introduction to the concept of operating costs | 9 | 20 | III | Costing MethodsOperating Costing. Process Costing and Joint and By product Costing. | 9 | 20 | IV | Management ApplicationsMarginal (Variable) Costing and Absorption Costing Decision Involving Alternative Choices and Pricing DecisionsMake-or-Buy | 9 | 20 | V | Planning, Control and Decision MakingBudgeting and Budgetary Control SystemStandard...
Words: 4490 - Pages: 18
...many more applications is the assumption of divisibility (see Sec. 3.3), which requires that noninteger values be permissible for decision variables. In many practical problems, the decision variables actually make sense only if they have integer values. For example, it is often necessary to assign people, machines, and vehicles to activities in integer quantities. If requiring integer values is the only way in which a problem deviates from a linear programming formulation, then it is an integer programming (IP) problem. (The more complete name is integer linear programming, but the adjective linear normally is dropped except when this problem is contrasted with the more esoteric integer nonlinear programming problem, which is beyond the scope of this book.) The mathematical model for integer programming is the linear programming model (see Sec. 3.2) with the one additional restriction that the variables must have integer values. If only some of the variables are required to have integer values (so the divisibility assumption holds for the rest), this model is referred to as mixed integer programming (MIP). When distinguishing the all-integer problem from this mixed case, we call the former pure integer programming. For example, the Wyndor Glass Co. problem presented in Sec. 3.1 actually would have been an IP problem if the two decision variables x1 and x2 had represented the total number of units to be produced of products 1 and 2, respectively, instead of the production rates....
Words: 36302 - Pages: 146
...Text: Quantitative Analysis for Management by Render, Stair and Hanna, 11th edition, Prentice Hall, 2012 – Chapter 1 Objectives of unit 1: After completing this unit, students should be able to: 1. Describe the quantitative analysis approach for management 2. Demonstrate an understanding by applications of quantitative analysis in real world situations 3. Demonstrate the use of modeling in quantitative analysis 4. Use computers and spreadsheet models to perform quantitative analysis 5. Understand the limitations of quantitative analysis 6. Demonstrate/perform break-even analysis. Scope of coverage: Concepts Development 1. Overview of quantitative analysis 2. Defining quantitative analysis 3. The approach to quantitative analysis 4. A quantitative analysis model 5. Using spreadsheet for quantitative analysis 6. Limitation of quantitative analysis Introduction Quantitative analysis for decision-making is the application of a scientific approach to solve management problems. The purpose is to help managers make better decisions. Quantitative analysis encompasses a number of mathematically oriented techniques that have either been developed within the field of management science or adapted from other disciplines such as natural sciences, mathematics, statistics and engineering. This course provides the principles and techniques of quantitative analysis and demonstrates their...
Words: 19689 - Pages: 79
