solve optimization problems excel

Using Solver to determine the optimal product mix

This article discusses using Solver, a Microsoft Excel add-in program you can use for what-if analysis, to determine an optimal product mix.

How can I determine the monthly product mix that maximizes profitability?

Companies often need to determine the quantity of each product to produce on a monthly basis. In its simplest form, the product mix problem involves how to determine the amount of each product that should be produced during a month to maximize profits. Product mix must usually adhere to the following constraints:

Product mix can’t use more resources than are available.

There is a limited demand for each product. We can’t produce more of a product during a month than demand dictates, because the excess production is wasted (for example, a perishable drug).

Let’s now solve the following example of the product mix problem. You can find the solution to this problem in the file Prodmix.xlsx, shown in Figure 27-1.

Let’s say we work for a drug company that produces six different products at their plant. Production of each product requires labor and raw material. Row 4 in Figure 27-1 shows the hours of labor needed to produce a pound of each product, and row 5 shows the pounds of raw material needed to produce a pound of each product. For example, producing a pound of Product 1 requires six hours of labor and 3.2 pounds of raw material. For each drug, the price per pound is given in row 6, the unit cost per pound is given in row 7, and the profit contribution per pound is given in row 9. For example, Product 2 sells for $11.00 per pound, incurs a unit cost of $5.70 per pound, and contributes $5.30 profit per pound. The month’s demand for each drug is given in row 8. For example, demand for Product 3 is 1041 pounds. This month, 4500 hours of labor and 1600 pounds of raw material are available. How can this company maximize its monthly profit?

If we knew nothing about Excel Solver, we would attack this problem by constructing a worksheet to track profit and resource usage associated with the product mix. Then we would use trial and error to vary the product mix to optimize profit without using more labor or raw material than is available, and without producing any drug in excess of demand. We use Solver in this process only at the trial-and-error stage. Essentially, Solver is an optimization engine that flawlessly performs the trial-and-error search.

A key to solving the product mix problem is to efficiently compute the resource usage and profit associated with any given product mix. An important tool that we can use to make this computation is the SUMPRODUCT function. The SUMPRODUCT function multiplies corresponding values in cell ranges and returns the sum of those values. Each cell range used in a SUMPRODUCT evaluation must have the same dimensions, which implies that you can use SUMPRODUCT with two rows or two columns, but not with one column and one row.

As an example of how we can use the SUMPRODUCT function in our product mix example, let’s try to compute our resource usage. Our labor usage is calculated by

(Labor used per pound of drug 1)*(Drug 1 pounds produced)+ (Labor used per pound of drug 2)*(Drug 2 pounds produced) + ... (Labor used per pound of drug 6)*(Drug 6 pounds produced)

We could compute labor usage in a more tedious fashion as D2*D4+E2*E4+F2*F4+G2*G4+H2*H4+I2*I4 . Similarly, raw material usage could be computed as D2*D5+E2* E5+F2*F5+G2*G5+H2*H5+I2*I5 . However, entering these formulas in a worksheet for six products is time-consuming. Imagine how long it would take if you were working with a company that produced, for example, 50 products at their plant. A much easier way to compute labor and raw material usage is to copy from D14 to D15 the formula SUMPRODUCT($D$2:$I$2,D4:I4) . This formula computes D2*D4+E2*E4+F2*F4+G2*G4+H2*H4+I2*I4 (which is our labor usage) but is much easier to enter! Notice that I use the $ sign with the range D2:I2 so that when I copy the formula I still capture the product mix from row 2. The formula in cell D15 computes raw material usage.

(Drug 1 profit per pound)*(Drug 1 pounds produced) + (Drug 2 profit per pound)*(Drug 2 pounds produced) + ... (Drug 6 profit per pound)*(Drug 6 pounds produced)

Profit is easily computed in cell D12 with the formula SUMPRODUCT(D9:I9,$D$2:$I$2) .

We now can identify the three components of our product mix Solver model.

Target cell. Our goal is to maximize profit (computed in cell D12).

Changing cells. The number of pounds produced of each product (listed in the cell range D2:I2)

Constraints. We have the following constraints:

Do not use more labor or raw material than is available. That is, the values in cells D14:D15 (the resources used) must be less than or equal to the values in cells F14:F15 (the available resources).

Do not produce more of a drug than is in demand. That is, the values in the cells D2:I2 (pounds produced of each drug) must be less than or equal to the demand for each drug (listed in cells D8:I8).

We can’t produce a negative amount of any drug.

I’ll show you how to enter the target cell, changing cells, and constraints into Solver. Then all you need to do is click the Solve button to find a profit-maximizing product mix!

To begin, click the Data tab, and in the Analysis group, click Solver.

Note: As explained in Chapter 26, "An Introduction to Optimization with Excel Solver," Solver is installed by clicking the Microsoft Office Button, then Excel Options, followed by Add-Ins. In the Manage list, click Excel Add-ins, check the Solver Add-in box, and then click OK.

The Solver Parameters dialog box will appear, as shown in Figure 27-2.

Click the Set Target Cell box and then select our profit cell (cell D12). Click the By Changing Cells box and then point to the range D2:I2, which contains the pounds produced of each drug. The dialog box should now look Figure 27-3.

We’re now ready to add constraints to the model. Click the Add button. You’ll see the Add Constraint dialog box, shown in Figure 27-4.

To add the resource usage constraints, click the Cell Reference box, and then select the range D14:D15. Select <= from the middle list. Click the Constraint box, and then select the cell range F14:F15. The Add Constraint dialog box should now look like Figure 27-5.

We have now ensured that when Solver tries different values for the changing cells, only combinations that satisfy both D14<=F14 (labor used is less than or equal to labor available) and D15<=F15 (raw material used is less than or equal to raw material available) will be considered. Click Add to enter the demand constraints. Fill in the Add Constraint dialog box as shown in Figure 27-6.

Adding these constraints ensures that when Solver tries different combinations for the changing cell values, only combinations that satisfy the following parameters will be considered:

D2<=D8 (the amount produced of Drug 1 is less than or equal to the demand for Drug 1)

E2<=E8 (the amount of produced of Drug 2 is less than or equal to the demand for Drug 2)

F2<=F8 (the amount produced of Drug 3 made is less than or equal to the demand for Drug 3)

G2<=G8 (the amount produced of Drug 4 made is less than or equal to the demand for Drug 4)

H2<=H8 (the amount produced of Drug 5 made is less than or equal to the demand for Drug 5)

I2<=I8 (the amount produced of Drug 6 made is less than or equal to the demand for Drug 6)

Click OK in the Add Constraint dialog box. The Solver window should look like Figure 27-7.

We enter the constraint that changing cells must be non-negative in the Solver Options dialog box. Click the Options button in the Solver Parameters dialog box. Check the Assume Linear Model box and the Assume Non-Negative box, as shown in Figure 27-8 on the next page. Click OK.

Checking the Assume Non-Negative box ensures that Solver considers only combinations of changing cells in which each changing cell assumes a non-negative value. We checked the Assume Linear Model box because the product mix problem is a special type of Solver problem called a linear model . Essentially, a Solver model is linear under the following conditions:

The target cell is computed by adding together the terms of the form (changing cell)*(constant) .

Each constraint satisfies the “linear model requirement.” This means that each constraint is evaluated by adding together the terms of the form (changing cell)*(constant) and comparing the sums to a constant.

Why is this Solver problem linear? Our target cell (profit) is computed as

This computation follows a pattern in which the target cell’s value is derived by adding together terms of the form (changing cell)*(constant) .

Our labor constraint is evaluated by comparing the value derived from (Labor used per pound of Drug 1)*(Drug 1 pounds produced) + (Labor used per pound of Drug 2)*(Drug 2 pounds produced)+ …(Labor us ed per pound of Drug 6)*(Drug 6 pounds produced) to the labor available.

Therefore, the labor constraint is evaluated by adding together the terms of the form (changing cell)*(constant) and comparing the sums to a constant. Both the labor constraint and the raw material constraint satisfy the linear model requirement.

Our demand constraints take the form

(Drug 1 produced)<=(Drug 1 Demand) (Drug 2 produced)<=(Drug 2 Demand) § (Drug 6 produced)<=(Drug 6 Demand)

Each demand constraint also satisfies the linear model requirement, because each is evaluated by adding together the terms of the form (changing cell)*(constant) and comparing the sums to a constant.

Having shown that our product mix model is a linear model, why should we care?

If a Solver model is linear and we select Assume Linear Model, Solver is guaranteed to find the optimal solution to the Solver model. If a Solver model is not linear, Solver may or may not find the optimal solution.

