SIMULATION WITH INTEGRATED SPREADSHEETS: THE DESIGN AND DEVELOPMENT OF A CONVERSATIONAL MARKETING CONCEPTS DECISION GAME Developments in Business Simulation & Experiential Exercises, Volume 15, 1988 253 SIMULATION WITH INTEGRATED SPREADSHEETS: THE DESIGN AND DEVELOPMENT OF A CONVERSATIONAL MARKETING CONCEPTS DECISION GAME Ronald S. Rubin, Ph.D. University of Central Florida ABSTRACT Integrated spreadsheets aren’t just for financial models. In fact, they can simulate many different types of dynamic processes. This paper attempts to outline some of the basic principles that could be applied in designing and developing a spreadsheet simulation. MARKETING CONCEPTS, a spreadsheet simulation developed by the author is used to illustrate program development and system utilization. INTRODUCTION Many marketing faculty find computer simulations lively and effective. In fact, many hook publishers are now offering as part of their principles of marketing textbook packages companion microcomputer software, including various simulation packages. Marketing decision games offer a unique means of teaching marketing concepts and developing strategic abilities. With hands-on decision- making through simulation, students wore fully appreciate the complexity of the interaction among marketing mix components. Opportunities emerge for employing previously studied marketing decision-making tools. Also, through computer feedback, students note applications and limitations of various concepts such as price elasticity, breakeven analysis, ratio analysis, price markups, and a myriad of other concepts applicable to marketing opportunities and problems. This paper reports an alternative form of decision game which the author believes has a number of unique advantages over the traditionally used programming language batch mode games - a conversational [5] spreadsheet simulation involving competition between one player and a programmed, synthetic environment. There is little question that the first, and still the primary, use of an integrated spreadsheet Is for financial applications such as budgeting, cash planning, and forecasting. n fact, advanced generations of spreadsheet software have concentrated on improving such features as formatting, word-processing, and integration with data bases; that is, features whose primary beneficiaries are the financial modelers. However, less obvious to the casual observer is the fact that most spreadsheet software can be used to analyze problems in disciplines other than accounting and finance [3]. Why Use a Spreadsheet? The speed and accuracy of the computer have great y changed the science of simulation modeling in recent years. It is now possible to build and use model of truly astounding complexity. However, to many faculty, the design of business simulations is a very complex aspect of programming that, while widely used,, is still regarded as a mysterious science. Simulations have been developed in all areas of science and technology [1] and there are specialized programming languages that can be used to write simulations [4]. Still, the process is not easily available to many nor-computer business faculty. But instructors can build customized simulation lessons quickly when they use the model presented in this paper. An attempt has been made by this author to offer an alternative format -the use of a spreadsheet in creating a marketing simulation. Spreadsheets possess some inherent advantages which are not available utilizing the typical methodology of our writing a computer program for processing decisions and producing simulation results. Perhaps one of the major advantages of a spreadsheet is the ease of installation of the simulations functions. The simulation writer does not have to learn a complex computer language. Using a spreadsheet, the writer enters the formulas as they might see them in a book or on a chalkboard (of course, using the reference cell language of the spreadsheet). Probably the biggest problem with using a general- purpose computer language for simulations is the complexity of the resulting software [6]. Like all programming tasks, writing a simulation is usually far more complex than it first appears and, sooner or later, most programmers feel the need for something easier. Spreadsheets, or. the other hand, provide the writer with a systematic approach in developing the simulation. Good worksheet structure conveys a sense of flow or progression. With a modular approach used in spreadsheet design, the writer can easily add functions and modify existing model assumptions. Finally, spreadsheets are becoming standard tools on the desks of many executives in the business community. Therefore, the spreadsheet environment watches the reeds of a manager or decision maker. Data is placed in tables, very much like the tables a manager might write on a sheet of paper. The results of the analysis can be readily printed, and can appear in the form of a table suitable for insertion in a report. That is, the spreadsheet conforms to the way managers handle data and information. Thereby, using the spreadsheet for simulations, students may be provided positive experience with the same type of software they are likely to find in the workplace. Simulation Worksheets by Design To indicate the potential of a spreadsheet simulation methodology, a conversational marketing simulation entitled MARKETING CONCEPTS, has been developed by the author. It appears to be a workable, pedagogical tool. The specific model from which this simulation evolved is based on a commercially available computer game called Paintco [21. The Paintco computer program permits a set of marketing decisions for the paint market (e.g. price, distribution, promotion, product quality, seasonality, for both the company and the market to be entered for a period of play and the computer then calculate their results and shows a series of reports. Several features of Paintco have been retained in the development of MARKETING CONCEPTS. The use of numerical levels (i.e. , I through 9) for data entry have been retained as an easily understood way of choosing a decision variable for data input and the formulas have been modified to reflect the laptop computer marker. Added to the spreadsheet simulation, however, have been a series of analysis modules which enable a student to explore several marketing concepts (e.g. price elasticity of demand, breakeven analysis, price markups, and selected ratio analysis components). The design and development of this spreadsheet decision game was primarily the result of finding simulation design fundamentals on a trail-and-error basis. Upon reflection on this simulation writing Developments in Business Simulation & Experiential Exercises, Volume 15, 1988 254 experience, the author has been able to develop a generalized model for writing conversational spreadsheet simulations. There are a number of specific design principles which a simulation writer should address when specifically designing a spreadsheet simulation. While probably not exhaustive, they include the following: Design Principle #1 Build a Simulation Worksheet Block by Block After determining what your simulation will accomplish, follow the pattern laid out in Figure 1 and block out the worksheet’s main sections in a vertical structure, from top to bottom: Data Input, Reference, Formulas, Results, Analysis, Menu Selection, and Macros. Figure 1 provides the blueprint for the MARKETING CONCEPTS simulation worksheet. The data input block is were the player enters the data that the worksheet will digest and use for the simulations calculations. This block contains the marketing mix decision variables of price, promotion, product features, and distribution. An example of this input is shown in Figure 2. Think of the data input block as a control panel. The player can change the decision variables to test “what if” conditions such as a decrease in price or an increase in advertising. A changed variable in this block, and the whole worksheet changes. Related to the data input block is a reference block which provides the user with a quick reference of the data input variables without having to go back to the instructions section of the simulation (See Figure 3). The formula block is entirely mechanical; the simulations formulas gather data from the input block and crunch away. The complexities of the equations and the interrelationships are illustrated in Figure 4. The results block is an abbreviated income statement which reports the bottom line of the students decisions. A complete illustration of the results is presented in Figure 5. It includes for the decision period in question, the units sold, the expenses incurred, the profit or loss, the total units sold in the market and the company’s market share. The analysis blocks permit the student to analyze in more detail their decisions. This block contains price elasticity of demand (See Figure 6 as an example), breakeven analysis, price markups, and selected ratio analysis techniques. Figure 7 illustrates the menu selection block which offers a variety of commands for use in moving around the simulation worksheet easily. Commands for moving to another section of the spreadsheet can be selected by using the computer’s right or left arrow keys which will move the cursor and highlight the desired command. Pressing the enter key at this time will move you to the desired section selected. Figure 8 illustrates the Macros used in the worksheet. The macro blocks should always be below and off to the side of the worksheet’s main activities. In Figure 1, for example, the macros for this simulation are fastened at row AA321 and 8B381. By setting up a worksheet with the vertical block model approach, each step in the worksheet will be visible and will support the one that follows it. In the MARKETING CONCEPTS simulation, the seven major blocks are made up of smaller, structurally similar blocks, which make it easier to root out errors in procedures or logic. This modular structure even makes it easier to print selected sections of the worksheet. Want a printout of the results section so that it can be compared with previous results, or you want a printout of one of the analysis sections? No problem; mark it and print it out. Good vertical block structure also really pays off when you have to modify the simulation worksheet, because it’s easier to recognize logically discrete elements, add new blocks, or reorder blocks. The Developments in Business Simulation & Experiential Exercises, Volume 15, 1988 255 Developments in Business Simulation & Experiential Exercises, Volume 15, 1988 256 worksheet will also make more sense from the start - both to you the author and to any one look over your shoulder - leaving you free to concentrate on the simulation objectives itself. Design Principle #2 Developing Block Structure Cast the worksheet’s general shape by entering labels for each block (such as Data Input, Formulas, Results, etc.). This will permit the user to know which section they are viewing. Also, for convenience’ sake, anchor the different blocks in the worksheet to easy-to-remember rows as A21, A41, and A81. Don’t put blocks side by side, because if you later insert rows in one block, you 11 encroach on its neighbor. Design Principle #3: Use Range Names to Minimize Reference Errors Although the worksheet’s formulas and micros can reference specific cells or labels, using range names simplifies matters. Range names should be applied to a cell, row, or entire block in a worksheet if it will minimize typing errors when creating formulas and macros. Most spreadsheet programs include a show function that lists all range names with their beginning and ending cells. Ranges should always be assigned to cells or rows that are used in calculations. Range names have been assigned to the equations in the formulas block. Design Principle #4: Protect Key Cells of the Worksheet When you create a worksheet simulation, all of the cells are unprotected; you can enter data in any of them. It's a good idea to protect the final version when you have unfinished building and modifying the simulation. This will prevent anyone from inadvertently modifying or erasing important cell contents. Most worksheet protection is global: it protects all the cells in the worksheet. Once global protection is enabled, you can then unprotect specific ranges or cells that you want to be able to access and change. Design Principle 5: Use Macro’s to Simplify Interaction with systems Any task that a spreadsheet can perform, from the simplest to the most complex, can be automated with a macro facility. A macro is made up of a series of steps, each of which represents an instruction to the spreadsheet. Regardless of how many steps a macro includes, it can be invoked with only two activating keystrokes. In MARKETING CONCEPTS, macros are used to move the user to certain locations within the spreadsheet, in developing the menu selection table, and to create a message screen to hide a macro activity (See Figure 8). Design Principle #6: Document, Document, Document! Documentation takes two forms: one for the simulation participant and the other for faculty members who would like to see the model and the various elements making up the simulation itself. You can display a description of the simulation scenario and the participant’s instructions on how to run the simulation in a number of way so that they are easy to find. One of the simplest techniques is to assign them to a “satellite” worksheet -a separate worksheet that passes to the main worksheet after it is used. In MARKETING CONCEPTS, the satellite worksheet is automatically loaded without operator intervention. Every rime the spreadsheet (Lotus 1- 2-3 or VP Planner) starts up, it searches the default disk drive for a file named AUTO123.WKS. Developments in Business Simulation & Experiential Exercises, Volume 15, 1988 257 When the file is found, it is automatically retrieved and the simulation scenario and instructions are displayed on the screen. To make the reading of this material as easy as possible, you should assign material to a full-sized-screen area only and tell the viewer to leave the cursor in the Al cell and use the PgDn key to move to another window. After reviewing the scenario and instructions the user can either recycle the scenario and instructions or pass to the data input section of the main worksheet to begin the simulation. In the satellite worksheet the user manual is completely written down as to all the fine points and details. An accurate and clear description must be made for all simulation data necessary to make appropriate decisions. Therefore, charts, tables, graphs, etc., would be helpful In presenting and understanding simulation data. An illustration of a typical table used throughout the introduction is shown in Figure 9. After the detailed explanation has been digested, the user can often refer to a summary table in the reference block. Also, make some room for documentation throughout the worksheet. Just as an article summary helps to guide readers, worksheet documentation helps subsequent users find the information they need to add or modify the simulation model. For example, a “table of contents” that lists the function of each of the models formulas is a handy guide. It also makes sense to note macro key combinations and describe what each macro does. Summary MARKETING CONCEPTS is an integrated spreadsheet based simulation designed for the introductory marketing course. Its Intended role is as a complement to the typical course format of text assignments, lectures, discussions, cases, and so forth. As such, it embodies the major marketing concepts to familiarize students with basic decision alternatives, and the interaction among marketing mix variables vis-à-vis the marketing environment. The simulation Is still under development, and it is difficult to judge its effectiveness at this time. It is expected to require at least 6 months to test the game in the classroom and make appropriate modifications on it, Design and development of a marketing simulation at best is a challenging and time-consuming task. However, an integrated spreadsheet provides some unique opportunities for simulations. The availability of Developments in Business Simulation & Experiential Exercises, Volume 15, 1988 258 such simulations opens the door for many instructors to develop and use simulations as a pedagogical technique. One no longer needs to be an expert in computer programming to develop simulations. As an instructor becomes familiar with the principles presented in the paper, the time involved in creating models for conversational simulations can be greatly reduced. There are many other dynamic systems, discrete and continuous, that one can simulate with a spreadsheet. I have tried to show how a spreadsheet can be used to model a marketing system, which demonstrates that a spreadsheet can be used for much more than financial analysis. Overall, the implementation of a spreadsheet simulation is technically impressive and has the potential to be a valuable pedagogical tool when applied in the right situations. REFERENCES [1] Bronson, R. “Computer Simulation,” BYTE. Vol. 9 Number 3, 1984, pp. 95-102. [2] Galloway, John Jr., Joel R. Evans, and Barry Berman. Paintco: A Computerized Marketing Simulation. (New York: Macmillan Publishing Company, 1985) [3] Matheny, Art. “Simulation with Electronic Spreadsheets,” BYTE. March, 1984, pp. 411-414. [4] Pratt, C. A. “Going Further: A Compendium of Conferences, Organizations, Books, and Software for Simulationists,” BYTE. Vol. 9, Number 3, 1984, pp. 204-208 15] Sewall, Murphy. “A Conversational Marketing Decision Game," Decision Sciences. Vol. 7, Number 2, April, 1976, pp. 358-365. [6] Young, Jan B. “A Grand SLAM for Simulation,” PC Magazine. March 19, 1985, pp. 148-161. Table of Contents Volume 15, 1988 The Role of Experiential Knowledge and Human Information Processing in Decision Making A Semantic Differential Instrument to Evaluate Experiential Teaching Methods A Comparison of Two Approaches to Management Skill-Building in an Organizational Behavior Course: A Replication Integrating Simulations: A Model for Business Policy Success Capstone Renaissance = Simulation + Interaction + DSS A Hybrid Method of Executing a Management Simulation: Combining the Best of Mainframes and Microcomputers Providing an Experiential Dimension to Cost/Managerial Accounting Courses Utilization of Computerized Tax Research Services in the Tax Research Curriculum Using and Expert System Based Decision Aid in Accounting Information Systems Event-Extended Entity-Relationship Diagrams for Understanding Simulation Model Structure and Function Multiple Objectives in the Development of the Gordon Macro Game A Comparative Study of Strategic Performance Factors in Actual and Simulated Business Environments An Empirical Investigation of Integrated Spatial-Proximity MCDM-Behavioral Problem Solving Technology Group Decision models Computer Simulation of Human Interaction The Role of Experiential Learning and Simulation in Teaching Management Skills Expert Systems - The New Business Simulation Tool Integrating Prolog into and Undergraduate Logistics Course Simulating Material Requirements Planning on Lotus 1-2-3 Innovation in Management Education: The Impact of the AACSB Experiential Learning in the International Environment Educational Testing with the Microcomputer A Simulation of Investment Analysis, Portfolio Management and Reporting Using Lotus 1-2-3 The Use of an Expert System to Develop Strategic Scenarios Two Exercises for Teaching about Motivation Sex Roles and the Good Manager A Form and Process for Nonconfidential Peer Evaluations Simulation and the Recalcitrant Student Employee Rights-Student Rights: A Classroom Exercise Computer Simulated Competition: An Alternative to Team Play Management Simulation The Relationship of Locus of Control and Vividness of Imagination Measures to Simulation Performance Formal Planning, Simulation Team Performance, and Satisfaction: A Replication Experimental Analysis of Magnitude and Source of Students' Inequitable Classroom Perceptions in Three Reward Conditions Strategy Design, Process and Implementation in a Stable/Complex Environment: An Exploratory Study Matching a Strategy Simulation to the Business Policy Literature: A Black Box Approach to Simulation Development An Evolutionary Classroom Experiential and Computer Simulation Model of a Corporate Strategic Planning System Collective Bargaining in the City of Elson: A Public Sector Experience Should Students Play Games in Labor Relations? Applying Cognitive Educational Objectives to Business Management Cases Grading as a Teaching and Feedback Mechanism: Involving Students in the Grading Process Teaching Controversies: A New Approach to Computer-Assisted Instruction and Simulation Packages Simulating Demand in and Independent-Across-Firm Management Game Advertising Response in the Gold and Pray Algorithm: A Critical Assessment A Model for Pricing Decisions in First Period Marketing Simulation Games Jog Your Right Brain: An Exercise for the Classroom and for Research Six Thinking Hats: An Exercise to Combat Confusion and Develop Thinking Skills Communicating in Context: A Simulation for Learning Business Communication A Simulated Consulting Service for the Compete Marketing Simulation Game Action Exams in the Consumer Behavior Class Using Focus Groups to Teach Problem Definition in Basic Marketing Research The Use of Journals in Management Simulations: A Literature Review and an ABSEL Response An Initial Step Towards Developing and Using an Expert System with a Business Simulation Self-Managed Learning: An Experiential Course Design Using the QWL Paradigm A Review of Current Developments in Experiential Learning Bring the Real World into the Classroom Assessing Student Performance on a Business Simulation Experience Minimizing Startup Anxiety: Case Studies of Simulation Experiences A Tale of Two Shepards Or Using Simulation in a Class Without Walls Enhancing Business Simulations Through the Utilization of Experiential Activities Involving Local Community Executives Simulation with Integrated Spreadsheets: The Design and Development of a Conversational Marketing Concepts Decision Game Introducing INMART: An International Marketing Simulation Using a Computer-Based Business Plan Assistant in Conjunction with a Marketing Simulation Game Overview of the ABSEL Guide to Experiential Learning and Simulation Learning