Modeling and Analysis
This course introduces modeling and managerial uses of computers. Three themes are developed:
- the building, using, and interpretation of computer-based models which aid managers in making decisions under conditions of both uncertainty and certainty;
- the analysis and interpretation of empirical data for use in computer-based models;
- organizational issues in the implementation of decision support systems combining data and models.
Initially, the course will be devoted to hands-on use of software tools to produce models which support individual decision making. Spreadsheet and simulation models, including decision making under uncertainty and optimization, will be the primary vehicles for exploring the value of these approaches to decision support. Analysis of empirical data will follow using models of data analysis from statistics. Included are the statistical tools of sampling, statistical testing procedures, and multivariate regression analysis. Emphasis will be on the usefulness of these methods to the manager. Later, the course will focus upon integrating tools and data to support organizational decision making that transcend the scope of any one decision maker.
Students are required to have prior knowledge of the Excel spreadsheet package and will acquire hands-on experience using Excel and simulation software for decision support.
REQUIRED TEXT
Decision Modeling with Microsoft Excel, by Moore and Weatherford, Prentice Hall (2001)
WEEK 1
Class #1
Prepare: Ebel Mining B (page 202 in DMME) Modify the network flow diagram for Ebel Mining to include the option of reclassifying a higher grade ore and shipping it to a lower grade ore customer. Use it to formulate a revised model for Ebel. Solve it and interpret the results managerially.
Read: Chapter 4, Sections 4.1 to 4.8
Prepare: Advanced Semiconductor Devices (page 212 in DMME). Formulate the model, solve it and attempt to answer as many questions as you can from the Sensitivity Report alone.
WEEK 2
Class #2
Read: Chapter 5, Sections 5.1 to 5.8
Prepare: Problem 5-18
Read: Ebel Mining C and D (page 282-283 in DMME) Think about how would you modify the Ebel B model to include use of the blender in addition to reclassification of ore done in Ebel B case? How would you modify the Ebel model to make it dynamic, as discussed in Ebel Mining D? We will cover the model formulations in class.
Hand-In: Sherman Motors. (syllabus) Answer the president's questions at the end of the Sherman Motors case via Solver. This is a group assignment; one report per group consisting of a 1 page memo with recommendations (no need to verbally summarize the case nor the model) followed by your Excel model with Solver report(s) and a printout of your Solver dialog and model formulas.
Class #3
Read: Chapter 6, Sections 6.1 to 6.8
Prepare: Problem 6-14
Read: Goforit Investments (syllabus) and Franchise Foods (syllabus). We will cover these in class.
Skim: DMME, Chapter 7, Sections 7.1, 7.3, 7.4, and 7.8
Read: Problem 7-45. We will cover this problem in class.
Optional Read: For those of you interested in applications of optimization to Finance, Sections 7.9 and 7.10 cover the theory of portfolio optimization, while Section 7.11 shows how portfolio optimization from historical data is done and develops the origin of the "efficient frontier." These are concepts you will cover in your Winter Quarter Finance course.
WEEK 3
Class #4
The next two sessions focus upon discrete event simulation in Extend, and also begins coverage of data analysis with some readings.
Read: DMME, Chapter 10, Sections 10.1 to 10.5, and Extend Version 4 Manual (syllabus), pages 3 to 14. (Extend LT is available on DMME's CD-ROM for personal installation and is installed on all Sloan computers.) You might find it time saving to download and play the Screen Cam tutorial, Discevnt.scm, also on the T269 Web page before doing the readings. It introduces Extend, but without audio commentary. The Screen Cam player application (SCPlayer.exe) is still available under PreEnrollment class files.
Prepare: Problems 10-2 and 10-4 in DMME on page 568. (The Hervis1.mox file is on DMME's CD-ROM and is also on the T269 Web page.)
Read: "Summarization of Data," pages 1-12, in syllabus
Hand-in: In a one page memo to your boss propose a useful application of optimization modeling for your organization. Describe the setting (one paragraph). Briefly and verbally (no formal variables nor algebraic formulations) describe: (a) a candidate performance measure to be optimized, (b) the decision variables, and (c) the constraints. Is it an operational model (for actual day-to-day use in operations) or an aggregate planning model (yielding insights and recommendations for decision support)? Is it a static (one time period) or a dynamic (multiple time periods) model? If a dynamic model, what is the time period (weekly, quarterly, etc.) and the planning horizon? Given this proposal were to be approved as a development project by your boss, what next steps would you take? This is an individual assignment.
Class #5
Prepare: Problem 10-3 and 10-5 in DMME on page 568. (The Hervis1b.mox file will be on the T269 Web page.)
Read: DMME, Chapter 10, Sections 10.6 and 10.7, and Extend Version 4 Manual (syllabus), pages 15 to 26. Note: The balance of the Extend Version 4 Manual (syllabus) gives detailed documentation of Extend blocks for future reference.
Read: Problem 10-11 in DMME on page 569. Think about how you would model this more complex situation. We will cover it in class. (The Hervis3.mox file will be on the T269 Web page. If you attempt to modify the model yourself, you will need another connector coming out of the Rental Office block for the Gold customers. To create one, Alt-Click the Rental Station hierarchical block to obtain a more detailed definition window. The toolbar will display more icons, including an "Item Connector" tool. Click it to change the cursor and click the edge of the hierarchical block to create another named connector in the Rental Station portion of the model. You can then rename it and draw connector lines to the named box as appropriate.)
Read: "The Normal Distribution," pages 1 to the top of 7, in syllabus (There is a typo at the top of page 6: NORMSDIST(0.75) = .7734, not the .5734 that is given.)
Optional: Answer problems 1, 2 and 3 at the end of "The Normal Distribution," in syllabus We will not cover these problems in class, but the answers are given. (Caution: do not peek at the answers in advance, else you will learn very little.)
WEEK 4
Class #6
Prepare It is estimated that the weekly demand for regular gasoline for the filling station across the street from the Schwab Residential Center will be Normally distributed, with an average of 10,000 gallons and a standard deviation of 500 gallons. The station will be supplied with gasoline once a week. (1) What must the capacity of its regular gasoline storage tank be if the probability that its supply will be exhausted in a given week is to be no more than 10%? (2) What must the capacity of its regular gasoline storage tank be if the probability that its supply will be exhausted in a given week is to be no more than 1%? (3) Its owner, Bill Jones, says "Under no circumstances do I ever want to lose business because I have run out of regular gasoline. We have plenty of land around the gas station to bury a large tank." How big would the regular gasoline tank have to be to guarantee that Bill never runs out of regular gasoline in any given week?
Read: "Simple Regression and Correlation," pages 1 to top of page 10, in syllabus
Prepare: Analyze the data from the Sloanie Sales Co. (A copy of the data is called compensa.xls on the T269 Web Page in the "Data Sets" folder.) The data set gives the total compensation (salary plus sales commission income in $000's), the number of years with the company, and the percentage of sales quota met last year for the 16 sales persons employed by the Sloanie Sales Co.
1. Use the Chart Wizard to draw two scatter plots of Compensation verses the person's seniority (Years of Experience) and Compensation verses Quota_%.
2. Use Excel's Trendline command (covered in Chapter 2 of DMME) to create a linear curve-fit model of Compensation predicted by Years (of Experience).
3. Fit a second linear curve-fit model of Compensation predicted by Quota_%.
4. Which of the two models is the best predictor of Compensation? How do you know this?
Hand-in: In a one page memo to the managing director of California Cooperative Bank, recommend staffing levels to achieve the customer performance goals for tellers and managers discussed in the California Cooperative Bank case in DMME, page 576. Attach your Extend model and relevant results from your Extend simulation analysis. This is an individual assignment.
Class #7
Read: "Introduction to Statistical Inference," pages 1 - 19, in syllabus
Read: The following problem. (We will cover it in class)
1. By law a food product must list FDA estimates of the contents of a packaged product. Suppose the FDA wants to estimate the mean sugar content by weight in 16 ounce boxes of corn flakes. The FDA randomly selects 100 boxes of corn flakes and measures sugar content of each, yielding a sample mean of 3.2 ounces with a sample standard deviation of .5 ounce.
a. Estimate the population mean sugar content in 16 ounce boxes of corn flakes with a 90% confidence interval and interpret the interval managerially.
b. How could the FDA reduce the confidence interval in a. and what, if any, drawbacks are there to reducing interval length?
WEEK 5
Class #8
Read: The following two problems. (We will cover them in class)
1. The new Dean, Bob Joss, believes the GSB should be run more like a business, and thus, has decided that Schwab residents should pay market rates for their room rent. To assess this change he is interested in monthly rental rates of deluxe one bedroom, one bath apartments in the Stanford area. He knows the managers of six new, upscale Palo Alto apartment complexes, and a phone call to each of them revealed the following monthly rental rates for their one-bedroom, one-bath units: $1540 $1605 $1460 $1700 $1580 $1590
a. Construct a 90% confidence interval for the population average monthly rental rate of a deluxe one-bedroom, one-bath apartment in the Palo Alto area and interpret the interval managerially.
b. What assumptions are required for the validity of the procedure you used in a.?
2. "A sample of 40 from a population of 400,000 will give nearly as precise an estimate of the population mean as a sample of 40 from a population of 4,000, provided the standard deviations of the populations are the same." Is this statement reasonable? Why or why not?
Hand-In: Gotham City Motors (syllabus) Use Excel to calculate (1) the overall average of maintenance costs for the 15 cars, (2) the average maintenance cost for new, 1, and 2 year old cars, and (3) the average maintenance cost for each of the two makes. Use Solver to fit two least squares linear functions to the data, one for each make, that predicts maintenance cost as a function of miles driven and age. (Note: You must use Solver's non-linear optimization option because of the sum of squared errors to be minimized.) Interpret the coefficients of the two linear models. Finally, code Make A as a "0" and Make B as a "1" and fit a least squares model that predicts maintenance cost as a function of three variables simultaneously: age, miles driven and make. Interpret this model for forecasting maintenance cost. How do you interpret the coefficient on the "make" variable? Hand in a 1 page summary memo to the manager containing your recommendations plus any supporting exhibits you feel are helpful to him. This is a group project: one report per group. (The Gotham data set is on the T269 Web Page in the "Data Sets" folder.)
Class #9
Read: "Simple Regression and Correlation," pages 10-18, in syllabus. (Ignore details of formulas for computing standard errors; we will use Regress for such computations.)
Prepare: "Regress Users Guide," pp. 1-27 (in syllabus) Work through the tutorial at a computer. Then run several regressions with Regress on your compensa.xls and gotham.xls data to compare the Regress results with your earlier Solver regressions.
Prepare: The data set gas.xls is on the T269 Web Page in the Data Sets folder and describes temperature and natural gas consumption in a city for 42 days. Draw a scatter plot of Gas consumption verses Temperature. Do you see any pattern in the data? Use Regress to fit a regression model to predict natural gas usage as a linear function of temperature. Is the regression line a good fit to the data? How do you know? Have Regress produce the descriptive statistics for each variable (mean, std. deviation, minimum, maximum). Does the regression line go through the mean of the temperatures and/or the mean of the gas consumption's? Do your residuals suggest that the underlying assumptions of regression modeling for inference are met? How do you know? Do you detect any outliers in the data? How do you know? Does the slope coefficient differ significantly from zero? Explain. What is your point forecast for the city's natural gas consumption if the temperature is 50 degrees? What would you forecast as the 95% confidence interval for the city's gas consumption at a temperature of 50 degrees? Do the same for a temperature of 25 degrees. Is your confidence interval wider or narrower than for the case of 50 degrees, why or why not? Where is the confidence interval for forecasting narrowest? Does this make sense to you? Why?
WEEK 6
Class #10
Read: "Multiple Regression and Correlation," pages 1-17 in syllabus
Read: "Tests of Hypothesis," pages 1-12 in syllabus
Prepare: A data set (available on the T269 Web page as jobshop.xls) contains data from a job shop manufacturing operation that includes data on 20 jobs for each of the following factors:
HOURS = Total shop hours from order receipt to shipment
PIECES = Quantity of pieces to be built to satisfy the order (ex. 38 widgets were ordered)
OPERS = Number of discrete manufacturing steps or operations required (e.g. milling, grinding, polishing, assembly, testing, etc.)
RUSH = 1 if a rush order, 0 otherwise.
Use Regress to run three regressions to predict number of HOURS. Interpret the results.
Regression # Independent Variable(s)
1 PIECES
2 OPERS
3 OPERS and PIECES
4 A final regression model of your choosing.
Class #11
Read: "Multiple Regression and Correlation," pages 18-24 in syllabus
Read: "Regress Users Guide," pp. 28-48 (in syllabus)
Prepare: The data set WAUTOACC contains the number of highway ACCIDents (in thousands) in the state of Washington, the number of MILES traveled (in billions) and the number of registered VEHICles (in thousands) for the years 1960 through 1974. (Available on the T269 Web page as WAUTOACC.XLS) Use Regress to run three regressions to predict number of ACCIDents. Interpret the results. In particular, look at the significance of the slope coefficients for the third regression. What is going on here?
Regression # Independent Variable(s)
1 MILES
2 VEHIC
3 MILES and VEHIC
Read: "Regression Term Project" in syllabus. Those of you electing to do a regression term project should be pursuing the definition of your term project by now. If you are doing a group project, have your group formed and begin to enter ideas, research hypotheses, etc. into your project diary If it is an optimization or simulation modeling term project, make an appointment to meet with me to review it. If it is a regression project, develop an example data sheet with column headings of variables, and some hypothetical data; it will help crystallize your thinking by forcing you to operationally define your variables. Set up an appointment with me, and use it only to explain your project to me. You should also be developing contacts with the appropriate people who will help supply the data.
WEEK 7
Class #12
Read: "Curvilinear Regression" in syllabus
Skim: "Discriminate Analysis" in syllabus This is for those of you considering use of an indicator variable as the dependent variable in a regression project; contact me for more readings on Logit analysis. Also, make note of the trick with indicator predictor variables to allow fitting a kinked regression line. An example of this is given in the Example of a Kinked Regression Fit in the syllabus. This might be useful for your project.
WEEK 8
Class #13
Hand In (group): Quality Kitchens Meat Loaf (A), in syllabus. Hand in short answers to the first three questions in the case. (Appendix B came from another regression package. Re-run the regression in Regress and use its results. The data set is called QKITCHA.XLS on the T269 Web Page.) This is a group assignment; one report per group.
Prepare: Create a regression model as you see fit to respond to the 4th question in Quality Kitchens (A) case. We will go over some techniques pertaining to the issues raised in the fourth question in class.
T269 Exam: Open Book
Part I of the exam is take home (open book, open notes, and using a computer). Part II will be in class, open book and open notes, no computer.
Class #14
Read: Chapter 7, sections 7.1 - 7.4 and 7.10 - 7.11 in DMME.
Prepare: Problems 7-18, 7-20, 7-25(a) and (b) only. In addition, re-solve the model in problem 7-25 with other values of the minimum expected return (that is, replace the 8% minimum expected return with higher and lower values) so as to generate a plot of the "efficient frontier" analogous to Figure 7-26. What is the largest value you can specify for the minimum and maximum expected return without making the optimization model infeasible? Be prepared to explain how the optimal portfolio composition changes as the minimum expected return varies. Would any of your previous answers change if "short selling" were permitted?
Time permitting, we will also cover the topic of statistical quality control.
WEEK 9
Class #15
Read: Chapter 11 in DMME, pages 11-1 to 11-11
Prepare: Australian Motors (AM) (in syllabus at end of DMME Chapter 11). Focus your attention on the organizational process by which AM attempted to improve fleet operations by use of an optimization model. (We will not dwell on the model's specific formulation. Hence, you need only to skim the model itself as summarized in the Exhibit 8.) Do you detect any problems with the modeling approach to fleet management taken by AM? Would you have approached this decision support system's development for AM fleet management differently and why? How would you have organized and managed the project team?
Special Family Session:
Prepare: Turkey, stuffing, cranberry sauce, and pumpkin pie. Then optimize the following model:
Maximize Calories_Consumed
subject to
Weight_Gain = 10 pounds
Eating_Time = 3 hours.
As usual: interpret the shadow prices!
Then, give thanks for a well placed (and deserved) holiday!
WEEK 10
Class #16
This class is the first of two on Data Mining.
Prepare: "Introduction to Pivot Tables" (to be handed out)
Prepare: "Books by Mail." (to be handed out) Use any of the techniques we have covered in T269, including possibly Pivot Tables, to recommend which customers in the database should receive the mail solicitation for the proposed book, and why.
Class #17
Guest Speaker: Professor Charles Bonini.
Read: Sea Land Case (to be handed out)
WEEK 11
Class #18
Prepare: "Re-Engineering," (a classic article on the subject, in syllabus) What is the key message of Hammer and the other re-engineering prophets? Why does re-engineering receive so much criticism? Are their ideas aging as the popularity of re-engineering has faded? Was re-engineering a fad?
Class #19
Course Review. Be prepared to discuss the lessons and concepts covered in T269.