If a Solver model is linear and we select Assume Linear Model, Solver uses a very efficient algorithm (the simplex method) to find the model’s optimal solution. If a Solver model is linear and we do not select Assume Linear Model, Solver uses a very inefficient algorithm (the GRG2 method) and might have difficulty finding the model’s optimal solution.

After clicking OK in the Solver Options dialog box, we return to the main Solver dialog box, shown earlier in Figure 27-7. When we click Solve, Solver calculates an optimal solution (if one exists) for our product mix model. As I stated in Chapter 26, an optimal solution to the product mix model would be a set of changing cell values (pounds produced of each drug) that maximizes profit over the set of all feasible solutions. Again, a feasible solution is a set of changing cell values satisfying all constraints. The changing cell values shown in Figure 27-9 are a feasible solution because all production levels are non-negative, production levels do not exceed demand, and resource usage does not exceed available resources.

The changing cell values shown in Figure 27-10 on the next page represent an infeasible solution for the following reasons:

We produce more of Drug 5 than the demand for it.

We use more labor than what is available.

We use more raw material than what is available.

After clicking Solve, Solver quickly finds the optimal solution shown in Figure 27-11. You need to select Keep Solver Solution to preserve the optimal solution values in the worksheet.

Our drug company can maximize its monthly profit at a level of $6,625.20 by producing 596.67 pounds of Drug 4, 1084 pounds of Drug 5, and none of the other drugs! We can’t determine if we can achieve the maximum profit of $6,625.20 in other ways. All we can be sure of is that with our limited resources and demand, there is no way to make more than $6,627.20 this month.

Does a Solver model always have a solution?

Suppose that demand for each product must be met. (See the No Feasible Solution worksheet in the file Prodmix.xlsx.) We then have to change our demand constraints from D2:I2<=D8:I8 to D2:I2>=D8:I8 . To do this, open Solver, select the D2:I2<=D8:I8 constraint, and then click Change. The Change Constraint dialog box, shown in Figure 27-12, appears.

Select >=, and then click OK. We’ve now ensured that Solver will consider changing only cell values that meet all demands. When you click Solve, you’ll see the message "Solver could not find a feasible solution." This message does not mean that we made a mistake in our model, but rather that with our limited resources, we can’t meet demand for all products. Solver is simply telling us that if we want to meet demand for each product, we need to add more labor, more raw materials, or more of both.

What does is mean if a Solver model yields the result Set Values Do Not Converge?

Let’s see what happens if we allow unlimited demand for each product and we allow negative quantities to be produced of each drug. (You can see this Solver problem on the Set Values Do Not Converge worksheet in the file Prodmix.xlsx.) To find the optimal solution for this situation, open Solver, click the Options button, and clear the Assume Non-Negative box. In the Solver Parameters dialog box, select the demand constraint D2:I2<=D8:I8 and then click Delete to remove the constraint. When you click Solve, Solver returns the message "Set Cell Values Do Not Converge." This message means that if the target cell is to be maximized (as in our example), there are feasible solutions with arbitrarily large target cell values. (If the target cell is to be minimized, the message "Set Cell Values Do Not Converge" means there are feasible solutions with arbitrarily small target cell values.) In our situation, by allowing negative production of a drug, we in effect “create” resources that can be used to produce arbitrarily large amounts of other drugs. Given our unlimited demand, this allows us to make unlimited profits. In a real situation, we can’t make an infinite amount of money. In short, if you see “Set Values Do Not Converge,” your model does have an error.

Suppose our drug company can purchase up to 500 hours of labor at $1 more per hour than current labor costs. How can we maximize profit?

At a chip manufacturing plant, four technicians (A, B, C, and D) produce three products (Products 1, 2, and 3). This month, the chip manufacturer can sell 80 units of Product 1, 50 units of Product 2, and at most 50 units of Product 3. Technician A can make only Products 1 and 3. Technician B can make only Products 1 and 2. Technician C can make only Product 3. Technician D can make only Product 2. For each unit produced, the products contribute the following profit: Product 1, $6; Product 2, $7; and Product 3, $10. The time (in hours) each technician needs to manufacture a product is as follows:

Each technician can work up to 120 hours per month. How can the chip manufacturer maximize its monthly profit? Assume a fractional number of units can be produced.

A computer manufacturing plant produces mice, keyboards, and video game joysticks. The per-unit profit, per-unit labor usage, monthly demand, and per-unit machine-time usage are given in the following table:

Each month, a total of 13,000 labor hours and 3000 hours of machine time are available. How can the manufacturer maximize its monthly profit contribution from the plant?

Resolve our drug example assuming that a minimum demand of 200 units for each drug must be met.

Jason makes diamond bracelets, necklaces, and earrings. He wants to work a maximum of 160 hours per month. He has 800 ounces of diamonds. The profit, labor time, and ounces of diamonds required to produce each product are given below. If demand for each product is unlimited, how can Jason maximize his profit?

Need more help?

Want more options.

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Microsoft 365 subscription benefits

Microsoft 365 training

Microsoft security

Accessibility center

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Ask the Microsoft Community

Microsoft Tech Community

Windows Insiders

Microsoft 365 Insiders

How to solve optimization problems with Excel and Solver

Whether it's minimizing costs or maximizing returns, this excerpt from the book data smart shows you how to use excel's solver add-in.

By John W. Foreman

Computerworld |

