October 2000 Hands-On Learning Experience Teaching decision-support systems using VBA By Christopher Zobel, Cliff Ragsdale and Loren Rees The Management Science and Information Technology (MSCI) Department at Virginia Tech teaches a core-level MBA course on the design and implementation of computer-based decision-support systems using Microsoft Excel and Visual Basic for Applications (VBA). Through the use of these tools, the course is able to address the learning needs of students with both technical and non-technical backgrounds, and it provides students with a practical, hands-on learning experience that can directly benefit them in their future careers. Background issues MSCI 5474: Computer-Based Decision-Support Systems is a first-year MBA course in the Pamplin College of Business at Virginia Tech. Taught through the department of Management Science and Information Technology, this has traditionally been a Microsoft Excel-based course in which students learn to use the included functionality of Visual Basic for Applications (VBA) to create custom business applications for decision-making support. Although many of the students enrolled in MSCI 5474 have a strong technical background, with undergraduate degrees in engineering or the sciences, there are typically quite a few who have very little computer experience or knowledge. Many of these students are simply looking to broaden their skill set as they pursue a concentration in an area such as finance or management. However, an increasing number of are responding to the growing popularity (and employability) of information technology as a career choice and are intending to concentrate in that area. The differing perspectives that these non-technical students bring to the class are refreshing and lead to a more dynamic learning environment. Yet, the disparity between their computer experience and that of other students can make it difficult to properly address every student's needs — particularly with typical class sizes of 50 students or more. The primary objective of the DSS course is to provide an opportunity for future managers at all technical levels with hands-on experience in creating and managing a decision-support system, and thus to gain a better idea of the work involved in doing so. The choice of VBA and Microsoft Excel not only supports this objective by providing appropriate tools to support the development of the different components of a computer based decision-support system, but it also establishes a common reference point among students of varying degrees of technical proficiency. Due to the prevalence of Excel as a desktop application in both industry and academia, there is a level of familiarity with the package that eases transition into the course for students. This allows them to begin the development (and learning) process fairly quickly, even if they have minimal previous computer programming experience. One issue associated with teaching both IT and OR is the occasional need for students to learn a specific software package in order to solve a problem or run a given analysis. Very often, when these students enter the working world they no longer have access to this particular software and must adjust appropriately. While a partial solution to this problem is to teach the process rather than the tool, there nevertheless are many advantages associated with a working knowledge of an effective problem-solving tool. By teaching our students decision-support systems within the context of the Excel/VBA development environment, we are providing them both with the process and with a tool that is readily available in the business world. Excel as a DSS generator Most definitions of a decision-support system include three main elements: a user interface, a modeling component and a database component. The inherent functionality of VBA with Excel provides the tools necessary to generate each of these three subsystems for a given DSS. User interface. In and of itself, Excel has a surprising amount of functionality available with which to create an attractive, useful user interface. By changing the color of different ranges of cells, hiding gridlines and sheet tabs, and using available toolbars for inserting different shapes and text styles, it is easy to completely transform the look of a given worksheet. It is even possible to add quite a bit of functionality to the interface by inserting simple controls, such as command buttons, from the forms menu and then recording and assigning macros to these controls in order to perform different tasks. Excel also has a well-defined charting and graphing capability that can present the results of any models that may have been run. The addition of VBA extends these capabilities not only by providing access to ActiveX controls, which have a much higher degree of functionality than the older forms controls, but also by providing access to user forms for collecting and displaying information. Given an understanding of VBA, it is straightforward to dynamically manipulate the properties of both ActiveX controls and user forms, thus providing great flexibility in the design of the user interface. The ability to create subroutines also allows a developer to automate the process of creating and implementing customized menus and command bars. Replacing standard Excel toolbars with customized menu bars designed for a specific DSS can significantly improve the professional appearance of an application created in Excel. The user interface is typically the first of the three DSS components introduced to students in MSCI 5474. In writing simple VBA subroutines to change the appearance of the Excel interface, students with little or no programming experience visually observe the behavior of different programming constructs. This can significantly ease their transition into creating other types of subroutines for performing more complicated functions. Figure 1: Sample Excel user interface. Modeling component. A primary strength of using Excel as the platform for generating decision-support systems is that it was specifically created to handle numerical data manipulation. Excel has a large number of built-in functions for statistical and numerical analysis and provides the flexibility, together with VBA, to create new algorithms and models based on these functions. Add-in programs such as Crystal Ball for simulation and Solver for mathematical programming also extend the inherent capabilities of Excel. In addition, the introductory management science course at the MBA level utilizes many of these spreadsheet approaches in the teaching of OR concepts. From a pedagogical standpoint, this gives students who have taken this course the opportunity to see the practical application of many of the models and solution approaches about which they previously learned. Database component. Although Excel is naturally able to store tables of data, it does not provide the functionality of a relational database. With access to VBA, however, establishing a direct connection is possible between Excel and any ODBC compliant database management system (e.g., Microsoft Access). Querying an external database from within Excel allows the import of requested information using the functionality of the database engine, and then the display of such information using the functionality of Excel. This gives students experience in manipulating data from a database and provides general experience in accessing and manipulating remote information. The PivotTable object is an excellent example of the built-in data processing capabilities of Excel. As with much of the higher-order functionality of Excel, the PivotTable has a "wizard" that walks the user through the process of establishing a connection to a database, then displaying the resulting information in a dynamic, user-defined format. The ability of the user to choose the format within which to display the data satisfies a fundamental precept of decision-support systems: a good DSS supports decision-making by providing the decision-maker with the tools to examine the different options available to the user, not by directly providing a "solution" to a problem. Pedagogical approach Beyond the choice of a technology that helps the learning process for students with less programming experience, there are some fairly straightforward pedagogical approaches incorporated into MSCI 5474. With large class enrollments, it is simply not possible to directly address every student's needs within the context of a given lecture. Therefore, the decision was made to shift part of the educational burden onto the shoulders of the individual students. Advanced students are given supplemental readings that go into more detail on many class topics. If students choose to utilize this resource, they improve their skill set at their own pace and with the support of the instructor. A similar opportunity is provided for those students with a weaker technical background. In addition to regular homework assignments, for which all students in the class are responsible, non-graded practice homework assignments are offered which address some of the more fundamental class materials. This gives students the opportunity to practice their skills and receive feedback from the instructor without worrying about how their efforts will affect their course grade. An additional approach to empowering student control over the learning process is the inclusion of a semester-long course project in which students build a working decision-support system from scratch. By allowing students to choose their own topic for the project (subject to approval), they can pursue a topic of personal interest. Although some students simply choose to build a standard textbook DSS, others are quickly able to see how their acquired skills are directly applicable in an area of individual importance. To take advantage of the benefits associated with a collaborative learning environment, course policy has traditionally allowed students to work together in teams on homework assignments and the class project. An obvious concern with this approach, particularly in a course that requires computer programming, is that a single student will sometimes complete the bulk of the work on a given assignment, with the other group members contribute little or nothing at all (and thus shortchange their learning process). One way to control this potential work (and learning) disparity is to require each student to submit their own copy of an assignment. Although this seems like a very minor step, it at least forces students to assume partial ownership of the final submitted product. Another possible solution is the use of peer evaluations that directly impact a portion of each group member's individual grade. Generally, these are quite effective, although occasional personality issues may skew the results, making it difficult to judge the true contributions of each team member. By restricting the evaluation's impact to only a percentage of each student's grade, however, it is possible to provide a fair assessment of each student's contributions. Varying student backgrounds can cause difficulty in assigning grades to group projects. In particular, grading on an absolute scale can be unfair to the less experienced students, since those students with a substantial programming background often produce nearly professional-quality projects. One approach to resolving this issue is to use a relative scale based on a student's improvement over the course of the semester rather than the absolute quality of their final project. The difficulty in this is, of course, capturing the initial quality of the students' work. A preliminary assessment is possible by having students complete a questionnaire at the beginning of the semester detailing their programming and analytic background. Students' work on the initial homework assignments can then help support this information and provide a basis for making judgment improvements in abilities at the end of the semester. As with any subjective measure of performance, there are concerns with the accuracy of this approach. However, through numerous assignments and interaction with students during office hours it should be possible to make an informed judgement about improvement in individuals' performances over the course of the semester. MSCI 5474 is a popular course, not just within the business school, but also among students from other disciplines. Its practical focus and accessibility to people with a wide range of backgrounds give it the potential to offer a valuable learning experience to many different types of students. Although addressing the needs of these students can make it a difficult course to teach, it is rewarding to teach, as well. Based on the experience of several semesters, we believe MSCI 5474 does an effective job not only of introducing the concepts involved in creating decision-support systems, but also of exploring the use of a valuable tool to which students will have easy access as they pursue careers after graduation. References Authors' note: We have been unable to find a textbook that, in and of itself, satisfies our needs concerning the development of decision-support systems using Excel and VBA. A variety of other references exist that can be used as supporting material for an Excel-based DSS course. The following is a partial list of such resources: Reed Jacobson, "Microsoft Excel 2000 VBA Fundamentals," Microsoft Press, 1999. John Walkenbach, "Microsoft Excel 2000 Power Programming with VBA," IDG Books, 1999. Peter Aitken, "Developing Solutions with Office 2000 Components and VBA," Prentice-Hall, 2000. "Microsoft Office Programmer's Guide," (http://www.microsoft.com/officedev/articles/Opg/toc/PGTOC.htm). Christopher Zobel, Cliff Ragsdale and Loren Rees are faculty members of the Department of Management Science and Information Technology, Virginia Polytechnic Institute and State University in Blacksburg, Va. Table of Contents OR/MS Today Home Page OR/MS Today copyright © 2000 by the Institute for Operations Research and the Management Sciences. All rights reserved. Lionheart Publishing, Inc. 506 Roswell Street, Suite 220, Marietta, GA 30060, USA Phone: 770-431-0867 | Fax: 770-432-6969 E-mail: lpi@lionhrtpub.com URL: http://www.lionhrtpub.com Web Site © Copyright 2000 by Lionheart Publishing, Inc. All rights reserved.