Abstract

This article discusses the use of Excel in the introductory course in statistics in the business school.  An attempt is made to identify how business school programs differ, and how the use of Excel to work cases or examples can contribute to a business statistics course that is in keeping with the school’s and the instructor’s learning objectives.

There are possibly more different ways to teach statistics to business students that any other subject in the business school curriculum.  The business statistics course can range from a highly quantitative presentation of distribution theory, to a course that might be described as “strategic analysis with data”.  The observed variations in pedagogy and content reflect differences among schools and faculty, but also reflect a varying response to changing technology.  This article discusses some issues that are important in designing the introductory business statistics course, and presents some ideas for using spreadsheets that may be appropriate in teaching statistics in the business school.

Designing the Business Statistics Course

No business school course exists in isolation: they are all part of a larger program.  A key issue in course design is aligning the characteristics of the course with those of the program in which the course is offered.  A course that does not “fit” with its program is unlikely to be a very satisfactory experience for the instructor, or be well received by the students. 

The first task in designing any business school course is, therefore, to understand the nature of the business program.  The business school marketplace is highly competitive and becoming more so, as the major schools compete for the same excellent domestic and foreign students, and for the attention of the same groups of prestigious recruiters and major donors.  This marketplace, however, is not homogeneous: there is room for a variety of different kinds of programs.  In marketing terms, business school programs occupy different “positions” in this marketplace. The major schools position their programs in an effort to present a distinctive image and so avoid direct competition: Chicago’s MBA program is very different from Harvard's.  Other schools position their programs seeking differentiation from their major competitors, while avoiding head-to-head competition with the major schools.

The position a school adopts determines the appropriate content and pedagogy for its programs.  A course that supports the school’s market position is much more likely to prosper than one that does not.  An extreme example will illustrate this point.  One differentiating feature among programs is “quantitativeness” which can usually be seen in the program prerequisites: an MBA program that requires all entering students to have taken a calculus course is likely to be much more quantitative than one that has no mathematics prerequisites.  A highly quantitative statistics course in an MBA program that has no mathematics prerequisites is unlikely to be a great experience for the student or the instructor. “Quantitativeness” is one obvious characteristic that differentiates business programs but other positioning factors are subtler.  Here are some other examples: the extremes of each positional characteristic have been described although many schools occupy intermediate positions.

Where a program ends up on these positional scales emerges as a result of market pressures modified by faculty politics.  Faculty will fight hard for positions that match their personal strengths and interests, which generally favor more “content”-based and “theoretical” programs.  If attracting enrollment becomes an issue, schools will try to find a position that appeals to their particular market.  Consequently, program positions are not static but evolve over time as schools react to both internal and external pressures.  

What does this have to do with the design of the business statistics course?  Since about 1990, the poor reaction of the market to overly “quantitative” MBA programs has pushed many schools into changing the positions of their MBA programs moving away from theoretical, content, and analysis towards practical, skills, and decision. This positional drift has important implications for the teaching of “statistics”.

The Making Statistics More Effective in Schools of Business (MSMESB) conferences have been held since 1986, and the first such conference provides support for this positional change.  The conference found that students were best motivated by exposure to real applications, problems, and cases and suggested that the emphasis in the statistics course on formal theory be reduced while emphasizing intuitive concepts and applications.  Finally, the conference suggested that report writing and the communication of statistical results received inadequate coverage (Easton, Roberts, and Tiao, 1988).  These findings are consistent with a movement in the business schools towards the practical, skills, and decision end of the positional spectrum.

Deciding what is appropriate “statistics” instruction in a business school

The first step in course design is to assess the position of the host program, and then to begin to lay out the general topics to be included, and select appropriate tools, perhaps including software.  Programs that are positioned as theoretical, content and analysis will likely require a “statistics” course that presents statistics in a rigorous, analytical way, perhaps including a fair bit of calculus.  I have no experience teaching such a course, but suspect that the opportunities to use Excel in this situation are quite limited.   

My experience comes from teaching at the Richard Ivey School of Business and in a number of programs around the world that have all been positioned towards the practical, skills, decision, and global business oriented end of the positional spectrum.  These business programs, as do others, have certain well-understood objectives, and instructors are expected to work towards these objectives in their courses.  The instructor designing a course can consider these objectives as being in two groups.  The first set of objectives might be called the “program objectives”, which provide an overall framework under which all courses and program activities are offered.  An individual course that does not support the program objectives will seem out of place, and so it is in the interests of each individual course instructor to design a course that supports these program objectives wherever possible.  For example, a highly quantitative statistics course in a very practical program will most likely leave the instructor bemoaning the fact that “our students don’t know any math”, will contribute little to the students’ practical ability, and will likely be poorly rated by the students. 

