Excel Solver Tutorial Pdf

Check Solver Add-in and click OK. You can find the Solver on the Data tab, in the Analyze group. Formulate the Model The model we are going to solve looks as follows in Excel. Exploring the Excel 2016 Environment Open Excel by using the Start menu or by double-clicking the Desktop icon for Excel 2016. Note the Title Bar section which has window controls at the right end, as in other Microsoft Office programs. Note that a blank workbook opens with a default file name of Book1. Quick Access Toolbar. Excel Solver Tutorial ISE 5405 Virginia Tech 1 Solver is a Microsoft Excel add-in program that uses techniques from the operations research to find an optimal value for a formula in the objective cell subject to constraints, on the values of other formula cells on a worksheet.

Load the Solver Add-in Formulate the Model Trial and Error Solve the Model

Excel includes a tool called solver that uses techniques from the operations research to find optimal solutions for all kind of decision problems.

Load the Solver Add-in

To load the solver add-in, execute the following steps.

1. On the File tab, click Options.

2. Under Add-ins, select Solver Add-in and click on the Go button.

3. Check Solver Add-in and click OK.

4. You can find the Solver on the Data tab, in the Analyze group.

Formulate the Model

Excel

The model we are going to solve looks as follows in Excel.

1. To formulate this linear programming model, answer the following three questions.

a. What are the decisions to be made? For this problem, we need Excel to find out how much to order of each product (bicycles, mopeds and child seats).

b. What are the constraints on these decisions? The constrains here are that the amount of capital and storage used by the products cannot exceed the limited amount of capital and storage (resources) available. For example, each bicycle uses 300 units of capital and 0.5 unit of storage.

c. What is the overall measure of performance for these decisions? The overall measure of performance is the total profit of the three products, so the objective is to maximize this quantity.

2. To make the model easier to understand, create the following named ranges.

Range NameCells
UnitProfitC4:E4
OrderSizeC12:E12
ResourcesUsedG7:G8
ResourcesAvailableI7:I8
TotalProfitI12

3. Insert the following three SUMPRODUCT functions.

Explanation: The amount of capital used equals the sumproduct of the range C7:E7 and OrderSize. The amount of storage used equals the sumproduct of the range C8:E8 and OrderSize. Total Profit equals the sumproduct of UnitProfit and OrderSize.

Trial and Error

With this formulation, it becomes easy to analyze any trial solution.

For example, if we order 20 bicycles, 40 mopeds and 100 child seats, the total amount of resources used does not exceed the amount of resources available. This solution has a total profit of 19000.

It is not necessary to use trial and error. We shall describe next how the Excel Solver can be used to quickly find the optimal solution.

Solve the Model

To find the optimal solution, execute the following steps.

1. On the Data tab, in the Analyze group, click Solver.

Enter the solver parameters (read on). The result should be consistent with the picture below.

You have the choice of typing the range names or clicking on the cells in the spreadsheet.

2. Enter TotalProfit for the Objective.

3. Click Max.

4. Enter OrderSize for the Changing Variable Cells.

5. Click Add to enter the following constraint.

6. Check 'Make Unconstrained Variables Non-Negative' and select 'Simplex LP'.

7. Finally, click Solve.

Result:

The optimal solution:

Conclusion: it is optimal to order 94 bicycles and 54 mopeds. This solution gives the maximum profit of 25600. This solution uses all the resources available.

  • Excel Data Analysis Tutorial
  • Advanced Data Analysis
  • Excel Data Analysis Resources
  • Selected Reading

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.

Excel

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 −

  • Click the FILE tab.
  • Click Options in the left pane. Excel Options dialog box appears.
  • Click Add-Ins in the left pane.
  • Select Excel Add-Ins in the Manage box and click Go.

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 −

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.

  • 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

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.

Understanding Solver Evaluation

The Solver requires the following parameters −

  • Decision Variable Cells
  • Constraint Cells
  • Objective Cells
  • Solving Method

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 −

  • The number of units sold, indirectly determining the amount of sales revenue.
  • The associated expenses, and
  • The profit.

You can proceed to define the problem as −

  • Find Unit Cost.
  • Find the advertising cost per Unit.
  • Find Unit Price.

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.

Excel Solver Tutorial Pdf Templates

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 −

Excel Solver Tutorial Pdf Creator

  • Total Profit – 30000.
  • Adv. Budget for Quarter1 – 8000.
  • Adv. Budget for Quarter2 – 12000.

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.

Excel Solver Vba Tutorial Pdf

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.

Excel Solver Tutorial Pdf Merge

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

Excel Solver Tutorial Pdf Template

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