WIXLIB

SOLVER for OptimizationDr. Warisa Wisittipanich

OptimizationLOGO The procedures for finding an alternativewith the best achievable performance aspossible under the given constraints Minimization/Maximization Optimization tools?LingoLindoExcel - SolverCPLEXMathlabEtc.

Excel SolverLOGO Solver is numerical optimization add-insoftware embedded in Excel, but notenable by default Used to solve problem to find optimal ornear optimal value Solve linear/some non-linear problems

Excel SolverLOGO Advantages– Everything of interests can be tracked (costs,profits, resources availability, resource usage)– Cells of interests can be varies– Used as “what-if” analysis tool Disadvantages– Black box– Need carefully modeling: constraints

Excel SolverLOGOMain Components : Target cells Changing cells Constraints

Excel SolverLOGO Target cell(s): contains objective function, the cellthat represents the goal or objective of theproblem Setting: maximization/minimization/fixed valueTarget CellMaximizeProfitMinimizeCostValue ofNet future value

Excel SolverLOGO Changing cells /Adjustable cells: cells that containdecision variables, the cells that can be modifiedto arrive at the desired outcome, cells that we canchange or adjust to optimize the target cellProblemChanging cellsProduct mixAmount of each product producedProduct shippingShipping quantityInvestmentMoney invested in each project

Excel SolverLOGO Constraints:– Restrictions or limitations to what Solver cando to solve the problem– Restrictions to the changing cells or other cellsrelated– The rules which define the limits of thepossible solutions to the problem– Requirement constraints: given by problem– Non-negative constraints

Excel SolverLOGOType of ConstraintNot greater thanNot less thanEqual toIntegerBinaryNon-negativeSymbol intbin 0ProblemConstraintsProduct mixProduct mix produced cannot use moreresources than are availableProduction planning Do not produce products more thandemandInvestmentObtain an expected return of at least 10percent on the investment

How to use SolverLOGO Activate Solver: add-in that is not installedby defaultFile Excel Option Add-in Manage Excel Add-in Select the Solver Add-in checkbox

How to use SolverLOGOSolver will be available as a new Tab on the data, Analysis Tab

How to use SolverLOGO Determine target cell(s): must be a Formulacell containing changing cells– maximize, minimize, set value Determine changing cells: must ‘feed’ intotarget cell Establish constraints: the ‘key’ to makeSolver work !!

How to use SolverLOGO

How to use SolverLOGO

How to use SolverLOGO

How to use SolverLOGOPrecision is the real number from 0 to 1higher numbers means more preciseTolerance shows how far away from thetrue optimal value and still beacceptable. Only applied to theproblem with integer constraints

How to use SolverLOGO

Functions in ExcelLOGO SUM() SUMPRODUCT()– Sum of product of 2 data sets [][] which arecorresponding to each other– ExampleSUMPRODUCT(L4:N4,L5:N5) (3*1) (4*5) (2*0) Etc .

ExampleLOGOMax z 3x1 2x2s.t.2x1 x2 100x1 x2 80x1 40x1, x2 0

Product mix exampleLOGOThe XYZ company makes two products: Doors and Windows.Three manufacturing resources are required: Cutting, Sanding andFinishing.The requirements measured in hours per unit and shown in the tablebelow along with the profit per unit of product. The availability ofCutting, sanding , and finishing operations are 40, 40, and 60 hoursrespectively. Determine the quantity of doors and windows to beproduced to maximize the profit of the company.ProductDoorsWindowsCutting Hours per Unit10.5Sanding Hours per Unit0.50.75Finishing Hours per Unit0.51Profit per Unit 500 400

Product mix exampleLOGOFormulate the Product mix problemX1 : number of Doors producedX2 : number of Windows producedz : company profitmax zs.t 500 X1 400 X2X1 0.5X2 400.5X1 0.75X2 400.5X1 X2 60X1 , X2 0 (non-negativeconstraint)

