Innovative Education
Executive Education Opportunities
Millions of analysts need training in spreadsheet modeling, optimization, Monte Carlo simulation and data analysis
By Wayne L. Winston
During the past five years I have trained nearly a thousand analysts (mostly financial analysts) at major corporations such as Microsoft, Cinergy, Intel, Cisco, GM, Ford, Eli Lilly, Pfizer, NCR, Arthur Andersen, U.S. Army and PricewaterhouseCoopers. The bulk of my work has been at Microsoft, but the classes have been well received at virtually all the companies listed above. I believe the training has greatly improved the quality of the work these analysts perform.
I would like to share with you my views on training analysts (be it financial, marketing or operations analysts). Assuming the average FORTUNE 500 firm has 2,000 "analysts" (my guess is that this estimate is low), that means at least one million workers are reasonable candidates for the training I will describe. At companies such as Pfizer, Eli Lilly and Microsoft, scientists and engineers are also reasonable candidates for these courses. For example, a reliability engineer at Microsoft took one of my classes and was amazed to find out that key formulas involving the exponential, gamma and Weibull random variables were included in Excel.
I start with the hypothesis that analysts do upwards of 90 percent of their "analysis" in Excel. This assumption has been borne out in all my classes. Therefore, all my training is spreadsheet-based. This is not to say that training in a modeling language (such as LINGO) would not be beneficial. I have just not been asked to do this type of training. I believe strongly that a nice follow-up course to the training I describe would include training in a modeling language and a process simulation language (such as ARENA, SIMAN, SLAM or SIGMA). This type of training is probably suitable for only a small percentage of practicing analysts.
Goals for Analyst Training
When training analysts, I believe there are four important types of skills ORMS professors can impart:
- Teach analysts how to be more efficient and do their day-to-day work better using Excel. As an example, I show analysts how double-clicking on a cell will copy a formula down 50,000 rows! This saves them a lot of time and probably prevents several cases of Carpal Tunnel Syndrome!
- Teach basic tools of business modeling that can be used to help an analyst make better decisions. High on this list would be sensitivity analysis and optimization.
- Teach basic data analysis and how Excel statistical functions and features such as Trend Curve can be used to facilitate day-to-day analysis and decision-making. Many analysts have even forgotten the definition of a standard deviation.
- Teach analysts important recent developments in business theory through carefully constructed examples. As an example, we include examples concerning customer valuation, real options, price bundling and predicting diffusion of new technologies (S-shaped curve and Bass models).
Course Descriptions
At Microsoft, we have developed an entire "quantitative modeling" curriculum (see Figure 1). We begin with two, four-hour "required or core" courses: Business Modeling and Data Analysis. Business Modeling covers topics such as GOAL SEEK, the AUDITING tool, DATA TABLES, SOLVER, an introduction to Monte Carlo simulation, and important EXCEL functions such as COUNTIF, SUMIF and TEXT functions. The Data Analysis course covers two main topics: how to describe and summarize data (with descriptive statistics, conditional formatting and pivot tables) and how to model relationships between variables (Using TREND CURVES and regression).
| CURRENT OFFERINGS | ||
| Data Analysis | Business Modeling | Advanced Business Modeling |
| Prereq: Intermediate Excel or equivalent experience | Prereq: Intermediate Excel or equivalent experience | Prereq: Business Modeling |
|
|
|
| FUTURE OFFERINGS (based on demand) | ||
| Advanced Solver | Advanced Data Analysis, Forcasting & Regression | Monte Carlo Sim w/Excel Add-in @RISK (8 hours) |
| Prereq: Business Modeling | Prereq: Data Analysis | Prereq: Business Modeling |
|
|
|
Figure 1: Excel 2000 — Analyysis & Modeling Tools Series
After taking both these courses students are ready for the "Elective Curriculum." The Elective Curriculum includes Advanced Business modeling (more stuff on SOLVER and Monte Carlo simulation), Advanced Data Analysis (more on regression, ANOVA, smoothing methods and data mining), Advanced Solver (introduction to Premium Solver including genetic algorithms), Real Options (basic introduction to options, viewing capital investments as options, using simulation and binomial trees to value real options), Monte Carlo Simulation (introduction to simulating profitability of products and valuing companies) using @RISK and Conjoint Analysis (how to determine the factors which make people purchase products). At other companies I am usually called in to simply do a one-day course on a topic of interest. For example, INTEL wants a one-day course on SOLVER and GM, and Pfizer want one-day courses on Monte Carlo analysis.
At Microsoft I have also done customized "labs" for different groups. For example, I showed Operations Finance how to forecast travel expenses and improve the techniques they used to analyze cost variances.
Keys to Success
As Steven Covey points out, "If you give a person fish for dinner, they eat dinner today. If you teach them to fish, they eat for a lifetime." The goal of my training is to give analysts a wide variety of techniques that can be used to help them to better analyze day-to-day data and build models that will lead to better decisions. I think the students appreciate that they can use the skills we impart throughout their business career.
A key is to teach by (relevant) example. When at all possible, I use problems and/or data that are specific to the company I am training. For example, when teaching Monte Carlo simulation at GM, we use their template for simulating cash flows for a new car. At Microsoft, we try and predict OFFICE sales in different countries from demographic data. This model enables us to determine how effective the sales force has been in different countries. We also taught a model in class that we developed for Microsoft to help predict technical support calls for WINDOWS 98.
Combining relevant examples with the power of EXCEL makes concepts come alive. A marketing professor may pontificate about the importance of customer value, but unless he shows the student the nuts and bolts of computing customer value in a specific situation, the concept is virtually useless.
It is crucial to be dynamic and make the class fun. To humanize the course I ask students trivia questions during breaks in the lecture. We introduce nonlinear SOLVER models by using SOLVER to rate professional football teams. We show students how to simulate the NCAA tournament or NBA playoffs. It is also important to create an atmosphere in which students feel comfortable asking questions. It is also important to quickly respond to questions. Your examples should be constructed so it is easy to go to the student's machine and find his error. I ask more trivia questions when I am looking for a student's error.
Many of you might think a lot of analysts know data tables, SOLVER, etc. This is definitely not the case. As an example, I would guess that less than 10 percent of all the people I have trained knew what a data table was. It is important to note that analysts who are at least 35 years old probably were not exposed to EXCEL modeling in school. They risk becoming obsolete if they do not receive the training we have discussed.
Before you go to a company, pull up all recent articles on the company from publications such as Forbes, Fortune, Business Week, Industry Standard and Economist. I strongly believe it is difficult to be a good business school professor if you do not regularly read each of these magazines. I find that during each class a morsel from my reading (I never know what!) that is relevant to the topic I am teaching will suddenly pop into my mind and make the class seem more relevant and current.
Finally, I strongly believe that the ORMS professor is best suited to deliver this type of training. In most schools only we have the competence in EXCEL, modeling, data analysis and new business ideas to pull this off.
Course Setting
In most cases we train in a "training room" containing 16 to 24 PCs. At Cisco Systems we allowed 50 people in the class, but different learning speeds make it difficult to handle more than 30 people. Before class we give each student files including "templates" and final versions of each example. During class the students complete each template along with my screen and raise their hand if they do not match mine. It is critical to have a projector that makes the image of the instructor's computer clearly visible to students sitting in the back row. It is also important to have lights that can easily be dimmed by the instructor. At Microsoft we have bagels in the morning, lots of caffeine, catered lunches and cookies in the afternoon. This keeps us going through long nine-hour days!
How to 'Sell' the Need for Training
How can you convince companies in your area that they need the sort of training I have described? One way to start is to do a small project (free of charge) for a company. For example, develop a forecasting model for a major product. Give a little presentation that weaves in cool EXCEL features like data tables and conditional formatting. In most cases, the executives will see the value of teaching all their analysts how to conduct such "valuable" analyses. Another way is to ask a corporate executive whether their analysts could perform the following types of analyses:
- How can you spot and display outliers in data?
- What do you learn from outliers?
- How can we forecast various expenses and income for budgeting?
- How can we forecast sales of a new product?
- Can your employees compute NPV for a project with cash flows on Jan 10, 2002, Feb 12, 2003 and April 10, 2004? (Use XNPV function; it is so cool!)
- When using a moving average forecast, how should periods be included in the moving average? (Use OFFSET function and data tables.)
- In a spreadsheet that records for each sale the date, place, product and salesperson, could you easily compute the amount sold by Joe of your 10 top SKUs on the East Coast during March? (Use DSUM function.)
- Suppose you have data on 10,000 transactions. For each transaction there is a single cell containing Product Description, Product ID and Price. Can you determine the average price charged for each product? (Use text functions and pivot tables.)
- How would you determine the annual growth rate in sales needed to ensure a project generates an NPV of $500 million? (Use GOAL SEEK)
- What product mix should a plant produce during a month? (Use SOLVER)
- Given the tax rates in different countries, shipping and production costs in different countries, where should products be produced to maximize profits? (Use SOLVER).
- What production schedule minimizes number of model changes and ensures a balanced workload? (Use SOLVER and genetic algorithms)
- How would you forecast demand for a highly seasonal product? (Develop a SOLVER model involving trend and seasonality or use Winter's Exponential Smoothing Method)
- How would you estimate the demand curve for a product and come up with the optimal product price? (Use Trend curve to fit demand curve and SOLVER to determine optimal price)
- How would you implement nonlinear pricing strategies such as bundling, quantity discounts and two-part tariffs? (Use INDEX and MATCH function and genetic algorithms)
- When the French franc appreciates by 20 percent, by how much should Eli Lilly change the price of Prozac? (Use SOLVER and basic elasticity concepts)
- How should Proctor and Gamble optimally hedge their foreign exchange risk? (Use Monte Carlo simulation to generate scenarios for exchange rates and genetic algorithms to create the optimal hedge)
- How should Eli Lilly determine the correct capacity level for a new drug? (Use Monte Carlo simulation)
- How can GM estimate the probability that their E.P.S. this quarter will meet Wall Street's target? (Use Monte Carlo simulation)
- GM is thinking of buying a key supplier for $400 million. Is this a good deal? (Use Monte Carlo simulation)
- How can GM estimate the probability that development costs on a project during next two years will exceed $2 billion? (Use Monte Carlo simulation)
- Why did Microsoft buy WEB TV when the NPV of the company's future cash flows was negative? (Use real option theory)
- When planning for your retirement, what should the optimal allocation of your money be between bonds, value stocks and technology stocks? (Use Monte Carlo simulation)
- Should Microsoft give new subscribers to MSN a Free PC? (Use customer valuation and Monte Carlo simulation)
- What percentage of retail sales in the United States will eventually be through E-commerce? (Use diffusion of new technology and S-curves)
- Jeff Bezos of Amazon.com is thinking of buying an Internet retailer with current annual revenues of $100 million and annual expenses of $150 million. For possible annual revenue growth rates of 10 percent to 100 percent and possible annual expense growth rates of 5 percent to 50 percent, determine how many years it will take for the company to break even. (Use IF statements and DATA TABLES)
- How should I allocate my sales force and/or promotional budget between different products? (Use SOLVER)
- How would you determine which attributes of a new soap drive consumer purchases? (Use conjoint analysis)
After completing the curriculum in Figure 1 (requiring 40 contact hours) I am confident most participants will be capable of dealing with many important business problems similar to those described above.
Summary
In today's world, life-long learning is a necessity, not a luxury. There are millions of analysts who need training in spreadsheet modeling, optimization, Monte Carlo simulation and data analysis.
The ORMS professor is uniquely suited to create a reality-based, relevant curriculum to increase analysts' skills in these areas. While training is demanding, it is financially and personally rewarding. Given that enrollments in most business schools are fairly stable, executive education of this type represents the major growth opportunity for our field.
Wayne L. Winston is a professor of Decision Sciences at the Kelley School of Business, University of Indiana, in Bloomington, Ind.
OR/MS Today copyright © 2001 by the Institute for Operations Research and the Management Sciences. All rights reserved.
Lionheart Publishing, Inc.
506 Roswell Street, Suite 220, Marietta, GA 30060, USA
Phone: 770-431-0867 | Fax: 770-432-6969
E-mail: lpi@lionhrtpub.com
URL: http://www.lionhrtpub.com
Web Site © Copyright 2001 by Lionheart Publishing, Inc. All rights reserved.