The standard DSS appears to be a passive support system.
- It is built to respond with a series of pre-programmed routines to standardized and well-defined commands.
- It finds and presents data in a standard form in response to predefined data requests.
- It analyzes changes in its context with pre-designed routines.
- It computes the consequences of input data on the basis of well-defined model structures.
Models
A model is a spreadsheet that has taken the leap from being a data organizer to an analysis tool (Decisioneering, 2005). Models are usually simplified versions of the things they represent. It is often less costly to analyze decision problems using models. A model represents a process with combinations of data, formulas, and functions. As you add cells that help you better understand and analyze your data, your data spreadsheet becomes a spreadsheet model. For each component or variable of the model (e.g., costs, rates, demands) you can ask yourself, "How sure am I of this value? Will it vary? Is this a best estimate or a known fact?" Since you constructed the model, you will probably be quick to identify which variables these are. This lack of knowledge about particular values, or the knowledge that some values may always vary contribute to the model's uncertainty, helps you to identify your risks. Errors within Spreadsheets are an accepted drawback for most finance departments, yet this fact is seldom communicated to users (Stoltz, 2005).
Steps to Building a Spreadsheet Model
- Formulate the problem to determine the objectives of the simulation study, the system performance measures to be computed, and the scenarios to be evaluated (Evans, 2000). A scenario is any specification of the controllable inputs, that is, a particular variation of the system that we wish to study. By comparing the performance of different scenarios, we expect to be able to draw conclusions as to which scenario is the best, if indeed there is a significant difference.
- Develop a logical model of the system under a study that describes key events and actions that take place. Visual models provide the basis for implementing the simulation model on a spreadsheet. For systems simulations, the model is usually described with a flowchart
- Specify probabilistic assumptions needed to drive the simulation, that is, the probability distributions of input variables. If historical data is available, we can use the empirical distribution to drive the simulation, or fit the data to a theoretical distribution using data fitting techniques. If not, then a distribution must be selected judgmentally.
- Implement the model on a computer to perform the necessary calculations required to evaluate the distribution of model outputs (Fig. 1). One of the advantages of using spreadsheets is that an experienced spreadsheet user can develop a model rapidly by exploiting the built-in functions and structure of the spreadsheet.
EXCEL SPREADSHEET
Figure 1: Spreadsheet model and data table simulation of a newsboy problem
Pivot Tables
A PivotTable report can help you see the "big picture" by summarizing and analyzing your data. You can control how Excel summarizes the data—for example, by sum, average, or count—without entering a single formula. In a PivotTable report, you can quickly add and remove data (Fig 2), rearrange the layout, view a subset of data, or show the details you want.
PIVOT TABLE
Figure 2 – Add and remove data
Solver
You use Solver when you want to find the best way to do something (Fig.4). Or, more formally, when you want to find the values of certain cells in a spreadsheet that optimize (maximize or minimize) a certain objective. An optimization model has three parts: a target cell, changing cells, and constraints. The target cell represents the objective or goal. For example, maximize monthly profit. Changing cells are the spreadsheet cells that we can change or adjust to optimize the target cell. For example, the amount of each product produced during the month. Constraints are restrictions you place on the changing cells. For example, use no more resources than are available and do not produce more of a product than can be sold. The Excel Solver is used to solve a mathematical model which has been entered into an Excel spreadsheet. This mathematical model can be either:
- Linear programming
- Non-linear programming
- Integer programming
Figure 3 – Select Solver from the tools menu.
Data Mining
Data mining is a capability consisting of the hardware, software, "warmware" (skilled labor) and data to support the recognition of previously unknown but potentially useful relationships (Labovitz, 2003). It supports the transformation of data to information, knowledge and wisdom, a cycle that should take place in every organization. Companies are now using this capability to understand more about their customers, to design targeted sales and marketing campaigns, to predict what and how frequently customers will buy products, and to spot trends in customer preferences that lead to new product development. It is important to note that data mining is not software alone, as some vendors would have clients believe. While software may play an important role, it is only in the context of clear objectives and careful thinking from business management along with the skill of the analyst that data mining ultimately leads to a success story rather than an embarrassing and costly failure. Data mining is a capability within the business intelligence system (BIS) of an organization's information system architecture. The purpose of the BIS is to support decision making through the analysis of data consolidated together from sources, which are either outside the organization or from internal transaction processing systems (TPS). The central data store of BIS is the data warehouse or data marts.
Online Analytical Processing (OLAP)
On-Line Analytical Processing (OLAP) is a category of software technology that enables analysts, managers and executives to gain insight into data through fast, consistent, interactive access to a wide variety of possible views of information that has been transformed from raw data to reflect the real dimensionality of the enterprise as understood by the user (Moulton, 2005). OLAP is implemented in a multi-user client/server mode and offers consistently rapid response to queries, regardless of database size and complexity. OLAP helps the user synthesize enterprise information through comparative, personalized viewing, as well as through analysis of historical and projected data in various "what-if" data model scenarios. This is achieved through use of an OLAP Server. OLAP functionality is characterized by dynamic multi-dimensional analysis of consolidated enterprise data supporting end user analytical and navigational activities including:
- calculations and modeling applied across dimensions, through hierarchies and/or across members
- trend analysis over sequential time periods
- slicing subsets for on-screen viewing
- drill-down to deeper levels of consolidation
- reach-through to underlying detail data
- rotation to new dimensional comparisons in the viewing area
Decision Making Process
Decision making is the cognitive process of selecting a course of action from among multiple alternatives (Wikipedia, 2005). The proper balance of human and machine decision making is an important part of a system's design. It is easy to think of automating tasks traditionally performed by people, but it is not that easy to analyze how decisions are made by an experienced, intuitive worker. If an improper analysis of human decision making is made, the wrong decision making may be placed into the machine, which can get buried in documentation that is rarely reviewed. This will become a critical issue as artificial intelligence applications proliferate.
Scenario Analysis
Scenario analysis is a process of analyzing possible future events by considering alternative possible outcomes (scenarios) (Answers.com, 2005). The analysis is designed to allow improved decision-making by allowing more complete consideration of outcomes and their implications. For example, in economics and finance, a financial institution might attempt to forecast several possible scenarios for the economy (e.g. rapid growth, moderate growth, slow growth) and it might also attempt to forecast financial market returns (for bonds, stocks and cash) in each of those scenarios. It might consider sub-sets of each of the possibilities. It might further seek to assign probabilities to the scenarios (and sub-sets if any). Then it will be in a position to consider how to distribute assets between asset types (i.e. asset allocation); the institution can also calculate the scenario-weighted expected return (which figure will indicate the overall attractiveness of the financial environment).
Summary
In today's information critical environment, far too many institutions still face significant reporting obstacles (O’Donnell, 2005). Many operate with stagnant reporting practices and inefficient, cumbersome processes. However, with recent technological advances in databases, reporting tools and increased experience in this field - there is hope. Most would recognize that producing and analyzing reports are vital to managing any business. Yet few have developed a formal business strategy to maximize their return on information. Developing a Decision Support (DS) strategy that evolves with changes in technology, business requirements and service options is the key to staying competitive. A Decision Support System (DSS) strategy should be tightly integrated and support the overall business plan, have individuals responsible for the plan development and monitoring, have support of senior management, be broad in scope, and be reviewed frequently. Whether you outsource DSS or you are charged with designing and developing this information processing functionality internally, you need a strategy to maximize your return on information.
References
Answers.com, (2005). Scenario Analysis. Retrieved May 29, 2005 from http://www.answers.com/main/ntquery?method=4&dsid=2222&dekey=Scenario+analysis&gwp=8&curtab=2222_1
Decisioneering. (2005). Risk Analysis Overview. Retrieved May 29, 2005 from http://www.decisioneering.com/risk-analysis-model.html
Evans, J. (2000). Spreadsheets as a Tool for Teaching Simulation. Retrieved May 29, 2005 from http://ite.pubs.informs.org/Vol1No1/Evans/index.php
Labovitz, M. (2003). What Is Data Mining and What Are Its Uses? Retrieved May 29, 2005 from http://www.darwinmag.com/read/100103/mining.html
MacDermant, S. (1998). Decision Support Definition – Long. Retrieved May 29, 2005 from http://home.att.net/~seanm/ds/dsslongdef.htm
Moulton. (2005). OLAP and OLAP Server Definitions. Retrieved May 29, 2005 from http://www.moulton.com/olap/olap.glossary.html
O’Donnell, T. (2001). Effective Decision Support Requires a Dynamic Strategy. Retrieved May 29, 2005 from www.ncr.com/repository/brochures/services/Decision_Support_Strategy.pdf
Stoltz, S. (2005). OLAP, an alternative technology over spreadsheets. Retrieved May 29, 2005 from http://www.businessintelligence.com/print_content.asp?code=139&pagenum=1
Wikipedia. (2005). Decision Making. Retrieved May 29, 2005 from http://www.answers.com/library/Wikipedia-cid-1474358484