Diet ProblemLOGOMy diet requires that all the food I eat come from one of the four “basicfood groups”(chocolate cake, ice cream, soda, and cheesecake). Now, thefollowing four foods are available for consumption: brownies, chocolate icecream, cola, and pineapple cheesecake. Each brownie costs 50 , each scoopof chocolate ice cream costs 20 , each bottle of cola costs 30 , and eachpiece of pineapple cheesecake costs 80 . Each day, I must consume at least500 calories, 6 oz of chocolate ,10 oz of sugar, and 8 oz of fat. The nutritional content per unit of each food is shown in Table2. Solve this problem thatcan be used to satisfy my daily nutritional requirements at minimum cost.Type of foodCaloriesChocolate (oz) Sugar (oz)Fat (oz)Brownies400322Chocolate ice cream(1 scoop)200224Cola(1 bottle)150041Pineapple Cheesecake(1 piece)500045

Transportation ProblemLOGOPowerco has 3 electric power plants that supply the needs of 4 cities.The costs of sending 1 million kwh of electricity from plant to city areshown in the table. Use Solver to minimize the cost of this problem.ToFromSupplyCity1City2City3City4(million kwh)Plant1 8 6 10 935Plant2 9 12 13 750Plant3 14 9 16 540Demand45203030(million kwh)

Assignment ProblemLOGOMachineco has 4 machines and 4 jobs to be completed. Each machinemust be assigned to complete one job. The time required to set up eachmachine for completing each job is shown the Table. Use Solver tominimize the total set up time needed to complete the 4 jobs.Time (Hours)MachineJob 1Job 2Job 3Job 411458722126537839424610

Step through Solver Trial SolutionsLOGOSolutions can be observed step by step:Solver Parameters Options

Step through Solver Trial SolutionsLOGOSelect the Show Iteration Results check box to see the values of eachtrial solutions --- click OKDuring the run;Stop: to stop the solution processContinue: to continue the solutionprocess

LOGOSolver Report

What-If AnalysisLOGOThe process of changing the values in cells to see how thosechanges will effect the outcome of formulas on the worksheetWhat-If Analysis tools in Excel: Scenarios consider many different variables Goal Seek find out how to get a desired result Data Tables see the effects of one or two variables on formula

What-If AnalysisLOGOGoal Seek to find out how to get a desired resultThe target is to determine the possible input value that produce theresultvariable 1?resultGoal Seek work with only one variable input value!!

What-If AnalysisLOGOData Tables see the effects of one or two variables on aformula/resultvariable1resultvariable2Data Table cannot work with more than two variables.But it can take as many different variable values as wanted.If a model has more than two variables, use scenarios.

What-If AnalysisLOGOScenarios consider many different variablesVariable 1Variable 2.resultVariable nIt can take many input variables sets up to 32 different valuesBut as many scenarios as wanted can be created

What-If AnalysisLOGOSolver is a tool for What-If AnalysisSolver is similar to Goal Seek except for thefact that it can determine more than onevariable inputs

Solver ReportLOGOTo save adjusting cell values as a scenario, click Save Scenario, thentype the name of the scenario in the Scenario Name box

Solver ReportLOGOScenario will be available for use in the Scenario Manager, which isaccessed from Data What-If-Analysis TabSee example

Solver ReportLOGOScenario Name is “good”

Solver ReportLOGOScenario Name is “good”.

Solver ReportLOGOReport will be created on a new worksheet in the current workbook

Product mix exampleLOGOThe XYZ company makes two products: Doors and Windows.Three manufacturing resources are required: Cutting, Sanding andFinishing.The requirements measured in hours per unit and shown in the tablebelow along with the profit per unit of product. The availability ofCutting, sanding , and finishing operations are 40, 40, and 60 hoursrespectively. Determine the quantity of doors and windows to beproduced to maximize the profit of the company.ProductDoorsWindowsCutting Hours per Unit10.5Sanding Hours per Unit0.50.75Finishing Hours per Unit0.51Profit per Unit 500 400

