What You Should Know about Optimization Modeling in Excel

By Stephen L. Nelson, E. C. Nelson

To understand optimization modeling, suppose that you’re a one-person business and you need Excel to track your profits. This example is sort of artificial, but it’s necessary to take some liberties in order to make optimization modeling and what the Solver does easy to understand.

Optimizing your imaginary profits

In your business, you make money two ways: You write books and you give seminars. Imagine that when you write a book, you make $15,000 for roughly six weeks of work. If you work out the math on that — dividing $15,000 by 240 hours — you see that you make roughly $62.50 an hour by writing a book.

Also assume that you make $20,000 for giving a one-day seminar on some subject on which you’re an expert. You make about $830.00 an hour for giving the seminar. You calculate this number by dividing the $20,000 that you make by the 24 hours that presenting the seminar requires you to invest.

In many situations, you might be able to figure out how many books you want to write and how many seminars you want to give simply by looking at the profit that you make in each activity.

If you make roughly $62 an hour writing a book and you make roughly $830 an hour giving a seminar, the obvious answer to the question, “How many books should I write and how many seminars should I give?” is, do as many seminars as possible and as few books as possible. You make more money giving seminars, so you should do that more.

Recognizing constraints

In many situations, however, you can’t just look at the profit per activity or the cost per activity. You typically need to consider other constraints in your decision-making. For example, suppose that you give seminars on the same subject that you write books about.

In this case, it might be that in order to be in the seminar business, you need to write at least one book a year. And so that constraint of writing one book a year needs to be considered while you think about what makes most sense about how you maximize your profits.

Commonly, other constraints often apply to a problem like this. For example, book publishers might require that you give a certain number of seminars a year in order to promote your books. So it might also be that in order to write books, you need to give at least four seminars a year. This requirement to give at least four book-promoting seminars a year becomes another constraint.

Consider other constraints, too, when you look at things such as financial resources available and the capacity of the tools that you use to provide your products or services. For example, perhaps you have only $20,000 of working capital to invest in things like writing books or in giving seminars.

And if a book requires $500 to be tied up in working capital but a seminar requires $2,500 to be tied up in working capital, you’re limited in the number of books that you can write and seminars that you can give by your $20,000 of working capital balance.

Another common type of constraint is a capacity constraint. For example, although there are 2,080 hours in a working year, assume that you want to work only 1,880 hours in a year. This would mean, quite conventionally, that you want to have ten holidays a year and three weeks of vacation a year.

In this case, if a book requires 240 hours and a seminar requires 24 hours, that working-hours limit constrains the number of books and seminars that you can give, too.

This situation is exactly the kind of problem that Solver helps you figure out. What Solver does is find the optimum value of what’s called your objective function. In this case, the objective function is the profit function of the business. But Solver, in working through the numbers, explicitly recognizes the constraints that you describe.