Microsoft Office Tutorials and References
In Depth Information
Tutorial C: Building a Decision Support System in Excel
BUILDING A DECISION SUPPORT
SYSTEM IN EXCEL
Decision Support Systems (DSS) are computer programs used to help managers solve complex business
problems. DSS programs are commonly found in large, integrated packages called enterprise resource planning
software that provide information services to an organization. Software packages such as SAP
offer sophisticated DSS capabilities. However, many business problems can be
modeled for solutions using less complex tools such as Visual Basic, Access, and Microsoft Excel.
A DSS program is actually a model representing a quantitative business problem. The problem can range
from finding a desired product mix to sales forecasts to risk analysis, but almost all of the problems examine
financial outcomes. The model itself contains the data and the algorithms (mathematical processes) needed
to solve the problem.
In a DSS program, either the user manually inputs data or the program accesses data from a file in the
system. The program runs the data through its algorithms and displays output formatted as information; the
manager uses this data to decide what action to take to solve the problem. Some sophisticated DSS programs
display multiple possible solutions and recommend one based on predefined parameters.
Managers often find the Excel spreadsheet program particularly useful for their DSS needs. Excel
contains hundreds of built-in arithmetic, statistical, logical, and financial functions. It can import data in
numerous formats from large database programs, and it can be set up to display well-organized, visually appealing
tables and graphs from the output.
This tutorial is organized into four sections:
, and PeopleSoft
1. Spreadsheet and DSS Basics—
by creating a DSS
program in Excel. The program is a cash flow model for a small business looking to expand. You
will get an introduction to spreadsheet design, building a DSS, and using financial functions.
2. Scenario Manager—
This section lets you
get your feet wet
Here you will learn how to use the Excel Scenario Manager. A DSS typically
gives you one set of answers based on one set of inputs
the real value of the tool lies in its
abiland take a comparative look at all the solutions based on all combinations of
the inputs. Rather than inputting and running the DSS several times manually, you can use
Scenario Manager to run and display the outputs from all possible combinations of the inputs.
The output is summarized on a separate worksheet in the Excel workbook.
3. Practice Using Scenario Manager—
ity to play
Next, you will be given a new problem to model as a DSS,
using Scenario Manager to display your solutions.
4. Review of Excel Basics—
This section reviews additional information that will help you complete
the spreadsheet cases that follow this tutorial. You will learn some basic operations, logical
functions, and cash flow calculations.
SPREADSHEET AND DSS BASICS
You are the owner of a thrift shop that resells clothing and housewares in a university town. Many of your
customers are college students. Your business is unusual in that sales actually increase during an economic recession.
Your cost of obtaining used items basically follows the consumer price index. It is the end of 2011, and business
has been very good due to the continuing recession. You are thinking of expanding your business to an adjacent
storefront that is for sale, but you will have to apply for a business loan to finance the purchase. The bank will
require a projection of your profit and cash flows for the next two years before it will loan you the money to
expand, so you have to determine your net income (profit) and cash flows for 2012 and 2013. You decide that
your forecast should be based on four factors: your 2011 sales dollars, your cost of goods sold per sales dollar,
your estimates of the underlying economy, and the business loan payment amount and interest rate.