"Excel Solver: Example Demonstration on Using Solver in Excel 2003"

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 Cell
The cells that Solver will change the contents of to achieve the desired objective.

Constraints
Contains the changes that Excel will make.

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)!

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(D4:D7)
• 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 button 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.

• 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 button and you will see the Add Constraints dialog box, as illustrated below.

• 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 button (NOT THE OK BUTTON).
• 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. The dialog box will look as below.

• Next we need to tell Excel that we must have a minimum of four small cars.
• Click on the Add button.
• 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 button.
• 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.

• Next we need to tell Excel that we must have a minimum of two large cars.
• Click on the Add button.
• 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 button and you will see the dialog box below.

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

• Click on the OK button 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.

Can't find what you're looking for? Try Google Search!

Custom Search

Back to Top

You're viewing the excel solver info page, click here to go back to the Home Page

Add To Your Social Bookmarks: Blink Del.icio.us Digg
Furl Google Simpy Spurl Technorati Y! MyWeb