Building Model-Driven Decision Support Systems
- Are any Model-Driven DSS used in your company? If so, what types of models are used and for what purposes?
- Who uses the Model-Driven DSS? Managers? Analysts? No one!
- Who develops and maintains Model-Driven DSS? MIS unit? Users?
Questions for Review
- What is linear programming?
- What are the general components of a quantitative model? What are decision variables? Uncontrollable variables? Result variables?
- What are the major types of models used in Model-Driven DSS?
- What is multi-criteria decision-making? What is risk analysis? What is a decision tree?
- What are the advantages of developing and using models is a DSS?
- What is goal seeking? How can it be used? What is solver software?
- What are the advantages of using simulation?
- What is a scenario? What is a financial scenario?
- Explain "What If" and sensitivity analysis.
Questions for Discussion
- Why is sensitivity analysis used with Model-Driven DSS?
- What is the role of forecasting in decision support?
- Suppose you have several factories and want to find the best locations to manufacture different products to meet demand in nearby cities. You want to maximize profits and minimize shipping costs. What kind of model would you use? How would you deal with the uncertainties in demand?
- Try developing a Spreadsheet-based Model-Driven DSS. For example, try the product mix example problem at URL http://www.frontsys.com/ prodmix.htm. Also, you could try to implement the Break-Even DSS in Figure 9.1.
- Visit Harvey Greenberg's Mathematical Programming glossary at URL http://www.cudenver.edu/~hgreenbe/glossary/intro.html and Michael Trick's Operations Research Page at URL http://mat.gsia.cmu.edu/index.html.
- Complete the ithink case study and tutorial at http://www.hps-inc.com/bus_solu/fable/fable1.htm. XYZ Manufacturing is having problems. They’ve been working overtime for weeks, but can never seem to catch up. To add insult to injury, their bottom line has been suffering—with expenses running substantially above normal. Use ithink to develop a map of what might be causing the overtime problem.
- Visit the Colorado River Decision Support System project web page at http://cdss.state.co.us/overview/bigoverview/crdsscov.html. The DSS is a simulation model of the Colorado River Basin that evaluates river and reservoir operations throughout the Colorado River System.
You are working with an entrepreneur who has developed a new product and is seeking venture capital to go into immediate production. She wants you to develop a spreadsheet application. She has orders for 100,000 units in the first year at a selling price of $6.00 per unit. According to her most likely scenario, both numbers are expected to increase 20% annually. She is able to rent a production facility for $50,000 a year for five years. The variable manufacturing cost is $1.50 per unit and is projected to increase at 10 percent a year. Administration costs another $25,000 a year and will likely increase at 5 percent annually. Develop a five-year financial forecast similar to that in Figure 9.7 showing profits before and after taxes. You should assume a tax rate of 36 percent.
Figure 9.7 A Pro Forma Spreadsheet DSS.
Create 4 additional scenarios for this situation - Scenario 1 is rapid increase in sales (30% per year) and slow increase in variable costs (5% per year); Scenario 2 is rapid increase in sales (30% per year) and rapid increase in variable costs (15% per year); Scenario 3 is slow increase in sales (10% per year) and slow increase in variable costs (5% per year); Scenario 4 is slow increase in sales (10% per year) and rapid increase in variable costs (15% per year).
Your worksheet should be completely flexible and capable of accommodating a change in any of the initial conditions or projected rates of increase, without having to edit or recopy any of the formulas. One way to meet this design goal is to create a table of the assumptions about initial conditions and rates of increase, and then reference the table cells as absolute references when building formulas.