The first step in designing a “statistics” course is to make a list of the “program objectives”: here is one interpretation of a set of “program objectives” for a practical, skills, decision, and global business oriented program that would be relevant to the teaching of “statistics”.  Graduates should possess:

• a general understanding of the world of business, including major companies, current issues, personalities, and economic regions,

• a general understanding of the business functions, and how they interrelate and work together,

• the ability to analyze business problems, arrive at sensible solutions, and implement necessary change,

• the ability to effectively communicate ideas through formal presentations, written reports, and group discussions,

• the ability to receive work presented by others critically and respond appropriately,

• an understanding of and ability to work in “teams”.

At first glance, these program objectives may not seem to have much to do with “statistics” but the statistics instructor can contribute to these objectives in many ways.  As examples:

• doing some cases or a project on a major international company can help students learn about the world of business,

•  choosing examples or cases from marketing, finance or the consulting field can contribute to a general understanding of the business functions, and how a common understanding of data can contribute to the functions working together,

• cases or examples where the use of data, supported by statistical analysis, leads to improved decision-making can develop students’ ability to analyze business problems and arrive at sensible solutions.  In real world cases, there is almost always an opportunity to discuss implementation and managing necessary change,

• examples that provide practice in Excel and/or exposure to functions or features of Excel that are new to students contribute to developing more general problem analysis skills,

• the use of a variety of Excel graphical presentation objects (both good ones and bad ones) helps students understand and learn effective presentation skills,

• students’ ability to effectively communicate ideas can be supported by including student presentations, written assignments, and classroom discussions in the course.  Classroom or small group discussions also contribute to developing the students’ ability to receive work presented by others critically and respond appropriately, and

• finally, group assignments accompanied by a presentation to the class or a written report, contribute to developing students’ “teamwork” skills.

The second step in designing the course is to define the course objective: What does the professional “statistics” instructor want students to take away from the “statistics” course as opposed to other courses in the program?  Here is my version of a “statistics” instructors’ wish list.  I would like students taking my statistics course to leave with:

• the ability to use data to improve their decision making,

• the ability to present data or results informatively,

• the ability to analyze data and draw “statistically correct” conclusions from data, and

• the ability to understand data when it is presented to them, to ask useful (perhaps “probing” questions), and to respond appropriately.

Translating these general objectives into specific “statistical” skills and abilities, I arrive at the following statistical topics list.  After taking my course, I would like business students to:

• understand tables and graphs, and be able to create useful and informative presentations of data,

• understand, and be able to use, sensible statistics to summarize data including measures of centralness (mean, median, mode), variability (variance, standard deviation, range) and distribution (absolute and relative frequencies, histogram, cumulatives),

• understand variability and recognize the existence of uncertainty (or distribution) in all estimates,

• understand and be able to exploit sequence effects in series,

• understand the basic constructs of statistical inference including populations, samples and sample selection, estimation, and hypothesis testing, and be able to perform statistically correct estimation and testing, and

• understand correlation and relationships and be able to build and use useful regression models (simple and multiple, linear and non-linear, and autoregression).

Choice of Pedagogy

Choice of pedagogy follows identification of course and program objectives.  The course objectives listed above emphasize developing students’ skills at working with and using data.  These objectives might be summarized as producing students who can actually do things using statistics, as opposed to producing students who understand statistical theory.  Usually “doing things” is taught through practice, and a course with these objectives should be loaded with problems and examples that students can use to practice application of the concepts that they are learning. 

The program objectives listed suggest a highly interactive classroom experience with students presenting and discussing their work, offering their ideas for comment and defending their ideas when they think they are correct while conceding gracefully when they recognize that they may have erred.  The general program objectives can be further supported if at least some of the problems that the students work on are set in real companies, deal with current or emerging business issues, and involve leading edge companies and industries.

The emphasis on real problems suggests the use of software to facilitate performing the statistical calculations, and here the instructor has a choice between using special purpose statistical software (such as SPSS or Minitab) or the general purpose Excel spreadsheet.  The case in favor of using Excel includes the following points:

• Excel is very widely available in industry and is now quite standard,

• there is a low set-up cost to using Excel: most students will have used Excel before the course,

• there is both an immediate and a lasting value to Excel:  your students will be able to use their Excel skills in other courses, and will almost certainly have access to Excel when they start work,

• improving students' Excel skills supports program objectives of improving students' general problem analysis skills, and