An optimization problem is one where you have to make the best decision (choose the best investments, minimize your company's costs, find the class schedule with the fewest morning classes, or so on). In optimization models then, the words "minimize" and "maximize" come up a lot when articulating an objective.

In data science, many of the practices, whether that's artificial intelligence, data mining, or forecasting, are actually just some data prep plus a model-fitting step that's actually an optimization model. We'll start with a little practice with optimization now. Just a taste.

In Excel, optimization problems are solved using an Add-In that ships with Excel called Solver.

On Windows, Solver may be added in by going to File (in Excel 2007 it's the top left Windows button) > Options > Add-ins, and under the Manage drop-down choosing Excel Add-ins and pressing the Go button. Check the Solver Add-In box and press OK.

On Mac, Solver is added by going to Tools then Add-ins and selecting Solver.xlam from the menu.

A Solver button will appear in the Analysis section of the Data tab in every version.

All right! Now that Solver is installed, here's an optimization problem: You are told you need 2,400 calories a day. What's the fewest number of items you can buy from the snack stand to achieve that? Obviously, you could buy 10 ice cream sandwiches at 240 calories a piece, but is there a way to do it for fewer items than that?

Solver can tell you!

To start, download a copy of the Calories spreadsheet from the book's companion website at www.wiley.com/go/datasmart -- use the download link for chapter 1. Make a copy of the Calories sheet in the Concessions.xlsx Excel workbook, name the sheet Calories-Solver, and clear out everything but the calories table on the copy. If you don't know how to make a copy of a sheet in Excel, you simply right-click the tab you'd like to copy and select the Move or Copy menu.

To get Solver to work, you need to provide it with a range of cells it can set with decisions. In this case, Solver needs to decide how many of each item to buy. So in Column C next to the calorie counts, label the column How many? (or whatever you feel like), and you can allow Solver to store its decisions in this column.

Solving Dynamical Optimization Problems in Excel

You can combine ExceLab calculus functions with either native Excel Solver or NLSOLVE to solve a variety of parameter estimation and dynamical optimization problems.

If you have learned how to obtain a solution with the calculus functions, you are almost done! Setting up a parameter or dynamical optimization problem is straightforward with just a couple more steps:

Solve your model as you would normally do using the relevant calculus solvers. Make sure to use variables with initial reasonable values for the model parameters you want to optimize instead of just hardcoding their values into your model formulas.

Your design parameters can be any parameters that influence the system behavior including coefficients, bcs, initial values, etc.

= Initial solution value – Target Value

Please Note. Pre ExceLab 7.0 versions utilized Criterion Functions for imposing constraints on array results. These functions have been deprecated and are no longer supported in ExceLab 7.0 or later. You can now define arbitrary constraint formulas on the array result by simply referencing any array value with DYNVAL . See examples on how to convert criterion functions constraints.

The best way to learn is by viewing the examples.

Computing the limits to maximize an integral value

Computing cone dimensions for a prescribed volume

Customizing the response of a second order dynamical system

Computing train travel time and thrust

Controlling the surface temperature of a heat conducting slab

Fitting a Battery Model to Experimental Data

ExcelWorks Privacy Policy

We do not share or sell any information collected from our customers.

We do not send any promotional or unsolicited emails.

We may occasionally email our customers important information related to transactions and our products.

We do not view or store any credit card data on our servers. Credit card transactions are processed on secure third-party payment gateway servers using 256-bit encryption.

ExceLab: Transforming Excel into a Calculus Power House

ExceLab functions and methods are protected by USA Patents 10628634, 10114812, 9892108 and 9286286.

Want to create or adapt books like this? Learn more about how Pressbooks supports open publishing practices.

12.3 Using Excel to Solve Optimization Problems

Excel has a routine to solve LP problems called “solver,” and in this section of the chapter we will show haw to use this functionality to solve a LP problem.

Setting the Spreadsheet

Setting up a LP problem in Excel is quite simple once the model has been defined as described in the previous section of the chapter. Figure 12.3 shows a potential setting for the Farmers problem in Excel. Besides a special area to capture all the inputs to the problem (cost, time, profit), all other areas in the spreadsheet correspond to the three key elements of a LP problem, the Objective Function , the Decision Variables and the Constraints .

Figure 12.3 LP MOdel setting in Excel

To set the inputs in the model, the constants provided in the text were entered into the spreadsheet with toe proper labels. The Decision Variables in the model were assigned zeroes as an initial value. Solver will find the actual solution, and it is possible to enter in these cells any value.

To enter Constraints and Objective Functions , it is possible to use the functions =SUM() and =SUMPRODUCT(), which are the best Excel functions to represent linear functions. When the function follows the form W + R –without any coefficients to the decision variables– it is possible to enter the equation as =SUM() , and when the equation follows the form C 1 W + C 2 R –where C i represents any coefficients in the equation– it is possible to use the function =SUMPRODUCT() . The example in Figure 12.3 includes each of the equations included in the model from previous section. For profit, that we modeled as Profit = 500W + 300R the Excel model uses then =SUMPRODUCT(B7:B8, D3:D4) , B7 and B8 are the decision variables W and R, and D3 and D4 include the constants 500 and 300. All other constraints, included in D8 to D11 use the equations at the right of the numbers.

Using Solver to Find the Solution

Once the LP model is set in an Excel Spreadsheet, it can be solved by entering the three main components into the Excel Solver Window (Figure 12.5). To open the Solver Window, you need to choose the option in the “Data” tab in Excel. The solver button is usually at the extreme right side of the ribbon (see Figure 12.4). When the Solver button is not visible in the Ribbon, it is most likely that the Solver Add-in needs to be activated in your computer. To activate the Solver Add-in, you will need to choose “Options” in the “File” menu in Excel, go to the Add-ins panel and click on the “Go” button to manage the Excel Add-ins. Make sure that the option for the Solver Add-in is selected as it is shown in Figure 12.4.

Figure 12.4 Solver button and Add-ins window in Excel.

As it is hown in Figure 12.5, there are three main spaces in the Solver Parameters Window to enter the Objective Function (Set Objective), the Decision Variables (By Changing Variable Cells) and the model Constraints . To add the objective function cell, it is possible to enter it manually, but it is also possible to click on the arrow at the right of the space for the function, and then click on the cell where the objective function is located. Same process applies to enter the Decision Variables .

To add the Constraints into the model, click on the “Add” button to display the window at the right of Figure 12.5. You can enter each constraint by clicking again on the appropriate cells in the spreadsheet. Make sure that the simbol in between cells is the appropriate for each constraint and click add. Once you added the last constraint, click on OK to close the window.

Make sure to check the “Make Unconstrained Variables Non-Negative” option to include the non-negativity constraints when appropriate, and make sure to choose the “Simplex LP” method as the solving method. Once the model is set, click on Solve and Excel will provide a solution.

Figure 12.5 Solver and Add constraint Windows

After clicking the “Solve” button, Excel will make calculations and will show a window with the result of the process, explaining that it was possible (or not) to find a solution. Click OK and look at the numbers in the spreadsheet (Figure 12.6). All zeroes have changed to the actual numbers for solutions, constraints and objective. The farmer will maximize their profit planting 4 acres of Wheat and 4 acres of Rye. The actual profit is $3,200, and they are using 8 acres, all the money and all the time. Any other combination within these constraints will result in a lower profit.

Figure 12.6 Solution to the Solver

Rmember the Steps to solve an LP Problem

Attribution

By Luis F. Luna-Reyes, Erika Martin and Mikhail Ivonchyk, and licensed under CC BY-NC-SA 4.0 .

Data Analytics for Public Policy and Management by Luis F. Luna-Reyes, Erika G. Martin and Mikhail Ivonchyk is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License , except where otherwise noted.

Excel’s Solver Feature – A Powerful Problem-Solving Tool!

Excel's Solver Feature - A Powerful Problem-Solving Tool!

Excel's solver feature - a powerful problem-solving tool.

Excel’s Solver feature is a powerful tool that allows users to find optimal solutions to complex problems. Solver is an add-in tool you can install in Microsoft Excel, and it uses mathematical algorithms to determine the best possible values for a given set of constraints. Solver is particularly useful for optimization problems, where the goal is to maximize or minimize a particular objective subject to defined constraints. For example, this tool can assist with issues like finding the optimal production schedule for a manufacturing plant, determining the best allocation of resources for a project, or maximizing profits for a business.

Working With Solver

Excel’s Solver feature uses an iterative process to search for the best solution to an optimization problem. It does this by changing the values of the decision variables and evaluating the objective function at each iteration. The objective function is the mathematical function that describes the problem you are solving.

Solver - Step By Step

To use Solver, first ensure it is available in your instance of Excel. If it is, you will access it on the Ribbon’s Data tab, likely near the right margin. If you do not see it on the Data tab, click File , Options , and Add-ins . Then, click Go near the bottom of the window to manage Excel add-ins . Finally, check the box next to Solver Add-in near the bottom of the window to activate the tool.

The next step is identifying the problem and defining the objective function and any relevant constraints . The objective function is the function that needs to be optimized, and it can be any mathematical equation that represents the goal of the problem. The constraints are the limitations or requirements inherent to the model.

Upon defining the problem, you are ready to use the optimization tool to find its solution. To do so, click Solver on the Ribbon’s Data tab. Next, specify the target cell , which is the cell that contains the objective function. Then, indicate the variables to adjust to find the optimal solution and the constraints in place. Constraints are any limitations that you must factor into the equation. For instance, if you’re using Solver to optimize the results of a capital budgeting problem, the total amount of capital available for investment would be a constraint.

Solver uses mathematical algorithms to find the best possible values for the variables to optimize the objective function. When doing so, it takes into account all specified constraints. You can choose from various Solver methods, including Simplex LP, GRG Nonlinear, and Evolutionary, depending on the complexity of the problem.

Solver's Advantages

One of Solver’s primary benefits is that it can handle nonlinear problems which are difficult or impossible to solve using traditional optimization techniques. Nonlinear problems involve functions that are not linear, and they can be much more complex than linear problems. Solver uses advanced algorithms to handle these problems and find the best solutions.

Another advantage of Solver is that it can handle multiple constraints simultaneously. This feature allows you to model complex systems and find optimal solutions that meet all the requirements. Solver also enables users to set different levels of precision and tolerances, which can help fine-tune the results.

A Downside To Solver

One potential downside of the tool is that it can be time-consuming to set up and run, especially for complex problems. For example, you may need to spend significant time defining the objective function and constraints and selecting the appropriate solver method and parameters. Additionally, Solver may not always find the global optimum solution, particularly for nonlinear problems. However, despite these potential drawbacks, Solver remains a valuable tool for solving optimization problems in Excel.

Solver In Action

To illustrate the tool’s power, consider the following example. Suppose you have a customer or client who sent a payment to you for $14,663.10, but they did not provide the remittance advice. Further, suppose the customer has forty-two outstanding invoices with your company, meaning trying to guess which combination of invoices totals $14,663.10 would be very time-consuming, at best. Instead, you could create a Solver model to identify which combination(s) of invoices sum to $14,663.10.

To build your model, perform the following steps.

Upon completing the steps above, your spreadsheet should resemble that shown in Figure 1 .

Click Solver on the Ribbon’s Data tab. Again, if you do not see Solver on that tab, the most likely cause is that you need to activate the add-in. Upon clicking Solver, Excel opens the Solver Parameters dialog box pictured in Figure 2 .

Near the top of the dialog box, indicate that you want to set cell B3 equal to the specific value of $14,663.10 by changing cells C6 through C47. Further, specify three constraints, as shown below.

Upon indicating the constraints and clicking Solve , Excel identifies the solution shown in Figure 3, with some rows hidden for presentation purposes. In this case, the combination of the invoice amounts for 8033, 8340, 8449, 8509, 9373, 9433, and 9473 is the only combination that adds to $14,663.10.

Although Solver is not a new Excel feature, it is often overlooked and underutilized. Yet, many accounting, financial, and other business professionals can use this feature to identify optimal results for various business problems, including budgeting, capital allocation, sales and marketing activities, and inventory optimization. Despite the tool’s power, it is relatively easy to use and can reduce the time spent trying to identify optimal results in complex models. Therefore, examine Solver and see how it can help you and your team find the best solution to your simple and complex models.

For more information on installing Solver, visit https://k2e.fyi/install_solver . Want to learn more about advanced features in Excel? If so, check out the learning options available from K2 Enterprises, by clicking here .

Commercial Property for Your Business

Excel Offers New Text Manipulation Tools

Optimization with Excel Solver

Solver is a Microsoft Excel add-in program you can use for optimization in what-if analysis.

According to O'Brien and Marakas, optimization analysis is a more complex extension of goal-seeking analysis. Instead of setting a specific target value for a variable, the goal is to find the optimum value for one or more target variables, under certain constraints. Then, one or more other variables are changed repeatedly, subject to the specified constraints, until you discover the best values for the target variables.

In Excel, you can use Solver to find an optimal value (maximum or minimum, or a certain value) for a formula in one cell called the objective cell, subject to certain constraints or limits, on the values of other formula cells on the worksheet.

This means that the Solver works with a group of cells called decision variables that are used in computing the formulas in the objective and constraint cells. Solver adjusts the values in the decision variable cells to satisfy the limits on constraint cells and produce the result you want for the objective cell.

You can use Solver to find optimal solutions for diverse problems such as −

Determining the monthly product mix for a drug manufacturing unit that maximizes the profitability.

Scheduling workforce in an organization.

Solving transportation problems.

Financial planning and budgeting.

Activating Solver Add-in

Before you proceed with finding solution for a problem with Solver, ensure that the Solver Add-in is activated in Excel as follows −

Click the DATA tab on the Ribbon. The Solver command should appear in the Analysis group as shown below.

In case you do not find the Solver command, activate it as follows −

The Add-Ins dialog box appears. Check Solver Add-in and click Ok. Now, you should be able to find the Solver command on the Ribbon under the DATA tab.

Solving Methods used by Solver

You can choose one of the following three solving methods that Excel Solver supports, based on the type of problem −

Used for linear problems. A Solver model is linear under the following conditions −

The target cell is computed by adding together the terms of the (changing cell)*(constant) form.

Each constraint satisfies the linear model requirement. This means that each constraint is evaluated by adding together the terms of the (changing cell)*(constant) form and comparing the sums to a constant.

Generalized Reduced Gradient (GRG) Nonlinear

Used for smooth nonlinear problems. If your target cell, any of your constraints, or both contain references to changing cells that are not of the (changing cell)*(constant) form, you have a nonlinear model.

Evolutionary

Understanding solver evaluation.

The Solver requires the following parameters −

Solver evaluation is based on the following −

The values in the decision variable cells are restricted by the values in the constraint cells.

The calculation of the value in the objective cell includes the values in the decision variable cells.

Solver uses the chosen Solving Method to result in the optimal value in the objective cell.

Defining a Problem

Suppose you are analyzing the profits made by a company that manufactures and sells a certain product. You are asked to find the amount that can be spent on advertising in the next two quarters subject to a maximum of 20,000. The level of advertising in each quarter affects the following −

You can proceed to define the problem as −

Next, set the cells for the required calculations as given below.

As you can observe, the calculations are done for Quarter1 and Quarter2 that are in consideration are −

No. of units available for sale in Quarter1 is 400 and in Quarter2 is 600 (cells – C7 and D7).

The initial values for advertising budget are set as 10000 per Quarter (Cells – C8 and D8).

No. of units sold is dependent on the advertising cost per unit and hence is budget for the quarter / Adv. Cost per unit. Note that we have used the Min function to take care to see that the no. of units sold in <= no. of units available. (Cells – C9 and D9).

Revenue is calculated as Unit Price * No. of Units sold (Cells – C10 and D10).

Expenses is calculated as Unit Cost * No. of Units Available + Adv. Cost for that quarter (Cells – C11 and D12).

Profit is Revenue – Expenses (Cells C12 and D12).

Total Profit is Profit in Quarter1 + Profit in Quarter2 (Cell – D3).

Next, you can set the parameters for Solver as given below −

As you can observe, the parameters for Solver are −

Objective cell is D3 that contains Total Profit, which you want to maximize.

Decision Variable cells are C8 and D8 that contain the budgets for the two quarters – Quarter1 and Quarter2.

There are three Constraint cells - C14, C15 and C16.

Cell C14 that contains total budget is to set the constraint of 20000 (cell D14).

Cell C15 that contains the no. of units sold in Quarter1 is to set the constraint of <= no. of units available in Quarter1 (cell D15).

Cell C16 that contains the no. of units sold in Quarter2 is to set the constraint of <= no. of units available in Quarter2 (cell D16).

Solving the Problem

The next step is to use Solver to find the solution as follows −

Step 1 − Go to DATA > Analysis > Solver on the Ribbon. The Solver Parameters dialog box appears.

Step 2 − In the Set Objective box, select the cell D3.

Step 3 − Select Max.

Step 4 − Select range C8:D8 in the By Changing Variable Cells box.

Step 5 − Next, click the Add button to add the three constraints that you have identified.

Step 6 − The Add Constraint dialog box appears. Set the constraint for total budget as given below and click Add.

Step 7 − Set the constraint for total no. of units sold in Quarter1 as given below and click Add.

Step 8 − Set the constraint for total no. of units sold in Quarter2 as given below and click OK.

The Solver Parameters dialog box appears with the three constraints added in box –Subject to the Constraints.

Step 9 − In the Select a Solving Method box, select Simplex LP.

Step 10 − Click the Solve button. The Solver Results dialog box appears. Select Keep Solver Solution and click OK.

The results will appear in your worksheet.

As you can observe, the optimal solution that produces maximum total profit, subject to the given constraints, is found to be the following −

Stepping through Solver Trial Solutions

You can step through the Solver trial solutions, looking at the iteration results.

Step 1 − Click the Options button in the Solver Parameters dialog box.

The Options dialog box appears.

Step 2 − Select the Show Iteration Results box and click OK.

Step 3 − The Solver Parameters dialog box appears. Click Solve .

Step 4 − The Show Trial Solution dialog box appears, displaying the message - Solver paused, current solution values displayed on worksheet .

As you can observe, the current iteration values are displayed in your working cells. You can either stop the Solver accepting the current results or continue with the Solver from finding solution in further steps.

Step 5 − Click Continue.

The Show Trial Solution dialog box appears at every step and finally after the optimal solution is found, Solver Results dialog box appears. Your worksheet is updated at every step, finally showing the result values.

Saving Solver Selections

You have the following saving options for the problems that you solve with Solver −

You can save the last selections in the Solver Parameters dialog box with a worksheet by saving the workbook.

Each worksheet in a workbook can have its own Solver selections, and all of them will be saved when you save the workbook.

You can also define more than one problem in a worksheet, each with its own Solver selections. In such a case, you can load and save problems individually with the Load/Save in the Solver Parameters dialog box.

Click the Load/Save button. The Load/Save dialog box appears.

To save a problem model, enter the reference for the first cell of a vertical range of empty cells in which you want to place the problem model. Click Save.

The problem model (the Solver Parameters set) appears starting at the cell that you have given as the reference.

To load a problem model, enter the reference for the entire range of cells that contains the problem model. Then, click on the Load button.

Optimal Solution with Excel Solver - Example 1

4.8 (37 ratings)

3.4K Students Enrolled

Course 2 of 4 in the Analytics for Decision Making Specialization

This Course

Video Transcript

In this data-driven world, companies are often interested in knowing what is the "best" course of action, given the data. For example, manufacturers need to decide how many units of a product to produce given the estimated demand and raw material availability? Should they make all the products in-house or buy some from a third-party to meet the demand? Prescriptive Analytics is the branch of analytics that can provide answers to these questions. It is used for prescribing data-based decisions. The most important method in the prescriptive analytics toolbox is optimization. This course will introduce students to the basic principles of linear optimization for decision-making. Using practical examples, this course teaches how to convert a problem scenario into a mathematical model that can be solved to get the best business outcome. We will learn to identify decision variables, objective function, and constraints of a problem, and use them to formulate and solve an optimization problem using Excel solver and spreadsheet.

Skills You'll Learn

Analytics, Linear Programming (LP), Mathematical Optimization

Jun 1, 2021

There are a lot of examples to work through and learn from which I find helps make the material easier to learn.

Sep 18, 2021

Very insightful course. Love the detail explaination for solving simple LP problems.

From the lesson

Module 4: Modeling & Solving Linear Problems in Excel

Having learned how to formulate linear optimization problem and the graphical methods for solving them, we are now going to start solving larger problems using Excel Solver. This module provides an overview of how to set up and solve these decision problems using Excel.

Associate Professor

Explore our Catalog

Join for free and get personalized recommendations, updates and offers., coursera footer, learn something new.

How to use Solver in Excel with examples

The tutorial 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?

In this tutorial, we are going to cover all essential aspects of the Excel Solver add-in and provide a step-by-step guide on how to use it most effectively.

What is Excel Solver?

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.

How to add Solver to Excel

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

To add Solver to your Excel, perform the following steps:

In Excel 2010 - Excel 365, click File > Options . In Excel 2007, click the Microsoft Office button, and then click Excel Options .

Open the Excel Options dialog to get to the Excel Add-ins list.

To get Solver on Excel 2003 , go to the Tools menu, and click Add-Ins . In the Add-Ins available list, check the Solver Add-in box, and click OK .

Where is Solver in Excel 2003?

Now that you know where to find Solver in Excel, open a new worksheet and let's get started!

How to use Solver in Excel

Before 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.

Goal : Calculate the minimal cost per service that will let you pay for the new equipment within the specified timeframe.

And now, let's see how Excel Solver can find a solution for this problem.

1. Run Excel Solver

2. define the problem.

The Solver Parameters window will open where you have to set up the 3 primary components:

Objective cell

Variable cells

Constraints.

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.

Variable 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.

In this example, we have a couple of cells whose values can be changed:

The 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.

To add a constraint(s), do the following:

Click the Add button right to the " Subject to the Constraints " box.

Click the Add button to add the constraint to the list.

Excel Solver allows specifying the following relationships between the referenced cell and the constraint.

To edit or delete an existing constraint do the following:

In this example, the constraints are:

3. Solve the problem

After 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.

Depending on the model complexity, computer memory and processor speed, it may take a few seconds, a few minutes, or even a few hours.

The Solver Result window will close and the solution will appear on the worksheet right away.

Excel Solver examples

Below 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.

Excel Solver example 1 (magic square)

I believe everyone is familiar with "magic square" puzzles where you have to put a set of numbers in a square so that all rows, columns and diagonals add up to a certain number.

For instance, do you know a solution for the 3x3 square containing numbers from 1 to 9 where each row, column and diagonal adds up to 15?

It's probably no big deal to solve this puzzle by trial and error, but I bet the Solver will find the solution faster. Our part of the job is to properly define the problem.

With all the formulas in place, run Solver and set up the following parameters:

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.

Problem : You want to minimize the cost of shipping goods from 2 different warehouses to 4 different customers. Each warehouse has a limited supply and each customer has a certain demand.

Goal : Minimize the total shipping cost, not exceeding the quantity available at each warehouse, and meeting the demand of each customer.

Formulating the model

To define our linear programming problem for the Excel Solver, let's answer the 3 main questions:

To make our transportation optimization model easier to understand, create the following named ranges:

The last thing left for you to do is configure the Excel Solver parameters:

How to save and load Excel Solver scenarios

When 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.

Saving the model

To save the Excel Solver scenario, perform the following steps:

Open the worksheet with the calculated model and run the Excel Solver.

Excel will save your current model, which may look something similar to this:

The current Excel Solver scenario is saved.

Loading the saved model

When you decide to restore the saved scenario, do the following:

In the Solver Parameters window, click the Load/Save button.

Select the range of cells containing the saved model and click Load.

This will open the main Excel Solver window with the parameters of the previously saved model. All you need to do is to click the Solve button to re-calculate it.

Excel Solver algorithms

When defining a problem for the Excel Solver, you can choose one of the following methods in the Select a Solving Method dropdown box:

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.

Practice workbook for download

43 comments

can we do the same thing in java or python?

How can I apply multi objective optimization?

In the example in "How to use Solver in Excel", why do you define a constraint for B2, when it is not one of the variable cells? I have tried deleting that constraint and the solution is the same.

In the analysis of the solution you say "provided there are at least 50 clients per month" when the condition B4<=50 means that you have "at most 50 clients per month".

Otherwise, nice instructions. Very helpful.

Thank you for your questions.

We define a constraint for B3 because it contains a condition that should be met (cost of the new equipment is $40,000 - it's a fixed amount). Variable cells are B4 and B5.

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<=50 was the condition for Solver.

You have been given information about a student below. You will need to construct a spreadsheet and use solver to resolve the constrained optimisation problem outlined below. You should have your solution open and accessible to you when you open the online test. During the test you will also be given a new constraint for one of the scenarios and asked to perform a new optimisation. You should create a copy of your spreadsheet scenario so that you can reconfigure the optimisation calculation as required. After taking the test you will also be required to upload your excel workbook to the Turnitin system under the name "Student Number.xlsx" where the text "Student Number" is replaced with your own numeric student number. 1 The Scenario A student is trying to work out how to allocate the hours of their week and has asked you to help them plan out their week using Solver. The student has given you the following information: • They have a part time job which pays them £11.20 per hour they spend working. • They take three university courses (A, B and C) and they aim to score at least 60 in each of these courses. For each module the student tells you a) what score they expect to get with no study and b) how much they expect their grade to increase with each additional hour of study per week for that course. The information is summarised below: Course No Study Score Score per hour A 30 3.1 B 25 3.4 с 10 2.1 • The student must pay £50 for groceries and £75 for rent on a weekly basis. • The student must sleep at least 6 hours a night on average). • The student also enjoys leisure activities, which they have cost them £10 per hour and exercise, which costs £4 per hour. • The student has no savings • The student's overall wellbeing is measured via the below equation wellbeing = score%3 score 4 score: Sleepo.5 Leisure0.9 Exercise 0.4Work-0.4 Where score A, scores and scorec are the scores from course A, B and C respectively. The other variables in this equation represent the time spent working, exercising, sleeping and leisure. A higher value for this equation represents a higher level of wellbeing, • Every hour in the week must be accounted for between work, studying for one of the three courses, leisure, sleep and exercise. • Hours can only be allocated to activities as whole hours and not as partial hours.

