Question 1 Merlion Holiday Pte Ltd (Merlion Holiday) is an outbound tour agency that focuses on tours in the United States, Europe and Australia. Business had been good in the last 5 years until the COVID-19 pandemic struck the world. With the closure of international borders, business for Merlion Holiday has ground to a complete halt. Mr Jack Chua, the CEO, had been frantically trying to save his business. It did not help that the local Government had also imposed Circuit Breaker lockdown restrictions.

Due to improving conditions, the Circuit Breaker was gradually lifted in phases. With local residents now allowed to move about and mingle more freely, Jack found a possible lifeline. Instead of relying on just outbound tours, he could re-focus his business on staycations for local residents! His idea was to put together a 3-day-and-2-night staycation package in which customers would stay and dine in hotels for 2 nights. In the day, they will be taken on guided tours to various attractions around the city state. Merlion Holiday would focus on promoting, selling and coordinating the packages. The actual tour and accommodation will be outsourced to various service partners, including tour guides, hotels and tour coach operators. Profit margins will be thin, but he can at least keep his business alive until the pandemic is over. At this point in time, he does not expect the staycation business to be viable over the long term, especially when normalcy returns. Thus, it makes sense to outsource most of the service delivery.

To develop this idea further, Jack has identified several service partners who are keen to partner with Merlion Holiday. Jack is now at the stage where he is planning a series of 6 staycations over 6 weeks. For each staycation, he needs one tour guide, one tour coach and one hotel to lead and host a group of 20 customers.

There is a pool of 6 associate tour guides who could be engaged on an ad hoc basis to lead the staycation tours. But they charged different rates. Guides 1 to 6 have quoted $380, $390, $400, $410, $420 and $430, respectively for each staycation in any of the 6 weeks. They are all available throughout the 6 weeks.

Six hotels are keen to partner Merlion Holiday. They are all able to supply up to 10 twinsharing rooms per staycation. Due to demand and supply conditions, the room rates are different in different weeks. Table 1 below shows the quotations from the six hotels.

Week	1	2	3	4	5	6
Hotel A	$100	$150	$200	$120	$110	$130
Hotel B	$110	$140	$170	$120	$105	$120
Hotel C	$115	$160	$205	$130	$120	$140
Hotel D	$120	$160	$200	$125	$120	$130
Hotel E	$125	$125	$125	$135	$120	$120
Hotel F	$130	$140	$150	$140	$130	$140

Table:1 Room Rates per Night

In addition, Hotels A, B and C charge a flat booking fee of $200 per staycation irrespective of the number of rooms booked. There is no booking fee for hotels D, E and F. Note that the booking fee per staycation is levied by the hotel on top of the room rates and is borne by Merlion Holiday.

Only 4 tour coach operators are willing to work with Merlion Holiday. They can all supply at least 1 coach during the 6 weeks indicated. Each coach can take 20 passengers and 1 tour guide with safe-distancing measures in place. Table 2 below shows the quotations from the 4 tour coach operators.

Week	1	2	3	4	5	6
Coach 1	$780	$780	$800	$800	$780	$800
Coach 2	$800	$800	$780	$780	$810	$810
Coach 3	$795	$800	$795	$795	$800	$800
Coach 4	$800	$790	$800	$790	$790	$790

Table 2: Tour Coach Charges Per Staycation

Jack is very excited that his idea is coming to fruition. As he plans the details of the staycation programme, he needs to make sure that he can deploy the partner resources cost efficiently.

(a) Construct a spreadsheet model in the form of a deployment table to help Jack choose a suitable tour guide, hotel and coach operator for each of the 6 weeks. Your model should compute the cost of each resource (tour guide, hotel, coach) engaged and the total cost of the 6-week deployment plan. To ensure that there is no accidental overdeployment, your model should also calculate the number of resources engaged per staycation.

Table 3 below shows a possible structure for the deployment table

Table 3: Deployment Table Structure

The blue cells are input cells used for selection. One staycation is scheduled for each week. A resource (Guide, Hotel or Coach) is selected by inputting 1 into the appropriate blue cell. You need to apply conditional formatting so that when a resource is selected, the corresponding cell changes from blue to yellow.

Hint: The cells for Guide, Hotel and Coach Counts should be dynamically computed as resources are selected. The values in Count and Cost are also automatically computed based on the selections made in the blue area.

What is the total cost of deployment if the resources are deployed to satisfy ALL the three (3) conditions stated below: (12 marks)

(i) Tour guides 1 to 6 are each deployed once for weeks 1 to 6, respectively;

(ii) Hotel E is used for weeks 1 to 3 while Hotel F is used for weeks 4 to 6; AND

(iii) Coach operator 4 is engaged for weeks 1 to 3 while coach operator 3 is engaged for weeks 4 to 6.

(b) Merlion Holiday wants to ensure that all coach operators are engaged at least once. No specific constraints are placed on Guides and Hotels. Obviously, each staycation package must involve exactly one hotel and one guide. Using Excel Solver with Simplex LP method, analyse the lowest cost of deploying all the resources over 6 weeks. How are the resources selected for each of the 6 weeks? (6 Marks)

(c) Show how will the assignments and lowest total cost change if the tour guides have the following constraints: Tour Guide 1 is not available in week 6 and can work at most 2 staycations over the other 5 weeks. Tour Guide 2 is not available in weeks 2, 3, 5 and 6. And Tour Guide 3 cannot work over 2 consecutive weeks. Tour Guides 4 to 6 are available for all weeks. Note that Merlion Holiday still wants to ensure that all coach operators are engaged at least once over the 6 weeks. (6 Marks)

Deployment Table Week 1 2 3 4 5 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 1 0 0 0 0 0 1 0 1 0 0 0 0 0 0 0 0 Guide1 Guide2 Guide 3 Guide4 Guide 5 Guide 6 Hotela HotelB Hotelc Hotels Hotele HotelF Coach1 Coach2 Coach3 Coach4 1 0 0 0 1 0 0 0 0 0 1 0 1 0 0 1 Resource Count Cost 0 $0 0 $0 $0 2. $820 4 $1,680 0 $0 0 $0 0 $0 6 $18,600 0 $0 0 $0 0 $0 2 $1,580 4 $3,170) $0 0 $0 Cost $25,850 1 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 1 1 0 0 0 1 0 1 0 1 0 0 0 0 0 0 0 0 0 1 1 1 1 Guide Count Hotel Count Coach Count 1 1 1 1 1 1 1 1 1 1 1 1 1 1