Excel and writing required

Section AAnswer all questions in this section.
Question 1
Johnny Chew recently accepted an offer from SUSS Business School. He is now planning his
course schedule for the upcoming academic year. The academic year at SUSS is divided into
two semesters, and Johnny has a wide range of courses to choose from. He has shortlisted
twenty potential courses that align with his interests and are beneficial for future job skills. His
interest in each course is rated on a scale of 3 to 5, while the relevance to job skills is rated
between 1 and 10, as indicated in the fifth and sixth columns of Table 1.
Johnny is allowed to take at most five courses in each semester. In determining his course
schedule, Johnny needs to consider the following:
•
Johnny can only take a course if he has completed or is concurrently taking all courses that
are prerequisites for the course. The prerequisites for all twenty courses are shown in the
fourth column of Table 1.
•
In the upcoming Jan semester, Johnny must take at least three of the following five courses:
Quantitative Methods (course 1), Business Application & Modelling (course 2), Economic
Theory (course 3), Data Mining I (course 6), and Business Communications (course 20).
•
If Johnny takes Text Mining (course 8), he will not be allowed to take Natural Language
Processing (course 14), because these two courses cover fairly similar contents.
•
Johnny would like to take at least one course in Digital Marketing (course 12 and/ or 13)
and at least one course in Supply Chain Management (course 10 and/ or 11).
Course
Index
Subject
Semester
Prerequisites Interest
Level
1
Quantitative Methods
Jan
5
7
2
Business
Applications & Modelling
Jan
5
6
3
Economic Theory
Jan & July
4
3
4
Modern Finance
Jan
4
4
5
Fintech
July
3
4
6
Data Mining I
Jan
3
8
7
Data Mining II
July
2, 6
3
8
8
Text Mining
Jan
1,3
5
7
9
Statistical Methods
July
1
4
5
10
Supply Chain Management I
Jan
4
4
11
Supply Chain Management II
July
4
3
12
Digital Marketing I
Jan
3
4
13
Digital Marketing II
July
3
4
14
Natural Language Process
July
5
7
15
Information System I
Jan
4
6
16
Information System II
July
15
4
6
17
Financial Leadership
July
4
4
3
18
New Product Development
July
10, 12, 17
3
7
19
Web 3.0 Organizations
Jan
4
3
3
20
Business Communications
Jan
5
5
4
1, 10
9, 12
3
Table 1. The listed courses Johnny is mostly interested
Job
Skill
Level
Question 1a
Suppose that Johnny’s overall objective is to maximize his total interest level. Formulate a
discrete optimization model that can be used to determine Johnny’s optimal course schedule.
You answer should include the following:
Will need to provide the relevant files
•
•
•
Determine the objective of the proposed model;
Define the necessary decision variables;
List all the constraints and give the explanation.
(40 marks)
Question 1b
Construct a spreadsheet model for your formulated optimization model in Q1(a) using the
Excel solver to solve the optimal solutions. (Present the screenshots of the spreadsheet model
and solution as part of your answer.)
Will need to provide excel file
(20 marks)
Question 1c
Try to amend your model in order to generate a different solution with the same (optimal)
objective value as in Q1(b).
Will need to provide excel file
•
What is the other optimal course schedule?
•
Based in the given context, which solution should Johnny use? Justify your answer.
(10 marks)
Question 1d
While selecting courses based solely on interest levels might seem ideal, it’s often crucial to
also consider the relevance of the skills gained for future job prospects. Balancing multiple
objectives, such as personal interests and the acquisition of job relevant skills, is a more
realistic and pragmatic approach. In reality, people often employ various methods to
incorporate and balance these multiple objectives. This discussion invites you to propose two
distinct methods commonly used for integrating multiple objectives in course selection.
Additionally, please describe the necessary steps and considerations for effectively
evaluating this multifaceted decision-making process.
(30 marks)