How to refer to a conditionally formatted cell in a certain range,when writing macros in MS EXCEL VBA. Please help me. Many many Thanks in anticipation.

Dear ablebits Team,

Pls help me to find combinations from below given values that makes total like a. 7306, b.9386, c. 5148

Given Values 1,313.00 1,365.00 1,378.00 1,599.00 1,872.00 2,028.00 2,028.00 2,171.00 2,197.00 2,236.00 2,678.00 3,510.00 3,913.00 4,225.00 4,680.00

I want to visualize these 2 scenarios in my Excel Report . will please gives us an idea how I can do that?

1. To present the block wise variation in students’ performances for both the subjects as well as the passing percentages of schools.

2.top 10 tribal majority blocks which have good proportion between tribal teachers and tribal students in their schools .

Exam year Gender of students Social category of students Total Enrolled students in school Total tribal students enrolled in school First Language Score in First Language (%) Score in Math (%) Division 2020 Boys GEN 181 23 Bengali 39% 24% Fail 2020 Girls ST 45 29 Bengali 36% 25% Fail 2019 Boys GEN 42 9 Bengali 75% 38% Pass 2018 Girls BC 280 54 Bengali 49% 18% Compartment 2018 Boys ST 376 46 Bengali 41% 30% Pass 2019 Boys GEN 90 0 Bengali 88% 56% Pass 2018 Girls ST 90 59 Bengali 37% 30% Pass 2019 Girls GEN 277 135 Bengali 64% 31% Pass 2018 Boys ST 40 27 Bengali 48% 24% Compartment 2018 Boys SC 243 7 Bengali 48% 30% Pass 2020 Girls ST 108 80 Bengali 54% 46% Pass 2019 Boys GEN 60 46 Bengali 40% 37% Pass 2020 Boys ST 90 59 Bengali 46% 58% Pass 2020 Girls GEN 453 161 Bengali 66% 32% Pass 2019 Boys GEN 358 97 Bengali 80% 85% Pass 2019 Boys GEN 243 7 Bengali 26% 22% Fail 2018 Girls ST 351 66 Bengali 52% 30% Pass

) AAu Press is trying to determine which of 36 books it should publish this year. The data in the Problem2 worksheet gives the following information about each book:

