Microsoft Office Tutorials and References

In Depth Information

**Tutorial D: Building a Decision Support System Using Excel Solver**

D

TUTORIAL

BUILDING A DECISION SUPPORT

SYSTEM USING EXCEL SOLVER

In Tutorial C, you learned that Decision Support Systems (DSS) are programs used to help managers solve

complex business problems. Cases 6 and 7 were DSS models that used Excel Scenario Manager to calculate

and display financial outcomes given certain inputs, such as economic outlooks and mortgage interest rates.

The outputs from Scenario Manager allowed you to see how different combinations of inputs affected cash

flows and income so that you could make the best decision for expanding your business or selecting a

technology to develop and market.

Many business situations require models in which the inputs are not limited to two or three choices, but

include large ranges of numbers in more than three variables. For such business problems, managers want to

know the best or optimal solution to the model. An optimal solution can either maximize an objective

variable, such as income or revenues, or minimize the objective variable, such as operating costs. The

formula or equation that represents target income or operating cost is called an objective function. Optimizing

the objective function requires the use of constraints (also called constraint equations), which are rules or

conditions you must observe when solving the problem. The field of applied mathematics that addresses

problem solving with objective functions and constraint equations is called linear programming. Before the

advent of digital computers, linear programming required the knowledge of complex mathematical

techniques. Fortunately, Excel has a tool called Solver that can compute the answers to optimization problems.

This tutorial has five sections:

1. Adding Excel Solver to the Excel Ribbon—

Solver is not installed with Excel 2010; you must add

it to the application. You may need to use Excel Options to add Solver to the Ribbon.

2. Using Excel Solver—

This section explains how to use Solver. You will start by using Solver to

determine the best mix of vehicles for shipping exercise equipment to stores throughout the

country.

3. Extending the example—

This section tests your knowledge of Solver as you modify the

transportation mix to accommodate changes: additional new stores to supply and redesign of the

product to reduce shipping volume.

4. Using Solver on a new problem—

In this section, you will use Solver on a new problem:

maximizing the profits for a mix of products.

5. Troubleshooting Solver—

Because Solver is a complex tool, you will sometimes have problems

using it. This section explains how to recognize and overcome such problems.

NOTE

If you need a refresher, Tutorial C offers guidance on basic Excel concepts such as formatting cells and using the =IF( ) and

AND( ) functions.