• Excel has instant credibility with students, who recognize Excel as a very widely used and powerful "number-crunching" device.

There are also some advantages to Excel in terms of statistical calculations:

• The Excel statistical functionality is seamlessly integrated with the other Excel functions:  it is simply a part of the spreadsheet.

• Excel is very good for doing basic data analysis, including graphing and producing tables,

• the Excel basic statistical functions (including average, standard deviation, median, slope, intercept) are easily accessible through the “function wizard” and generally produce the answers statisticians expect,

• the “Analysis Toolpak” adds a great deal of more advanced statistical functionality,

• simple (i.e. one-independent variable) regression is easy if done with “Trendlines” although this results in very limited statistics (R² and regression coefficients only).

It is, however, important to recognize some of the weaknesses of Excel:

• Some (a few) students will have little prior Excel experience,

• Excel makes analysis so easy that some students will do too much analysis,

• it is easier to crunch numbers in Excel than it is to think about why this is being done, or what value it is,

• crunching numbers in Excel is much easier than thinking about softer or more managerial issues,

• Excel is poor at handling mathematical formulas, or processing symbols,

• the function formulas are hidden when using Excel, and

• the “Analysis Toolpak” is quite often cumbersome (e.g. Histogram, Multiple Regression),

Students who have no prior Excel experience should be encouraged (or required) to attend an Excel course prior to the start of the business statistics course:  it is too much to try to teach both Excel and business statistics from scratch in the program time usually allocated to the statistics course.  The topics list for the Ivey Excel "boot-camp" is attached as Appendix. 

There are a number of add-ins available that enhance the basic statistical functionality of Excel or make it easier to perform basic statistical operations in Excel.  These include statistical add-ins PHStat, Data Analysis Plus, and MegaStat, as well as @Risk and Crystal Ball which include simulation capabilities in addition to purely statistical functionality.  I developed my own add-in called SAM (Statistical Applications Module) originally to help in the classroom, where I felt the need for one-touch statistical output in order to avoid long quiet spells while keystroking through standard series of tasks.  For example, often when provided with some sample data that is germane to a decision problem, it is useful to compute the sample mean, median, variance or standard deviation, maximum and minimum values, and to look at the distribution (histogram), a sequence plot to check for sequence effects, and possibly a normal plot to check if the sample is well approximated by a normal distribution.  To avoid repeating this sequence over and over in the classroom, a single button-click in SAM provides descriptive statistics for a sample (mean, median, standard deviation, standard error, maximum value, minimum value, and sample size), with additional buttons providing a default histogram, sequence plot, or normal plot of the sample.  Another single button provides confidence intervals and prediction intervals from a sample.  SAM proved popular with our students and so we now distribute it to them.

A final important issue is the prior statistical training of the students.  In the typical required course in business school statistics, there will be students with a broad range of statistical background.  It is not uncommon to have students with degrees, or near degrees, in statistics mixed up with “artsies” who have so far avoided working with numbers and are proud of this fact. Finding course materials that can challenge those with extensive statistical prerequisites without overwhelming the “artsies” can be difficult, but this can be accomplished through good real world decision-oriented applications and the use of Excel.  Those who understand the statistics can be challenged by a new area of application, a new statistical analysis tool in Excel, and can learn by explaining their analysis to others.  At the same time, the neophytes can learn the basic skills and concepts, but perhaps miss some of the more advanced nuances of the application.  Each group should be able to arrive at a decision recommendation and present their own level of statistical analysis to support their recommendation.

Introducing Excel Examples into the Course

First, a word about how the classroom might be set up for an Excel-based session. At the start of class, ask for a volunteer “driver” and plug that student’s notebook into the overhead projector.  Choice of driver depends on the class.  Early in the course, the chosen driver should be someone with only moderate proficiency in Excel.  Later, when considerable keystroking is required, for example to build several multiple regression models, a driver with greater Excel proficiency should be selected.  Importantly, the driver should be always working with his or her own PC and own version of Excel.  The driver should be instructed not to enter anything into the spreadsheet unless instructed to by “the class”.  This set-up has a number of advantages, including:

• this is a highly active learning mode where the students in class are doing things and contributing, so they keep interested and keep up with developments,

• the instructor receives instant feedback on where the class is and what the students are learning, or have learned so far,

• the instructor does not have to pause while typing at the computer and can concentrate on what the students are saying,

• the instructor has no opportunity to demonstrate an inability to type or work the spreadsheet,

• the kinds of data entry mistakes that the “driver” makes are typical of the kinds of mistakes students make, with the result that students learn the processes of correcting these mistakes,

