Linear Programming Using Excel
Subject: Linear Programming using Excel Application: Microsoft Excel 2007 Task: Solving a Linear Program Using Excel Tutorial Date: 25th February, 2010 by Nathan Smith

Install the Solver Add-In

1. In the Microsoft Office button, go to excel options to click Add-ins 2. In the Add-Ins box, select Solver Add-In and click Go...

Linear Programming Using Excel - 1

Install the Solver Add-In (continue)

3. In the Add-Ins available box, check the Analysis ToolPak and then OK

Linear Programming Using Excel - 2

Setting Up the Problem on the Spreadsheet

Example Min Z = 6X + 7Y s.t 2X + 6Y â‰¥ 10 5X + 3Y â‰¥ 10 X,Y â‰¥ 0

Linear Programming Using Excel - 3

(continued)

1. Enter the coefficients of the objective function Z i.e., (6, 7) in cells E5 and F5. 2. Enter the coefficients of the Constraint-1 i.e., (2,6) and RHS value 10 in cells E9, F9 and H9 respectively 3. Enter the coefficients of the Constraint-2 i.e., (5,3) and RHS value 10 in cells E10, F10 and H10 respectively

Linear Programming Using Excel - 4

(continued)

1. For the Objective function value, enter the formula for computing Z = SUMPRODUCT(E5:F5,E6:F6). This formula uses the coefficient values and also the solution values for variables X and Y, which are supposed to be solved. 2. Similarly enter the formula for LHS of the Constraints 1 & 2 i.e., SUMPRODUCT(E9:F9,$E$6:$F$6) & SUMPRODUCT(E10:F10,$E$6:$F$6) respectively

Linear Programming Using Excel - 5

Now Excel Solver will be used, in the Data tab click Solver. The solver box appears as follows.

(continued)

1. Set the Target Cell for the Objective Function Z value i.e., $E$7 2. Check the Equal to Min i.e., Minimum Option. 3. For Changing Cell, select the solution values of the variables X & 7 i.e., $E$6:$F$6 Linear Programming Using Excel - 6

4. For subject to the constraints, LHS >=RHS i.e., click on the Add option and select $E$13:$E$14 >= $E$13:$E$14 5. Also all the...

...The development of linearprogramming has been ranked among the most important scientific advances of the mid 20th century. Its impact since the 1950’s has been extraordinary. Today it is a standard tool used by some companies (around 56%) of even moderate size. Linearprogramming uses a mathematical model to describe the problem of concern. Linearprogramming involves the planning of activities to obtain an optimal result, i.e., a result that reaches the specified goal best (according to the mathematical model) among all feasible alternatives.
LinearProgramming as seen by various reports by many companies has saved them thousands to even millions of dollars. Since this is true why isn’t everyone using LinearProgramming? Maybe the reason is because there has never been an in-depth experiment focusing on certain companies that do or do not use linearprogramming. My main argument is that linearprogramming is one of the most optimal ways of resource allocation and making the most money for any company today.
I used (in conjunction with another field supporter – My Dad) the survey method to ask 28 companies that were in Delaware, New Jersey, and Pennsylvania whether they were linearprogramming users. In addition, I wanted to examine the effect of...

...RESEARCH PAPER ON
LINEARPROGRAMMING
Vikas Vasam
ID: 100-11-5919
Faculty: Prof. Dr Goran Trajkovski
CMP 561: Algorithm Analysis
VIRGINIA INTERNATIONAL UNIVERSITY
Introduction:
One of the section of mathematical programming is linearprogramming.
Methods and linearprogramming models are widely used in the optimization of processes in all sectors of the economy: the development of the production program of the company, its distribution on the performers, when placing orders between the performers and the time intervals, to determine the best range of products, in problems of perspective, current and operational planning and management, traffic planning, defining a plan of trade and distribution, in the problems of development and distribution of productive forces, bases and depots of material handling systems, resources, etc. especially widely used methods and linearprogramming model for solving problems are savings (choice of resource-saving technologies, preparation of mixes, nesting materials), production, transportation and other tasks.
Beginning of linearprogramming was initiated in 1939 by the Soviet mathematician and economist Kantorovich in his paper "Mathematical methods of organizing and planning production." The appearance of...

