MGMT20005 Business Decision Analysis
Assignment 2: Group Assignment
Introduction: This is a group assignment of up to 3 students from the same tutorial group.
This assignment is designed to allow you to demonstrate that you can effectively analyse
business decision problems, apply mathematical modelling approaches such as Linear
Programming or Integer Programming to solve the problems. You must master Excel ‘Add-In’
features such as Solver to obtain the optimal solutions, generate a sensitivity analysis report,
and suggest courses of action for management.
Select one of the two problems: DHL Supply Chain, and CEL (see the accompanying pdf files).
For each problem, there are 75 groups of data for certain parameters (see the accompanying
Excel files). You can choose any one group of data for the implementation of your model.
Make sure you write down the group index of the data you choose on the front cover of your
Weight: 25% of the total grade.
Submission: Submit your reports via Turnitin. Only one submission is required for each group.
Make sure you include the names and student numbers for all team members!
Due date: 5pm, Friday October 25, 2019.
Late submissions will attract a marking penalty when approval for late submission has not been
given. The mark awarded will be reduced by 10% for each day the work is late. Assignments
submitted later than 7 days after the due date will not be marked and will receive no marks.
Word limit: 3000. The total length of the report (or executive summary) is a maximum of
3,000 words (excluding figures, tables, references, and appendices). The assignment must be
word processed in 12-point type and double-spaced.
Descriptions: Prepare an executive report that includes the following sections:
Introduction – description of the business optimization problem, etc.
Methodology – description and illustration of the mathematical modelling approach for
Implementation – formulation of the problem by using the LP or IP models, application
of the Excel Solver to optimize the mathematical model, and execution of the sensitivity
Discussions and Conclusions – suggestions for courses of action to the selected
company as well as the evaluation of the mathematical models that you build.
References (if any, and if so, please use APA referencing style)
Appendix (if required)
Marking Criteria: The table below lists the criteria against which your reports will be marked.
Criteria Possible Mark
Description of the problem to be analyzed 4
Description and application of LP or IP to analyze and solve
Suggestions for courses of action to the company, and critical
evaluation of the mathematical models you built
Structure and presentation. Use of appropriate language,
spelling, grammar, and punctuation
It is important to pay special attention to spelling, grammar, and punctuation in order to avoid
ambiguity and confusion. Students can include relevant graphs, tables, and other exhibits as
appendices. They must be clearly labelled and will not be included in the word count.
MGMT20005 Business Decision Analysis
Assignment 2: Group Assignment
Case Study 1 – DHL Supply Chain
(This case study has been adapted from a case by NUS and Ivey School of Business.)
A 2015 World Economic Forum publication declared, “Human activity generates annual
greenhouse gas emissions of around 50,000 mega tonnes CO2 (Carbon Dioxide emission). We
estimate that 2,800 mega tonnes of 5.5% of the total are contributed by the logistics and
The executive summary stated, “Significant movement is expected towards reduced supply
chain carbon intensity. This will create both opportunities and risks for logistics and transport
firms, with changes in supply and demand driven by regulation of carbon emissions, higher
and more volatile fuel prices and evolving consumer demand. The sector can play an influential
role in decarbonisation, both in its own operations and through broader supply chain
optimisation. This provides direct benefits through reduced costs, managed risks and business
It concluded with several recommendations for supply chain stakeholders. Among the six
recommendations for logistics and transport providers was to “switch (transport) modes where
possible.” For shippers and buyers, it was recommended to “plan to allow slower and better
optimised transport.” Finally, policy makers were also invited to “reflect the cost of carbon in
energy tariffs; support carbon measurement and labelling standards and build open carbon
After reading the report, Lun-Hsuan Yang, a member of the solutions team at DHL Supply
Chain, recognized the very findings he had uncovered in a recent analysis, undertaken as part
of the Go Green environmental protection program initiated by the parent firm, Deutsche Post
DHL. As a thought leader on sustainability in the industry, Deutsche Post DHL recognised
there were clear opportunities to begin resolving the carbon emission problems faced by many
of its customers. DHL even stated on its website, “We recognised environmental protection as
our responsibility as well as a business opportunity.”
Deutsche Post DHL was the first logistics company to set a quantified carbon efficiency goal
– to improve its CO2 efficiency across global operations by 30 per cent compared to the 2013
The exercise Lun-Hsuan undertook pertained to a consumer electronics company (CEC).
Prominent among its line of products were 32” and 42” LCD TV sets (LCD32” and LCD42”).
Production of the LCD TV sets was subcontracted to various original design manufacturers
(ODMs) located in China and Taiwan. The responsibility of DHL Supply Chain was to ship
the LCD TV sets from the ODMs to the distribution centre (DC) located in Shanghai. In the
latest contract, the CEC had allocated a budget of CNY 3 billion (Chinese Renminbi) for the
production and shipping of 700,000 units of LCD42” and 440,000 units of LCD32” TV sets to
its DC. Lun-Hsuan had worked with the CEC to configure the optimal supply chain that would
fulfil this order within the CNY 3 billion budget while satisfying various constraints pertaining
to economy of scale, production capacity, supplier risk management and service level
requirements on the shipping front. At that point, this optimisation exercise did not consider
the volume of CO2 emissions.
The CEC had a list of seven ODMs to which it could subcontract the production of LCD TV
sets. ODM1 and ODM2 were the only companies that could produce both LCD32” and
LCD42”. The remaining five ODMs produced LCD 42” exclusively. Their unit production
costs are listed in the data sheet (see Excel sheet ‘BDA DHL Supply Chain.xlsx’). To engender
economies of scale in the production, the CEC guaranteed a minimum order of 180,000 to any
selected ODMs. Also, to mitigate dependency risk on any ODM, the maximum order (with
each ODM) for either LCD32” or LCD42” was capped at 400,000 units. ODM1 and ODM2
had high production capacities, and if chosen, they each had the ability to produce up to
400,000 units of LCD32”, as well as up to 400,000 units of LCD42”.
Several transportation modes were available to ship the TV sets from the ODMs to the DC:
regular air, air express, road, road LTL (less than truckload), road network, rail and water. The
distances from the ODMs to the DC and the various shipping rates are tabulated in the Excel
data sheet. ODM5 was located near the DC, restricting shipping to road, road LTL or road
network. ODM6 was located in Taiwan and shipping could only be conducted via air or water.
Across shipping modes, the rates of carbon emission in kilograms (kgs) varied greatly from
regular air to water per ton shipped per kilometre (km) travelled (see Excel data sheet). Each
LCD32” weighed about 16.5kgs and each LCD42” weighed about 22kgs.
Shipping times varied from two days (via air express) to 10 days (via water). Based on
historical information on shipping times and customer order cycle times, the CEC decided that
to maintain satisfactory inventory levels, DHL supply Chain had to ship a minimum number
of 32” and 42” LCD TV sets, according to the criteria listed at the bottom of the Excel data
sheet. There was no constraint on shipments via water.
Lun-Hsuan also had to take into account of some additional OMDs pre-existing contractual
arrangements with shipping the TV sets from the ODMs to the DC. For example, these
arrangements were: at least one of ODM 5 or 7 must be used to ship the LCD 42”. And, if
ODM 3 or ODM 4 was used, then ODM 6 must also be used.
In the exercise, Lun-Hsuan assumed a likely consequence of government legislation to reduce
emission of CO2 would appear in the form of a tax incentive. He also anticipated the brand
value of the LCD TV sets could rise as a result of customer awareness. Lun-Hsuan estimated
these factors could translate into a 10 per cent increase in the budget for this specific supply
chain. He was eager to find out the maximum potential reduction in CO2 emission made
possible through a potential CNY 3.3 billion budget for manufacturing and shipping the TVs
from the ODMs to the DC.
Lun-Hsuan has come up with a few questions that he wants answers to and this may help you
with your analysis and discussions of this problem.
1. Demonstrate how the original supply chain can be optimised on a budget of CNY 3
2. Evaluate the extent of the reduction in CO2 emission if the budget were increased by
10% to CNY 3.3 billion.
3. What are some other interesting observations and findings you can uncover?