The Microsoft Excel is a super useful software and can be used to solve even complex mathematical problems sometimes as well as used in solving other difficult analysis as well as calculations but with modern software, you can still get the best out of your Excel.
Let’s say you want to perform tasks such as finding the lowest and highest numbers possible to answer a mathematical problem, then you’ll need to get the Solver extension which can quickly help you get the job done as quick as possible. Below is everything you should know about the Solver add on in Microsoft Excel and how to install and use the software.
So what’s a Solver in Excel anyway?
You’re right Microsoft didn’t develop this tool but from a third-party developer but it comes bundled with the Excel software upon installation but it’s always disabled by default. What it does is offering you the “What-if” analysis which helps in determining the variables you need to in order to answer a mathematical problem proficiently. I hope that definition makes sense.
Take for example, what is the minimum number of sales you’d need to make in order to cover the cost of an expensive piece of business equipment?
Now if you check this problem out there are three parts to it which includes a target value, variable that it can change to reach that value and then the constraints that Solver has to work in. All of these three elements are then used by the Solver software (Add-on) to work out the sales you’d need in order to cover the cost of that equipment. This makes Solver a more advanced tool than Excel’s own Goal Seek feature.
So how do you enable Solver in Excel?
By default, Microsoft turns this Add-in off (maybe because it’s not their software…who knows) so you’ll need to follow this step by step guide to turn on the Solver add-in for your Microsoft Excel.
First of all, open Excel and press File > Options to open the Excel Options menu.
In the Excel Options window, press the Add-ins tab to view the settings for Excel add-ins.
In the Add-ins tab, you’ll see a list of available Excel add-ins.
Select Excel add-ins from the Manage drop-down menu at the bottom of the window, then press the Go button.
In the Add-ins window, press the checkbox next to the Solver Add-in option, then press OK to confirm.
Once you click OK, the Solver add-in will be enabled, ready for you to begin using it.
So how do you use the Excel add-in for Excel?
Now that you’ve turned on the add-in, the Solver will be available for your use anytime. To start that out, you’ll need an Excel spreadsheet which will appropriate data to allow you to use the Solver. But to further explain how to use the Solver, then we’re going to be a bit more mathematical in this guide.
As said earlier about the cost of covering for an expensive equipment, in order to be able to make the payment for the expensive equipment, a business needs to sell a certain number of products to pay for the equipment. And in order to meet the target, several variables may change and to determine this, Solver can be used to determine the product cost to pay for the equipment based on a set number of products.
Alternatively, if you’ve set the price, you could determine the number of sales you’d need to break even—this is the problem that we’ll be attempting to solve using Solver.
Star Solver in Excel
To use Solver to solve this kind of query, press the Data tab on the Excel ribbon bar.
In the Analyze section, press the Solver option.
This will load the Solver Parameters window. From here, you can set up your Solver query.
Choose Solver Parameters
You’ll need to select the Set Objective cell. For this example, you want the revenue in cell B6 to match the equipment cost in cell B1 to break even. With this, you can determine the number of sales needed to meet-up with the equipment cost.
The To figure lets you find the minimum (Min) or maximum (Max) possible value to meet a target, or you can set a manual figure in the Value Of box. The best option for our test query would be the Min option. The reason for that is because we need to find the minimum number of sales to reach our break-even target. If you wanted to do better than this (for instance, to make a profit), you could set a custom revenue target in the Value Of box instead.
The price is remaining the same, so the number of sales in cell B5 is the variable cell. This is the value that needs to increase. You’ll need to select this in the By Changing Variable Cells option box.
The next is to set the constraints which is what Solver will pick up and use for its final value. If your criteria are more complex, then you can set a number of constraints for Solver to work. For this query, we’re looking for a revenue number that’s greater than, or equal to, the initial equipment cost. To add a constraint, press the Add button.
Use the Add Constraint window to identify your criteria. For this example, cell B6 (the target revenue figure) needs to be greater than, or equal to, the equipment cost in cell B1.
Once you’ve selected your constraint criteria, press the OK or Add buttons.
Before you can run your Solver query, you need to confirm the solving method for Solver to use. By default, this is set to the GRG Nonlinear option, but there are other solving methods available. Once you’re ready to run the Solver query, press the Solve button.
How to run a Solver Query
The moment you click on the Solve button, Excel will attempt running your Solver query and then pop out a result window which lets you know if the query was successful or not.
For our example, Solver found that the minimum number of sales required to match the equipment cost (and thus break even) was 4800.
You can select the Keep Solver Solution option if you’re happy with the changes Solver has made, or Restore Original Values if you’re not.
To return to the Solver Parameters window and make changes to your query, press the Return to Solver Parameters Dialog checkbox.
Click OK to close the results window to finish.