• Projected revenue and development costs • Pages in each book • Whether each book is geared toward an audience of software developers (indicated by a 1 in column E)

AAy Press can publish books totaling up to 8,500 pages this year, and must publish at least 5 books geared toward software developers. How can AAu Press maximize its PROFIT (is profit the same as revenue)? To avoid points to be taken away, please make sure that you set this up so that the constraint can be seen next to data as it is shown in the exercise above (see the figures from the previous page). Book pages Cost Revenue Developer book 1 911 177.98 29.15 1 2 911 47.88 99.52 1 3 911 83.24 94.19 1 4 911 104.3 85.76 1 5 911 78.62 68.98 1 6 911 36.38 41.78 1 7 911 56.16 96.97 1 8 911 50.62 68.20 1 9 911 130.04 71.32 1 10 911 142.14 91.55 1 11 911 40.55 60.82 0 12 911 53.43 186.35 0 13 911 81.13 174.39 0 14 911 80.01 129.90 0 15 911 66.23 8.59 0 16 911 18.9 32.28 0 17 911 71.21 38.72 0 18 911 73.36 158.37 0 19 911 61.47 184.70 0 20 911 80.73 133.53 0 21 911 72.01 63.63 0 22 911 27.37 105.87 0 23 911 45.95 174.76 0 24 911 30.58 116.21 0 25 911 41.16 157.58 0 26 911 78.82 3.07 0 27 911 80.82 151.72 0 28 911 18.12 44.24 0 29 911 38.53 136.70 0 30 911 46.56 10.13 0 31 911 70.27 124.69 0 32 911 72.64 69.87 0 33 911 48.31 4.28 0 34 911 36.29 99.06 0 35 911 62.87 138.95 0 36 911 83.17 58.63 0

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?

