DSc 4240/8240 Midterm Exam

1. What is the role of models in supporting organizational decision-making? Draw your ideas from the following Overview of Decision Sciences, Why Model?, and MB-DSS : Modeling Uncertainty and Complexity.

What are models? (short definition)

· A model is a representation, abstraction, simulation of a phenomenon that we are trying to understand. The idea of the model is duplicate a certain situation. By duplicating a certain situation, decision makers can simulate different scenarios to find out how decisions may affect the company. The better the model duplicates the phenomenon the better decisions mangers will make. I believe it is virtually impossible to exactly duplicate large and difficult business markets for particular industries. It would difficult to model human behavior, although one can establish probabilities of decision from other companies by the decision made in the past.

Why is modeling necessary and can be of value? (list points)

· Human memory is limited and can only process a few issues at a time. A computer model is of value because the computer has unlimited number of issues that can be processed at one time. This allows for greater information to processed and reduce the time required to make decisions.

· Computer models have the ability to track several variables at a time. The value of tracking several variables allows managers to make what if scenario decisions by adjusting many variables at a time. Whereas a non-computer model, one can only track a few variables at a time consuming lots of time.

· Models greatly reduce the effort and man power required to make decisions. The value here is a little more obvious since a reduction of man power directly reduces cost to the company.

· Models generate outcomes for decision makers. The value of generating various outcomes allows managers to make better informed decisions. If the model is built properly, decision should greatly benefit the success of the company.

· Reduces complexity. Reduced complexity allows managers to grasp the situation or problem more clearly leading to better decisions.

· Test assumptions. Testing assumptions allows managers to change the variables to find how certain decisions will affect the outcome.

Where are they relevant? (a picture of an organization structure with functions outlined)

· Models are relevant for organizations to tie all of the different functions so that the model will take into account on how each function can and will affect other functions. The marketing department may model a high demand. The manufacturing department must then decide if they can produce the quantity need at a low enough cost to earn at least the companies cost of capital. Once manufacturing determines that it can meet current demand, (if not, manufacturing goes back to marketing for adjusted results), finance will determine if the proposed manufacturing has the capital required to produce the products. Finance will investigate to determine if this level of manufacturing and sales maximizes shareholder wealth. Finance will also determine if the current estimates will exceed the cost of capital. Finance will also determine the funds required to meet current demands on the company. This is a primary example of how a model must tie all functions of an organization together. As all functions of the organization are a team and all players (functions), must be on the same page implementing the strategy of the company. The model will enhance the decision making of the organization in its entirety.

What are some modeling techniques? (a short note on optimization, forecasting, decision analysis, etc.)

· Optimization – This technique allows a company to maximize its resources. By using excel spreadsheets, variables can be enter and based on given requirements, the model will determine the optimal level for the company to operate at. By operating a company at it optimal level, shareholder value will be maximed as well as maximizing the stability of the company.

· Forecasting – This technique allows a company or organization to plan a strategy for the future. A demand model will allow the company to determine the level of manufacturing capacity that will be required in the future. By forecasting or planning for the future correctly, the company will be able to maximize it shareholder wealth. A company who forecast incorrectly or fail to forecast will find that its potential market share will decline.

· Decision Analysis – This technique allows the decision maker to vary the input variables in order to find which combination of variables that maximizes company value. This analysis will provide insight to variables that management need to concentrate on. For example, if the cost of capital is the most sensitive variable in determine company value, management can concentrate on improve their cost of capital or maintaining status quo. By knowing the variables hat are most important, management will not waste pervious time on variables that have little affect on company value.

How are models developed? (stages of modeling)

1. Define the problem

2. Observe the System and Collect Data

3. Formulate a Model

4. Verify the Model and Use the Model for Predication

5. Select a Suitable alternative

6. Present the Results of the Study to the Organization

7. Implement and Evaluate Recommendations

How are the models utilized? (what if, goal-seeking, simulation, scenario analysis, auditing, etc.)

· Models can be used for several reasons. I can see a company using models to improve the probability that their decisions will be successful or that decisions will meet the expectations of the decision makers. Another key issue for models, that is mentioned seldom, is the ability to vary competitor’s decisions to find the impact to your company.

