Excel SLO 8.3-Using Solver PDF

Preview

Summary

SIMNET Excel...

Description

Excel - Chapter 8 - Exploring Data Analysis and Business Intelligence

SLO8.3

UsingSolver Solver is an analysis tool that finds the lowest, the highest, or a specific result for a formula by changing values in other cells within limitations that you set. Using Solver can be described as solving a problem in reverse,becauseyoustartwiththedesiredanswerandSolvercalculateshowtoreachthatanswer.Paradise LakesResort,forexample,mightsetagoalforquarterlyrevenueanduseSolvertodeterminethebestwayto reachthatgoalbycalculatingatargetforeachrevenuecategory. Solverisasophisticatedanalysistool,butitmaynotbeabletofindasolutiontoeveryproblem.Whenit cannotdoso,theSolverResultsdialogboxinformsyouthatitcouldnotfindasolution.

InstallandRunSolver Solver is an Excel addin. An addin is an enhanced command or feature that is not installed with the initialExcelsetup.YoucaninstalladdinsfromtheAddInsdialogboxinExcelOptions.

HOWTO:InstallSolver 1. Select Options [File tab]. 2. Click AddIns in the left pane. The ViewandmanageMicrosoftOfficeAddins dialog box opens. Applications that are currently active are listed near the top of the window. 3. Click SolverAddin in the list of InactiveApplicationAddIns (Figure 8-8). 4. Click Go near the bottom of the dialog box to open the Addins dialog box. 5. Select the SolverAddin box (Figure 8-9). You can remove Solver by deselecting the box. 6.

Click OK.

88AddinsdialogboxinExcelOptions 89Addinsdialogbox

The Solver button is located in the Analyze group on the Data tab.

  ASolverproblemhasthreecomponents,known asparameters.Aparameterisinformationusedby Solver to find a solution. The parameters are the objectivecell,variablecells,andconstraints. 810SolverproblemforParadiseLakes

TheObjectiveCell The objectivecellisacellwithaformulatobecalculatedtoreachadesiredresult.Itissometimes referredtoasthetargetcell.Youcansettheobjectivecelltothemaximumorminimumortoavalue. InFigure810,ParadiseLakeswantstodetermineamaximumtotalrebate.TheformulaincellD8is theobjectivecell.

VariableCells Inordertoreachthedesiredresultintheobjectivecell,Solverchangesthecellsidentifiedasvariable cells.Thesecellsmayalsobecalled decision cellsor changingcells.InFigure810,thevariable cellsarecellsB4:B7.

Constraints Aconstraintisarestrictionorlimitation.Itcouldbealimitationontheformula,ononeormoreofthe variablecells,oralimitationonothercellsthataredirectlyrelatedtotheobjectivecell.InFigure810, a constraint for cell B4, the Baudette value, is that pounds collected cannot be fewer than 130 or greaterthan150.WhenSolvercannotfindasolution,itisoftenduetohowconstraintsaredefined.

SolvingMethod Solver uses an algorithm, a stepbystep procedure, to find a solution. Three solving methods are available: GRG Nonlinear, LP Simplex, and Evolutionary. For most problems, start with GRG Nonlinear.IfSolver cannot find a solution, try either of the other methods. For sophisticated Solver problemswithcomplicatedconstraints,identifythetypeofproblemandexplorethesolvingmethods indetail. To run Solver, click the Solver button on the Data tab in the Analyze group and define each parameterintheSolverParametersdialogbox.

HOWTO:RunSolver 1. Click the Solver button [Data tab, Analyze group]. The SolverParameters dialog box opens. 2. Click the SetObjective box and click the cell with the formula to be solved. The objective cell must include a formula. 3. Make a selection for the To parameter. You can solve the formula for the minimum or maximum value. You can choose ValueOf and type a specific value in the entry box. 4. Click the ByChangingVariableCells box.

5. Select the cells that can be changed. You can paste or type a range name instead of selecting cells. Press Ctrl to select nonadjacent cells. 6. Click Add to the right of the SubjecttotheConstraints box. The AddConstraint dialog box opens. You can set multiple constraints for a cell. 7. Click the CellReference box and select the first cell or range that has a limitation. 8. Click the middle drop-down arrow and choose an operator. The int operator limits the value to a whole number.

811AddConstraintdialogbox

The bin operator requires a binary value, either 0 or 1. The dif operator is used to specify that all values in a range must be different. 9. Click the Constraint box and enter a value (Figure 8-11). You can click a cell with a value for the constraint. 10. Click Add in the AddConstraint dialog box to add another constraint. If you click OK but want to add another constraint, click Add to the right of the SubjecttotheConstraints box. 11. Click OK in the AddConstraint dialog box when all constraints are identified. The constraints are listed in the Solver Parameters dialog box. 12. Select the MakeUnconstrainedVariables NonNegative box. If you leave this box unchecked, a variable cell without a constraint can be solved to a negative number. 13. Click the SelectaSolvingMethod arrow and choose a method (Figure 8-12).

812CompletedSolverparameterswith constraints

If Solver cannot find a solution, try a different method. 14. Click Solve. The SolverResults dialog box includes an option to keep the solution or to return to the original values. You can generate Solver reports. You can save the results as a scenario. 15. Click OK to keep the solution.

 

SolverReports SelectanoptionintheSolverResultsdialogboxtogeneratestatisticalanalysisreportsabouttheproblem and the solution. Three reports for a solved problem are listed: Answer, Sensitivity, and Limits. These reportsarestraightforwardtogenerate,butyoushouldhaveanunderstandingofstatisticalconceptsand termstounderstandandinterpretthereports.

MOREINFO When Solver cannot find a solution, print the Feasibility and FeasibilityBounds reports to helpidentifyconstraintissues.

AnswerReport The Answer report identifies and lists each parameter and serves as documentation of your work. The report includes original values and values suggested by Solver as shown in Figure813. ThefirstAnswerreportisinsertedintheworkbookinasheet named Answer Report 1. If you run Solver multiple times, you can generate an answer report each time, and the sheets are namedAnswerReport2,andsoon.

813ASolverAnswerreport

HOWTO:CreateanAnswerReport 1. Complete the SolverParameters dialog box as needed. 2. Click Solve. 3. Select Answer in the Reports section in the SolverResults dialog box. (Figure 8-14). You can also select Sensitivity and Limits to generate all three reports. 4. Select the OutlineReports box if the report should be formatted as an Excel outline.

814SolverResultswindow

5. Click OK. The report is generated on a new sheet. 6. Select the AnswerReport1 sheet tab. The report documents how Solver reached the solution. IfSolverreturnsaruntimeerrorinamessagebox,uninstallthecommandfromtheAddInsdialog boxinExcelOptions,andexitExcel.ThenrestartExcelandinstallSolveragain.

HOWTO:UninstallSolver 1. Select Options [File tab] and click AddIns in the left pane. The ViewandmanageMicrosoftOfficeAddins dialog box opens. Active applications are listed near the top of the window. 2. Select SolverAddin in the list of ActiveApplicationAddIns. 3. Click Go near the bottom of the dialog box. 4. Deselect the SolverAddin box and click OK....