HOW TO ADD SOLVER IN EXCEL 2013 SOFTWARE

To solve this problem, simply add the constraint that the binary changing cell for Project 3 is less than or equal to the binary changing cell for Project 4.Д¬ell L9 refers to the binary value related to Project 3, and cell L12 to the binary value related to Project 4. Because the current optimal solution selects Project 3 but not Project 4, this tells you that the current solution canвЂ™t remain optimal. For example, suppose that if you select Project 3, you must also select Project 4. Sometimes project selection models have other constraints. The company can obtain a maximum NPV of $9,293 million ($9.293 billion) by choosing Projects 2, 3, 6вЂ“10, 14вЂ“16, 19, and 20. With the Solver Parameters dialog box filled in, I click Solve and get the results shown earlier in first screenshot above. Therefore, I select the Simplex LP engine. This model is linear because the target cell is computed as the sum of terms that have the form (changing cell)*(constant) and because the resource usage constraints are computed by comparing the sum of (changing cells)*(constants) to a constant. Use the Bin option in the Add Constraint dialog box to set up binary changing cells-cells that display either a 0 or a 1. I now fill in the Solver Parameters dialog box as shown below In a similar fashion, I compute the capital used each year and the labor used each year by copying from E2 to F2:J2 the formula SUMPRODUCT(doit,E6:E25). Therefore, IвЂ™m able to compute the NPV of all projВects, and the target cell is linear because it is computed by summing terms that follow the form (changing cell)*(constant). (The range name NPV refers to the range C6:C25.) For every project with a 1 in column A, this forВmula picks up the NPV of the project, and for every project with a 0 in column A, this formula does not pick up the NPV of the project. In cell B2, I use the formula SUMPRODUCT(doit,NPV) to compute the total NPV generated by selected projects. в– Constraints You need to ensure that for each Year t ( t=1, 2, 3), Year t capital used is less than or equal to Year t capital available, and Year t labor used is less than or equal to Year t labor available.ДЄs you can see, the worksheet must compute for any selection of projects the NPV, the capital used annually, and the programmers used each year. For example, a 1 in cell A6 indicates that you undertake Project 1 a 0 in cell C6 indicates that you donвЂ™t undertake Project 1. IвЂ™ve located these cells in the range A6:A25 (and named the range doit). в– Changing cells Look for a 0 or 1 binary changing cell for each project. в– Target cell Maximize the NPV generated by selected projects. As always with a Solver model, you should begin by identifying the target cell, the changing cells, and the constraints.

HOW TO ADD SOLVER IN EXCEL 2013 SOFTWARE

With this background, youвЂ™re ready to solve the software project selection problem. You set up Solver to use a range of binary changing cells by adding a constraint: select the changing cells you want to use, and then choose Bin from the list in the Add Constraint dialog box.

If a binary changing cell that corresponds to a project equals 0, you donвЂ™t do the project. When a binary changing cell that corresponds to a project equals 1, you do the project. A binary changing cell always equals 0 or 1. The trick in modeling situations in which you either do or donвЂ™t do something is to use binary changing cells.

LetвЂ™s assume that the company canвЂ™t undertake a fraction of a software project if 0.5 of the needed resources is allocated, for example, the company would have a nonworking program that would bring in $0 revenue! The company must decide whether it should undertake each project. For example, during Year 1 up to $2.5 billion in capital and 900 programmers are available. Cells E4:G4 show the capital (in millions of dollars) available during each of the three years, and cells H4:J4 indicate how many programmers are available. Project 2 requires 139 programmers during Year 1, 86 programmers during Year 2, and 83 programmers during Year 3. It requires $151 million during Year 1, $269 million during Year 2, and $248 million during Year 3. Data we will use with Solver to determine which projects to undertakeДЇor example, Project 2 yields $908 million.

How to add solver in excel 2013