How to use Solver in Excel 2007

Home Excel 2003 Macros Excel 2007 Macros Excel 2003 Excel 2010
About Us Free Ebook Offline Training Solver Books



How to use Solver in Excel 2007: First check whether the Solver is installed on your computer by clicking on 'data' in the menu bar. You will see 'Solver' under data analysis on the right.
If not installed, click on the 'office button', then 'Excel Options' at the bottom of the window, next click on 'Add-ins' and follow the instructions to add 'Solver'.
Solver in Excel is a What-if analysis tool that finds the optimal value of a target cell by changing values in cells used to calculate the target cell.
Let's see how we can use the solver tool to solve a practical business problem like running a cybercafe. We take a loan from the bank and purchase computers, furniture and Uninterrupted Power Supply equipment (UPS). Using the PMT function we can calculate the amount of monthly instalments we have to repay to our bank.
Our regular monthly expenses include salaries, electricity, maintenance, internet charges to our ISP, telephone costs, advertisement costs and rent. Our rented accomadation can easily accomadate 24 computers but we start with 12.
Based on our costs we can calculate the minimum amount we should charge the customer to break-even. That is, at this point in our business we neither make a profit nor a loss.
Now using the solver and a recce of what other cybercafes are charging per hour we can do a what if analysis.
  • Click on 'Data'
  • Select Solver
  • A new window opens. Now define the target cell (D15). Set its value equal to a certain amount like 100000 or 70000. You can also set to minimum or maximum. In this example it doesn't make sense to do that. In cases where you wish to minimize costs like costs of delivery, you could use this option. You could also set the value equal to 0. This would give you the breakeven point where the monthly expenses would be equal to the monthly earnings. In certain what-if analysis you could set the value equal to maximum if you wanted to maximize, let's say, your profit. But as indicated, this depends on the problem.
  • Next define the cells that will affect the target cell (B15,B6)
  • Then define the constraints like maximum and minimum hours of work, maximum and minimum number of computers. Also ensure that the number of computers is an integer because we cannot have 1.32 computers! We also define the minimum and maximum amount that we can charge per hour for cyber cafe use based on a recce.
  • Then we click on solve. If solver finds a solution, we can finally click on OK to keep the solution or cancel to find another solution based on different inputs.

More on Solver
Interesting Books on Solver for reference
How to use Solver to optimize your interest earnings
Using Solver for financial planning
Sensitivity analysis using the scenario manager in Solver
Excel 2007 Excel 2003 Macros Excel 2007 Macros Excel 2003 Excel 2010
About Us Offline Training Books on Solver