Excel 4 Ur Business

Overview

• How can a large drug company determine the monthly product mix at their Indianapolis plant that maximizes corporate profitability?
• If Microsoft produces Xbox consoles at three locations, how can they minimize the cost of meeting demand for Xbox consoles?
• What price for Xbox consoles and games will maximize Microsoft’s profit from Xbox sales?
• Microsoft would like to undertake 20 strategic initiatives that will tie up money and skilled programmers for the next five years. They do not have enough resources to undertake all 20 projects. Which projects should they undertake?
• How do bookmakers find the best set of ‘ratings’ for NFL teams to set accurate point spreads?
• How should I allocate my retirement portfolio among high-tech stocks, value stocks, bonds, cash, and gold?

In all these situations, we want to find the best way to do something. More formally, we want to find the values of certain cells in a spreadsheet that optimize (maximize or minimize) a certain objective. The Excel Solver helps you answer optimization problems.

An optimization model has three parts: the target cell, the changing cells, and the constraints. The target cell represents the objective or goal. We want to either minimize or maximize the target cell. In the example of a drug company’s product mix, the plant manager would presumably want to maximize the profitability of the plant during each month. The cell that measures profitability would be the target cell. The target cells for each situation described at the beginning of the chapter are listed in the following table.

Keep in mind that in some situations, you might have multiple target cells. For example, Microsoft might have a secondary goal to maximize Xbox market share.

Changing cells are the spreadsheet cells that we can change or adjust to optimize the target cell. In the drug company example, the plant manager can adjust the amount produced for each product during a month. The cells in which these amounts are recorded are the changing cells in this model. The following table lists the appropriate changing cell definitions for the models described at the beginning of the chapter.

are restrictions you place on the changing cells. In our product-mix example, the product mix can’t use more of any available resource (for example, raw material and labor) than the amount of the available resource. Also, we should not produce more of a product than people are willing to buy. In most Solver models, there is an implicit constraint that all changing cells must be nonnegative. I’ll discuss nonnegativity constraints in more detail in later chapters. Remember that a Solver model does not need to have any constraints. Table 24-3 lists the constraints for the problems presented at the start of the chapter.

The best way to understand how to use the Excel Solver is by looking at some detailed examples. In later chapters, you’ll learn how to use the Solver to address each of the problems presented in this chapter as well as several other important business problems.

To install the Excel Solver, click Tools, Add-Ins, and then check Solver-Add In. Click OK, and Excel will install the Solver. Once Solver is installed, you can run Solver by selecting Tools, Solver. The following figure shows the Solver Parameters dialog box. In the next few chapters, you’ll see how to use this dialog box to input the target cell, changing cells, and constraints for a Solver model.

After you have input the target cell, changing cells, and constraints, what does Solver do? To answer this question, you need some background in Solver terminology. Any specification of the changing cells that satisfies the model’s constraints is known as a feasible solution. For instance, in our product mix example, any product mix that satisfies the following three conditions would be a feasible solution:

• Mix does not use more raw material and labor than is available.
• Mix produces no more of each product than is demanded.
• Amount produced of each product is nonnegative.

Essentially, Solver searches over all feasible solutions and finds the feasible-solution that has the ‘best’ target cell value (the largest value for maximum optimization, the smallest for minimum optimization). Such a solution is called an optimal solution. As you’ll see in post Using Solver to Determine the Optimal Product Mix , some Solver models have no optimal solution and some have a unique solution. Other Solver models have multiple (actually an infinite number of) optimal solutions. In the post Using Solver to Determine the Optimal Product Mix , we’ll begin our study of Solver examples by examining the drug company product mix problem.

 

Explore posts in the same categories: MS Excel Tune Up