Many of us use Microsoft Excel to make spreadsheets to help us with finances, organize lists and much more.  A little-known feature called the “Solver” gives you computing power you may have never imagined!

Let’s start with the foundation. There are three levels of using a computer.

1. Basic. This level includes using a computer to make a grocery list for you.  You could use paper and pen instead, but because of things like spell check and being able to keep all of your lists in no more space than your computer already takes up, you choose you computer.  Is it faster? Maybe, although picking up a pad and writing a grocery list is pretty quick. Does it require a power source to use? Yes, although you may say that you put it on your phone and that has power anyway.  It is really up to your own judgment, but my preference is usually Post-Its for this task.
2. Intermediate.  This level includes using your computer for something that you could do manually, but it would take much more time/resources/effort and perhaps contain errors of some kind.  Would you write a 20 page document by hand or even with a typewriter? Not in this century, most likely.  If you had to keep a list of 1000 addresses, would you use a Rolodex? Does anyone reading this even know what a Rolodex is?
3. Advanced. This level includes things that you wouldn’t even think of doing manually because it would take a team of people and even then may not be possible.  Things like financial models, database applications and other types of analysis and reporting fall into this category.

Most applications that we use computers for today are level 2 apps.  I know that some would argue that internet search and Facebook do more than someone would be able to pull off manually, but I would submit that the reason you need Facebook is because of Facebook.  You wouldn’t need to search the internet if there was no internet. These applications are becoming more prevalent in our lives, and we are becoming more dependent upon them.  So as they have not always been required in my lifetime, they may be for the next generation.

This blog is about a level 3 application that people have had to do for quite a while.  The example today is about how much product to make given limited parts and wanting to make the most product.  Fortunately, this mind-numbing problem from our worst math calss nightmares is pretty easy with Excel’s Solver.

The first thing to do is to make sure you have the Solver installed.  The directions here are for Excel 2007, but other versions’ installations can be as simple as going to Google and searching for “Install Solver in Excel 2003”, or whatever your version is.

For Excel 2007, click on the Office circle in the upper left corner of Excel and then “Excel Options” at the bottom of the menu.  Click on Add-Ins, then at the bottom of the window next to Manage: Excel Add-ins, click “Go…”.  Check “Solver Add-in” and then OK. Noe if you look in the “Data” tab at the top in the “Analysis” section, you will see “Solver”.

Download this Excel file.  It is a simple model for making home theater components.  Open the file and you will see that the upper left has how many parts required to make an item (e.g. it takes 2 “Speaker Cones” to make an LCD TV). On the right side you have the inventory on hand for use.  Below to the left is how much profit you make for each item. Lastly, at the bottom is the number of each item to make and the total profit made if you produced and sold the quantities recommended.  If you are a logic puzzle nut, you may want to try to solve this problem manually BEFORE getting the answer from Excel.  Either way, here are the steps to find the answer.

Click on the “Solver” from the Analysis section of the Data tab, and you will get a window to open up that will help you solve your problem. The values below should already be filled-in, so all you have to do is click “Solve”.  If you want to follow the steps below, simply click the “Reset All” button first to clear the values and enter them.

1. Click on the box next to “Set Target Cell:”, then click on H20. It will show up like $H$20, but for our purposes that is the same thing.
2. On the “Equal To:” line, click on “Max”.
3. Click in the box under “By Changing Cells:”, then click on the cell C20 and drag over to E20.  It should appear as $C$20:$E$20.
4. Click on the “Add” button and the Add Constraint box should appear.  Click again on the cell C20 and drag over to E20.  It should appear as $C$20:$E$20. The middle dropdown should show “>=”.  Finally, under the “Constraint:” box type “0” (zero).  This will ensure that our answers are greater than 0, since you can’t make negative product. Click the Add button.
5. With the “Add Constraint” box still up, click on the box under “Cell Reference:”, then click on cell H6 and drag down to H10 (it will appear as $H$6:$H$10).  Be sure the dropdown shows “<=”, then click in the box under “Constraint:”. Click on cell G6 and drag to cell G10 (it will show up like =$G$6:$G$10 in the box).  Click “OK” to enter this constraint and close the dialog box.
6. Click “Solve”.  You will see your answer appear in cells C20 to E20.

It turns out that if you make 200 LCD TVs and 200 Stereos you will make $25,000. And it also means that if you were hoping to eek out $30,000 from that inventory you will not be able to do it.

Excel is an incredible application and we will most likely come back to it at some point for another tip. Enjoy!

P.S. These steps can also be found in this video from the original creators of “Solver”.  They also have an updated version if you need more power than the standard Excel version delivers.