Optimization with Excel Solver Excel Data Analysis

What is 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 extra complex extension of goal-seeking analysis. instead of placing a selected goal value for a variable, the goal is to locate the best value for one or extra goal variables, below certain constraints. Then, one or more different variables are modified repeatedly, subject to the required constraints, till you find out the best values for the goal variables.

In Excel, you can use Solver to locate an Optimal value (maximum or minimum, or a certain value) for a method in one cell known as the goal cell, problem to certain constraints or limits, on the values of different method cells at the worksheet.

This means that the Solver works with a group of cells called selection variables that are used in computing the formulas inside the goal and constraint cells. Solver adjusts the values in the selection 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.

Optimization with Excel Solver

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.

Optimization with Excel Solver

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.

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 using including collectively the terms of the (changing cell)*(constant) form.
  • Each constraint satisfies the linear version requirement. this means that every constraint is evaluated by including collectively the terms of the (changing cell)*(constant) form and comparing the sums to a constant.

Generalized Reduced Gradient (GRG) Nonlinear

Used for easy nonlinear issues. if your target cell, any of your constraints, or both include references to converting cells that are not of the (changing cell)*(constant) shape, you have a nonlinear version.

Evolutionary

Used for smooth nonlinear issues. if your target cell, any of your constraints, or each contain references to converting cells that are not of the (changing cell)*(constant) form, you have a nonlinear version.

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 selection variable cells are restricted through the values in the constraint cells.
  • The calculation of the value in the objective cell consists of the values within the selection variable cells.
  • Solver uses the selected solving technique to result in the best value in the objective cell.

Defining a problem

Suppose you are reading the profits made through a company that manufactures and sells a certain product. You are requested to locate the quantity that may be spent on advertising in the next quarters problem to a most of 20,000. The level of advertising in every region 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.

Optimization with Excel Solver

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

Optimization with Excel Solver

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

  • No. of devices available for sale in Quarter1 is 400 and in Quarter2 is 600 (cells – C7 and D7).
  • The initial values for marketing budget are set as 10000 per region (Cells – C8 and D8).
  • No. of devices offered is dependent on the advertising value according to unit and hence is budget for the region / Adv. cost per unit. note that we have used the Min characteristic to take care to look that the no. of units offered in <= no. of units available. (Cells – C9 and D9).
  • Sales is calculated as Unit rate * No. of units offered (Cells – C10 and D10).
  • Expenses is calculated as Unit price * No. of devices to be had + Adv. cost for that region (Cells – C11 and D12).
  • Profit is revenue – expenses (Cells C12 and D12).
  • Total profit is income in Quarter1 + income in Quarter2 (cell – D3).

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

Optimization with Excel Solver

As you may observe, the parameters for Solver are −

  • Goal cell is D3 that includes total profit, that you want to maximize.
  • Choice 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 devices offered in Quarter1 is to set the constraint of <= no. of units available in Quarter1 (cell D15).
  • Cell C16 that includes the no. of units offered 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.

Optimization with Excel Solver

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.

Optimization with Excel Solver


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.

Optimization with Excel Solver

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

Optimization with Excel Solver

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

Optimization with Excel Solver

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.

Optimization with Excel Solver

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

Optimization with Excel Solver

The results will appear in your worksheet.

Optimization with Excel Solver

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

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

Optimization with Excel Solver

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.

Optimization with Excel Solver

As you can observe, the present day iteration values are displayed in your working cells. you may both prevent the Solver accepting the current results or continue with the Solver from locating solution in further steps.

Step 5 − Click Continue.

The show Trial solution conversation container seems at every step and finally after the best solution is located, Solver results dialog box seems. Your worksheet is updated at every step, finally showing the result values.

Saving Solver Selections

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

  • You can save the last selections in the Solver Parameters dialog box with a worksheet through saving the workbook.
  • Each worksheet in a workbook may have its own Solver choices, and they all can be saved when you save the workbook.
  • You can also outline more than one problem in a worksheet, every with its own Solver selections. In such a case, you can load and save issues 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 version, enter the reference for the first cell of a vertical range of empty cells in which you need to region the problem model. click save.

Optimization with Excel Solver

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

Optimization with Excel Solver

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

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Excel Data Analysis Topics