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.
Watch Part 1 in SOLVER Series of Video Tutorials
- Here is the link to watch Part 1 of my series of Excel Tutorials for the Solver Add-in Tool.
- Watch Part 1 of this Series in High Definition on YouTube – My Channel is DannyRocksExcels
Watch Tutorial in High Definition
Follow this link to view this Excel Tutorial in High Definition on my YouTube Channel – DannyRocksExcels.
How to Take Advantage of the Go To Special Dialog Box Options in Excel
Go To Special Options
In my opinion, the Go To Special Options Dialog Box offers some of the most useful tools in Excel!
Why?
Because, you must…
Select Cells Before Performing an Action on these Cells
The “key” to understanding ANY MS Office or Windows Program is… You MUST select a single cell or a range of cells BEFORE you can perform an action on them – e.g. Formatting you selection, deleting your selection, editing your selection or auditing your selection.
Tips Presented in this Video Tutorial
I am positive that Excel users at ANY LEVEL will be able to pick up at least one solid tip from this Video Tutorial. Please send me your comments to let me know what you learned – or what you need clarification on.
Watch Tutorial in High Definition Mode
Follow this link to view this Excel Tutorial in High Definition / Full Screen Mode on my YouTube Channel – DannyRocksExcels
Learn About My New Extended Length Excel Video Tutorials
I have just published the first in a series of “Extended Length” – 90 Minutes – Video Tutorials, “Excel Pivot Tables to Summarize, Analyze and Present Your Data.” Follow this link to learn more about this tutorial. I have created separate versions of the tutorial for Excel 2010, 2007 and 2003.