Algebra 2 Linear Programming Worksheet Answers

[Raili Schmoldt]

Thetutorial explains how to add and where to find Solver in different Excel versions, from 2016 to 2003. Step-by-step examples show how to use Excel Solver to find optimal solutions for linear programming and other kinds of problems.

Everyone knows that Microsoft Excel contains a lot of useful functions and powerful tools that can save you hours of calculations. But did you know that it also has a tool that can help you find optimal solutions for decision problems?

Excel Solver belongs to a special set of commands often referred to as What-if Analysis Tools. It is primarily purposed for simulation and optimization of various business and engineering models.

The Excel Solver add-in is especially useful for solving linear programming problems, aka linear optimization problems, and therefore is sometimes called a linear programming solver. Apart from that, it can handle smooth nonlinear and non-smooth problems. Please see Excel Solver algorithms for more details.

While Solver can't crack every possible problem, it is really helpful when dealing with all kinds of optimization problems where you need to make the best decision. For example, it can help you maximize the return of investment, choose the optimal budget for your advertising campaign, make the best work schedule for your employees, minimize the delivery costs, and so on.

How to add Solver to ExcelThe Solver add-in is included with all versions of Microsoft Excel beginning with 2003, but it is not enabled by default.

Note. If Excel displays a message that the Solver Add-in is not currently installed on your computer, click Yes to install it.Where is Solver in Excel?In the modern versions of Excel, the Solver button appears on the Data tab, in the Analysis group:

Where is Solver in Excel 2003?After the Solver Add-in is loaded to Excel 2003, its command is added to the Tools menu:

Note. The examples discussed in this tutorial use Solver in Excel 2013. If you have another Excel version, the screenshots may not match your version exactly, although the Solver functionality is basically the same.How to use Solver in ExcelBefore running the Excel Solver add-in, formulate the model you want to solve in a worksheet. In this example, let's find a solution for the following simple optimization problem.

Problem. Supposing, you are the owner of a beauty salon and you are planning on providing a new service to your clients. For this, you need to buy a new equipment that costs $40,000, which should be paid by instalments within 12 months.

Exactly what does Excel Solver do with the above parameters? It finds the optimal value (maximum, minimum or specified) for the formula in the Objective cell by changing the values in the Variable cells, and subject to limitations in the Constraints cells.

The Objective cell (Target cell in earlier Excel versions) is the cell containing a formula that represents the objective, or goal, of the problem. The objective can be to maximize, minimize, or achieve some target value.

In this example, the objective cell is B7, which calculates the payment term using the formula =B3/(B4*B5) and the result of the formula should be equal to 12:

Variable cellsVariable cells (Changing cells or Adjustable cells in earlier versions) are cells that contain variable data that can be changed to achieve the objective. Excel Solver allows specifying up to 200 variable cells.

Tip. If the variable cells or ranges in your model are non-adjacent, select the first cell or range, and then press and hold the Ctrl key while selecting other cells and/or ranges. Or, type the ranges manually, separated with commas.ConstraintsThe Excel Solver Constrains are restrictions or limits of the possible solutions to the problem. To put it differently, constraints are the conditions that must be met.

3. Solve the problemAfter you've configured all the parameters, click the Solve button at the bottom of the Solver Parameters window (see the screenshot above) and let the Excel Solver add-in find the optimal solution for your problem.

Now that you've got the basic idea of how to use Solver in Excel, let's have a closer look at a couple more examples that might help you gain more understanding.

Excel Solver examplesBelow you will find two more examples of using the Excel Solver addin. First, we will find a solution for a well-known puzzle, and then solve a real-life linear programming problem.

To begin with, enter the numbers from 1 to 9 in a table consisting of 3 rows and 3 columns. The Excel Solver does not actually need those numbers, but they will help us visualize the problem. What the Excel Solver add-in really needs are the SUM formulas that total each row, column and 2 diagonals:

Finally, click the Solve button, and the solution is there!