· Use of the models allows the user to vary the inputs or variables to find the outcome. The user can use goal seeking in a model to determine what actions are necessary to achieve a certain outcome. A user can vary the variables (what-if) to determine the possibilities of different decisions. Models are also used to determine which type of decision (scenario analysis) has the biggest impact on the outcome. By changing one variable at a time, the user can determine which variables impacts the outcome the greatest.

2. Describe the role of model management and analytical modeling in Enterprise Applications of Information Technology. Draw your ideas from the following: Putting it all together: Decision Sciences in Context, and Model Management.

Describe in two short paragraphs (or lists) what model management and enterprise DSS mean to you.

· For me, the idea of model management and enterprise DSS is the ability of the company to forecast its demand and its competitor’s demand. By competitors, I am looking at watching and forecasting the possibilities of your competitors and how their decisions will affect the demand for our firm. The model will also allow the company to implement its strategy. If the strategy of the company if for high growth and market share, a well designed and effect model will enable the company to institute a production and price strategy to meet its goals.

· The model also allows the company to create a tool in which all of the functions of the company are tied together to determine a pro-forma cash flow statement. By creating an effective model, the company can see how each function plays it role in the company and how it will affect other functions in the company. The company who create the most effective and most through model will create a competitive advantage over its competitors thereby creating additional shareholder value for its equity holders.

Develop a framework integrating key concepts of IT, organizational functions, and analytical modeling support.

3. Using the SIMQ DSS as a prototype of an enterprise-wide DSS, design a framework for integrating the models across the functional areas of business. Use A model for Enterprise-Wide DSS.

Enterprise DSS

The purpose of this report is to describe the workings of several spreadsheets and propose a new format that would be more efficient and user-friendly. These spreadsheets are used in a business to determine production requirements, estimate sales, and create proforma financial statements. After describing the spreadsheets, a proposal for a new user-friendly interface will be introduced.

Startup Spreadsheet (1Startup.wk4)

This spreadsheet is used as the data source for the other three spreadsheets. The main data is imported into this spreadsheet and linked to the others through formulas. The first thing a user must do is specify which disk drive the data is imported from. Then the data is imported and available for use. Models for demand forecasting are also set up in this spreadsheet for use in the sales spreadsheet.

What major decisions are made in this functional area?

No real decisions are made in this spreadsheet; it is the basis for all other functional areas to use for data.

What inputs and what outcomes are required to help decision making?

Inputs in this spreadsheet are data from competitors such as information on price, demand, sales, materials, wages, and productivity. Also, seasonal indices are imported along with other numbers that may be used in demand forecasting. No outcomes are found in this spreadsheet.

What kind of analyses could one do?

This spreadsheet does not actually perform the analysis; it is used in other spreadsheets to perform analyses, which will be discussed later.

Potential or recommended improvements?

1. Change the buttons on the ‘DSS Source’ spreadsheet to say ‘Download from Data Warehouse’ instead of drive ‘A’ or ‘B’.

2. More clearly label the data items in the ‘Update’ and ‘Models’ spreadsheet so that a user can tell what all the information is for. Right now, a user cannot tell what a lot of information is, such as the information under Product X on the ‘Update’ spreadsheet.

The format of this spreadsheet is not critical considering the user won’t be using it to perform analyses. As long as the spreadsheet imports the correct data, it doesn’t need a lot of designing.

Sales Spreadsheet (Sales.wk4)

This spreadsheet is used to estimate Sales for each product being analyzed. It can be used to run what-if analyses to decide what level of sales is necessary to keep the business in profit.

What major decisions are made in this functional area?

The major decisions made in this spreadsheet are price levels, advertising expenditures, research and development expenditures, and whether there is enough capacity to handle the estimated sales.

What inputs and what outcomes are required to help decision-making?

Inputs include historical sales levels, historical expenditures on items that affect sales, competitor information, and regression coefficients used in calculating demand. Outputs are capacity availability, demand forecasts, marketing profit, market share, units to manufacture, and marketing costs.

What kind of analyses could one do?

What-if analyses of demand at different levels of price, advertising expenditures, research and development expenditures, capacity levels, and competitor actions.

Potential or recommended improvements?