• the “driver” is working with their own PC, mouse, and version of Excel which cuts down on the number of screw-up possibilities, and

• students who contribute ideas on how to build or improve the model receive quick feed back, although the instructor needs to moderate the proceedings so that weaker students are not embarrassed when they suggest something “off the wall”.

Here are a few examples to illustrate some basic statistical concepts, and some of the ways Excel might be used in the classroom in the teaching of statistics.

Last Year’s Starting Salary Data

Business students are keenly interested in starting salaries for the school’s graduates.  The placement office will probably provide an Excel worksheet that lists starting salary, bonuses, city, industry, position description, etc. of all of last year’s graduates that they know about, sorted by program (mine does).  The data usually covers about three-quarters of the graduating classes.  The school probably also publishes “official” statistics on starting salaries, most importantly average salaries by program that are widely quoted.

Prior to the first statistics class, e-mail this file to the students, including a very general assignment such as: What can you tell me about starting salaries?  At the start of the class, project this file and ask something like: Where do you want to start?  In the next half-hour or so, go where the students want to go.  Usually they will compute some means [introducing the Excel =average() function], compare them with the official averages, compute medians [=median()], talk about percentiles, breakout “I-banking”, consulting, and marketing and compute separate statistics for these popular careers.  They may also look at different locations (New York versus Toronto is a popular comparison at Ivey), and discuss salary variation and ranges. 

This exercise serves several functions.  It introduces or reviews a number of statistical concepts in a situation that interests the students keenly, is very real, and from which they learn a great deal about their future prospects.  Even those students who know the concepts will still learn from this class.  The session also reviews Excel and some of its basic concepts and functionality, and emphasizes the fact the both statistics and Excel are useful tools to help them, as future managers, understand issues that are important to them.

Firestone:  Pay No Dough if it Doesn’t Snow

Understanding statistics is about understanding variation.  Important objectives of the business statistics course are to introduce students to thinking in terms of distributions of outcomes, and risk. The Firestone Tire Company case presents a business situation where point estimates reveal little, but an understanding of the distributions of the outcomes reveals a great deal.  Firestone is considering a promotion in which buyers of snow tires can claim refunds if there is little snow.  The refund points are expressed as percentages of average annual snowfall.  Understanding this promotion is all about understanding variation.  Data is provided that enables the risk of the promotion to be assessed for three locations. 

Display of the sample data promotes discussion of important statistical issues about populations, samples, and sample selection.  After computing common statistics (mean, median, standard deviation), and examining distributions (histogram) of snowfall data, a number of additional statistical issues emerge.  Are there sequence effects?  Does the data "fit" a normal distribution?  Once students are comfortable with the data, the distribution of refunds in each location can be computed (either from the empirical data or in some cases from a normal distribution approximation). The distributions reveal that while the expected values look reasonable, the probability of refunding 100% of the purchase price is quite high.  This is, therefore, a risky promotion.

Since the probability of customers receiving refunds in at least one location is essentially zero, the example also provides an opportunity for a discussion of the ethics of advertising something which the public might believe to be possible, but which the company should know is just about impossible.

Burnhamwood College

The Burnhamwood College case is about admissions to an MBA program.  The issue is to decide what weight to attach to GMAT scores in the admission decision.  Data is provided on entering GMAT score and final MBA program grade point average (GPA) for a sample of students from three consecutive years.  Business students are aware of the issues, and many are curious about the admissions process, and are very interested in predicting their own performance in the program.  Simple regression models can be constructed that show a significant but weak (R² in the 0.2-0.4 range depending on year) correlation between GMAT and MBA GPA.  A predictive model can be constructed that enables students to enter “typical” GMATs (their own?) and then interpret prediction intervals.

This example enables a number of technical issues to be covered (I use it as the second class on regression) including the statistics of the simple regression model, and an example of moderate but significant correlation.  The case also illustrates the important point that the existence of statistically significant correlation does not necessarily lead to a useful predictive model.

Brent Harbridge Developments

An important management use of sample data is to provide estimates of probabilities for decision-making purposes.  The Brent-Harbridge case merges statistics and decision analysis.  The Chief Executive of a property development company has to decide how to bid on three upcoming property auctions.  Data is available on previous bids at several different types of property auction, and regression models can be constructed that appear to provide useful predictive models for winning auction bids.  The first exercise for the students is to develop these regression models.  The regression modeling provides students with practice in model building, including interpreting the regression statistics, creating category variables, understanding model choice criteria, and using grouped data.  Once useful regression models are selected, deriving the predictive distribution and recognizing its normality, together with the properties of the normal distribution, allows computation of the probability of winning each auction at any bid amount.  Incorporating these probabilities into a decision tree analysis, and including decision criteria (including expected values) enables a bidding strategy to be derived.  

