...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 • Changing cells - the cells in the spreadsheet...
Words: 2610 - Pages: 11
...Problem Solver I chose problem solver because I have realized in my life that is what I love to do. I love to solve problems. I have a hard time practicing or participating in activities that don’t have a sense of purpose. But if there is a problem, I obsess over it until it is resolved. The problem that I decided to put through the five step process was with the problem I had after graduating with my B.S. degree. I graduated in December of 2011, right in the middle of so many problems with people being able to get jobs. At the time I was working in a field that I would say was about 20% related to my degree. My degree is in Electronics Engineering and I was working in a power plant, however it was a methane gas power plant and the majority of what I did was mechanical maintenance. Not liking where I was and not able to get more than a few interviews, I was stuck. Either I was too qualified, the job didn’t pay enough, or I was under qualified. Step 1: The main problem was job placement, there were many factors but in the end I was not in a place that I wanted to be in my career. Step 2: The causes were many, as I mentioned earlier. * Under/over qualified * Weak jobs market * Pay associated with available jobs * Nowhere to go with current job, or lack of desire to do so I also at this time began to realize that I didn’t like where my experience was taking me. I had now worked in two jobs that were in an industrial setting, (large machinery, noisy, dirty...
Words: 623 - Pages: 3
...BTMP 1013 BUSINESS MATHEMATICS GROUP ASSIGNMENT Student Guidelines 1. Each group consist of 5 person per group. Ideally to have mixtures of gender and ethnics in a group. 2. Each group required to learn Linear Programming using Microsoft Excel’s Solver. You may refer to Business Mathematics reference books, video at Youtube or website. 3. Solve the Linear Programming questions using Microsoft Excel’s Solver. You need to print screen each step for each questions and state conclusion. 4. Produce educational video(s) to explain the solution for some questions. You may decide which questions to be recorded. 5. Please submit your assignment in bound. 6. Plagiarism, copying and cheating will NOT be awarded any mark, and disciplinary actions will be taken instead. Any requires/ questions/ problems regarding this assignment should be directed to your lecturer either during lecture or in the consultation hours. 7. The due date to submit this assignment is on 13 November 2015. Solve the following questions using Microsoft Excel’s Solver. Pick any of the questions and record/take video step by step on how to solve it using Microsoft Excel’s Solver. 1. Minimize C=2x-3y-4z Subject to -x+2y-z≤8 x-2y+2z≤10 2x+4y-3z≤12 x≥0, y≥0,z≥0 2. Minimize C=2x+3y Subject to 8x+y≥80 3x+2y+2z≥100 x+4y≥80 x≥0, y≥0 3. Deluxe River Cruises operates...
Words: 1022 - Pages: 5
...Lab 0: MATLAB and ODE Solvers | ME 4173 Robot Kinematics | | | | Introduction The following report will display the results and conclusions of an experiment to simulate the output of an inverse pendulum system in MATLAB. The objectives of this experiment were to review MATLAB programming and using MATLAB to simulate ODEs and systems. Objectives * Examine the basics of MATLAB * Use MATLAB to simulate a system * Use ODE solvers to numerically integrate the system over a set time period Apparatus The apparatus used in this experiment was MATLAB. It was used to provide a simulation environment to analyze the inverse pendulum’s motion. Experiments and Results There were six components of this experiment. This experiment was mostly familiarizing with MATLAB. All code used is illustrated in the Appendix – Code. The first part consisted of learning commands within the MATLAB environment. It was a brief overview of how commands work in MATLAB. There was no code used in the part of the experiment. The second part of the experiment examined how arrays were created and used in MATLAB. The first step was to create a matrix. This matrix was then subjected to various commands including eye( ), zeroes( ), and ones( ). Indexing was also used to access various parts of the matrix. Matrix operations such as transpose, inverse, size and length were also shown. Part three of the experiment explained how a script was created and what it was...
Words: 828 - Pages: 4
...Solver: A solver is a generic term indicating a piece of mathematical software, possibly in the form of a stand-alone computer program or as a software library, that 'solves' a mathematical problem. A solver takes problem descriptions in some sort of generic form and calculates their solution. In a solver, the emphasis is on creating a program or library that can easily be applied to other problems of similar type. Types of problems with existing dedicated solvers include: * Linear and non-linear equations. In the case of a single equation, the "solver" is more appropriately called a root-finding algorithm. * Systems of linear equations. * Nonlinear systems. * Systems of polynomial equations, which are a special case of non linear systems, better solved by specific solvers. * Linear and non-linear optimisation problems * Systems of ordinary differential equations * Systems of differential algebraic equations * Logic/satisfiability problems * Constraint satisfaction problems * Shortest path problems * Minimum spanning tree problems * Search algorithms Excel Solver, introduced by Microsoft in 1991, is a powerful optimization application used in finance, production, distribution, purchasing and scheduling. It is part of the data analysis tools used for what-if analysis--a process of identifying changes in a cell by adjusting related cells. If your boss has asked you to find ways to increase company's profits by determining...
Words: 3603 - Pages: 15
...Instrucciones de EXCEL Solver Octubre de 2000 2 La opción Solver de EXCEL sirve para resolver problemas de optimización lineal y no lineal; también se pueden indicar restricciones enteras sobre las variables de decisión. Con Solver es posible resolver problemas que tengan hasta 200 variables de decisión, 100 restricciones explícitas y 400 simples (cotas superior e inferior o restricciones enteras sobre las variables de decisión). Para acceder a Solver, seleccione Tools en el menú principal y luego Solver. La ventana con los parámetros de Solver aparecerá tal y como se muestra a continuación: 3 Cuadro de diálogo de los parámetros de Solver La ventana Solver Parameters se utiliza para describir el problema de optimización a EXCEL. El campo Set Target Cell contiene la celda donde se encuentra la función objetiva correspondiente al problema en cuestión. Si desea hallar el máximo o el mínimo, seleccione Max o Min. Si la casilla Value of está seleccionada, Solver tratará de hallar un valor de la celda igual al valor del campo que se encuentra a la derecha de la selección. El cuadro de diálogo By Changing Cells contendrá la ubicación de las variables de decisión para el problema. Por último, las restricciones se deben especificar en el campo Subject to the Constraints haciendo clic en Add. El botón Change permite modificar las restricciones recién introducidas y Delete sirve para borrar las restricciones precedentes. Reset All borra el problema en curso y restablece todos...
Words: 1383 - Pages: 6
...Linear Programming: Using Solver in Excel Linear Programming was conceptually developed before World War II by the outstanding Russian mathematician A.N.Kolmogorov and gained its popularity ever since the development of Simplex method by George B. Dantzig in 1947. Linear programming deals with problems of maximizing or minimizing a linear function in the presence of linear equality and/or inequality constraints. In these problems, we find the optimal, or most efficient way of using limited resources to achieve the objective of the situation. Linear Programming enables users to model large and complex problems and solve in a short amount of time by the use of effective algorithm, hence it is a powerful and widely used tool in various fields such as science, industrial engineering, financial planning and management decision making. Nowadays, with the development of technology, most of the real world Linear Programming problems are solved by computer programs. Excel Solver is a popular one. We work through different examples to demonstrate the applications of linear Programming model and the use of Excel Solver for various decision making in operation and supply chain management. Components of Linear Programming model To solve the linear programming problems, we first need to formulate the mathematical description called a mathematical model to represent the situation. Linear programming model usually consists of the following components * Decision variables: These represent...
Words: 2395 - Pages: 10
...Deadlock Detector and Solver Abstract Deadlock is one of the most serious and complex problems concerning the reliability of concurrent Java Programs. This paper presents Deadlock Detector and Solver which detects and resolves circular deadlocks of a java program. An agent written in C++ runs parallel to Java Program and monitors the Java Virtual Machine for deadlocks. If the deadlock is detected, the solver agent is used to resolve the deadlock . Introduction The onset of multicore processors forces the programmers to use multiple threads in order to take advantage of hardware parallelism. Java is one of the first languages to make multithreading available to developers. Along with advantages of concurrent systems and multithreading, there are some challenges involved. Java has inter-process communication model which means it has set of methods for exchange of data among multiple threads and processes. It is based on shared data structures and meshes well with hardware architectures in which multiple cores share memory. However Java is susceptible to deadlocks. Deadlock is a condition under which the entire program is halted as each thread in a set attempts to acquire a lock already held by another thread in a set. Java is susceptible to deadlocks because (a) threads exchanges information by sharing variables that they lock with mutex locks and (b) the locking mechanism interacts with other language features, such as aliasing. Consider a simple banking transaction example...
Words: 3641 - Pages: 15
...Constraint Logic Programming in Prolog: Hanjie Puzzle Solver Lu´ Cleto and Jo˜o Marinheiro ıs a FEUP-PLOG, Turma 3MIEIC05, Group 23 {ei11077,ei11129}@fe.up.pt http://www.fe.up.pt Abstract. The purpose of this project was to use constraint logic programming in Prolog to implement a solver for the 2D puzzle, Hanjie. For this purpose we used the clp(FD) library provided by SICStus Prolog 4.2.3, specifically the sum/3 and automaton/3 combinatorial constraints. The program we developed is able to solve puzzles with dimensions up to 88x88, with only one possible solution, in less than one second. When there are multiple solutions, the execution time for the obtaining the first solution varies with the number of possible solutions. These results show that the execution time of the program is primarily affected by the amount of possible results. While larger grid dimensions do increase the execution time, the increase is linear if the number of possible solutions is maintained. On the other hand, increasing the number of possible solutions will lead to an exponential growth in execution time. 1 Introduction The goal of this project is to use constraint logic programming in Prolog to develop a logic program capable of solving a decision problem in the form of the 2D puzzle, Hanjie. This puzzle consists of a rectangular grid with ’clues’ on top of every column and to the left of every row that indicate the number and length of gray blocks in that column/row. To achieve this goal, first...
Words: 9959 - Pages: 40
...Published: Wednesday April 2, 2014 MYT 12:20:00 PM Updated: Wednesday April 2, 2014 MYT 1:32:48 PM Malaysian students are poor problem solvers, survey says BY PATRICK LEE Email Facebook 1K PETALING JAYA: When it comes to problem-solving, Malaysian 15year-olds are among of the world’s poorest, a survey by Programme for International Student Assessment (PISA) has determined. The 2012 survey conducted by the Organisation for Economic Cooperation and Development (OECD) found that more than one in five Malaysian students could not even reach basic levels of problem solving. Malaysia came in at 39th with a mean score of 422 points among the 44 countries surveyed. In comparison, Singapore topped the list of countries with 562 points. Malaysia’s score also put it below the OECD average of 500 points. The survey divided problem solving proficiency into six levels, with Level 6 being the highest and Level 1 being the lowest, though it was added that there was a level below Level 1. “... and in Uruguay, the United Arab Emirates, Montenegro, Malaysia, Brazil and Israel, more than one in five students do not reach this level (Level 1),” the report said. The PISA 2012 report said students proficient at Level 1 can only explore problems given to them in a limited manner. “In general, students at Level 1 can solve straightforward problems provided there is only a simple condition to be satisfied and there are only one or two steps to be performed to reach that goal.” According to the...
Words: 1561 - Pages: 7
...concept aims to captivate a large pool of personnel with differing skills sets, commonly referred to as solvers, to provide solutions to those who desire particular skills for their problem, called the seekers. Common value propositions: For seekers: • Efficient and effective completion of specified tasks. • Employment on a ‘need’ basis for cost savings. • Vast skills pool to find the most suitable candidates for the job. • ‘Open Innovation’ (Chesbrough, H. 2003). For solvers: • Vast job opportunities. • Specified workload and financial reward. • Flexibility of working hours and location. • Gain experience in various fields of work according to own preference. 1.2 Crowdsourcing Platforms Analysis Platform Website Specific Value Propositions Mechanical Turk Seekers – Individuals and companies seeking extensive labour. • Opportunities open to the general public as jobs focus on the division of labour for simple tasks requiring minimal innovation or technicality. • Fixed payment, easy to calculate based on ‘HITS’ (Amazon Mechanical Turk Inc. 2015). Solvers – any individual with free time. Freelancer Seekers – Individuals and businesses seeking innovative fashion design. • Requests of design announced gaining response from various designers to choose from (Freelancer Technology Pty Ltd, 2015). • High degree of communication between seeker and solver for idea...
Words: 1997 - Pages: 8
...1 Mathematical Programming The Mathematical Programming Add-in constructs models that can be solved using the Solver Add-in or one of the solution add-ins provided in the collection. When the Math Programming add-in is installed, several new command lines are added to the OR_MM menu. The menu items under the title Math Programming create models of the different types. Selecting an item from this list causes a dialog box to be presented which constructs a mathematical programming model. The models created by the add-in are solved with the Excel Solver, the Jensen Network Solver or the Jensen LP/IP Solver. All are Excel add-ins. Documentation for these programs can be reached by clicking the links on the lower left. The Solver add-in comes with Excel, and it can solve linear programming, integer programming and nonlinear programming models. The Math Programming add-in automatically builds Solver models and calls the computational procedures that solve the problems. All four model types can be can be solved in this way. The Jensen LP/IP Solver solves linear or integer programming problems. It is available for the Linear/Integer Programming and Network Flow Programming model types. The Jensen Network Solver can solve pure or generalized network flow models. Both linear and integer problems can be solved. It is available for the Network Flow Programming or Transportation model types. Parametric analysis can be applied to any of the math programming models. Here one parameter...
Words: 12583 - Pages: 51
...capability to solve linear (and often nonlinear) programming problems. The SOLVER tool in Excel: May be used to solve linear and nonlinear optimization problems Allows integer or binary restrictions to be placed on decision variables Can be used to solve problems with up to 200 decision variables 2 How to Install SOLVER: The SOLVER Add-in is a Microsoft Office Excel add-in program that is available when you install Microsoft Office or Excel. To use the Solver Add-in, however, you first need to load it in Excel. The process is slightly different for Mac or PC users. Microsoft: 1. Click the Microsoft Office Button , and then click Excel Options. 2. Click Add-Ins, and then in the Manage box, select Excel Add-ins and click Go. 3. In the Add-Ins available box, select the Solver Add-in check box, and then click OK. If Solver Add-in is not listed in the Add-Ins available box, click Browse to locate the add-in. If you get prompted that Solver is not currently installed, click Yes to install it. 4. After you load Solver, the Solver command is available in the Analysis group on the Data tab. MAC: 1. Open Excel for Mac 2011 and begin by clicking on the Tools menu. 2. Click Add-Ins, and then in the Add-Ins box, check Solver.xlam and then click OK. 3. After restarting Excel for Mac 2011 (fully Quit Excel 2011), select the Data tab, then select Solver to launch 3 How to Use SOLVER: The key to solving an LP on a spreadsheet is: Set up a spreadsheet that...
Words: 2523 - Pages: 11
...Assignment Problems Chapter 13: Decision Analysis Faculty Pankaj Dutta Chapter – 11 Linear Programming LPP solution through Excel Solver: The Steps in Implementing an LP Model in a Spreadsheet: 1. 2. 3. 4. Organize the data for the model on the spreadsheet. Reserve separate cells in the spreadsheet for each decision variable in the model. Create a formula in a cell in the spreadsheet that corresponds to the objective function. For each constraint, create a formula in a separate cell in the spreadsheet that corresponds to the left-hand side (LHS) of the constraint. PANKAJ DUTTA IMTCDL Chapter – 11 Linear Programming LPP solution through Excel Solver: Max Z = 350X1 + 300X2 Subject To 1X1 + 1X2 ≤ 200 9X1 + 6X2 ≤ 1566 12X1+16X2 ≤ 2880 X1 , X 2 ≥ 0 1. Organize the data for the model on the spreadsheet. PANKAJ DUTTA IMTCDL Chapter – 11 Linear Programming LPP solution through Excel Solver: Max Z = 350X1 + 300X2 Subject To 1X1 + 1X2 ≤ 200 9X1 + 6X2 ≤ 1566 12X1+16X2 ≤ 2880 X1 , X 2 ≥ 0 1. Organize the data for the model on the spreadsheet. Changing cells Target cell PANKAJ DUTTA IMTCDL Constraint cells Chapter – 11 Linear Programming Max Z = 350X1 + 300X2 Subject To 1X1 + 1X2 ≤ 200 9X1 + 6X2 ≤ 1566 12X1+16X2 ≤ 2880 X1 , X 2 ≥ 0 LPP solution through Excel Solver: 1. Organize the data for the model on the spreadsheet. – Target cell - the cell in the spreadsheet that represents the objective function ...
Words: 798 - Pages: 4
...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 requirements. Formulating LP Models The first step in model formulation is the development of the constraints. For example: Investment decisions are restricted by the amount of capital and government regulations. Management decisions are restricted by plant capacity and availability of resources...
Words: 4037 - Pages: 17