The only part of this spreadsheet that most users would look at is the part that is boxed in. It would make sense to make this the main user interface, and have all the other information on a separate spreadsheet or even hidden from view.

Production Requirements Spreadsheet (Prodreq.wk4)

This spreadsheet is used to estimate Production requirements for different levels of Sales. It can be used to run what-if analyses to decide what level of production is necessary and most cost efficient for an estimated amount of sales and inventory levels. These changes can include more workers, more overtime, or a combination of the two.

What major decisions are made in this functional area?

Major decisions in this functional area include production levels, staffing levels, overtime allowed, and how much to ship at one time.

What inputs and what outcomes are required to help decision-making?

Inputs required are estimated sales, ending inventory, plant capacity, average employment, average wages, and the percentage of overtime allowed. Outcomes include costs of production, number of employees required to complete work, costs of labor and overtime, and shipping costs.

What kind of analyses could one do?

What-if analyses on level of production necessary for given amount of sales and inventory. The most cost effective method of staffing can be found with this spreadsheet through manipulating number of employees and overtime levels. Also, the most cost effective shipping plan can be found with this spreadsheet.

Potential or recommended improvements?

The biggest improvement that could be made to this spreadsheet is hiding the unnecessary information that the user is unlikely to need.

Proforma Financial Statements Spreadsheet (Proforma.wk4)

This sheet allows the user to change the different variables per product to determine the impact that the changes in variables will have on the financial outlook of the company. By changing, the variables or drivers, financial managers can determine the level of cash flow necessary to maintain company stability and to implement its current strategy.

What major decisions are made in this functional area?

The Proforma spreadsheet aids in the financial decision-making at a mid to senior management level. Different scenarios can be analyzed using fluctuations in the cost of goods sold. This sheet allows the user to change the different variables per product to determine the impact that the changes in variables will have on the financial outlook of the company. By changing, the variables or drivers, financial managers can determine the level of cash flow necessary to maintain company stability and to implement its current strategy. Additional criteria include the capital structure and different capital budgeting options including costs for commercial paper, debt, dividends, and expected collection rate.

What inputs and what outcomes are required to help decision-making?

The inputs in this sheet include:

· Price changes for products

· Advertising budgets per product

· Research and development per product

· Other operational expenses not directly related to production

· Interest payable on bonds and/or commercial paper

· Investments rates

· Any distress cost related to operations

· Dividends payable

· Expected accounts receivable rate

· Hiring and training employee expenses

· Personnel department fixed expenses

· Expected downtime if any

· Expected sales per product line

The outputs for this sheet are related to the balance sheet, income statement, or cash flow for the company based on projections entered in the input area. The proforma outputs create in this sheet allow managers to analyze how different variables will impact the financial outcome of possible decisions.

The outputs include:

· Sales Revenue

· Gross Margin

· Net Income

· Ending Cash Balance

· Excess Cash

What kind of analyses could one do?

A manager could change variables that are within the control of the company in the near future. For example, the company could easily increase or decrease the advertising budget for the company without large delays, but the company would additional time to hire and train new employees if they wish to exceed capacity beyond its current production capacity. The hiring of new employees will not have immediate impact in the next couple of financial quarters except for overhead cost.

By changing the variables, the company can determine its internal profitability rate and cost of capital. By obtaining its profitability rate and cost of capital, management can determine future investment strategies in its attempt to increase shareholder value. The main idea would be to insure that the profitability rate exceeds the cost of capital where value for the shareholder can be created. The scenarios analysis will help management focus on which variables have the biggest impact, so they can implement or maintain its strategy.

Potential or recommended improvements?

The proforma sheet needs to reduce the number of variables available to the user. The user of this would benefit by having inputs that would automatically change based on changes in one variable. For example, if the user changes the price of one product, changes in advertising, R&D and production would be changed automatically to meet the new demand at the entered sales price. By allowing the decision maker the ability to change all the variables, conflicts will be created between the input variables. For example, an increase in price may reduce advertising required at the new sales price, whereas the manager may increase price and increase advertising simultaneously which would most likely create conflict leading to incorrect decisions. The idea would be for this decision maker to make changes that his or her knowledge based will allow and leaving other variables to those functions based on their expertise. The changes created by the decision maker will allow that individual the opportunity to test his/her changes with each functionality to determine if his/her changes are achievable.

