How to Use the Solver Add-in for Excel – Part 2

Solver Parameters

This is Part 2 of my series of video tutorials where I demonstrate how to use the SOLVER Add-in Tool in Excel to produce a specific result for a formula. Remember that SOLVER is an Add-in program that you must activate before you can use it. Watch Part 1 in this series to learn how to activate SOLVER.

In this tutorial, I work with the same data set that I used in Part 1. This time, I want to achieve a specific amount of Gross Profit Sales Dollars. First, however, I must determine what is a reasonable amount to set as the Target for Solver to return.

Use SUMPRODUCT Function in Excel

I use the SUMPRODUCT Function to quickly determine my possible Revenue, Cost of Goods Sold and Gross Profit is I sell all of the Units that I have in stock. Now, I have a reasonable Target for SOLVER to reach. SUMPRODUCT is a very efficient Function that, unfortunately, very feel Excel users – in my experience – know about. Follow this link to my Index of Excel Video Lessons to find additional tutorials for SUMPRODUCT.

Best Practice Tips for Solver

Work with a copy of your worksheet. This is always a good idea, and it is most important when you are beginning to learn how to work with SOLVER.
Write out your Goal for SOLVER.

Do you want SOLVER to Maximize the Formula? For example, to Maximize your potential profit?
Do you want SOLVER to Minimize the Formula? For example, to Minimize your costs?
Do you want SOLVER to return a Specific Number? If so, what is that number?
Determine your “Variable Cells.” SOLVER calls them the “By Changing Cells.” These are the cells that SOLVER will automatically adjust.

The Variable Cells must “feed int” the formula in your Target Cell – This is the key to making SOLVER work!
Write out your “Constraints.” These are the “real-world” conditions that you want SOLVER to operate under. This is the real power in SOLVER.
Do not treat the SOLVER results as “the Gospel.” SOLVER is a powerful Business Intelligence Tool. You, however, know your own business or industry. Use SOLVER as a tool to help you to make informed business decisions.