Analyzing Business Data with Excel

Chapter 9. Optimizing

You make decisions all the time. You decide where to go for lunch or what kind of car to buy, and you hope for the best possible outcome. In these situations defining “best possible outcome” can be difficult, and there is no way to be sure things will work out for the best. In business there are times when the best possible outcome can be defined and measured. If we know the costs and potential gain and we know what outcome we want, Excel has tools that can find the best decision.

Sometimes you are only deciding one thing. You may be setting the price of a product or deciding how many employees will be needed to do a job. In cases where only one value is being set, Excel’s Goal Seek tool can find the value that gives the desired outcome. In order to use Goal Seek you have to model the situation, building in the costs and benefits. You also have to define the desired outcome. In this chapter, we learn to use Goal Seek both as a one-time problem solver and as a way to add power to a macro.

Not all problems can be solved with one value. Excel’s Solver tool is designed to handle problems with multiple variables. As with Goal Seek, you must first model the problem. Solver problems are more complex, since they involve more options. This chapter demonstrates ways to build Solver solutions. At the end of the chapter we build a macro that uses the Solver to handle a typical business problem.

Goal Seek

I need $100,000. The bad news is that I only have $65,000. The good news ...