Proposed System Architecture (Interfaces.xls)

The proposed interfaces for each spreadsheet, Sales, Prodreq, and Proforma are in this file.

SALES AND MARGIN

	X	Y	Z
Estimated Competitor Behavior in This Quarter				Quarter	30
Avg Price	$364.03	$479.90	$306.77
Avg Advertising	$113,636	$91,270	$72,265
Avg R & D	$21,100	$8,500	$59,200

Potential Firm Decisions:				TOTAL
Price	$362.50	$475.50	$306.50
Advertising	$113,636	$87,000	$68,000	$268,636
R & D	$21,000	$8,500	$68,000	$97,500
Capacity		Too little capacity!		14,000

Average Demand	4,809	3,982	4,312	13,103
Normalized Mkt Share	1.069	1.133	1.103
Firm Demand - Share	10.69%	11.33%	11.03%
Beginning Inventory	85	0	194	279
Desired Ending Inv.	0	0	0

Firm Demand - Units	5,142	4,511	4,755	14,408
Units to Mfg	5,057	4,511	4,561	14,129
Dollar Sales	$1,863,975	$2,144,981	$1,457,408	$5,466,363
Marketing Cost	$616,255	$735,603	$420,377	$1,772,234
Marketing Profit	$1,247,720	$1,409,377	$1,037,031	$3,694,129

4. Describe the process of model development, implementation (DSS design) , and use of models (sensitivity, goal-seeking, simulation, etc) . Use The Modeling Process and MB-DSS : Modeling Uncertainty and Complexity..

Using the model for estimating demand as an example, describe the use of time-series analysis and regression analysis to develop forecasting models.

· Time series analysis allows a company to take historical data from time periods that could be monthly quarterly or yearly results and project into the future what the trend of the demand will be. Using software like CB predictor, seasonality can be determined for particular periods, which will highlight the high and low demand periods. Regression analysis will allow companies to determine which input variables help explain the results. By knowing which input variables affect demand the greatest, companies can concentrate on those particular input variables to improve their position in the market place. To determine which variables are significant, historical data is necessary. Using an excel spreadsheet or something that will run multiple regression, the demand for the industry would be the dependent variables and input variables such as adverting, research, and development as the independent variables. Once the regression is run, coefficients that meet the 0.05 significant level will explain a portion of the results. The amount of variation being explained will be shown in the adjusted R-squared value

Describe the process of implementing these forecasting models in a spreadsheet model (DSS). Clearly outline the different components (database, modelbase, and the interface.

· Database – the database will be a collection of all the historical data including output and input variables. These variables will be used to find significant trends or other important information within them. Regression and time series analysis will be used to find the important significant trends.

· Model Base – the model base includes the output that is produced by the regression and trend analysis functions. The model base will also include all forecasting calculated for future periods to help in determine future outputs.

· Interface – the interface allows the user to change the input variables to determine the desired outcome. The important input variables are determined by trend and regression analysis. The regression analysis determines which variables explain a significant portion of the outcome. The interface will be tied to the model base and the database. By tying the interface with the other components, the outcomes will be automatically computed. The automatic computations allow the user to change the variables and quickly receive the outputs. In the past, every time an input was changed, the outputs would have to calculate by hand. Now that computers are available, the calculations are done within microseconds. This allows the user to change a number of the variables to find the variables that have the most input.<o:p></o:p></SPAN>

How can such a model be used to perform various analyses. Define and provide examples of sensitivity analysis, scenario analysis, and goal-seeking analysis.

· An effective model can be used to perform sensitivity analysis. The user can change one variable at a time, determining which variable or variables that have the greatest impact. By determining which variables have the greatest impact, the company can concentrate on those variables. By knowing which variables are significant, the company’s resources can be put to use in the most effective manner.

· If a company is seeking to perform at a certain level, they can use the model to goal seek. By creating an effective model, a goal seeking function will inform the company on how each input should be treated to reach such a goal. For example, if a company wishes to sell 10,000 units of a particular product, a goal seeking function will inform the company of how much to charge and how much advertising is necessary