The advertising director a large retail store in Columbus, Ohio, is considering three advertising media possibilities: (1) ads in the Sunday Columbus Dispatch newspaper, (2) ads in a local trade magazine that is distributed free to all houses in the city and northwest suburbs, and (3) ads on Columbus’ WCCTV station. She wishes to obtain a new-customer exposure level of at least 50% within the city and 60% in the northwest suburbs. Each TV ad has a new-customer exposure level of 5% in the city and 3% in the northwest suburbs. The Dispatch ads have corresponding exposure levels per ad of 3.5% and 3%, respectively, while the trade magazine has exposure levels per ad of 0.5% and 1%, respectively. The relevant costs are $1,000 per Dispatch ad, $300 per trade magazine ad, and $2,000 per TV ad. The advertising policy is that no single media type should consume more than 45% of the total amount spent. Find the advertising strategy that will meet the store’s objective at minimum cost.

Can You Please Solve this problem for me?

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?

Btw, the GUI of MS Office is in English on my computer.

Hello Sir i want to find out what would be the minimum Bench resources and Bench cost that i should have for a Turnover of $ 50000 At Present my billing is $9000 Presently the Bench Resources are 17 in Nos Present Bench cost is $12000 Can you let me know how to find this on excel solver

Hello everyone, I have a question and solver giving error , could you help me on that ı am writing question below; Many Thanks

The Dakota Aliens is a new professional basketball franchise in Dakota. The team’s general manager, Martian, and coach, Michael Jordan, are trying to develop a roaster of players. They drafted seven players from a pool to which the other teams in the league each contributed two players. However, the general manager and coach perceive these acquisitions to be no more than role players. They believe that the nucleus of their new team must come from the free agents who are currently available on the market. The team is well under the salary cap, and the owner has made 50 million per yer available to them to sign players. The coach and general manager have put together the following list of 12 free agents, with important statistics for each, including their rumored asking price in terms of annual salary. Pre-Game Averages Projected Annual Salary Player Position Point Rebound Assists Minutes Pound Back court 14,7 4,4 9,3 8,2 millions Bang Front court 12,6 10,6 2,1 34,5 6,5 millions Bupkus Back court 13,5 8,7 1,7 29,3 5,2 millions Blanko Back court 27,1 7,1 4,5 42,5 16,4 millions Nawt Back court 18,1 7,5 5,1 41 14,3 millions Balrog Front court 22,8 9,5 2,4 38,5 23,5 millions Gud Front court 9,3 12,2 3,5 31,5 4,7 millions Destructor Front court 10,2 12,6 1,8 44,4 7,1 millions Ulysees Front court 16,9 2,5 11,7 42,7 15,8 millions Cyrax Back court 28,5 6,5 1,3 38,1 26,4 millions Bilaterus Front court 24,8 8,6 6,9 42,6 19,5 millions Sylvester Front court 11,3 12,5 3,2 39,5 8,6 millions

Jordan and the Martian want to sign five free agents. They would like the group they sign to have at least 80 points, pull down an average 40 rebounds per game (8 per players), dish out an average 25 assists, and have averaged 190 minutes ( 38 per player) per game in the past. Their immediate object is to identify the players who as a group would meet their objective in minimum cost.

If they do not want to sign more than two front court and three back court players, which players as a group should they choose?

Hello Selçuk! I recommend using Solver. How to use Solver in Excel with examples - read above

Hello! please help me with the steps on creating named ranges and how to enter the constrains for the solver parameter based on the name ranges created.

Very nice tutorial. Lot of thanks.

A theatre company needs to determine the lowest cost production budget for an upcoming theatre show. Specifically, they will have to determine the lowest which set pieces to construct and which pieces must be rented from another company at a pre-determined fee. The time available for constructing the set is two weeks after which rehearsals commence. To construct the set, the theatre has two part-time carpenters who work upto 12 hours a week and each at $100 per hour. Additionally, the scene artist can work 15 hours per week at $150 per hour.

The set design requires 20 walls, 2 hanging drops with pained scenery and 3 large wooden tables serving as props. The number of hours required for each piece for carpentry and painting is given below. Carpentery Painting Walls 0.5 2.0 Hanging Drops 2.0 3.0 Wooden Tables 3.0 4.0

Flats, hanging drops and props can also be rented at a cost of $750, $5000 and $3500 each. How many of each unit should be built by the theatre company and how many units should be rented to minimize costs?

Hi everyone, i am Silvana Pantic, from Slovenia, am so glade coming back to this great forum to testify about the help i received from Credit-Suisse Loan Film. I was in desperate need of a loan in other to be free from debt and financial bondage that was place on me by my ex husband. It was really bad that i have to seek for help from Friends,family and even my bank but on one could assist me because my credit score was really bad. So i was browsing with my computer and saw some testimonies from people that Credit-Suisse Loan Film assisted with a loan, then i decided to contact them via email {[email protected]}

Hello there!

I have a problem with production scheduling. We have three wire cutting machines and 90 different tools for contactor crimping and seal application (automotive harness business) that are being used on these machines as active processing parts for the different wires and other harness components. Operation on each of the machine is similar except that combination of tools is different, first the machine unroll the wire form the spool, then it cuts the wire on a predetermined length, then applies a seal (water protection) and then crimp a contactor. There are operations where we use two seal applicators and crimping tools on a single machine, so both ends of the wire are sealed and crimped. The combination depends on the wire cross-section, seal specification and contactor specification (crimp parameters vary based on the client specification). Goal - is to prepare the production plan with minimum change over of the tools and eliminate the situations when the tools are needed on more than one machine. The replanting has to be flexible, even daily. Some tools are available in more than one unit (two three). The changeover of applicators is longer than for crimping tools (1.5 hours vs. 30 min). Would be good to have a tip how to solve this complex task.

Look forward to hear from you! Slava

I tried the Magic Square one with my students and we were not able to solve it as directed. We received error messages, such as "An AllDifferent Constraint must have either no bounds, or a lower bound of 1 and and upper bound of N, where N is the number of cells in the Constraint." However, we could not figure out where to set this parameter. Can anyone help with this?

Hi Michelle, To figure out the source of the problem, you can download our sample workbook and compare our Magic Square model with yours.

