A CUSTOMIZED EXCEL DATA ANALYSIS SYSTEM FOR USE IN UNDERGRADUATE MARKETING RESEARCH Developments in Business Simulation and Experiential Learning, Volume 31, 2004 A CUSTOMIZED EXCEL DATA ANALYSIS SYSTEM FOR USE IN UNDERGRADUATE MARKETING RESEARCH Alvin C. Burns Louisiana State University alburns@lsu.edu Keith R. Burns Independent Programmer keith@keithburns.com Ronald Bush University of West Florida rbush@uwf.edu Jeanne M. Burns Southeastern Louisiana State University burnsj@gov.state.la.us ABSTRACT STATISTICS (ANALYSIS SOFTWARE) ARE THE PITS This paper describes the difficulties marketing research that instructors encounter when using statistical software packages. One of the difficulties in teaching arises from the nature of the statistical software packages available. Specifically, standard output in these packages generates many values by default. Students must learn “where to find” the values important for their particular analysis. Also, there are many options which may be selected to run a single procedure, and these are confusing to students. A third problem lies in using the output tables in word processing software. This paper presents a new type of data analysis software, XL Data Analyst, which overcomes these problems. The XL Data Analyst’s features and output are compared with SPSS. Over 50 years ago, Robert Ferber (1951) commented on the “lack of coherence between the business statistics course and the marketing course.” This lack of coherence is most apparent in the marketing research course where no matter what fresh approach is adopted, if any, the teacher of marketing research cannot avoid using statistics. Specifically, this paper describes the difficulties marketing research instructors can encounter when using a statistical software package such as SPSS, SAS, Minitab, Analyse-it™, or even Excel’s Data Analysis Tool. These difficulties and frustrations emanate from two sources. First, the instructor must teach (or reteach) statistics. If a poll were conducted, statistical analysis would most certainly be voted the least favorite subject of business students. From the authors’ combined experience of decades of teaching undergraduate marketing research, we can attest with great confidence that marketing research students not only have voided their memories of any statistical knowledge whatsoever, but they also have attached huge amounts of negative affect onto the topic. In other words, the typical student abhors statistics with great passion. INTRODUCTION The teaching of marketing research to undergraduates is challenging for several reasons, and various authors have written about innovative and engaging pedagogical approaches. These approaches have ranged from computer-based facilitators (e.g., Burns, Burns, and Bush (1995), Cort and Dominguez (1975), Finn (1987), Gentry (1979), McKay (1975), Rubin (1990), and Stanton (1977)) to experiential endeavors (e.g., Burns (1978), Fuller (1988), Lawton (1987), Niffenegger (1982), and Richardson (1979)). The second source of the marketing research instructor’s difficulties with statistics lies with whatever statistical analysis software he/she uses. There are two aspects to this issue. One difficulty is that learning a new program, particularly one as complicated as a statistical analysis program, is not a delight to students. Frankly, it is a considerable challenge to students. As Tam and Siu (1996) have noted, “…students may feel stranded 102 mailto:alburns@lsu.edu mailto:keith@keithburns.com mailto:rbush@uwf.edu mailto:burnsj@gov.state.la.us Developments in Business Simulation and Experiential Learning, Volume 31, 2004 when faced with the required use of unfamiliar computer packages for statistical analysis in marketing research courses.” As an interesting side note, Tam and Siu (1996) were referring to Hong Kong (i.e., Asian) students who are typically better prepared than U.S. students in mathematical concepts. The other troublesome aspect of statistical analysis programs is that, quite properly, statistical analysis programs generate statistical values of many kinds by default, and they have features that allow users to request and receive a great many other statistical values as well. As a quick example, consider SPSS’s One-way ANOVA routine. By default, there are 10 different statistical values (SSWithin, SSBetween, SSTotal, Withindf, Betweendf, Totaldf, MSWithin, MSBetween, F, and Sig). There are 14 equal variances and 4 unequal variances post hoc tests that can be requested, up to 6 optional statistics, and 3+ types of contrasts that can be requested. While instructors of marketing research are cognizant of the need for all of these statistical values, it is a very daunting task to teach undergraduate students how to navigate statistical software and how to deal with standard statistical output generated by these programs. Figure 1 Burns and Bush (2003) SPSS Clickstream for One-Way ANOVA Marketing research textbook authors have wrestled with statistical analysis program navigation and output interpretation. As an example, we will use Burns and Bush’s (2003) treatment of SPSS for Windows Student Version that comes packaged with their textbook. In fact, these authors claim that their book is “integrated” with SPSS (Preface, page xix). Burns and Bush (2003) innovated the concept of “clickstreams” which are annotated screen captures that show the cursor point-and-click sequence that is used in SPSS to obtain statistical values. Figure 1 is taken from this textbook’s website (www.mktgresearch.com) and found in the textbook (page 507). As can be seen, the approach they use is a numbered clip art “hand” pointer that shows the cursor motion and clicks along with post-it notes that explain each step. Burns and Bush (2003) also use a web-based (or downloadable stand-alone) ancillary called the “SPSS Student Assistant” that has screen capture videos that show the cursor’s motions and clicks. This learning aid is an improvement of its earlier version (Burns, Burns, and Bush, 1995). In addition to the improved SPSS Student Assistant, and in order to deal with the statistical output generated by SPSS, 103 Developments in Business Simulation and Experiential Learning, Volume 31, 2004 104 these authors created annotated SPSS output. Figure 2 shows the annotated ANOVA output (page 508). As can be seen in Figure 2, the relevant aspects of the ANOVA output are highlighted and explained with the use of post-it note clip art, arrows, circles, highlights, and even X-outs. At the risk of repeating ourselves, the clickstream/annotated approach demonstrates that students need a great deal of assistance to learn how to make a statistical analysis program generate the desired analysis, and they require detailed instruction on where to look in the generated output and how to interpret what they find when they look at the right place. The second point reveals that there is, in fact, a considerable disconnect between statistical analysis program output and the practical interpretation and use of the findings. Marketing research instructors are focused on the practical, meaning that they want their students to comprehend the basic statistical findings and to interpret these into a managerially meaningful (i.e., practical) presentation format. Stated simply, marketing researchers construct tables and graphs that communicate their findings to their clients. While SPSS has the feature of “Tablelooks” that enables formatting of SPSS output tables into a professional appearance, they remain statistical analysis output tables unless the user does considerable reworking in Tablelooks. Plus, there are subtle issues with copying and pasting SPSS tables into a word processor program just as there are with the SPSS Graph feature. To avoid these, one of the authors of this paper instructs his undergraduate marketing research students to copy and paste SPSS output tables into Microsoft Excel where they can be reworked, formatted, and/or converted to graphs with ease. Figure 2 Burns and Bush (2003) Annotated SPSS One-Way ANOVA Output . THE XL DATA ANALYST In a particularly lucid moment, two of the authors of this paper conceived of a better way to overcome these difficulties. In particular, since Microsoft Office Suite is the standard adopted by a very large majority of universities, and a great many business schools require their students to be proficient in Microsoft Office, the decision was made to develop a data analysis system that runs on Excel. As a bit of background, in the 1990’s, SPSS was the statistics analysis program used by many business schools in the business statistics course. This adoption of SPSS greatly facilitated the use of SPSS in the marketing research course as students entered the course with good familiarity of SPSS’s set up and functionality. However, for various reasons, there has been a move away from the traditional statistical analysis programs such as SPSS to the use of Excel in the basic statistics course. Levine (2000) has commented that “Microsoft Excel in an elementary statistics class seems a natural alternative to the computing battles and Developments in Business Simulation and Experiential Learning, Volume 31, 2004 stresses associated with using some of the more standard statistical software packages.” With the decision to use Excel as the foundation data analysis vehicle, an exhaustive search was conducted for Excel- based statistical analysis programs, and a handful was found including Analyse-it™, Excel’s Data Analysis Tool, and XLStat©. Close inspection of these revealed that while they did use familiar features of Excel, their output was essentially the same as SPSS, SAS, or other statistical analysis software programs. The authors also discovered Excel statistical analysis macro systems developed for statistics books (e.g., Sincich, Levine, and Stephan (1999)); however, these were also rejected for the identical reasonThus, the decision was made to develop a macro system for Excel that performs all of the analyses taught in an undergraduate marketing research course but which would produce output that students could readily interpret. Moreover, the output would be formatted into professionally-appearing tables, and since the output would be in Excel, users could easily use Excel graph features to create visual presentations. Since the Microsoft Office suite would be the platform, the tables and graphs could be seamlessly moved into Microsoft Word or PowerPoint. This vision guided the creation of the XL Data Analyst, a macro system that operates within Excel using Excel spreadsheet features and functions and programmed with Visual Basic for features that were necessary but not within Excel. Development took place with one author writing Excel macros to execute the various statistical analyses and output tables, another author writing Visual Basic code embedded in the macros to effect a user-friendly menu- and variables-selection windows system, and the other authors alpha testing the system as well as providing constructive comments and suggestions throughout the development of the XL Data Analyst. In its present form, the XL Data Analyst is an Excel macro system that creates a menu item called “XL Data Analyst” in Excel’s top menu, and this menu item operates with a cascading menu (See Figure 3) to enable a user to request any of the analyses or computational routines listed in Table 1. As can be seen, the XL Data Analyst accommodates data analyses treated in most undergraduate marketing research textbooks. Data is entered and stored in rows and columns with the columns corresponding to questions (variables) on the questionnaire, and the rows corresponding to respondents. The first row of the Data worksheet is reserved for variable names, and a “Define Variables” worksheet links the variables to their respective long description (e.g, Respondent’s place of dwelling) value codes (e.g, 1, 2, 3, 4), and value labels (House, Mobile home, Apartment, Homeless). When a user selects a type of data analysis via point and click, the XL Data Analyst opens up a selection window appropriate for that analysis with prompts as to what to select. A click on “OK” activates the analysis. Figure 3 XL Data Analyst Menu and Variable Selection Menu 105 Developments in Business Simulation and Experiential Learning, Volume 31, 2004 Table 1 Data Analyses and Computational Routines in the XL Data Analyst XL Data Analyst Menu Analyses/Routines (Submenus) Summarize • Averages (standard deviation, maximum, minimum) • Percents (frequencies, percentages) Generalize • Confidence intervals for an average • Confidence intervals for a percent • Hypothesis test for an average • Hypothesis test for a percent Compare • 2 group percents • 2 group averages • 3+ group averages • Paired variables averages Relate • Crosstabulations • Correlations • Regression Calculate • Random numbers • Sample size The XL Data Analyst has been programmed to generate 2 types of analysis output. First and foremost, it creates an interpreted professional table. That is, the table is laid out such that it can be copied and pasted directly into a report or other presentation vehicle. Moreover, the table reports any statistical findings (at the 95% level of confidence) in a straightforward manner. See Figure 4, for instance, on how the XL Data Analyst presents the findings of an ANOVA. With the XL Data Analyst, the table identifying significant differences (or not) between the various group means appears only if there is a significant F value result (95% level of confidence). If the F value is nonsignificant at the 95% level of confidence, a simple message appears that indicates to the user that there is no significant difference between any of the group means. At the same time, the XL Data Analyst generates and reports the appropriate statistical values in case the user wishes to examine them. As can be seen in Figure 4, the statistical values are set off to the side, arranged in a table with gray background, and identified with cryptic labels. The assumption is that if a user intends to examine the statistical values, he/she will be sufficiently familiar with them to understand the labels. The gray background implies to students that the statistical values are not paramount. XL DATA ANALYST COMPARED TO SPSS In this section of our paper, we will compare the XL Data Analyst to SPSS for Windows Student Version. There are two reasons for this comparison. First, we are intimately familiar with SPSS as we have used it a great many years, and, second, SPSS is the market leader. So, presumably, we are comparing the XL Data Analyst with the statistical program that has become the standard for a great many marketing research instructors. Table 2 presents our comparison of these two software vehicles, and it illustrates that the difficulties previously mentioned with teaching marketing research using SPSS are overcome by the XL Data Analyst. Specifically, the XL Data Analyst yields interpreted analyses in professional tables that can be turned into Microsoft Excel graphs using the Excel graphing features and pasted into a presentation document such as Microsoft Word or PowerPoint with ease. The capacity of the XL Data Analyst far exceeds that allowed by SPSS Student Version, and there are other criteria such as: lower cost, minimum menu options, less required background knowledge of statistics that render the XL Data Analyst more attractive than SPSS Student Version. 106 Developments in Business Simulation and Experiential Learning, Volume 31, 2004 Figure 4 XL Data Analyst Interpreted Output 107 Developments in Business Simulation and Experiential Learning, Volume 31, 2004 Table 2 Comparison of XL Data Analyst to SPSS for Windows Student Version Feature/Aspect SPSS Student Version XL Data Analyst Usefulness of Output Interpreted findings? None Always Professional tables? Possible with “TableLooks” Standard output Graphs? Possible with SPSS Graph feature* Excel graphs Works with Microsoft Word, PowerPoint, etc? Can encounter pasting issues Seamless Capacity Aspects Maximum Number of Variables? 50 255 Maximum number of records? 1500 65,535 Other Considerations Cost? $100 Undetermined Menu names Largely statistical (e.g. One Sample T-Test) Practical (e.g. Confidence Intervals-Average) Menu statistics options? Many Few Required knowledge? Requires some statistical knowledge Requires minimal statistical knowledge Sample size calculations? None For percentage estimates *Or can paste SPSS tables into an Excel spreadsheet and then use Excel graph features CURRENT AND EXPECTED STATUS OF THE XL DATA ANALYST The XL Data Analyst was developed in 2003, and it will enter into Beta testing at the end of this same year. It will continue to be tested by the authors and others and refined while a marketing research textbook is written that integrates the XL Data Analyst as its data analysis software program. Plans are to see this textbook in print sometime in 2004. At that time this new approach will undergo the most difficult of all tests, the reaction of the marketplace. Hopefully, the XL Data Analyst will lead to an improvement in the way students learn and use statistical software. REFERENCES Burns, Alvin (1978). “The Extended Live Case Approach To Teaching Marketing Research,” Exploring Experiential Learning: Simulations and Experiential Exercises, Volume 5, (March), pp. 245-249, available in the Bernie Keys Library, 4th edition, published by the Association for Business Simulation and Experiential Learning. Burns, Alvin and Ronald Bush (2003). Marketing Research: Online Research Applications, Upper Saddle River, NJ: Prentice Hall. Burns, Alvin, Keith R. Burns, and Ronald F. Bush (1995). “The SPSS® Student Assistant: The Integration Of A Statistical Analysis Program Into A Marketing Research Textbook,” Developments In Business Simulation & Experiential Exercises, Volume 22 (March), pp. 204-209, available in the Bernie Keys Library, 4th edition, published by the Association for Business Simulation and Experiential Learning. Cort, Stanton and Luis V. Dominguez (1975). “Mode I Stores, Inc.: Computer Supported Cases On The Marketing Research And Problem Solving Process,“ Business Games and Experiential Learning in Action, Volume 2, (March), pp. 299-307, available in the Bernie Keys Library, 4th edition, published by the Association for Business Simulation and Experiential Learning. Ferber, Robert (1951). “On Teaching Statistics to Marketing Students,” Journal of Marketing, January, Volume 15, Issue 3; page 340. Finn, David, (1987). “Teaching About Sampling In A Marketing Research Class,” Developments In Business Simulation & Experiential Exercises, Volume 14, (March), pp. 57-62, available in the Bernie Keys Library, 4th edition, published by the Association for Business Simulation and Experiential Learning. 108 Developments in Business Simulation and Experiential Learning, Volume 31, 2004 Fuller, Donald, (1988). “Using Focus Groups To Teach Problem Definition In Basic Marketing Research,” Developments In Business Simulation & Experiential Exercises, Volume 15, (March), pp. 217-220, available in the Bernie Keys Library, 4th edition, published by the Association for Business Simulation and Experiential Learning. Gentry, James (1979). “Teaching Pert Experientially In Marketing Research,” Insights into Experiential Pedagogy, Volume 6, (March), pp. 175-177, available in the Bernie Keys Library, 4th edition, published by the Association for Business Simulation and Experiential Learning. Lawton, Leigh, (1987). “Using A Joint Project Involving MBA Marketing Management and Undergraduate Marketing Research Students To Teach Marketing Research,” Developments In Business Simulation & Experiential Exercises, Volume 14, (March), pp. 129-132, available in the Bernie Keys Library, 4th edition, published by the Association for Business Simulation and Experiential Learning. Levine, Richard (2000). “Practical Statistics by Example Using Microsoft Excel," The American Statistician, Alexandria: May 2000. Volume 54, Issue 2, pp. 151-153. MacKay, David (1975), “Using Computer Assisted Cases For Marketing Research Instruction,” Simulation Games and Experiential Learning in Action, Volume 2, (March), pp. 127-134, available in the Bernie Keys Library, 4th edition, published by the Association for Business Simulation and Experiential Learning. Niffenegger, Phil (1982). “The Small Group Research Project: An Experiential Learning Approach For Undergraduate Marketing Research Students,” Developments In Business Simulation & Experiential Exercises, Volume 9, (March), pp. 49-53, available in the Bernie Keys Library, 4th edition, published by the Association for Business Simulation and Experiential Learning. Richardson, Neil (1979). “Debugging and Implementing the Live Case Approach To Marketing Research In The Australian Environment,” Insights into Experiential Pedagogy, Volume 6, (March), pp. 7-10, available in the Bernie Keys Library, 4th edition, published by the Association for Business Simulation and Experiential Learning. Rubin, Ronald (1990). “STATUTOR: An Expert System for Selecting Analytical Techniques for Analyzing Marketing Research Data,” Developments In Business Simulation & Experiential Exercises, Volume 17, (March), pp. 138-142, available in the Bernie Keys Library, 4th edition, published by the Association for Business Simulation and Experiential Learning. Sincich, Terrey, David A. Levine, and David Stephan (1999). Practical Statistics by Example Using Microsoft Excel, Upper Saddle River, NJ: Prentice Hall. Stanton, Wilbur (1977). “A Computer Simulation for Marketing Research and Consumer Behavior,” Computer Simulation and Learning Theory, Volume 3, (March), pp. 211-218, available in the Bernie Keys Library, 4th edition, published by the Association for Business Simulation and Experiential Learning. Tam, Kam-Chuen and Wai-Sum Siu, (1996). “Teaching statistical analysis in the market research course at tertiary institutions in Hong Kong,” Journal of Education for Business, Washington: May/Jun. Volume. 71, Issue 5, pp. 300-304. 109 Table of Contents Volume 31, 2004 Controlling the Complexity and Orenting Target Groups by a Modular, Server-Based Business Game System Learning Network Demonstration: Delivering Business Education in a Distance Learning Environment Economic Evolution, Human Capital Investment, and Adult Distributed Electronic Learning: A Literature Review Designing a Globalization Simulation to Teach Corporate Social Responsibility Developing and Teaching an Online / In-Class Hybrid: A Demonstration A Model for Evaluating Online Instruction An Evaluation of a Distributed Learning Course: A Students'-Eye Perspective Blended Learning Strategy Improved Business Writing Skills How to Receive and Process Attachemnts while Greatly Reducing the Risk of Viruses and Trojans Introducing Online Components to a Class: How to Increase teh Likelihood of Success Teaching Strategic Communications Online: Using Learning Outcomes to Develop a Case-Based Course Implementing Distance Approaches to Education: A Panel Discussion for ABSEL: Las Vegas, 2004 MANDI: Learning Management Through Field Sales Experience An International Capital budgeting Experiential Exercise A Primer To Combating Terrorism: Playing It Safe While On Overseas Assignment (An Experiential Exercise) Integrating The Business Curriculum With A Comprehensive Case Study: A Prototype The Case Brief: A Model For Case Analysis, Writing And Discussion Technology Infused Pedagogy And Delivery – A Sure Bet? A Proposal For Panel Discussion Absel Conference 2004 Research Strategy And The Bkl: Getting The Most From The Absel Archives Simple But Effective: Rediscovering The Class Discussion Needle And Thread: An Activity For Examining Various Management Behaviors A Customized Excel Data Analysis System For Use In Undergraduate Marketing Research Team Leader Selection - Does It Matter? The Power Of Perspective: Reframing Your Framing Skills For Innovative Instruction In Leadership And Influence Exercise: How Should Merit Raises Be Allocated? An Online Situation For Problem-Based Learning In A Junior-Level Management Course The Eden Alternative As A Roadway For Change: A Service Learning Quality Improvement Project Avoiding Catastrophe: The Role Of Individual Accountability In Team Effectiveness Omega Systems: A Change Management Exercise The Risks And Rewards Of Providing Students A Structured Cheating Opportunity Experimentation With Assessment Techniques: A Proposal For Panel Discussion Using A 2 - Page Case To Introduce Concepts Of Business Strategy Interactive Session The Integration Of Appreciative Inquiry And Experiential Learning For Peak Performance Appreciative Inquiry Case Story: New York City Leadership Challenge Individual Achievement Versus Team Performance: An Empirical Study With Business Games Some Strategists Don't Learn Or Can't Learn Computer Simulation: A Design Architectonic On The Value Of Bugs In Simulation Environments Online Sales Forecasting With The Multiple Regression Analysis Data Matrices Package Simulation Exercises And Problem Based Learning: Is There A Fit? A Study Of Business Game Stock Price Algorithms Assessing Individual Performance In A Total Enterprise Simulation Information Use In A Business Game Determining The Value Of A Firm Unsorting Algorithms For An Ordered List And Its Application To Business Simulations Teaching Public Finance Management Through Simulation Antecedents Of Game Performance Student Expectations Of Classroom Teaching Practices In Developing And Presenting Course Information In Hong Kong Implementation And Impacts Of The Balanced Scorecard: An Experiment With Business Games Impact: Shocking The Legacy Mindset Implementation Of The Eepad Framework Of Business Processes In An Accounting Information Systems Course Are Business Games Really Delivering What Students Are Led To Believe?? Reporting Lessons Learned: What Gets Reported; Who Gains Value Teacher Expectations Of Classroom Teaching Practices In Developing And Presenting Course Information In Hong Kong Student Reactions To The Use Of A Computer-Based Simulation As An Integrating Mechanism For A Mba Curriculum A Cognitive Investigation Of The Internal Validity Of A Management Strategy Simulation Game The Casino Challenge: Making Simulation Delivery A Safe Bet! Accounting For Company Reputation: Variations On The Gold Standard Foreign Currency Hedging: A Simulation The Influence Of Variables Easily Controlled By The Instructor/Administrator On Simulation Outcomes: In Particular, The Variable, Reflection. Absel Awareness Among Business School Faculty Validating Business Simulations: Does High Market Share Lead To High Profitability? Simulation Debriefing Procedures Coaching And Business Simulations: A Formula For Success? A Seminal Inventory Of Basic Research Using Business Simulation Games The Influence Of Scorecard Evaluation On Decisions And Outcomes