1 / 10

Lab 3 Solver Add-In In Excel

Lab 3 Solver Add-In In Excel. ► Lab 2 Review ► Solver Add-in Introduction ► Practice Solver following Instructor Saferly Inc. linear Programming Case ► Exercise. Solver Add-In In Excel.

season
Télécharger la présentation

Lab 3 Solver Add-In In Excel

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Lab 3 Solver Add-In In Excel • ► Lab 2 Review • ► Solver Add-in Introduction • ► Practice Solver following Instructor • Saferly Inc. linear Programming Case • ► Exercise

  2. Solver Add-In In Excel • Solvers, or optimizers, are software tools that help users find the best way to allocate scarce resources (such as to compute an optimal solution to a linear program stored in a worksheet) • Solver is an add-in written by Frontline Systems - not by Microsoft - www.frontsys.com/mlvbaref.htm or www.solver.com/tutorial.htm • It has an EXCEL interface • Go to Tools -> Solver … • To see how Solver really works, let’s work together at the following problem:

  3. An Example of Product Mix Saferly, Inc. manufactures two types of kitchen utensils: knives and forks. Both must be pressed and polished. The shop manager estimates that there will be a maximum of 70 hours available next week in the pressing machine center and 100 hours in the polishing center. Each case of knives requires an estimated 0.2 hour of pressing and 0.5 hour of polishing, while each case of forks requires 0.4 hour of pressing and 0.25 hour of polishing. The company can sell as many knives as it produces at the prevailing market price of $12 per case. Forks can be sold for $9 per case as many as it produces. It cost $4 to produce a case of knives and $3 to produce a case of forks. Saferly wants to determine how many cases of knives and forks it should produce to maximize the total dollar profit.

  4. Linear Programming Model • Decision variables: K – cases of knives to be produced F – cases of forks to be produced • Objective Function: maximize the total dollar profit (P) P = (12-4)K + (9-3)F = 8K + 6F ($/week)  Max P = 8K + 6F ($/week) • Constraints: Pressing 0.2K + 0.4F ≤ 70 (hours/week) Polishing 0.5K + 0.25F ≤ 100 (hours/week) K, F ≥ 0

  5. Create The Model In Excel • To create the model on Excel spreadsheet with the decision variables, result variables, and constraints included: • Open an empty Excel worksheet • Write the title of the model • Describe the decision variable definitions • Specify decision variables • Specify the objective function • Indicate the constraints

  6. Make solver available • Tools  Add-ins • Make sure that ‘Solver Add-in’ is checked

  7. Use Solver Add-In In Excel • Now use Solver to solve Saferly’s product mix model • Set Target Cell: the cell that contains the formula for the objective function (in this case, profit) • Equal To: the specification of the type of the objective function – Max, Min, or Value of • By Changing Cells: the cells set to contain the values of the decision variables • Subject to the Constraints: the adding of the constraints – CellRef (left hand side), Relations (<=, =, >=, Int, bin), FormulaText (right hand side, often a single number) • Options: • Assume Linear Model • Assume Non-Negative • Reset All: clears all previous settings • Run Solver

  8. Some Tips • Use the SUMPRODUCT function, which multiplies corresponding components in the given arrays, and returns the sum of those products. • Syntax: SUMPRODUCT(array1,array2,array3, ...)Array1, array2, array3, ...   are 2 to 30 arrays whose components you want to multiply and then add. • Indicate absolute cell address using “$”, so that the cell address doesn’t change when you copy formulas • You can add several constraints at the same time, if their relations are the same

  9. Exercise – Krazy Krakers Krazy Krakers makes three different types of crackers – Plain, Cheesey and No-Fat. Four ingredients are available in limited quantities; the amount of each needed per case of each type of cracker, the amount available, the cost per unit, and the selling price per case of each type of cracker are shown below. Plain Cheesey Non-Fat Total Available Unit Cost Labor 1 2 3 200 hours/week $2.5 Flour 5 4 7 750 pounds/week $0.5 Salt 3 2 1 150 ounces/week $1.0 Grease 4 9 0 900 gram/week free Price $13 $17 $21 Formulate an appropriate spreadsheet model to maximize Krazy Krakers’s weekly profits, then use solver to solve this model.

  10. More practice • Try the solver add- in with the problems that were set up in lecture. • Submit to my digital drop box one file that shows your grasp of the add-in (any of the problems you did would be fine)

More Related