Step 6 − The Add Constraint dialog box appears. Step 5 − Next, click the Add button to add the three constraints that you have identified. Step 4 − Select range C8:D8 in the By Changing Variable Cells box. Step 2 − In the Set Objective box, select the cell D3. The Solver Parameters dialog box appears. of units sold in Analysis > Solver on the Ribbon. Note that we have used the Min function to take care to see that the no. of units sold is dependent on the advertising cost per unit and hence is budget for the quarter / Adv. The initial values for advertising budget are set as 10000 per Quarter (Cells – C8 and D8). of units available for sale in Quarter1 is 400 and in Quarter2 is 600 (cells – C7 and D7). Next, set the cells for the required calculations as given below.Īs you can observe, the calculations are done for Quarter1 and Quarter2 that are in consideration are − You can proceed to define the problem as −
The Solver requires the following parameters − 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. Generalized Reduced Gradient (GRG) Nonlinear
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. The target cell is computed by adding together the terms of the (changing cell)*(constant) form.Įach constraint satisfies the linear model requirement. A Solver model is linear under the following conditions − You can choose one of the following three solving methods that Excel Solver supports, based on the type of problem − LP Simplex Now, you should be able to find the Solver command on the Ribbon under the DATA tab.
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. Then, one or more other variables are changed repeatedly, subject to the specified constraints, until you discover the best values for the target variables. 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. Solver is a Microsoft Excel add-in program you can use for optimization in what-if analysis.Īccording to O'Brien and Marakas, optimization analysis is a more complex extension of goal-seeking analysis.