Product mix exampleLOGOFormulate the Product mix problemX1 : number of Doors producedX2 : number of Windows producedz : company profitmax zs.t 500 X1 400 X2X1 0.5X2 400.5X1 0.75X2 400.5X1 X2 60X1 , X2 0 (non-negativeconstraint)

Product mix exampleLOGO

Answer ReportLOGOAnswer Report reports The original and final objective values The original and final values of the variables The final values of constraints The constrains formulas, binding status and theslacksConstraint with no Slack - Binding

Answer ReportLOGOConstraint with no Slack - Binding

Sensitivity ReportLOGOSensitivity Report gives The optimal variable values The final values for constraints The shadow prices for the constraints with themaximum allowable increase and decrease The reduced cost for the input variables with themaximum allowable increase and decreaseNote: if the model contains integer or Booleanconstraints, sensitivity report cannot be produced

Sensitivity ReportLOGO

Sensitivity ReportLOGOShadow Price/Dual price: determine how theobjective values will change as you obtain an additionalunit of constraints without re-run the SolverIf constraint is binding (no slack) -- Shadow price 0If constraint is no binding -- Shadow price 0

Sensitivity ReportLOGOReduced Cost : determine additional cost/profit forevery additional variable unit occurredIf reduced Cost 0 - Solution is optimal

Sensitivity ReportLOGOMAXIMIZATION (ex. Profit, Revenue)If shadow price is positiveIf shadow price is negativeProfit increaseProfit decreaseIf reduced cost is positiveIf reduced cost is negativeProfit increaseProfit decreaseMINIMIZATION (ex. Cost)If shadow price is positiveIf shadow price is negativeCost increaseCost decreaseIf reduced cost is positiveIf reduced cost is negativeCost increaseCost decrease

Sensitivity ReportLOGOAnalysis: Product Mix Problem1. If the availability of the cutting hours is increased to 80hours, how much does the XYZ company gain theirprofit?2. How much should the XYZ company pay for the sandinglabor cost if the availability of sanding hours is increasedto 41?

Limits ReportLOGOLimits Report reports The achieved optimal objective value The input variables with the optimal values and withlower and upper bound The lower bound indicates the smallest value that avariable can take while satisfying the constraints andholding all of the other variables constant The upper bound is the largest value the variable cantake under these circumstances

Limits ReportLOGO

Diet ProblemLOGOMy diet requires that all the food I eat come from one of the four “basicfood groups”(chocolate cake, ice cream, soda, and cheesecake). Now, thefollowing four foods are available for consumption: brownies, chocolate icecream, cola, and pineapple cheesecake. Each brownie costs 50 , each scoopof chocolate ice cream costs 20 , each bottle of cola costs 30 , and eachpiece of pineapple cheesecake costs 80 . Each day, I must consume at least500 calories, 6 oz of chocolate ,10 oz of sugar, and 8 oz of fat. The nutritional content per unit of each food is shown in Table2. Solve this problem thatcan be used to satisfy my daily nutritional requirements at minimum cost.Type of foodCaloriesChocolate (oz) Sugar (oz)Fat (oz)Brownies400322Chocolate ice cream(1 scoop)200224Cola(1 bottle)150041Pineapple Cheesecake(1 piece)500045

Diet ProblemLOGOCreate sensitivity report and answer the followingquestions1. If you must consume total calories at least 600 oz,what is the new cost?2. If you must consume chocolate at least 8 oz, whatis the new cost ?3. If you want to eat 1 piece of brownie, what is thenew cost?

Transportation ProblemLOGOPowerco has 3 electric power plants that supply the needs of 4 cities.The costs of sending 1 million kwh of electricity from plant to city areshown in the table. Use Solver to minimize the cost of this problem.ToFromSupplyCity1City2City3City4(million kwh)Plant1 8 6 10 935Plant2 9 12 13 750Plant3 14 9 16 540Demand45203030(million kwh)