City University of Hong Kong Information on a Course offered by the Department of Management Sciences with effect from Semester A in 2012 / 2013
Part I Course Title: Quantitative Business Analysis with Visual Basic for Applications Course Code: MS3111 Course Duration: One Semester No. of Credit Units: 3 Level: B3 Medium of Instruction: English Prerequisites: Nil Precursors: Nil Equivalent Courses: Nil Exclusive Courses: Nil
Part II Course Aims This course aims to provide an introduction to create business decision support systems using Excel VBA programming language. Students can also use the knowledge learned from this course to develop applications in other areas such as statistical analysis, or financial modeling.
1
Course Intended Learning Outcomes (CILOs) Upon successful completion of this course, students should be able to: No. 1
2
3
4
CILOs Demonstrate knowledge of general programming logic and demonstrate skills in using VBA as a programming language. Manipulate common Excel objects such as ranges, workbooks, and worksheets using VBA programs. Create Excel user forms for simple tasks (such as forms embedded with OK and Cancel buttons) and complex tasks (such as selecting multiple items from a Listbox control). (Ability + Accomplishment) Design and develop business operation or optimization applications using the techniques learnt in other modules (such as MS3103 Resource Allocation Techniques). (Ability + Accomplishment)
Weighting
DEC-related dimension
25%
Ability
25%
Ability
25%
Ability + Accomplish ment
25%
Ability + Accomplish ment
Teaching and learning Activities (TLAs) (Indicative of likely activities and tasks students will undertake to learn in this course. Final details will be provided to students in their first week of attendance in this course.) CILO No. 1, 2, 3
1, 2, 3
1, 2, 3
TLAs 1. Lectures Lecturer explains the programming logic and demonstrates VBA programming syntax. 2. In-class activities In order to reinforce the logic and syntax taught, students are asked to create VBA programs for simple but realistic business problems in class individually. Feedback is given by the lecturer in class. Through these in-class exercises, the lecturer can identify the common problems that the students have and give more elaboration as needed. The students can also identify the kinds of mistakes that they have made and learn how to correct them. 3. Out-of-class assignments A key to successful computer programming is to come up with a logical solution for a complex realistic problem and then turn the solution into a useable VBA program. This is a time consuming process which it is not possible to do in class. Students tackle these complex business-related problems as out-of-class assignments. The students may work in small groups for these assignments so that they can discuss the problems, come up a solution, and create the program together. 2
4. Project The ultimate aim of the course is to provide students with the specialist knowledge and training to create a VBA OR/MS decision support system. Students are asked to develop one such innovation for a problem they have encountered in other modules or for a scenario specified by the lecturer. This is a semester-long activity. The students are asked to submit a plan in the early part of the 1, 2, 3, 4 semester so that the problem can be identified, and the proposed solution to the problem verified by the lecturer. The students need to make use of everything they have learned in this course in order to create the program. They are encouraged to form small groups for the project so that they can analyze the problems and create the program together. They can always seek help and advice from the lecturer during the semester. Constructive alignment of CILOs and TLAs TLA 1 TLA 2 TLA 3 TLA 4 CILO 1 CILO 2 CILO 3 CILO 4 Assessment Tasks/Activities (Indicative of likely activities and tasks students will undertake to learn in this course. Final details will be provided to students in their first week of attendance in this course.) This course is assessed 100% via coursework. CILO NO.
Types of Assessment Tasks (ATs)
1, 2, 3
1. Assignments
1, 2, 3
2. Tests
1, 2, 3, 4 3. Project
Assessment Details Small student groups may be formed for these assignments, which are designed to allow students to create VBA programs out of class for more complex business problems. The form of these tests will be similar to those described in the in-class activities above, but students cannot seek help from the lecturer or their peers, and they need to submit their programs within a specified time. The students are encouraged to work together in small groups. A project plan needs to be submitted in the early part of the semester. A project report will be submitted at the end of the semester.
Weighting
15%
20%
65%
3
Constructive alignment of CILOs and ATs AT 1 AT 2 AT 3 CILO 1 CILO 2 CILO 3 CILO 4
Grading of Student Achievement: Letter Grade
Grade Point
A+ A A-
4.3 4.0 3.7
B+ B B-
3.3 3.0 2.7
C+ C C-
2.3 2.0 1.7
D
1.0
F
0.0
Overall Grade Definitions Overall score is 76 or above out of 100. Demonstrated excellent ability to develop macros and OR/MS decision support system using all EXCEL VBA learned in lectures. Overall score is between 61 and 75 out of 100. Demonstrated good ability to develop macros and OR/MS decision support system using some EXCEL VBA learned in lectures. Overall score is between 46 and 60 out of 100. Demonstrated reasonable ability to develop macros and OR/MS decision support system using some EXCEL VBA learned in lectures. Overall score is between 41 and 45 out of 100. Demonstrated limited ability to develop macros and OR/MS decision support system using some EXCEL VBA learned in lectures. Overall score is 40 or below out of 100. Failed to demonstrate ability to develop macros and OR/MS decision support system using EXCEL VBA learned in lectures.
Part III Keyword Syllabus 1. Basics of VBA Macros; Simple VBA programs; Control logic and loops; Excel objects; Working with Range; Working with Workbook & Worksheets 2. User Forms Designing User Forms; Setting properties of controls; Writing event code for User Forms 3. Applications for Management Science Possible applications include blending application, product mix application, and minimum-cost network flow application. 4
Recommended Reading VBA for Modelers, Developing Decision Support Systems with Microsoft Excel, 3rd edition, S. Christian Albright. Duxbury. Microsoft Excel 2010 Programming By Example: with VBA, XML, and ASP, Julitta Korol. Mercury Learning & Information, 2011. Excel 2010 Power Programming with VBA, John Walkenbach. Wiley, 2010. VBA and Macros for Microsoft Office Excel 2010, Bill Jelen and Tracy Syrstad, QUE, 2010. Microsoft Excel VBA Programming for the Absolute Beginner, Duane Birnbaum, Course Technology, 2007. Microsoft Excel 2007 Visual Basic for Applications Step by Step, Reed Jacobson, Microsoft Press, 2007. Excel 2007 VBA Macro Programming, Richard Shepherd, McGraw-Hill, 2009. Professional Excel Development: The Definite Guide to Developing Applications Using Microsoft Excel, VBA, and .Net. Rob Bovey, Dennis Wallentin, Stephen Bullen, and John Green, Addison-Wesley, 2009. Pro Excel 2007 VBA, Jim DeMacro, Apress, 2008.
5