...TOPIC – LINEARPROGRAMMINGLinearProgramming is a mathematical procedure for determining optimal allocation of scarce resources.
Requirements of LinearProgramming
• all problems seek to maximize or minimize some quantity
• The presence of restrictions or constraints
• There must be alternative courses of action
• The objective and constraints inlinearprogramming must be expressed in terms of linear equations or
inequalities
Objective Function it maps and translates the input domain (the feasible region) into output range, with
the two-end values called the maximum and minimum values
Restriction Constraints it limits the degree to which we can pursue our objective
Decision Variables represents choices available to the decision maker in terms of amount of either inputs or outputs
Parameters these are the fixed values in which the model is solved
Basic Assumption of LinearProgramming
1. Certainty- figures or number in the objective and constraints are known with certainty and do not vary
1. Proportionality - for example 1:2 is equivalent to 5:10
1. Additivity - the total of all the activities equals the sum of the individual...

... LINEARPROGRAMMING
DATE;
5 JUNE, 14
UNIVERSITY OF CENTRAL PUNJAB
INTRODUCTION TO LINEARPROGRAMMINGLinearprogramming (LP; also called linear optimization) is a method to achieve the best outcome (such as maximum profit or lowest cost) in a mathematical model whose requirements are represented by linear relationships. Linearprogramming is a special case of mathematical programming.
It is a mathematical technique used in computer modeling to find the best possible solution in allocating limited resources (energy, materials, machines, money etc) to achieve maximum profit or minimum cost.
LinearProgramming is a method of expressing and optimizing a business problem with a mathematical model. It is one of the most powerful and widespread business optimization tools.
Linearprogramming can be used in very large variety of business problems. They include:
transportation distribution problems
production scheduling in oil & gas, manufacturing, chemical, etc industries
financial and tax planning
human resource planning
facility planning
fleet scheduling.
LINEARPROGRAMMING; an optimization technique capable of solving an amazingly...

...LINEARPROGRAMMING
INTRODUCTION:
The term ‛programming′ means planning and it refers to a particular plan of action amongst several alternatives for maximizing profit or minimizing cost etc. Programming problems deal with determining optimal allocation of limited resources to meet the given objectives, such as cost, maximum profit, highest margin or least time, when resources have alternative uses.
The term ‛linear’ means that all inequations or equations used and the function to be maximized or minimized are linear. That is why linearprogramming deals with that class of problems for which all relations among the variables involved are linear.
Formally, linearprogramming deals with the optimization (maximization or minimization) of a linear function of a number of variables subject to a ¹equations in variables involved.
The general form of a linearprogramming problem is
Optimize (Maximize or Minimize) Z = c1x1 + c2x2 + ……..+ cnxn
Subject to
a11 x1 + a12x2 + ….. + a1n xn (≤ , = , ≥) b1
a21 x1+ a22x2+ ….. + a2nxn (≤ , = , ≥ ) b2
. . . .
am1 x1+ am2 x2 +...

...EXCELSOLVER TUTORIAL
Many firms face the problem of how to best use multiple scarce resources. Linearprogramming is designed to help find the product mix that maximizes profits in the short run when multiple constraints exist. While linearprogramming can be solved as a mathematical problem using pencil and paper, it is much more efficient to use ExcelSolver. The key to using ExcelSolver is to make certain you have modeled the problem correctly and then interpreted the results appropriately. In this problem we will practice the use of Solver.
In this example suppose that you manufacture regular and premium golf carts. The selling price, variable costs and manufacturing times are as follows:
Regular
Premium
SALES PRICE
$ 8,000
$ 10,000
VARIABLE COST
5,600
6,500
CONTRIBUTION MARGIN
$ 2,400
$ 3,500
Assembly hours
20
50
Inspect and Test
5.0
2.5
Your company currently has 10,000 hours available for assembly and 1,200 hours for inspection and testing. There is also a limit to how many premium golf carts that can be sold (150 max). Given this information if you want to maximize profits what mix of regular and premium golf carts should you produce?
SOLUTION:
1. Input the above data into an Excel Spreadsheet...

...Spreadsheet Modeling and ExcelSolver A mathematical model implemented in a spreadsheet is called a spreadsheet model. Major spreadsheet packages come with a built-in optimization tool called Solver. Now we demonstrate how to use Excel spreadsheet modeling and Solver to find the optimal solution of optimization problems. If the model has two variables, the graphical method can be used to solve the model. Very few real world problems involve only two variables. For problems with more than two variables, we need to use complex techniques and tedious calculations to find the optimal solution. The spreadsheet and solver approach makes solving optimization problems a fairly simple task and it is more useful for students who do not have strong mathematics background. The first step is to organize the spreadsheet to represent the model. We use separate cells to represent decision variables, create a formula in a cell to represent the objective function and create a formula in a cell for each constraint left hand side. Once the model is implemented in a spreadsheet, next step is to use the Solver to find the solution. In the Solver, we need to identify the locations (cells) of objective function, decision variables, nature of the objective function (maximize/minimize) and constraints. Example One (Linear model): Investment Problem Our first example...

