AN EXCEL WORKBOOK FOR STUDENT PLANNING AND INTERFACE WITH A SIMULATION GAME Developments in Business Simulation and Experiential Learning, Volume 25, 1998 AN EXCEL WORKBOOK FOR STUDENT PLANNING AND INTERFACE WITH A SIMULATION GAME Lee Tangedahl, University of Montana ABSTRACT This paper describes an Excel workbook (i.e. a spreadsheet model), with Visual Basic modules, which has been developed for use in a simulation game course. This Excel workbook emphasizes student planning in the simulation game but also used to introduce students to the simulation game and to provide a convenient interface with the simulation game. INTRODUCTION Planning is perhaps the most critical element in successful simulation game playing and a spreadsheet model is the obvious planning tool. While some students may be capable of creating their own spreadsheet planning models, all the students in the class can be “jump started” in a simulation game by providing them with a prewritten spreadsheet planning model. First, the spreadsheet mode! can be used to introduce the students to the simulation game by familiarizing them with the variables of the game and allowing them to observe interactions between the variables. Second, the students can manipulate the decision variables in the spreadsheet model to search for optimal decisions to submit for the upcoming period in the simulation game. Third, a simple press of a button in the spreadsheet model creates a decision file that is sent to the instructor to be read into the simulation game software. The first section of this paper briefly describes the simulation game and the course in which the Excel workbook is used. The second section describes the Excel workbook in some detail and the third section describes the typical use of the workbook. The last section briefly discusses the observations from the use of the Excel workbook. THE SIMULATION GAME AND COURSE The Excel workbook is used in a semester capstone course in which student teams compete during in a 4-year (16-quarter) simulation game. The simulation game is a two product manufacturing game with 29 decision variables including production, marketing and financial decisions. The game has been created and used for many years at the University of Montana. The students are graded each year on written planning reports, the performance of their company in the game, and year-end PowerPoint presentations. The course emphasizes teamwork, planning, oral and written communication. The course provides students with experience in applying the knowledge from previous courses, in using technology, and in meeting deadlines. THE EXCEL WORKBOOK The workbook is written in Excel 97 with Visual Basic program modules. The workbook is designed to plan for the four quarters in one year. The workbook includes worksheets for instructions, constants, history variables, environmental and uncontrollable variables, decision variables, output computations, and formatted financial statements. The constants worksheet includes system variables (section number, team number, team name, file path, and current year), upper and lower limits for decision variables, and the game constants. The output computations worksheet has formulas for about 200 output values and ratios for each period and totals for the year. The Visual Basic modules, run by pressing button controls, create decision files, select data to be displayed in the formatted statements, and select data to be printed. 108 Developments in Business Simulation and Experiential Learning, Volume 25, 1998 USING THE WORKBOOK There are six steps performed by student teams in using the workbook: 1. Enter system variables 2. Enter initial history variables 3. Enter environmental variables 4. Enter uncontrollable variables 5. Enter decision variables 6. Create decision file The first three steps are straightforward. Step 4 requires estimation of the uncontrollable variables (demand, productivity, credit rating, and stock quotations) using historic data, knowledge of business concepts, and ingenuity - plus a little guesswork. The more successful teams will also compute error estimates for more accurate best- worst case scenarios. The uncontrollable variables are then manipulated along with the decision variables in step 5. To determine the optimal set of decision variables, students will typically open the decision variables in one window and open the output calculations in a second window and perform “what if” analysis. Following are three examples of this process. (1) To optimize the production schedule, demand can be set to “most likely” values and then production quantities can be manipulated to observe the trade off between idle time, overtime, carrying costs, and lost sales. (2) To minimize the cost of operating capital, the uncontrollable variables can be set to “worst case” values and then short term financing variables can be manipulated to minimize the interest expense. (3) To maximize the return on marketing expenditures, estimated demand can be set according to various combinations of price and marketing efforts to determine the marketing plan with the highest ratio of sales revenue to marketing expense. Once the optimal decision variables have been determined, a decision file is created by pressing a button control on the decision variable worksheet. An error message will appear if any of the decision variables are out of range. The file created can be submitted via email, ftp, or Internet browser. OBSERVATIONS The Excel workbook, used in conjunction with written annual planning reports, has definitely resulted in better planning by the student teams leading to a more competitive game. First, “catastrophic” mistakes have become extremely rare. Second, if the uncertainty and number variables are small, competition in the game will be very close because all the teams are able to optimize their decisions. Third, as the uncertainty and number of variables increases, teams must make greater use of the planning model in order to become more successful. It is important that the spreadsheet planning model be used in conjunction with a formalized planning process and written planning reports. The students have also gained invaluable experience in manipulating a large spreadsheet. They must be able to work between multiple worksheets, to open multiple windows, and 1.0 perform frequent “what if’ analysis. They can appreciate the power provided by all the formulas in a large spreadsheet as well as the danger created by entering a single incorrect number. On the other hand, there is the possibility that the students may become too reliant on the spreadsheet model. For example, students may accept numbers produced by the spreadsheet model without full understanding of the conditions imposed by the model. Or, students may neglect to utilize “traditional” optimization techniques such as breakeven analysis in favor of trial and error with the spreadsheet model Finally, the programmed creation of decision files may not seem like much to the students, but it means a lot to the instructor. The files have been validated by the Excel workbook and can be transmitted to the instructor via any network. 109 Table of Contents Volume 25, 1998 Marketing Goes to the Movies Bringing Experiential Learning to a Principles of Marketing Course Investment Analysis Application Using In-house Spreadsheet Models SugarCoated Statistics: An Exercise for the First Day of Class Improving Undergraduate Student Involvement in Management Science and Business Writing Courses Using the Seven Principles in Action Establishment and Funding for Interuniversity / Multidisciplinary Student experiences The Prospects of Creative Teaching: A Discussion with Patricia Sanders The Simulation and Classroom Assessment Techniques Developments of Management Skill Assessment Games as Instruments of Assessment: A Framework for Evaluation The Role of Artificial Intelligence in Business Curricula Threshold Solo Competitor: A Management Simulation (V1.0) a Windows-Based. Play Alone, Total Enterprise Simulation and Assessment Instrument Toward An Understanding of One's self-concept Total Enterprise Simulations and the Internet: Improving Student Perceptions and Simplifying Administrative Workloads The Expatriate an Assignment Orientation Game An Expatriate's Nightmare: An Experiential Exercise in Coping with Overseas Assignments Analyzing Experiential Exercise: Using the Scientific Method for Problem Solving The Second Component to Experiential Learning: A Look Back at How ABSEL has handled the Conceptual and Operational Definitions of Learning Predictive Models of Learning: Participant Satisfaction of Experiential Exercises in Business Education Accelerating Moral Development through Use of Experiential Ethical Dilemmas Ethical Dilemmas to use with Business Simulations to Teach Ethics The Class Approach in Behavioral Simulation in a Business Policy/Strategic Management course: A Progression toward Greater Realism An Exploration of the Emergence of Process Prototypes in a Management Course Utilizing a Total Enterprise Simulation How Organizations Are Improving Their Performance Utilizing Electronic Commerce: Examples From The Internet The Market Access Planning System (Maps): A Computer-Based Decision Support System For Facilitating Experiential Learning In International Business An Excel Workbook For Student Planning And Interface With A Simulation Game Design Of Multi-Media Based Pedagogy For Leadership Training Panel Discussion On Using The Internet For Courses Valuing And Enhancing Teaching: Sharing Tips Via The Web The Buddy Project: A Semester Long Project Aimed At Developing An Appreciation For Diversity Enhancing The Excitement And Learning Retention In The Classroom: The Power Of Magic The Supervised Management Internship: A Job Or Learning Experience Team Ware™ An Online Moderated Class Discussion Facility And Beyond A Neophyte Distance Educator's Experience Learning Management By Practicing Management: A Report Of Significant Student Service In 1997 Integration Of Academic And Service Learning: Students' Perceptions About Its Effects And Outcomes The Value Of Incorporating A Service Learning Component Into Course Content: A Presentation And Roundtable Discussion Business Games Teach: Thoughts on the Sources of Conflicting Conclusions on their Effectiveness Antecedents Of Learning In The Simulation: A Replication Using Student Journals To Enhance Learning From Simulations Technological Change And Intertemporal Movements In Consumer Preferences In The Design Of Computerized Business Simulations With Market Segmentation Integrating The Marketing Curriculum Using Collaborative Learning Teaching Time Management In A Sales Program: The Application Of A Computer Simulation Game Adapting Interactive Computer Simulations For Content Based Esl Instruction Multimedia And Student Expectations Synthesizing Data For Media Simulations Composing A Team Health Promoting Behaviors-A Decision Making Exercise Does it really Work? An Application of the Group Interaction Framework Administering the MIT Beer Game: Lessons Learned A Paperless Economy? Instructing Students on the Aspects of Successful Electronic Commerce Maximizing Learning Gains in Simulations: Lessons from the Training Literature Observing General Ability in a Total Enterprise Gaming Simulation FReach Teach: A Computer-Based System for Teaching Advertising Media Planning An Integrated Business Instruction System An Experiential Exercise you can Tinker With Experiential Exercises or Computer Simulations? Cash Flow Statements: Are They Important in Business Simulations? Holistic Cognitive Strategy in a Computer-Based Marketing Simulation Game: An Investigation of Attitudes Towards the Decision-Making Process Barnga: A Game on Cultural Clashes The Many Faces of Culture: Understanding Country and Corporate Culture Students' View of the Use of Business Gaming in Hong Kong Assessing General Management Interest What is the Future of Business Gaming? Starting a Small Music Trivia Business Exercise and Other Innovative Icebreakers An Integrated Approach to Behavioral Skill Development Career Focus: A Student and Business Learning Experience The Use of Concept Mapping in Teaching Strategic Management An Experiential Approach to Developing Mission Statements Business Games in Brazil-Learning or Satisfaction A Simulation within a Simulation: Job Layoff's and Emotional Reactions