The solver in excel is part of an analysis tool known as “what ifs analysis”. You can use solver to ascertain an optimal value in one cell known as the “target cell”. Basically, solver is used for a group of cells that are directly or indirectly related. Constraints can also be applied to minimize the value that can be used by Solver. This article will provide step-by-step guide on how to use solver to find solution to a business problem.
Before you begin, ensure that the Solver is already installed on your computer. Go to the menu bar and click on “data”. The Solver is displayed on the right side under the data analysis. When you don't see it, then Solvent may not be installed. You can install it manually if you have the 2010 or higher version of Excel. Take your cursor to the office button and click on it. Go over to the Excel Options below and click on “Add-ins”. The instructions provided will help you add Solver to your Excel features.
Below is a practical example of the use of Solver to find solutions to all kinds of decision problems.
* Start-up the Solver Add-in and follow the following process
The solver can be located on the data tab
* Click the options on the green file tab
* Under the Add-ins option, click on the Solver and click on the Go button. See the screenshot below
After opening the Solver, a dialogue box loads automatically which allows you to specify the parameters you want to run the Solver.
The parameter you specify will depend on the type of problem you want to solve. But the most common types of parameters are constraints, changing cells, and target cells.
Using Solver Tool in Excel
Okay. Now that we are through Adding Solver to the Excel features, it is time to learn how to use Solver tool in Excel. Let's say you want to use this tool to help you solve a business problem such as running a cybercafé. The following steps will help solve this problem using Solver.
Suppose you take a bank loan to purchase UPS, furniture, and computers, and other accessories needed to successfully run a cybercafé, we can make use of the PMT function to find the amount of monthly installments needed to completely repay the bank.
The regular monthly expenses include maintenance, electricity, salaries, internet charges to the ISP, rent, advertisement costs, and telephone costs. The rented accommodation can take up to 24 computers but let's begin with 12.
Based on the cost, the minimum amount to charge the customers can be determined. But at this point, we are not going to concern ourselves with profit. The estimates will be made for breakeven (because we want to repay the bank loan before looking at profits). Using the Solver and an analysis of the per hour rates obtainable in other cybercafés, a what-ifs-analysis can be done to determine what can be charged to customers.
Go to data and select Solver. When the new window appears, select the target cell and equate its value to a certain number like 70,000 or 100,000. You may decide to set the lowest or highest value, but we won't need to set this level in this example. If we are dealing with minimization of cost here, then the maximum and minimum option will be useful.
If you want, you can equally set the value to be zero (0), to allow you establish the break even point where the monthly earnings will be equal to the monthly expenditures.
The Final step is to determine the cells that will affect the target cell. Determine the constraints like the minimum and maximum hours of work, minimum and maximum numbers of computers. It is important to make sure that the number of computers is a whole number. You can't have 2.4 computers because it will be difficult to analyze data. We also need to determine the lowest and highest amount that can be charged per hour for the cybercafé as determined by the research from other cybercafés. When this is done, input the necessary figures and make use of the Autosum features. You can now use the save data to manage your expenses and income until you approach breakeven for the loan collected.