Hi. I’m very new to solver and was asked to solve this question: Mathew is the business owner of a laundry shop located at City Plaza. He has operated the business since June 2018 and after operating it for 6 months, he has realised that in certain months, the sales revenue is sufficient to cover the operating expenditures, while for certain months the sales revenue is not enough to cover the operating expenditures and he has to rely on his personal savings to tide through. It is now the last week of December 2018 and he realises that moving forward, it is better for his business to have access to loan facility from the bank to ease out his operation. However, he is unsure of which loan package to sign up and has approached you, a close friend, to help him as you are trained in financial planning. To perform the analysis, you have requested Mathew to give a projection of the sales revenue and operating expenditures for the next twelve months. The estimates are as follows: Month Sales Revenue ($) Bills ($) January 4,000 6,000 February 3,000 5,000 March 3,000 4,000 April 3,000 3,000 May 5,000 4,000 June 9,000 1,000 July 3,000 6,000 August 2,000 6,000 September 1,000 4,000 October 2,000 2,000 November 6,000 1,000 December 10,000 1,000 Based on the whole year projection, Mathew will make $8,000 net profit at the end of the year. However, since all expenditures must be paid in full by the end of every month, Mathew may be short on cash in some months until he sees the big sales in certain months, e.g. June and December. Mathew has two sources of loan:  Annual loan at 12% of interest per year, e.g. he borrows $100 at the beginning of January 2019 and pays back $112 at the end of December 2019. Early-pay-back is not allowed and Mathew can get an annual loan in January only.  Monthly loan at 2.5% of interest, e.g. he borrows $100 at the end of March and pays back $102.5 at the end of April. Early-pay-back is not allowed and Mathew cannot get a monthly loan in December. He needs your help to determine whether he should just take up the annual loan with effect from January, or a mixture of both types of loan facilities. Assume that Mathew has zero cash balance at the beginning of 2019.

I have tried to look up similar questions online but the prob is I don’t understand how the solution was derived. Can someone help please? Thank you.

Dear Cheusheva I have tried my best to study your instruction and practice with my problem but I fail to come to an acceptable result. I hope you help me. I have a table as follows x1 x2 x3 x4 Age Code Name 34 36 38 42 17 0 A 32 38 40 41 19 1 B 36 39 32 40 20 0 C 42 34 42 41 19 1 D 33 38 42 29 20 1 E 31 39 41 45 18 0 F (others in similar form) I want to have a minimum of sum of four variables with the constraints - A person (name) is chosen only 1 time in the sum (4 people for four variables) - Sum of code is 2 (two "1" and two "0") - sum of age is <=60 I hope to have your reply soon. Best regards

You explain very well! You have a gift

Fantastic Examples to make you understand the algorithm.

Great. Everything that I wanted to know more about solver is here.

Question? I created a workbook for scheduling hours for employees working at a movie theater for 1 week. I need to have a certain number of employees for each day of the week, but I need to deal with their timeoff requests. And some of my employees are fulltime and some parttime. The timeoff request says "Can't work Saturday". I need to write a constraint based on those entries, add constraints so that employees are not scheduled to work on days when they are unavailable to work. How do I write a constraint to cover this?

Excellent Sir, thanks a lot.

i faced a problem with exelsolver by error how can i correct that?

hello, please i was giving this assignment but i am finding it hard to understand it. please can any one help me solve it?

To create a Linear Programming model using MS Excel Solver (25%) A metal works manufacturing company produces four products fabricated from sheet metal in a production line that consist of four operations: 1) Stamping, 2) Assembly, 3) Finishing and 4) Packaging. The processing times per unit for each operation and total available hours per month are as follows:

Product (hour/unit) Operation 1 2 3 4 Total Hours available per month Stamping 0.07 0.2 0.1 0.15 700 Assembly 0.15 0.18 -- 0.12 450 Finishing 0.08 0.21 0.06 0.10 600 Packaging 0.12 0.15 0.08 0.12 500 5 The sheet metal required for each product, the maximum demand per month, the minimum required contracted production, and the profit per product are given as follows: Monthly sales demand Product Sheet Metal (ft2 ) Minimum Maximum Profit(£) 1 2.1 300 3,000 9 2 1.5 200 1,400 10 3 2.8 400 4,200 8 4 3.1 300 1,800 12 The company has 5,200 square feet of fabricated metal available each month. Formulate a linear programming model and use Excel Solver function to suggest the best mix of products which would result in the highest profit within the given constraints.

Can u better rephrase or construct the question properly

Hello Charles,

I managed to solve the problem that you posted (albeit some understandings I have changed to suit the scenario). Post your email ID and I will post the excel file to you asap.

Warm Regards, Bhupesh

Many thanks I have been trying to learn this function for decades.

Thank you there is no things more than this to upload your knowledge

Great explanation! Thorough Demonstration of Skills and a brilliant performance

Excellent & very descriptive examples thanks

excellent tutorial

Thanks for such a nice & easy tutorial of difficult commands.

Thank you sir a wonderful article

Awesome knowledge Keep it up AbleBits team,

404 Not found

Udemy Settles Lawsuit for $4 million

Exploring edX’s “Final” Tax Returns: Revenue Surges, Losses Mount, and Contributions Decline

Analyzing edX’s latest tax returns offers valuable insights and context for its acquisition motivations.

Unearthing the Past: A Review of Historic Landscape Archaeology by the University of Padova
20+ Online Learning Deals and Discounts
[2023] 100+ Philosophy Courses You Can Take for Free
[2023] 100 Robotics Courses You Can Take for Free
Stanford Medicine Offers Courses with Free Certificate & CME Credit

700+ Free Google Certifications

Most common

cyber security

Popular subjects

Programming

Web Development

Data Science

Popular courses

Preparing to Manage Human Resources

Nutrition and Health: Human Microbiome

CS50's Introduction to Computer Science

Organize and share your learning with Class Central Lists.

View our Lists Showcase

Class Central is learner-supported. When you buy through links on our site, we may earn an affiliate commission.

Solving Optimization and Scheduling Problems in Excel

via LinkedIn Learning Help

What you should know before watching this course
Using the exercise files
Finding target values using Goal Seek
Introducing linear and integer programming
Installing the Solver add-in on Windows
Organizing a worksheet for use in Solver
Finding a solution using Solver
Introducing the problem
Organizing the worksheet
Adding data to the worksheet
Defining changing value cells and summary formulas
Setting the problem's criteria in Solver
Setting the problem's criteria in Solver and solving
Adding data and changing values to the worksheet
Defining summary formulas
Changing parameters by hand
Performing sensitivity analysis
Defining a scenario
Showing and hiding scenarios
Editing and deleting scenarios
Creating a scenario summary worksheet
Further resources

Related Courses

Excel/vba for creative problem solving, intro to excel, a beginner’s guide to data handling and management in excel, excel: skills development & training, intro to excel: essential training & tutorials, excel for beginners, related articles.

Select rating

Start your review of Solving Optimization and Scheduling Problems in Excel

Never Stop Learning.

Get personalized course recommendations, track subjects and courses with reminders, and more.

Create an account
Product Overview
Analytic Solver Overview
Analytic Solver Optimization
Analytic Solver Simulation
Analytic Solver Data Mining
Analytic Solver Academy
RASON Decision Services
Solver Engines
Optimization and Simulation
Forecasting and Data Mining
Case Studies
Data Mining Webinar
Optimization Webinar
Simulation Webinar
Optimization Tutorials
Simulation Tutorials
Data Mining Tutorials
Finance Examples
Investment Examples
Production Examples
Distribution Examples
Purchasing Examples
Scheduling Examples
Video Demos
Technical Support
Consulting Help
Academy Courses
Excel Solver Help
Data Mining Help
Excel User Guides
SDK User Guides
Recommended Books
Product Catalog
Types of Licenses
License Agreement
Limited Warranty
Standard vs Custom Terms
Invoicing Payment

Optimization Tutorial

Welcome to our tutorial about Solvers for Excel and Visual Basic -- the easiest way to solve optimization problems -- from Frontline Systems, developers of the Solver in Microsoft Excel.

This tutorial addresses the following questions:

What are Solvers Good For?

What Must I Do to Use a Solver?
How Do I Define a Model?
Can You Show Me Step by Step?
What Kind of Solution Can I Expect?
What Makes a Model Hard to Solve?

After completing this tutorial, you can learn even more about topics such as linearity versus nonlinearity and sparsity in optimization models by completing our Advanced Tutorial .

Solvers, or optimizers, are software tools that help users determine the best way to do something. The "something" might involve allocating money to investments, or locating new warehouse facilities, or scheduling hospital operating rooms. In each case, multiple decisions need to be made in the best possible way while simultaneously satisfying a number of logical conditions (or constraints). The "best" or optimal solution might mean maximizing profits, minimizing costs, or achieving the best possible quality. Here are some representative examples of optimization problems:

