MS3111 Quantitative Business Analysis with Visual Basic for Applications
Part ICourse Intended Learning Outcomes (CILOs)
Course Duration : One Semester
Credit Units : 3
Medium of Instruction: English
Prerequisite(s) : Nil
Precursors(s) : Nil
Equivalent Course(s) : Nil
Exclusive Course(s) : Nil
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.
Upon successful completion of this course, students should be able to:
|No. ||CILOs ||Weighting |
|1 ||Demonstrate knowledge of general programming logic and demonstrate skills in using VBA as a programming language. ||25% |
|2 ||Manipulate common Excel objects such as ranges, workbooks, and worksheets using VBA programs. ||25% |
|3 ||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) ||25% |
|4 ||Design and develop business operation or optimization applications using the techniques learnt in other modules (such as MS3103 Resource Allocation Techniques). (Ability + Accomplishment) ||25% |
Teaching and Learning Activities (TLAs)
(Indicative of likely activities and tasks designed to facilitate students’ achievement of the CILOs. Final details will be provided to students in their first week of attendance in this course)
|CILO No. ||TLAs ||Hours/week (if applicable) |
Lecturer explains the programming logic and demonstrates VBA programming syntax.
|1,2,3 ||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.
|1,2,3 ||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.
|1,2,3,4 ||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 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 ||Hours/week (if applicable) |
|CILO 1 ||ü ||ü ||ü ||ü ||- |
|CILO 2 ||ü ||ü ||ü ||ü ||- |
|CILO 3 ||ü ||ü ||ü ||ü ||- |
|CILO 4 || || || ||ü ||- |
(Indicative of likely activities and tasks designed to assess how well the students achieve the CILOs. 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 ||Type of Assessment Tasks/Activities ||Assessment Details ||Weighting (if applicable) |
|1,2,3 ||1. Assignments ||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. ||15% |
|1,2,3 ||2. Tests ||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. ||20% |
|1,2,3,4 ||3. Project ||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. ||65% |
Constructive Alignment of CILOs and Assessment Tasks
| ||AT1 ||AT2 ||AT3 |
|CILO 1 ||ü ||ü ||ü |
|CILO 2 ||ü ||ü ||ü |
|CILO 3 ||ü ||ü ||ü |
|CILO 4 || || ||ü |
Grading of Student Achievement :Refer to Grading of Courses in the Academic Regulations (Attachment) and to the Explanatory Notes.
|Letter Grade ||Grade Point ||Grade Definitions || |
|Excellent: ||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. |
|Good: ||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. |
|Adequate: ||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. |
|D ||1.0 ||Marginal: ||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. |
|F ||0.0 ||Failure: ||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. |
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.
Department of Management Sciences