...LP (2003) 1
OPERATIONS RESEARCH: 343
1. LINEARPROGRAMMING 2. INTEGER PROGRAMMING 3. GAMES
Books: Ð3Ñ IntroÞ to OR ÐF.Hillier & J. LiebermanÑ; Ð33Ñ OR ÐH. TahaÑ; Ð333Ñ IntroÞ to Mathematical Prog ÐF.Hillier & J. LiebermanÑ; Ð3@Ñ IntroÞ to OR ÐJ.Eckert & M. KupferschmidÑÞ
LP (2003) 2
LINEARPROGRAMMING (LP)
LP is an optimal decision making tool in which the objective is a linear function and the constraints on the decision problem are linear equalities and inequalities. It is a very popular decision support tool: in a survey of Fortune 500 firms, 85% of the responding firms said that they had used LP. Example 1: Manufacturer Produces: Ingredients used in the production of A & C: Each ton of A requires: Each ton of C requires: Supply of X is limited to: Supply of Y is limited to: 1 ton of A sells for: 1 ton of C sells for: A (acid) and C (caustic) X and Y 2lb of X; 1lb of Y 1lb of X ; 3lb of Y 11lb/week 18lb/week £1000 £1000
Manufacturer wishes to maximize weekly value of sales of A & C. Market research indicates no more than 4 tons of acid can be sold each week. How much A & C to produce to solve this problem. The answer is a pair of numbers: x" Ðweekly production of AÑ, x# Ðweekly p.of CÑ There are many pairs of numbers Ðx" , x# Ñ: Ð0,0Ñ, Ð1,1Ñ, Ð3,5Ñ.... Not all pairs Ðx" , x# Ñ are possible weekly productions Ðex. x" œ 27, x# œ 2 are not possibleÑ Ð Ð27,...

11494 Words |
44 Pages

Share this Document

{"hostname":"studymode.com","essaysImgCdnUrl":"\/\/images-study.netdna-ssl.com\/pi\/","useDefaultThumbs":true,"defaultThumbImgs":["\/\/stm-study.netdna-ssl.com\/stm\/images\/placeholders\/default_paper_1.png","\/\/stm-study.netdna-ssl.com\/stm\/images\/placeholders\/default_paper_2.png","\/\/stm-study.netdna-ssl.com\/stm\/images\/placeholders\/default_paper_3.png","\/\/stm-study.netdna-ssl.com\/stm\/images\/placeholders\/default_paper_4.png","\/\/stm-study.netdna-ssl.com\/stm\/images\/placeholders\/default_paper_5.png"],"thumb_default_size":"160x220","thumb_ac_size":"80x110","isPayOrJoin":false,"essayUpload":false,"site_id":1,"autoComplete":false,"isPremiumCountry":false,"userCountryCode":"US","logPixelPath":"\/\/www.smhpix.com\/pixel.gif","tracking_url":"\/\/www.smhpix.com\/pixel.gif","cookies":{"unlimitedBanner":"off"},"essay":{"essayId":35061874,"categoryName":"Software Development","categoryParentId":"5","currentPage":1,"format":"text","pageMeta":{"text":{"startPage":1,"endPage":3,"pageRange":"1-3","totalPages":3}},"access":"premium","title":"Excel Solver for Linear Programming","additionalIds":[3,17,52,27],"additional":["Business \u0026 Economy","Literature","Business \u0026 Economy\/Organizations","Sports \u0026 Recreation"],"loadedPages":{"html":[],"text":[1,2,3]}},"user":null,"canonicalUrl":"http:\/\/www.studymode.com\/essays\/Excel-Solver-For-Linear-Programming-766264.html","pagesPerLoad":50,"userType":"member_guest","ct":10,"ndocs":"1,500,000","pdocs":"6,000","cc":"10_PERCENT_1MO_AND_6MO","signUpUrl":"https:\/\/www.studymode.com\/signup\/","joinUrl":"https:\/\/www.studymode.com\/join","payPlanUrl":"\/checkout\/pay","upgradeUrl":"\/checkout\/upgrade","freeTrialUrl":"https:\/\/www.studymode.com\/signup\/?redirectUrl=https%3A%2F%2Fwww.studymode.com%2Fcheckout%2Fpay%2Ffree-trial\u0026bypassPaymentPage=1","showModal":"get-access","showModalUrl":"https:\/\/www.studymode.com\/signup\/?redirectUrl=https%3A%2F%2Fwww.studymode.com%2Fjoin","joinFreeUrl":"\/essays\/?newuser=1","siteId":1,"facebook":{"clientId":"306058689489023","version":"v2.8","language":"en_US"},"analytics":{"googleId":"UA-32718321-1"}}