Finance and Investment

Working capital management involves deciding how much cash to allocate to different purposes (accounts receivable, inventory, etc.) across multiple time periods, to maximize interest earnings.

Capital budgeting involves deciding how much money to invest in projects that initially consume cash but later generate cash, to maximize a firm's return on capital.

Portfolio optimization -- creating "efficient portfolios" -- involves deciding how much money to invest in stocks or bonds to maximize return for a given level of risk, or to minimize risk for a target rate of return.

Manufacturing

Job shop scheduling involves deciding how to assign work orders to different types of production equipment, to minimize delivery time or maximize equipment utilization.

Blending (of petroleum products, ores, animal feed, etc.) involves deciding how to combine raw materials of different types and grades, to meet demand while minimizing costs.

Cutting stock (for lumber, paper, etc.) involves deciding how to cut large sheets or timbers into smaller pieces, to meet demand while minimizing waste.

Distribution and Networks

Routing (of goods, natural gas, electricity, digital data, etc.) involves deciding which paths items should move through to arrive at various destinations, to minimize costs or maximize throughput.

Loading (of trucks, rail cars, etc.) involves deciding how items of different sizes should be placed in vehicles so as to minimize wasted or unused space.

Scheduling of everything from workers to vehicles and meeting rooms involves deciding how resources should be allocate to various tasks in order to meet demand while minimizing overall costs.

Title: Hardware Dynamical System for Solving Optimization Problems

Associated Organizations

Collections, supplementary to, permanent link, date issued, resource type, resource subtype, rights statement.

Official website of the Cybersecurity and Infrastructure Security Agency

Here’s how you know

Official websites use .gov

A .gov website belongs to an official government organization in the United States.

Secure .gov websites use HTTPS

Education & Training
NICCS Education & Training Catalog

Solve a job shop scheduling optimization problem by using Azure Quantum

Online, Self-Paced

Learn how to use Azure Quantum's optimization service to solve a job shop scheduling problem.

Learning Objectives

Framework connections.

Operate and Maintain

IMAGES

How to solve linear optimization problems using excel solver?
Solving Optimization Problem Using Excel Solver
An example of a linear optimization problem with excel
Optimization Problems
Make an excel tool to solve any optimization problem by Kennyhcw
Excel Solver solver is messing up my optimization

VIDEO

Solve optimization problems using MS Excel
Solve optimization problems that yield polynomial functions
Optimization Problems Part 1
Excel: Optimization example (Ice Cream King)
Digital Annealer technology
Excel

COMMENTS

Define and solve a problem by using Solver
Click Add. Accept the constraint and return to the Solver Parameters dialog box. Click OK. To. Do this. Keep the solution values on the sheet. Click Keep Solver Solution in the Solver Results dialog box. Restore the original data. Click Restore Original Values.
Using Solver to determine the optimal product mix
A key to solving the product mix problem is to efficiently compute the resource usage and profit associated with any given product mix. An important tool that we can use to make this computation is the SUMPRODUCT function. The SUMPRODUCT function multiplies corresponding values in cell ranges and returns the sum of those values.
Solving Optimization Problems in Excel
Solving Optimization Problems in Excel LearnChemE 162K subscribers Subscribe 18K views 4 years ago Microsoft Excel Organized by textbook: https://learncheme.com/ Demonstrates how to use the...
Solving Linear Optimization Model: Using Excel
Solving Linear Optimization Model: Using Excel Bryan Crigger · Follow 11 min read · Oct 16, 2018 -- 1 When solving Optimization Problems there are many items that need to be identified.
How to solve optimization problems with Excel and Solver
On Windows, Solver may be added in by going to File (in Excel 2007 it's the top left Windows button) > Options > Add-ins, and under the Manage drop-down choosing Excel Add-ins and pressing...
Solving optimization problems in Excel
Solving Dynamical Optimization Problems in Excel You can combine ExceLab calculus functions with either native Excel Solver or NLSOLVE to solve a variety of parameter estimation and dynamical optimization problems. If you have learned how to obtain a solution with the calculus functions, you are almost done!
12.3 Using Excel to Solve Optimization Problems
To activate the Solver Add-in, you will need to choose "Options" in the "File" menu in Excel, go to the Add-ins panel and click on the "Go" button to manage the Excel Add-ins. Make sure that the option for the Solver Add-in is selected as it is shown in Figure 12.4. Figure 12.4 Solver button and Add-ins window in Excel.
Excel's Solver Feature
Excel's Solver feature is a powerful tool that allows users to find optimal solutions to complex problems. Solver is an add-in tool you can install in Microsoft Excel, and it uses mathematical algorithms to determine the best possible values for a given set of constraints. Solver is particularly useful for optimization problems, where the ...
Excel Solver Exercises: 8 Advanced Problems
To solve the problems, you should know about the following: the SUM, SUMPRODUCT, HLOOKUP, COUNTIF, IF, and OR functions and Enable solver, solver properties, solver example, choosing the best project, portfolio optimization with solver, solver for linear programming, usage of solver to minimize cost, assign work using an evolutionary solver, and...
Some Practical Examples with Excel Solver
What Is Solver in Excel? Solver is a Microsoft Excel add-in program. The Solver is part of the What-If Analysis tools that we can use in Excel to test different scenarios. We can solve decision-making issues using the Excel tool Solver by finding the most perfect solutions. They also analyze how each possibility impacts the worksheet's output.
Optimization with Excel Solver
Solving Methods used by Solver You can choose one of the following three solving methods that Excel Solver supports, based on the type of problem − LP Simplex Used for linear problems. A Solver model is linear under the following conditions − The target cell is computed by adding together the terms of the (changing cell)* (constant) form.
Optimal Solution with Excel Solver
Using practical examples, this course teaches how to convert a problem scenario into a mathematical model that can be solved to get the best business outcome. We will learn to identify decision variables, objective function, and constraints of a problem, and use them to formulate and solve an optimization problem using Excel solver and spreadsheet.
Excel Solver tutorial with step-by-step examples
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.
Excel
ISM Course ExcelPart 11.06The corresponding playlist can be found here: Excel (en): https://www.youtube.com/playlist?list=PL0eGlOnA3oppM0mxuLqYW6-TqR2NlZrZXA...
Schedule Optimization in Excel (Detailed Analysis)
Download the working file from the link below. Schedule Optimization.xlsx What Is Solver in Excel? Solver is a Microsoft Excel add-in program. The Solver is part of the What-If Analysis tools that we can use in Excel to test different scenarios. We can solve decision-making issues using the Excel tool Solver by finding the most perfect solutions.
PDF OPTIMIZATION WITH EXCEL
Optimization without constraints with the Excel solver The best method to illustrate the method to follow in order to solve an optimization problem with Excel is to proceed with an example. The steps are detailed and vary little from one problem to the next: Example Consider 6the function B : T ;
What's the Easiest Way to Solve Optimization Problems?
Welcome to our tutorial about Solvers in Excel -- the easiest way to solve optimization problems -- from Frontline Systems, developers of the Solver in Microsoft Excel.
Optimization with Excel Solver
You can dial one of the following thre solving methods that Excel Solver carriers, bases on the genre to problem −. LP Singleplex. Used for linearly problems. A Solver model is linear under the following environment −. The target dungeon will computed by addition with the terms of the (changing cell)&ast;(constant) bilden.
Solving Optimization and Scheduling Problems in Excel
Setting the problem's criteria in Solver and solving; 4. Solving a Transportation Problem. Introducing the problem; Organizing the worksheet; Adding data to the worksheet; Defining changing value cells and summary formulas; Setting the problem's criteria in Solver; 5. Solving a Resource Scheduling Optimization Problem. Introducing the problem ...
Optimization Tutorial
Welcome to our tutorial about Solvers for Excel and Visual Basic-- the easiest way to solve optimization problems -- from Frontline Systems, developers of the Solver in Microsoft Excel. This tutorial addresses the following questions:
Hardware Dynamical System for Solving Optimization Problems
Optimization problems form the basis of a wide gamut of computationally challenging tasks in signal processing, machine learning, resource planning and so on. Out of these, convex optimization, and in particular least square optimization, covers a vast majority; and recent advances in iterative algorithms to solve such problems of large dimensions have gained traction.
Solve a job shop scheduling optimization problem by using Azure ...
Operate and Maintain. The materials within this course focus on the Knowledge Skills and Abilities (KSAs) identified within the Specialty Areas listed below. Click to view Specialty Area details within the interactive National Cybersecurity Workforce Framework. Network Services.