MS980 Business Analytics
2019-20 Second Semester
GROUP PROJECT (OPTIMIZATION)
(Note this project is 20% of your overall class mark.)
MaximizeHealth, a data consultancy company developing data-driven predictive analytics and
decision making tools for a broad range of clients from healthcare industry, including governmental
organizations such as NHS and various pharmaceuticals, employs a number of technical experts,
including data scientists, optimization experts and statisticians, as well as management consultants
and a sales team.
MaximizeHealth has been getting heavily more involved in optimization-related projects than usual,
and although they are in the process of recruiting more optimization experts in-house, they have a
number of urgent projects with due dates approaching and hence they need your expertise in this
area. In particular, they are trying to address some distribution related challenges stemming from
the local NHS.
The distribution network of interest primarily helps to distribute bulk/commonplace items to
hospitals and clinics on a daily basis. This network does not require a “cold chain”, that is, it can be
run without a need for refrigerated vehicles. The costs are incurred per standard box carried per
mile, which has currently a rate of £1.25 (for example, if 5 boxes are carried 2 miles from the depot,
then the cost of this operation would be £12.50). The current routes used for these distribution
operations as well as their distances in miles can be found in the Excel data file, under the “GGCnetwork” tab. In this network, travel in both directions on each route is possible (and same mileage
is used for the cost purposes of both directions). The key depot for this distribution network is the
Gartnavel General Hospital (“GGH”), and the Pharmacy Distribution Centre (“PDC”) also serves as a
supply point, though rather with a significantly less capacity.
They have also provided you weekly demands for bulk items, which you can find in the data file
under the “GCC-demands” tab for different hospitals and clinics. They are aware that GGH can
currently supply up to 1,200 boxes a week, and PDC can supply up to 250 boxes a week. Also note
that currently the direct routes from/to PDC (such as PDC to/from GRI) have a capacity of 80 boxes a
week in either direction (no other route capacities in the network). They believe that one can build
a balanced network model using the daily demands as well as distances provided, and hence they
expect you to help them to build such a network LP model.
Finally, there is an ongoing discussion with regards to changing the current costing model to a fixed
charge model, where the use of a route in a week will incur a fixed charge of £150, plus a reduced
rate of £0.55 per box per mile (instead of the current rate of £1.25). The fixed charge will incur only
once on a route (even if both directions are used), and if a route is not used, then the cost will be 0.
MaximizeHealth expects you to deliver the following:
a) Build a single Excel file from the data file provided to you, where you create two new sheets,
one for the network LP model (the current distribution network problem), and one for the
fixed charge problem. You should not be copying data around but rather link data as
needed. The two sheets you create should be designed properly following “good
spreadsheet modelling”, and with the Solver set up correctly so that the experts at
MaximizeHealth can easily understand your model and resolve it as needed (also they may
need to do modifications in the future). Your two models are equally valuable to the
company (hence expect an even split of marks between the two models).
(80 marks)
b) You are expected to write a memo to the CTO of the company, Ms. Ragina Krishnan (at most
3 pages with standard margins, 11pt Arial or Calibri, 1.5 line spacing). Your memo should
briefly explain key aspects of your Excel models (such as how you structured your
constraints, any Excel tricks you performed to make your model effective, and so on) and
any key messages regarding model outputs. It is perfectly fine to refer to your Excel model
when writing your memo. (Note: A memo is in a way an ‘informal letter’ written to someone
to give key messages; also note that as CTO, Ms. Krishnan has technical expertise, so you can
certainly use technical language when you need to.)
(20 marks)
Deliverables: One Excel file and one Word/PDF file. You can name the files as you wish, but they
should start with your group number, e.g. “GroupA_abcdef.doc”.