Ian Steele

The owner of a convenience store has been suffering “leakage” from the till.  Ian Steele has confessed to theft of a small amount, but the financial results of the store suggest that a much greater amount is missing.  The statistical issue is to try to estimate how much was really stolen and the period over which the thefts occurred, but the case also provides a rich learning experience on how theft can be detected in time series data.  Many analysis approaches are possible, including regression with a category variable denoting theft, and hypothesis tests that test key statistics from the likely theft period with the same statistics from the likely no-theft period.  In the final analysis, a carefully plotted graph provides as clear a picture of the theft as do the statistics.

Examinations and Grading

Grading an Excel-based course should be consistent with the pedagogy of the instruction.  This suggests that a reasonable percentage of each student’s grade be based on the student’s contribution to the classes when the examples or cases are discussed. 

The written instruments that match Excel based teaching of statistics most closely are take-home examinations and in-class cases or examples.  Multiple choice written examinations are problematical since they emphasize “content” rather than skills and decision-making ability. The advantage of a take-home examination is that students can spend the time necessary to perform quite complex analysis or model building, and then submit a well-written (typed) management report.  Students can work in groups and submit one written report per group, or can work in groups but submit individual reports.  Such a group take-home examination contrasts nicely with individual in-class examinations based on one or more cases or problems (where students will have less time to perform the analysis).

A combination of carefully chosen take-home and in-class assignments can contribute to students’ education in statistics, but also to the development of their personal individual and group work skills, writing skills, and their ability to communicate technical material to a general audience.

Conclusions

The use of Excel together with realistic business examples and exercises can contribute greatly to students understanding of both business and statistics. 

Understanding data, and the basic elements of descriptive statistics, including graphs and charts, and regression modeling are identifiable “takeaways” from the statistics course that have high added value in a practical, skills, and decision oriented, business program. The ability to use Excel and the Excel statistical functionality is a skill that students can use immediately as they enter the workforce, and is seen as valuable by both students and employers.

In addition, the use of real, relevant, business oriented cases and examples that is facilitated through the use of Excel, can contribute to students’ understanding of business and business issues. In addition to contributing to students’ education in statistics, cases and examples can be introduced into the course in a manner that contributes to developing students’ presentation, listening, and discussion skills, and also to their writing ability if written reports are required.

References:

Brent Harbridge Developments, Case 9A98E042, Richard Ivey School of Business, London, Canada.

Burnhamwood University (1992), Statistics for Business with Spreadsheets, Text and Cases, Second Edition, Boyd and Fraser Publishing, Boston.

Easton, G., H.V. Roberts, G.C. Tiao (1988), “Conference Report:  Making Statistics More Effective in Schools of Business”, Journal of Business and Economics, Vol. 6, No. 2, pp. 247-260.

Firestone: Pay No Dough if it Doesn’t Snow, Case 9-84-E002, Richard Ivey School of Business, London, Canada.

Ian Steele, Case 9A99E009, Richard Ivey School of Business, London, Canada.

Appendix:   EXCEL "Boot-camp" Coverage
 

Rows and columns and sheets:

• Insert row/column/sheet

• Change column widths

• Rename Sheet

Menus and Toolbars:

• Worksheet Menu

• Toolbar – customizing (adding and deleting buttons)

Cells:

• Active object:  cell and ranges

• Cell/range formats: numbers (general format, 0.0000, scientific (E) notation, add decimal and reduce decimal buttons), date formats and 36000 dates

• $ and % buttons

• Formulas = +-*/^, calculation order of =A1+B1/C1^2

• Editing formulas

• Strings and string arithmetic (“=A”&b6&”B”)

• Text and fill colours, change fonts and font size

• Bold and Italic and Underline buttons

Files:

• Open and Close

• Save and Save As

• Default directory and some file management.

Windows:

• Active window, change window (multiple files open)

Commands:

• Cut/Paste cells and ranges

• Copy/Paste relative and absolute addressing

• Copy/Autofill (pull down one or two cells)

• Paste Special Values

• Paste Special Transpose

• Print: Set Print Area, Print Preview, Fit to page

• Undo buttons

• Sort;  sorting range and keys.

Functions:

• Function wizard

• =Sum() and AutoSum button

• =IF() function

Charts:

• Chart Wizard

• Chart types

• Bar charts and Line graphs

• Chart items; series, legend, axis labels, titles etc.

• Series add/delete

• Some format options

^ã INFORMS