首页 > > 详细

MS980 Business Analytics

 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 “GGC￾network” 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”.
联系我们 - QQ: 99515681 微信:codinghelp2
© 2014 www.7daixie.com
程序代写网!