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 −
Before you proceed with finding solution for a problem with Solver, ensure that the Solver Add-in is activated in Excel as follows −
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.
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 –
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.
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.
The Solver requires the following parameters −
Solver evaluation is based on the following –
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 –
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 completed for Quarter1 and Quarter2 that are in consideration are −
Next, you can set the parameters for Solver as given below –
As you may observe, the parameters for Solver are −
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 −
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 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.
You have the following saving options for the issues that you solve with Solver −
Excel Data Analysis Related Tutorials
|VBA For Excel Tutorial||Microsoft Excel Tutorial|
|Microsoft Word Tutorial||Microsoft Powerpoint Tutorial|
|Microsoft Entity Framework Tutorial||Microsoft Azure Tutorial|
|Microsoft Project Tutorial||Advanced Excel Charts Tutorial|
|Advanced Excel Functions Tutorial||Excel Dashboards Tutorial|
|Excel pivot tables Tutorial||Excel Power View Tutorial|
Excel Data Analysis Related Interview Questions
|VBA For Excel Interview Questions||Microsoft Excel Interview Questions|
|Microsoft Word Interview Questions||Microsoft Powerpoint Interview Questions|
|Microsoft Entity Framework Interview Questions||Microsoft Azure Interview Questions|
|Microsoft Project Interview Questions||Excel Data Analysis Interview Questions|
|Excel pivot tables Interview Questions||Excel Power View Interview Questions|
|Microsoft Interview Questions|
Excel Data Analysis Tutorial
Excel Data Analysis
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.