How to Set Up a Solver Worksheet in Excel

By Stephen L. Nelson, E. C. Nelson

Here is an Excel workbook set up to solve an optimization modeling problem for a one-person business. If you choose to construct the Solver workbook example yourself (a fine idea), you want to tell Excel to display actual formulas rather than formula results in the workbook.

This is what this workbook does, by the way. To do this, select the worksheet range in which you want to display the actual formulas rather than the formula results and then simultaneously press the Ctrl and the ` (grave accent) keys. By pressing Ctrl+`, you tell Excel to display the formula rather than the formula result within the selected range.

image0.jpg

Setting up a Solver workbook requires three steps:

  1. Identify the Solver variables.

    First, you want to identify the variables in your optimization modeling problem. In the case of trying to figure out the number of books to write and seminars to give to make the most money in your one-person business, the two Solver variables are books and seminars.

    You would enter the labels shown in range A1:A3 and then the starting variable values shown in range B2:B3. This part of the worksheet isn’t anything magical. It simply identifies which variables go into the objective function. The objective function is the formula that you want to maximize or minimize. The values stored in the worksheet range B2:B3 are the starting guesses about what the optimal variable values should be.

    This is just a guess that the optimal number of books to write is two and that the optimal number of seminars to give is eight. You won’t know what the optimal number of books and seminars actually is until you work out the problem.

    Although you don’t have to name the cells that hold the variable values — in this case, cells B2 and B3 — naming those cells makes your objective function formula and your constraint formulas much easier to understand. So, you should name the cells.

    If you set up a workbook like this one, you can name the variable value cells by selecting the worksheet range A2:B3 and then clicking the Formula tab’s Create from Selection command button. When Excel displays the Create Names from Selection dialog box, select the Left Column check box and click OK.

    This tells Excel to use the labels in the left column: This would be the range A2:A3 — to name the range B2:B3. In other words, by following these steps, you name cell B2 Books and you name cell B3 Seminars.

    image1.jpg

  2. Describe the objective function.

    The objective function, shown in cell B5, gives the formula that you want to optimize. In the case of a profit formula, you want to maximize a function because you want to maximize profits, of course.

    Not all objective functions should be maximized. Some objective functions should be minimized. For example, if you create an objective function that describes the cost of some advertising program or the risk of some investment program, you can logically choose to minimize your costs or minimize your risks.

    To describe the objective function, create a formula that describes the value that you want to optimize. In the case of a profit function for the one-person business, you make $15,000 for every book that you write and $20,000 for every seminar that you give. You can describe this by entering the formula =15000*Books+20000*Seminars.

    In other words, you can calculate the profits of your one-person business by multiplying the number of books that you write times $15,000 and the number of seminars that you give times $20,000. This is what shows in cell B5.

  3. Identify any objective function constraints.

    In the worksheet range A8:C11, the constraints are described and identified on the objective function. Four constraints can limit the profits that you can make in your business:

    • Cash required limit: The first constraint (cell A8) quantifies the cash required constraint. In this example, each book requires $500 cash, and each seminar requires $2,500 cash. If you have $20,000 cash to invest in books and seminars, you’re limited in the number of books that you can write and the number of seminars that you can give by the cash, up-front investment that you need to make.

      The formula in cell B8, describes the cash required by your business. The value shown in cell C8, 20000, identifies the actual constraint.

    • Working hours limit: The working hours limit constraint is quantified by having the formula in cell B9 and the value 1880 in cell C9. Use these two pieces of information, the formula and the constant value, to describe a working hours limit. In a nutshell, this constraint says that the number of hours that you spent on books and seminars needs to be less than 1880.

    • Minimum number of books policy: The constraint that you must write at least one book a year is set up in cells B10 and C10. The formula =Books goes into cell B10. The minimum number of books, 1, goes into cell C10.

    • Minimum number of seminars policy: The constraint that you must give at least four seminars a year is set up in cells B11 and C11. The formula goes into cell B11. The minimum number of seminars constant value, 4, goes into cell C11.

After you give the constraint formulas and provide the constants to which the formula results will be compared, you’re ready to solve your optimization modeling problem. With the workbook set up, solving the function is actually very easy.

Setting up the workbook and defining the problem of objective function and constraint formulas is the hard part.