Faculty
Professor Kenneth R. Baker
Objectives
Course Description
A decision support system (DSS) provides organizations and individuals with informative analyses that enhance decision making. A DSS is made up of a model (or models), a source of data, and a user interface. When a model is implemented in Excel, it is possible to use Visual Basic for Applications (VBA) to make the system more efficient by automating interactive tasks that users would otherwise have to repeat routinely. VBA can also make the system more powerful by extending the functionality of a spreadsheet model and by customizing its use. This course covers the programming and design skills needed to build spreadsheet-based decision support systems, and it includes a project experience for each student.
Why would an MBA student be interested in this course?
The material in this course extends problem-solving capabilities acquired in the Decision Science course and elsewhere in the curriculum. Whereas the typical treatment of a problem-solving technique would lead to a stand-alone model based on a given data set, the DSS approach makes it possible to integrate different model types, to insulate an unsophisticated user from the details of the analysis, and to tailor the models to the specific needs of a decision-making client. This capability is especially important when the client is not comfortable with Excel or when the underlying model needs to be protected from the ultimate user. The student will discover how to design a communicative user interface that will link data, models, and reports with each other and with decision-making needs. Ultimately, the design and implementation of decision-support activities represents a valuable capability in consulting and staff support positions in industry.
Requirements
Materials
Text
S. Christian Albright, VBA for Modelers, Brooks/Cole (2007, Second Edition).
Supplementary References
M. Seref, R. Ahuja, and W. Winston, Developing Spreadsheet-based Decision Support Systems, Dynamic Ideas (2007).
An alternative, which was used as a text in 2007, but only about a third of the book supports the course. Part I reviews Excel. Part II supports the course. Part III contains some advanced material and a set of case exercises.
Master Visually Excel 2003 VBA Programming by Julia Kelly. Wiley. 2005
A recipe-styled reference book, composed of short “How To” chapters.
Safari Books Online (must be coming from Dartmouth IP address, e.g. on campus or VPN): http://proquest.safaribooksonline.com/
Programming Excel with VBA and .NET by Steve Saunders, Jeff Webb
http://proquest.safaribooksonline.com/0596007663
This is a good basic VBA book that you can access online. This apparently replaces "Writing Excel Macros" by Steven Roman which was published by O'Reilly several years back and was a very good reference.
Excel Scientific and Engineering Cookbook by David M. Bourg, http://proquest.safaribooksonline.com/0596008791
This is not specifically a business-oriented book but it has a lot of chapters organized as "recipes" that might answer "real world" things that you want to do with VBA, e.g. importing data from the web or MS Access and doing a lot of different types of calculations. The last chapter is about financial calculations.
Grading
This course meets once a week. During part of the second half of the course, students will be working on projects and will meet with the instructor by appointment to discuss progress. Homework assignments are due at the start of each class, including the first. The course grade is based on the homework assignments, in-class exercises, and the project.
Schedule
March 26
Class #1: Introduction to VBA and the Excel Object Model
Ch 1-3. The VBA environment
Ch 4. Macros
Homework Due: HW 1.1-3
April 2
Class #2: Introduction to Programming
Ch 5. Variables, Objects, Formulas
Ch 6. Ranges
Ch 10. Subroutines and functions
Homework Due: HW2.1-4
April 9
Class #3: Using VBA to Manage Data
Ch 7. Controls: conditional statements and loops
Ch 8. Collections and other objects
Ch 9. Arrays
Homework Due: HW3.1-5
April 16
Class #4: User Interfaces
Ch 11. User forms, error-checking, and protection
Homework Due: HW4.1-3
April 23
Class #5: Libraries
Ch 17. Controlling Excel’s Solver
Homework Due: HW5.1-4
Homework Due (4/30): HW6.1-2
April 25 - May 20
Classes #6-8: Student Projects
Meetings with the instructor to be arranged.
May 21
Class #9: Project reports