Excel Solver example 2 (linear programming problem)This is an example of a simple transportation optimization problem with a linear objective. More complex optimization models of this kind are used by many companies to save thousands of dollars each year.

The next thing for you to do is to calculate the total quantity shipped from each warehouse (G7:G8), and the total goods received by each customer (B9:E9). You can do this with simple Sum formulas demonstrated in the below screenshot. Also, insert the SUMPRODUCT formula in C12 to calculate the total cost of shipping:

Please pay attention that we've chosen the Simplex LP solving method in this example because we are dealing with the linear programming problem. If you are not sure what kind of problem yours is, you can leave the default GRG Nonlinear solving method. For more information, please see Excel Solver algorithms.

SolutionClick the Solve button at the bottom of the Solver Parameters window, and you will get your answer. In this example, the Excel Solver add-in calculated the optimal quantity of goods to deliver to each customer from each warehouse with the minimal total cost of shipping:

And here is one more example of practical use: Find all combinations that equal given sum with Excel Solver.

How to save and load Excel Solver scenariosWhen solving a certain model, you may want to save your Variable cell values as a scenario that you can view or re-use later.

For example, when calculating the minimal service cost in the very first example discussed in this tutorial, you may want to try different numbers of projected clients per month and see how that affects the service cost. At that, you may want to save the most probable scenario you've already calculated and restore it at any moment.

Saving an Excel Solver scenario boils down to selecting a range of cells to save the data in. Loading a Solver model is just a matter of providing Excel with the range of cells where your model is saved. The detailed steps follow below.

At the same time, the Solver Parameters window will show up where you can change your constraints and try different "what if" options.

Loading the saved modelWhen you decide to restore the saved scenario, do the following:

To change how Solver finds a solution, click the Options button in the Solver Parameters dialog box, and configure any or all options on the GRG Nonlinear, All Methods, and Evolutionary tabs.

This is how you can use Solver in Excel to find the best solutions for your decision problems. At the end of this post, you can download the sample workbook with all the examples discussed in this tutorial and reverse-engineer them for better understanding. I thank you for reading and hope to see you on our blog next week.

You decide to include the cost of making the combo as you purchase from different

suppliers. The packaging costs vary between R25 and R15. Determine how many

units you need to sell daily to still get a profit of R18 000 at each varying packaging

cost when selling the combo at R55 each. Use the Solver function.

The phrase "provided there are at least 50 clients per month" relates to a particular solution shown in the screenshot - cost per service ($66.67) was calculated assuming there are 50 clients per month (see the value in B4). B4

Is it possible to solve this problem using solver?

I have two groups of items lets say we have repairmen and repair contracts. Based on repairmen scoring and contract scoring i need the best match. Scoring of both is more than single number but a set of scores in the given scale. For example repairmen have 3 scores from 1-5 and the same with contracts. Can solver help with best match of workers to contracts?

Solve the problem by linear programming using an Excel spreadsheet model (Solver function),

remembering that the Client's objective is to maximise total annual income. State the optimal

investments plan clearly, giving the values of all the problem variables. Include a copy of your

spreadsheet, making sure that the layout of the spreadsheet is easy to follow and is carefully

annotated. The layout should be your own design.

I work for a German company so my Windows 10 is in German with a comma as the decimal separator. Most of our spreadsheets need to be in English for our international clients. If I change the decimal separator from within Excel 2016 from the system decimal separator (comma) to a decimal point, I find that non-integer Solver constraints get altered by themselves. For example if I enter 1.2 as the value of a constraint on a cell and run Solver I get the solution I would expect. If I then rerun Solver after changing a value somewhere in the spreadsheet I get a different answer, because the value of the constraint has been "magically" changed to 12. If I use the system separator (comma) in Excel the Solver does not remove the decimal separator from the contraints.

Does anyone else have this problem? Have you found a solution - apart from calculating the sheet with the system separator then changing the decimal separator for the printout, that is?

3a8082e126