Results 1 to 8 of 8

Thread: Topic 40: Guide on Using Solver in Excel 2003

  1. #1
    Administrator
    Join Date
    Oct 2009
    Posts
    5,607
    Rep Power
    10

    Topic 40: Guide on Using Solver in Excel 2003

    Advertisement

    Excel Solver is the most versatile ''what if'' tool. It can handle many different variables and where possible, Solver will produce the optimum answer.

    Note: Solver is an add-ins tool in Microsoft Excel 2003. If you are unable to view the Solver, just go to the Tools and click on Add-Ins. From the Add-Ins dialog box appear, tick on Solver Add-in and click OK.

    Excel Solver Terminology

    Target Cell
    The cell that will be set to a value, maximum or minimum. Often this cell is where you specify the maximum cost of a project.

    Changing CellAdvertisement

    The cells that Solver will change the contents of to achieve the desired objective.

    Constraints
    Contains the changes that Excel will make.

  2. #2
    Administrator
    Join Date
    Oct 2009
    Posts
    5,607
    Rep Power
    10
    Excel Solver - An Example

    In this example we have a budget to purchase as many new car as possible for the new company car fleet. We need a mix of small, medium and large cars.

    We have a number of constraints however:

    • The total budget is limited to $ 500,000
    • We need at least 4 small sized cars
    • We need at least 3 medium sized cars
    • We need at least 2 large sized cars
    • Also if we are going to let the computer calculate a mix of the different number of cars to meet our requirements and budget, then we will have to specify to Excel that buying part of a car is no use, i.e. he numbers of each type of car bought must be whole numbers (integers)!

  3. #3
    Administrator
    Join Date
    Oct 2009
    Posts
    5,607
    Rep Power
    10
    Construct your Spreadsheet and Use the Excel Solver

    • We have constructed the following data.
    • Formulas and functions used:
    • In cell E4 we have used the formula =B4*C4
    • In cell E5 we have used the formula =B5*C5
    • In cell E6 we have used the formula =B6*C6
    • In cell E8 we have used the function =SUM(D47)
    • When you have entered all the data, formulas and functions as outlined above, click on the cell D8.
    • From the Tools menu, click on Solver…
    • Make sure that the text box, Set Target Cell contains $D$8. If you selected this cell prior to starting Solver this cell reference should be entered here automatically.
    • Click on the Value of ****on and in the text box to the side of it enter the number 500,000 (this is setting the maximum size of our budget). The dialog should appear as illustrated below.


  4. #4
    Administrator
    Join Date
    Oct 2009
    Posts
    5,607
    Rep Power
    10
    • Next we need to decide which values need to change in order to fit our requirements. In this case the values to be changed are the numbers of each class of car that we can afford.
    • Click in the By Changing Cells part of the dialog box.
    • Either enter the information required by typing in $C$4:$C$6
      OR use the mouse to drag across these cells C4 to C6 and the cell reference will be entered automatically.

    • Next we need to tell Excel that constrains we have, such as limited budget etc.
    • Click on the Add ****on and you will see the Add Constraints dialog box, as illustrated below.


  5. #5
    Administrator
    Join Date
    Oct 2009
    Posts
    5,607
    Rep Power
    10
    • To enter the restraint that we have a budget limited to $ 500,000, in the Cell Reference text box enter the cell $E$8.
    • Then click on the down arrow in the center part of the dialog box, and select the = symbol.
    • In the Constraint part of the dialog box, enter the number 500000.
    • The dialog box will resemble that shown below.




    • To force Excel to realize that we cannot buy part of a car just whole numbers of cars we need to add another constraint.
    • Click on the Add ****on (NOT THE OK ****ON).
    • In the Cell Reference text box either enter the information $C$4:$C$6
      OR use the mouse to drag across these cells C4 to C6 and the Cell Reference will be entered automatically.
    • In the center part of the dialog box, click on the down arrow and select int which will force Excel to only use integer (i.e. whole) numbers in the selected range. Thedialog box will look as below.


  6. #6
    Administrator
    Join Date
    Oct 2009
    Posts
    5,607
    Rep Power
    10
    • Next we need to tell Excel that we must have a minimum of four small cars.
    • Click on the Add ****on.
    • In the Cell Reference text box either enter the reference $C$4
      OR use the mouse to click on the cell C4 and the cell reference will be entered automatically.
    • Select >= from the center part of the dialog box.
    • In the Constraint box, enter the value 4.The dialog box will be as illustrated below.



    • Next we need to tell Excel that we must have a minimum of three medium sized cars.
    • Click on the Add ****on.
    • In the Cell Reference text box either enter the reference $C$5
      OR use the mouse to click on the cell C5 and the cell reference will be entered automatically.
    • Select >= from the center part of the dialog box.
    • In the Constraint box, enter the value 3. The dialog will be as illustrated below.


  7. #7
    Administrator
    Join Date
    Oct 2009
    Posts
    5,607
    Rep Power
    10
    • Next we need to tell Excel that we must have a minimum of two large cars.
    • Click on the Add ****on.
    • In the Cell Reference text box either enter the reference $C$6.
      OR use the mouse to click on the cell C6 and the cell reference will be entered automatically.
    • Select >= from the center part of the dialog box.
    • In the Constraint box, enter the value 2. The dialog will be as illustrated below.


    We are now ready to solve the purchasing problem.

    Click on the OK ****on and you will see the dialog box below.

    • Click on the Solve ****on. After a short time you will see the following dialog box.

  8. #8
    Administrator
    Join Date
    Oct 2009
    Posts
    5,607
    Rep Power
    10

    • Click on the OK ****on to accept the solution.
    • The data in your workbook will now be as illustrated below.


    I hope that you already see the true value of Excel Solver. It's a good tool if you apply it correctly.

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •