Microsoft Office Tutorials and References
In Depth Information
Tutorial D: Building a Decision Support System Using Excel Solver
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
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.
If you need a refresher, Tutorial C offers guidance on basic Excel concepts such as formatting cells and using the =IF( ) and
AND( ) functions.